Problem with T-SQL script to generate a report
-
Hi All,
It would be great if anybody could help me how to write a T-SQL script to generate a report in spreadSheet from a table in SQLserver 2000 database.
And this report should be generated as per the following timings(Sheduled timings only).<script></script>
As well it should be able to send a mail regarding the availability of the report to user groups.
Incase if the generation of report fails then we need to send a mail reg the failure.
Any HELP is greatly appreciated!!!
Thanks in Advance!!!
Regards,
Sashi John Micke'
-
This was removed by the editor as SPAM
-
If you can set up Database Mail, then you can use sp_send_dbmail to send mail.
Below is how to use that procedure taken from BOL. If you need more help, respond.
Sends an e-mail message to the specified recipients. The message may include a query result set, file attachments, or both. When mail is successfully placed in the Database Mail queue sp_send_dbmail returns the mailitem_id of the message. This stored procedure is in the msdb database.
Transact-SQL Syntax Conventions
Syntax
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
Arguments
[ @profile_name = ] 'profile_name'
Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.
[ @recipients = ] 'recipients'
Is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified or sp_send_dbmail returns an error.
[ @copy_recipients = ] 'copy_recipients'
Is a semicolon-delimited list of e-mail addresses to carbon copy the message to. The copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified or sp_send_dbmail returns an error.
[ @blind_copy_recipients = ] 'blind_copy_recipients'
Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to. The blind copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified or sp_send_dbmail returns an error.
[ @subject = ] 'subject'
Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is 'SQL Server Message'.
[ @body = ] 'body'
Is the body of the e-mail message. The message body is of type nvarchar(max), with a default of NULL.
[ @body_format = ] 'body_format'
Is the format of the message body. The parameter is of type varchar(20), with a default of NULL. When specified, the headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values:
TEXT
HTML
Defaults to TEXT.
[ @importance = ] 'importance'
Is the importance of the message. The parameter is of type varchar(6). The parameter may contain one of the following values:
Low
Normal
High
Defaults to Normal.
[ @sensitivity = ] 'sensitivity'
Is the sensitivity of the message. The parameter is of type varchar(12). The parameter may contain one of the following values:
Normal
Personal
Private
Confidential
Defaults to Normal.
[ @file_attachments = ] 'file_attachments',
Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max).
[ @query = ] 'query'
Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.
[ @execute_query_database = ] 'execute_query_database'
Is the database context within which the stored procedure runs the query. The parameter is of type sysname, with a default of the current database. This parameter is only applicable if @query is specified.
[ @attach_query_result_as_file = ] attach_query_result_as_file
Specifies whether the result set of the query is returned as an attached file. attach_query_result_as_file is of type bit, with a default of 0.
When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified.
[ @query_attachment_filename = ] query_attachment_filename
Specifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.
[ @query_result_header = ] query_result_header
Specifies whether the query results include column headers. The query_result_header value is of type bit. When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1. This parameter is only applicable if @query is specified.
[ @query_result_width = ] query_result_width
Is the line width, in characters, to use for formatting the results of the query. The query_result_width is of type int, with a default of 256. The value provided must be between 10 and 32767. This parameter is only applicable if @query is specified.
[ @query_result_separator = ] 'query_result_separator'
Is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ' ' (space).
[ @exclude_query_output = ] exclude_query_output
Specifies whether to return the output of the query execution in the e-mail message. exclude_query_output is bit, with a default of 0. When this parameter is 0, the execution of the sp_send_dbmail stored procedure prints the message returned as the result of the query execution on the console. When this parameter is 1, the execution of the sp_send_dbmail stored procedure does not print any of the query execution messages on the console.
[ @append_query_error = ] append_query_error
Specifies whether to send the e-mail when an error returns from the query specified in the @query argument. exclude_query_output is bit, with a default of 0. When this parameter is 1, Database Mail sends the e-mail message and includes the query error message in the body of the e-mail message. When this parameter is 0, Database Mail does not send the e-mail message, and sp_send_dbmail ends with return code 1, indicating failure.
[ @query_no_truncate = ] query_no_truncate
Specifies whether to execute the query with the option that avoids truncation of large variable length data types (varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, and user-defined data types). When set, query results do not include column headers. The query_no_truncate value is of type bit. When the value is 0 or not specified, columns in the query truncate to 256 characters. When the value is 1, columns in the query are not truncated. This parameter defaults to 0.
Note:
When used with large amounts of data, the query_no_truncate option consumes additional resources and can slow server performance.
[ @mailitem_id = ] mailitem_id [ OUTPUT ]
Optional output parameter returns the mailitem_id of the message. The mailitem_id is of type int.
-
You can use SQL Server Business Intelligence Development Studio to create a SQL Server Integration Services (SSIS), the successor to DTS in SQL Server 2005. With this tool you can generate data to an excel (or text file), and then add a step to deliver the mail afterwards.
-
And this report should be generated as per the following timings(Sheduled timings only).
Then schedule this package to run as a job. If the job fails you can have a email notification sent to you....
-
Hi Gloria,
Thanks for your suggestion.As, I am using SQL server 2000 ,I dont think they have Business Intelligence studio to generate a report in XL or CSV.
But I can write a script for that.Hope so
Rgds,
Sashi
-
With SQL Server 2000 Interprise Manager you can make a Data Transformation Services (DTS) that can do the same thing.
Under the Data Transformation Services folder, right click on Local Packages, Select New Package. All the tools you need will be there.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply