More Help with Lined Insert 1

[dpm1234]

Hello everyone, I have written a stored procedure which inserts into tables on a linked server. The statement runs fine from Query Analyzer, but when I tried to save it in a stored procedure, I get the following error:

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

I checked ANSI NULLS and ANSI WARNINGS for the server (database properties->connetion tab) but I still get this result.

I also tried:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
in my stored proc. Doesn't help. Any suggestions??

Here is the query in case it helps:
CREATE PROCEDURE [dbo].[Insert_Remote_Record]
@name varchar(200),
@type varchar(200),
@seq_nbr int

AS

BEGIN
INSERT INTO [RMTSVR].database.dbo.RemoteTable
--INSERT INTO LocalTable (
name,
type,
seq_nbr
)

Values (@name, @type, @seq_nbr)
END

GO

 

[JohnDTampaBay]

I believe the ANSI_NULLS setting needs to be ON for the session creating the procedure, not within the procedure. Try this:

SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Insert_Remote_Record]
    @name varchar(200),
    @type    varchar(200),
    @seq_nbr int    

 AS
    
BEGIN
    INSERT INTO [RMTSVR].database.dbo.RemoteTable
    --INSERT INTO LocalTable        (
            name,
            type,
            seq_nbr
        )

    Values (@name, @type, @seq_nbr)
END

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.