Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tfertil
Starting Member
4 Posts |
 Posted - 2005-12-06 : 18:14:37
|
| Hi!We need to execute code inside a distributed transaction like this:begin transactiondrop table testSELECT * into testFROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=myserver;UID=sa;PWD=xxxxxx',mydatabase.dbo.mytable)commit transactionThis code actually WORKS in our site.But the problem is that "mydatabase" and "mytable" must be dynamic, so we try to execute the code from a string, like this:begin transactionDECLARE @SqlStr NVARCHAR(1000)drop table testSET @SqlStr='SELECT * into test FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL Server};SERVER=myserver;UID=sa;PWD=xxxxx'',mydatabase.dbo.mytable)'EXEC sp_executesql @SqlStrcommit transactionThe SELECT is exactly the same, but inside the sp_executesql it doesn't work!We also try using EXEC (@SqlStr) with the same results..We're using the following configuration:LOCAL server: Windows XP Pro SP2 - SQL Server 2000 Enterprise SP4REMOTE server: Windows Server 2003 Std SP 1 - SQL Server 2000 Enterprise SP 4.We know MSDTC and firewall issues are not causing the problem, because the plain SELECT works, it's only when we execute the dynamic SQL that it fails...Thanks for any help or suggestion... |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-06 : 19:12:05
|
| If you PRINT @SqlStr can the actual displayed SQL String be executed manually? Perhaps some obscure syntax error is creeping in, or you are exceeding the declared size of @SqlStr ??Kristen |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-12-06 : 19:21:54
|
| The problem is that code inside sp_executesql is isolated from all other code.Embed the entire script (including transaction commands) inside the @SqlStr variable..DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
tfertil
Starting Member
4 Posts |
Posted - 2005-12-06 : 21:48:59
|
Thanks, Kristen... The real string is longer, but no, it doesn't exceed the declared size... and to debug we're trying with exactly the sentences in the message... finally: yes, the displayed SQL string (using PRINT) can be execute...quote:
Originally posted by Kristen
If you PRINT @SqlStr can the actual displayed SQL String be executed manually? Perhaps some obscure syntax error is creeping in, or you are exceeding the declared size of @SqlStr ??Kristen
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-07 : 03:16:46
|
| Did you try what David suggested, that's far more likely to be the problem than my suggestion!Kristen |
|
|
|
tfertil
Starting Member
4 Posts |
Posted - 2005-12-07 : 07:37:44
|
You're right... and wrong!We tested with a brand new server, Win 2003 Server, SQL 2000 STD, and we discovered this:BEFORE applying SQL SP4, the SQL works only when not executed inside the EXEC (@SqlStr). AFTER SP4, it works! But now we don't know if other settings affect the result: We're gonna install again Win 2003 Server and SQL 2000 STD + SP4 to get a default installation, and then try again to see what configuracion changes are neccesary (Firewall, MSDTC configuration, etc.).Thanks for your reply, we'll be posting any additional information, if any!quote:
Originally posted by byrmol
The problem is that code inside sp_executesql is isolated from all other code.Embed the entire script (including transaction commands) inside the @SqlStr variable..DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end.
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-07 : 09:24:05
|
| "BEFORE applying SQL SP4"Would that have been SP3, or something earlier still?Kristen |
|
|
|
|
|
|
|
|
distributed transaction inside sp_executeSQL