Linked Svr SQL2000

Last Post 17 Feb 2005 03:17 AM by Janetb99. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Janetb99
New Member
New Member

--
16 Feb 2005 10:50 AM
I've successfully linked a server using EM (both are sql2000). I can successfully run a select statement through QA (see below). I'm trying to test a locally-stored procedure stored 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

select * from linkedName.db.dbo.tblName

Janetb99
New Member
New Member

--
16 Feb 2005 11:03 AM
Guru, everything works fine in QA, even the settings. I get the error in Enterprise Manager when establishing the new stored procedure and it won't let me save it . FYI - no syntax errors.

jb
Janetb99
New Member
New Member

--
16 Feb 2005 11:43 AM
'Cause my name's not guru and I'm scared.
Janetb99
New Member
New Member

--
17 Feb 2005 03:17 AM
rm Guru,
Okay, big gulp and I did the stored proc in QA successfully. Opened it in EM and then set the permissions.

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_countyListing")
coData=rsCo.getrows
numCo=ubound(coData,2)

Any clues?
Janetb99
New Member
New Member

--
18 Feb 2005 08:49 AM
Good question! Wish I'd thought of it. It gets weird.

If I log on to query analyzer using the userid/password from the remote server that I've duplicated on my server, execute the sp, it executes.

If I delete everything in my ado except for the connection to the server, and put the same server, default catalog, userid, password into the connection string, I get the error:

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user useridAbove

Ideas? And, thanks.


Janetb99
New Member
New Member

--
18 Feb 2005 09:07 AM
rm,
I got it to work! Basically, all I did was delete the linked server, re-add it with the same info, start-stop sql services, and bingo! I think that my trying things and switching the linked server security access back-and-forth didn't get cleared one time. Anyway, cool, neato, thanks so much.

jb


Acceptable Use Policy
---