Attempting to create script that automatically sends query result to Excel

[kdjonesmtb2]

exec master..xp_cmdshell'bcp "select top 1 * from plandata.dbo.member" queryout C:\queryoutput.xls -o "C:\querycommanddetails.txt" -T -c -C RAW'

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

Is there another method if cmdshells do not work in my SQL environment

 

[gmmastros]

There is a configuration setting to enable xp_cmdshell.

To view the configuration option for this...

sp_configure 'Show Advanced Options', 1
Reconfigure with override
GO
sp_configure 'xp_cmdshell'

Run the code above and report back the "run_value" that is returned.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kdjonesmtb2]

It looks like I do not have permissions to perform this operation

Is there any other way to perform the tasks I am trying to do without the cmdshell?

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 2
You do not have permission to run the RECONFIGURE statement.

 

[gmmastros]

You could try running bcp from the command line.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[philhege]

You need to get with the DBA and plead your case for this functionality. George mentioned bcp, but you'd still need xp_cmdshell rights to get to the command line from within any SQL process.

If the DBA's answer is "No", you'll have to spin up a .NET app that creates the Excel file, or create an Excel ODC file that connects to the SQL instance and runs the query/SP.

http://office.microsoft.com/en-us/e....aspx#BMcreate_an_office_data_connection_(odc

-----------
With business clients like mine, you'd be better off herding cats.

 

[gmmastros]

Phil,

I meant, "Run BCP outside of the SQL Process". You know.... from a workstation or directly on the server.

Thanks for clarifying for me.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

You code build your select directly into Excel so when you open the spreadsheet it runs the SQL. There is a lot of information available on doing that on the internet. Here is one link:

http://office.microsoft.com/en-us/excel-help/connect-to-import-sql-server-data-HA010217956.aspx

 

[TheBugSlayer]

If this something you need to to only one time then you could do it manually: run the SELECT query in Management Studio, then click the top left corner of the Results pane to select the whole result set, righ-click anywhere on the selection and choose Save Results As and save as a CSV file to later import in Excel, or choose Copy With Headers and paste inside Excel. Otherwise, as someone advised above, make your case to your DBA or the person in charge.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[SkipVought]

As Tyson posted, the absolute simplest way to update an Excel workbook 'automatically' is from Excel itself using MS Query. No macros or fancy stuff required!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]