Help sending email

My code below works fine when run from my pc (changed all the values for
obvious reasons). The code is placed inside a DTS task via VBS scripting.
But when I try to run directly from the server where sqlserver is installed,
the script fails.

I have SMTP running, but there is no outlook installed.

Can someone please advise what I am missing.
Thanks
Bob

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "send@.test.com"
objEmail.To = "receive@.test.com"
objEmail.Subject = "TEST SUBJECT"
objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send

set objEmail = nothingHi B

One thing you might try is change

> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
to
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = "2"

quotes around the 2.

I spent several hours a week ago trying the very same thing and that made
all the difference.

Also is \\server\test.csv accessible from the server you are running this
on?

Here is the full text of the DTS Package I wrote. Note that I don't think
all the fields you included are necessary.

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
email_alert "dchristo@.yahoo.com", "George_Bush@.whitehouse.gov","Test
Subject", "Test Body"
Main = DTSTaskExecResult_Success
End Function

Sub email_alert(strTo, strFrom, strSubject, strBody)
Dim iConf 'As CDO.Configuration
Dim imsg 'As CDO.Message
Dim flds

Set imsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

Set flds = iConf.Fields

'The http://schemas.microsoft.com/cdo/configuration/ namespace defines
the majority of fields used to set configurations for various CDO objects.
We set and update the following three fields (SendUsing, SMTP_SERVER, and
TimeOut) of the Configuration object:

With flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
"2"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"smtp-server.mn.rr.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= 10
.Update
End With

Set imsg.Configuration = iConf
imsg.To = strTo
imsg.From = strFrom
imsg.Subject = strSubject
imsg.TextBody = strBody
imsg.AddAttachment "c:\log\myfile.txt"
imsg.Send
End Sub
--
-Dick Christoph

"B" <no_spam@.no_spam.com> wrote in message
news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
> My code below works fine when run from my pc (changed all the values for
> obvious reasons). The code is placed inside a DTS task via VBS scripting.
> But when I try to run directly from the server where sqlserver is
> installed,
> the script fails.
> I have SMTP running, but there is no outlook installed.
> Can someone please advise what I am missing.
> Thanks
> Bob
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "send@.test.com"
> objEmail.To = "receive@.test.com"
> objEmail.Subject = "TEST SUBJECT"
> objEmail.AddAttachment "\\server\test.csv"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserver") = "SERVER_NAME"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpauthenticate") = 1
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusername") = "username"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendpassword") = "userpwd"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
> set objEmail = nothing|||Follow-up to my original post below.

Is it possible for the "objEmail.To" to lookup the values from a sqlserver
table?

At the moment, I type the email address separated by a semi-colon.

TIA~

"B" <no_spam@.no_spam.com> wrote in message
news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
> My code below works fine when run from my pc (changed all the values for
> obvious reasons). The code is placed inside a DTS task via VBS scripting.
> But when I try to run directly from the server where sqlserver is
installed,
> the script fails.
> I have SMTP running, but there is no outlook installed.
> Can someone please advise what I am missing.
> Thanks
> Bob
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "send@.test.com"
> objEmail.To = "receive@.test.com"
> objEmail.Subject = "TEST SUBJECT"
> objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
> set objEmail = nothing|||Hi B,

Well not directly but you could create an ADODB Command, Connection and
Recordset and use the command to return you a recordset from the Database
that would have 1 or many email addresses that you could concatenate
together and stick in the objEmail.To field.

--
-Dick Christoph
"B" <no_spam@.no_spam.com> wrote in message
news:c4mdnfssT45ImT7ZnZ2dnUVZ_r-dnZ2d@.rcn.net...
> Follow-up to my original post below.
> Is it possible for the "objEmail.To" to lookup the values from a sqlserver
> table?
> At the moment, I type the email address separated by a semi-colon.
> TIA~
>
> "B" <no_spam@.no_spam.com> wrote in message
> news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
>> My code below works fine when run from my pc (changed all the values for
>> obvious reasons). The code is placed inside a DTS task via VBS
>> scripting.
>> But when I try to run directly from the server where sqlserver is
> installed,
>> the script fails.
>>
>> I have SMTP running, but there is no outlook installed.
>>
>> Can someone please advise what I am missing.
>> Thanks
>> Bob
>>
>>
>> Set objEmail = CreateObject("CDO.Message")
>>
>> objEmail.From = "send@.test.com"
>> objEmail.To = "receive@.test.com"
>> objEmail.Subject = "TEST SUBJECT"
>> objEmail.AddAttachment "\\server\test.csv"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendusing") = 2
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpserver") = "SERVER_NAME"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpauthenticate") = 1
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendusername") = "username"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendpassword") = "userpwd"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpserverport") = 25
>> objEmail.Configuration.Fields.Update
>> objEmail.Send
>>
>> set objEmail = nothing
>>
>>

Help scripting backup devices

Hi guys

I am relatively new to SQL Server admin and I have been handed a task of creating backup devices for a particular SQL Server which has 204 databases.

Rather than go through and create Full, Incramental and Transaction Log backup devices for each database is ther anyway of doing the following

Create a script to go through and for each database name in 'sysdatabases'

create a folder e.g \sqldata\backup\%databasename%

and within each folder create a backup device called

%databasename%_full
%databasename%_inc
%databasename%_log

Also all these databases are running in 'Simple' recovery mode so obviously I need to change this to 'Full' to enable incramental and log backups - is this possible using the same script.

Hope someone can help as the thought of doing all of this individually for each database scares me silly!!! :)

Thanks in advance for any help

Hanleynot sure if this is an option but could you create a database maintenance plan inwhich the option 'All databases' is set and does the backup?|||I could do that but I need to create the backup devices first, that is my problem, I need to automate the creation of 3 backup devices for each database (240 of them)

:)|||This is 100% untested, but it should give you an idea or two:DECLARE @.cDb sysname

DECLARE zDb CURSOR FOR SELECT
sd.name
FROM master.dbo.sysdatabases AS sd

OPEN zDb
FETCH zDb INTO @.cDb

WHILE 0 = @.@.fetch_status
BEGIN
EXECUTE ('EXECUTE master.dbo.xp_cmdshell ''mkdir z:\sqldata\backup\'
+ @.cDb + '''')

EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_full'', ''z:\sqldata\backup\' + @.cDb + '_full.dmp''')
EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_inc'', ''z:\sqldata\backup\' + @.cDb + '_inc.dmp''')
EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_log'', ''z:\sqldata\backup\' + @.cDb + '_log.dmp''')

EXECUTE ('ALTER DATABASE ' + @.cDb + ' SET RECOVERY FULL')

FETCH zDb INTO @.cDb
END

CLOSE zDb
DEALLOCATE zDb-PatP|||Pat P

Thanks very much for that

I'll give it a try on Monday

Much appreciated

:)|||Pat P

Script was successful, exactly what I was looking for.

Many thanks

Hanley

:) :)

Help required using Variables and Comparing them in SSIS

Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure

are the variables i am populating Mapping them to result set in execute sql task.

This is the criteria for script task to Fail or success

I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.

I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script.
End IF

I've done something similar to this, albeit much simpler.

I created an Execute SQL task to select a single row from a table.

In the SQL Task Editor, General, I specified ResultSet = Single row, SQLSourceType = Direct Input, and entered the Select SQL in the SQLStatement field.

In the Result Set pane, I mapped the Result Names to the Variable Names using 0 for column1, 1 for columnn 2 etc.

Then in the Precedence Constraint Editor, I selected Evaluation Operation = Expression and Constraint, Value = Success, and entered my expression using the Variable Names.

The result of the expression controls the conditional execution of one of the two following data flows

|||

We have done something similar in a script task, you should be able to call the required variables using something similar to below

If Dts.Variables("gvMedSumFileMode".ToString() = "P" && Dts.Variables("gvMedSumFileType").ToString() = "U" then

if cint(Dts.Variables("gvMedSumFileVolume").ToString()) = cint(dts.Variables("gvMedCtrlFileVolume").ToString()) + 1 then

Dts.Task.Result = success

....

....

....

|||

Dev2624 wrote:

Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure

gvMedSumFileMode

gvMedSumFileType

gvMedSumFileVolume

gvMedCtrlFileVolume

gvMedSumSupplementNumber

gvMedCtrlSupplementNumber

are the variables i am populating Mapping them to result set in execute sql task.

This is the criteria for script task to Fail or success

If myvar11("gvMedSumFileMode").ToString = "P" && myVar12("gvMedSumFileType").ToString = "U" Then

If CType(myVar13("gvMedSumFileVolume").Value, Integer) = CType(myVar14("gvMedCtrlFileVolume").Value, Integer) + 1 Then

DTS.Task .Result = success

else

if CType(myVar13("gvMedSumFileVolume").Value, Integer) = CType(myVar14("gvMedCtrlFileVolume").Value, Integer) && CType(myVar15("gvMedSumSupplementNumber").Value, Integer) = CType(myVar16("gvMedCtrlSupplementNumber").Value, Integer) + 1 then

DTS.Task .Result = success

else
DTS.Task .Result = Failure

End if

End if

else

DTS.Task .Result = failure

I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.

I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script.
End IF

I agree with the first answer; using expressions in the precedence constratint is the best way to 'direct' the traffic in your control flow, as they are easier to maintain and implement. Make sure you research on 'Precedence constraints'.|||

When you are using DTS.Variables are you writing them in the main page of script task under readonly Variables or declaring the variables as MyVariables is the right approach, also the Previous reply to this that i have followed the same method and trying to populate the variables , Can you please guide me as to how can we use these as expressions(in my case)

where conditions are for ex if varx="p" && vary="u"

then if varz= varc+1 success

or other case is if varx="p" && vary="u"

then if varz=varc

if varg=varh+1

success

how do we acheive these conditions in expressions.

|||

Also when i am trying to use && to evaluate both the variables at once it is not allowing me to do it , its throwing an error as expression expected .The questions i might ask might be trivial to you but please help me as i am a newbie and learning the steps ..

also is cint is the right word since the variable gvmedsumfilevolume is int in type. and i am trying to compare two integer values in the expression.

|||

Sorry I was getting confised between c# and vb.net. Below is the correct code, the variables that you are using within the script must also be placed in the "ReadOnlyVariables" property on the "Script Task Editor" > "Script" page. They should be in the format

gvMedSumFileMode,gvMedSumFileType,gvMedSumFileVolume",gvMedCtrlFileVolume

Note that there are no gaps between the commas and the next variable. Not sure if this is a bug but we found that it would ignore the remaining variables if spaces were included.

If Dts.Variables("gvMedSumFileMode").Value.ToString() = "P" And Dts.Variables("gvMedSumFileType").Value.ToString = "U" Then

If CInt(Dts.Variables("gvMedSumFileVolume").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlFileVolume").Value.ToString()) + 1 Then

Dts.TaskResult = Dts.Results.Success

Else

If CInt(Dts.Variables("gvMedSumFileVolume").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlFileVolume").Value.ToString()) And _

CInt(Dts.Variables("gvMedSumSupplementNumber").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlSupplementNumber").Value.ToString()) + 1 Then

Dts.TaskResult = Dts.Results.Success

Else

Dts.TaskResult = Dts.Results.Failure

End If

End If

Else

Dts.TaskResult = Dts.Results.Failure

End If

|||

You should be using the variabledispenser... BEWARE :-)

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

|||

This is the code that i am using and the task is failing is this the right way to do this . I am populating the variables using sql direct input and mapping them into the result set.

Variable names have changed and made these changes in the code. Please tell me how to resolve this

MedSumFileMode String

MedSumFileType String

MedSumFileVolume int32 rest all variables are type int32.

|||Dev, please share the error message. That would help, don't you think?

Also, You don't want LockOneForWrite unless you're only working with one variable. In this case you're not, so you'll want to use LockForWrite instead. If you're not writing to the variables, use LockForRead. Search for examples, but for starters:

Code Snippet

...

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

|||

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

when i use this it highlights an error saying too many arguments Public SubLock For Read(variable As String)

|||

If i use Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("MedSumFileMode",vars)
the error goes away. is this the right approach.

ill post back with the error details

|||

Dev2624 wrote:

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

when i use this it highlights an error saying too many arguments Public SubLock For Read(variable As String)

Yep, I'm not quite awake yet this morning.

Code Snippet

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode")
Dts.VariableDispenser.LockForRead("MedSumFileType")
Dts.VariableDispenser.LockForRead("MedSumFileVolume")
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume")
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber")
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber")
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

|||

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)

at ScriptTask_08359122705d4affbf377ddc743eea59.ScriptMain.Main() in dts://Scripts/ScriptTask_08359122705d4affbf377ddc743eea59/ScriptMain:line 30

this is the error .

|||

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode")
Dts.VariableDispenser.LockForRead("MedSumFileType")
Dts.VariableDispenser.LockForRead("MedSumFileVolume")
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume")
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber")
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber")
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

The problem with this method is that the vars collection does not take the string name of the variable it will only take the index of the variable, hence the code becomes difficult to read

If vars(0).Value.ToString = "P" And vars(1).Value.ToString = "U" Then.......