Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Red face Unanswered: Simple Cross Query

    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
    from [svr-amed-sql].ams.dbo.s_msfreq
    where freqgroup = 'PRSERC'

    I get

    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?

    Thanks guys!!

    Spencer

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To tell if they are properly linked, use the simplest SELECT statement that you can manage. I'd recommend using:
    Code:
    SELECT *
       FROM [svr-amed-sql].master.dbo.sysdatabases
    This will either return a result set if the servers are properly linked (and your local login has remote access), or an error message.

    If they are not linked, I'd use sp_addlinkedserver to link them.

    -PatP

  3. #3
    Join Date
    Aug 2004
    Posts
    2

    Unhappy Whoops

    These servers must not be linked because I ran that simple select statement
    SELECT *
    FROM [svr-amed-sql].master.dbo.sysdatabases
    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?

    USE master
    GO
    EXEC sp_addlinkedserver
    what goes here?
    GO

    Thank you guys so much for any help! Thanks for the quick reply Phelan!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    EXECUTE sp_addlinkedserver 'svr-amed-sql', N'SQL Server'
    -PatP

  5. #5
    Join Date
    Aug 2004
    Posts
    2
    Crap.... it's in there as well as with itself and those are the only two so they are linked. You think it has something to do with this?

    http://www.winnetmag.com/sqlserver/f...hreadid=115522

    just a guess....

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That smells pretty likely to me!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •