I am trying run a select and insert in query analyzer on another database across servers. They are both running win 2003 one with SQL2003 Enterprise(the server name of this one is svr-sql-ams6401 and I am running this query while connected to this database in Analyzer) and one with SQL2k Standard(svr-amed-sql). Below is the query with results:
insert into s_msfreq (freqgroup, freqcode, descrip)
select freqgroup, freqcode, descrip
where freqgroup = 'PRSERC'
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to complete this operation could not be found on the server (they were supplied with the ODBC setup disk for the SQL Server driver). Please contact your system administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset returned 0x80004005: ].
I am pretty sure these servers are linked.... but how do I tell and how do I link the two if they are not? Or, is this not the problem?
These servers must not be linked because I ran that simple select statement
and got the same huge error message.
So - the thing is svr-amed-sql machine has an important database (ams - which we are trying to grab the data from) and the database we are trying to send the data to is a test database (test64) on the other machine (svr-sql-ams6401) and contains test data so I am not really worried about that. BUT,
on the same machine *svr-sql-ams6401* , there is a database that is live and must not be effected by anything that I do (db name=prod64 - very important)
Basically - what parameters do I need to pass to get this thing right?
I took this example and pasted it from the ms website:
What machine should I run this on? svr-amed-sql or svr-sql-6401?
what goes here?
Thank you guys so much for any help! Thanks for the quick reply Phelan!
According to MSDN, 0x80004005 is a generic failure message, so it isn't very helpful.
I vaguely remember this problem occuring for SQL servers that have hyphens in their name, so we need to tread with some care.
I'd search the master.dbo.sysservers table on the source server (the one you are accessing via Query Analyzer) looking for the name of the remote server. If it is present, then there already is a linked server, but something else is wrong. If it is not present, then I'd add it using: