| 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 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
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. |
|
|
|
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? |
|
|
|
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. |
|
|
|
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? |
|
|
|
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. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-17 : 23:36:38
|
| Tried with openquery instead of four part name? |
|
|
|
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. |
|
|
|
|
linked server doesn't suport transaction interface