[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 745
  • Last Modified:

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
0
allanmark
Asked:
allanmark
  • 13
  • 11
  • 2
5 Solutions
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
HoggZillaCommented:
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
 
HoggZillaCommented:
0
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 13
  • 11
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now