Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sending mail with extended Stored Proc

Status
Not open for further replies.
Glowworm27

Glowworm27

Programmer
May 30, 2003
587
US
Hey folks,

I have an extended stored proc that can send email.
I put the query in an Execute SQL Task in the DTS designer.

I want to be able to access a global variable so I can tell the person how long it took to run.

below is the query that I am trying to us in the Task. I tryed to use the Question mark so I could access the global variables but it throws an error. Stating that there is a syntax error.

Code: 
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
    @FROM       = N'goakes@tiresplus.com',
    @TO         = N'goakes@tiresplus.com',
    @CC         = N'rbrachmann@tiresplus.com',
    @subject    = N'Daily ReIndexing Completed',
    @message    = N'<HTML><H1>Daily ReIndexing Completed</H1><p>
The Daily ReIndexing Completed: Started at: ' + ? + '
</p></HTML>',
    @type       = N'text/html',
    @server     = N'10.231.1.15'

select RC = @rc 
go

Thanks in advance


George Oakes
Check out this awsome .Net Resource!
 
Sort by date Sort by votes
Does this execute if you don't have the question mark in there (and replace with hard-coded value)? And have you been able to successfully assign your input parameter from your global variable (what is its' data type, you may need a cast?)?

If yes to both, and this is still not working, you may consider using a script task to change the query within the execute SQL task box. I have an example somewhere of this, if you decide to go that route.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Yeah I tested all that, I am now trying to do what you suggest and change the query with the sql task now.

If you can find the example I would appreciate it.
Right now I am working on something similar but the example is for a datapump task. I think I can fiuger it out though....

i hope :)

George Oakes
Check out this awsome .Net Resource!
 
Upvote 0 Downvote
Hey George - Check this out:

Code: 
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Option Explicit

Function Main()
	Dim oPkg, oSQL, sSQLStatement

	' Build new SQL Statement
	sSQLStatement = "SELECT * FROM someTable WHERE SomeColumn = '" & _
		DTSGlobalVariables("@D1").Value & "'"

	' Get reference to the SQL Task
	Set oPkg = DTSGlobalVariables.Parent
	Set oSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask

	' Assign SQL Statement to SQL Task
	oSQL.SQLStatement = sSQLStatement

	' Clean Up
	Set oSQL = Nothing
	Set oPkg = Nothing

	Main = DTSTaskExecResult_Success
End Function

I imagine you can alter this to suit your query farily easily.

Hope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Glad you got it sorted :)

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tekpr00
  • Locked
  • Question
Help with concatenation of BIG integers
Replies
0
Views
154
tekpr00
tekpr00
jeffwest21
  • Locked
  • Question
BCP Out file with double quotes
Replies
1
Views
248
gk53
gk53
ChewDoggie
  • Locked
  • Question
Help with Select statement Substring
Replies
2
Views
162
ChewDoggie
ChewDoggie
Rene1024
  • Locked
  • Question
Help with a Bulk insert statement
Replies
1
Views
193
drlex
drlex
ciarra41
  • Locked
  • Question
Rollup to one record with same order number
Replies
2
Views
171
drlex
drlex

Part and Inventory Search

Sponsor

Back
Top