USE statement - 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 statement

Author

Topic

nejc
Starting Member

17 Posts

Posted - 2005-08-30 : 02:50:50

I am trying to make a "universal" script for making replications. I have everything else done already, except for the USE [databse] statement.

What I am asking is, how do i put a variable after the USE statement.
Thats what i tried and it doesnt work:

DECLARE @db varchar(30)
SET @db = 'TESTdb'

USE @db
GO

Help appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-30 : 03:00:50

Thats not possible inside stored Procedure
You can use DatabaseName..ObjectName method

DECLARE @db varchar(30)
SET @db = 'TESTdb'

Exec('Select * from '+@db+'..yourTable')

Madhivanan

Failing to plan is Planning to fail

nejc
Starting Member

17 Posts

Posted - 2005-08-30 : 03:30:32

thank you for a fast reply madhivanan. I have been trying out your advice with no success so far. I am kinda new to whole T-SQL scene. I dont understand why i would have to give the object name in there too, as the tables for replication are defined below.
Theres the whole script (with some parts deleted):

-- Enabling the replication database
use master
GO

exec sp_replicationdboption @dbname = N'DB1', @optname = N'merge publish', @value = N'true'
GO

use [DB1]
GO

-- Adding the merge publication
exec sp_addmergepublication @publication = N'PUBLICATION', @description = N'blahblah',........
exec sp_addpublication_snapshot @publication = N'PUBLICATION',@frequency_type = 8, ...........
GO

exec sp_grant_publication_access @publication = N'PUBLICATION', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'PUBLICATION', @login = N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'PUBLICATION', @login = N'sa'
GO

-- Adding the merge articles
exec sp_addmergearticle @publication = N'PUBLICATION', @article = N'Article-from-table', @source_owner = N'dbo', @source_object = N'TABLE11', @type = N'table', ....
GO

-- Adding the merge subscription
exec sp_addmergesubscription @publication = N'PUBLICATION', @subscriber = N'SERVER', @subscriber_db = N'DB2', @subscription_type = N'push', ....
GO

I was looking into how USE works. Correct me if i'm wrong, but USE just returns the name of the table specified (if none, it returns the name of the database thats currently being used). The question is, where it returns it to ? Does it use a temporary variable for returning.... ?

nejc
Starting Member

17 Posts

Posted - 2005-08-30 : 08:50:47

i figured out another way to do that....took me awhile tho...

In my application, when a user logs in, he chooses which database she/he wants to log in to, so i totally removed both of the USE statments from the script and it works like a charm :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-30 : 09:27:23

hey there.
the USE statement doesn't allow varables for DbName.
by doing exec('use DbName') you effectivly use another batch (like opening a new window in Query Analyzer)

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-30 : 09:51:48

Usage of Use is available only thru Dynamic SQL

Exec('Use '+@dbName+
'Select ..........')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources