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
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
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.
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")