this is the way I am executing the commands that are within my XML:
declare @i int = 1
select @sql1 = ''
SELECT @SQL2 = 'Radhe'
WHILE @sql2 is not null
begin
SELECT @sql2 = @XML.value('(/Krishna/text())[sql:variable("@i") cast as xs:int?][1]', 'varchar(max)')
if @DEBUG=1
PRINT COALESCE(@sql2,'@SQL2 WAS NULL' ' -- @I IS ' CAST(@I AS VARCHAR(5)))
if @sql2 is not null
begin
SET @sql1 = CAST (@sql1 @sql2 @vbCrLf AS NVARCHAR(MAX))
IF @PrintOnly = 1
BEGIN
EXEC sp_DisplayNVarchar @textToDisplay = @SQL2, @debug =0
END
ELSE
BEGIN
EXEC (@SQL2)
END
end
SELECT @i = @i 1
if @i >= @Limit
SET @sql2 = null
end
BASICALLY:
each line of the XML is a command
SELECT @sql2 = @XML.value('(/Krishna/text())[sql:variable("@i") cast as xs:int?][1]', 'varchar(max)')
My question is:
How could I replace the every GO
inside my long script into a new line in my XML?
Every time I meet a GO
, that GO
is removed but from that place on is a new line
in my XML.
this is an example of code and XML that works:
here is the code:
---------------------------------------
----check the data
---------------------------------------
GO
SELECT [@@TRANCOUNT]=@@TRANCOUNT
TRUNCATE TABLE #the_inserts
TRUNCATE TABLE #VICASA477
INSERT INTO #the_inserts(RADHE1)
SELECT RADHE1='use apcore;' CHAR(10) CHAR(13) 'exec sp_count ' '''' E.AP_NAME2 ''''
FROM #E E
DECLARE @XML3 XML
SELECT @XML3 = (SELECT #the_inserts.radhe1 AS Krishna FROM #the_inserts FOR XML PATH(''))
INSERT INTO #VICASA477
EXEC sp_execXML @dbname=N'APCore'
,@XML=@xml3
,@DEBUG=0
,@PrintOnly=0
select @XML3
SELECT * FROM #vicasa477
GO
Here is the XML: (partial view but you get the idea)
<Krishna>use apcore;
exec sp_count '[sub].[matchAgreementEmailSent]'</Krishna>
<Krishna>use apcore;
exec sp_count '[sub].[receivedLog]'</Krishna>
<Krishna>use apcore;
exec sp_count '[sub].[repl_Airline]'</Krishna>
<Krishna>use apcore;
exec sp_count '[sub].[repl_Airport]'</Krishna>
<Krishna>use apcore;
exec sp_count '[sub].[repl_ArrivalCalendar]'</Krishna>
<Krishna>use apcore;
exec sp_count '[sub].[repl_Aupair]'</Krishna>
<Krishna>
and here the results: (partial view but you get the idea)
CodePudding user response:
There's no good way to run that script from TSQL. You need to run each batch separately, which can be done by accumulating the lines for each batch, and executing it when you see GO
. Stripping GO
is a hack which won't work for DDL scripts as many DDL statements must begin a batch, or be the only statement in the batch.
The thing that won't work is the scripts like
use somedb
go
create table foo(id int)
In TSQL if you parse and exec this as:
exec ('use somedb')
exec ('create table foo(id int)')
The database context will be switched inside the first batch, but revert to the original database context at the end of the first dynamic SQL invocation. You simply can't change the database context permanently for the session in dynamic SQL. So your pre-processor must concatenate the database context switch with the subsequent batches. eg
exec ('use somedb
create table foo(id int)')
Which might work if your script is generated exactly the same and you reliably identify the use database
statements. But it's complicated and basically not worth doing. Instead use an external program like sqlcmd
or powershell's `invoke-sqlcmd', or even a client program like .NET that can issue top-level batches that permanently change the database context.
And you may find other session-level settings that will have the same problem.
CodePudding user response:
EDIT: As mentioned by @DavidBrowne, this answer doesn't work if changing the current database with USE
is necessary.
You can run this script using a cursor, which executes each batch separately.
To split the batches we need to use XQuery. This is made significantly more complicated by the fact that the batches are separated by the same Krishna
node again, rather than each being contained in a separate child node.
DECLARE @sql nvarchar(max), @crsr CURSOR;
SET @crsr = CURSOR FAST_FORWARD READ_ONLY FOR
SELECT
x.krsh.query('
let $go := .
let $prev := /Krishna[. << $go][text() = "GO"][1]
return /Krishna[text() != "GO"][. << $go and not(. << $prev)]/text()
').value('text()[1]','nvarchar(max)') line
FROM @xml.nodes('/Krishna[text() = "GO"]') x(krsh);
OPEN @crsr;
GOTO Ftch;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @sql;
ftch:
FETCH NEXT FROM @crsr
INTO @sql;
END;
The logic runs like this:
- Use
.nodes
to grab allKrishna
nodes which containGO
. - For each of those, we run the following XQuery expression:
- Assign the current node to
$go
- Find the node previous to this which contains
GO
. - Find all
Krishna
nodes which do not containGO
, and... - ... are located before
$go
... - ... and after
$prev
(if any). - Return the inner
text()
- Concatenate all the text together using
.query
and.value
Note: This assumes that the final node is always GO
.