Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JHCOLVI
Starting Member
13 Posts |
 Posted - 2008-08-27 : 19:01:34
|
| Hi I am new to SQL Server 2005.I am pulling the same data from 7 different databases. The only thing different in my SQL code is the database name. Instead of making up 7 different SQL packages, I built a stored procedure using the database name as a variable. I set up a For Loop Container with an Execute SQL Task Container inside it to run the stored procedure. I also built a counter variable (@icounter) in the For Loop Container to run the SQL Task seven times. I have built a User Variable for the database name (@Q_name) and added to the parameter mapping in SQL Task. But how do I change the value of the @Q_name for each time it runs thru the SQL Task?I tried expressions, but had problem with assigning the property. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-28 : 05:15:50
|
| Use a For Each Item enumerator and give list of databases in it.Map your varaibles to this collection of values created in varaible mapping tab. then use this variable for taking databse name in execute sql task. |
 |
|
|
JHCOLVI
Starting Member
13 Posts |
Posted - 2008-08-28 : 13:26:31
|
| I am new SQL Server 2005. I looked at the For Each Item enumerator and I am not sure how to add the databases to the collection list and how map the to variable to the collection list. When I tried it, the collection list was setting up columns. I assume the columns were the databases and I put the database name in the columns. When mapping the variable Q_Name (char variable) to collection list (columns), it asked for a index and I put 6 because starting at 0 that will be seven times. One for each database. When I ran it, I got an error message saying 6 can not be assign to the Q_Name because it was the wrong variable type. I am totally confused here. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-28 : 13:54:08
|
| You should give variable value as your database names and that will collection list. |
|
|
|
JHCOLVI
Starting Member
13 Posts |
Posted - 2008-08-28 : 16:23:19
|
| I did put the database names as my variable, but it seems the variable mapping is the problem. It assigning an index (integer) to the value of the variable instead of the database name (string) that I entered in the collection list. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-30 : 10:14:56
|
quote:
Originally posted by JHCOLVI
I am new SQL Server 2005. I looked at the For Each Item enumerator and I am not sure how to add the databases to the collection list and how map the to variable to the collection list. When I tried it, the collection list was setting up columns. I assume the columns were the databases and I put the database name in the columns. When mapping the variable Q_Name (char variable) to collection list (columns), it asked for a index and I put 6 because starting at 0 that will be seven times. One for each database. When I ran it, I got an error message saying 6 can not be assign to the Q_Name because it was the wrong variable type. I am totally confused here.
You need to create only a single column. it will have by default 0 as index. then just type in values(database names) beneath column header row by row. then use your variable and map it to index 0 in variable mapping tab. |
|
|
|
|
|
|
PASSING PARAMETERS TO SQL TASK THRU A FOR LOOP PKG