USE problem! - SQL Server Forums

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

Transact-SQL (2000)

USE problem!

Author

Topic

daquoc
Starting Member

35 Posts

Posted - 2006-05-10 : 02:54:07

Hi experts

I use database like this

DECLARE @DATANAME CHAR(10)
set @DATANAME = 'MY_DATA'

execute('USE ' + @DATANAME)

The command completed successfully, but the database I want to 'use' isn't focused. I don't know why. Help me.
Thank.
quoc duong

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-10 : 02:56:29

my guess you want to "focus" because you want to execute some sql statements?

include them in your dsql for the 'use'...

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-10 : 03:01:21

If you need to access another database's table, just specify the databasename.dbo.table_name

-- in db1 accessing table1 in db2
select * from db2.dbo.table1

daquoc
Starting Member

35 Posts

Posted - 2006-05-10 : 05:00:03

Oh no.
I want to 'use' database, not a table. I mean that in my SQLSERVER which has some database, for ex : payroll, account, hrms ect.As I work in Query Analyzer. I do USE PAYROLL to focus PAYROLL database, either I USE ACCOUNT to focus ACCOUNT database. It's OK, but when I

declare @mydata varchar(20)
set @mydata = 'PAYROLL'
execute('USE ' + @mydata) -> I can't focus PAYROLL database.

Try these commands in your SQLSERVER.
Thank.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-10 : 05:24:24

Hi daquoc,

I think we're trying to understand why you want to do that (rather than just type USE PAYROLL). Can you give us some clues?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

pootle_flump

1064 Posts

Posted - 2006-05-10 : 06:09:02

Hi

The 'USE MyDB' only has scope of the EXEC statement. You will need to include any statements you wish to execute in 'MY_DATA'
in the string that you execute with EXEC. Apart from certain system sprocs you should be able to bet away with not using USE though (as the above posts state).

Actually - I've just reread Jen's post - I'm really rehashing her advice.

FROM BoL:
Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this example, the database context is master:

USE master EXEC ("USE pubs") SELECT * FROM authors

daquoc
Starting Member

35 Posts

Posted - 2006-05-11 : 21:39:34

Hi experts
Why do I do that?. Here is the explanation.
My focus: I want to know how many objects (tables, view, storepro ect) are there in my SQLSERVER except system database, so I do sth like this.

--- My code
declare cursor _tmpDATA for
use master
select name from sysdatabases
where name <> 'master' and name <> 'model' and name <> 'msdb' and name <> 'Northwind' and name <> 'pubs' and name <> 'tempdb'

open _tmpDATA

declare @DATANAME varchar(20)
fetch next from _tmpDATA into @DATANAME
while @@fetch_status = 0
begin
-- Here's the problem
execute('USE' + @DATANAME)
--- Select object list from each database I focus
--- and then insert into another table for further work.
--- some code here
....
fetch next from _tmpDATA into @DATANAME
end
close _tmpDATA
deallocate _tmpDATA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 22:13:53

You don't have to use USE at all

use master
declare @sql	nvarchar(4000)
declare cursor _tmpDATA cursor for
use master
select name from sysdatabases
where name <> 'master' and name <> 'model' and name <> 'msdb' and name <> 'Northwind' and name <> 'pubs' and name <> 'tempdb'

open _tmpDATA

declare @DATANAME varchar(20)
fetch next from _tmpDATA into @DATANAME
while @@fetch_status = 0
begin
-- Here's the problem
--execute('USE' + @DATANAME)
--- Select object list from each database I focus
--- and then insert into another table for further work.
--- some code here
	select	@sql = 'select xtype, count(*) from ' + @DATANAME + '.dbo.sysobjects group by xtype'
	exec (@sql)

fetch next from _tmpDATA into @DATANAME
end
close _tmpDATA
deallocate _tmpDATA

daquoc
Starting Member

35 Posts

Posted - 2006-05-12 : 02:21:45

Thank a lot.
Nice weekend!.
daquoc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-12 : 02:58:38

And without cursor



Declare @dbname varchar(100)
set @dbname=''

while exists
	(
		select * from Master..sysdatabases where name>@dbname and dbid>6
	)
Begin
	select @dbname=min(name) from Master..sysdatabases where name>@dbname and dbid>6
	exec('select '''+@dbname+''' as database_Name , xtype, count(*) as counts from ' + @dbname + '.dbo.sysobjects group by xtype')
End

Madhivanan

Failing to plan is Planning to fail

daquoc
Starting Member

35 Posts

Posted - 2006-05-14 : 23:16:03

Thanks a lot.
daquoc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources