dynamic SQL & triggers
-
Yep... I missed that one... guess it's time for some sleep
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That's not only one you missed.
Compare this:
1) Create VIEW in all tarteted databases with set of fields matching set of inserted data;
2) Create INSTEAD OF INSERT trigger on this view ...
and
create an updateable passthrough view in each target database
_____________
Code for TallyGenerator -
i realised that the context of the inserted table is within the table and created a temp table that grabs the inserted data. i then insert into the target database and table using a sql string which is executed.
i think that the way i was writing the sql statement was causing the insert to fail. i changed the way i write it and it is all working fine now.
thanks again for all those who are giving me ideas here - much appreciated. first time i have ever used a forum so it is good
but the question is still there in my mind - are triggers the best solution given that everything happens on the same server and it is important to keep things in sync.
-
create an updateable passthrough view in each target database
I don't see where you said those words, but may that's what you meant... I was talking about just creating the views and nothing else... no triggers, no procs, no inserts... don't need 'em.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jono,
I'm doing something similar. I have three databases with the same table and I want to copy the data from one database to another (3 seperate clients who sort of work together and have agreed for the moment to use 1 client's data from a legacy system). I like having this in a sproc instead of DTS Package so I can search my sprocs when the db schema changes. Then I set the sproc to be executed nightly from a job.
Hope this helps!
exec DTSCopyInventoryLookup 'INTERFLOW02','Inventory.dbo.Transmissions','Transmissions'
The Sproc:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE DTSCopyInventoryLookup
@LinkedServer varchar(100),
@RemoteTableName varchar(100),
@LocalTableName varchar(100)
AS
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
declare @strQuery varchar(8000)
--Part A
set @StrQuery='delete openquery('
set @StrQuery=@StrQuery+@LinkedServer
set @StrQuery=@StrQuery+','
set @StrQuery=@StrQuery+''' select * from '
set @StrQuery=@StrQuery+@RemoteTableName
set @StrQuery=@StrQuery+''' )'
EXEC(@StrQuery)
--delete openquery(INTERFLOW02,'select * from Inventory.dbo.transmissions')
--get dynamic comma delimited list of the columns in the table (alphabetized order)
declare @list varchar(100)
declare @sql varchar(1000)
select @list=coalesce(@list+',','')+name
from dbo.syscolumns
where object_name(id) = @LocalTableName
--'Transmissions' --and autoval is null
--Active,Description,SortOrder,TransmissionId
--create a temp table that will store the insert statments
--drop table #Stmt
CREATE TABLE #Stmt (descr varchar(1000) NOT NULL )
--string the insert into the temp table together
--because I want to dynamically reference the columns in the remote table,
--the remote table itself and the local table to pull data from
--Although I pull the columns dyamically above when I set @list
--I manually put the columns in below, perhaps a better/dynamic way?
set @sql = 'insert into #Stmt
'
set @sql = @sql+'select ''insert openquery('+@LinkedServer
set @sql = @sql+' ''''select '
set @sql = @sql+@RemoteTableName
set @sql = @sql+' where 1=0'''' '
set @sql = @sql+' ''+cast(active as varchar)+'
set @sql = @sql+' ''''''+description+'
set @sql = @sql+'''+cast(sortorder as varchar)+'''
set @sql = @sql+'''+cast(transmissionid as varchar)+'''
from '
set @sql = @sql+@LocalTableName
exec(@sql)
--This is an example statment that is stored in @Stmt.descr
--insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(0,'None',0,1)
--Now, use this undocumented command to execute the query result set
--This sproc calls master.dbo.sp_execresultset (so alter with ansi null ON)
--to avoid this error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
--options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
exec IFexecresultset "select descr from #Stmt","Inventory"
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
thanks for that
i guess that the main benefit of a sproc is that it does not interupt a write option - ie the insert just happens - and the data is grabbed later. this is a good idea and one i may investigate should triggers prove to be not the ideal solution.
cheers
-
Nicely done, Kim.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Okay, I see I missed something. First, Ninja's RGR'us pointed out I needed to make my own verion of master.dbo.sp_execresultset instead of modifying it, in order to keep the warranty intact. Point taken. So, I included that sproc in this post.
And I realized my original post called IFexecresultset (which called master.dbo.sp_execresultset), I eliminated that.
Thanks.
--exec DTSCopyInventoryLookup 'INTERFLOW02','Inventory.dbo.Transmissions','Transmissions'
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE DTSCopyInventoryLookup
@LinkedServer varchar(100),
@RemoteTableName varchar(100),
@LocalTableName varchar(100)
AS
declare @strQuery varchar(8000)
set @StrQuery='delete openquery('
set @StrQuery=@StrQuery+@LinkedServer
set @StrQuery=@StrQuery+','
set @StrQuery=@StrQuery+''' select * from '
set @StrQuery=@StrQuery+@RemoteTableName
set @StrQuery=@StrQuery+''' )'
EXEC(@StrQuery)
--get dynamic comma delimited list of the columns in the table (alphabetized order)
declare @list varchar(100)
declare @sql varchar(1000)
select @list=coalesce(@list+',','')+name
from dbo.syscolumns
where object_name(id) = @LocalTableName
--and autoval is null -- to avoid identity fields
--create a temp table that will store the insert statments
--drop table #Stmt
CREATE TABLE #Stmt (descr varchar(1000) NOT NULL )
--string the insert into the temp table together
--because I want to dynamically reference the columns in the remote table,
--the remote table itself and the local table itself to pull data out
--Although I pull the columns dyamically above when I set @list
--I manually put the columns in below, perhaps a better/dynamic way?
set @sql = 'insert into #Stmt
'
set @sql = @sql+'select ''insert openquery('+@LinkedServer
set @sql = @sql+' ''''select '
set @sql = @sql+@RemoteTableName
set @sql = @sql+' where 1=0'''' '
set @sql = @sql+' ''+cast(active as varchar)+'
set @sql = @sql+' ''''''+description+'
set @sql = @sql+'''+cast(sortorder as varchar)+'''
set @sql = @sql+'''+cast(transmissionid as varchar)+'''
from '
set @sql = @sql+@LocalTableName
exec(@sql)
--This is an example statment that is stored in @Stmt.descr
--insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(0,'None',0,1)
--Now, execute the statements stored in the temp table
--using my own version of master.dbo.sp_execresultset to keep the MS warranty and support on the server
declare @retcode bit,@proc nvarchar(4000)
select @proc = quotename('Inventory') + N'.dbo.IFexecresultset'
exec @retcode = @proc 'select descr from #Stmt', 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/* A copy of master.dbo.xp_execresultset, but ALTER with ansi_nulls on
to avoid this error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
*/
CREATE procedure dbo.IFexecresultset
(
@cmd nvarchar(4000), -- Command that will return a resultset to concat and execute
@debug bit = 0 -- Display debug information and resultset query (no exec)
)
as
begin
set nocount on
declare @MAXBUFFLEN int,
@qPosition int,
@qValue nvarchar(4000),
@scriptLen int,
@scriptText nvarchar(4000),
@maxIndex bigint,
@rowIndex bigint
-- declare 250 nvarchar(4000) (nearly 1MB worth of space)
declare @q1 nvarchar(4000), @q2 nvarchar(4000), @q3 nvarchar(4000), @q4 nvarchar(4000), @q5 nvarchar(4000), @q6 nvarchar(4000), @q7 nvarchar(4000), @q8 nvarchar(4000), @q9 nvarchar(4000), @q10 nvarchar(4000),
@q11 nvarchar(4000), @q12 nvarchar(4000), @q13 nvarchar(4000), @q14 nvarchar(4000), @q15 nvarchar(4000), @q16 nvarchar(4000), @q17 nvarchar(4000), @q18 nvarchar(4000), @q19 nvarchar(4000), @q20 nvarchar(4000),
@q21 nvarchar(4000), @q22 nvarchar(4000), @q23 nvarchar(4000), @q24 nvarchar(4000), @q25 nvarchar(4000), @q26 nvarchar(4000), @q27 nvarchar(4000), @q28 nvarchar(4000), @q29 nvarchar(4000), @q30 nvarchar(4000),
@q31 nvarchar(4000), @q32 nvarchar(4000), @q33 nvarchar(4000), @q34 nvarchar(4000), @q35 nvarchar(4000), @q36 nvarchar(4000), @q37 nvarchar(4000), @q38 nvarchar(4000), @q39 nvarchar(4000), @q40 nvarchar(4000),
@q41 nvarchar(4000), @q42 nvarchar(4000), @q43 nvarchar(4000), @q44 nvarchar(4000), @q45 nvarchar(4000), @q46 nvarchar(4000), @q47 nvarchar(4000), @q48 nvarchar(4000), @q49 nvarchar(4000), @q50 nvarchar(4000),
@q51 nvarchar(4000), @q52 nvarchar(4000), @q53 nvarchar(4000), @q54 nvarchar(4000), @q55 nvarchar(4000), @q56 nvarchar(4000), @q57 nvarchar(4000), @q58 nvarchar(4000), @q59 nvarchar(4000), @q60 nvarchar(4000),
@q61 nvarchar(4000), @q62 nvarchar(4000), @q63 nvarchar(4000), @q64 nvarchar(4000), @q65 nvarchar(4000), @q66 nvarchar(4000), @q67 nvarchar(4000), @q68 nvarchar(4000), @q69 nvarchar(4000), @q70 nvarchar(4000),
@q71 nvarchar(4000), @q72 nvarchar(4000), @q73 nvarchar(4000), @q74 nvarchar(4000), @q75 nvarchar(4000), @q76 nvarchar(4000), @q77 nvarchar(4000), @q78 nvarchar(4000), @q79 nvarchar(4000), @q80 nvarchar(4000),
@q81 nvarchar(4000), @q82 nvarchar(4000), @q83 nvarchar(4000), @q84 nvarchar(4000), @q85 nvarchar(4000), @q86 nvarchar(4000), @q87 nvarchar(4000), @q88 nvarchar(4000), @q89 nvarchar(4000), @q90 nvarchar(4000),
@q91 nvarchar(4000), @q92 nvarchar(4000), @q93 nvarchar(4000), @q94 nvarchar(4000), @q95 nvarchar(4000), @q96 nvarchar(4000), @q97 nvarchar(4000), @q98 nvarchar(4000), @q99 nvarchar(4000), @q100 nvarchar(4000),
@q101 nvarchar(4000), @q102 nvarchar(4000), @q103 nvarchar(4000), @q104 nvarchar(4000), @q105 nvarchar(4000), @q106 nvarchar(4000), @q107 nvarchar(4000), @q108 nvarchar(4000), @q109 nvarchar(4000), @q110 nvarchar(4000),
@q111 nvarchar(4000), @q112 nvarchar(4000), @q113 nvarchar(4000), @q114 nvarchar(4000), @q115 nvarchar(4000), @q116 nvarchar(4000), @q117 nvarchar(4000), @q118 nvarchar(4000), @q119 nvarchar(4000), @q120 nvarchar(4000),
@q121 nvarchar(4000), @q122 nvarchar(4000), @q123 nvarchar(4000), @q124 nvarchar(4000), @q125 nvarchar(4000), @q126 nvarchar(4000), @q127 nvarchar(4000), @q128 nvarchar(4000), @q129 nvarchar(4000), @q130 nvarchar(4000),
@q131 nvarchar(4000), @q132 nvarchar(4000), @q133 nvarchar(4000), @q134 nvarchar(4000), @q135 nvarchar(4000), @q136 nvarchar(4000), @q137 nvarchar(4000), @q138 nvarchar(4000), @q139 nvarchar(4000), @q140 nvarchar(4000),
@q141 nvarchar(4000), @q142 nvarchar(4000), @q143 nvarchar(4000), @q144 nvarchar(4000), @q145 nvarchar(4000), @q146 nvarchar(4000), @q147 nvarchar(4000), @q148 nvarchar(4000), @q149 nvarchar(4000), @q150 nvarchar(4000),
@q151 nvarchar(4000), @q152 nvarchar(4000), @q153 nvarchar(4000), @q154 nvarchar(4000), @q155 nvarchar(4000), @q156 nvarchar(4000), @q157 nvarchar(4000), @q158 nvarchar(4000), @q159 nvarchar(4000), @q160 nvarchar(4000),
@q161 nvarchar(4000), @q162 nvarchar(4000), @q163 nvarchar(4000), @q164 nvarchar(4000), @q165 nvarchar(4000), @q166 nvarchar(4000), @q167 nvarchar(4000), @q168 nvarchar(4000), @q169 nvarchar(4000), @q170 nvarchar(4000),
@q171 nvarchar(4000), @q172 nvarchar(4000), @q173 nvarchar(4000), @q174 nvarchar(4000), @q175 nvarchar(4000), @q176 nvarchar(4000), @q177 nvarchar(4000), @q178 nvarchar(4000), @q179 nvarchar(4000), @q180 nvarchar(4000),
@q181 nvarchar(4000), @q182 nvarchar(4000), @q183 nvarchar(4000), @q184 nvarchar(4000), @q185 nvarchar(4000), @q186 nvarchar(4000), @q187 nvarchar(4000), @q188 nvarchar(4000), @q189 nvarchar(4000), @q190 nvarchar(4000),
@q191 nvarchar(4000), @q192 nvarchar(4000), @q193 nvarchar(4000), @q194 nvarchar(4000), @q195 nvarchar(4000), @q196 nvarchar(4000), @q197 nvarchar(4000), @q198 nvarchar(4000), @q199 nvarchar(4000), @q200 nvarchar(4000),
@q201 nvarchar(4000), @q202 nvarchar(4000), @q203 nvarchar(4000), @q204 nvarchar(4000), @q205 nvarchar(4000), @q206 nvarchar(4000), @q207 nvarchar(4000), @q208 nvarchar(4000), @q209 nvarchar(4000), @q210 nvarchar(4000),
@q211 nvarchar(4000), @q212 nvarchar(4000), @q213 nvarchar(4000), @q214 nvarchar(4000), @q215 nvarchar(4000), @q216 nvarchar(4000), @q217 nvarchar(4000), @q218 nvarchar(4000), @q219 nvarchar(4000), @q220 nvarchar(4000),
@q221 nvarchar(4000), @q222 nvarchar(4000), @q223 nvarchar(4000), @q224 nvarchar(4000), @q225 nvarchar(4000), @q226 nvarchar(4000), @q227 nvarchar(4000), @q228 nvarchar(4000), @q229 nvarchar(4000), @q230 nvarchar(4000),
@q231 nvarchar(4000), @q232 nvarchar(4000), @q233 nvarchar(4000), @q234 nvarchar(4000), @q235 nvarchar(4000), @q236 nvarchar(4000), @q237 nvarchar(4000), @q238 nvarchar(4000), @q239 nvarchar(4000), @q240 nvarchar(4000),
@q241 nvarchar(4000), @q242 nvarchar(4000), @q243 nvarchar(4000), @q244 nvarchar(4000), @q245 nvarchar(4000), @q246 nvarchar(4000), @q247 nvarchar(4000), @q248 nvarchar(4000), @q249 nvarchar(4000), @q250 nvarchar(4000)
-- initialize all vars
select @q1 = N'', @q2 = N'', @q3 = N'', @q4 = N'', @q5 = N'', @q6 = N'', @q7 = N'', @q8 = N'', @q9 = N'', @q10 = N'',
@q11 = N'', @q12 = N'', @q13 = N'', @q14 = N'', @q15 = N'', @q16 = N'', @q17 = N'', @q18 = N'', @q19 = N'', @q20 = N'',
@q21 = N'', @q22 = N'', @q23 = N'', @q24 = N'', @q25 = N'', @q26 = N'', @q27 = N'', @q28 = N'', @q29 = N'', @q30 = N'',
@q31 = N'', @q32 = N'', @q33 = N'', @q34 = N'', @q35 = N'', @q36 = N'', @q37 = N'', @q38 = N'', @q39 = N'', @q40 = N'',
@q41 = N'', @q42 = N'', @q43 = N'', @q44 = N'', @q45 = N'', @q46 = N'', @q47 = N'', @q48 = N'', @q49 = N'', @q50 = N'',
@q51 = N'', @q52 = N'', @q53 = N'', @q54 = N'', @q55 = N'', @q56 = N'', @q57 = N'', @q58 = N'', @q59 = N'', @q60 = N'',
@q61 = N'', @q62 = N'', @q63 = N'', @q64 = N'', @q65 = N'', @q66 = N'', @q67 = N'', @q68 = N'', @q69 = N'', @q70 = N'',
@q71 = N'', @q72 = N'', @q73 = N'', @q74 = N'', @q75 = N'', @q76 = N'', @q77 = N'', @q78 = N'', @q79 = N'', @q80 = N'',
@q81 = N'', @q82 = N'', @q83 = N'', @q84 = N'', @q85 = N'', @q86 = N'', @q87 = N'', @q88 = N'', @q89 = N'', @q90 = N'',
@q91 = N'', @q92 = N'', @q93 = N'', @q94 = N'', @q95 = N'', @q96 = N'', @q97 = N'', @q98 = N'', @q99 = N'', @q100 = N'',
@q101 = N'', @q102 = N'', @q103 = N'', @q104 = N'', @q105 = N'', @q106 = N'', @q107 = N'', @q108 = N'', @q109 = N'', @q110 = N'',
@q111 = N'', @q112 = N'', @q113 = N'', @q114 = N'', @q115 = N'', @q116 = N'', @q117 = N'', @q118 = N'', @q119 = N'', @q120 = N'',
@q121 = N'', @q122 = N'', @q123 = N'', @q124 = N'', @q125 = N'', @q126 = N'', @q127 = N'', @q128 = N'', @q129 = N'', @q130 = N'',
@q131 = N'', @q132 = N'', @q133 = N'', @q134 = N'', @q135 = N'', @q136 = N'', @q137 = N'', @q138 = N'', @q139 = N'', @q140 = N'',
@q141 = N'', @q142 = N'', @q143 = N'', @q144 = N'', @q145 = N'', @q146 = N'', @q147 = N'', @q148 = N'', @q149 = N'', @q150 = N'',
@q151 = N'', @q152 = N'', @q153 = N'', @q154 = N'', @q155 = N'', @q156 = N'', @q157 = N'', @q158 = N'', @q159 = N'', @q160 = N'',
@q161 = N'', @q162 = N'', @q163 = N'', @q164 = N'', @q165 = N'', @q166 = N'', @q167 = N'', @q168 = N'', @q169 = N'', @q170 = N'',
@q171 = N'', @q172 = N'', @q173 = N'', @q174 = N'', @q175 = N'', @q176 = N'', @q177 = N'', @q178 = N'', @q179 = N'', @q180 = N'',
@q181 = N'', @q182 = N'', @q183 = N'', @q184 = N'', @q185 = N'', @q186 = N'', @q187 = N'', @q188 = N'', @q189 = N'', @q190 = N'',
@q191 = N'', @q192 = N'', @q193 = N'', @q194 = N'', @q195 = N'', @q196 = N'', @q197 = N'', @q198 = N'', @q199 = N'', @q200 = N'',
@q201 = N'', @q202 = N'', @q203 = N'', @q204 = N'', @q205 = N'', @q206 = N'', @q207 = N'', @q208 = N'', @q209 = N'', @q210 = N'',
@q211 = N'', @q212 = N'', @q213 = N'', @q214 = N'', @q215 = N'', @q216 = N'', @q217 = N'', @q218 = N'', @q219 = N'', @q220 = N'',
@q221 = N'', @q222 = N'', @q223 = N'', @q224 = N'', @q225 = N'', @q226 = N'', @q227 = N'', @q228 = N'', @q229 = N'', @q230 = N'',
@q231 = N'', @q232 = N'', @q233 = N'', @q234 = N'', @q235 = N'', @q236 = N'', @q237 = N'', @q238 = N'', @q239 = N'', @q240 = N'',
@q241 = N'', @q242 = N'', @q243 = N'', @q244 = N'', @q245 = N'', @q246 = N'', @q247 = N'', @q248 = N'', @q249 = N'', @q250 = N''
select @MAXBUFFLEN = 4000,
@qPosition = 1,
@qValue = N'',
@scriptLen = 0,
@scriptText = N''
-- check if our scripting table exists, if so let's drop it
if object_id('tempdb..#MSrepl_exec_script', 'U') is not null
begin
drop table #MSrepl_exec_script
end
-- create and load a local tmp table with the resultset based off of the cmd provided
create table #MSrepl_exec_script (orderCol int identity(1,1) primary key clustered, scriptText nvarchar(4000))
if @@error <> 0
begin
goto Error
end
insert into #MSrepl_exec_script(scriptText) exec(@cmd)
if @@error <> 0
begin
goto Error
end
-- get the max value inserted in the identity col for
-- #MSrepl_exec_script table and set the rowIndex = 1
select @maxIndex = max(orderCol),
@rowIndex = 1
from #MSrepl_exec_script
-- retrieve the current scriptText row
select @scriptText = isnull(scriptText, N'')
from #MSrepl_exec_script
where orderCol = @rowIndex
while (@rowIndex <= @maxIndex)
begin
select @scriptLen = @scriptLen + datalength(@scriptText)/2
if (@scriptLen < @MAXBUFFLEN)
begin
-- for this case we continue to append since we know we have room
select @qValue = @qValue + @scriptText + N' '
-- add one to the script length for the space we just added
select @scriptLen = @scriptLen + 1
-- increment the row position counter
select @rowIndex = @rowIndex + 1
-- retrieve the current scriptText row
select @scriptText = isnull(scriptText, N'')
from #MSrepl_exec_script
where orderCol = @rowIndex
-- If there are no more script pieces then we will not continue
-- instead we will set the current @qPosition and the loop will exit
--
-- Also be sure that if we reach the MAXBUFFLEN at this point we do not
-- continue. Instead we want to set the value below first before we continue.
-- FYI: It will never be possible to be greater than MAXBUFFLEN in this section.
if(@rowIndex <= @maxIndex and @scriptLen != @MAXBUFFLEN)
begin
continue
end
end
else if (@scriptLen > @MAXBUFFLEN)
begin
-- here we have exceeded the @MAXBUFFLEN char limit so we will
-- fill the @qValue buffer and then store off the remaining
-- scriptText to be appended on the next itteration of the loop
select @qValue = @qValue + left(@scriptText, @MAXBUFFLEN - datalength(@qValue)/2)
select @scriptText = right(@scriptText, @scriptLen - @MAXBUFFLEN)
end
else
begin
-- Append the entire scriptText since we know we have room. Do not
-- add the command seperator (space) since there is no room for that
select @qValue = @qValue + @scriptText
-- set the scripttext to empty so that on the next loop
-- we will add a space to the first qPostition buffer
select @scriptText = N''
end
-- set one of the 250 vars
-- we need to break this 'else if' list by 100 because there is a limit of nesting levels
-- this is a good idea in any case because it should speed things up a bit
if @qPosition <= 100
begin
if @qPosition = 1 select @q1 = @qValue else if @qPosition = 2 select @q2 = @qValue else if @qPosition = 3 select @q3 = @qValue else if @qPosition = 4 select @q4 = @qValue else if @qPosition = 5 select @q5 = @qValue else if @qPosition = 6 select @q6 = @qValue else if @qPosition = 7 select @q7 = @qValue else if @qPosition = 8 select @q8 = @qValue else if @qPosition = 9 select @q9 = @qValue else if @qPosition = 10 select @q10 = @qValue
else if @qPosition = 11 select @q11 = @qValue else if @qPosition = 12 select @q12 = @qValue else if @qPosition = 13 select @q13 = @qValue else if @qPosition = 14 select @q14 = @qValue else if @qPosition = 15 select @q15 = @qValue else if @qPosition = 16 select @q16 = @qValue else if @qPosition = 17 select @q17 = @qValue else if @qPosition = 18 select @q18 = @qValue else if @qPosition = 19 select @q19 = @qValue else if @qPosition = 20 select @q20 = @qValue
else if @qPosition = 21 select @q21 = @qValue else if @qPosition = 22 select @q22 = @qValue else if @qPosition = 23 select @q23 = @qValue else if @qPosition = 24 select @q24 = @qValue else if @qPosition = 25 select @q25 = @qValue else if @qPosition = 26 select @q26 = @qValue else if @qPosition = 27 select @q27 = @qValue else if @qPosition = 28 select @q28 = @qValue else if @qPosition = 29 select @q29 = @qValue else if @qPosition = 30 select @q30 = @qValue
else if @qPosition = 31 select @q31 = @qValue else if @qPosition = 32 select @q32 = @qValue else if @qPosition = 33 select @q33 = @qValue else if @qPosition = 34 select @q34 = @qValue else if @qPosition = 35 select @q35 = @qValue else if @qPosition = 36 select @q36 = @qValue else if @qPosition = 37 select @q37 = @qValue else if @qPosition = 38 select @q38 = @qValue else if @qPosition = 39 select @q39 = @qValue else if @qPosition = 40 select @q40 = @qValue
else if @qPosition = 41 select @q41 = @qValue else if @qPosition = 42 select @q42 = @qValue else if @qPosition = 43 select @q43 = @qValue else if @qPosition = 44 select @q44 = @qValue else if @qPosition = 45 select @q45 = @qValue else if @qPosition = 46 select @q46 = @qValue else if @qPosition = 47 select @q47 = @qValue else if @qPosition = 48 select @q48 = @qValue else if @qPosition = 49 select @q49 = @qValue else if @qPosition = 50 select @q50 = @qValue
else if @qPosition = 51 select @q51 = @qValue else if @qPosition = 52 select @q52 = @qValue else if @qPosition = 53 select @q53 = @qValue else if @qPosition = 54 select @q54 = @qValue else if @qPosition = 55 select @q55 = @qValue else if @qPosition = 56 select @q56 = @qValue else if @qPosition = 57 select @q57 = @qValue else if @qPosition = 58 select @q58 = @qValue else if @qPosition = 59 select @q59 = @qValue else if @qPosition = 60 select @q60 = @qValue
else if @qPosition = 61 select @q61 = @qValue else if @qPosition = 62 select @q62 = @qValue else if @qPosition = 63 select @q63 = @qValue else if @qPosition = 64 select @q64 = @qValue else if @qPosition = 65 select @q65 = @qValue else if @qPosition = 66 select @q66 = @qValue else if @qPosition = 67 select @q67 = @qValue else if @qPosition = 68 select @q68 = @qValue else if @qPosition = 69 select @q69 = @qValue else if @qPosition = 70 select @q70 = @qValue
else if @qPosition = 71 select @q71 = @qValue else if @qPosition = 72 select @q72 = @qValue else if @qPosition = 73 select @q73 = @qValue else if @qPosition = 74 select @q74 = @qValue else if @qPosition = 75 select @q75 = @qValue else if @qPosition = 76 select @q76 = @qValue else if @qPosition = 77 select @q77 = @qValue else if @qPosition = 78 select @q78 = @qValue else if @qPosition = 79 select @q79 = @qValue else if @qPosition = 80 select @q80 = @qValue
else if @qPosition = 81 select @q81 = @qValue else if @qPosition = 82 select @q82 = @qValue else if @qPosition = 83 select @q83 = @qValue else if @qPosition = 84 select @q84 = @qValue else if @qPosition = 85 select @q85 = @qValue else if @qPosition = 86 select @q86 = @qValue else if @qPosition = 87 select @q87 = @qValue else if @qPosition = 88 select @q88 = @qValue else if @qPosition = 89 select @q89 = @qValue else if @qPosition = 90 select @q90 = @qValue
else if @qPosition = 91 select @q91 = @qValue else if @qPosition = 92 select @q92 = @qValue else if @qPosition = 93 select @q93 = @qValue else if @qPosition = 94 select @q94 = @qValue else if @qPosition = 95 select @q95 = @qValue else if @qPosition = 96 select @q96 = @qValue else if @qPosition = 97 select @q97 = @qValue else if @qPosition = 98 select @q98 = @qValue else if @qPosition = 99 select @q99 = @qValue else if @qPosition = 100 select @q100 = @qValue
end
else if @qPosition <= 200
begin
if @qPosition = 101 select @q101 = @qValue else if @qPosition = 102 select @q102 = @qValue else if @qPosition = 103 select @q103 = @qValue else if @qPosition = 104 select @q104 = @qValue else if @qPosition = 105 select @q105 = @qValue else if @qPosition = 106 select @q106 = @qValue else if @qPosition = 107 select @q107 = @qValue else if @qPosition = 108 select @q108 = @qValue else if @qPosition = 109 select @q109 = @qValue else if @qPosition = 110 select @q110 = @qValue
else if @qPosition = 111 select @q111 = @qValue else if @qPosition = 112 select @q112 = @qValue else if @qPosition = 113 select @q113 = @qValue else if @qPosition = 114 select @q114 = @qValue else if @qPosition = 115 select @q115 = @qValue else if @qPosition = 116 select @q116 = @qValue else if @qPosition = 117 select @q117 = @qValue else if @qPosition = 118 select @q118 = @qValue else if @qPosition = 119 select @q119 = @qValue else if @qPosition = 120 select @q120 = @qValue
else if @qPosition = 121 select @q121 = @qValue else if @qPosition = 122 select @q122 = @qValue else if @qPosition = 123 select @q123 = @qValue else if @qPosition = 124 select @q124 = @qValue else if @qPosition = 125 select @q125 = @qValue else if @qPosition = 126 select @q126 = @qValue else if @qPosition = 127 select @q127 = @qValue else if @qPosition = 128 select @q128 = @qValue else if @qPosition = 129 select @q129 = @qValue else if @qPosition = 130 select @q130 = @qValue
else if @qPosition = 131 select @q131 = @qValue else if @qPosition = 132 select @q132 = @qValue else if @qPosition = 133 select @q133 = @qValue else if @qPosition = 134 select @q134 = @qValue else if @qPosition = 135 select @q135 = @qValue else if @qPosition = 136 select @q136 = @qValue else if @qPosition = 137 select @q137 = @qValue else if @qPosition = 138 select @q138 = @qValue else if @qPosition = 139 select @q139 = @qValue else if @qPosition = 140 select @q140 = @qValue
else if @qPosition = 141 select @q141 = @qValue else if @qPosition = 142 select @q142 = @qValue else if @qPosition = 143 select @q143 = @qValue else if @qPosition = 144 select @q144 = @qValue else if @qPosition = 145 select @q145 = @qValue else if @qPosition = 146 select @q146 = @qValue else if @qPosition = 147 select @q147 = @qValue else if @qPosition = 148 select @q148 = @qValue else if @qPosition = 149 select @q149 = @qValue else if @qPosition = 150 select @q150 = @qValue
else if @qPosition = 151 select @q151 = @qValue else if @qPosition = 152 select @q152 = @qValue else if @qPosition = 153 select @q153 = @qValue else if @qPosition = 154 select @q154 = @qValue else if @qPosition = 155 select @q155 = @qValue else if @qPosition = 156 select @q156 = @qValue else if @qPosition = 157 select @q157 = @qValue else if @qPosition = 158 select @q158 = @qValue else if @qPosition = 159 select @q159 = @qValue else if @qPosition = 160 select @q160 = @qValue
else if @qPosition = 161 select @q161 = @qValue else if @qPosition = 162 select @q162 = @qValue else if @qPosition = 163 select @q163 = @qValue else if @qPosition = 164 select @q164 = @qValue else if @qPosition = 165 select @q165 = @qValue else if @qPosition = 166 select @q166 = @qValue else if @qPosition = 167 select @q167 = @qValue else if @qPosition = 168 select @q168 = @qValue else if @qPosition = 169 select @q169 = @qValue else if @qPosition = 170 select @q170 = @qValue
else if @qPosition = 171 select @q171 = @qValue else if @qPosition = 172 select @q172 = @qValue else if @qPosition = 173 select @q173 = @qValue else if @qPosition = 174 select @q174 = @qValue else if @qPosition = 175 select @q175 = @qValue else if @qPosition = 176 select @q176 = @qValue else if @qPosition = 177 select @q177 = @qValue else if @qPosition = 178 select @q178 = @qValue else if @qPosition = 179 select @q179 = @qValue else if @qPosition = 180 select @q180 = @qValue
else if @qPosition = 181 select @q181 = @qValue else if @qPosition = 182 select @q182 = @qValue else if @qPosition = 183 select @q183 = @qValue else if @qPosition = 184 select @q184 = @qValue else if @qPosition = 185 select @q185 = @qValue else if @qPosition = 186 select @q186 = @qValue else if @qPosition = 187 select @q187 = @qValue else if @qPosition = 188 select @q188 = @qValue else if @qPosition = 189 select @q189 = @qValue else if @qPosition = 190 select @q190 = @qValue
else if @qPosition = 191 select @q191 = @qValue else if @qPosition = 192 select @q192 = @qValue else if @qPosition = 193 select @q193 = @qValue else if @qPosition = 194 select @q194 = @qValue else if @qPosition = 195 select @q195 = @qValue else if @qPosition = 196 select @q196 = @qValue else if @qPosition = 197 select @q197 = @qValue else if @qPosition = 198 select @q198 = @qValue else if @qPosition = 199 select @q199 = @qValue else if @qPosition = 200 select @q200 = @qValue
end
else
begin
if @qPosition = 201 select @q201 = @qValue else if @qPosition = 202 select @q202 = @qValue else if @qPosition = 203 select @q203 = @qValue else if @qPosition = 204 select @q204 = @qValue else if @qPosition = 205 select @q205 = @qValue else if @qPosition = 206 select @q206 = @qValue else if @qPosition = 207 select @q207 = @qValue else if @qPosition = 208 select @q208 = @qValue else if @qPosition = 209 select @q209 = @qValue else if @qPosition = 210 select @q210 = @qValue
else if @qPosition = 211 select @q211 = @qValue else if @qPosition = 212 select @q212 = @qValue else if @qPosition = 213 select @q213 = @qValue else if @qPosition = 214 select @q214 = @qValue else if @qPosition = 215 select @q215 = @qValue else if @qPosition = 216 select @q216 = @qValue else if @qPosition = 217 select @q217 = @qValue else if @qPosition = 218 select @q218 = @qValue else if @qPosition = 219 select @q219 = @qValue else if @qPosition = 220 select @q220 = @qValue
else if @qPosition = 221 select @q221 = @qValue else if @qPosition = 222 select @q222 = @qValue else if @qPosition = 223 select @q223 = @qValue else if @qPosition = 224 select @q224 = @qValue else if @qPosition = 225 select @q225 = @qValue else if @qPosition = 226 select @q226 = @qValue else if @qPosition = 227 select @q227 = @qValue else if @qPosition = 228 select @q228 = @qValue else if @qPosition = 229 select @q229 = @qValue else if @qPosition = 230 select @q230 = @qValue
else if @qPosition = 231 select @q231 = @qValue else if @qPosition = 232 select @q232 = @qValue else if @qPosition = 233 select @q233 = @qValue else if @qPosition = 234 select @q234 = @qValue else if @qPosition = 235 select @q235 = @qValue else if @qPosition = 236 select @q236 = @qValue else if @qPosition = 237 select @q237 = @qValue else if @qPosition = 238 select @q238 = @qValue else if @qPosition = 239 select @q239 = @qValue else if @qPosition = 240 select @q240 = @qValue
else if @qPosition = 241 select @q241 = @qValue else if @qPosition = 242 select @q242 = @qValue else if @qPosition = 243 select @q243 = @qValue else if @qPosition = 244 select @q244 = @qValue else if @qPosition = 245 select @q245 = @qValue else if @qPosition = 246 select @q246 = @qValue else if @qPosition = 247 select @q247 = @qValue else if @qPosition = 248 select @q248 = @qValue else if @qPosition = 249 select @q249 = @qValue else if @qPosition = 250 select @q250 = @qValue
if @qPosition > 250
begin
raiserror('Internal Error : Command buffer limit of ~1MB has been reached! Resultset will not be executed.', 16, -1)
goto Error
end
end
-- Since we just filled a buffer move the index value by one
-- and reset the current query row value to empty and len to 0
select @qPosition = @qPosition + 1,
@qValue = N'',
@scriptLen = 0
end
if @debug = 0
begin
-- concat all buffers and execute
exec(@q1+@q2+@q3+@q4+@q5+@q6+@q7+@q8+@q9+@q10+
@q11+@q12+@q13+@q14+@q15+@q16+@q17+@q18+@q19+@q20+
@q21+@q22+@q23+@q24+@q25+@q26+@q27+@q28+@q29+@q30+
@q31+@q32+@q33+@q34+@q35+@q36+@q37+@q38+@q39+@q40+
@q41+@q42+@q43+@q44+@q45+@q46+@q47+@q48+@q49+@q50+
@q51+@q52+@q53+@q54+@q55+@q56+@q57+@q58+@q59+@q60+
@q61+@q62+@q63+@q64+@q65+@q66+@q67+@q68+@q69+@q70+
@q71+@q72+@q73+@q74+@q75+@q76+@q77+@q78+@q79+@q80+
@q81+@q82+@q83+@q84+@q85+@q86+@q87+@q88+@q89+@q90+
@q91+@q92+@q93+@q94+@q95+@q96+@q97+@q98+@q99+@q100+
@q101+@q102+@q103+@q104+@q105+@q106+@q107+@q108+@q109+@q110+
@q111+@q112+@q113+@q114+@q115+@q116+@q117+@q118+@q119+@q120+
@q121+@q122+@q123+@q124+@q125+@q126+@q127+@q128+@q129+@q130+
@q131+@q132+@q133+@q134+@q135+@q136+@q137+@q138+@q139+@q140+
@q141+@q142+@q143+@q144+@q145+@q146+@q147+@q148+@q149+@q150+
@q151+@q152+@q153+@q154+@q155+@q156+@q157+@q158+@q159+@q160+
@q161+@q162+@q163+@q164+@q165+@q166+@q167+@q168+@q169+@q170+
@q171+@q172+@q173+@q174+@q175+@q176+@q177+@q178+@q179+@q180+
@q181+@q182+@q183+@q184+@q185+@q186+@q187+@q188+@q189+@q190+
@q191+@q192+@q193+@q194+@q195+@q196+@q197+@q198+@q199+@q200+
@q201+@q202+@q203+@q204+@q205+@q206+@q207+@q208+@q209+@q210+
@q211+@q212+@q213+@q214+@q215+@q216+@q217+@q218+@q219+@q220+
@q221+@q222+@q223+@q224+@q225+@q226+@q227+@q228+@q229+@q230+
@q231+@q232+@q233+@q234+@q235+@q236+@q237+@q238+@q239+@q240+
@q241+@q242+@q243+@q244+@q245+@q246+@q247+@q248+@q249+@q250)
if @@error <> 0
begin
goto Error
end
end
else
begin
-- print out debug info
select '@q1' = datalength(@q1)/2, '@q2' = datalength(@q2)/2, '@q3' = datalength(@q3)/2, '@q4' = datalength(@q4)/2, '@q5' = datalength(@q5)/2, '@q6' = datalength(@q6)/2, '@q7' = datalength(@q7)/2, '@q8' = datalength(@q8)/2, '@q9' = datalength(@q9)/2, '@q10' = datalength(@q10)/2,
'@q11' = datalength(@q11)/2, '@q12' = datalength(@q12)/2, '@q13' = datalength(@q13)/2, '@q14' = datalength(@q14)/2, '@q15' = datalength(@q15)/2, '@q16' = datalength(@q16)/2, '@q17' = datalength(@q17)/2, '@q18' = datalength(@q18)/2, '@q19' = datalength(@q19)/2, '@q20' = datalength(@q20)/2,
'@q21' = datalength(@q21)/2, '@q22' = datalength(@q22)/2, '@q23' = datalength(@q23)/2, '@q24' = datalength(@q24)/2, '@q25' = datalength(@q25)/2, '@q26' = datalength(@q26)/2, '@q27' = datalength(@q27)/2, '@q28' = datalength(@q28)/2, '@q29' = datalength(@q29)/2, '@q30' = datalength(@q30)/2,
'@q31' = datalength(@q31)/2, '@q32' = datalength(@q32)/2, '@q33' = datalength(@q33)/2, '@q34' = datalength(@q34)/2, '@q35' = datalength(@q35)/2, '@q36' = datalength(@q36)/2, '@q37' = datalength(@q37)/2, '@q38' = datalength(@q38)/2, '@q39' = datalength(@q39)/2, '@q40' = datalength(@q40)/2,
'@q41' = datalength(@q41)/2, '@q42' = datalength(@q42)/2, '@q43' = datalength(@q43)/2, '@q44' = datalength(@q44)/2, '@q45' = datalength(@q45)/2, '@q46' = datalength(@q46)/2, '@q47' = datalength(@q47)/2, '@q48' = datalength(@q48)/2, '@q49' = datalength(@q49)/2, '@q50' = datalength(@q50)/2,
'@q51' = datalength(@q51)/2, '@q52' = datalength(@q52)/2, '@q53' = datalength(@q53)/2, '@q54' = datalength(@q54)/2, '@q55' = datalength(@q55)/2, '@q56' = datalength(@q56)/2, '@q57' = datalength(@q57)/2, '@q58' = datalength(@q58)/2, '@q59' = datalength(@q59)/2, '@q60' = datalength(@q60)/2,
'@q61' = datalength(@q61)/2, '@q62' = datalength(@q62)/2, '@q63' = datalength(@q63)/2, '@q64' = datalength(@q64)/2, '@q65' = datalength(@q65)/2, '@q66' = datalength(@q66)/2, '@q67' = datalength(@q67)/2, '@q68' = datalength(@q68)/2, '@q69' = datalength(@q69)/2, '@q70' = datalength(@q70)/2,
'@q71' = datalength(@q71)/2, '@q72' = datalength(@q72)/2, '@q73' = datalength(@q73)/2, '@q74' = datalength(@q74)/2, '@q75' = datalength(@q75)/2, '@q76' = datalength(@q76)/2, '@q77' = datalength(@q77)/2, '@q78' = datalength(@q78)/2, '@q79' = datalength(@q79)/2, '@q80' = datalength(@q80)/2,
'@q81' = datalength(@q81)/2, '@q82' = datalength(@q82)/2, '@q83' = datalength(@q83)/2, '@q84' = datalength(@q84)/2, '@q85' = datalength(@q85)/2, '@q86' = datalength(@q86)/2, '@q87' = datalength(@q87)/2, '@q88' = datalength(@q88)/2, '@q89' = datalength(@q89)/2, '@q90' = datalength(@q90)/2,
'@q91' = datalength(@q91)/2, '@q92' = datalength(@q92)/2, '@q93' = datalength(@q93)/2, '@q94' = datalength(@q94)/2, '@q95' = datalength(@q95)/2, '@q96' = datalength(@q96)/2, '@q97' = datalength(@q97)/2, '@q98' = datalength(@q98)/2, '@q99' = datalength(@q99)/2, '@q100' = datalength(@q100)/2,
'@q101' = datalength(@q101)/2, '@q102' = datalength(@q102)/2, '@q103' = datalength(@q103)/2, '@q104' = datalength(@q104)/2, '@q105' = datalength(@q105)/2, '@q106' = datalength(@q106)/2, '@q107' = datalength(@q107)/2, '@q108' = datalength(@q108)/2, '@q109' = datalength(@q109)/2, '@q110' = datalength(@q110)/2,
'@q111' = datalength(@q111)/2, '@q112' = datalength(@q112)/2, '@q113' = datalength(@q113)/2, '@q114' = datalength(@q114)/2, '@q115' = datalength(@q115)/2, '@q116' = datalength(@q116)/2, '@q117' = datalength(@q117)/2, '@q118' = datalength(@q118)/2, '@q119' = datalength(@q119)/2, '@q120' = datalength(@q120)/2,
'@q121' = datalength(@q121)/2, '@q122' = datalength(@q122)/2, '@q123' = datalength(@q123)/2, '@q124' = datalength(@q124)/2, '@q125' = datalength(@q125)/2, '@q126' = datalength(@q126)/2, '@q127' = datalength(@q127)/2, '@q128' = datalength(@q128)/2, '@q129' = datalength(@q129)/2, '@q130' = datalength(@q130)/2,
'@q131' = datalength(@q131)/2, '@q132' = datalength(@q132)/2, '@q133' = datalength(@q133)/2, '@q134' = datalength(@q134)/2, '@q135' = datalength(@q135)/2, '@q136' = datalength(@q136)/2, '@q137' = datalength(@q137)/2, '@q138' = datalength(@q138)/2, '@q139' = datalength(@q139)/2, '@q140' = datalength(@q140)/2,
'@q141' = datalength(@q141)/2, '@q142' = datalength(@q142)/2, '@q143' = datalength(@q143)/2, '@q144' = datalength(@q144)/2, '@q145' = datalength(@q145)/2, '@q146' = datalength(@q146)/2, '@q147' = datalength(@q147)/2, '@q148' = datalength(@q148)/2, '@q149' = datalength(@q149)/2, '@q150' = datalength(@q150)/2,
'@q151' = datalength(@q151)/2, '@q152' = datalength(@q152)/2, '@q153' = datalength(@q153)/2, '@q154' = datalength(@q154)/2, '@q155' = datalength(@q155)/2, '@q156' = datalength(@q156)/2, '@q157' = datalength(@q157)/2, '@q158' = datalength(@q158)/2, '@q159' = datalength(@q159)/2, '@q160' = datalength(@q160)/2,
'@q161' = datalength(@q161)/2, '@q162' = datalength(@q162)/2, '@q163' = datalength(@q163)/2, '@q164' = datalength(@q164)/2, '@q165' = datalength(@q165)/2, '@q166' = datalength(@q166)/2, '@q167' = datalength(@q167)/2, '@q168' = datalength(@q168)/2, '@q169' = datalength(@q169)/2, '@q170' = datalength(@q170)/2,
'@q171' = datalength(@q171)/2, '@q172' = datalength(@q172)/2, '@q173' = datalength(@q173)/2, '@q174' = datalength(@q174)/2, '@q175' = datalength(@q175)/2, '@q176' = datalength(@q176)/2, '@q177' = datalength(@q177)/2, '@q178' = datalength(@q178)/2, '@q179' = datalength(@q179)/2, '@q180' = datalength(@q180)/2,
'@q181' = datalength(@q181)/2, '@q182' = datalength(@q182)/2, '@q183' = datalength(@q183)/2, '@q184' = datalength(@q184)/2, '@q185' = datalength(@q185)/2, '@q186' = datalength(@q186)/2, '@q187' = datalength(@q187)/2, '@q188' = datalength(@q188)/2, '@q189' = datalength(@q189)/2, '@q190' = datalength(@q190)/2,
'@q191' = datalength(@q191)/2, '@q192' = datalength(@q192)/2, '@q193' = datalength(@q193)/2, '@q194' = datalength(@q194)/2, '@q195' = datalength(@q195)/2, '@q196' = datalength(@q196)/2, '@q197' = datalength(@q197)/2, '@q198' = datalength(@q198)/2, '@q199' = datalength(@q199)/2, '@q200' = datalength(@q200)/2,
'@q201' = datalength(@q201)/2, '@q202' = datalength(@q202)/2, '@q203' = datalength(@q203)/2, '@q204' = datalength(@q204)/2, '@q205' = datalength(@q205)/2, '@q206' = datalength(@q206)/2, '@q207' = datalength(@q207)/2, '@q208' = datalength(@q208)/2, '@q209' = datalength(@q209)/2, '@q210' = datalength(@q210)/2,
'@q211' = datalength(@q211)/2, '@q212' = datalength(@q212)/2, '@q213' = datalength(@q213)/2, '@q214' = datalength(@q214)/2, '@q215' = datalength(@q215)/2, '@q216' = datalength(@q216)/2, '@q217' = datalength(@q217)/2, '@q218' = datalength(@q218)/2, '@q219' = datalength(@q219)/2, '@q220' = datalength(@q220)/2,
'@q221' = datalength(@q221)/2, '@q222' = datalength(@q222)/2, '@q223' = datalength(@q223)/2, '@q224' = datalength(@q224)/2, '@q225' = datalength(@q225)/2, '@q226' = datalength(@q226)/2, '@q227' = datalength(@q227)/2, '@q228' = datalength(@q228)/2, '@q229' = datalength(@q229)/2, '@q230' = datalength(@q230)/2,
'@q231' = datalength(@q231)/2, '@q232' = datalength(@q232)/2, '@q233' = datalength(@q233)/2, '@q234' = datalength(@q234)/2, '@q235' = datalength(@q235)/2, '@q236' = datalength(@q236)/2, '@q237' = datalength(@q237)/2, '@q238' = datalength(@q238)/2, '@q239' = datalength(@q239)/2, '@q240' = datalength(@q240)/2,
'@q241' = datalength(@q241)/2, '@q242' = datalength(@q242)/2, '@q243' = datalength(@q243)/2, '@q244' = datalength(@q244)/2, '@q245' = datalength(@q245)/2, '@q246' = datalength(@q246)/2, '@q247' = datalength(@q247)/2, '@q248' = datalength(@q248)/2, '@q249' = datalength(@q249)/2, '@q250' = datalength(@q250)/2
select @q1+@q2+@q3+@q4+@q5+@q6+@q7+@q8+@q9+@q10+
@q11+@q12+@q13+@q14+@q15+@q16+@q17+@q18+@q19+@q20+
@q21+@q22+@q23+@q24+@q25+@q26+@q27+@q28+@q29+@q30+
@q31+@q32+@q33+@q34+@q35+@q36+@q37+@q38+@q39+@q40+
@q41+@q42+@q43+@q44+@q45+@q46+@q47+@q48+@q49+@q50+
@q51+@q52+@q53+@q54+@q55+@q56+@q57+@q58+@q59+@q60+
@q61+@q62+@q63+@q64+@q65+@q66+@q67+@q68+@q69+@q70+
@q71+@q72+@q73+@q74+@q75+@q76+@q77+@q78+@q79+@q80+
@q81+@q82+@q83+@q84+@q85+@q86+@q87+@q88+@q89+@q90+
@q91+@q92+@q93+@q94+@q95+@q96+@q97+@q98+@q99+@q100+
@q101+@q102+@q103+@q104+@q105+@q106+@q107+@q108+@q109+@q110+
@q111+@q112+@q113+@q114+@q115+@q116+@q117+@q118+@q119+@q120+
@q121+@q122+@q123+@q124+@q125+@q126+@q127+@q128+@q129+@q130+
@q131+@q132+@q133+@q134+@q135+@q136+@q137+@q138+@q139+@q140+
@q141+@q142+@q143+@q144+@q145+@q146+@q147+@q148+@q149+@q150+
@q151+@q152+@q153+@q154+@q155+@q156+@q157+@q158+@q159+@q160+
@q161+@q162+@q163+@q164+@q165+@q166+@q167+@q168+@q169+@q170+
@q171+@q172+@q173+@q174+@q175+@q176+@q177+@q178+@q179+@q180+
@q181+@q182+@q183+@q184+@q185+@q186+@q187+@q188+@q189+@q190+
@q191+@q192+@q193+@q194+@q195+@q196+@q197+@q198+@q199+@q200+
@q201+@q202+@q203+@q204+@q205+@q206+@q207+@q208+@q209+@q210+
@q211+@q212+@q213+@q214+@q215+@q216+@q217+@q218+@q219+@q220+
@q221+@q222+@q223+@q224+@q225+@q226+@q227+@q228+@q229+@q230+
@q231+@q232+@q233+@q234+@q235+@q236+@q237+@q238+@q239+@q240+
@q241+@q242+@q243+@q244+@q245+@q246+@q247+@q248+@q249+@q250
end
return 0
Error:
return 1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
well all done
i have decided to move away from triggers and developed a series of procedures that are totally dynamic. the main sproc is run as a job every 4 hours and all is good.
what made things really easy was learning about sp_executesql
thanks for your assistance
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply