Reading a record without placing a lock

Dear All,
I have one server application running which continously reading and
updating a DB.
While there is a Reporting tool which generating reports.
The reports can fail, but the server application cannot. So, I need to
run a query in the Reporting tool without placing a lock on the DB
(totally transaprent to the server).
Currently this is what I am doing.
SELECT * FROM Data WITH (NOLOCK);
Question:
1. Am I doing it correctly?
2. Is there a better way of doing? Example: setting the LOCK MODE
(instead of specifying NOLOCK on every command)
Thanks in advance.<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.
Hi
You can set the transaction isolation level to read uncommitted for the
session, but then you are potentially going to have dirty reads. Other ways
to do this would be to offload the reporting database either by using log
shipping, replication, mirroring or a snapshot.
John|||Hi
> 1. Am I doing it correctly?
No. What if some user inserts/deletes the row while you are reading. You
are about to get an inconsistent data. For example
you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
so while you read page A another connection inserst the value let me say
50, but you have already read data from the page A , so it moves the all
data to a new created page so now that data looks like a)10,40 ,50,
b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
60(duplicate) from page D as well.
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
at SNAPSHOT ISOLATION LEVEL in the BOL
<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.|||Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row =A0while =A0you are reading.= You
> are about to get an inconsistent data. For example
> =A0you have tree pages with data =A0like a) 10,40,60 b) 80,100,90 =A0c)110=,70,85 ,
> so while =A0you read page A =A0another connection inserst the value let me= say
> 50, but you have already read data =A0from the page A , so it moves the al=l
> data to a new created page so now that data looks like =A0a)10,40 ,50,
> b) 80,100,90 =A0c)110,70,85 ,d)60 ... and =A0as you keep reading =A0you g=et
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint =A0or if you use SQL Server 2005 =A0take a =look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message|||<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message
Hi
I can't see how unreliability and inconsistence made the user requirements
for this system!
John|||<<There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).>>
The ANSI SQL Compliant way to describe how much you want to be isolated from other users is the SET
TRANSACTION ISOLATION command. SQL Server supports this, and READ UNCOMMITTED seems to do what you
want. Apparently Informix has a non-standard command named LOCK MODE, something that SQL Server do
not have. Assuming these indeed do the same thing, I support MS for using the ANSI SQL compliant
name for the command instead of some other command name. If they do not do the same, perhaps you can
enlighten un in what way they differ?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message

Reading .LDF

Dear all,
How to identity all those changes done in a db for any user in a specific
period of time?
I am not talking about put triggers in each object or something like that
(on the other hand such actions never will reach exhaustely all the actions
commited)
For example:
developer1 modify a view at 16:00 and then developer2 delete it.
Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
level (hexadecimal info)
I've been able to to order theses scenarios (values as follows belong to
field 'OPERATION':
0)AFTER TOTAL SHRINK (O KB)
LOP_BEGIN_CKPT
LOP_END_CKPT
LOP_FILE_HDR_MODIFY
1)FOR INSERT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_COMMIT_XACT
2)FOR UPDATE STATEMENT:
LOP_BEGIN_XACT
LOP_MODIFY_ROW
LOP_COMMIT_XACT
3)FOR CREATE OBJECT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_MARK_DDL
It seems very tricky but I suppose that there would be some method for
obtain such information. How can I do this?
Perhaps, it's a useless madness, obsessive.
--
Current location: Alicante (ES)Enric
Visit at
http://www.lumigent.com/ --explorer log for sql
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:A38C627F-9C4C-44B6-B012-F94BA91DBFB5@.microsoft.com...
> Dear all,
> How to identity all those changes done in a db for any user in a specific
> period of time?
> I am not talking about put triggers in each object or something like that
> (on the other hand such actions never will reach exhaustely all the
> actions
> commited)
> For example:
> developer1 modify a view at 16:00 and then developer2 delete it.
> Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
> level (hexadecimal info)
> I've been able to to order theses scenarios (values as follows belong to
> field 'OPERATION':
> 0)AFTER TOTAL SHRINK (O KB)
> LOP_BEGIN_CKPT
> LOP_END_CKPT
> LOP_FILE_HDR_MODIFY
> 1)FOR INSERT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_COMMIT_XACT
> 2)FOR UPDATE STATEMENT:
> LOP_BEGIN_XACT
> LOP_MODIFY_ROW
> LOP_COMMIT_XACT
>
> 3)FOR CREATE OBJECT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_MARK_DDL
>
> It seems very tricky but I suppose that there would be some method for
> obtain such information. How can I do this?
> Perhaps, it's a useless madness, obsessive.
> --
> Current location: Alicante (ES)|||> How to identity all those changes done in a db for any user in a specific
> period of time?
In hindsight:
http://www.aspfaq.com/2449
Moving forward:
http://www.aspfaq.com/2496|||Thanks a lot to both
--
Current location: Alicante (ES)
"Aaron Bertrand [SQL Server MVP]" wrote:

> In hindsight:
> http://www.aspfaq.com/2449
> Moving forward:
> http://www.aspfaq.com/2496
>
>|||There are 3rd party tools for mining information from the transaction log.
However, the intended purpose of this log is for maintaining transactions,
not auditing, so you may find it limiting if your intent is monitoring user
activity.
With a little studying and configuration, you can trace most any desired
event using SQL Server Profiler, which is a tool installed with SQL Server.
Depending on what event categories you choose, you can trace not just data
or object modifications but also things like SP executions and even table
selects. It is basically a service that runs on server, and it's output can
be archived to a file or table. You will need admin rights to use this tool.
http://msdn.microsoft.com/library/d...>
erf_86ib.asp
[url]http://www.sqlservercentral.com/columnists/bkelley/auditingwithsqlprofiler.asp[/ur
l]
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:A38C627F-9C4C-44B6-B012-F94BA91DBFB5@.microsoft.com...
> Dear all,
> How to identity all those changes done in a db for any user in a specific
> period of time?
> I am not talking about put triggers in each object or something like that
> (on the other hand such actions never will reach exhaustely all the
> actions
> commited)
> For example:
> developer1 modify a view at 16:00 and then developer2 delete it.
> Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
> level (hexadecimal info)
> I've been able to to order theses scenarios (values as follows belong to
> field 'OPERATION':
> 0)AFTER TOTAL SHRINK (O KB)
> LOP_BEGIN_CKPT
> LOP_END_CKPT
> LOP_FILE_HDR_MODIFY
> 1)FOR INSERT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_COMMIT_XACT
> 2)FOR UPDATE STATEMENT:
> LOP_BEGIN_XACT
> LOP_MODIFY_ROW
> LOP_COMMIT_XACT
>
> 3)FOR CREATE OBJECT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_MARK_DDL
>
> It seems very tricky but I suppose that there would be some method for
> obtain such information. How can I do this?
> Perhaps, it's a useless madness, obsessive.
> --
> Current location: Alicante (ES)

Read SDF file in Desktop

Dear all,

I Need to read the Data from the SDF file in the Desktop. I have SQL Server 2005 in my System.

i know this is possible. but i dont know how it is ....

regards,

Tamil

You must install SQL Server Compact Edition 3.1 on your desktop in order to open SDF files.|||

Thank you Paulo. It is very useful..

Now i can manipulate the datas in the SDF files in VS 2005 environment.

Can i access those data using C# coding in Web Application or WS?

To connect this SDF file which provider i have to use?

Thanks & Regards,

|||

Yes i got it !!!

Now I can Read the SDF file data by importing System.Data.SqlServerCe; from the below Location

$Program Files$\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies\System.Data.SqlServerCe.dll

See the below Link

http://www.codeproject.com/cs/database/StartSqlServerEW.asp?df=100&forumid=321708&exp=0&select=1584032

Thanks And Regards

Re: help please

Dear all,

I have 3 column: date, time, stocks name, price

I have 2 questions:

1. what is the command (or query languange) to get the
the first and/or last observations for any given day (i know it can be done in
aggregate query, in Ms acces but can it be done in SQL server query as well?)?
e.g. I want to get the first and last price of the day for any particular stocks

2. how to calculate return with the following formula:
return=log P(t)-log P(t-1), where P(t) is price at
time t say 10 am and P(t-1) is price at one period
previous t say 9 am?

Regards

CharlyThe min and max functions will tell you the price ranges

eg select max(pricecolumn) from tablename where date='20040517'

select min(pricecolumn) from tablename where date='20040517'

If you want to be more selective look at the date/time setting you are using in your data and tailor the where command to select at that particular time

eg where date='2003-02-28 10:00:00.000'

Look at books online for the log function, and use selective where clauses for the times, ie where date='2004-05-17 10:00:00.000'|||select max(pricecolumn) from tablename where date='20040517'
broup by [stocks name]

RDA pull exception

Dear All,

I created a sample test application to implement RDA, after I made all required configuration, I got the following exception after call rda.pull() :

{
Error # 1 of 1
Error Code: -2147024809
Message : An error has occurred on the computer running IIS. Try restarting the IIS server.
Minor sqlError.: 28022
Source : Microsoft SQL Server 2005 Mobile Edition}

Note: rda.submitQuery is executed successfully ..

any ideas

Thanks and regards

Hullo Ataha,

Now i facing the same pbm faced u, could u solve this error, then pls let me know, how to do it ?

RDA pull exception

Dear All,

I created a sample test application to implement RDA, after I made all required configuration, I got the following exception after call rda.pull() :

{
Error # 1 of 1
Error Code: -2147024809
Message : An error has occurred on the computer running IIS. Try restarting the IIS server.
Minor sqlError.: 28022
Source : Microsoft SQL Server 2005 Mobile Edition}

Note: rda.submitQuery is executed successfully ..

any ideas

Thanks and regards

Hullo Ataha,

Now i facing the same pbm faced u, could u solve this error, then pls let me know, how to do it ?

RDA Pull error using Microsoft SQL Server Compact Edition 2005

Hello dear all.

I am using a Compact Framework 2.0 on a mobile 5.0 with a local Microsoft SQL Compact Edition 2005 database. The backend database is a Microsoft SQL Server 2005 and I am trying to pull a 45000 rows table (the table has 2 varchars(8) forming a primary key and an INTEGER column).

The statement I issue is the following:

rda.Pull(table.TableName, table.TableQuery, Program.RdaOleDbConnectionString, RdaTrackOption.TrackingOnWithIndexes, table.TableName + "Errors");

The application works perfectly when I am pulling smaller tables (up to 15000 rows) but when I try to pull this one I get the following error details:

sqlCeEx
{""}
base {System.SystemException}: {""}
Errors: {System.Data.SqlServerCe.SqlCeErrorCollection}
errors: {System.Data.SqlServerCe.SqlCeErrorCollection}
HResult: -2147024882
Message: ""
NativeError: 0
Source: "Microsoft SQL Server Compact Edition"

Please have in mind that the handheld on which I deploy does not have an extra storage card.

I have done a thorough internet search and I have found no solution to this problem. I truly rely on you to find the solution.

Thanks in advance,

Steliosvcy.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlce/html/sqlce_troubleconnect.asp

Sounds like a timeout error to me.

HRESULT = -2147012894 = FFFFFFFF80072EE2

Take the 2EE2, Convert back to decimal (12002).
Lookup the error in wininet.h (C:\program files\windows ce something\something\include

//
// Internet API error returns
//

#define INTERNET_ERROR_BASE 12000
#define ERROR_INTERNET_TIMEOUT (INTERNET_ERROR_BASE + 2)

How to fix it? Gawd knows. Change the timeout? Let me know how to do that if you find out!! (That's what I'm trying to do at the moment)

Regards,

James.
|||

You can set the oledb timeout (from IIS to SQL) with: Connect Timeout=180 in the connection string (3 minutes in this case).

Code Snippet

Provider=SQLOLEDB; Data Source=mySQLServer; Initial Catalog=NorthWind; user id=sa; password=; Connect Timeout=180;

You may have to adjust to fit your environment.

|||

SqlCeReplication and SqlCeRemoteDataAccess. Both of these have SendTimeout, ReceiveTimeout for IIS connectivity and SQL Server connectivity can be controlled by OLEDB connection string or QueryTimeout on REPL/RDA Object.

Thanks,

Laxmi

RDA Pull error using Microsoft SQL Server Compact Edition 2005

Hello dear all.

I am using a Compact Framework 2.0 on a mobile 5.0 with a local Microsoft SQL Compact Edition 2005 database. The backend database is a Microsoft SQL Server 2005 and I am trying to pull a 45000 rows table (the table has 2 varchars(8) forming a primary key and an INTEGER column).

The statement I issue is the following:

rda.Pull(table.TableName, table.TableQuery, Program.RdaOleDbConnectionString, RdaTrackOption.TrackingOnWithIndexes, table.TableName + "Errors");

The application works perfectly when I am pulling smaller tables (up to 15000 rows) but when I try to pull this one I get the following error details:

sqlCeEx
{""}
base {System.SystemException}: {""}
Errors: {System.Data.SqlServerCe.SqlCeErrorCollection}
errors: {System.Data.SqlServerCe.SqlCeErrorCollection}
HResult: -2147024882
Message: ""
NativeError: 0
Source: "Microsoft SQL Server Compact Edition"

Please have in mind that the handheld on which I deploy does not have an extra storage card.

I have done a thorough internet search and I have found no solution to this problem. I truly rely on you to find the solution.

Thanks in advance,

Steliosvcy.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlce/html/sqlce_troubleconnect.asp

Sounds like a timeout error to me.

HRESULT = -2147012894 = FFFFFFFF80072EE2

Take the 2EE2, Convert back to decimal (12002).
Lookup the error in wininet.h (C:\program files\windows ce something\something\include

//
// Internet API error returns
//

#define INTERNET_ERROR_BASE 12000
#define ERROR_INTERNET_TIMEOUT (INTERNET_ERROR_BASE + 2)

How to fix it? Gawd knows. Change the timeout? Let me know how to do that if you find out!! (That's what I'm trying to do at the moment)

Regards,

James.
|||

You can set the oledb timeout (from IIS to SQL) with: Connect Timeout=180 in the connection string (3 minutes in this case).

Code Snippet

Provider=SQLOLEDB; Data Source=mySQLServer; Initial Catalog=NorthWind; user id=sa; password=; Connect Timeout=180;

You may have to adjust to fit your environment.

|||

SqlCeReplication and SqlCeRemoteDataAccess. Both of these have SendTimeout, ReceiveTimeout for IIS connectivity and SQL Server connectivity can be controlled by OLEDB connection string or QueryTimeout on REPL/RDA Object.

Thanks,

Laxmi