Hello,
Brief overview. Got 2 tables, client table and document table. Both tables have client name as the primary key. Client table shows client info, address, phone, dob. Document table shows client name, document, document type.
I need to write a query that will count how many documents are in the table for each name.
This is attempt at it, please let me know whats wrong. Thanks.
SELECT count [client table].client name as cli_name, count ([document table].name as doc_qty)
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].nameYour syntax is funny:
Try
count ([document table].name) as doc_qty|||wow, that was it, thanks a lot|||Agree also no count at the start.
SELECT [client table].client name as cli_name, count ([document table].name) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].name
Also so check the ID`s think they might be wrong or badly named.
Ie. the document table link on id not clientID
If it does not work post the tables as well|||yeah, the id thing was throwing it off too, i had to rejoin by name and client name from both tables after I gave some thought as to what exactly this query needed to do
working query:
SELECT [client table].[client name] as cli_name, count ([document table].[name]) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].[client name] = [document table].name
GROUP BY [client table].[client name]
ORDER BY [client table].[client name];
Had another question, I'm supposed to place a constraint into both tables to lock them and not allow any new data to be entered. I have read how to write it in code, but am using access. Is there any way to modify and create tables in access2003 in sql view? I really cant stand the GUI.|||Sure, you can use a CREATE TABLE statement just as you would using Query Analyzer.|||sweet, thanks a lot for making my life a whole lot easier(for today atleast)sql
Showing posts with label client. Show all posts
Showing posts with label client. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Count and top
I have complicated select statement which returns a lot of rows.
I would like to show only top 50 rows on client, but also notice the client
with the number of all rows of his search statement, like
Showing top 50 of 1000 rows.
How can I get count of all rows, something like:
SELECT top 50 *, count(all) from .......
One way is to create 2 select statements:
first one to get count of all records suitable to client search
SELECT count(*) FROM ....WHERE ...
and second to show the client only top 50 of them:
SELECT top 50 * FROM...WHERE ...
I wonder, can this be done with one select statement?
Regards,SSee the following link for a number of possible options.
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%232lQ41obGHA.3388@.TK2MSFTNGP05.phx.gbl...
>I have complicated select statement which returns a lot of rows.
> I would like to show only top 50 rows on client, but also notice the
> client with the number of all rows of his search statement, like
> Showing top 50 of 1000 rows.
> How can I get count of all rows, something like:
> SELECT top 50 *, count(all) from .......
> One way is to create 2 select statements:
> first one to get count of all records suitable to client search
> SELECT count(*) FROM ....WHERE ...
> and second to show the client only top 50 of them:
> SELECT top 50 * FROM...WHERE ...
> I wonder, can this be done with one select statement?
> Regards,S
>
>|||You can use @.@.ROWCOUNT.
And make a sp returns ROWCOUNT.
SELECT Top 50 * FROM WHERE..,
SELECT @.@.ROWCOUNT
"simonZ"?? ??? ??:
> I have complicated select statement which returns a lot of rows.
> I would like to show only top 50 rows on client, but also notice the clien
t
> with the number of all rows of his search statement, like
> Showing top 50 of 1000 rows.
> How can I get count of all rows, something like:
> SELECT top 50 *, count(all) from .......
> One way is to create 2 select statements:
> first one to get count of all records suitable to client search
> SELECT count(*) FROM ....WHERE ...
> and second to show the client only top 50 of them:
> SELECT top 50 * FROM...WHERE ...
> I wonder, can this be done with one select statement?
> Regards,S
>
>
>|||It can be done in one select that queries the table twice. Use a
subquery in the SELECT column list.
SELECT top 50 *,
(select count(all) from .......) as CountAll
FROM ......
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 10:42:07 +0200, "simonZ"
<simon.zupan@.studio-moderna.com> wrote:
>I have complicated select statement which returns a lot of rows.
>I would like to show only top 50 rows on client, but also notice the client
>with the number of all rows of his search statement, like
>Showing top 50 of 1000 rows.
>How can I get count of all rows, something like:
>SELECT top 50 *, count(all) from .......
>One way is to create 2 select statements:
>first one to get count of all records suitable to client search
>SELECT count(*) FROM ....WHERE ...
>and second to show the client only top 50 of them:
>SELECT top 50 * FROM...WHERE ...
>I wonder, can this be done with one select statement?
>Regards,S
>
>|||hongju je napisal:
> You can use @.@.ROWCOUNT.
> And make a sp returns ROWCOUNT.
> SELECT Top 50 * FROM WHERE..,
> SELECT @.@.ROWCOUNT
>
> "simonZ"=EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1=ED=95=9C =EB=82=B4=EC=9A=A9:
>
ient
I would like to show only top 50 rows on client, but also notice the client
with the number of all rows of his search statement, like
Showing top 50 of 1000 rows.
How can I get count of all rows, something like:
SELECT top 50 *, count(all) from .......
One way is to create 2 select statements:
first one to get count of all records suitable to client search
SELECT count(*) FROM ....WHERE ...
and second to show the client only top 50 of them:
SELECT top 50 * FROM...WHERE ...
I wonder, can this be done with one select statement?
Regards,SSee the following link for a number of possible options.
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%232lQ41obGHA.3388@.TK2MSFTNGP05.phx.gbl...
>I have complicated select statement which returns a lot of rows.
> I would like to show only top 50 rows on client, but also notice the
> client with the number of all rows of his search statement, like
> Showing top 50 of 1000 rows.
> How can I get count of all rows, something like:
> SELECT top 50 *, count(all) from .......
> One way is to create 2 select statements:
> first one to get count of all records suitable to client search
> SELECT count(*) FROM ....WHERE ...
> and second to show the client only top 50 of them:
> SELECT top 50 * FROM...WHERE ...
> I wonder, can this be done with one select statement?
> Regards,S
>
>|||You can use @.@.ROWCOUNT.
And make a sp returns ROWCOUNT.
SELECT Top 50 * FROM WHERE..,
SELECT @.@.ROWCOUNT
"simonZ"?? ??? ??:
> I have complicated select statement which returns a lot of rows.
> I would like to show only top 50 rows on client, but also notice the clien
t
> with the number of all rows of his search statement, like
> Showing top 50 of 1000 rows.
> How can I get count of all rows, something like:
> SELECT top 50 *, count(all) from .......
> One way is to create 2 select statements:
> first one to get count of all records suitable to client search
> SELECT count(*) FROM ....WHERE ...
> and second to show the client only top 50 of them:
> SELECT top 50 * FROM...WHERE ...
> I wonder, can this be done with one select statement?
> Regards,S
>
>
>|||It can be done in one select that queries the table twice. Use a
subquery in the SELECT column list.
SELECT top 50 *,
(select count(all) from .......) as CountAll
FROM ......
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 10:42:07 +0200, "simonZ"
<simon.zupan@.studio-moderna.com> wrote:
>I have complicated select statement which returns a lot of rows.
>I would like to show only top 50 rows on client, but also notice the client
>with the number of all rows of his search statement, like
>Showing top 50 of 1000 rows.
>How can I get count of all rows, something like:
>SELECT top 50 *, count(all) from .......
>One way is to create 2 select statements:
>first one to get count of all records suitable to client search
>SELECT count(*) FROM ....WHERE ...
>and second to show the client only top 50 of them:
>SELECT top 50 * FROM...WHERE ...
>I wonder, can this be done with one select statement?
>Regards,S
>
>|||hongju je napisal:
> You can use @.@.ROWCOUNT.
> And make a sp returns ROWCOUNT.
> SELECT Top 50 * FROM WHERE..,
> SELECT @.@.ROWCOUNT
>
> "simonZ"=EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1=ED=95=9C =EB=82=B4=EC=9A=A9:
>
ient
Couldn't connect to my server from client
I have a communication error from my client when I try to connect to my SQL
server 2000 SP4 on a Windows 2003 server. Is there any method that I can get
to see if I don't miss something when I have made the installation on the
server part ?
Every help will be welcome
Regards,
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200609/1I dont believe that SQL server keeps track of that type of information
unless you have C2 auditing enabled. You could also pick that up in a trace
file had you been tracing.
http://www.microsoft.com/technet/pr...tain/sqlc2.mspx
I could be wrong though. Does anyone else have any information?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Silver via droptable.com" <u26873@.uwe> wrote in message
news:669141b5c2b48@.uwe...
>I have a communication error from my client when I try to connect to my SQL
> server 2000 SP4 on a Windows 2003 server. Is there any method that I can
> get
> to see if I don't miss something when I have made the installation on the
> server part ?
> Every help will be welcome
> Regards,
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...curity/200609/1
>sql
server 2000 SP4 on a Windows 2003 server. Is there any method that I can get
to see if I don't miss something when I have made the installation on the
server part ?
Every help will be welcome
Regards,
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200609/1I dont believe that SQL server keeps track of that type of information
unless you have C2 auditing enabled. You could also pick that up in a trace
file had you been tracing.
http://www.microsoft.com/technet/pr...tain/sqlc2.mspx
I could be wrong though. Does anyone else have any information?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Silver via droptable.com" <u26873@.uwe> wrote in message
news:669141b5c2b48@.uwe...
>I have a communication error from my client when I try to connect to my SQL
> server 2000 SP4 on a Windows 2003 server. Is there any method that I can
> get
> to see if I don't miss something when I have made the installation on the
> server part ?
> Every help will be welcome
> Regards,
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...curity/200609/1
>sql
Couldn't connect server
Hi to all,
When I try to connect SQL 2000 Enterprise client with SQL
Server 2000 Enterprise Server , the following error
message displayed.
Unable to connect to server
Server:Msg 17,Level 16,State 1
Microsoft ODBC SQL Server Driver TCP/IP Sockets SQL
Server does not exist or access denied
How can i solve this problem?
Database Server configuration:
Windows Server 2003 with SQL Server 2000 Enterprise
Edition
Advance thankx
Sankar.First, verify you can make a network connection like a mapped drive to the
server.
Second, open Client Network Utility on your workstation, Server Network
Utility on the server (both programs are in the SQL Program group) and
verify you are both listening on the same protocols in the same order.
Lastly, you should verify MSSQLServer service is running on the server and
that there are no firewalls in between your computers which might be
blocking ports.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Sankar" <anonymous@.discussions.microsoft.com> wrote in message
news:04c801c3d8ea$36ab78e0$a301280a@.phx.gbl...
the SQL Server machine by name. If not, try specifying the SQL Server
machine name ip address as the server name when attempting to connect. If
the first fails but the second succeeds then there is a name resplution
problem. If the second fails as well then there could be a network related
problem that is preventing the client from accessing the SQL Server.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||> the SQL Server machine by name. If not, try specifying the SQL Server
I have the same problem but I can ping the machine.
Mike Collier BSc( Hons) Comp Sci
Get a copy of AdoAnywhere Browser FREE if you register for the forum
quickly...
http://www.adoanywhere.com/forum
"Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
news:h0mDbBU2DHA.2184@.cpmsftngxa07.phx.gbl...
ping
328306 INF: Potential Causes of the ("SQL Server Does Not Exist or Access
http://support.microsoft.com/?id=328306
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
When I try to connect SQL 2000 Enterprise client with SQL
Server 2000 Enterprise Server , the following error
message displayed.
Unable to connect to server
Server:Msg 17,Level 16,State 1
Microsoft ODBC SQL Server Driver TCP/IP Sockets SQL
Server does not exist or access denied
How can i solve this problem?
Database Server configuration:
Windows Server 2003 with SQL Server 2000 Enterprise
Edition
Advance thankx
Sankar.First, verify you can make a network connection like a mapped drive to the
server.
Second, open Client Network Utility on your workstation, Server Network
Utility on the server (both programs are in the SQL Program group) and
verify you are both listening on the same protocols in the same order.
Lastly, you should verify MSSQLServer service is running on the server and
that there are no firewalls in between your computers which might be
blocking ports.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Sankar" <anonymous@.discussions.microsoft.com> wrote in message
news:04c801c3d8ea$36ab78e0$a301280a@.phx.gbl...
quote:|||Also, since you are attempting a TCP?IP connection verify that you can ping
> Hi to all,
> When I try to connect SQL 2000 Enterprise client with SQL
> Server 2000 Enterprise Server , the following error
> message displayed.
>
> Unable to connect to server
> Server:Msg 17,Level 16,State 1
> Microsoft ODBC SQL Server Driver TCP/IP Sockets SQL
> Server does not exist or access denied
> How can i solve this problem?
> Database Server configuration:
> Windows Server 2003 with SQL Server 2000 Enterprise
> Edition
>
> Advance thankx
> Sankar.
the SQL Server machine by name. If not, try specifying the SQL Server
machine name ip address as the server name when attempting to connect. If
the first fails but the second succeeds then there is a name resplution
problem. If the second fails as well then there could be a network related
problem that is preventing the client from accessing the SQL Server.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||> the SQL Server machine by name. If not, try specifying the SQL Server
quote:
> machine name ip address as the server name when attempting to connect. If
> the first fails but the second succeeds then there is a name resplution
> problem. If the second fails as well then there could be a network related
> problem that is preventing the client from accessing the SQL Server.
I have the same problem but I can ping the machine.
Mike Collier BSc( Hons) Comp Sci
Get a copy of AdoAnywhere Browser FREE if you register for the forum
quickly...
http://www.adoanywhere.com/forum
"Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
news:h0mDbBU2DHA.2184@.cpmsftngxa07.phx.gbl...
quote:
> Also, since you are attempting a TCP?IP connection verify that you can
ping
quote:|||Work through this article:
> the SQL Server machine by name. If not, try specifying the SQL Server
> machine name ip address as the server name when attempting to connect. If
> the first fails but the second succeeds then there is a name resplution
> problem. If the second fails as well then there could be a network related
> problem that is preventing the client from accessing the SQL Server.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
328306 INF: Potential Causes of the ("SQL Server Does Not Exist or Access
http://support.microsoft.com/?id=328306
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Couldn't call a procedure
Hi,
I am using MSSQL-2000. I couldn't call a procedure from a client (windows
98) but its working fine when i call it from window2000 system.
What is the reason for this?
Thanks,
SouraHi,
This will not happen. Even from client you are loging into SQL Server
machine using some tools.
Could you please exactly say how you are calling the procedure.
Thanks
Hari
SQL SERVER MVP
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:0F6D09C0-7057-4651-AEFF-A18C73691659@.microsoft.com...
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>|||Hi
Please tell us what is the authentication that u are using.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"SouRa" wrote:
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>
I am using MSSQL-2000. I couldn't call a procedure from a client (windows
98) but its working fine when i call it from window2000 system.
What is the reason for this?
Thanks,
SouraHi,
This will not happen. Even from client you are loging into SQL Server
machine using some tools.
Could you please exactly say how you are calling the procedure.
Thanks
Hari
SQL SERVER MVP
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:0F6D09C0-7057-4651-AEFF-A18C73691659@.microsoft.com...
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>|||Hi
Please tell us what is the authentication that u are using.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"SouRa" wrote:
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>
Tuesday, March 20, 2012
Couldn't call a procedure
Hi,
I am using MSSQL-2000. I couldn't call a procedure from a client (windows
98) but its working fine when i call it from window2000 system.
What is the reason for this?
Thanks,
SouraHi,
This will not happen. Even from client you are loging into SQL Server
machine using some tools.
Could you please exactly say how you are calling the procedure.
Thanks
Hari
SQL SERVER MVP
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:0F6D09C0-7057-4651-AEFF-A18C73691659@.microsoft.com...
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>|||Hi
Please tell us what is the authentication that u are using.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"SouRa" wrote:
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>
I am using MSSQL-2000. I couldn't call a procedure from a client (windows
98) but its working fine when i call it from window2000 system.
What is the reason for this?
Thanks,
SouraHi,
This will not happen. Even from client you are loging into SQL Server
machine using some tools.
Could you please exactly say how you are calling the procedure.
Thanks
Hari
SQL SERVER MVP
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:0F6D09C0-7057-4651-AEFF-A18C73691659@.microsoft.com...
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>|||Hi
Please tell us what is the authentication that u are using.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"SouRa" wrote:
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>
Couldn't call a procedure
Hi,
I am using MSSQL-2000. I couldn't call a procedure from a client (windows
98) but its working fine when i call it from window2000 system.
What is the reason for this?
Thanks,
Soura
Hi,
This will not happen. Even from client you are loging into SQL Server
machine using some tools.
Could you please exactly say how you are calling the procedure.
Thanks
Hari
SQL SERVER MVP
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:0F6D09C0-7057-4651-AEFF-A18C73691659@.microsoft.com...
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>
|||Hi
Please tell us what is the authentication that u are using.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
"SouRa" wrote:
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>
sql
I am using MSSQL-2000. I couldn't call a procedure from a client (windows
98) but its working fine when i call it from window2000 system.
What is the reason for this?
Thanks,
Soura
Hi,
This will not happen. Even from client you are loging into SQL Server
machine using some tools.
Could you please exactly say how you are calling the procedure.
Thanks
Hari
SQL SERVER MVP
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:0F6D09C0-7057-4651-AEFF-A18C73691659@.microsoft.com...
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>
|||Hi
Please tell us what is the authentication that u are using.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
"SouRa" wrote:
> Hi,
> I am using MSSQL-2000. I couldn't call a procedure from a client (windows
> 98) but its working fine when i call it from window2000 system.
> What is the reason for this?
> Thanks,
> Soura
>
>
sql
Saturday, February 25, 2012
could not deploy the cube
hi hannes,
i tried the option you had told me
now i am getting that i dont have permission to create a object in the server i.e, the client on which i am wroking has no permission
and i dont know how to give it...
could you help me with this
lalitha
Hi Lalithak,
You would use Manage Roles option in your Analysis Services Database.
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
|||
Javier Luna wrote:
Hi Lalithak,
You would use Manage Roles option in your Analysis Services Database.
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
is it in server or client..
lalitha
|||Hi Lalithak,
It is in your server.
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
|||
See my reply in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=676039&SiteID=1
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Friday, February 24, 2012
Could not continue scan with NOLOCK due to data movement. when delete a record
Error 601
Severity Level 12
Message Text
Could not continue scan with NOLOCK due to data movement.
My client always hit this error when he tried to delete a record from
table. The table contains about 1 million records. the table has a
unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
(col_a),
non-unique index idx_3 on col_c. An update trigger on table also.
When he execute
delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
the 601 error.
but if he execute
delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
delete this one record.
We execute dbcc checktable(table_a) and no error reported.
I read posts that saying it is because read-uncommitted or nolock
hint, but we do not has this options and we did not execute select
statement.
Any idea why this happens, we are afraid that the table is not healthy/
waiting to corruption. Is there anything we can do to narrow down
where the cause is and to provent it happen again?
and it is MS SQL 2000 with SP4
thanks in advance
-rockdaleDoes this KB article apply?
http://support.microsoft.com/kb/815008
Linchi
"rockdale" wrote:
> Error 601
> Severity Level 12
> Message Text
> Could not continue scan with NOLOCK due to data movement.
> My client always hit this error when he tried to delete a record from
> table. The table contains about 1 million records. the table has a
> unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> (col_a),
> non-unique index idx_3 on col_c. An update trigger on table also.
> When he execute
> delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> the 601 error.
> but if he execute
> delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> delete this one record.
> We execute dbcc checktable(table_a) and no error reported.
> I read posts that saying it is because read-uncommitted or nolock
> hint, but we do not has this options and we did not execute select
> statement.
> Any idea why this happens, we are afraid that the table is not healthy/
> waiting to corruption. Is there anything we can do to narrow down
> where the cause is and to provent it happen again?
> and it is MS SQL 2000 with SP4
> thanks in advance
> -rockdale
>|||NOPE.
The database is SQL2K with SP4.
They did not execute any select statement, not mention Read Uncommited
option.
They execute the delete statement using one where condition and get
the error.
They execute the delete statement using another where condition and
no error occured.
I even execute the DBCC TRACEON (9134, -1) as the article you
mentioned, I still getting the same error. I can not restart the sql
server, it is a production server.
Thanks
On Oct 19, 11:46 am, Linchi Shea
<LinchiS...@.discussions.microsoft.com> wrote:
> Does this KB article apply?http://support.microsoft.com/kb/815008
> Linchi
>
> "rockdale" wrote:
> > Error 601
> > Severity Level 12
> > Message Text
> > Could not continue scan with NOLOCK due to data movement.
> > My client always hit this error when he tried to delete a record from
> > table. The table contains about 1 million records. the table has a
> > unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> > (col_a),
> > non-unique index idx_3 on col_c. An update trigger on table also.
> > When he execute
> > delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> > the 601 error.
> > but if he execute
> > delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> > delete this one record.
> > We execute dbcc checktable(table_a) and no error reported.
> > I read posts that saying it is because read-uncommitted or nolock
> > hint, but we do not has this options and we did not execute select
> > statement.
> > Any idea why this happens, we are afraid that the table is not healthy/
> > waiting to corruption. Is there anything we can do to narrow down
> > where the cause is and to provent it happen again?
> > and it is MS SQL 2000 with SP4
> > thanks in advance
> > -rockdale- Hide quoted text -
> - Show quoted text -|||Look for a DELETE trigger on the table.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"rockdale" <rockdale.green@.gmail.com> wrote in message
news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
> Error 601
> Severity Level 12
> Message Text
> Could not continue scan with NOLOCK due to data movement.
> My client always hit this error when he tried to delete a record from
> table. The table contains about 1 million records. the table has a
> unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> (col_a),
> non-unique index idx_3 on col_c. An update trigger on table also.
> When he execute
> delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> the 601 error.
> but if he execute
> delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> delete this one record.
> We execute dbcc checktable(table_a) and no error reported.
> I read posts that saying it is because read-uncommitted or nolock
> hint, but we do not has this options and we did not execute select
> statement.
> Any idea why this happens, we are afraid that the table is not healthy/
> waiting to corruption. Is there anything we can do to narrow down
> where the cause is and to provent it happen again?
> and it is MS SQL 2000 with SP4
> thanks in advance
> -rockdale
>|||They have a n update trigger in the table basically update the
lastedUpdateDateTime field. The Trigger is for Update only, so I do
not think delete will trigger this trigger.
On Oct 19, 1:30 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Look for a DELETE trigger on the table.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "rockdale" <rockdale.gr...@.gmail.com> wrote in message
> news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
>
> > Error 601
> > Severity Level 12
> > Message Text
> > Could not continue scan with NOLOCK due to data movement.
> > My client always hit this error when he tried to delete a record from
> > table. The table contains about 1 million records. the table has a
> > unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> > (col_a),
> > non-unique index idx_3 on col_c. An update trigger on table also.
> > When he execute
> > delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> > the 601 error.
> > but if he execute
> > delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> > delete this one record.
> > We execute dbcc checktable(table_a) and no error reported.
> > I read posts that saying it is because read-uncommitted or nolock
> > hint, but we do not has this options and we did not execute select
> > statement.
> > Any idea why this happens, we are afraid that the table is not healthy/
> > waiting to corruption. Is there anything we can do to narrow down
> > where the cause is and to provent it happen again?
> > and it is MS SQL 2000 with SP4
> > thanks in advance
> > -rockdale- Hide quoted text -
> - Show quoted text -|||I just delete the trigger and I still getting the same error.
More Info on index:
They have a UNIQUE NONCLUSTERED Constraints on table_a
(col_a, col_b, col_c)
an INDEX on table_a
(col_c)
an INDEX on table_a
(col_a)
Might these indices problems?
thanks
On Oct 19, 1:30 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Look for a DELETE trigger on the table.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "rockdale" <rockdale.gr...@.gmail.com> wrote in message
> news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
>
> > Error 601
> > Severity Level 12
> > Message Text
> > Could not continue scan with NOLOCK due to data movement.
> > My client always hit this error when he tried to delete a record from
> > table. The table contains about 1 million records. the table has a
> > unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> > (col_a),
> > non-unique index idx_3 on col_c. An update trigger on table also.
> > When he execute
> > delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> > the 601 error.
> > but if he execute
> > delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> > delete this one record.
> > We execute dbcc checktable(table_a) and no error reported.
> > I read posts that saying it is because read-uncommitted or nolock
> > hint, but we do not has this options and we did not execute select
> > statement.
> > Any idea why this happens, we are afraid that the table is not healthy/
> > waiting to corruption. Is there anything we can do to narrow down
> > where the cause is and to provent it happen again?
> > and it is MS SQL 2000 with SP4
> > thanks in advance
> > -rockdale- Hide quoted text -
> - Show quoted text -
Severity Level 12
Message Text
Could not continue scan with NOLOCK due to data movement.
My client always hit this error when he tried to delete a record from
table. The table contains about 1 million records. the table has a
unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
(col_a),
non-unique index idx_3 on col_c. An update trigger on table also.
When he execute
delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
the 601 error.
but if he execute
delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
delete this one record.
We execute dbcc checktable(table_a) and no error reported.
I read posts that saying it is because read-uncommitted or nolock
hint, but we do not has this options and we did not execute select
statement.
Any idea why this happens, we are afraid that the table is not healthy/
waiting to corruption. Is there anything we can do to narrow down
where the cause is and to provent it happen again?
and it is MS SQL 2000 with SP4
thanks in advance
-rockdaleDoes this KB article apply?
http://support.microsoft.com/kb/815008
Linchi
"rockdale" wrote:
> Error 601
> Severity Level 12
> Message Text
> Could not continue scan with NOLOCK due to data movement.
> My client always hit this error when he tried to delete a record from
> table. The table contains about 1 million records. the table has a
> unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> (col_a),
> non-unique index idx_3 on col_c. An update trigger on table also.
> When he execute
> delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> the 601 error.
> but if he execute
> delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> delete this one record.
> We execute dbcc checktable(table_a) and no error reported.
> I read posts that saying it is because read-uncommitted or nolock
> hint, but we do not has this options and we did not execute select
> statement.
> Any idea why this happens, we are afraid that the table is not healthy/
> waiting to corruption. Is there anything we can do to narrow down
> where the cause is and to provent it happen again?
> and it is MS SQL 2000 with SP4
> thanks in advance
> -rockdale
>|||NOPE.
The database is SQL2K with SP4.
They did not execute any select statement, not mention Read Uncommited
option.
They execute the delete statement using one where condition and get
the error.
They execute the delete statement using another where condition and
no error occured.
I even execute the DBCC TRACEON (9134, -1) as the article you
mentioned, I still getting the same error. I can not restart the sql
server, it is a production server.
Thanks
On Oct 19, 11:46 am, Linchi Shea
<LinchiS...@.discussions.microsoft.com> wrote:
> Does this KB article apply?http://support.microsoft.com/kb/815008
> Linchi
>
> "rockdale" wrote:
> > Error 601
> > Severity Level 12
> > Message Text
> > Could not continue scan with NOLOCK due to data movement.
> > My client always hit this error when he tried to delete a record from
> > table. The table contains about 1 million records. the table has a
> > unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> > (col_a),
> > non-unique index idx_3 on col_c. An update trigger on table also.
> > When he execute
> > delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> > the 601 error.
> > but if he execute
> > delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> > delete this one record.
> > We execute dbcc checktable(table_a) and no error reported.
> > I read posts that saying it is because read-uncommitted or nolock
> > hint, but we do not has this options and we did not execute select
> > statement.
> > Any idea why this happens, we are afraid that the table is not healthy/
> > waiting to corruption. Is there anything we can do to narrow down
> > where the cause is and to provent it happen again?
> > and it is MS SQL 2000 with SP4
> > thanks in advance
> > -rockdale- Hide quoted text -
> - Show quoted text -|||Look for a DELETE trigger on the table.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"rockdale" <rockdale.green@.gmail.com> wrote in message
news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
> Error 601
> Severity Level 12
> Message Text
> Could not continue scan with NOLOCK due to data movement.
> My client always hit this error when he tried to delete a record from
> table. The table contains about 1 million records. the table has a
> unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> (col_a),
> non-unique index idx_3 on col_c. An update trigger on table also.
> When he execute
> delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> the 601 error.
> but if he execute
> delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> delete this one record.
> We execute dbcc checktable(table_a) and no error reported.
> I read posts that saying it is because read-uncommitted or nolock
> hint, but we do not has this options and we did not execute select
> statement.
> Any idea why this happens, we are afraid that the table is not healthy/
> waiting to corruption. Is there anything we can do to narrow down
> where the cause is and to provent it happen again?
> and it is MS SQL 2000 with SP4
> thanks in advance
> -rockdale
>|||They have a n update trigger in the table basically update the
lastedUpdateDateTime field. The Trigger is for Update only, so I do
not think delete will trigger this trigger.
On Oct 19, 1:30 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Look for a DELETE trigger on the table.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "rockdale" <rockdale.gr...@.gmail.com> wrote in message
> news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
>
> > Error 601
> > Severity Level 12
> > Message Text
> > Could not continue scan with NOLOCK due to data movement.
> > My client always hit this error when he tried to delete a record from
> > table. The table contains about 1 million records. the table has a
> > unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> > (col_a),
> > non-unique index idx_3 on col_c. An update trigger on table also.
> > When he execute
> > delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> > the 601 error.
> > but if he execute
> > delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> > delete this one record.
> > We execute dbcc checktable(table_a) and no error reported.
> > I read posts that saying it is because read-uncommitted or nolock
> > hint, but we do not has this options and we did not execute select
> > statement.
> > Any idea why this happens, we are afraid that the table is not healthy/
> > waiting to corruption. Is there anything we can do to narrow down
> > where the cause is and to provent it happen again?
> > and it is MS SQL 2000 with SP4
> > thanks in advance
> > -rockdale- Hide quoted text -
> - Show quoted text -|||I just delete the trigger and I still getting the same error.
More Info on index:
They have a UNIQUE NONCLUSTERED Constraints on table_a
(col_a, col_b, col_c)
an INDEX on table_a
(col_c)
an INDEX on table_a
(col_a)
Might these indices problems?
thanks
On Oct 19, 1:30 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Look for a DELETE trigger on the table.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "rockdale" <rockdale.gr...@.gmail.com> wrote in message
> news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
>
> > Error 601
> > Severity Level 12
> > Message Text
> > Could not continue scan with NOLOCK due to data movement.
> > My client always hit this error when he tried to delete a record from
> > table. The table contains about 1 million records. the table has a
> > unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> > (col_a),
> > non-unique index idx_3 on col_c. An update trigger on table also.
> > When he execute
> > delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> > the 601 error.
> > but if he execute
> > delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> > delete this one record.
> > We execute dbcc checktable(table_a) and no error reported.
> > I read posts that saying it is because read-uncommitted or nolock
> > hint, but we do not has this options and we did not execute select
> > statement.
> > Any idea why this happens, we are afraid that the table is not healthy/
> > waiting to corruption. Is there anything we can do to narrow down
> > where the cause is and to provent it happen again?
> > and it is MS SQL 2000 with SP4
> > thanks in advance
> > -rockdale- Hide quoted text -
> - Show quoted text -
Could not continue scan with NOLOCK due to data movement
I have a client that is continually getting this error message:
Could not continue scan with NOLOCK due to data movement
Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
Document they patched the server
http://support.microsoft.com/default.aspx/kb/815008.
Even after patching, they are continuing to get the error.
The article also mentions a trace flag, but I don't think that flag
should apply. Our software is not trying to "READ UNCOMMITTED"
isolation levels, we are providing a WITH NO LOCK optimizing hint on
the SQL statement.
Any help would be appreciated. If you need additional information, let
me know.
Thank you!
That is usually due to the fact they are running in Read Uncommitted
isolation level or are using the NOLOCK hint. If they are reading a page /
row and another user deletes that row or splits that page they get hosed.
This is usually rare unless you do a lot of scanning which typically
indicates lack of proper tuning and indexes.
Andrew J. Kelly SQL MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegro ups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>
|||Read Uncommitted and WITH NOLOCK are equivalent. WITH NOLOCK sets the
isolation level for the individual query while READ UNCOMMITTED does the
same thing for either the connection or the entire database, depending on
where it is used. You are likely using a very active transactional system
to support an analysis type query. The underlying transactional changes
cause the large aggregations or intermediate result sets to fail. You can
try using a replicated or log shipped copy as your source or upgrade to SQL
2005 and try using Snapshot isolation.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegro ups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>
Could not continue scan with NOLOCK due to data movement
Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
Document they patched the server
http://support.microsoft.com/default.aspx/kb/815008.
Even after patching, they are continuing to get the error.
The article also mentions a trace flag, but I don't think that flag
should apply. Our software is not trying to "READ UNCOMMITTED"
isolation levels, we are providing a WITH NO LOCK optimizing hint on
the SQL statement.
Any help would be appreciated. If you need additional information, let
me know.
Thank you!
That is usually due to the fact they are running in Read Uncommitted
isolation level or are using the NOLOCK hint. If they are reading a page /
row and another user deletes that row or splits that page they get hosed.
This is usually rare unless you do a lot of scanning which typically
indicates lack of proper tuning and indexes.
Andrew J. Kelly SQL MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegro ups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>
|||Read Uncommitted and WITH NOLOCK are equivalent. WITH NOLOCK sets the
isolation level for the individual query while READ UNCOMMITTED does the
same thing for either the connection or the entire database, depending on
where it is used. You are likely using a very active transactional system
to support an analysis type query. The underlying transactional changes
cause the large aggregations or intermediate result sets to fail. You can
try using a replicated or log shipped copy as your source or upgrade to SQL
2005 and try using Snapshot isolation.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegro ups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>
Could not continue scan with NOLOCK due to data movement
I have a client that is continually getting this error message:
Could not continue scan with NOLOCK due to data movement
Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
Document they patched the server
http://support.microsoft.com/default.aspx/kb/815008.
Even after patching, they are continuing to get the error.
The article also mentions a trace flag, but I don't think that flag
should apply. Our software is not trying to "READ UNCOMMITTED"
isolation levels, we are providing a WITH NO LOCK optimizing hint on
the SQL statement.
Any help would be appreciated. If you need additional information, let
me know.
Thank you!That is usually due to the fact they are running in Read Uncommitted
isolation level or are using the NOLOCK hint. If they are reading a page /
row and another user deletes that row or splits that page they get hosed.
This is usually rare unless you do a lot of scanning which typically
indicates lack of proper tuning and indexes.
Andrew J. Kelly SQL MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>|||Read Uncommitted and WITH NOLOCK are equivalent. WITH NOLOCK sets the
isolation level for the individual query while READ UNCOMMITTED does the
same thing for either the connection or the entire database, depending on
where it is used. You are likely using a very active transactional system
to support an analysis type query. The underlying transactional changes
cause the large aggregations or intermediate result sets to fail. You can
try using a replicated or log shipped copy as your source or upgrade to SQL
2005 and try using Snapshot isolation.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>
Could not continue scan with NOLOCK due to data movement
Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
Document they patched the server
http://support.microsoft.com/default.aspx/kb/815008.
Even after patching, they are continuing to get the error.
The article also mentions a trace flag, but I don't think that flag
should apply. Our software is not trying to "READ UNCOMMITTED"
isolation levels, we are providing a WITH NO LOCK optimizing hint on
the SQL statement.
Any help would be appreciated. If you need additional information, let
me know.
Thank you!That is usually due to the fact they are running in Read Uncommitted
isolation level or are using the NOLOCK hint. If they are reading a page /
row and another user deletes that row or splits that page they get hosed.
This is usually rare unless you do a lot of scanning which typically
indicates lack of proper tuning and indexes.
Andrew J. Kelly SQL MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>|||Read Uncommitted and WITH NOLOCK are equivalent. WITH NOLOCK sets the
isolation level for the individual query while READ UNCOMMITTED does the
same thing for either the connection or the entire database, depending on
where it is used. You are likely using a very active transactional system
to support an analysis type query. The underlying transactional changes
cause the large aggregations or intermediate result sets to fail. You can
try using a replicated or log shipped copy as your source or upgrade to SQL
2005 and try using Snapshot isolation.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>
Could not continue scan with NOLOCK due to data movement
I have a client that is continually getting this error message:
Could not continue scan with NOLOCK due to data movement
Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
Document they patched the server
http://support.microsoft.com/default.aspx/kb/815008.
Even after patching, they are continuing to get the error.
The article also mentions a trace flag, but I don't think that flag
should apply. Our software is not trying to "READ UNCOMMITTED"
isolation levels, we are providing a WITH NO LOCK optimizing hint on
the SQL statement.
Any help would be appreciated. If you need additional information, let
me know.
Thank you!That is usually due to the fact they are running in Read Uncommitted
isolation level or are using the NOLOCK hint. If they are reading a page /
row and another user deletes that row or splits that page they get hosed.
This is usually rare unless you do a lot of scanning which typically
indicates lack of proper tuning and indexes.
--
Andrew J. Kelly SQL MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>|||Read Uncommitted and WITH NOLOCK are equivalent. WITH NOLOCK sets the
isolation level for the individual query while READ UNCOMMITTED does the
same thing for either the connection or the entire database, depending on
where it is used. You are likely using a very active transactional system
to support an analysis type query. The underlying transactional changes
cause the large aggregations or intermediate result sets to fail. You can
try using a replicated or log shipped copy as your source or upgrade to SQL
2005 and try using Snapshot isolation.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>
Could not continue scan with NOLOCK due to data movement
Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
Document they patched the server
http://support.microsoft.com/default.aspx/kb/815008.
Even after patching, they are continuing to get the error.
The article also mentions a trace flag, but I don't think that flag
should apply. Our software is not trying to "READ UNCOMMITTED"
isolation levels, we are providing a WITH NO LOCK optimizing hint on
the SQL statement.
Any help would be appreciated. If you need additional information, let
me know.
Thank you!That is usually due to the fact they are running in Read Uncommitted
isolation level or are using the NOLOCK hint. If they are reading a page /
row and another user deletes that row or splits that page they get hosed.
This is usually rare unless you do a lot of scanning which typically
indicates lack of proper tuning and indexes.
--
Andrew J. Kelly SQL MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>|||Read Uncommitted and WITH NOLOCK are equivalent. WITH NOLOCK sets the
isolation level for the individual query while READ UNCOMMITTED does the
same thing for either the connection or the entire database, depending on
where it is used. You are likely using a very active transactional system
to support an analysis type query. The underlying transactional changes
cause the large aggregations or intermediate result sets to fail. You can
try using a replicated or log shipped copy as your source or upgrade to SQL
2005 and try using Snapshot isolation.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>
Could not connect server from client machine
Hi all
I m a new member to this forum. Im a hardware engineer who rectify aome software related issues. Now our employees having problem with connecting their XP prof based clients to 2003 server from SQL query analyzer. I tried refresh sql server manager in server and tried restarting the server machine many times. sometimes it the client machine connects and disconnects immediately. i aslo tried changing the poll interval , but no use.
waiting for a speedy solutin... i know this is as easy query.
Hi mvramsubbu,
A warm welcome to forum
You need to digg more to get the Exact erro message refer Windows Event Viewer and SQL Server's Error Log for error and post them here so that you can get a good answer. unless you post the error message we can not help you.
Hemantgiri S. Goswami
Could not bind a SQL Server 2005 Web Service to DataGridView when creating Web Service Client in
Hello,
I would like to develop a minimal Web Service Client in Visual Studio (C#) which call a Web Service created in SQL Server 2005. I have definied a simple stored procedure and a HTTP endpoint which are both working.
In Visual Studio 2005 Pro I proceed following steps:
- New application (C#) Add web reference to the project (http://localhost:8084/Produktdaten?wsdl) Add DataGridView to the form1 in the designer window Configure DataGridView Data Source by selecting "other data sources" - "project data sources" - "localhost" and "produktdaten". In the Form1 (Designer View) the columns "EnableDecompress" ... appears. I could not select my expected web method "GetProduct" definied in the stored procedure.
Afterwards I tried to solve the problem manually with the following code (form1.cs). I could compile this windows application succesfully. When I started the corresponding.exe file I got an error. In the debugging mode problems were reported with the line "dataGridView1.DataMember = "Product";". In the SQL Server database (AdventureWorks) the product table belongs to the schema production. Also with production.product the code listed below did not work.
Could you please help to get this simple web service client running.
Best regards,
Wolfgang
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace AWProductWebServiceClient
{
publicpartialclassForm1 : Form
{
public Form1()
{
InitializeComponent();
localhost.Produktdaten ws = new localhost.Produktdaten();
dataGridView1.DataSource = ws.GetProduct();
dataGridView1.DataMember = "Product";
}
privatevoid dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}
Wolfgang,
In general, you can not directly assign the result from the SQL Server 2005 Native Web Services web method as the data source to a dataGridView.
BTW, you do not need to configure the DataGridView data source at design time.
If the stored procedure was mapped to the endpoint WITHOUT the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is an array of objects. The array can contain SqlMessages, DataSet, RowCount, etc. I believe the DataGridView.DataSource requires a DataTable, but I can't be sure. What has worked for me, as a quick sample code, is:
object[] resp = proxy.method(myParam);
foreach (object o in resp)
{
if (o.GetType().ToString().Equals("System.Data.DataSet"))
{
this.dataGridView1.DataSource = ((DataSet)o).Tables[0];
}
}
If the stored procedure was mapped to the endpoint WITH the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is directly a DataSet. The DataSet object can contain multiple DataTables (depending on how many SELECT statements are in the stored procedure). You can then assign the DataTable as the data source of the DataGridView. So, something like:
DataSet ds = proxy.method(myParam);
this.dataGridView1.DataSource = ds.Tables[0];
Jimmy
|||Jimmy,
sorry for my late response (vacation).
The provided solution works fine. Also the explanation of the "FORMAT" keyword was very helpful.
Thank you very much.
Best regards,
Wolfgang
Could not bind a SQL Server 2005 Web Service to DataGridView when creating Web Service Client in
Hello,
I would like to develop a minimal Web Service Client in Visual Studio (C#) which call a Web Service created in SQL Server 2005. I have definied a simple stored procedure and a HTTP endpoint which are both working.
In Visual Studio 2005 Pro I proceed following steps:
New application (C#)
Add web reference to the project (http://localhost:8084/Produktdaten?wsdl)
Add DataGridView to the form1 in the designer window
Configure DataGridView Data Source by selecting "other data sources" - "project data sources" - "localhost" and "produktdaten". In the Form1 (Designer View) the columns "EnableDecompress" ... appears. I could not select my expected web method "GetProduct" definied in the stored procedure.
Afterwards I tried to solve the problem manually with the following code (form1.cs). I could compile this windows application succesfully. When I started the corresponding.exe file I got an error. In the debugging mode problems were reported with the line "dataGridView1.DataMember = "Product";". In the SQL Server database (AdventureWorks) the product table belongs to the schema production. Also with production.product the code listed below did not work.
Could you please help to get this simple web service client running.
Best regards,
Wolfgang
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace AWProductWebServiceClient
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
localhost.Produktdaten ws = new localhost.Produktdaten();
dataGridView1.DataSource = ws.GetProduct();
dataGridView1.DataMember = "Product";
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}
Wolfgang,
In general, you can not directly assign the result from the SQL Server 2005 Native Web Services web method as the data source to a dataGridView.
BTW, you do not need to configure the DataGridView data source at design time.
If the stored procedure was mapped to the endpoint WITHOUT the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is an array of objects. The array can contain SqlMessages, DataSet, RowCount, etc. I believe the DataGridView.DataSource requires a DataTable, but I can't be sure. What has worked for me, as a quick sample code, is:
object[] resp = proxy.method(myParam);
foreach (object o in resp)
{
if (o.GetType().ToString().Equals("System.Data.DataSet"))
{
this.dataGridView1.DataSource = ((DataSet)o).Tables[0];
}
}
If the stored procedure was mapped to the endpoint WITH the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is directly a DataSet. The DataSet object can contain multiple DataTables (depending on how many SELECT statements are in the stored procedure). You can then assign the DataTable as the data source of the DataGridView. So, something like:
DataSet ds = proxy.method(myParam);
this.dataGridView1.DataSource = ds.Tables[0];
Jimmy
|||Jimmy,
sorry for my late response (vacation).
The provided solution works fine. Also the explanation of the "FORMAT" keyword was very helpful.
Thank you very much.
Best regards,
Wolfgang
Could not bind a SQL Server 2005 Web Service to DataGridView when creating Web Service Clien
Hello,
I would like to develop a minimal Web Service Client in Visual Studio (C#) which call a Web Service created in SQL Server 2005. I have definied a simple stored procedure and a HTTP endpoint which are both working.
In Visual Studio 2005 Pro I proceed following steps:
New application (C#)
Add web reference to the project (http://localhost:8084/Produktdaten?wsdl)
Add DataGridView to the form1 in the designer window
Configure DataGridView Data Source by selecting "other data sources" - "project data sources" - "localhost" and "produktdaten". In the Form1 (Designer View) the columns "EnableDecompress" ... appears. I could not select my expected web method "GetProduct" definied in the stored procedure.
Afterwards I tried to solve the problem manually with the following code (form1.cs). I could compile this windows application succesfully. When I started the corresponding.exe file I got an error. In the debugging mode problems were reported with the line "dataGridView1.DataMember = "Product";". In the SQL Server database (AdventureWorks) the product table belongs to the schema production. Also with production.product the code listed below did not work.
Could you please help to get this simple web service client running.
Best regards,
Wolfgang
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace AWProductWebServiceClient
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
localhost.Produktdaten ws = new localhost.Produktdaten();
dataGridView1.DataSource = ws.GetProduct();
dataGridView1.DataMember = "Product";
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}
Wolfgang,
In general, you can not directly assign the result from the SQL Server 2005 Native Web Services web method as the data source to a dataGridView.
BTW, you do not need to configure the DataGridView data source at design time.
If the stored procedure was mapped to the endpoint WITHOUT the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is an array of objects. The array can contain SqlMessages, DataSet, RowCount, etc. I believe the DataGridView.DataSource requires a DataTable, but I can't be sure. What has worked for me, as a quick sample code, is:
object[] resp = proxy.method(myParam);
foreach (object o in resp)
{
if (o.GetType().ToString().Equals("System.Data.DataSet"))
{
this.dataGridView1.DataSource = ((DataSet)o).Tables[0];
}
}
If the stored procedure was mapped to the endpoint WITH the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is directly a DataSet. The DataSet object can contain multiple DataTables (depending on how many SELECT statements are in the stored procedure). You can then assign the DataTable as the data source of the DataGridView. So, something like:
DataSet ds = proxy.method(myParam);
this.dataGridView1.DataSource = ds.Tables[0];
Jimmy
|||Jimmy,
sorry for my late response (vacation).
The provided solution works fine. Also the explanation of the "FORMAT" keyword was very helpful.
Thank you very much.
Best regards,
Wolfgang
Subscribe to:
Posts (Atom)