Error on Stored Procedure

I need to perform a select statement across servers... this statement works in query analyzer but not as a procedure. It tells me I need to enable the ANSI_NULLS and ANSI_WARNINGS options when put into a procedure (which it looks like i did!) I think it has something to do with this GO as the stored procedure isn't recognizing it. I tried even building it as a string and executing. Any ideas as to enable this in a procedure?

CREATE PROCEDURE TESTING AS
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SELECT * FROM MIDSERV.DB000.dbo.tblcustomer
select * from #tableTake out GO statements after SET and re-run the SP.|||Originally posted by Satya
Take out GO statements after SET and re-run the SP.

CREATE PROCEDURE TESTING AS
SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SELECT * FROM MIDSERV.DB000.dbo.tblcustomer

I get the same error telling me to enable ansi_nulls and ansi_warnings...|||Aren't the ANSI_NULL and ANSI_WARNING options to has to be set at connection level?

i belive (thought not so sure) that you cannot use it inside a stored procedure, you just have to call it BEFORE you call your stored procedure.|||that is a GOOD CALL!!!!!!!!!!!

You wouldn't know how about to set those at connection level would you? I tried doing it in a job scheduled in EM and it didn't work too well

First transact-sql statement in job:
set ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

Second transact-sql statement in job
exec procedure1

also tried combining
set ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO|||mmm...do have already tried to remove the "GO" statement?

to set an option at connection level you just have to call it BEFORE anything else you want to execute with that options activated.

now i cannot try, but i'll make a test as soon as i get a SQL Server console under my hand :-)sql

Error Message

Hi all
My doubt is when we perform any task in case of any error we will get the message like this

Msg 2627, Level 14, State 1, Procedure rec_ins, Line 3
Violation of PRIMARY KEY constraint 'PK_emp'. Cannot insert duplicate key in object 'dbo.emp'.

I want to suppress the message generated by the database is it possible or not . In case of yes how to handle it

waiting for valuable reply

Baba

If you use sql server 2005 use the try..catch block.

Code Snippet

Begin Try

Insert Into YourTable Values(Yourvalues)

End Try

Begin Catch

End Catch

If you use sql server 2000 you can't suppress the message. You have to validate the data while inserting...

Code Snippet

Insert Into Yourtable

Select Yourvalues Where

Not Exists (Select 1 From YourTable With(Nolock) Where PrimaryKeyColumn=PrimaryKeyValue)

|||Thank u for u r reply