Linked Server not working: something pretty basic is missing?

Linked Server not working: something pretty basic is missing?

Post by Misch » Tue, 08 Oct 2002 06:29:31

I must be missing something silly, and it's driving me around the bend:
With SQLServer 2K, I can only get rudimentary linkedserver use.
At best, only simple paths (server...table) workl.
(server.database.schema.table) does not.
Perhaps the nature of what currently "works" for me will give a clue as
to where I'm going wrong:
I'm using single instances of SQL 2000 Server on two servers: WEBDB3 and
I want to query WEBDB3 from MGRDB. If I had a dream query, it would be:


Yes, I know optimizer hints don't "travel" :-)

To remove all extraneous incompatibilities, I reduced this to a
loopback, where the 'local' and 'remote' servers are just one machine.
To not lose the flavour of what I'm trying, I've left the two server
names in this example; however, all tests were done on WEBDB3 linking to

On WEBDB3: sp_configure "remote login",1
In WEBDB3.Master:        sp_addlogin 'MGRUSER','COMMON','password'
In WEBDB3.DB1234:     sp_adduser 'MGRUSER','MGRUSER','db_owner'

(In ODBC Admin) create a SQLServer ODBC DSN ('DSWEBDB3') with default
(Via SQL EntMgr) create a linked server LSWEBDB3 with:
        Type: OLEDB Provider for ODBC
        Data source: DSWEBDB3
        Catalog: DB1234
        Provider Options:
            + Level zero only
            + Allow InProcess
    Security: No mapped logins; default for unmapped logins is remote
login with MGRUSER/password.
    Server Options:
        + Data access
        + Use remote collation

AND ...
sp_linkedservers shows provider MSDASQL for the above (and SQLOLEDB
sp_helplinkedsrvlogin shows LSWEBDB3 with local login=null,
isselfmapped=0, remote login=MGRUSR.

In EntMgr: clicking on (Tables) shows 175 items, which is correct for

In Query Analyzer: "select count(*) from LSWEBDB3...sysobjects" returns
the right number for DB1234.

BUT ....
"select count(*) from LSWEBDB3.DB1234.DBO.sysobjects" fails with:
    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDASQL' reported an error.
     The provider did not give any information about the error.
(after a 10-sec delay)

If I omit 'Level zero only' from Provider Options, then "select
count(*)  from LSWEBDB3..sysobjects" returns:
    "Invalid schema or catalog specified for provider 'MSDASQL'."

If I use 'OLEDB Provider for SQL Server', the "select count(*)..."
    Server: Msg 7314, Level 16, State 1, Line 1
    OLE DB provider 'LS2' does not contain table 'sysobjects'.
    The table either does not exist or the current user does not have
permissions on that table.

If I use Server Type 'SQL Server' instead of an OLEDB provider, "select
count(*)..." returns:
   SQL Server does not exist or access denied.      (after a 20-sec


Linked Server not working: something pretty basic is missing?

Post by Misch » Tue, 08 Oct 2002 09:22:16

After setting DBCC TRACEON (7300,3604) for more info,
"select * from LSWEBDB3.DB1234.dbo.sysfiles" generates:

     [Non-interface error:  OLE DB provider MSDASQL returned
         an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should

SQLSRV32.DLL version is  3.80.0528.00.


1. I am sure this is pretty basic...but

A Filemaker 4 installed on a server needs to perform automated date
comparison/find/delete all function, that purges records that have
become outdated. As far as I can find , the host  has to restart the
database, presumably manually for the function to kick in. Is there a
non-manual way around that? I am sure that this has been asked before,
but I am stuck. Will appreciate any help.



Square One Digital Design

2. How to build a large Enterprise J2EE application - in two days...!?

3. HELP: Am I missing the point or something ?

4. Good News for MS Windows users: Your favorite database is here..

5. Am I missing something????

6. Inserting a text field in Informix

7. Am I missing something?

8. Decomposition Question

9. Stored Procedure - am I missing something

10. Query Analyzer text save - am I missing something?

11. Am I missing something incredibly easy?

12. Am I Missing Something

13. Am I missing something, or is VB6 wierd?