Error on Stored Procedure

[justastef]

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 #table

[Granty]

How did you create your procedure? Through QA or enterprise manager?

Reason I am asking is because when I use a linked server, I cannot create the proc in Enterprise manager - I get the same error as you. However if I copy my create procedure statement and paste it into QA, then run it, all works fine.

Just an idea.

[justastef]

In query analyzer that statement works.... I need it to work in a stored procedure in Enterprise Manager.... any ideas? I've been suggested to remove the gos which i did and still a no go. It looked like something like this:

CREATE PROCEDURE procedure1 AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SELECT * from RemoteServer.RemoteDB.dbo.tblcustomer
GO

I managed to get this to work but I need this to execute through one procedure

CREATE PROCEDURE procedure1 AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
exec procedure2
GO

CREATE PROCEDURE procedure2 AS
SELECT * from RemoteServer.RemoteDB.dbo.tblcustomer
GO

Suggestions????

Any

[techgnome]

your GOs are in the wrong spots....
When creating SPs, GO is used to indicate where the END OF THE PROC is....

CREATE PROCEDURE TESTING AS

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

SELECT * FROM MIDSERV.DB000.dbo.tblcustomer
select * from #table

GO

Now, which select do you REALLY want in the SP?
The second one will NEVER return anything. It's selecing from a temp table that will only exist as long as the connection. Since the table isn't created within the SP (there's no CREATE TABLE, nor is there a SELECT INTO) it should actually generate an error.

[justastef]

CREATE PROCEDURE TESTING AS

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

SELECT * FROM MIDSERV.DB000.dbo.tblcustomer

GO


I accidentally copied an extra line (with the #table) into my original procedure when typing into the forums... I want to run the above... as you stated YES, go is to signal the end of the procedure, however, if you go into enterprise manager and try to create that procedure as above, it will not work... it says I need to enable Ansi_nulls and ansi_warnings...

[Granty]

But you can create it from QA right?

So, what is the problem....?

[justastef]

Sometimes what you create in Query Analyzer doesn't always work anywhere else... this is known as you can surf through the forum and find other people with the same complaint.

Just because that statement (minus the create procedure) works in Query Analyzer doesn't mean that my procedure is going to like it very much. And it doesn't. I'm trying to figure out why so I can fix it. Believe me if I didn't have to do this I wouldn't... its already been quite frustrating but I have my specs and this is what I have to do ... no thinking outside the box for me.

[Granty]

Easy tiger, I'm not trying to get on your case here.

What I meant was, run the whole create procedure statement in QA, not just the select (basically write your SP then copy and paste the WHOLE thing into QA and run it). It should create the SP for you which you can view in the list in Enterprise manager.

[justastef]

Didn't mean to sound edgy there... should read before I post.


It creates the procedure and I can run the procedure from QA but not through the Enterprise Manager or even the tool DBArtisan... just through Query Analyzer.

[Granty]

Hmmmm, I guess I am missing what you are trying to do.

I do not execute any SP's in enterprise manager. I either do it in QA, from a job, or from VB/Access. I wouldnt even know how to try to do it from EM.....

Sorry I cant be of more help man.

[techgnome]

OK, so the problem isn't really creating it, it's the running it from EM that's the issue... OK.... here's what I think might be wrong....
You're pulling data from a server/DB/table NOT in the db you are connected to....
Do you log into EM the same way as you do through QA... What's happening is that when you attempt to run it, it wants to validate your user creds against the other server.... I suspect that what ever userID you are using in QA, has access to the other DB as well... but the user context in EM or the other app doesn't.....