SQLTeam.com | Weblogs | Forums

Function to send email returning errors when called


#1

Hi all,

this is my first post here, apologies if this was already asked, but i did not find it anywhere...

I'm trying to create function on the server, which will email file back to recipient.
my function code is:

ALTER FUNCTION [dbo].[email_file] (@filename nvarchar(max), @Recipient nvarchar(max), @SubjectLine nvarchar(max)) returns int
as
BEGIN
	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = '<<hidden>>'
		,@body = 'Automated report.'
		,@recipients = @Recipient
		,@subject = @SubjectLine
		,@file_attachments = @Filename
	return 0
end

when i call this code from any query:
EXEC dbo.email_file 'C:\Users\<<hidden>>\Desktop\invalid_jobs.csv', '<<hidden>>', 'Invalid jobs'

it returns this error:

Msg 557, Level 16, State 2, Procedure sp_send_dbmail, Line 1
Only functions and some extended stored procedures can be executed from within a function.

but when i call this code directly (minus return statement) it works just fine.

any ideas what could be the reason? I'm new to SQL, still learning, apologies if this is something obvoius...


#2

How are you doing is? Are you using SQL Server or SQL Express?

or you can move invalid_jobs.csv file to another drive lik "D" and check it.

Thanks
Basit.


#3

Hi
I'm using SQL Server 2012.
Access to file is not an issue, it has been created by the same query without an issue.

My issue is that I cannot place code to send email from SQL server into saved function.
I'm trying to create function which will take 3 parameters (path to file, recipient address, subject) and send email using those. But it fails with above error.
But when i take this code, and use it directly in the query window, email lis being sent without an issue.

using this code: (email address scrambled)

exec dbo.deletetable dbo, _temporary_table
select * into _temporary_table from [Account]
exec dbo.Generate_CSV_File dbo, _temporary_table, 'C:\test.csv'
exec dbo.deletetable dbo, _temporary_table

exec msdb.dbo.sp_send_dbmail
	@profile_name = 'Reporting Exchange'
	,@body = 'Automated report.'
	,@recipients = 'wxxxx@xxxx.com'
	,@subject = 'Sample subject line'
	,@file_attachments = 'c:\test.csv'

Will send this email without a problem.

But using code:

exec dbo.deletetable dbo, _temporary_table
select * into _temporary_table from [Account]
exec dbo.Generate_CSV_File dbo, _temporary_table, 'C:\test.csv'
exec dbo.deletetable dbo, _temporary_table

exec dbo.email_file 'C:\test.csv', 'wxxxx@xxxxx.com', 'sample subject'

is returning error:

(402 row(s) affected)

(403 row(s) affected)

(7 row(s) affected)
Msg 557, Level 16, State 2, Procedure sp_send_dbmail, Line 1
Only functions and some extended stored procedures can be executed from within a function.

SQL Version: (select @@version)

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )

I can post code for dbo.DeleteTable and dbo.Generate_CSV_File if needed. I've created both of those, so no issues on copyright grounds.


#4

What you see is what you get and the error message is correct. You can't do this in a function. It has to be done in a stored procedure.


#5

The way i understand this error is that you can execute functions from within the function:

Only functions and some extended stored procedures can be executed from within a function.

Yet it doesn't allow me to call this function from query window (which, to the best of my understanding acts as a function, not stored procedure)...

Tomorrow (when I get back to work) I'll try the same from within Stored Procedure. I'll also try changing this function into Stored Procedure, see if this makes any difference.


#6

sp_send_dbmail is NOT a function - it is a stored procedure provided by Microsoft.

There is no reason to wrap the stored procedure as a function and in your example you are not actually calling the function correctly. A function is either a scalar or table function - so it has to be used as either a column or a table (e.g. SELECT dbo.Function(parameters) FROM sometable - or SELECT {somecolumns} FROM dbo.Function(parameters)).

You should also investigate how to use sp_send_dbmail fully - as everything you appear to be doing in these other procedures can be done just using sp_send_dbmail. For example, you can have sp_send_dbmail create the output file for you - as well as run the query to get the data - directly.

Again - you can pass the query, the query database, the flag to attach a file, the attachment filename, etc...


#7

Ah... apologies, I've managed to completely omit the fact that sp_send_dbmail is stored procedure...
I'm aware that virtually all other tasks can be achieved by this, but there are further requirements on the format of the file:

  • all data values within CSV file need to be enclosed with double quotes, even empty cells
  • column headers need to be exported as well, also enclosed in double quotes.

For those reasons I've created function to generate CSV file. It's dynamic SQL, reads column names, puts those in temporary column, then reads data, adds to the same table, then I'm using BCP to export this to CSV file.

I'm new to SQL environment, I was (am) programmer, so my approach might not be most effective one - but I know it works.

I know that functions have different purpose in SQL, and I'm using those as well, but being programmer pushes me to create functions with less parameters required, easier to remember and use (in my world at least). having my own function to send file by email saves me remembering profiles, correct syntax of built in stored procedure, etc... I know, hardly any benefit, just the way I work.

Generate_CSV_File is this:

ALTER FUNCTION [dbo].[Generate_CSV_File] (@schema NVARCHAR(MAX), @table NVARCHAR(MAX), @filename NVARCHAR(MAX)) returns int
as
declare @st nvarchar(max)
declare @Column nvarchar(max)
declare @CommandLine nvarchar(254)

exec dbo.DeleteTable reporting, _temporary_holder_for_csv_file

declare column_cur cursor for select [column_name] from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @schema and TABLE_NAME = @table
open column_cur
set @st = 'select '
fetch next from column_cur into @Column
while @@FETCH_STATUS = 0
begin
	set @Column = '(select ''' + dbo.wrapstring(@Column) + ''') as [' + @Column + '],'  + char(13) + char(10)
	set @st = @st + @Column
	fetch next from column_cur into @Column
end
deallocate column_cur
set @st = left(@st, len(@st)-3) + char(10) + char(13) + 'into [reporting].[_temporary_holder_for_csv_file]'

declare column_cur cursor for select [column_name] from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @schema and TABLE_NAME = @table
open column_cur
set @st = @st + 'union all select '
fetch next from column_cur into @Column
while @@FETCH_STATUS = 0
begin
	set @Column = 'dbo.wrapstring(cast([' + @Column + '] as nvarchar(max))),' + char(10) + char(13)
	set @st = @st + @Column
	fetch next from column_cur into @Column
end
set @st = left(@st, len(@st)-3) + char(10) + char(13)
set @st = @st + 'from [' + @schema + '].[' + @table + ']' + char(13) + char(10)

exec sp_executesql  @st
set @CommandLine = 'BCP  [xxxxxxxxxx].[reporting].[_temporary_holder_for_csv_file] out ' + @filename + ' -c -t, -T -S'
EXEC xp_cmdshell  @CommandLine
deallocate column_cur
exec dbo.DeleteTable reporting, _temporary_holder_for_csv
return 0

dbo.WrapString is this:

ALTER FUNCTION [dbo].[WrapString] (@iString NVARCHAR(MAX)) returns NVARCHAR(MAX)
AS
BEGIN
	return '"' + dbo.FixString(@iString) + '"'
END

dbo.DeleteTable is this:

ALTER FUNCTION [dbo].[DeleteTable] (@schema nvarchar(max), @table nvarchar(max)) returns int
as
	begin
	declare @cmd nvarchar(max)
	set @cmd = 'if exists (select * from information_schema.tables where table_schema = ''' + @schema + ''' and table_name = ''' + @table + ''') drop table ' + @schema + '.' + @table
	exec sp_executesql  @cmd
	return 0
end

I know, now the perfect way, but it works. As I said earlier - I'm new to SQL environment. If you can think of any way of improving this solution - I'm open fr suggestions.

Thanks for all help!


#8

You don't need to recreate the wheel...

SELECT quotename('my column', char(34))

The function QUOTENAME replaces your custom function to 'wrap' a string. All of the other work you are doing doesn't need to be done either...

Select quotename('my column', char(34)) As '"My Column"'

The above creates the value and the column - both quoted as required.

So all you really need is a query to return the results and you can wrap the output any way you want...


#9

Thanks for your input mate.
Can you elaborate what do you mean that all other work I'm doing doesn't need to be done?
At the time when i coded those I was oblivious to QUOTENAME function (I'm still learning...). I need to fix data within (hence FixString, but that's internal requirement - removing particular characters from within the data). I guess that QUOTENAME would be quicker than my own function... as it's built in already.

I'm not sure I understand this one:
Select quotename('my column', char(34)) As '"My Column"'
wouldn't this return:
"my column"
as a result? it would put double quotes around column name as well, I agree, but BCP doesn't transfer column names to the file. For this I'm using dynamic SQL to generate table, which will contain column names (with double quotes) in the first row of data, so they do get exported to the file. Is there any way of exporting column names? Sorry if I'm missing something obvoius here...

Cheers,


#10

You don't need to use BCP to create the file - sp_send_dbmail will create the file for you...and send it. All you need to do is define the query to return the results you want in the file...

For example:

Execute msdb.dbo.sp_send_dbmail
        @profile_name = '{your profile}'
      , ...
      , @query = '
 Select quotename(col1, char(34)) As '"col1"'
      , quotename(col2, char(34)) As '"col2"'
   From dbo.MyTable'
      , @execute_query_database = '{your database}'
      , @attach_query_result_as_file = 1
      , @query_attachment_filename = '{your file name}.csv'
      , @query_result_header = 1
      , @query_result_separator = ','
      , @query_result_no_padding = 1;

Add in the recipients, subject, body, etc... and this will send an email with an attachment that has the filename you are looking for...

Note: this file cannot be opened directly from email - it must be saved first then opened in your favorite text editor. If you attempt to open it directly, your email program will attempt to use Excel to open the file and it will say the file is corrupted. If you need the file to be opened in Excel there is a way you can modify column one is your query so Excel recognizes the file and opens it directly.


#11

For an even simpler process - which does not require running anything in SQL Server directly...use Powershell.

Invoke-Sqlcmd -ServerInstance {your server} -Query {Select * From dbo.YourTable} | Export-Csv -Path C:\Temp\yourfile.csv;

Then you can use the Powershell command: Send-MailMessage