October 7, 2003 at 9:14 am
Greetings,
Question: I am generating an install script for our customers, and I have to run portions of this script on different databases. What I need the script to do is to create a new database, switch over to that new database, perform several operations on it, switch over to the master database, do some operations on that, and finally switch the context over to the original database that the user was using and perform the rest of the install on that database.
However, I am having problems integrating the USE @dbname command into the script. Since the new database does not exist when the script starts to run, the script will not parse if I put the 'USE @new_dbname' command in the script...it will say that this new database does not exist.
I can get around this by putting the USE command into Dynamic SQL. The problem is that if I do this, the USE command runs only in the context of that Dynamic SQL...once I exit out of that context, it goes back to the database that it was previously using.
Is there any way to change database context within a script other than by using the USE command?
Or, is there a better way to integrate the USE command into this script?
Thanks,
October 7, 2003 at 11:46 am
Not that I know of. Typically on the app side you avoid this by changing the connection string or executing a use db on the open connection. One way might to put placeholders in your script, then at run time replace ~DBNAME1~ with the real name.
Andy
October 7, 2003 at 11:56 am
USE master
declare @dttm varchar(55)
select @dttm=convert(varchar,getdate(),113)
raiserror('Beginning InstPubs.SQL at %s ....',1,1,@dttm) with nowait
GO
if not exists (select * from sysdatabases where name='pubs')
begin
raiserror('Creating pubs database....',0,1)
CREATE DATABASE pubs ON DEFAULT = 3
end
GO
CHECKPOINT
go
USE pubs
--perform operation
go
use master
--perform operation
go
use yourdatabase
--perform operation
go
October 10, 2003 at 1:32 am
As long as you put 'GO' between creating the database and 'USE'ing it the script will run but I suspect you already knew that 🙂
I don't think there is anyway round getting the script to pass a syntax check and this may look a little knaff to your customers if they are running from it from isql or the like.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply