November 13, 2007 at 6:51 am
I want to insert records into a table on my local sql server from a table on remote database.I am using query in the following format...
insert into MYTABLE (col1,col2) select col1,col2 from server.database.owner.table
If the server has IP address 100.12.54.5(say) and the remote sql server requires user name and password then how do i specify the last part(server.database.owner.table) of my query......?
November 13, 2007 at 7:41 am
After you create a linked server ( sp_addlinkedserver http://msdn2.microsoft.com/en-us/library/ms190479.aspx)you can specify the password and username with sp_addlinkedsrvloginhttp://msdn2.microsoft.com/en-us/library/ms189811.aspx
Regards,
Andras
November 13, 2007 at 11:46 pm
Well thanks Andras, that some good solution and its working fine now. I have few more doubts....
1. In sp_addlinkedserver the parameter @srvproduct='sql server' then none of the other parameter is required not even the datasource then how is it gonna connect..? and why do we use the @location parameter when the IP of the computer can be defined in the datasource itself...?I couldn't find the answer so thought ask here.....
2. Please refer the following query
insert into test1 (boardid,mask,since) select a.boardid,a.mask,a.since from openrowset('SQLOLEDB','user id=sa;pwd=pass;data source=101.12.9.57;catalog=yaftest','select * from yaf_bannedip') as a
What I am trying to do above is already done with the solution you gave but want to know what is going wrong here......It gives the following error although the credentials given are correct...
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ].
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy