Pointing Stored Procedures to the current DB

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

SQL Server Development (2000)

Pointing Stored Procedures to the current DB

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-13 : 16:52:02

Is there a way to point a query in a stored procedure to the DB in which the PROC is being executed? (both on the same server)

Example:
--Database_1


CREATE PROC A_QUERY AS
BEGIN 
	SELECT
			STUFF
	FROM		TABLENAME	
	WHERE	 STUFF <> 'BADSTUFF'
END

--Database_2


EXECUTE DATABASE_1..A_QUERY

I want it to run against DB2 rather than DB1.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-13 : 17:03:37

Use the fully qualified Stored Procedure Name
ie instead of SPName --> use --> DB2.dbo.SPName

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-13 : 17:24:57

Can you provide an example:

EXECUTE DATABASE_1..A_QUERY

Seems to be executing just fine, it's just running against the DB in which the PROC resides rather than the DB from which it's being called.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-13 : 17:40:14

If you want it to run against DATABASE_2, then the stored procedure must exist in that database or the objects within the stored procedure must be pointing to DATABASE_2.

Tara Kizer
aka tduggan

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-14 : 11:39:22

Sorry, but how would I struction the PROC so that it will use the DB from which the PROC is executed?

This is resulting in an error:


CREATE PROC TESTPROC
	@DB VARCHAR(128)
AS
BEGIN 
	SELECT * FROM @DB..PRCINFO3
END

"Line 6: Incorrect syntax near '.'."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-14 : 12:10:13

CREATE PROC TESTPROC
AS
BEGIN
SELECT * FROM DB1..PRCINFO3
END

Do not do it dynamically. If you need to, then you'll need to use dynamic SQL at the expense of security and performance.

Tara Kizer
aka tduggan

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-14 : 14:06:40

Thanks, tk that works fine! Followup, can I execute a proc that resides in on different server's DB?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-14 : 14:19:28

Yes. You would have to setup a linked server though and refer to the object using its four part naming convention:

LinkedServerName.DbName.OwnerName.ObjectName

Tara Kizer
aka tduggan

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-14 : 15:32:12

Thanks tkizer, I'll create a separate post for my questions about setting up the linked server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources