need help with Insert into remote table

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

General SQL Server Forums

New to SQL Server Programming

need help with Insert into remote table

Author

Topic

allan_wiber
Starting Member

2 Posts

Posted - 2009-03-09 : 19:06:53

What is the syntax for doing INSERT into a remote table with SELECT from a local table?

My code generated an error.

Code:

EXECUTE ('INSERT REMOTESERVER.WIBWEB_DB.dbo.Associate_Codes
(CODE, DESCRIPTION)
Select
CODE, DESCRIPTION
from LOCALSERVER.WIB_DB.dbo.gen_tables
WHERE table_name = "assoc"') AT REMOTESERVER

Error:
OLE DB provider "MSDASQL" for linked server "REMOTESERVER" returned message "[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'LOCALSERVER' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'REMOTESERVER'.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 20:11:26

1) create a linked server with access to the table in question.
2) Execute the code ... without EXECUTE ..

From the information above it looks like you don't have any linked servers defined.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

allan_wiber
Starting Member

2 Posts

Posted - 2009-03-09 : 20:45:07

I ran exec sp_linkedservers and verified that the servers are defined.

I tried running it without the EXECUTE, but it gave a similar error. Running the select portion by itself worked fine. Once I include the insert portion, I get the "could not find server 'REMOTESERVER' in sys.servers" error. However, this time it is the 'REMOTESERVER' that was not found.

CODE:

INSERT REMOTESERVER.WIBWEB_DB.dbo.Associate_Codes(CODE, DESCRIPTION)
select CODE, DESCRIPTION from OPENQUERY(LOCALSERVER,'select * from WIB_DB.dbo.gen_tables
WHERE table_name = "assoc"')

I also tried it without OPENQUERY and got a different error.

INSERT REMOTESERVER.WIBWEB_DB.dbo.Associate_Codes(CODE, DESCRIPTION)
Select
CODE, DESCRIPTION
from LOCALSERVER.WIB_DB.dbo.gen_tables
WHERE table_name = 'assoc'

ERROR:
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "LOCALSERVER" supplied inconsistent metadata for a column. The column "TIME_STAMP" (compile-time ordinal 7) of object "WIB_DB.dbo.gen_tables" was reported to have a "DBCOLUMNFLAGS_ISROWVER" of 0 at compile time and 512 at run time.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 22:21:46

Well that was a different message ...

Ref ... http://support.microsoft.com/kb/910454.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources