can we do "use dblive" in T-SQL in an SP

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

General SQL Server Forums

New to SQL Server Programming

can we do "use dblive" in T-SQL in an SP

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-09-13 : 13:30:41

How do we specify a part of an SP code to execute the code on a certain different database
thank you

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-13 : 13:34:30

qualify the object name or use dynamic sql

dbname.dbo.tblname
select * from dbname..susobjects

exec dbname..sp_executesql N'select * from sysobjects'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-13 : 13:36:15

You can use Four-Part names in SP Code, but you cannot use 'USE <dbname>' to execute the code in different database

Thanks,
Gopi Nath Muluka

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-13 : 13:52:58

[code]
use tempdb

exec ( '
use pubs
select DB= db_name()
')
[/code]
Results:
[code]
DB
-------------------------------
pubs

[/code]

CODO ERGO SUM

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-14 : 04:52:28

quote:
Originally posted by Michael Valentine Jones

use tempdb

exec ( '
use pubs
select DB= db_name()
')

Results:

DB
-------------------------------
pubs

CODO ERGO SUM

>> How do we specify a part of an SP code
The important bit there is SP.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-14 : 07:13:09

quote:

>> How do we specify a part of an SP code
The important bit there is SP.



use [db other than pubs]

create proc zzzabc as
exec ( '
use pubs
select DB= db_name()
')

-- execute our new proc

exec zzzabc

 
Results:

DB
-------------------------------
pubs

drop proc zzzabc

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-14 : 07:31:09

The use statement you put before it wouldn't work in an SP
The dynamic sql statement is fine and becomes similar to the sp_executesql solution I gave.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-14 : 07:36:24

Notice that the use statement is BEFORE the proc. This is to establish that we are not in the pubs DB and to prove that the dynamic SQL works in the proc.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-14 : 08:08:58

OOps sorry.
Was talking abot Michael Valentine Jones post - didn't realise you were a different person and weren't replying to my post.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-14 : 08:43:20

So much for posting before I drink a pot of coffee

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-09-14 : 13:59:56

I don't think that I understood what the code bellow does exactly and and which context we use it:
use [db other than pubs]

create proc zzzabc as
exec ( '
use pubs
select DB= db_name()
')
thanks for explaining to me

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-15 : 07:25:50

It's using dynamic sql to set the context to another dtaabase before executing commands.
use pubs is invalid in a stored procedure but can be used in dynamic sql as it is executed as it's own batch.

It's something to be avoided if possible.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources