August 31, 2009 at 4:07 pm
Hi guys,
I am having a hard time figuring this out, I'd like to create a database from a stored procedure which table definitions are stored in a SQL file.
So, far I have this...
--------------------------------------------------
DECLARE @DBName VARCHAR(5)
SET @DBName = 'DEMO'
DECLARE @sql VARCHAR(300)
SET @sql = 'IF DB_ID(''' + @DBName + ''') IS NULL CREATE DATABASE ' + @DBName + ' ELSE RETURN'
EXEC(@sql)
SET @sql = 'USE ' + @DBName + '; '
SET @sql = @sql + 'EXEC master..xp_cmdshell ''osql -E -n -SMyServer -iC:\Temp\MySQLFile.sql'';'
EXEC(@sql)
--------------------------------------------------
Where "MySQLFile.sql" has the table definition (attached for your reference)
Your help is greatly appreciated!
...Alex
August 31, 2009 at 4:51 pm
So... whats the problem?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2009 at 9:50 pm
Opps I forgot to state the issue, the problem is that all the T-SQL code inside the file doesn't get executed properly on the new scope (USE @DBName)
I'd like to run all the code from the file against the newly created database.
I hope it's clear now 😀
...Alex
August 31, 2009 at 10:14 pm
The use statement that you specified is not going to ever help. The OSQL command executes in its own context, and will go to the default database for whatever user SQL is running under.
DECLARE @DBName varchar(128)
SET @DBName = 'DEMO'
DECLARE @cmd varchar(300)
IF DB_ID(@DBName) IS NULL
BEGIN
SELECT @cmd = 'CREATE DATABASE ' + @DBName
EXEC ( @cmd )
SELECT @cmd = 'osql -E -n -S' + @@SERVERNAME + ' -d' + @DBName + ' -i"C:\Temp\MySQLFile.sql" '
EXEC master..xp_cmdshell @cmd
END
This should be real close and somewhat more resilient that the original.
CEWII
September 1, 2009 at 3:17 pm
Works great! Thanks a bunch! 😀
...Alex
September 1, 2009 at 3:35 pm
You are welcome.
CEWII
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply