Xp_sendmail Variables

Oct 16, 2006

Is there a way where I can put variables into the subject line of an xp_sendmail execution? I would like to take a column out of the inserted table and put it into the subject line. The only solution I can find is @query which cannot use the inserted table.

View 1 Replies

Variables And Xp_sendmail

May 9, 2002

Goal--I want this script to go through some tables and look at some equipment ID's and whoever has equipment assigned to them I want to send an e-mail to them telling them to bring in the equipment for inventory.

Problem--On line 28 I set my xp_sendmail variable "@query" equal to a select statement. Inside that select statement I refer to a variable @USERNAME from outside of the query. I get a must declare variable error. Is there any way to refer to a variable from outside this select statement?

Any suggestions?

Thanks!!!

SET NOCOUNT ON
go
PRINT 'Determining which users to email ...'
PRINT ' '
go
DECLARE @USERNAME varchar(30)
DECLARE USERNAME_CURSOR CURSOR FOR
select DISTINCT A.UserName from tblMISFixedAssetTable2 A, tblMISFixedAssetTable B
where A.BarCodeID = B.BarCodeID
and A.UserName is not null
and B.MobileEquipment = '-1'
and A.LastUpdate = (Select Max(C.LastUpdate)
FROM tblMISFixedAssetTable2 C
WHERE C.BarCodeID = A.BarCodeID)

OPEN USERNAME_CURSOR
FETCH NEXT FROM USERNAME_CURSOR INTO @USERNAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @USERNAME = RTRIM(@USERNAME)
exec master.dbo.xp_sendmail
@recipients = @USERNAME,
@subject = 'Inventory 2002',
@message = 'Please bring in your Portable/Mobile Equipment',
@query = 'SELECT A.BarCodeID, B.ItemCategory, B.ItemDescription
from MISAsset.dbo.tblMISFixedAssetTable2 A,
MISAsset.dbo.tblMISFixedAssetTable B
where A.BarCodeID = B.BarCodeID
and A.UserName = @USERNAME
and B.MobileEquipment = "-1"
and A.LastUpdate = (Select Max(C.LastUpdate)
FROM MISAsset.dbo.tblMISFixedAssetTable2 C
WHERE C.BarCodeID = A.BarCodeID)'
EXEC ("PRINT 'Emailing the user * " + @USERNAME + " with inventory items.*'")
END
FETCH NEXT FROM USERNAME_CURSOR INTO @USERNAME
END
DEALLOCATE USERNAME_CURSOR
PRINT ' '
PRINT 'Done!'
PRINT ' '
go

View 1 Replies View Related

Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

Sep 4, 2006

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

João

View 8 Replies View Related

How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

Jan 24, 2006

Hi,

I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .

I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).

It seems it does not get the values passed in for those variables. I deployed the package to a sql server.

are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.

exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"

'

thanks,

Guangming