Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: Linked Server SQL2000 SP

    Brand new to linked servers and trying some stuff. I've successfully linked a server using EM (both are sql2000). I can successfully run a select statement through QA. I'm trying to test a locally-stored procedure and am having a bit of trouble. Please, no arguements about best method - I have no choice. Cannot create stored procs on remote machine.

    If I run this through QA, I get an immediate return set: select * from linkedName.dbName.dbo.tblName

    If I try to create a test stored proc like the following, I get: Error 7405: Heterogeneous queries require the ANSI_Nulls and ANSI_WARNINGS options to be set for the connection. This ensure consistent query semantics. Enable these options and thenreissue your query. When I looked it up in BOL, I thought I was setting these items?

    CREATE PROCEDURE zp_countyListing AS

    select * from linkedName.db.dbo.tblName

    Thanks for the assistance.

  2. #2
    Join Date
    Oct 2003
    Okay, didn't use EM, but created the stored proc in a local db successfully with qa.

    Now, the ado connection/security is the problem, I think. I've created a local user with the same userid/password in the local sql as the linked server's access credentials and gave datareader permissions to the local db in which I created the stored procedure. But, no matter which data source and initial catalog I use in the connection string, (even when using the four-part naming convention within the ado stored proc execution statement, I can't get the stored proc record set.

    I've set the security under the linked server to the local account with the same name and pwd as the linked server-> selected impersonate -> be made using this security context ->supplied the userid/pwd.

    set Conn=server.createObject("adodb.connection")
    Conn.Open "Provider=sqloledb;Data Source=localServer;Initial Catalog=localDB;User Id=userIDName;Password=userPwd"

    set rs = CreateObject("ADODB.RecordSet") 'recordset object
    set rsCo=Conn.execute("localServer.localDB.dbo.zp_coun tyListing")

Posting Permissions

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