Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @.@.ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @.@.ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @.@.ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Delete a row and all other rows thats linked to it

Hi
i want to delete a row in my database but the problem is, i cant delete it as other table rows is linked to it. I have to delete all the rows thats linked to the row i wanna delete first.

Is there a easier way to delete the row and all the rows thats linked to it? i wanna code it to do it.

an suggestions?That's what cascading deletes are for, but that's in the database design. Do searches on Declaritive Referential Integrity or DRI.sql

Delegation through Linked Server Stops working

I have a Linked Server from SQL 2005 to a SQL 2000 server. I have it
configured to use delegation. This will work fine for a while and then
suddenly stop working. Sometimes it works for an hour, sometimes for a day.
I have to restart the SQL 2005 server and it will begin to work again. The
error is:
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Any ideas?A few others have reported similar issues - with no
solutions. I worked at a place where we had delegation
sporadically failing and then working after reboots. A
ticket was opened with Microsoft but the issue was never
resolved. I would guess it's a Kerberos issue, not a SQL
issue. Make sure AD is clean and you don't have duplicate or
bad SPNs for all machines involved. Make sure all machines
involved have times sync working correctly, using the same
time server.
I'd suggest getting the Kerberos Delegation troubleshooting
doc available at:
http://www.microsoft.com/downloads/...&DisplayLang=en
We also installed a tool that would do verbose logging for
Kerberos errors - I just looked and couldn't find the tool.
Maybe if someone else knows they will jump in and provide a
link for that tool.
It can be a difficult issue to troubleshoot and you may want
to consider opening up a support ticket with Microsoft
Product Support.
-Sue
On Wed, 16 Aug 2006 12:43:01 -0700, Sheriff
<Sheriff@.discussions.microsoft.com> wrote:

>I have a Linked Server from SQL 2005 to a SQL 2000 server. I have it
>configured to use delegation. This will work fine for a while and then
>suddenly stop working. Sometimes it works for an hour, sometimes for a day
.
>I have to restart the SQL 2005 server and it will begin to work again. The
>error is:
>TCP Provider: An existing connection was forcibly closed by the remote host
.
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Any ideas?|||Is there a solution for this issue.
delegation on linked server fails in our network when we use
nt-authenticated logins. we have a sql server 2000 nodes (n1,n2) on win 2003
cluster.
Any thots,hints,links,pointers appreciated
thanks,
GA
"Sue Hoegemeier" wrote:

> A few others have reported similar issues - with no
> solutions. I worked at a place where we had delegation
> sporadically failing and then working after reboots. A
> ticket was opened with Microsoft but the issue was never
> resolved. I would guess it's a Kerberos issue, not a SQL
> issue. Make sure AD is clean and you don't have duplicate or
> bad SPNs for all machines involved. Make sure all machines
> involved have times sync working correctly, using the same
> time server.
> I'd suggest getting the Kerberos Delegation troubleshooting
> doc available at:
> http://www.microsoft.com/downloads/...&DisplayLang=en
> We also installed a tool that would do verbose logging for
> Kerberos errors - I just looked and couldn't find the tool.
> Maybe if someone else knows they will jump in and provide a
> link for that tool.
> It can be a difficult issue to troubleshoot and you may want
> to consider opening up a support ticket with Microsoft
> Product Support.
> -Sue
> On Wed, 16 Aug 2006 12:43:01 -0700, Sheriff
> <Sheriff@.discussions.microsoft.com> wrote:
>
>|||Are you having a completely different issue?
This post was about delegation working and then suddenly
failing until a reboot. Is this your issue?
-Sue
On Sun, 27 Aug 2006 12:23:01 -0700, DallasBlue
<DallasBlue@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Is there a solution for this issue.
>delegation on linked server fails in our network when we use
>nt-authenticated logins. we have a sql server 2000 nodes (n1,n2) on win 200
3
>cluster.
>Any thots,hints,links,pointers appreciated
>thanks,
>GA
>"Sue Hoegemeier" wrote:
>|||looks like it works for few minutes when restarted the nodes...
"Sue Hoegemeier" wrote:

> Are you having a completely different issue?
> This post was about delegation working and then suddenly
> failing until a reboot. Is this your issue?
> -Sue
> On Sun, 27 Aug 2006 12:23:01 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>|||yes, when we restat the nodes the kerberos delegation starts to work for few
minutes and then stops with the 'login failed reason (null)' error...
"Sue Hoegemeier" wrote:

> Are you having a completely different issue?
> This post was about delegation working and then suddenly
> failing until a reboot. Is this your issue?
> -Sue
> On Sun, 27 Aug 2006 12:23:01 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>|||So then you followed everything in the troubleshooting
delegation doc? No one has every really posted any
resolution. I had posted a lot of steps Microsoft will have
you do when/if you open a ticket. That's about all I know
about it. It's not really a SQL issue, it's a kerberos
issue. Whether it's AD problems or issues with tickets
expiring, it's hard to say.
-Sue
On Tue, 29 Aug 2006 14:38:02 -0700, DallasBlue
<DallasBlue@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>yes, when we restat the nodes the kerberos delegation starts to work for fe
w
>minutes and then stops with the 'login failed reason (null)' error...
>"Sue Hoegemeier" wrote:
>|||do have a ticket open with microsoft for more than a month now, but no
resolution yet. "Troubleshooting Kerberos delation" is nearly a 90 page doc.
tried a lot of things nothing seems to fix it...
"Sue Hoegemeier" wrote:

> So then you followed everything in the troubleshooting
> delegation doc? No one has every really posted any
> resolution. I had posted a lot of steps Microsoft will have
> you do when/if you open a ticket. That's about all I know
> about it. It's not really a SQL issue, it's a kerberos
> issue. Whether it's AD problems or issues with tickets
> expiring, it's hard to say.
> -Sue
> On Tue, 29 Aug 2006 14:38:02 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>|||Yup...a client site I was at had the issue and an open case
with PSS. At first I thought it was expiring tickets causing
the problem but then it looked more like it could be
duplicate/bad SPNs. We'd clean out AD and then find
duplicate SPNs errors after removing all dupes. Don't know
what else to tell you - I haven't seen anyone who has a
ticket open post a solution. And the place I was at never
got a resolution to the problem either.
-Sue
On Wed, 30 Aug 2006 14:50:03 -0700, DallasBlue
<DallasBlue@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>do have a ticket open with microsoft for more than a month now, but no
>resolution yet. "Troubleshooting Kerberos delation" is nearly a 90 page doc
.
>tried a lot of things nothing seems to fix it...
>"Sue Hoegemeier" wrote:
>|||Things started working with the double hop after forcing the kerberos to use
TCP instead of UDP as in the article 244474 at
support.microsoft.com/kb/244474/
"Sue Hoegemeier" wrote:

> Yup...a client site I was at had the issue and an open case
> with PSS. At first I thought it was expiring tickets causing
> the problem but then it looked more like it could be
> duplicate/bad SPNs. We'd clean out AD and then find
> duplicate SPNs errors after removing all dupes. Don't know
> what else to tell you - I haven't seen anyone who has a
> ticket open post a solution. And the place I was at never
> got a resolution to the problem either.
> -Sue
> On Wed, 30 Aug 2006 14:50:03 -0700, DallasBlue
> <DallasBlue@.discussions.microsoft.com> wrote:
>
>

Delegation stops working after a while

I have a linked server set up between two servers (A & B).
Server A is running 2005 and Server B is running 2000.
Both servers SQL services are running using a domain user account and have
their SPN's registered in the AD.
The client connects to Server A using integrated security (TCP/IP and
Kerberos not NTLM) and runs disributed queries using the linked server to
server B.
Delegation is set up in the AD and is working, at least for some time.
After a while (sometimes a couple of minutes, sometimes a couple of hours)
the delegation seems to stop working and the client recieves the error
"Login
failed for user '(null)'. Reason: Not associated with a trusted SQL Server
connection." The client is still connected and authenticated using TCP/IP
and
Kerberos.
After a restart of SQL Server on server A the delegation starts working
again.
I cannt find anything in the eventlogs on either one of the servers or the
client, and nothing in the sql server logs.
Does anybody have any idea of what could be wrong, or give me a though on
where to start looking.
Thanks
/MattiasWe are encountering this problem also. I have contacted MS but they are
still gathering information.
There is at least one other unanswered post on this here as well, see
subject = "SQL2005 Linked server authentication drops".
Are you running sql server under under a domain account that is not in the
local admins group by any chance?
--
-b
"Mattias" wrote:
> I have a linked server set up between two servers (A & B).
> Server A is running 2005 and Server B is running 2000.
> Both servers SQL services are running using a domain user account and have
> their SPN's registered in the AD.
> The client connects to Server A using integrated security (TCP/IP and
> Kerberos not NTLM) and runs disributed queries using the linked server to
> server B.
> Delegation is set up in the AD and is working, at least for some time.
> After a while (sometimes a couple of minutes, sometimes a couple of hours)
> the delegation seems to stop working and the client recieves the error
> "Login
> failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection." The client is still connected and authenticated using TCP/IP
> and
> Kerberos.
> After a restart of SQL Server on server A the delegation starts working
> again.
> I cannt find anything in the eventlogs on either one of the servers or the
> client, and nothing in the sql server logs.
> Does anybody have any idea of what could be wrong, or give me a though on
> where to start looking.
> Thanks
> /Mattias
>
>|||I am the other unanswered posting!
It fails intermittently when we run under 'sa' or a domain account that is
in the local admin group.
I have raised this through the Microsoft concierge service and they said
there are others with the same problem but no resolutions as yet!
Wendy
"BBogart" wrote:
> We are encountering this problem also. I have contacted MS but they are
> still gathering information.
> There is at least one other unanswered post on this here as well, see
> subject = "SQL2005 Linked server authentication drops".
> Are you running sql server under under a domain account that is not in the
> local admins group by any chance?
> --
> -b
>
> "Mattias" wrote:
> > I have a linked server set up between two servers (A & B).
> > Server A is running 2005 and Server B is running 2000.
> > Both servers SQL services are running using a domain user account and have
> > their SPN's registered in the AD.
> >
> > The client connects to Server A using integrated security (TCP/IP and
> > Kerberos not NTLM) and runs disributed queries using the linked server to
> > server B.
> > Delegation is set up in the AD and is working, at least for some time.
> >
> > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > the delegation seems to stop working and the client recieves the error
> > "Login
> > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > connection." The client is still connected and authenticated using TCP/IP
> > and
> > Kerberos.
> >
> > After a restart of SQL Server on server A the delegation starts working
> > again.
> >
> > I cannt find anything in the eventlogs on either one of the servers or the
> > client, and nothing in the sql server logs.
> >
> > Does anybody have any idea of what could be wrong, or give me a though on
> > where to start looking.
> >
> > Thanks
> > /Mattias
> >
> >
> >|||We have seen this in our environment too. One moment a linked server query
will work just fine with delegated Windows credentials, the next moment you
receive errors like the following:
OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I am still working on a reproducable way of generating the message, but I
seem to have problems a lot when I initially generate a linked server within
SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
I suspect that something in the Kerberos token is expiring. After I logout
and login, I can usually start a new session that works (just not today).
"Woo" wrote:
> I am the other unanswered posting!
> It fails intermittently when we run under 'sa' or a domain account that is
> in the local admin group.
> I have raised this through the Microsoft concierge service and they said
> there are others with the same problem but no resolutions as yet!
> Wendy
>
>
> "BBogart" wrote:
> > We are encountering this problem also. I have contacted MS but they are
> > still gathering information.
> >
> > There is at least one other unanswered post on this here as well, see
> > subject = "SQL2005 Linked server authentication drops".
> >
> > Are you running sql server under under a domain account that is not in the
> > local admins group by any chance?
> > --
> > -b
> >
> >
> > "Mattias" wrote:
> >
> > > I have a linked server set up between two servers (A & B).
> > > Server A is running 2005 and Server B is running 2000.
> > > Both servers SQL services are running using a domain user account and have
> > > their SPN's registered in the AD.
> > >
> > > The client connects to Server A using integrated security (TCP/IP and
> > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > server B.
> > > Delegation is set up in the AD and is working, at least for some time.
> > >
> > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > the delegation seems to stop working and the client recieves the error
> > > "Login
> > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > connection." The client is still connected and authenticated using TCP/IP
> > > and
> > > Kerberos.
> > >
> > > After a restart of SQL Server on server A the delegation starts working
> > > again.
> > >
> > > I cannt find anything in the eventlogs on either one of the servers or the
> > > client, and nothing in the sql server logs.
> > >
> > > Does anybody have any idea of what could be wrong, or give me a though on
> > > where to start looking.
> > >
> > > Thanks
> > > /Mattias
> > >
> > >
> > >|||I also suspect a ticket is expiring.
I am still working with MS on this with no resolution yet.
Once we see a failure, failures continue regardless of logging off and back
on until sql server is restarted. A reboot is not necessary as I previously
thought.
We have seen it take as little as a few hours or up to a week or more for
the failures to start again.
--
-b
"JD Qixcle" wrote:
> We have seen this in our environment too. One moment a linked server query
> will work just fine with delegated Windows credentials, the next moment you
> receive errors like the following:
> OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I am still working on a reproducable way of generating the message, but I
> seem to have problems a lot when I initially generate a linked server within
> SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
> I suspect that something in the Kerberos token is expiring. After I logout
> and login, I can usually start a new session that works (just not today).
>
>
> "Woo" wrote:
> > I am the other unanswered posting!
> >
> > It fails intermittently when we run under 'sa' or a domain account that is
> > in the local admin group.
> >
> > I have raised this through the Microsoft concierge service and they said
> > there are others with the same problem but no resolutions as yet!
> >
> > Wendy
> >
> >
> >
> >
> > "BBogart" wrote:
> >
> > > We are encountering this problem also. I have contacted MS but they are
> > > still gathering information.
> > >
> > > There is at least one other unanswered post on this here as well, see
> > > subject = "SQL2005 Linked server authentication drops".
> > >
> > > Are you running sql server under under a domain account that is not in the
> > > local admins group by any chance?
> > > --
> > > -b
> > >
> > >
> > > "Mattias" wrote:
> > >
> > > > I have a linked server set up between two servers (A & B).
> > > > Server A is running 2005 and Server B is running 2000.
> > > > Both servers SQL services are running using a domain user account and have
> > > > their SPN's registered in the AD.
> > > >
> > > > The client connects to Server A using integrated security (TCP/IP and
> > > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > > server B.
> > > > Delegation is set up in the AD and is working, at least for some time.
> > > >
> > > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > > the delegation seems to stop working and the client recieves the error
> > > > "Login
> > > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > > connection." The client is still connected and authenticated using TCP/IP
> > > > and
> > > > Kerberos.
> > > >
> > > > After a restart of SQL Server on server A the delegation starts working
> > > > again.
> > > >
> > > > I cannt find anything in the eventlogs on either one of the servers or the
> > > > client, and nothing in the sql server logs.
> > > >
> > > > Does anybody have any idea of what could be wrong, or give me a though on
> > > > where to start looking.
> > > >
> > > > Thanks
> > > > /Mattias
> > > >
> > > >
> > > >|||Did you have this issue resolved ?
Any hints / links /pointers/ thots will be appreciated
Thanks,
GA
"BBogart" wrote:
> I also suspect a ticket is expiring.
> I am still working with MS on this with no resolution yet.
> Once we see a failure, failures continue regardless of logging off and back
> on until sql server is restarted. A reboot is not necessary as I previously
> thought.
> We have seen it take as little as a few hours or up to a week or more for
> the failures to start again.
> --
> -b
> "JD Qixcle" wrote:
> > We have seen this in our environment too. One moment a linked server query
> > will work just fine with delegated Windows credentials, the next moment you
> > receive errors like the following:
> >
> > OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> > "Communication link failure".
> > Msg 10054, Level 16, State 1, Line 0
> > TCP Provider: An existing connection was forcibly closed by the remote host.
> > Msg 18452, Level 14, State 1, Line 0
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I am still working on a reproducable way of generating the message, but I
> > seem to have problems a lot when I initially generate a linked server within
> > SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
> > I suspect that something in the Kerberos token is expiring. After I logout
> > and login, I can usually start a new session that works (just not today).
> >
> >
> >
> >
> > "Woo" wrote:
> >
> > > I am the other unanswered posting!
> > >
> > > It fails intermittently when we run under 'sa' or a domain account that is
> > > in the local admin group.
> > >
> > > I have raised this through the Microsoft concierge service and they said
> > > there are others with the same problem but no resolutions as yet!
> > >
> > > Wendy
> > >
> > >
> > >
> > >
> > > "BBogart" wrote:
> > >
> > > > We are encountering this problem also. I have contacted MS but they are
> > > > still gathering information.
> > > >
> > > > There is at least one other unanswered post on this here as well, see
> > > > subject = "SQL2005 Linked server authentication drops".
> > > >
> > > > Are you running sql server under under a domain account that is not in the
> > > > local admins group by any chance?
> > > > --
> > > > -b
> > > >
> > > >
> > > > "Mattias" wrote:
> > > >
> > > > > I have a linked server set up between two servers (A & B).
> > > > > Server A is running 2005 and Server B is running 2000.
> > > > > Both servers SQL services are running using a domain user account and have
> > > > > their SPN's registered in the AD.
> > > > >
> > > > > The client connects to Server A using integrated security (TCP/IP and
> > > > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > > > server B.
> > > > > Delegation is set up in the AD and is working, at least for some time.
> > > > >
> > > > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > > > the delegation seems to stop working and the client recieves the error
> > > > > "Login
> > > > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > > > connection." The client is still connected and authenticated using TCP/IP
> > > > > and
> > > > > Kerberos.
> > > > >
> > > > > After a restart of SQL Server on server A the delegation starts working
> > > > > again.
> > > > >
> > > > > I cannt find anything in the eventlogs on either one of the servers or the
> > > > > client, and nothing in the sql server logs.
> > > > >
> > > > > Does anybody have any idea of what could be wrong, or give me a though on
> > > > > where to start looking.
> > > > >
> > > > > Thanks
> > > > > /Mattias
> > > > >
> > > > >
> > > > >|||--
-b
"DallasBlue" wrote:
> Did you have this issue resolved ?
> Any hints / links /pointers/ thots will be appreciated
> Thanks,
> GA
> "BBogart" wrote:
> > I also suspect a ticket is expiring.
> >
> > I am still working with MS on this with no resolution yet.
> >
> > Once we see a failure, failures continue regardless of logging off and back
> > on until sql server is restarted. A reboot is not necessary as I previously
> > thought.
> >
> > We have seen it take as little as a few hours or up to a week or more for
> > the failures to start again.
> > --
> > -b
> >
> > "JD Qixcle" wrote:
> >
> > > We have seen this in our environment too. One moment a linked server query
> > > will work just fine with delegated Windows credentials, the next moment you
> > > receive errors like the following:
> > >
> > > OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> > > "Communication link failure".
> > > Msg 10054, Level 16, State 1, Line 0
> > > TCP Provider: An existing connection was forcibly closed by the remote host.
> > > Msg 18452, Level 14, State 1, Line 0
> > > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > > Server connection.
> > >
> > > I am still working on a reproducable way of generating the message, but I
> > > seem to have problems a lot when I initially generate a linked server within
> > > SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
> > > I suspect that something in the Kerberos token is expiring. After I logout
> > > and login, I can usually start a new session that works (just not today).
> > >
> > >
> > >
> > >
> > > "Woo" wrote:
> > >
> > > > I am the other unanswered posting!
> > > >
> > > > It fails intermittently when we run under 'sa' or a domain account that is
> > > > in the local admin group.
> > > >
> > > > I have raised this through the Microsoft concierge service and they said
> > > > there are others with the same problem but no resolutions as yet!
> > > >
> > > > Wendy
> > > >
> > > >
> > > >
> > > >
> > > > "BBogart" wrote:
> > > >
> > > > > We are encountering this problem also. I have contacted MS but they are
> > > > > still gathering information.
> > > > >
> > > > > There is at least one other unanswered post on this here as well, see
> > > > > subject = "SQL2005 Linked server authentication drops".
> > > > >
> > > > > Are you running sql server under under a domain account that is not in the
> > > > > local admins group by any chance?
> > > > > --
> > > > > -b
> > > > >
> > > > >
> > > > > "Mattias" wrote:
> > > > >
> > > > > > I have a linked server set up between two servers (A & B).
> > > > > > Server A is running 2005 and Server B is running 2000.
> > > > > > Both servers SQL services are running using a domain user account and have
> > > > > > their SPN's registered in the AD.
> > > > > >
> > > > > > The client connects to Server A using integrated security (TCP/IP and
> > > > > > Kerberos not NTLM) and runs disributed queries using the linked server to
> > > > > > server B.
> > > > > > Delegation is set up in the AD and is working, at least for some time.
> > > > > >
> > > > > > After a while (sometimes a couple of minutes, sometimes a couple of hours)
> > > > > > the delegation seems to stop working and the client recieves the error
> > > > > > "Login
> > > > > > failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> > > > > > connection." The client is still connected and authenticated using TCP/IP
> > > > > > and
> > > > > > Kerberos.
> > > > > >
> > > > > > After a restart of SQL Server on server A the delegation starts working
> > > > > > again.
> > > > > >
> > > > > > I cannt find anything in the eventlogs on either one of the servers or the
> > > > > > client, and nothing in the sql server logs.
> > > > > >
> > > > > > Does anybody have any idea of what could be wrong, or give me a though on
> > > > > > where to start looking.
> > > > > >
> > > > > > Thanks
> > > > > > /Mattias
> > > > > >
> > > > > >
> > > > > >

Delegation stops working after a while

I have a linked server set up between two servers (A & B).
Server A is running 2005 and Server B is running 2000.
Both servers SQL services are running using a domain user account and have
their SPN's registered in the AD.
The client connects to Server A using integrated security (TCP/IP and
Kerberos not NTLM) and runs disributed queries using the linked server to
server B.
Delegation is set up in the AD and is working, at least for some time.
After a while (sometimes a couple of minutes, sometimes a couple of hours)
the delegation seems to stop working and the client recieves the error
"Login
failed for user '(null)'. Reason: Not associated with a trusted SQL Server
connection." The client is still connected and authenticated using TCP/IP
and
Kerberos.
After a restart of SQL Server on server A the delegation starts working
again.
I cannt find anything in the eventlogs on either one of the servers or the
client, and nothing in the sql server logs.
Does anybody have any idea of what could be wrong, or give me a though on
where to start looking.
Thanks
/MattiasWe are encountering this problem also. I have contacted MS but they are
still gathering information.
There is at least one other unanswered post on this here as well, see
subject = "SQL2005 Linked server authentication drops".
Are you running sql server under under a domain account that is not in the
local admins group by any chance?
--
-b
"Mattias" wrote:

> I have a linked server set up between two servers (A & B).
> Server A is running 2005 and Server B is running 2000.
> Both servers SQL services are running using a domain user account and have
> their SPN's registered in the AD.
> The client connects to Server A using integrated security (TCP/IP and
> Kerberos not NTLM) and runs disributed queries using the linked server to
> server B.
> Delegation is set up in the AD and is working, at least for some time.
> After a while (sometimes a couple of minutes, sometimes a couple of hours)
> the delegation seems to stop working and the client recieves the error
> "Login
> failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection." The client is still connected and authenticated using TCP/IP
> and
> Kerberos.
> After a restart of SQL Server on server A the delegation starts working
> again.
> I cannt find anything in the eventlogs on either one of the servers or the
> client, and nothing in the sql server logs.
> Does anybody have any idea of what could be wrong, or give me a though on
> where to start looking.
> Thanks
> /Mattias
>
>|||I am the other unanswered posting!
It fails intermittently when we run under 'sa' or a domain account that is
in the local admin group.
I have raised this through the Microsoft concierge service and they said
there are others with the same problem but no resolutions as yet!
Wendy
"BBogart" wrote:
[vbcol=seagreen]
> We are encountering this problem also. I have contacted MS but they are
> still gathering information.
> There is at least one other unanswered post on this here as well, see
> subject = "SQL2005 Linked server authentication drops".
> Are you running sql server under under a domain account that is not in the
> local admins group by any chance?
> --
> -b
>
> "Mattias" wrote:
>|||We have seen this in our environment too. One moment a linked server query
will work just fine with delegated Windows credentials, the next moment you
receive errors like the following:
OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I am still working on a reproducable way of generating the message, but I
seem to have problems a lot when I initially generate a linked server within
SQL Management Studio 2005. My connections rarely last beyond 24 hours, and
I suspect that something in the Kerberos token is expiring. After I logout
and login, I can usually start a new session that works (just not today).
"Woo" wrote:
[vbcol=seagreen]
> I am the other unanswered posting!
> It fails intermittently when we run under 'sa' or a domain account that is
> in the local admin group.
> I have raised this through the Microsoft concierge service and they said
> there are others with the same problem but no resolutions as yet!
> Wendy
>
>
> "BBogart" wrote:
>|||I also suspect a ticket is expiring.
I am still working with MS on this with no resolution yet.
Once we see a failure, failures continue regardless of logging off and back
on until sql server is restarted. A reboot is not necessary as I previously
thought.
We have seen it take as little as a few hours or up to a week or more for
the failures to start again.
--
-b
"JD Qixcle" wrote:
[vbcol=seagreen]
> We have seen this in our environment too. One moment a linked server quer
y
> will work just fine with delegated Windows credentials, the next moment yo
u
> receive errors like the following:
> OLE DB provider "SQLNCLI" for linked server "THESERVER" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote hos
t.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I am still working on a reproducable way of generating the message, but I
> seem to have problems a lot when I initially generate a linked server with
in
> SQL Management Studio 2005. My connections rarely last beyond 24 hours, a
nd
> I suspect that something in the Kerberos token is expiring. After I logou
t
> and login, I can usually start a new session that works (just not today).
>
>
> "Woo" wrote:
>|||Did you have this issue resolved ?
Any hints / links /pointers/ thots will be appreciated
Thanks,
GA
"BBogart" wrote:
[vbcol=seagreen]
> I also suspect a ticket is expiring.
> I am still working with MS on this with no resolution yet.
> Once we see a failure, failures continue regardless of logging off and bac
k
> on until sql server is restarted. A reboot is not necessary as I previous
ly
> thought.
> We have seen it take as little as a few hours or up to a week or more for
> the failures to start again.
> --
> -b
> "JD Qixcle" wrote:
>|||-b
"DallasBlue" wrote:
[vbcol=seagreen]
> Did you have this issue resolved ?
> Any hints / links /pointers/ thots will be appreciated
> Thanks,
> GA
> "BBogart" wrote:
>

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
QuentinThe parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or even
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> > Hi group,
> >
> > I am seeing degraded performance after applying SP4 on SQL 2000.
> >
> > My specific queries (they are in stored proc) access linked server. When
> > the process is run, I keep seeing blocks on the linked server side among
> > processes of the same spid spun off from the calling process like this:
> >
> > A. a process (say spid 58) on initiating server runs, some times shows
> > blocked by a process (say spid 111) on the linked server, some times not
> > displaying the block but just continue to run slow;
> >
> > B. a few processes (spid 111) on the linked server running, often showing
> > one or two of them blocked by another spid 111.
> >
> > I have googled and found that there are reports of such slowed down
> > performance after SP4 application, not necessarily related to the use of
> > linked server. There is an explanation by Microsoft why processes could
> > show being blocked by process of the same spid. Problem is that it looks
> > like no solution to the problem and nor explanation why the switch of
> > locks is so slow.
> > From what I read other people report, this blocking among processes of the
> > same spid repeats itself so often and is so slow that you can easily catch
> > the display of
> > such blocks in EM. This is the same case by me: when the process is
> > running, I
> > easily see such blocks with refresh of EM.
> >
> > My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> > only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> > checked on my systems and is set up properly.
> >
> > Wish to hear from you.
> >
> > Quentin
> >
> >
> >|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegroups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>> The parallel process blocking occurred long before you applied SP4.
>> Microsoft added visibility to that blocking in SP4 so DBAs could manage
>> parallelism better. Reduce the degree of parallelism on your query, or
>> even
>> server-wide, and you will see the self-blocking go away.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
>> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
>> > Hi group,
>> >
>> > I am seeing degraded performance after applying SP4 on SQL 2000.
>> >
>> > My specific queries (they are in stored proc) access linked server.
>> > When
>> > the process is run, I keep seeing blocks on the linked server side
>> > among
>> > processes of the same spid spun off from the calling process like this:
>> >
>> > A. a process (say spid 58) on initiating server runs, some times shows
>> > blocked by a process (say spid 111) on the linked server, some times
>> > not
>> > displaying the block but just continue to run slow;
>> >
>> > B. a few processes (spid 111) on the linked server running, often
>> > showing
>> > one or two of them blocked by another spid 111.
>> >
>> > I have googled and found that there are reports of such slowed down
>> > performance after SP4 application, not necessarily related to the use
>> > of
>> > linked server. There is an explanation by Microsoft why processes
>> > could
>> > show being blocked by process of the same spid. Problem is that it
>> > looks
>> > like no solution to the problem and nor explanation why the switch of
>> > locks is so slow.
>> > From what I read other people report, this blocking among processes of
>> > the
>> > same spid repeats itself so often and is so slow that you can easily
>> > catch
>> > the display of
>> > such blocks in EM. This is the same case by me: when the process is
>> > running, I
>> > easily see such blocks with refresh of EM.
>> >
>> > My process used to run about 30 sec on SP3a, now it runs some 15 min.
>> > The
>> > only troubleshoot hint I saw sofar is the correct setup of MS DTC,
>> > which I
>> > checked on my systems and is set up properly.
>> >
>> > Wish to hear from you.
>> >
>> > Quentin
>> >
>> >
>> >
>|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.org

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
Quentin
The parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>
|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic...94&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:[vbcol=seagreen]
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or even
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegr oups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic...94&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>
|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.
|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/archi...02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.org

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
QuentinThe parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic...294&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:[vbcol=seagreen]
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or ev
en
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegroups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic...294&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.orgsql

Defining linked server in a failover cluster

Hi,
Are there any issues to consider when defining a linked
server for a virtual SQL server pointing to another
virtual SQL server in a failover cluster?
Many thanks,
OskarNo. As far as sql concerns, a valid linked server can be either virtual or
physical.
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:6bdf01c48362$8b493180$a601280a@.phx.gbl...
> Hi,
> Are there any issues to consider when defining a linked
> server for a virtual SQL server pointing to another
> virtual SQL server in a failover cluster?
> --
> Many thanks,
> Oskar
>

Defining linked server in a failover cluster

Hi,
Are there any issues to consider when defining a linked
server for a virtual SQL server pointing to another
virtual SQL server in a failover cluster?
Many thanks,
Oskar
No. As far as sql concerns, a valid linked server can be either virtual or
physical.
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:6bdf01c48362$8b493180$a601280a@.phx.gbl...
> Hi,
> Are there any issues to consider when defining a linked
> server for a virtual SQL server pointing to another
> virtual SQL server in a failover cluster?
> --
> Many thanks,
> Oskar
>

Defining linked server in a failover cluster

Hi,
Are there any issues to consider when defining a linked
server for a virtual SQL server pointing to another
virtual SQL server in a failover cluster?
--
Many thanks,
OskarNo. As far as sql concerns, a valid linked server can be either virtual or
physical.
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:6bdf01c48362$8b493180$a601280a@.phx.gbl...
> Hi,
> Are there any issues to consider when defining a linked
> server for a virtual SQL server pointing to another
> virtual SQL server in a failover cluster?
> --
> Many thanks,
> Oskar
>

Defining AS400 Linked Server

Can anyone help me understand what it takes to define a Linked Server
connection to an IBM eSeries (AS400)? Do I need Microsoft's SNA Server
or some other product or can I simply do it with the tools provided with
SQL Server 2000?

Any guidance would be very appreciated. Thanks.

FaridHi Farid,

I managed this installing the AS400 Client Access for windows components
on the database-server.
At least you need the as400 odbc-database-driver on the windows-server.
Create a (system) odbc connection to your as400 and then create a linked
server to this odbc connection.
you maybe have to tweak / play around with the odbc-options for the
as400-connection to make it work.

just to point out the direction...

Tobias

fn wrote:

> Can anyone help me understand what it takes to define a Linked Server
> connection to an IBM eSeries (AS400)? Do I need Microsoft's SNA Server
> or some other product or can I simply do it with the tools provided with
> SQL Server 2000?
> Any guidance would be very appreciated. Thanks.
> Farid|||fn <f_n_a_c_e_r_removeunderlines@.hotmail.com> wrote in message news:<56-dnf8HPfeXyJrcRVn-qw@.britsys.net>...
> Can anyone help me understand what it takes to define a Linked Server
> connection to an IBM eSeries (AS400)? Do I need Microsoft's SNA Server
> or some other product or can I simply do it with the tools provided with
> SQL Server 2000?
> Any guidance would be very appreciated. Thanks.
> Farid

This discussion may help:

http://groups.google.com/groups?hl=...%40cpmsftngxa07

Simon|||I found IBM's Client Access drivers to be error prone and slow.

Although not-free, Hit Software, www.hitsw.com, provides OLEDB and
ODBC drivers for accessing DB2/AS400 servers. I used the OLEDB driver
with great success.

ffoiii

fn <f_n_a_c_e_r_removeunderlines@.hotmail.com> wrote in message news:<56-dnf8HPfeXyJrcRVn-qw@.britsys.net>...
> Can anyone help me understand what it takes to define a Linked Server
> connection to an IBM eSeries (AS400)? Do I need Microsoft's SNA Server
> or some other product or can I simply do it with the tools provided with
> SQL Server 2000?
> Any guidance would be very appreciated. Thanks.
> Farid