SQL Server Results to Excel File

Let me know if it works better to have an Excel spreadsheet hotwired to your SP with a big magic 'Get my data for me' button, as that is illustrated with the Excel VBA code in the article Microsoft Excel & SQL Server:  Self service BI to give users the data they want

As far as your exact requirement I don't have that code handy, but my knee-jerk reaction is that it would be a big hole in security to allow a role/group to execute SP's that write the results out to a file, and most DBA's I know would be very hesitant to grant such privs.   This is definitely possible in an SSIS package though.  Also possible in SSRS where the user goes into the report and selects Export to Excel.

That looks really neat!

My SP generates an output based on data imported into three #TempTables from CSV reports, Using BULK INSERT,..

Could your example take it's input from the filenames + path, put the filename & path into a variable, use the variables to do the BULK INSERT and then output to two worksheets in the Work Book?

Appreciate your thoughts.

Thank you

Hi garethtnash,
You can use bcp. Please try like below-

--

DECLARE @SQL AS VARCHAR(MAX) = 'SELECT * FROM [a]'
DECLARE @S AS VARCHAR(MAX) = ''
DECLARE @FilePath AS VARCHAR(1000) = 'C:\'
DECLARE @Dt AS VARCHAR(6) = FORMAT(GETDATE(),'ddMMyy')

SET @s = 'bcp "'+ @SQL + '" queryout ' + '(Success(' + @Dt + ')).xls' + ' -c -t\t -T -S ' + [ServerName\InstanceName]
EXEC MASTER..xp_cmdshell @s

SET @s = 'bcp "'+ @SQL + '" queryout ' + '(Failure(' + @Dt + ')).xls' + ' -c -t\t -T -S ' + [ServerName\InstanceName]
EXEC MASTER..xp_cmdshell @s

--

Select allOpen in new window

Hope it helps!

Probably a silly question....! But where do I get the [ServerName\InstanceName] from?

Is the server name the name at the top tree and the instancename the database name?

Thanks Pawan

Hi garethtnash,

Server name will the name of the server where you are working. If you are working on local system then you can use localhost.

database name is the name of the database on which you are working.

You can get those by running below queries-

--
SELECT @@SERVERNAME
SELECT @@servicename
--

Select allOpen in new window

Hope it helps!

Great, so would --

--

DECLARE @SQL AS VARCHAR(MAX) = 'SELECT * FROM [a]'
DECLARE @S AS VARCHAR(MAX) = ''
DECLARE @FilePath AS VARCHAR(1000) = 'C:\'
DECLARE @Dt AS VARCHAR(6) = FORMAT(GETDATE(),'ddMMyy')

SET @s = 'bcp "'+ @SQL + '" queryout ' + '(Success(' + @Dt + ')).xls' + ' -c -t\t -T -S ' + @@SERVERNAME + \ + @@servicename
EXEC MASTER..xp_cmdshell @s

SET @s = 'bcp "'+ @SQL + '" queryout ' + '(Failure(' + @Dt + ')).xls' + ' -c -t\t -T -S ' + @@SERVERNAME + \+ @@servicename
EXEC MASTER..xp_cmdshell @s

--

Select allOpen in new window

work?

Thank you

Hi garethtnash,
Yes I think it should work. Please try it out on your dev environment.

Regards,
Pawan

Hi Pawan,

One question.. you have declared @FilePath but I can't see it used?

Any thoughts

Hi,
Updated -

--
DECLARE @SQL AS VARCHAR(MAX) = 'SELECT * FROM [a]'
DECLARE @S AS VARCHAR(MAX) = ''
DECLARE @FilePath AS VARCHAR(1000) = 'C:\'
DECLARE @Dt AS VARCHAR(6) = FORMAT(GETDATE(),'ddMMyy')

SET @s = 'bcp "'+ @SQL + '" queryout ' + '(Success(' + @FilePath + @Dt + ')).xls' + ' -c -t\t -T -S ' + [ServerName\InstanceName]
EXEC MASTER..xp_cmdshell @s

SET @s = 'bcp "'+ @SQL + '" queryout ' + '(Failure(' + @FilePath + @Dt + ')).xls' + ' -c -t\t -T -S ' + [ServerName\InstanceName]
EXEC MASTER..xp_cmdshell @s
--

Select allOpen in new window

Hope it helps!

No luck I'm afraid -

Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'

the code I use is

BEGIN
DECLARE @SQL4 AS NVARCHAR(MAX) = 'Select * from'
DECLARE @S AS NVARCHAR(MAX) = ''
DECLARE @FilePath AS NVARCHAR(1000) = 'C:\SPTEST\'
DECLARE @Dt AS NVARCHAR(6) = /*FORMAT(GETDATE(),*/'ddMMyy'/*)*/

SET @s = 'bcp "'+ @SQL4 + '" queryout ' + '(Success(' + @FilePath + @Dt + ')).xls' + ' -c -t\t -T -S ' + @@SERVERNAME /*+ \ + @@servicename*/
EXEC MASTER..xp_cmdshell @s

/*SET @s = 'bcp "'+ @SQL + '" queryout ' + '(Failure(' + @FilePath + @Dt + ')).xls' + ' -c -t\t -T -S ' + [ServerName\InstanceName]
EXEC MASTER..xp_cmdshell @s*/
END

Select allOpen in new window

Query is incomplete.

Select * from

Tablename missing.

Yes, I removed that for posting here,.. with the full select statement I get the error above

Thanks

Hi Pawan,

Can I send you my SP directly? So you can see what I've done? I can't share any test data though..

Thanks

Yes please.

En-route - please can you only post the part that needs to be fixed back here :)

Thank you

I shall check and reply soon.

I had similar problem, and Pawan solution worked!