Error when creating Stored Procedure -- sql server connect

Vince NO[at]SPAM Netflow
1/2/2004 1:18:50 PM

I am trying to create a new stored procedure:
*******************************************************
CREATE PROCEDURE XXX_GetInfo AS
select cast(info.id as varchar) refno, info.begindt, enddt, info.company,
info.type, info.weight
from info info
union
select cast(test.id as varchar) refno, test.begindt, enddt, test.company,
test.type, info.weight
from link1.test.dbo.test test
order by begindt,enddt
*******************************************************
link1 is a linked server

When I check syntax, sql comes back and says that syntax is correct.

When I try to create the stored procedure, I get an:

[Microsoft SQL-DMO (ODBC SQLState: 42000)]

Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistent query
semantics. Enable these options and then reissue your query.

I have checked that ANSI_NULLS, ANSI_WARNINGS, and ANSI Padding is
configured as on for both servers.

When Setting up the linked server :
General Tab
Linked server - link1
Other data source - Provider Name - Microsoft OLE DB Provider for
SQL Server
Product name -
Data source - ip address of the server
Provider string -
catalog - test
Security Tab
Be made using this security context - sa of the remote machine

Any help or insight as to why it will not allow me to create the stored
procedure will be greatly appreciated.

Thanks in Advance
Vince

Linchi Shea
1/2/2004 2:33:08 PM

[quoted text, click to view]

What exactly did you check?

Why don't you just add the following lines before the
CREATE PROCEDURE statement in the script?

set ANSI_NULLS on
go
set ANSI_WARNINGS on
go

Linchi

[quoted text, click to view]

cgross NO[at]SPAM online.microsoft.com
1/2/2004 10:51:22 PM

Server settings are overridden by tool settings which are overriden by
explicit SET settings. So just issue the appropriate SET statements in the
same query window where you are executing the create proc statement.

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

v-binyao NO[at]SPAM online.microsoft.com (
1/3/2004 6:01:43 AM

Hi Vince,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.

I think Cindy has pointed out the root cause that the server settings are overidden. To explicit
that settings in either Enterprise Manager or Query Analyzer, you should issue the SET within
the CREATE PROCEDURE so that these setting will not be affected by SQL statements in
anohter Client Tools . Otherwise, the life span of these settings ends with the batch finishes
and they may be OFF before you create your procedure.

Please look through the following script and see if it works on your side. If there is anything
more we can do to assist you, please feel free to post it in the group.

-----------------------------------------------------------------
CREATE PROCEDURE XXX_GetInfo
AS

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

select cast(info.id as varchar) refno, info.begindt, enddt, info.company,
info.type, info.weight
from info info
union
select cast(test.id as varchar) refno, test.begindt, enddt, test.company,
test.type, info.weight
from link1.test.dbo.test test
order by begindt,enddt
GO
-------------------------------------------------------------

Best regards,

Billy Yao
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

shiggins_dev
1/8/2004 11:21:54 AM

Actually, I am getting the same error message and setting
ANSI_NULL and ANSI_WARNINGS explicitly in the proc don't
seem to fix it. Here is my code:

CREATE PROCEDURE osp_importexcel

@filename varchar(1000)
AS

Set ANSI_NULLS ON
Set ANSI_WARNINGS ON

declare @sqlst as nvarchar(1000)

set @sqlst = 'drop table Mailing'

exec sp_sqlexec @sqlst

set @sqlst = 'select * into Mailing from OPENROWSET
(''Microsoft.Jet.OLEDB.4.0'',
''Excel
8.0;Database='+@filename+''', [Data$])'

exec sp_sqlexec @sqlst
GO

Any ideas?

Thanks much in advance
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server connect

sql server connect : Error when creating Stored Procedure