Execute a script stored in TEXT field

[bborissov]

Have a table with one Text field. I used it to store a whole script for creating a DataBase, becuase a Script is very long (more than 120K) how could I execute it w/o using several variables? Is there a way to execute it. I want this because the name of the DB is different each time and I must USE that DB before create all tables and other objects in?
All this is in a SP create in other DB
Something like:

USE MyMainDB
EXEC CreateNewDB 1

--- SP code
CREATE PROCEDURE CreateNewDB(@DataBaseID as int)
AS
BEGIN
  DECLARE @DataBaseName varchar(50)
  SET @DataBaseName = [NewFirmDB]+RIGHT('0000'+cast(@DataBaseID as varchar(4)),4)
 EXEC ('CREATE DATABASE '+@DataBaseName)
--- Here comes the trouble
--- 
 EXEC ('USE '+@DataBaseName+';'+Contents of that field)
END

TIA




Borislav Borissov

 

[vongrunt]

How about sp_executesql?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[bborissov]

OK but How?
How could I get the contents of that filed into variable and pass it to sp_executesql?
I tryed

DECLARE @str_exec varchar(max)
SELECT @str_exec = Field FROM MyTable
but it is truncated to 8K.

Borislav Borissov

 

[SQLDenis]

you can't, sp_executesql will only take up to 8000 nvarchar = 4000 bytes

run exec ('') with substrings of the field, look for GO statements so that you can split it there

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Thank you both for your posts.

I do this that way:

DECLARE @str_exec1 varchar(max)
DECLARE @str_exec2 varchar(max)
DECLARE @str_exec3 varchar(max)
DECLARE @str_exec4 varchar(max)
etc.

SELECT @str_exec1 = SUBSTRING(MyField,1,8000),;
       @str_exec2 = SUBSTRING(MyField,8001,8000),;
       @str_exec3 = SUBSTRING(MyField,16001,8000),;
       @str_exec4 = SUBSTRING(MyField,24001,8000)
-- etc
FROM  MyTable -- table has only one record

EXEC ('USE MyDataBase;'+@str_exec1+@str_exec2+@str_exec3+@str_exec4 /*etc...*/)

but I want to avoid that number of variables and make code more readable (if possible)






Borislav Borissov

 

[vongrunt]

> you can't, sp_executesql will only take up to 8000 nvarchar = 4000 bytes

Not exactly... sp_exec takes anything convertible to ntext, including text itself.

Problem is how to do that anyway since concatenation (+) and @variable declaration are not allowed for text data type :E


------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[SQLDenis]

nope, EXEC has the same limit 8000 varchars

you have to look for go statements (if you have them) and execute till go then start from there add 7000 to it find next go etc etc

big mess basically

or somehow create a procedure out of this from DTS and execute the proc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Thank you.
I'll try tomorow.

Borislav Borissov

 

[SQLDenis]

yep
The first parameter to sp_executesql is an SQL statement. The data type for the parameter is ntext, so you must use a variable of the nvarchar data type. (ntext you cannot use, as you cannot assign to ntext variables

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

How about dumpin' text column into .sql file then using ol' fashioned osql with -d <dbname> switch?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[SQLDenis]

--works
declare @test nvarchar(100)
select @test ='use northwind select * from orders'
EXECUTE sp_executesql @test

--doesn't work
declare @test ntext
select @test ='use northwind select * from orders'
EXECUTE sp_executesql @test

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

Persoanlly, rather than store this in a db and try to run it, I would create a template and then use the replace parameter feature in QA to change the references that need to change. Then just execute the script.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[bborissov]

this is great if I could do this in QA
But I supposed to do this with my application w/o any access to QA. I can do it with FrontEnd but I hope I could find a way to make SQL Server to do this job not me.

Borislav Borissov

 

[SQLSister]

YOu could make it a stored procedure in the master database with a parameter for the database name. Then use dynamic SQL to create the database and tables.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[bborissov]

That was my first attempt, But after I create DataBase I couldn't USE it and stay in it to create tables and all other objects (keep in mind that DB name is in variable). I don't want to change whole script to look like this:
SET @sql_str = 'CREATE TABLE '+@DBNAME+'.dbo.TableName (....)'
EXEC (@sql_str)
If you know how to use this:
EXEC ('USE '+@DBNAME)
or any other way to USE the DB and stay in it,
I'll be very happy that .

Thank you for trying to help.

Borislav Borissov