SP failing with linked server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP failing with linked server

Author

Topic

flodpanter
Starting Member

3 Posts

Posted - 2006-02-03 : 07:00:25

SELECT * FROM OPENQUERY(BRONNUM, 'SELECT top 5 Varenummer,KategoriID,VisNr,AutoID from dbo.Vare_online ')

works fine in query analyzer. In SP it doesnt. When i click apply on creation of the SP, I get Error 7405.
So I tried this:

CREATE PROCEDURE [dbo].[sp_test] AS

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

SELECT *
FROM OPENQUERY(BRONNUM, 'SELECT top 5 Varenummer,KategoriID,VisNr,AutoID from dbo.Vare_online ')

SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO

I still get Error 7405 though. I have searched the web, but no luck so far. What do I do?

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-03 : 07:10:36

Move the ANSI nulls and warnig out of create procedure

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

CREATE PROCEDURE [dbo].[sp_test] AS

SELECT *
FROM OPENQUERY(BRONNUM, 'SELECT top 5 Varenummer,KategoriID,VisNr,AutoID from dbo.Vare_online ')
GO

SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources