Table valued function

Question

Table valued function

GekGurly

Valued Member

Points: 55
More actions
November 29, 2010 at 4:04 pm

#147875

I have 2 queries, one is a table valued function, the other is a stored procedure. The stored procedure generates a weekly email (set up in the maintenance plan), the table valued function when run on its on produces a table filled with data as we wish to see it displayed. When I see the csv file generated with the email the columns are not all in a row, the final column goes down under the second column, and the first record ends up being a bunch of dash's.
Any help would be greatly appreciated!! 🙂 The table valued function & Stored Procedure are attached, as well as a sample of the output.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

I think the best thing to do would be to generate the csv file through either the OpenRowset function, or via BCP. Then email that file.

By running the query as part of the sp_send_dbmail procedure with @query_result_header set to it's default, it's going to include the column headings, with the dashes.

You might also want to investigate the FOR XML clause for generating comma-delimited strings. If you don't know how, just ask.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95461 More actions · Answer 2

Using BCP:

-- declare and initialize variables

declare @sql varchar(8000),

@q varchar(255),

@fn varchar(255),

@path varchar(100);

SET @q = 'SELECT * FROM master.sys.objects';

SET @path = 'c:\temp';

SET @fn = @path + '\sysobjects.csv';

-- You may need to use the -S parameter to specify the server

-- especially if it's a named instance.

-- You may need to use the -U & -P parameters to specify the

-- user name / password to log in as if Trusted Connections

-- (-T) does't work.

SET @sql = 'bcp "' + @q + '" queryout ' + @fn + ' -c -t, -T';

-- ensure that the output directory already exists

execute xp_create_subdir @path;

exec master..xp_cmdshell @sql, no_output;

-- Change your sp_send_dbmail to this to send the file

/*

EXEC msdb.dbo.sp_send_dbmail

@recipients='angela.smith@blueridge.ca',

--@recipients=@CompanyRepEmail,

@subject=@s,

@body=@b,

--@query=@q,

--@attach_query_result_as_file=1,

@query_attachment_filename=@fn,

--@query_result_separator='',

@profile_name='projects'

*/

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

GekGurly Valued Member Points: 55 More actions · Answer 3

GekGurly

Valued Member

Points: 55

November 30, 2010 at 1:48 pm

#1257022

Awesome, thank you. I will give it a try!

WayneS SSC Guru Points: 95461 More actions · Answer 4

GekGurly (11/30/2010)
Awesome, thank you. I will give it a try!

And how is it working?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

GekGurly Valued Member Points: 55 More actions · Answer 5

I am having issues with getting the send mail to generate a file. And have had to make changes to the stored procedure, and am having some issues with data. So I guess its not going as well as I had hoped it would be going.