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 2005 Forums
 Transact-SQL (2005)
 linked server doesn't suport transaction interface

Author  Topic 

ranger
Starting Member

13 Posts

Posted - 2007-08-16 : 04:38:25
Hi,
I'm using SQL Server 2005 Workgroup Edition. There is 3rd party database that I need to insert data into from my db (running under the same instance). The database, linked server, oledb, etc was all created using their setup program (in other words, I don't have much knowledge of it).
What I'm trying to do is, insert some data into one of their tables from a trigger in my database via the linked server just using normal insert syntax and the 4-part naming convention...
INSERT NSQ.AppDB.dbo.Content (col1, col2) values (val1, val2)


This works fine if I execute the SQL from SQL Management Studio sql editor or from a stored procedure. But if this is used in a trigger or a stored proc that is called by a trigger, then I get the following error.

The requested operation could not be performed because OLE DB provider "NSQ" for linked server "NSQ" does not support the required transaction interface.


I've tried setting "Non Transacted Updates" for the OLEDB provider, but it had no effect. I'm guess this has something to do with the fact that maybe triggers run under some special transaction context which this doesn't like. Is there anything I can do to get this working in a trigger just like it does elsewhere?? It's killing me!

Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-16 : 06:17:54
do you have DTC (Distributed Transaction Coordinator) enabled?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

ranger
Starting Member

13 Posts

Posted - 2007-08-16 : 06:49:18
Hi spirit1,
Thanks for you reply. I see a Windows service called "Distributed Transaction Coordinator" and it is started, if that's what you mean.
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-16 : 22:43:30
Is nsq a sql server? Is dtc running on both servers? Did you enable network access for dtc?
Go to Top of Page

ranger
Starting Member

13 Posts

Posted - 2007-08-16 : 23:56:32
Hi rmiao,
NSQ is a database.... Everything is running on the same server under the same MSSQL instance. So we're simply talking about 2 databases under the same instance.
Thanks for the response.. hopefully that answered your questions.
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-16 : 23:58:14
Why use linked server if them are in same instance?
Go to Top of Page

ranger
Starting Member

13 Posts

Posted - 2007-08-17 : 00:41:30
That's a good question. I was wondering the same thing. My knowledge is pretty limited, so sorry if I'm not able to talk more intelligently on the subject...
So first, let me say that I'm following their instructions on how to do these things (obviously, doing it from a trigger seems to be an exception for some reason).
But I think the reason for using their OLE DB provider via linked server is because it's not just a normal db, they've added all kinds of extensions with special functionality. It's not your average, run of the mill db. Does that make sense? I don't know, just a guess.
Anyway, I was thinking the same thing as you before and so I thought that simply removing the NSQ linked server name from the table path, would bypass that linked server problem. So I just gave it "insert into dbname.dbo.tablename....".... and I still get the exact same error .... again, only when using it in a trigger.
But I'm pretty convinced that they require us to go through their OLE DB provider because they've added some extended layers behind the scenes.

I know I can't expect anyone here to try and figure out somebody else's proprietary drivers without much more info. But I guess I was just hoping that somebody would be able to tell me what is different about running an insert command from console vs running inside the trigger, and then work backwards from there to figure out a workaround.

Thanks again for you reply.

Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-17 : 23:36:38
Tried with openquery instead of four part name?
Go to Top of Page

ranger
Starting Member

13 Posts

Posted - 2007-08-19 : 23:33:00
Actually yeah, I have tried the Insert statement with OpenQuery. It just tells me that the operation is "not supported". Again, this special OleDB provider getting in the way.
I'm thinking that my only option is for the trigger to kick of an asynchronous process that does the inserts. I've just now started looking into this and a quick Google on it makes it look a lot more complicated than I would've expected.... any tips on that would be appreciated... but until then, I'm going to start digging into it and see what I come up with.

Thanks again.
Go to Top of Page
   

- Advertisement -