Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Capturing The Error Description In A Stored Procedure


Capturing The Error Description In A Stored Procedure

Author
Message
Ed Salva
Ed Salva
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 311

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
IsaacGoGo
IsaacGoGo
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
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




user4301
user4301
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
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?





Ed Salva
Ed Salva
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 311

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
Yoav S.
Yoav S.
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
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.


Joh Louis
Joh Louis
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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


gex
gex
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Frances L
Frances L
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 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.


charles evans
charles evans
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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.
Frances L
Frances L
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 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 )


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search