| Author |
 Topic  |
|
|
mmalaka
Starting Member
Ireland
33 Posts |
 Posted - 07/25/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/25/2012 : 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
India
47157 Posts |
Posted - 07/25/2012 : 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
Ireland
33 Posts |
Posted - 07/25/2012 : 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
Ireland
33 Posts |
Posted - 07/25/2012 : 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
India
47157 Posts |
Posted - 07/25/2012 : 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
Flowing Fount of Yak Knowledge
USA
1430 Posts |
Posted - 07/25/2012 : 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
India
47157 Posts |
Posted - 07/25/2012 : 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/
|
|
|
| |
Topic |
|
Running SQL script stored into a table
