Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Capturing The Error Description In A Stored Procedure Expand / Collapse
Author
Message
Posted Friday, September 23, 2005 9:08 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 11:53 AM
Points: 28, Visits: 310

Very nice,  I was working on something similar a while back.

Was able to get all but the email to work properly.

Inhouse network staff were unable to help, and internet searches yeilded limited results. 

Haven't had time to revisit this. 

Curious if you had any problems with email setup.

 



-- Optimist with experience and still learning
Post #223126
Posted Friday, September 23, 2005 11:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:16 AM
Points: 24, Visits: 51

Hi All,

One common question I get about this article is about the email not working.  The article's example assumes you have a valid, open relay SMTP service running from your client or SQL Server instance machine.

If you don't, here is a code example of referencing a remote SMTP service:

Function Main()
Dim iMsg
Dim objMail 
Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2
set iMsg = CreateObject("CDO.Message")
set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields
With Flds
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
'Change this parameter below to your valid SMPT Server Name
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="10.10.10.99"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10 
    .Update
End With
With iMsg
 Set .Configuration = iConf
.From = "test@test.com"
.To = "test@test.com"
.AddAttachment ( "c:\temp\test.xls")
.Subject="Test Spreadsheet"
.TextBody = "Spreadsheet"
.Send
End With

Set objMail = nothing
Main = DTSTaskExecResult_Success
End Function
 
Best Regards,
 
Joe



Post #223186
Posted Friday, September 23, 2005 11:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 7:00 AM
Points: 18, Visits: 51

Does anybody have any suggestions for those using Exchange Server at the corporate office?

I am informed that we do not therefore use SMTP.

By the way is it possible to get the message number or some thing similar to actually really positively completely tell the message has been sent?

 

 




Post #223198
Posted Friday, September 23, 2005 12:24 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 11:53 AM
Points: 28, Visits: 310

Thanks Joe, 

I have serveral variants of the script,  but JIC I quick tested the script with the appropriate changes.  no luck.  

I need to dig a little deeper into our mail server.   We are using Novell Groupwise and had a network god who liked to tweak settings for fun.  (even to the point that admin only has restricted rights) fortunatly he's no longer with us, but his memory lives on ... 

I need to do some more research on the novell side and lower level settings. 



-- Optimist with experience and still learning
Post #223219
Posted Monday, September 26, 2005 5:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 20, 2014 5:20 AM
Points: 220, Visits: 45

Hi user4301,

To use the exchange server you need to install a MAPI client on the sql server machine (an outlook account of the account that runs your sql server service). look a Books online on the topic of SQLMail. Then there is a dts task of sendmail that uses this outlook account and will send Emails.

Also there is a nice XP in http://www.sqldev.net/xp/xpsmtp.htm that sends emails in SMTP.

its a nice wrapper for the commands of SMTP.

 

yoav.

 

Post #223500
Posted Wednesday, September 28, 2005 1:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 29, 2005 11:31 AM
Points: 1, Visits: 1

Hi all,

I liked the article by Mr. Sack, this is my first interaction with SQL and need help.

See, I have a back-up of two .db files from SQL 2000 that need to get converted onto Excel 2003 then through a citrix connection then transferred into another SQL db...now i only have that back -up file and excel on a standalone Pc any suggestions?  THanks

 

 

 

 

Post #224536
Posted Wednesday, September 28, 2005 11:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 27, 2007 9:52 AM
Points: 14, Visits: 1
In response to "The FinalSQL"'s question:

Yes it is possible to pump data to multiple Tabs in a excel file.
The trick? There is none. Just simply Create Table's for each new tab you require (as you did for the "Author's " tab in the example given. Be sure to specify that the connection you are using is indeed the existing Excel file! Once the table's (or Tabs) have been created, use the Transform Data Task to upload your data into the specified Tab (Indicate which tab you want to uplaod data to by selecting it in the "destination" tab of the Transaform Data Task.

Another nice feature of using teh ActiveX Scripts is that you can format your excel files after the Data Pump has been completed.
(Or you can simply utilise a template as someone has already discussed).

A lot of people seem to have problems with the ActiveX SendMail functionaility. Personnaly I have only used the XP_sendmail function or the "Send Mail Task" option in DTS Designer. This option uses a wizzard of sorts to set-up your send mail task.
Please Note that in all instances of sending mail through a DTS Package that you must have a Mail Service running in SQL Server.


I hope this helps and doesn't confuse you.




gex
Post #224682
Posted Monday, September 25, 2006 6:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 18, 2011 6:11 PM
Points: 355, Visits: 299

I am pretty new to DTS. but I have problem to name the active script and sql connection with the authors table.

I have ACCESS database. I am curious how I can get query export to excel monthly. I have timer set up which the end users launch the application. but it is not reliable.

Thanks.

Post #310811
Posted Monday, September 25, 2006 9:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, Visits: 158
I did this for years at my former job. You can also call an Excel macro which formats the report directly from an active script task before sending.
Post #310958
Posted Tuesday, September 26, 2006 10:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 18, 2011 6:11 PM
Points: 355, Visits: 299

Will you please post your active script task code to call EXCEL macro?

Thanks.

Also I have error to run the DTS. it said syntax error in the drop table or drop index. I also tried to DROP TABLE 'authors'

DROP TABLE 'authors'
 GO
CREATE TABLE `authors` (
`au_id` VarChar (11) ,
`au_lname` VarChar (40) ,
`au_fname` VarChar (20) ,
`phone` VarChar (12) ,
`address` VarChar (40) ,
`city` VarChar (20) ,
`state` VarChar (2) ,
`zip` VarChar (5) ,
`contract` Bit  )

Post #311273
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse