Linked server behavior not funny at all

Hi All
My procedure uses remote database and remote server name as parameters. Using these parameter the proc builds a dynamic sql which should insert records into a table on local server. This should be so simple but it isn't.
The process freezes when I am running it from query analyzer.
When I kill it, it remains in the killed/ rollback state indefinitely. You cannot stop sql server service now as spid 3 blocks on this process. So the only recourse is to kill sqlserver process from taskmanager or restart the server. horrid!!!
I can run the dynamic sql (being executed in the proc) in query analyzer with the login used for linking the servers.
What is happening here?
Thanks for reading and your help
Arun
PS
From my experience over the last 2-3 days stress testing linked servers, it appears that MS has spent minimal time testing linked server functionality. It works when I do select * from srv2.db2.dbo.tbl but anything more, it falls apart.
is it also your experience?
Hi,
Could you please paste the code of the stored proc? As I understand it, you
are trying to pass a remote server and remote db name to a stored proc, and
that will take the data located on that remote data source and insert it
into a local table on the local server?
How long have you waited for the unresponsive behaviour?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Hi Vikram
Thanks for your response. Since posting, I have changed the process to avoid the linked server by putting data locally (delivery pressure). So I don't have the code snippets ready at hand. I will create new ones and post them.
But this is basically what I was trying.
The insert takes less than no time when done locally. When I do it across linked servers, it wasn't done in 7 minutes and I killed it. There were no records in the destination table.
-- dynamic sql in proc
select * from srv2.db.dbo.tbl a where fld3 = (select max(fld3) from srv2.db.dbo.tbl where fld1 = a.fld1 and fld2 = a.fld2)
proc is fired from local server
insert tbl2 exec proc @.srv, @.db
Arun
"Vikram Jayaram [MS]" wrote:

> Hi,
> Could you please paste the code of the stored proc? As I understand it, you
> are trying to pass a remote server and remote db name to a stored proc, and
> that will take the data located on that remote data source and insert it
> into a local table on the local server?
> How long have you waited for the unresponsive behaviour?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>