Soft coding Database connections within SQL Task 1

[MegM]

We have several DTS applications which require the updating of one database from data in another database. In DTS you can only attach to one connection at a time within a SQL Task so you are required to hard code the second database. Is there a way around using hard coded database names.

Any help would be appreciated.

 

[nigelrivett]

You can build the sql statement in an activex task and set the statement in the sql task. The activex task can get the database from a global variable.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MegM]

Doesn't this force the statement to be parsed and recompiled everytime it is executed? Also some of the statements are pretty extensive, is there a limitation of the size of Global Variables.

 

[nigelrivett]

You put the database name in the global variable. The code to build the sql statement is in the activex job.

The code has to be parsed when it executed whatever you do - unless the server has a previous query plan.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MegM]

I will try this.

Thank you!