August 3, 2009 at 1:18 pm
I have a stored procedure that is used by QA to verify certain data exists on all our servers. It executes a SQL statement that makes use of OPENQUERY() across linked servers. It then attempts to INSERT INTO #temp EXEC(@SQL)
pseudo code:
@sql = OPENQUERY() statement
INSERT INTO #temp EXEC(@SQL) --fails if this code is present
The code is run from a SQL 2000 server and database. No error message is present anywhere. The query simply hangs and causes serious blocking. However, this is only the case when it runs the OPENQUERY() against a SQL 2005 server that was built new (databases still in 80 compatibility). Running against SQL 2005 servers that were upgraded from SQL 2000 (attach/reattach) have no issues. To further complicate things running the stored procedure from a 2005 server against the servers that were failing results in no issues.
MSDTC is running, RPC and RPC Out are set to true on linked servers, select statements execute without issue if run directly or via openquery against the 2005 database. Things only fail when attempting to INSERT INTO a temp or permanent table.
Any ideas on what may have changed between 2000 and 2005?
I tried to revive another thread over on MSDN ( http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4c4d0f5b-d181-4bf2-acf5-ca1d430ee3e9 ) without any luck.
Thanks in advance to anyone that could shed some light on this.
August 3, 2009 at 1:27 pm
use a global temp table (insert into ##temp .. )
The probability of survival is inversely proportional to the angle of arrival.
August 4, 2009 at 7:48 am
I doubt that a global temporary table will make a difference as scope is not the problem. Why are you using dynamic SQL to execute the OPENQUERY statement? Have you tried eliminating the Dynamic SQL and just doing:
Insert Into #temp
Select
*
From
OPENQUERY()
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2009 at 8:34 am
As far as the dynamic SQL goes it was written by QA. I haven't been here long and was asked to look into the issue. It is an ugly piece of code that grabs server names, database names, and a few parameters and inserts these values into a #scripts table. The #scripts table is then passed through a cursor which pulls them out and executes them via an openquery which attempts to then write to the #results table on the server running the SP.
As a work around I'm having them execute the script from a 2005 QA server, which is working.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply