Dynamic SQL

Dynamic SQL

Post by Konstanti » Wed, 15 May 2002 21:56:28



Hi,

i need to update all rows in table. To accelerate updates i save all table
triggers on in temporary table and try to restore it after updating.
See sample of code:


/*
 CREATE TABLE #T
   (sid UNIQUEIDENTIFIER,
   tbname VARCHAR (255),
   trname VARCHAR (255),
   trtext TEXT)
*/

table #T

UPDATE ......

-- now i try to retrieve trigger text from temp table #T and execute it as
SQL statement



It works only if length of trtext less than 8000.

What can i do if my trigger have size more then 8000 characters?

Konstantin.

 
 
 

Dynamic SQL

Post by Dejan Sark » Wed, 15 May 2002 22:10:45


Konstantin,

You can save the triggers by copying rows from the syscomments system table
in your temp table. There a trigger can span more than one row if the text
is too long. The text in exach row can be read in a memory variable. In the
EXECUTE statement you can concatenate two or more string variables,

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


> Hi,

> i need to update all rows in table. To accelerate updates i save all table
> triggers on in temporary table and try to restore it after updating.
> See sample of code:


> /*
>  CREATE TABLE #T
>    (sid UNIQUEIDENTIFIER,
>    tbname VARCHAR (255),
>    trname VARCHAR (255),
>    trtext TEXT)
> */

temp
> table #T

> UPDATE ......

> -- now i try to retrieve trigger text from temp table #T and execute it as
> SQL statement



> It works only if length of trtext less than 8000.

> What can i do if my trigger have size more then 8000 characters?

> Konstantin.


 
 
 

Dynamic SQL

Post by David Sat » Wed, 15 May 2002 22:11:20


If I understand what you are trying to do, there is no need to drop and
re-create the triggers.  It is not a valid approach since another process
may run at the same time as the one that dropped the trigger and then what ?

Check SQL Server books online under ALTER TABLE, specifically the { ENABLE |
DISABLE } TRIGGER
        { ALL | trigger_name [ ,...n ] } section

--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------

> Hi,

> i need to update all rows in table. To accelerate updates i save all table
> triggers on in temporary table and try to restore it after updating.
> See sample of code:


> /*
>  CREATE TABLE #T
>    (sid UNIQUEIDENTIFIER,
>    tbname VARCHAR (255),
>    trname VARCHAR (255),
>    trtext TEXT)
> */

temp
> table #T

> UPDATE ......

> -- now i try to retrieve trigger text from temp table #T and execute it as
> SQL statement



> It works only if length of trtext less than 8000.

> What can i do if my trigger have size more then 8000 characters?

> Konstantin.

 
 
 

Dynamic SQL

Post by Konstanti » Wed, 15 May 2002 22:38:11


Thank You very much!
It's really i need.



> If I understand what you are trying to do, there is no need to drop and
> re-create the triggers.  It is not a valid approach since another process
> may run at the same time as the one that dropped the trigger and then what
?

> Check SQL Server books online under ALTER TABLE, specifically the { ENABLE
|
> DISABLE } TRIGGER
>         { ALL | trigger_name [ ,...n ] } section

> --
> HTH,
> David Satz
> Principal Web Engineer
> Hyperion Solutions
> { SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
> (Please reply to group only - emails answered rarely)
> -----------------------------------------------------------------


> > Hi,

> > i need to update all rows in table. To accelerate updates i save all
table
> > triggers on in temporary table and try to restore it after updating.
> > See sample of code:


> > /*
> >  CREATE TABLE #T
> >    (sid UNIQUEIDENTIFIER,
> >    tbname VARCHAR (255),
> >    trname VARCHAR (255),
> >    trtext TEXT)
> > */

> temp
> > table #T

> > UPDATE ......

> > -- now i try to retrieve trigger text from temp table #T and execute it
as
> > SQL statement



> > It works only if length of trtext less than 8000.

> > What can i do if my trigger have size more then 8000 characters?

> > Konstantin.

 
 
 

Dynamic SQL

Post by Konstanti » Wed, 15 May 2002 22:45:33


Thanks Dejan!

i know i may put trigger lines into table separately (now i put its as
concatenation of all rows),
but how can i know how much variables i need in EXEC statement

Konstantin.



> Konstantin,

> You can save the triggers by copying rows from the syscomments system
table
> in your temp table. There a trigger can span more than one row if the text
> is too long. The text in exach row can be read in a memory variable. In
the
> EXECUTE statement you can concatenate two or more string variables,

> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org



> > Hi,

> > i need to update all rows in table. To accelerate updates i save all
table
> > triggers on in temporary table and try to restore it after updating.
> > See sample of code:


> > /*
> >  CREATE TABLE #T
> >    (sid UNIQUEIDENTIFIER,
> >    tbname VARCHAR (255),
> >    trname VARCHAR (255),
> >    trtext TEXT)
> > */

> temp
> > table #T

> > UPDATE ......

> > -- now i try to retrieve trigger text from temp table #T and execute it
as
> > SQL statement



> > It works only if length of trtext less than 8000.

> > What can i do if my trigger have size more then 8000 characters?

> > Konstantin.

 
 
 

Dynamic SQL

Post by Martin Krau » Wed, 15 May 2002 22:40:46


What about this.

alter table <TABLE_NAME> disable trigger all
update ...
alter table <TABLE_NAME> enable trigger all

Martin

>-----Original Message-----
>Hi,

>i need to update all rows in table. To accelerate updates
i save all table
>triggers on in temporary table and try to restore it
after updating.
>See sample of code:


>/*
> CREATE TABLE #T
>   (sid UNIQUEIDENTIFIER,
>   tbname VARCHAR (255),
>   trname VARCHAR (255),
>   trtext TEXT)
>*/

triggets into temp
>table #T


>UPDATE ......

>-- now i try to retrieve trigger text from temp table #T
and execute it as
>SQL statement



>It works only if length of trtext less than 8000.

>What can i do if my trigger have size more then 8000
characters?

>Konstantin.

>.

 
 
 

Dynamic SQL

Post by Dejan Sark » Wed, 15 May 2002 22:45:02


Konstantin,

I think it is the colid colum in the syscomments table that show the row
number for an object, so you can use MAX(colid) WHERE id=.... to find the
number of variables needed. Anyway, as you noticed, Dave really understood
what you need.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


> Thanks Dejan!

> i know i may put trigger lines into table separately (now i put its as
> concatenation of all rows),
> but how can i know how much variables i need in EXEC statement

> Konstantin.


> ???Y/???Y ????? ???Y??:


> > Konstantin,

> > You can save the triggers by copying rows from the syscomments system
> table
> > in your temp table. There a trigger can span more than one row if the
text
> > is too long. The text in exach row can be read in a memory variable. In
> the
> > EXECUTE statement you can concatenate two or more string variables,

> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org



> > > Hi,

> > > i need to update all rows in table. To accelerate updates i save all
> table
> > > triggers on in temporary table and try to restore it after updating.
> > > See sample of code:


> > > /*
> > >  CREATE TABLE #T
> > >    (sid UNIQUEIDENTIFIER,
> > >    tbname VARCHAR (255),
> > >    trname VARCHAR (255),
> > >    trtext TEXT)
> > > */

into
> > temp
> > > table #T


> > > UPDATE ......

> > > -- now i try to retrieve trigger text from temp table #T and execute
it
> as
> > > SQL statement



> > > It works only if length of trtext less than 8000.

> > > What can i do if my trigger have size more then 8000 characters?

> > > Konstantin.

 
 
 

Dynamic SQL

Post by Konstanti » Wed, 15 May 2002 23:26:06


Thank You Martin!



> What about this.

> alter table <TABLE_NAME> disable trigger all
> update ...
> alter table <TABLE_NAME> enable trigger all

> Martin

> >-----Original Message-----
> >Hi,

> >i need to update all rows in table. To accelerate updates
> i save all table
> >triggers on in temporary table and try to restore it
> after updating.
> >See sample of code:


> >/*
> > CREATE TABLE #T
> >   (sid UNIQUEIDENTIFIER,
> >   tbname VARCHAR (255),
> >   trname VARCHAR (255),
> >   trtext TEXT)
> >*/

> triggets into temp
> >table #T


> >UPDATE ......

> >-- now i try to retrieve trigger text from temp table #T
> and execute it as
> >SQL statement




> >It works only if length of trtext less than 8000.

> >What can i do if my trigger have size more then 8000
> characters?

> >Konstantin.

> >.

 
 
 

Dynamic SQL

Post by Erland Sommarsko » Thu, 16 May 2002 07:43:29



> If I understand what you are trying to do, there is no need to drop and
> re-create the triggers.  It is not a valid approach since another process
> may run at the same time as the one that dropped the trigger and then what
>?

> Check SQL Server books online under ALTER TABLE, specifically the
> { ENABLE |  DISABLE } TRIGGER
>         { ALL | trigger_name [ ,...n ] } section

A word of caution: also ALTER TABLE DISABLE TRIGGER will affect all
users, not only the process that issues this command.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

1. Dynamic SQL accessing dynamic temp tables

I posted a problem last week about running a dynamically built SQL statement
against a table variable and received a helpfull response from Tobias Thernstrom.
He suggested using a temporary table instead of a table variable as follows :
****************************************************
        Owen,

        The problem is that the EXEC-statement runs in a
        different batch from the proc. itself, this means that any vars. declared
in
        the proc.
        will be out of scope in the batch. So the only change you would need to do
        is
        to create a temp. table instead of a table var.

        Ex (this is your second last script, slitely modified).

        CREATE proc spcompanytest
        as
        CREATE TABLE #tmptable (test_co_no int, flag char)



        company_number = 45 or company_number = 46'


        select company_number, 'Y' from test_company




        Hope this helps !

        /Tobias
****************************************************
The problem I now have is that my stored procedure may be being run many times at
once by different web-users querying the database, I presume that there may well
be contention with the temporary table as it will have the same name for all
procedures. I thought that I might be able to get around the problem by creating
the tablename from the user-id that the user logs into the intranet as, but I get
the same problem as I initally started out with (allbeit from a different angle).
I can't dynamically create a temporary table from a parameter input into the
stored procedure _and_ use it with my dynamicaly built SQL.

Any further help would be a real bonus.

Cheers,

Owen.

2. Newbie Question

3. Dynamic order by without dynamic sql ?

4. Select from @tablename does not work???

5. Dynamic Execute Statement vs. Passing Dynamic SQL

6. sql7+vc+odbc=problem

7. DYNAMIC SQL MVS DB2 V6 DYNAMIC WHERE

8. connect property of data control

9. PL/SQL:Efficiency of inline SQL vs use of Dynamic SQL Package

10. FREE Dynamic SQL and PL/SQL Utilities

11. Dynamic SQL generation in T-SQL

12. more dynamic SQL-Server SQL question