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 |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2010-06-24 : 05:01:28
|
| Hi,There is a problem with linked server.On the same server, we are creating the LinkedServers to point to some of the databases under the same instance.This setup is used since the prod and qa layers are different. Prod has different servers fro load balancing but QA has allunder the same server.Locally, i have setup the Linked servers and performing an INSERT operation using a SQL script.It is working fine. And observation is am getting the number of rows affected while am trying to perform any DML operations through LNKED SERVERS.But , the same thing i am trying to INSERT using linked server on QA layer, it is giving me an error syaing thatinsert into dcdbserver2.dcdbset2.dbo.test select 2 /*Server: Msg 7343, Level 16, State 2, Line 1OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[server1].[db1].[dbo].[test]'. Unknown provider error.[OLE/DB provider returned message: Cursor operation conflict]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::InsertRow returned 0x80040e23: Unknown provider error.].*/other observations on QA,1.Insert is the only one failing, but delete and update is working fine on QA.2. eventhough the DELETE and UPDATE are working but i am getting 0 rows affected. This setcount is making any difference over here.3. Also, i checked the DTC is also working or not. It is working fine.4. Also, the db user is having the db_owner on the respective catalog but don't know why it is gving that error.Can anybody figure out what the problem is ?? Any immediate fix for this.In my local all DML operations are working fine.Thanks in Advance |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-24 : 13:34:00
|
| Does INSERT using OPENQUERY work? Or is that not an option because it would require application changes? |
 |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-06-26 : 03:03:39
|
| Kristen,I figured out why it is happening. At server level/instance level , the NOCOUNT option is checked. That is the difference if found the difference between prod and other DEV layers.Now linked server is working for all the DML operations.And also, am getting number of rows affected. But my question, why NOCOUNT is stopping only INSERT operation and remaining other options are working fine.... Why is it so???? |
|
|
|
|
|
|
|
|
Linked Server issue