Scheduling Simple Cdosys Email Task Does Not Work
Feb 26, 2007
I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis. The email is sent using an activex script in a "SQL 2000 DTS Package Task". When executed manually, the email is sent ok. When scheduled (and run under our SQL agent account), it fails. Can anyone point me in the right direction? Is this a permissions issue?
'-- this script seems to cause problems, but only when scheduled --
dim mailer
set mailer = CreateObject("CDO.Message")
dim cdoconfig
const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
set cdoconfig = CreateObject("CDO.Configuration")
with mailer
set .Configuration = cdoconfig
.BodyPart.charset = "unicode-1-1-utf-8"
.BodyPart.ContentTransferEncoding = "quoted-printable"
.Fields("urn:schemas:httpmail:importance").Value = 2
.Fields.Update
.Subject = "Notification"
.From = "donotreply@test.com"
.TextBody = "TEST"
.Bcc = "someone@test.com"
.Send
end with
'-------------
Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.
Any help would be appreciated.
View 2 Replies
ADVERTISEMENT
Feb 8, 2005
This was a P A I N to get working. Maybe someone else here is sending email and could use it.
A UNICODE Send Mail using CDOSYS with ReadReceipt and Importance...
Sub SendMail (sFromAddress, sToAddress, sCcAddress, sBccAddress, sSubject, sBody, boolReadReceipt, intImportance )
'on error resume next
Const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
Const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
dim cdoMessage, cdoConfiguration
Set cdoConfiguration = Server.CreateObject ("CDO.Configuration")
' Outgoing SMTP server
With cdoConfiguration
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Fields.Update
End With
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
' Update the CDOSYS Configuration
SET .Configuration = cdoConfiguration
.BodyPart.charset = "unicode-1-1-utf-8"
IF boolReadReceipt Then
.Fields(cdoDispositionNotificationTo)= sFromAddress
.Fields(cdoReturnReceiptTo)= sFromAddress
End If
' Set the Importance: 0:Low, 1:Normal, 2:High
.Fields("urn:schemas:httpmail:importance").Value= intImportance
.Fields.Update
.From= sFromAddress
.ReplyTo= sFromAddress
.To= sToAddress
.Cc= sCcAddress
.Bcc= sBccAddress
.Subject= sSubject
.Textbody= sBody
.Send
End With
Set cdoMessage = Nothing
Set cdoConfiguration = Nothing
End Sub
View 6 Replies
View Related
Sep 8, 2004
Hi,
Im using a stored procedure to send an email using CDOSYS. Im using the code i got here
http://support.microsoft.com/defaul...;312839&sd=tech
I can successfully send an email, but would like to know how i can add additional fields like setting the importance or priority of the how email. How would i change this stored procedure to do this.
here is the code...
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/d...s_messaging.asp
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/d...n_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'MailServerName'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
go
View 3 Replies
View Related
Sep 8, 2004
Hi,
Im using a stored procedure to send an email using CDOSYS. Im using the code i got here
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech
I can successfully send an email, but would like to know how i can add additional fields like setting the importance or priority of the how email. How would i change this stored procedure to do this.
here is the code...
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'MailServerName'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
go
View 2 Replies
View Related
Oct 5, 2004
Hi there,
I am wondering if the following is possible.
I would like to have a scheduled job that would check every day a date field of every record (on a specific table). If that date correspond to today then I would like to send an email with the information of that record.
Is this possible?
Btw if I mentionned Job Scheduling it's because I don't know if there is other ways available for such purpose, so if you think of an other way please let me know.
View 9 Replies
View Related
Feb 8, 2006
Hello,
I have a report that uses a begin date and an end date as paremeters along with a user's email adddress to generate thier schedule for the coming week. I would like to schedule this report to run at the beginning of the week and email to each user based on thier email address. So the parameters will be system.date +1 and system.date+6 and user email. I have the users email addresses in a table so the 3rd parameter could be a "for each email in table users...". Is this posssible? Should it be in Report services or a scheduled Stored Procedure? Any help with syntax will be greatly appreciated!
Thanks,
Bob Myles
View 4 Replies
View Related
Sep 20, 2005
Hey Guys!
I have a SQL Update Query that I want to run automatically everyday and
I have read through the posts and I do not understand very clearly how
to go about creating one.
The SQL query I want to run is this:
UPDATE Rental
SET TotalFee = ExtraFee + TotalFee
WHERE DaysOverdue >= 0
Once I have made a DTS package. How do I declare it in my coding???
Thanks in advance!
View 4 Replies
View Related
Oct 10, 2007
I'm currently using Windows Task Scheduler to run a few jobs. We would like to start using the SQL Server Agent to handle this. The problem I am having is in specifing the 'working directory' of the program. The program is generating some files in a temp directory withing the root of the application. For some reason when I schedule in SQL it is generating these files in the WinSys32 directory.
I am using the CmdExec type in my job step.
I am sure I may have left out some info. please let me know if you need anything else.
thanks.
View 7 Replies
View Related
Feb 21, 2005
Hi All,
I have a problem with scheduling an Analysis Services task.
When I execute the DTS package from EM, every thing works fine.
When I schedule the package from SQL Agent, I get the following error when the job executes:
Error = -2147024770 (8007007E)
Error string: The specified module could not be found.
Error source: Microsoft Data Transformation Services (DTS) Package
I am running SQL 2K SP3a with Analysis Services 2K SP3a on WIN Server 2003 Std Edition
Any ideas or suggestions?
View 2 Replies
View Related
Dec 14, 2004
I am kicking myself, but cannot seem to get a simple stored procedure to return ANY records. I know it's soming that has to do with the Joins. Don't know what. I ran query analyzer and passed it a valid city and nothing was returned, but no errors either. If I run the query without any parameters, all the records are retrieved fine. ANY IDEAS?
CREATE PROCEDURE spUnitsbyCity
@city varchar
AS
SELECT A.unitcity, A.unitid, B.radioip, B.radiomac, C.videoserverip
FROM tbl_units as A
INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
WHERE A.unitcity = @city
GO
View 2 Replies
View Related
Dec 8, 2003
Can someone please help me fix this. The if statement always runs. I only want it to run if the statement is true and there is a result coming back. If the productID does not have any magazines linking to it, I don't want the If statement to run. Thanks in advance.
ASP.NET code:
If Not Magazine.GetMagazinesForProduct(ProductID) Is Nothing Then
blanklabel.Text = categoryDetails.Spacer
blanklabel.Visible = True
blanklabel2.Text = categoryDetails.Spacer
blanklabel2.Visible = True
magazinerecommendedlabel.Text = "Recommended/Featured in the following magazine(s):"
magazinerecommendedlabel.Visible = True
End If
-------------------------
magazine class:
Public Function GetMagazinesForProduct(ByVal productID As String) As SqlDataReader
Dim connection As New SqlConnection(connectionString)
Dim command As New SqlCommand("GetMagazinesForProduct", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@ProductID", SqlDbType.VarChar, 50)
command.Parameters("@ProductID").Value = productID
connection.Open()
Return command.ExecuteReader(CommandBehavior.CloseConnection)
End Function
-----------------------
Stored Procedure:
CREATE PROCEDURE GetMagazinesForProduct
(@ProductID varchar)
AS
SELECT Magazine.[Name], Magazine.[Issue], Magazine.SmallImagePath
FROM Magazine INNER JOIN MagazineProduct
ON Magazine.MagazineID = MagazineProduct.MagazineID
WHERE MagazineProduct.ProductID = @ProductID
RETURN
GO
--------------------------
View 3 Replies
View Related
Apr 2, 2008
Hi all,
I have a table with 2 columns (simplified for this question):
Date and Action
Date is normal datetime, Action is varchar and can be either Sent or Received. The data can look like this:
1. '2008-04-02 15:09:09.847', Sent
2. '2008-04-02 15:09:10.125', Sent
3. '2008-04-02 15:09:11.125', Received
4. '2008-04-02 15:09:12.459', Received
5. '2008-04-02 15:09:15.459', Received
6. '2008-04-02 15:09:24.121', Sent
7. '2008-04-02 15:09:28.127', Received
I want to find a pair of rows Sent-Received with the Max Date difference, where Received follows immediately after Sent.
It means in our example, valid are only lines
2. and 3. or
6. and 7.
In this example, rows 6. and 7. have bigger difference of Dates, so the result of the query should be
6. '2008-04-02 15:09:24.121', Sent, 7. '2008-04-02 15:09:28.127', Received
It is probably easy, I just cannot work it out. Suprisingly finding MIN was quite easy. Thanx for any tips!
Regards,
alvar
View 6 Replies
View Related
Apr 23, 2008
Hi Team Expert,
I am working on an ASP.NET and c# project. I am creating a page that allow user to add new Workitem to TFS and successfully do so. Now I would like to implement the way to email to notify the user that the new WI is added €¦. Does anyone know how to do this or have an example of how to implement that.
Any help would be greatly appreciated.
Thanks so much
Ddee
View 1 Replies
View Related
Oct 25, 2007
SSRS 2000
Data driven subscription uses a SQL query to create report parameter values.
The subscription errors are inconsistent meaning sometimes no reports in a batch fail and sometimes 3 out of 5 will fail. I don't think it is data related because I'm using the same data each time.
When a report fails to email there is a consistent error related to rendering a chart. That message is in red below.
The data source is Analysis Services 2000. Has anyone seen this before? I've seen a lot of postings that are similar but no solutions. Is it possible for RS to lose data or get corrupt data from an MDX query?
Is there a timeout between RS and Analysis Services (PivotTable Service) to check?
SSRS trace log:
ReportingServicesService!runningjobs!237c!10/25/2007-14:15:01:: w WARN: Thread pool pressure: turning off threads
ReportingServicesService!processing!237c!10/25/2007-14:15:01:: e ERROR: System.ArgumentException: Invalid parameter used.
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
at Dundas.Charting.WebControl.ChartImage.GetImage()
at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
ReportingServicesService!processing!237c!10/25/2007-14:15:01:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used. ---> System.ArgumentException: Invalid parameter used.
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
at Dundas.Charting.WebControl.ChartImage.GetImage()
at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
--- End of inner exception stack trace ---
ReportingServicesService!reportrendering!237c!10/25/2007-14:15:01:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., ;
Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used. ---> System.ArgumentException: Invalid parameter used.
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
at Dundas.Charting.WebControl.ChartImage.GetImage()
at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderChart(PageTableGrid pageTableGrid, Int32 row, Int32 col, ReportItemInfo reportItemInfo, PageCell pageCell, Boolean addHeaderRows)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderGridCell(PageLayout pageLayout, Int32 row, Int32 col, Hashtable& duplicateItemsTable, Boolean addHeaderRows)
at Microsoft.ReportingServices.Rendering.BIFFUtility.WorkSheet.WriteRowBlocksAndCells(ExcelRenderer excelRenderer, Stream stream, UInt32 indexBeginOffsetPosition, Int32 minCol, Int32 maxCol)
at Microsoft.ReportingServices.Rendering.BIFFUtility.BaseWorkSheet.WriteWorkSheet(ExcelRenderer excelRenderer, Stream stream, Int32 offset)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
--- End of inner exception stack trace ---
ReportingServicesService!library!237c!10/25/2007-14:15:01:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.
ReportingServicesService!emailextension!237c!10/25/2007-14:15:01:: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An error has occurred during rendering of chart chart_SalesMarginTrend. Details: Invalid parameter used. ---> System.ArgumentException: Invalid parameter used.
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height, PixelFormat format)
at System.Drawing.Bitmap..ctor(Int32 width, Int32 height)
at Dundas.Charting.WebControl.ChartImage.GetImage()
at Dundas.Charting.WebControl.Chart.Save(Stream imageStream)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.DundasChart.GetImage(ImageType type, ChartInstanceInfo instanceInfo, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage(ImageType type, Boolean& hasImageMap)
at Microsoft.ReportingServices.ReportRendering.Chart.GetImage()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderChart(PageTableGrid pageTableGrid, Int32 row, Int32 col, ReportItemInfo reportItemInfo, PageCell pageCell, Boolean addHeaderRows)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderGridCell(PageLayout pageLayout, Int32 row, Int32 col, Hashtable& duplicateItemsTable, Boolean addHeaderRows)
at Microsoft.ReportingServices.Rendering.BIFFUtility.WorkSheet.WriteRowBlocksAndCells(ExcelRenderer excelRenderer, Stream stream, UInt32 indexBeginOffsetPosition, Int32 minCol, Int32 maxCol)
at Microsoft.ReportingServices.Rendering.BIFFUtility.BaseWorkSheet.WriteWorkSheet(ExcelRenderer excelRenderer, Stream stream, Int32 offset)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc)
at Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext reportContext, ItemProperties properties, ParameterInfoCollection effectiveParameters, Guid reportId, ClientRequest session, String description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection thisReportDataSources, Boolean cachingRequested, Warning[]& warnings, ReportSnapshot& resultSnapshotData, DateTime& executionDateTime, RuntimeDataSourceInfoCollection& alldataSources, UserProfileState& usedUserProfile)
at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.ReportImpl.Render(String renderFormat, String deviceInfo)
at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage message, Notification notification, SubscriptionData data)
at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification notification)
at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
ReportingServicesService!library!237c!10/25/2007-14:15:01:: Data Driven Notification for activation id 1ab1f67c-82c0-4dba-9745-a4057ab1cb4f was saved.
ReportingServicesService!library!237c!10/25/2007-14:15:01:: Status: Failure sending mail: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.
ReportingServicesService!notification!237c!10/25/2007-14:15:01:: Notification 15307c1b-0b60-493d-b2c1-fde89780ff06 completed. Success: False, Status: Failure sending mail: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., DeliveryExtension: Report Server Email, Report: SalesPerfScorecard_Email, Attempt 0
ReportingServicesService!dbpolling!237c!10/25/2007-14:15:01:: NotificationPolling finished processing item 15307c1b-0b60-493d-b2c1-fde89780ff06
View 4 Replies
View Related
Jan 18, 2006
Hi all, I have a problem with this trigger. It seams to be very simple, but it doesn't work...
I created a trigger on a table and I would want that this one updates a field of a table on a diffrent DB (Intranet). When I test it normally (a real situation), it doesn't work, but when I do an explicit update ("UPDATE AccesCard SET LastMove = getDate();" by example) it works.
If anyone could help me, I would appreciate.
NB: Is there a special way, in a trigger, to update a table when the table to update is on another BD ?
Francois
This is the trigger:
------------------------------------------------------------
ALTER TRIGGER UStatus
ON AccesCard
AFTER UPDATE, INSERT
AS
DECLARE @noPerson int
SET NOCOUNT OFF
IF UPDATE(LastMove)
BEGIN
SELECT @noPerson = Person FROM INSERTED
UPDATE Intranet.dbo._Users SET Intranet.dbo._Users.status = 1 WHERE personNo = @noPerson;
END
SET NOCOUNT ON
View 5 Replies
View Related
Apr 20, 2007
***Disclaimer***
This is homework, just need a hint.
I compare this to other scripts and can't find any differences. I'm getting a syntax error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
Any hints would be great! Thanks.
USE StateUBookstore
ALTER TABLE dbo.Classes
ADD( InstructorID char (5) NOT NULL,
Credits int NOT NULL);
GO
ALTER TABLE dbo.Students
ADD( PhoneNO char(14) NULL,
Email char (50) NULL),
DROP COLUMN BookStoreEmp;
GO
ALTER TABLE dbo.Enrollment
ADD( Semester int NOT NULL,
EndDate DateTime NOT NULL);
View 13 Replies
View Related
Apr 23, 2008
I have my db in a pocket pc wm5.0, I just want to make a simple query
select * from table
where pk = '1'
but this doesnt work, if you tried any other field else than the primary key
it works... WHY???
would it be a problem with the sdf file? help please!!!
View 3 Replies
View Related
Apr 24, 2008
Hi,
I tried to create a simple application based on the Sql Server CE samples and, as is typical when I play with databases, the program failed. In this case it failed to even load. The program has a form and a data source that I dropped onto the form. When I run the program the program breaks at this line:
Code Snippetthis._connection = new global::System.Data.SqlServerCe.SqlCeConnection();
The error is:
An unhandled exception of type 'System.DllNotFoundException' occurred in System.Data.SqlServerCe.dll
Additional information: Unable to load DLL 'sqlceme35.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
My first attempt at 'fixing' this was simply to copy the requisite DLLs to my bindebug folder. I found the DLLs here:
C:Program Files (x86)Microsoft SQL Server Compact Editionv3.5 and copied these - sqlcecompact35.dll, sqlceca35.dll, sqlceme35.dll, sqlceoledb35.dll, sqlceqp35.dll and sqlcese35.dll, sqlceer35EN.dll - to my bindebug folder.
Now instead of the first error, I get this error:
An unhandled exception of type 'System.BadImageFormatException' occurred in System.Data.SqlServerCe.dll
Additional information: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)
I am running Vista Business (x64) and using (the desktop version of) Sql Server Compact Edition 3.5 with Visual Studio 2008 Standard Edition. The test 'program' is written in C#. (Incidentally I can play with the database with no problems in Visual Studio 2008's server explorer. I can look at data, add data, etc.)
Can anyone please help?
Thanks.
View 3 Replies
View Related
Apr 13, 2006
I want to insert values from the querystring but nothing happens with this code (the sp works great from the Query Analyzer):
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"InsertCommand="spSearch_row_insert" InsertCommandType="StoredProcedure">
<InsertParameters>
<asp:QueryStringParameter Name="CustomerID" QueryStringField="1" DefaultValue="1" Type="String" />
<asp:QueryStringParameter Name="SearchID" QueryStringField="2" DefaultValue="1" Type="String"/>
<asp:QueryStringParameter Name="SearchDate" QueryStringField="3" DefaultValue="1" Type="String" />
<asp:QueryStringParameter Name="IP" QueryStringField="4" DefaultValue="1" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</form>
I'm very grateful for help!// G
View 3 Replies
View Related
Mar 6, 2007
I'm trying to work my way through the steps of using a User Id and Password in a connection string.
I'm working with SQL 2005 Express, VS2005, in the development server. Got an error I can't get around...tried it several diffent ways on a slightly more complicated test site...no joy...so went to the MSDN tutorial...made the most "vanilla" test I could think of, and still can't figure it out.
I thought it would be simple enough that I could post the whole thing (below)
The test works fine with Integrated Security = True in the connection string. When I remove that phrase, I get the error:
{"CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file E:MyPathApp_DataVSST_DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}
This occurs on the cn.Open statement below.
It gets past the login, so I know that the SQL User and password match up correctly.
==========================
<connectionStrings>
<add name="VSST_CN"
connectionString="Data Source=MyServerSQLEXPRESS;AttachDbFilename=E:MyPathApp_DataVSST_DB.mdf;User Id = VSST; Password=vsst123"
providerName="System.Data.SqlClient"/>
</connectionStrings>
=========================
Page Code Behind (no controls on page)
------------
Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("VSST_CN").ToString())
Dim cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM VSST_Table", cn)
cn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
rdr.Read()
Response.Write(rdr(0).ToString())
End Sub
End Class
=================
The DB
Table VSST_Table,
ID is int, primary key, identity
Field1, Field2, Field3, Field4, Field5 are varchar(50)
I added one record ("A", "B", "C", "D", "E") to the table through VS2005 Server Explorer
This shows up in Count = 1 from running the page when Integrated Security = True
=================
In SSMSE: (this is ALL I did, tried to use the minimum so not to confuse...)
I added the SQL Authentication Server level user "VSST" with the password "vsst123" (and the login works, as noted above)
I attach the .mdf
I add VSST to the Database Users, and give it db_owner
I add VSST to the Table with all permissions checked.
=====================================
I can't figure this out. This is a very vanilla test and I'm stumped. I'm about to give up on SQL Authentication entirely (at least for now), and just try to filter my inputs for SQL Injections...that's the only reason I have (at this stage in my biz plan) for needing SQL Authentication. On the other hand, I really don't like being this stumped on something that is so widely promoted as a common practice.
Any help on this would be greatly appreciated.
Thanks!
View 5 Replies
View Related
Jan 23, 2004
In my DTS package I tried to make an email task and it works if I execute it manualy, but if I schedule it I get the following error:
DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_16 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_16 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnProgress: DTSStep_DTSDataPumpTask_2; 8 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 8 DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSSendMailTask_1 DTSRun OnError: DTSStep_DTSSendMailTask_1, Error = -2147220352 (80040480) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220352 (80040480); Provider Error: 0 (0) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Erro... Process Exit Code 1. The step failed.
What could it be?
View 6 Replies
View Related
Sep 17, 2007
This is simple enough and I'm stumped. Why doesn't this work?
Code Snippet
use w
go
-- Create image warehouse
create table dbo.ImageWarehouse (
[ImageWarehouseID] int identity(1,1) primary key,
[ImageName] varchar(100),
Photo varbinary(max))
Go
-- Import image
Insert into dbo.ImageWarehouse
([ImageName]) values ('testingimage.gif')
update dbo.ImageWarehouse
set Photo =
(SELECT * FROM OPENROWSET(BULK 'c: estingimage.gif', SINGLE_BLOB)AS x )
WHERE [ImageName]='testingimage.jpg'
go
-- Check population
--delete from dbo.ImageWarehouse
select * from dbo.ImageWarehouse
go
-- Export image
declare @SQLcommand nvarchar(4000)
set @SQLcommand = 'bcp "SELECT top 1 Photo FROM w.dbo.ImageWarehouse WHERE ImageName = ''testingimage.gif''" queryout "c: estingimage_out.gif" -T -N -S [my server name]'
exec xp_cmdshell @SQLcommand
go
I successfully see
Code SnippetImageWarehouseID ImageName Photo
1 testingimage.gif 0x4749463839615802C (long hex string)
then
Code Snippet
output
NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
NULL
But testingimage_out.gif is unreadable. It's the same size (28k) as the original trestingimage.gif. Same result with a .jpg file. Try to open the file and it's just red X.
No SQL errors. Just result error. What am I overlooking?
View 4 Replies
View Related
Sep 13, 2007
Hello,
I have installed VS 2008 Beta 2 which includes SQL CE 3.5, (I have VS 2005 installed too).
I can not get any INSERT command to work!
First i have tried the VS wizard for a new connection and a dataset. but after inserting a row into one of the dataset tables and then updating it to SQL CE with tha data adapter, nothing happens! NO ERROR and NO inserted row in the database file! so the identity of the inserted row in dataset table doesnt get updated.
Then i tried a simple code without any dataset in a clean solution:
Code Snippet
Dim con As New SqlCeConnection("Data Source=|DataDirectory|MyDatabase#1.sdf")
Dim cmd As New SqlCeCommand("INSERT INTO [Table1](Name) VALUES('TestValue')", con)
Try
con.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlCeException
MessageBox.Show(ex.Message)
Finally
con.Close()
End Try
NO ERROR! NO INSERTED ROW! Nothing happen!
I'm using it in a desktop application.
Why?
Regards,
Parham.
View 5 Replies
View Related
Sep 20, 2007
Hello,
I have data (using Full Result Set) and would like to email values.
Query output from SQL Task:
ID Client
1 ABC
2 Acme
3 Holtz
Goal email output (inside Script Task):
The following records found today:
ID Client
1 ABC
2 Acme
3 Holtz
Thank you in advance.
Note: I know how the email works, it's just passing the resultset I need help on.
View 3 Replies
View Related
May 19, 2006
I have a pretty simple process that copies some files from an AS400 to a directory on the same server where my sql 2005 instance lives. I then use an email task to send these files to a client.
However, the email task does not work. I get the following message:
[Send Mail Task] Error: Either the file "BYNSOSR1;BYNSOSR2;BYNSOSR3;BYNSOSR4;BYNSOSR5;BYNSOSR6;BYNSOSR7" does not exist or you do not have permissions to access the file.
Well, the files definitely exist where they are supposed to be so I guess it's a permissions error. However, I have no clue which user would need permissions on that directory. As a test, I gave everyone read permissions and still got this error.
Any suggestions about which user I need to allow read access to the directory where these files exist?
Thanks in advance for any info.
View 3 Replies
View Related
Sep 20, 2006
I am generating the text file on run time using flat file destination. The text file is generated on the location C: SSIS or D:SSIS based on the location specified in the configuration file.
The text file contains the non matching rows during lookup transform task.
I want to send this dynamically generated text file using the send email task.
But while doing this, I receive an error during package validation:
Package validation error:
Error at send email task [ send email task ] : either the file C:SSISErroroutput.txt does not exists or you do not have permission to access the file.
Error at send email task ; There were error during task validation.
Please suggest as I need to generate the text file only in case of lookup failure.
View 3 Replies
View Related
Feb 9, 2004
I am trying to send job status information using smtp via cdosys. I have it emailing but I want to pass the servername and jobname in the email. Can anyone assist?
***oops, here is the SP and the command ran from the job steps;;;
************************************************** *****
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtpserver'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
************************************************** ****
declare @Body varchar(4000)
select @Body = 'Job Succeeded'
exec sp_send_cdosysmail 'me@you.com','you@me.com','servername Jobname',@Body
Thanks
View 1 Replies
View Related
Feb 25, 2008
Wondering if it is by design that when I select messagesource to be variable it only shows user variables or if there is some setting I'm missing to expose the system variables?
This email task is on the eventhandler tab.
Thanks
View 3 Replies
View Related
Apr 4, 2007
i use email task and it dosent send an email at all....this task causes an error..
If i need to set up email for the package if the package fails...
or how should i set in the sql server agent...iknow there is an option there but i dont know how to..
Please let me know...
View 18 Replies
View Related
Sep 20, 2006
I have a script task in which I check the size of a file.
If the size of a file is greater then 0 KB then I need to send this file as an attachment using send email task.
If size is equal to 0 KB then I don't want to send email at all.
In the script task, I have a flag(@sFileExists) which i set to true in case of size of file greater then 0 and false in other case.
I am using a precedance constraint (Expression : @sFileExists=true) and condition is logical AND.
After script task based on the condition (@sFileExists=true) I am using the send email task for sending the email.
But I am not receiving the email. Please sugest where I am wrong.
Please note that the send email task is working all-right separately.
View 4 Replies
View Related
Sep 18, 2007
I have just created and tested a package that uses the Send Email Task and it works fine in our dev environment - I guess this is expected as I am an admin on my machine and have rights on our mail server. But Im trying to document the considerations for rolling this out into a customer production setting.
Is anyone familiar with the security considerations or can point me in the direction of some documentation?
For example, presumably the SQL Server Agent Windows Account will need rights to contact the mail server? Will it need its "own" email account or can you just put any valid email addy in the from box? Presumably the account will need the right permissions to access the attachement file too?
Do these sound like relevant considerations to pass on to customer DBAs/system admins? Anything else Ive missed, am I worrying to much?!
Cheers.
View 3 Replies
View Related
Jun 30, 2006
HI,
How can I programmatically change the properties of Send Mail task using Script Task. I want to change the From, To and Attachment parameters of the Send Mail task.
If the Script Task can't do it, Is there any alternative to do?
Thanks
View 1 Replies
View Related
Jun 12, 2007
Hi,
I have 2 tasks:
- Execute SQL task
- FTP task
They both are linked with a red "failure" line to a:
- Send Mail task
How can I put some text in the email body:
"SQL caused an error" or "FTP caused an error"
...to let it know which of the 2 (sql or ftp task)did cast an error?
Thank you!!
View 10 Replies
View Related