November 24, 2004 at 2:05 pm
I have set up a linked server and am able to run a select statement against it in QA. When I use the same syntax in a stored procedure, I get an error about ANS_NULLS and ANSI_WARNINGS being set for the connection.
How do I set those values? I put the two set statements in the stored procedure to no avail. I can see nowhere in linked server properties where I can set these either.
Your replies are appreciated.
Thanks,
Sean Wyatt
seanwyatt.com
November 24, 2004 at 2:22 pm
I had a similar issue with attempting to use the OPENROWSET function within a stored procedure. What syntax are you using to access this Linked Server within your stored procedure? I believe that you have to use the four-part naming convention for accessing this linked server. If you are trying to use OPENROWSET or OPENQUERY then you will receive this error.
See: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_23xd.asp
A.J.
DBA with an attitude
November 24, 2004 at 2:33 pm
In QA I Ran This Successfully:
SELECT [DESCRIPTION]
FROM LINK_SERVER.VMFG.dbo.PART
WHERE ([ID] = 'CFR26141585')
However, when I try to put that syntax into a stored procedure:
CREATE PROCEDURE [dbo].[up_GET_PART_DESC]
@PART_ID varchar(30)
AS
SELECT [DESCRIPTION]
FROM LINK_SERVER.VMFG.dbo.PART
WHERE ([ID] = @PART_ID)
I get the ANSI_NULLS and ANSI_WARNINGS message
Sean Wyatt
seanwyatt.com
November 24, 2004 at 2:45 pm
Found the problem:
http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;296769
I added
SET ANSI_NULLS ON
GO
To the top of the CREATE PROCEDURE statement and all is well.
Thanks for your reply.
Sean Wyatt
seanwyatt.com
November 24, 2004 at 3:10 pm
One thing I just noticed is that if you create the procedure via Query Analyzer rather than Enterprise Manager it'll work fine (as long as ANSI NULLS is ON in the Query Analyzer options (Which it usually is).
A.J.
DBA with an attitude
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply