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 |
|
notes4we
Yak Posting Veteran
90 Posts |
 Posted - 2008-10-14 : 15:03:44
|
| I have a SQL Table that has the email addresses stored in it and the package names also.My table1 looks like this:PackageName ToEmailID FromEmailIDPName1 notes4we@sql.com notes4us@sql.comPName2 notes4us@sql.com notes4we@sql.comPName3 notes4u@sql.com notes4u@sql.comNow, I am using an Execute SQL Task and in the SQL Statement, I typed an SQL Query:Select ToEmailIDFROM dbo.Table1WHERE PackageName = 'PName2'And this query gives me the desired output, which is notes4us@sql.com But, in the where clause I am trying to use the following System Variable in this query.@[System::PackageName]Do you know anyway I can use this in the SQL Query.I have a number of packages. So, if I am able to use this variable, then I will not have to write the package name seperately for all the packages.Please do let me know if you know someway to achieve this.Thank you. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-14 : 18:53:08
|
| Didn't understand your problem clearly? If you are using System variable for package name then scope of variable will be limited to only current package. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-15 : 04:35:37
|
quote:
Originally posted by notes4we
I have a SQL Table that has the email addresses stored in it and the package names also.My table1 looks like this:PackageName ToEmailID FromEmailIDPName1 notes4we@sql.com notes4us@sql.comPName2 notes4us@sql.com notes4we@sql.comPName3 notes4u@sql.com notes4u@sql.comNow, I am using an Execute SQL Task and in the SQL Statement, I typed an SQL Query:Select ToEmailIDFROM dbo.Table1WHERE PackageName = 'PName2'And this query gives me the desired output, which is notes4us@sql.com But, in the where clause I am trying to use the following System Variable in this query.@[System::PackageName]Do you know anyway I can use this in the SQL Query.I have a number of packages. So, if I am able to use this variable, then I will not have to write the package name seperately for all the packages.Please do let me know if you know someway to achieve this.Thank you.
Use ? as a place holder in your query and map it to system variable in parameters tab. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-15 : 10:32:50
|
quote:
Originally posted by visakh16
quote:
Originally posted by notes4we
I have a SQL Table that has the email addresses stored in it and the package names also.My table1 looks like this:PackageName ToEmailID FromEmailIDPName1 notes4we@sql.com notes4us@sql.comPName2 notes4us@sql.com notes4we@sql.comPName3 notes4u@sql.com notes4u@sql.comNow, I am using an Execute SQL Task and in the SQL Statement, I typed an SQL Query:Select ToEmailIDFROM dbo.Table1WHERE PackageName = 'PName2'And this query gives me the desired output, which is notes4us@sql.com But, in the where clause I am trying to use the following System Variable in this query.@[System::PackageName]Do you know anyway I can use this in the SQL Query.I have a number of packages. So, if I am able to use this variable, then I will not have to write the package name seperately for all the packages.Please do let me know if you know someway to achieve this.Thank you.
Use ? as a place holder in your query and map it to system variable in parameters tab.
But still @[System::PackageName] will only capture current packagename. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-15 : 11:07:48
|
| yup...it should. i thought OP was asking was a way to pass the name as a parameter to sql query in Execute SQL task so that he doesnt have to hardcode package name?? |
|
|
|
notes4we
Yak Posting Veteran
90 Posts |
Posted - 2008-10-15 : 13:42:03
|
| Yes Visakh. It worked and what you answered was the one that I was looking for.Use question mark in sql statement as Select ToEmailIDFROM dbo.Table1WHERE PackageName = ? Go to parameter mapping. Add a parameter. Rename parameter name to 0. Select system variable for package name from the list. And it is done. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-15 : 14:01:04
|
quote:
Originally posted by notes4we
Yes Visakh. It worked and what you answered was the one that I was looking for.Use question mark in sql statement as Select ToEmailIDFROM dbo.Table1WHERE PackageName = ? Go to parameter mapping. Add a parameter. Rename parameter name to 0. Select system variable for package name from the list. And it is done.
Cheers  |
|
|
|
|
|
|
|
|
Using a variable in a SQL Query in an Execute SQL