OpenQuery and nulls - 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

Transact-SQL (2000)

OpenQuery and nulls

Author

Topic

iminore
Posting Yak Master

141 Posts

Posted - 2005-08-16 : 07:56:58

This procedure:

CREATE PROCEDURE procSearch
@find varchar(80)
AS
set nocount on

exec('set ansi_nulls on set ansi_warnings on select fileindex, filename from OpenQuery(kmserver, ''select fileindex, filename from scope() where freetext(contents,''''' + @find + ''''')'')')

GO

is giving the error:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options...

Yet it works in query analyser. the proc is to do with Indexing Services, if relevant.

iminore
Posting Yak Master

141 Posts

Posted - 2005-08-16 : 08:33:00

I'll answer my own question.

If I compile the procedure with:
set ansi_nulls on
set ansi_warnings on
then it works.

Now how do I ensure that always happens?

SQLTEAMSteve
Starting Member

8 Posts

Posted - 2005-08-18 : 13:34:29

CREATE PROCEDURE procSearch
@find varchar(80)
AS
set nocount on
set ansi_nulls on
set ansi_warnings on
exec('set ansi_nulls on set ansi_warnings on select fileindex, filename from OpenQuery(kmserver, ''select fileindex, filename from scope() where freetext(contents,''''' + @find + ''''')'')')

set ansi_nulls off
set ansi_warnings off
set nocount off

_________________________
Stephen R Montgomery

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources