Running SQL script stored into a table

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 2008 Forums

Transact-SQL (2008)

Running SQL script stored into a table

Author

Topic

mmalaka
Starting Member

33 Posts

Posted - 2012-07-25 : 11:23:43

Experts

I have a table which include sql scripts
The table contains one column called [T-SQL CREATESCRIPT] of type varchar(500)

I am creating a script to go through the records of the table and execute the script in each record.

The first record contains the following script

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'V_M_ABS_LIC_REQ_A') DROP VIEW V_M_ABS_LIC_REQ_A; GO create view V_M_ABS_LIC_REQ_A AS SELECT a.*, b.G3E_SRROWNO,b.GEOM,c.feature_state FROM M_ABS_LIC_REQ_N a, M_ABS_LIC_REQ_A b,gc_netelem_s c WHERE a.G3E_FID = b.G3E_FID and a.G3E_FID = c.G3E_FID; GO

below is my script where I try to do this for the first record in the table

declare @cursor cursor, @SQL nvarchar(500)
set @cursor = cursor for select top 1 [T-SQL CREATESCRIPT] from POSTPROCESSSCRIPT
open @cursor

while 1=1
begin
fetch from @cursor into @SQL
if @@fetch_status <> 0
break
Print @SQL;
SET @SQL = REPLACE(@SQL,' ','');
SET @SQL = REPLACE(@SQL,' ','');
SET @SQL = REPLACE(@SQL,';',';'+ CHAR(13)+ CHAR(10));
SET @SQL = REPLACE(@SQL,'GO','GO'+ CHAR(13)+ CHAR(10));
Print @SQL;
exec SP_EXECUTESQL @SQL;

end

when I run this I get the following output:

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'V_M_ABS_LIC_REQ_A') DROP VIEW V_M_ABS_LIC_REQ_A; GO create view V_M_ABS_LIC_REQ_A AS SELECT a.*, b.G3E_SRROWNO,b.GEOM,c.feature_state FROM M_ABS_LIC_REQ_N a, M_ABS_LIC_REQ_A b,gc_netelem_s c WHERE a.G3E_FID = b.G3E_FID and a.G3E_FID = c.G3E_FID; GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'V_M_ABS_LIC_REQ_A') DROP VIEW V_M_ABS_LIC_REQ_A;
GO
create view V_M_ABS_LIC_REQ_A AS SELECT a.*, b.G3E_SRROWNO,b.GEOM,c.feature_state FROM M_ABS_LIC_REQ_N a, M_ABS_LIC_REQ_A b,gc_netelem_s c WHERE a.G3E_FID = b.G3E_FID and a.G3E_FID = c.G3E_FID;
GO

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 3
'CREATE VIEW' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'GO'.

Any advice plz?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-25 : 11:31:15

Have a look at the description of "go".
I think it gets ignored when executed like this.

Try this
exec ('select 1
go
create proc a as select 1
go
')

You could execute your commands separately - spilt them at a go rather than inserting a crlf.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 11:35:09

whats the purpose behind storing sql queries as values in table? is it for some automated execution?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mmalaka
Starting Member

33 Posts

Posted - 2012-07-25 : 11:41:53

quote:
Originally posted by visakh16

whats the purpose behind storing sql queries as values in table? is it for some automated execution?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes

mmalaka
Starting Member

33 Posts

Posted - 2012-07-25 : 11:55:02

quote:
Originally posted by nigelrivett

Have a look at the description of "go".
I think it gets ignored when executed like this.

Try this
exec ('select 1
go
create proc a as select 1
go
')

You could execute your commands separately - spilt them at a go rather than inserting a crlf.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Can you please advice me on how to split at a Go ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 12:20:23

quote:
Originally posted by mmalaka

quote:
Originally posted by visakh16

whats the purpose behind storing sql queries as values in table? is it for some automated execution?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes

then isnt it better to make it as a job and execute queries through SQLCMD via batch file?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-07-25 : 19:59:47

You are getting the syntax error because "GO" is not part of the SQL language. It is only recognized by the Management Studio environment as a batch separator. The SQL script you have consists of two batches. Could you place the batches into two different records and then impose an execution order on the records?

=================================================
Show me a sane man and I will cure him for you. -Carl Jung, psychiatrist (1875-1961)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 21:56:58

quote:
Originally posted by Bustaz Kool

You are getting the syntax error because "GO" is not part of the SQL language. It is only recognized by the Management Studio environment as a batch separator. The SQL script you have consists of two batches. Could you place the batches into two different records and then impose an execution order on the records?

=================================================
Show me a sane man and I will cure him for you. -Carl Jung, psychiatrist (1875-1961)

and batch separator is customisable too for a connection

http://visakhm.blogspot.com/2010/02/custom-batch-separator-in-t-sql.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources