Use Container-level variables in a "Execute Sql Task"

Greetings all

I am pulling out my hair (what little I have!) trying to accomplish what should be a simple task.

I have an "Execute Sql Task" that sends out an email (see attached code and screendumps).  I am trying to use variables to pass values (email addresses, server names, etc) to the task. Sadly, it is not working.  If I substitute the variables for hard-coded stings, then it works - the email is sent and received.

Any thoughts on what I've missed out on?

In advance, thanks!!!

   allanmark
declare @user_name varchar(80)
declare @user_email varchar(80)
declare @support_name varchar(80)
declare @support_email varchar(80)
declare @server_name varchar(80)
declare @server_smtp varchar(80)
declare @message_body varchar(1000)
 
set @message_body = 
'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +
  'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +
  'has been placed on the designated network drive and can be opened in Excel. For further ' +
  'assistance, please contact ' + @support_name + ' at IT support.' +
  '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n'
 
 
exec [CE_Extract].dbo.sp_SQLSMTPMail
	@vcTo = @user_name,
	@vcCC= @support_email,
	@vcSubject = 'BranchTarget Update: Failure! XXX',
	@vcBody = @message_body,
	@vcFrom = 'BranchTarget.Update@realpeople.co.za',
	@vcSMTPServer = @server_smtp,
	@cSendUsing = '2',
	@vcPort = '25',
	@cAuthenticate = '0',
	@vcSenderName = 'BranchTarget.Update@realpeople.co.za',
	@vcServerName = server_name

Open in new window

Var-Definition.jpg
Var-Params.jpg
allanmarkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PedroCGDCommented:
Allan,
Use the SQL profiler to confirm if the data that is being passed using variables is the same data when you pass using hardcoded values.
Give feedback.
regards!!
pedro
www.pedrocgd.blogspot.com
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
allanmarkAuthor Commented:
Hi PedroCGD

I attempted to run the SqlProfiler - have never used it before and was thus total clueless - if you know of a introductory tutorial, I would liek to learn the tool.

In the interim, I played around and discovered that the variable values are not being fed through to the procedure. I hard-coded assignments (see attached)  in my script and then used the parameters - it worked, with the only issue being the \n (which I used to force a newline.

So, my step, I guess, would be to look at what needs to change with the parameters / parameter mapping.


allanmark
declare @user_name varchar(80)
declare @user_email varchar(80)
declare @support_name varchar(80)
declare @support_email varchar(80)
declare @server_name varchar(80)
declare @server_smtp varchar(80)
declare @message_body varchar(1000)
 
set @user_name = 'allanMark'
set @user_email = 'allanMark@gmail.com'
set @support_name = 'Joe Soap'
set @support_email = 'joesoap@gmail.com'
set @server_name = 'itblue'
set @server_smtp = 'smtp.nana.co.za'
 
set @message_body = 
'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +
  'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +
  'has been placed on the designated network drive and can be opened in Excel. For further ' +
  'assistance, please contact ' + @support_name + ' at IT support.' +
  '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n'
 
 
exec [CE_Extract].dbo.sp_SQLSMTPMail
	@vcTo = @user_email,
	@vcCC= @support_email,
	@vcSubject = 'BranchTarget Update: Failure! ZZZ',
	@vcBody = @message_body,
	@vcFrom = 'BranchTarget.Update@realpeople.co.za',
	@vcSMTPServer = @server_smtp,
	@cSendUsing = '2',
	@vcPort = '25',
	@cAuthenticate = '0',
	@vcSenderName = 'BranchTarget.Update@realpeople.co.za',
	@vcServerName = @server_name

Open in new window

0
PedroCGDCommented:
Its not so complicated to use SQL Profiler in this example. :-)
Follow the images and see the query I exexuted and that SQL profiler catched..
helped?
Regards!
Pedro
SQLProfiler1.JPG
SQLProfiler2.JPG
SQLProfiler3.JPG
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Steve HoggITCommented:
Change your parameters to zero based indexes. For the first parameter it will be 0, the second 1, etc..
In your scriipt, use the ? (question mark) to signify a parameter.

declare @user_name varchar(80)
declare @user_email varchar(80)
declare @support_name varchar(80)
declare @support_email varchar(80)
declare @server_name varchar(80)
declare @server_smtp varchar(80)
declare @message_body varchar(1000)
 
set @user_name = ?
set @user_email = ?
set @support_name = ?
set @support_email = ?
set @server_name = ?
set @server_smtp = ?
 
set @message_body = 
'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +
  'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +
  'has been placed on the designated network drive and can be opened in Excel. For further ' +
  'assistance, please contact ' + @support_name + ' at IT support.' +
  '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n'
 
 
exec [CE_Extract].dbo.sp_SQLSMTPMail
	@vcTo = @user_email,
	@vcCC= @support_email,
	@vcSubject = 'BranchTarget Update: Failure! ZZZ',
	@vcBody = @message_body,
	@vcFrom = 'BranchTarget.Update@realpeople.co.za',
	@vcSMTPServer = @server_smtp,
	@cSendUsing = '2',
	@vcPort = '25',
	@cAuthenticate = '0',
	@vcSenderName = 'BranchTarget.Update@realpeople.co.za',
	@vcServerName = @server_name

Open in new window

0
Steve HoggITCommented:
0
Steve HoggITCommented:
What, I uploaded the same picture twice. I never make mistakes. But just in case, here is the second picture.
parameters-mapping.bmp
0
allanmarkAuthor Commented:
Thanks.

Did as you suggested. When I try to "Parse Query" it gives me an error - "The query failed to parse.  Syntax error or access violation."

Wondering what I've missed?
declare @user_name varchar(80);
declare @user_email varchar(80);
declare @support_name varchar(80);
declare @support_email varchar(80);
declare @server_name varchar(80);
declare @server_smtp varchar(80);
declare @message_body varchar(1000);
 
set @user_name = ?;
set @user_email = ?;
set @support_name = ?;
set @support_email = ?;
set @server_name = ?;
set @server_smtp = ?;
 
set @message_body = 
'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +
  'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +
  'has been placed on the designated network drive and can be opened in Excel. For further ' +
  'assistance, please contact ' + @support_name + ' at IT support.' +
  '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n'
 
 
exec [CE_Extract].dbo.sp_SQLSMTPMail
        @vcTo = @user_email,
        @vcCC= @support_email,
        @vcSubject = 'BranchTarget Update: Failure! ZZZ',
        @vcBody = @message_body,
        @vcFrom = 'BranchTarget.Update@realpeople.co.za',
        @vcSMTPServer = @server_smtp,
        @cSendUsing = '2',
        @vcPort = '25',
        @cAuthenticate = '0',
        @vcSenderName = 'BranchTarget.Update@realpeople.co.za',
        @vcServerName = @server_name

Open in new window

Var-Params2.jpg
0
allanmarkAuthor Commented:
Hi!

Don't know if this means anything ... I just noticed that my Parameter Mapping doesn't have the "Parameter Size" coulmn as per Hoggzilla's example.
0
Steve HoggITCommented:
OK, I have a couple things.
First, do you have default values in the variables? That might help it parse.
Second, It looks like your order is off. UserEmail is the 2nd parameter (Index 1) and you have it mapped to Index 2. If this does not work, when I get to the office I will try to duplicate your error.
0
allanmarkAuthor Commented:
Good pickup re the order -- I have corrected.

I inserted default values -- same error.

0
Steve HoggITCommented:
OK, my suggestion is to set the BypassPrepare property in the General window to True. This will stop it from checking the SQL Statement for syntax.
0
allanmarkAuthor Commented:
Did that.

It pass through as expected but crashe don execution with:

[Execute SQL Task] Error: Executing the query "declare @user_name varchar(80); declare @support_name varchar(80); declare @user_email varchar(80); declare @support_email varchar(80); declare @server_name varchar(80); declare @server_smtp varchar(80); declare @message_body varchar(1000);  set @user_name = ?; set @support_name = ?; set @user_email = ?; set @support_email = ?; set @server_name = ?; set @server_smtp = ?;  set @message_body =  'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +   'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +   'has been placed on the designated network drive and can be opened in Excel. For further ' +   'assistance, please contact ' + @support_name + ' at IT support.' +   '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n'   exec [CE_Extract].dbo.sp_SQLSMTPMail       @vcTo = @user_email,       @vcCC= @support_email,       @vcSubject = 'BranchTarget Update: Failure! ZZZ',       @vcBody = @message_body,       @vcFrom = 'BranchTarget.Update@realpeople.co.za',       @vcSMTPServer = @server_smtp,       @cSendUsing = '2',       @vcPort = '25',       @cAuthenticate = '0',       @vcSenderName = 'BranchTarget.Update@realpeople.co.za',       @vcServerName = @server_name " failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Me = seriously confused (and loosing mi\ore hair!!).
0
Steve HoggITCommented:
I got the sql to run and inserted the @message_body into a return variable which I then put to a message box. Attached. Since it is running for me, make sure your EXEC command is syntaxed properly as this is the piece I cannot check.

msgbox-message-body.bmp
0
Steve HoggITCommented:
Here is my code for what I did. Notice the select statement at the end of the message_body. That is there to return a value and populate a variable.
declare @user_name varchar(80);
declare @user_email varchar(80);
declare @support_name varchar(80);
declare @support_email varchar(80);
declare @server_name varchar(80);
declare @server_smtp varchar(80);
declare @message_body varchar(1000);
 
set @user_name = ?;
set @user_email = ?;
set @support_name = ?;
set @support_email = ?;
set @server_name = ?;
set @server_smtp = ?;
 
set @message_body = 
'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +
  'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +
  'has been placed on the designated network drive and can be opened in Excel. For further ' +
  'assistance, please contact ' + @support_name + ' at IT support.' +
  '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n';
 
select @message_body;
 

Open in new window

0
allanmarkAuthor Commented:
Now I am confused! It looks liek my problem is the question mark!!

I copied your code and got the same hassle!! When I remove the question marks and use hard-coding, it works!

0
Steve HoggITCommented:
If you are willing, take your .dtsx file, remove anything sensitive, and paste it here. I will take a look. In the meantime I am posting my example package here for you.
You have to rename the file with a .txt on the end for it to upload. When saving my file, just remove the .txt from the filename. I wish we could upload .dtsx files.
YourPackage.dtsx.txt

PARAMETERS-IN-SQLTASK.dtsx.txt
0
allanmarkAuthor Commented:
Many thanks! have attache dmy package.

I added your package to my application.

"Parse Query" gives teh same hassles as I am getting with mine. If I skip prasing and execute the task, it crashes (see atatched snippet).

I am wondering if it isn't perhaps somethign with my configuration?
[Execute SQL Task] Error: Executing the query "declare @user_name varchar(80); declare @user_email varchar(80); declare @support_name varchar(80); declare @support_email varchar(80); declare @server_name varchar(80); declare @server_smtp varchar(80); declare @message_body varchar(1000);   set @user_name = ?; set @user_email = ?; set @support_name = ?; set @support_email = ?; set @server_name = ?; set @server_smtp = ?;   set @message_body =  'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +   'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +   'has been placed on the designated network drive and can be opened in Excel. For further ' +   'assistance, please contact ' + @support_name + ' at IT support.' +   '\n\n\nMessage sent by:    BranchTarget_Update (SSIS upload package)\n\n';   select @message_body;    " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. 

Open in new window

Package.dtsx.txt
0
Steve HoggITCommented:
I will look right away. Remeber, you need to set Bypass Prepare to True. Because of the Input Variables it cannot fully parse and you will get an error. Notice in my package I set the bypass prepare to True.
0
Steve HoggITCommented:
OK, I see you have the bypassprepare set to True. If you remove the email Exec statement, does it work then? Can you verify all of your variables have values before it executes? Perhaps a NULL in one of the values is causing an issue. You could build some ISNULL(@username,'') logic into the sql. I am really lost after that since it works for me - at least the package I sent to you works for me.
0
allanmarkAuthor Commented:
Hi!

I am stumped!

I created a new project and added your package. With bypassprepare set to false, it fails the Parse. With bypassprepare set to true,  it passes s(obviously, a sit skips it), but the execution fails.

0
allanmarkAuthor Commented:
Still looking!

What I noticed was that the Paramater Mapping  -- your's has a "Parameter Size" column and mine does not.

An issue?
0
allanmarkAuthor Commented:
Another thought!  Could my issues be service pack related - re my previous post - I've since found out that this column was added with SP2.
0
allanmarkAuthor Commented:
GOT IT!!

The answer is Service Pack 2!!

Havign all but given up, I decided to look into why my "Execute Sql Task" parameter Mapping did not have a "Parameter Size". The answer - it was included in Sp2. So, I decided to download SP2 - 282mb on a 384KB ADSL line - took 2hrs to download and then a further hour to install!

I ran your example - it now works!!!

Will tets my Send EMail tonight and post back results.
0
Steve HoggITCommented:
Awesome, great news!
0
allanmarkAuthor Commented:
Thousand apologies. Got side-tracked with a couple of other issues and clean forgot to close this.
0
allanmarkAuthor Commented:
Once again - a thousand apologies

PedroCGD - as always, your commensta re an education
HoggZilla - I kept my hair - thankyou
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.