Executing .sql file 2

[gny]

I´m new to sql server so maybe this is a really easy one...
I´m developing a VB app and have a server on which I need to dynamically create multiple databases. This is not a problem, I can do that in Transact-SQL.
The problem is that I need to create a set of sp:s, tables and views in the new databases from a template. First I created these objects in the model db so they were always copied to the new db, but since the server will be used for other db:s as well I have to abandon this approach.
I have an "admin" db which creates the new db:s, but I haven´t managed to run the .sql script from an sp in that db (I tried to use the "USE" statement, which is not allowed)
So what I pretty much would like to do is to somehow execute a generated .sql file in the new db:s. Do you know to do it? Or another solution?

/gny

 

[rajeevnandanmishra]

Hi gny,
Try to do like following :
CREATE PROCEDURE runTemplate(@database varchar(30))
AS
DECLARE @cmdString VARCHAR(100)
SELECT @cmdString=&quot;isql -U<userid> -P<password> -S<server> -d&quot;+@database+&quot; -i'<mysqlpath\mysql.sql' &quot;+
&quot;-o'<mysqlpath\msg.txt>'&quot;

EXEC master..xp_cmdshell @sql, no_output

You should replace the userid, password, servername, mysqlpath\mysql.txt and mysqlpath\msg.txt as per your values.
You can also pass these values as parameter and then create the string.

mysqlpath\msg.txt is useful to check later the messages return by executing mySql.sql


Hope this will move you in right direction.

 

[gny]

Thanks a lot. I´ll try it right away!

 

[tlbroadbent]

Create the stored procedure in the master database. Make sure the name starts with &quot;sp_&quot;. The stored procedure becomes a system SP when you do this.

After creating the new database execute the system SP using the new database name to qualify it.

Exec newdbname..sp_Create_DB_Objects

When you execute a system stored procedure with the database name, SQL changes the context of the execution to the database temporarily or for the duration of the SP execution. It is like issuing a temporary USE command. Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin