| Author |
Topic |
|
killer2002
Starting Member
12 Posts |
 Posted - 2003-03-24 : 02:24:37
|
| Hi there i have a question.In one databse i have one table named "customers" this table is also in three others databases and it gets updated everytime when i run the DTS package.Can the table-name be variable?so any table with different names could run in this same package? |
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2003-03-24 : 02:31:37
|
| Hi Kill,Well table name can be defined in a variable name but u need to use dynamic sql to execute. And your second requirement, am unable to follow. If u cd clarify further.:)RoyalSher |
 |
|
|
killer2002
Starting Member
12 Posts |
Posted - 2003-03-24 : 02:48:16
|
| How can you use dynamic sql ??Do you have maybe an example?? |
|
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-03-24 : 02:57:20
|
| A Simple Example.DECLARE @SQL VARCHAR(1000)DECLARE @TABNAME VARCHAR(25)SET @TABNAME='PUBS.DBO.AUTHORS'SET @SQL = 'SELECT * FROM ' + @TABNAME + ' WHERE STATE = ''TN'''EXEC(@SQL)GOSekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
|
killer2002
Starting Member
12 Posts |
Posted - 2003-03-24 : 02:59:56
|
| thanks,but can you do this too in a DTS package?So lets say i have a table(i don't know the name)and i want that table gets updated in three other databases |
|
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2003-03-24 : 04:45:02
|
Well I dunno, what exactly you wanna do.. ? Probably u can explain the scenario and u might get a better way of doing it. And i believe we can't pass variable values in a DTS package. Never tried but cd be possible SQL - DMO.Try it. Ne one correct me if i am wrong.RoyalSher |
|
|
|
killer2002
Starting Member
12 Posts |
Posted - 2003-03-24 : 04:50:13
|
| Ok i lets say i have a table called "customers" in the database pubs.The table customers gets updated in the database called northwind if i press the execution button in the DTS package(so far everything is working well)But now i wanna create a sort of template from this procedure so i can do the updates to other tables in the database pubsEdited by - killer2002 on 03/24/2003 04:52:10 |
|
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2003-03-24 : 04:58:27
|
| Understanding the basic process as explained by u.. let me put it, that u r adding new values (addition of records) to the tables using dts package or u r updating columns of the existing records.for first assumption of mine, i think u don't need much explanation, (if needn't be [;]), in the designer of package add up the steps but pointing to diff db's and same tables.else for second assumption write a trigger in the table which does the job of updation as itz happening in 'customers' tab ??!!RoyalSher |
|
|
|
killer2002
Starting Member
12 Posts |
Posted - 2003-03-24 : 05:13:35
|
| no i mean something else sorry for being a noob.I want to update tables and send these tables to other databases.But can these names of the tables be variable?And if they can how?? Or have you give the tables a name |
|
|
|
|
Update tables