Dynamic openquery + no DTS support = trouble

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic openquery + no DTS support = trouble

Author

Topic

Lavos
Posting Yak Master

200 Posts

Posted - 2002-02-28 : 21:26:11

I've finally gotten a fun project to implement at work. One of my primary concerns is making sure the system is maintainable for the next guy. (Especially since at $10/hour I'm going to be looking for work as soon as I get a resume I'm comfortable with.)

We're trying to do away with the large scale use of access databases (especially since the shared mde's tend to corrupt on a weekly basis.) and we're going to move to a sql server backend with a VB program front end. We currently have a massive planning/production/erp program that runs on a progress database (and it works pretty well unless the database file hits the magic 2 gig limit.) and we're using sql server to provide additional report capabilities that noone here can do in the progress db's native language. There's no native oledb support so we have to use the oledb provider for odbc (and we have to use 3'rd party ready only odbc drivers, which is fine since we don't have to alter any data on the progress database.)

Unfortunately, I've got to have some variables in the distributed queries and OPENQUERY doesn't support that. (unfortunately, I can't use 4 part names either.) I was fine with that, I just created the whole statement and EXEC()'d it. (Actually, I worked on it, got frustrated about not being able to get it right, went home, searched through the forums until I found a hint from robvolk about using print, then went back and got all my ''''' + @Variable + ''''' 's in the right places.)

At first I was happy with the massive speed increases, but now I have to go a step farther and start joining in more tables. Unfortunately, this means I'm going to have to store the values in a temp table for further processing since an exec() can't be embedded in a select statement.

Here's my problem, the insert into a temp table fails because it can't enlist the linked server into a DTC transaction. Even though I'm just doing a SELECT from the linked server. Everything I've read would suggest this should work, but it doesn't. I haven't been able to find an option or setting to ignore enlisting distributed transactions, so I thought of a different workaround. Write the entire frickin procedure in a string, and then exec() the whole thing.

Unfortunately, that's going to be a nightmare to maintain (and since I'm not planning on being around I can't exactly answer questions about why it has to be done that way.) but I can't think of another way to implement it. It's doubly frustrating since I tried a hard coded openquery that inserted into a temp table and it worked perfectly. (It's only when I include the EXEC() that the insert fails from not being able to enlist the server in a DTC transaction.)

This won't be too bad for most of the proc's I'm going to have to write, but there is one in particular that requires iteration that is going to be an absolute nightmare to do this way.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-01 : 07:11:49

I'm assuming that you're running this as a stored procedure. If so, do you get an error about ANSI_NULLS and ANSI_WARNINGS needing to be set?

If you're getting another error, let me know (paste your code and the actual error message in your reply). Since you're using OPENQUERY you basically have to be able to use distributed transactions. It seems that it's supported now. If it's just an ANSI_NULLS/ANSI WARNINGS problem, set these to ON, then create the procedure (don't include them in the procedure itself, they'll be ignored).

Lavos
Posting Yak Master

200 Posts

Posted - 2002-03-01 : 08:27:38

The problem is similar to using OPENQUERY inside a trigger. Since the database is in autocommit mode, the insert statement is being treated as a singular transaction.

This is a rough edit for space of what I'm doing.

USE TestDatabase
GO
DECLARE @SomeVariable varchar(8)
SET @SomeVariable = '040200'

CREATE TABLE #TempTable ( SomeValue int )

DECLARE @QueryString

-- The forum is stripping out my plusses, so this doesn't look right, but it is.
SET @QueryString = 'SELECT SomeValue FROM OPENQUERY(LINKED_SERVER,
''SELECT SomeValue FROM LinkedTable WHERE KeyValue = '''''
++ @SomeVariable ++ ''''')'

--This Works
INSERT INTO @TempTable
SELECT SomeValue FROM OPENQUERY(LINKED_SERVER,
'SELECT SomeValue FROM LinkedTable WHERE KeyValue = ''040200''')

-- This fails from being unable to enlist the linked server into a distributed transaction
INSERT INTO #TempTable
EXEC(@QueryString)

DROP TABLE #TempTable
GO

I believe the error is because the server doesn't see that I'm just reading data, and so it tries to fire up DTC just in case. The solution for the trigger problem is to insert a COMMIT TRANS before the dynamic OPENQUERY call to end the transaction, but I can't do that here :(

It looks like the entire body is going to have to be one string that get's executed.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-01 : 08:40:03

How about this:

USE TestDatabase
GO
DECLARE @SomeVariable varchar(8)
SET @SomeVariable = '040200'

CREATE TABLE #TempTable ( SomeValue int )

DECLARE @QueryString

-- The forum is stripping out my plusses, so this doesn't look right, but it is.
SET @QueryString = 'INSERT INTO #TempTable SELECT SomeValue FROM OPENQUERY(LINKED_SERVER,
''SELECT SomeValue FROM LinkedTable WHERE KeyValue = '''''
+ @SomeVariable + ''''')'

EXEC(@QueryString)

DROP TABLE #TempTable
GO

I've tried something like this a few times and it worked fine, but I'm not sure it was due to the same problem you're having.

Lavos
Posting Yak Master

200 Posts

Posted - 2002-03-01 : 15:39:31

Ahh, thanks a lot. It's working now. I might still have to go back to using one huge dynamic query to avoid tempdb usage, but I'll cross that bridge when I come to it ;)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources