Execute a large text string
-
Hello!
How could I execute the code I have in a table, wich field type is TEXT?
According to BOL, I can only execute varchars
At the moment I do this ugly code
Please, help
DECLARE
@AuditTriggerCustomized1 varchar(8000) --We use SIXTEEN vars to hold a trigger of up to 128.000 chars
, @AuditTriggerCustomized2 varchar(8000)
, @AuditTriggerCustomized3 varchar(8000) --With 16 vars we can expect to audit up to about 96 fields in a table
, @AuditTriggerCustomized4 varchar(8000) --(with the actual trigger template, every 6 fields in a table uses to fill one full varchar)
, @AuditTriggerCustomized5 varchar(8000)
, @AuditTriggerCustomized6 varchar(8000)
, @AuditTriggerCustomized7 varchar(8000)
, @AuditTriggerCustomized8 varchar(8000)
, @AuditTriggerCustomized9 varchar(8000)
, @AuditTriggerCustomized10 varchar(8000)
, @AuditTriggerCustomized11 varchar(8000)
, @AuditTriggerCustomized12 varchar(8000)
, @AuditTriggerCustomized13 varchar(8000)
, @AuditTriggerCustomized14 varchar(8000)
, @AuditTriggerCustomized15 varchar(8000)
, @AuditTriggerCustomized16 varchar(8000)
[............]
SET @AuditTriggerCustomized1 = (select cast(SUBSTRING(Code, 8000*0+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized2 = (select cast(SUBSTRING(Code, 8000*1+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized3 = (select cast(SUBSTRING(Code, 8000*2+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized4 = (select cast(SUBSTRING(Code, 8000*3+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized5 = (select cast(SUBSTRING(Code, 8000*4+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized6 = (select cast(SUBSTRING(Code, 8000*5+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized7 = (select cast(SUBSTRING(Code, 8000*6+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized8 = (select cast(SUBSTRING(Code, 8000*7+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized9 = (select cast(SUBSTRING(Code, 8000*8+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized10 = (select cast(SUBSTRING(Code, 8000*9+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized11 = (select cast(SUBSTRING(Code, 8000*10+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized12 = (select cast(SUBSTRING(Code, 8000*11+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized13 = (select cast(SUBSTRING(Code, 8000*12+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized14 = (select cast(SUBSTRING(Code, 8000*13+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized15 = (select cast(SUBSTRING(Code, 8000*14+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
SET @AuditTriggerCustomized16 = (select cast(SUBSTRING(Code, 8000*15+1, 8000) as varchar(8000)) from tmp_AuditTriggerCustomized)
PRINT ' - [sp_CreateAuditTrigger] Audit Trigger Customized: '
PRINT @AuditTriggerCustomized1
PRINT @AuditTriggerCustomized2
PRINT @AuditTriggerCustomized3
PRINT @AuditTriggerCustomized4
PRINT @AuditTriggerCustomized5
PRINT @AuditTriggerCustomized6
PRINT @AuditTriggerCustomized7
PRINT @AuditTriggerCustomized8
PRINT @AuditTriggerCustomized9
PRINT @AuditTriggerCustomized10
PRINT @AuditTriggerCustomized11
PRINT @AuditTriggerCustomized12
PRINT @AuditTriggerCustomized13
PRINT @AuditTriggerCustomized14
PRINT @AuditTriggerCustomized15
PRINT @AuditTriggerCustomized16
EXECUTE ( @AuditTriggerCustomized1 +
@AuditTriggerCustomized2 +
@AuditTriggerCustomized3 +
@AuditTriggerCustomized4 +
@AuditTriggerCustomized5 +
@AuditTriggerCustomized6 +
@AuditTriggerCustomized7 +
@AuditTriggerCustomized8 +
@AuditTriggerCustomized9 +
@AuditTriggerCustomized10 +
@AuditTriggerCustomized11 +
@AuditTriggerCustomized12 +
@AuditTriggerCustomized13 +
@AuditTriggerCustomized14 +
@AuditTriggerCustomized15 +
@AuditTriggerCustomized16 )
-
Me again
I also would like to know, how does the memory handles that many declared varchars.
Would the server allocate memory for the full 8000 chars in a varchar, despite of I don't even try to fill it ?
I know that varchar fields in a table won't be allocated if not used, but what about variables in a stored procedure.
Nice to hear from you all!
Kind regards
-
Nobody?
-
maybe use a cursor to concat into a varchar() variable
-
Download that text field in a local file, let say mycode.sql.
Call isql for example to execute mycode.sql.
Example:
isql /i D:\APP_CODE\control\mycode.sql /Server /Usa /P > "D:\APP_CODE\control\mycodelog.txt"
George
-
This is the method that I've used to do this.
Using a cursor concatenate the varchar() field won't work because of the 8000 character limit on varchar fields.
Any method involving temporary files is unstable and insecure.
-
...and, if you do write the TEXT out to a text file for execution, use OSQL instead of ISQL because ISQL is deprecated compared to OSQL.
Just an opinion but both your original code and the method of writing the text to a file and executing OSQL are good methods. In fact, I'll use OSQL to write the text file and then execute it. Haven't had any problems with stability, yet. So far as security goes, if it's setup as a job to be run from the server under an SA proxy setup by the DBA's, you can do the whole shootin' match from the job using trusted conncections/xp_CmdShell and security is never compromised (or so my DBA's have told me
)--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) -
thank you
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply