June 13, 2010 at 7:58 pm
Hello friends,
I use this query to insert data into a table in remote server using a linked server
and the source table is in my local server.
Here is the query:
insert openquery(MY_linkedserver,'select name, num, email, ssn' from remote_table)
select name,num,email,ssn from local_table.
This query used to run perfect previously.
But, this time it gave me an error.
ERROR:
OLE DB provider "MSDASQL" for linked server "MY_linkedserver" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "MY_linkedserver" could not INSERT INTO table "[MSDASQL]".
Can some one help me in decoding this error.
I have no clue of where to check it out.
Is this the problem with the query(I dont think because, it worked gud during my last executions)
or do you think some has changed in destination table properties.
June 14, 2010 at 5:33 am
It could be caused by number of different problems:
1. Check you can connect top the link server by running simple select query.
2. Check the security permissions.
3. Datatype casting. Quite often with numeric datatypes with different precisions. Check what the difference between source and destination datatypes, then check what sort of data is trying to be inserted.
June 14, 2010 at 7:20 am
Thanks for the reply Elutin,
1. Actually, I can run a simple select query on the linked server specified above.
2. I dont think permissions might be the problem because, I did these insertions before.
3. All the data types matches for both the tables and as I said, I made insertions before.
Or can anyone one recommend me an alternate way of doing this insertion through linked server?
June 14, 2010 at 7:51 am
What you mean by "It was working before"?
Do you insert exactly the same data all the time? Exactly the same values?
This probelm might happen due to the data itself.
Can you execute simple insert with just one row of data?
June 14, 2010 at 8:29 am
I mean to say that I used to insert data into the remote table using this procedure.
I also tried to insert just one row and it still gives me the same error.
June 14, 2010 at 8:38 am
Check security configuration of DTC.
If you are looking for alternative way to move data between two servers, try SSIS.
Just in case, could you provide the sample of data you are trying to insert and structure of the destination table (as create table).
June 14, 2010 at 12:29 pm
OPENROWSET should do the trick eg
INSERT OPENROWSET('SQLNCLI', 'SERVER=REMOTE_SERVER;TRUSTED_CONNECTION=YES', 'SELECT name, num, email, ssn from Remote_Database.dbo.remote_table')
SELECT name, num, email, ssn FROM local_table
June 14, 2010 at 12:56 pm
striker-baba (6/14/2010)
Or can anyone one recommend me an alternate way of doing this insertion through linked server?
A simple way, if you have the linked server established under Server Objects - Linked Servers is
[Code="sql"]
INSERT INTO MY_linkedserver.database_name.schema_name.remote_table (name, num, email, ssn)
SELECT name, num, email, ssn
FROM local_table
[/Code]
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 28, 2011 at 3:50 am
Hi all
I have a similar scenario, two servers and when data is inserted into 1 database table, it has to be inserted into table in other database,
Now, i have a linked server to another database and a trigger on the table. When insert, in the trigger, it starts a job which inserts into linked server. (Actually i tried to have the insert statement in the trigger itself, I got error
"the partner transaction manager has disabled its support for remote network transactions sql 2008" so moved to job).
This is right solution or anything better. Can we use Service Broker for this scenario? Using Linked Server and Service Broker are similar?
regards
KRS
April 28, 2011 at 7:50 am
krishnaroopa, that might be a good solution that you have there or you might want to use another technology like Mirroring, Log Shipping, Messaging or something else. Or it might just take a change to your environment to get the trigger to work properly.
Since this topic is almost a year old, I'd suggest starting a new topic and give as many details as you can. You're more likely to get more people looking at it that way.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 9, 2012 at 1:41 am
Did this work?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply