March 10, 2023 at 5:37 am
Hi everyone
I need to output a table into a CSV format so it can be consumed by another process. I would like to create a stored procedure to do this. I know how to create store procedures but do not know how to export results to a CSV. How can I do this?
Thank you
March 10, 2023 at 9:17 am
Use BCP. You will have to enable xp_cmdshell for it though which requires elevation. And be sure to enable it only for a minimum amount of time. It has some security implications.
BCP will place the file on the database server itself.
Since BCP is a command line utility, you have to use xp_cmdshell to call it from within a stored proc. But you can also run it from the command line.
March 10, 2023 at 2:34 pm
I'd suggest taking a look at sqlcmd instead of bcp. There's a ton more functionality there. While you can run sqlcmd, or bcp, from xp_cmdshell, it's not considered a good practice. I would instead suggest running externally from PowerShell or some other mechanism. Also, take a look at DBATools. There's some excellent export functionality built into that toolset.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2023 at 3:45 pm
What is the nature of the process that will be using the file? There's a lot of options to get a table out of SQL Server into a file and depending on what it's being used for some might make more sense.
March 10, 2023 at 5:32 pm
I find that the reason why most people poo-poo xp_CmdShell is because they don't actually know how to use it correctly and safely and they also forget that only the people or things that can use it can also turn it on. They also fail to realize that disabling it does nothing to prevent an attacker that breaks in with sysadmin or control privs for enabling it and using it. Of course if that happens, you'd be toast even if xp_CmdShell didn't exist.
I also find that a lot of people do things like storing power shell scripts with login and passwords in clear text and it's really difficult to audit for that eventuality but it seems no one ever bats an eye at such eventualities.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2023 at 5:39 pm
I find that the reason why most people poo-poo xp_CmdShell is because they don't actually know how to use it correctly and safely and they also forget that only the people or things that can use it can also turn it on. They also fail to realize that disabling it does nothing to prevent an attacker that breaks in with sysadmin or control privs for enabling it and using it. Of course if that happens, you'd be toast even if xp_CmdShell didn't exist.
I also find that a lot of people do things like storing power shell scripts with login and passwords in clear text and it's really difficult to audit for that eventuality but it seems no one ever bats an eye at such eventualities.
WAY too true.
Yeah, I don't want to scare people off of xp_cmdshell, but it is easy to muck it up, so I do tend to shy from recommending it. Odd spot.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2023 at 3:13 am
thank you everyone
I got this far but there is no csv file being produced. Does anyone know why? I got the script from online.
-- To allow advanced options to be changed.
EXECUTE sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXECUTE sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
EXEC master..xp_cmdshell 'sqlcmd -s, -W -Q "SELECT* FROM DBO.Stocks" | findstr /v /c:"-" /b > "C:\Users\Me\file.csv"'
In the output window in SS it has 2 row by 1 column result. The first record says "Access is denied.". The second record says "NULL". I think this may give clues. I borrowed some code here where it says how to enable xp_cmdshell:
I looked at Option2 from here:
Thank you everyone in advance for your time to help me with this issue.
March 11, 2023 at 3:38 am
What is the nature of the process that will be using the file? There's a lot of options to get a table out of SQL Server into a file and depending on what it's being used for some might make more sense.
I am using SS to create stock indicators. I use another program to visualize the stock indicators created in SS. The challenge is that the stock program uses CSV as an input with specific headers. I need to get the tables out of SS into a CSV format. Does that answer your question?
March 11, 2023 at 4:25 am
I was reading online that xp_cmdshell can expose me to security issues so is there a safe way to do what I am trying to do? I found this on our forum
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO
I will add this after the end of the script I posted above to close off the security hole
March 11, 2023 at 5:30 am
It doesn't close any "security hole". Only the people that can enable it can use it. Turning it off won't prevent that.
As to your "access denied" error, where is the "C:\Users\Me\:" directory? On the SQL Server? If so, it's likely that even SQL Server doesn't have the privs to write to your user directory.... nor should it. It shouldn't ever write to the C: drive on the server. This is one of the reasons why people do call xp_CmdShell a security risk. And, if it's on your local machine, you'd have to use a UNC to your local drive and five SQL Server privs to write there... again... bad idea and THAT would be a security risk.
Of course, having PowerShell or anything else do the same would also be a security risk. You need to setup a dedicated and protected directory when you're doing such things.
I'll also ask, regardless of the method you setup to actually do this, do you have written permission from you security officer to create this "out of server" file, never mind send it to some place else?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2023 at 1:23 am
What do I need to do to give SS the appropriate permissions? I plan to revoke them once the query is done.
March 12, 2023 at 2:08 am
It doesn't close any "security hole". Only the people that can enable it can use it. Turning it off won't prevent that.
As to your "access denied" error, where is the "C:\Users\Me\:" directory? On the SQL Server? If so, it's likely that even SQL Server doesn't have the privs to write to your user directory.... nor should it. It shouldn't ever write to the C: drive on the server. This is one of the reasons why people do call xp_CmdShell a security risk. And, if it's on your local machine, you'd have to use a UNC to your local drive and five SQL Server privs to write there... again... bad idea and THAT would be a security risk.
Of course, having PowerShell or anything else do the same would also be a security risk. You need to setup a dedicated and protected directory when you're doing such things.
I'll also ask, regardless of the method you setup to actually do this, do you have written permission from you security officer to create this "out of server" file, never mind send it to some place else?
The directory is located on my personal machine. It is on the same drive where SS is stored and running from.
March 12, 2023 at 7:32 am
Jeff Moden wrote:It doesn't close any "security hole". Only the people that can enable it can use it. Turning it off won't prevent that.
As to your "access denied" error, where is the "C:\Users\Me\:" directory? On the SQL Server? If so, it's likely that even SQL Server doesn't have the privs to write to your user directory.... nor should it. It shouldn't ever write to the C: drive on the server. This is one of the reasons why people do call xp_CmdShell a security risk. And, if it's on your local machine, you'd have to use a UNC to your local drive and five SQL Server privs to write there... again... bad idea and THAT would be a security risk.
Of course, having PowerShell or anything else do the same would also be a security risk. You need to setup a dedicated and protected directory when you're doing such things.
I'll also ask, regardless of the method you setup to actually do this, do you have written permission from you security officer to create this "out of server" file, never mind send it to some place else?
The directory is located on my personal machine. It is on the same drive where SS is stored and running from.
Then, I have to ask again, did you get permission from the Information Security department (or, failing to have one of those), permission from the Director of the IT department to put company information on a "personal" machine?
And, I'm sorry... I cannot, in good conscience, help you do such a thing. Again, this is one of the reasons people think that xp_CmdShell is dangerous but any method to do this is dangerous. If someone found out that you did this, it could lead to your termination as an employee, as well. Try getting another job after such a black mark.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2023 at 8:15 am
while still putting emphasis on what Jeff stated with regards to company data on a local PC I am still supplying what I consider would be the better approach to generate the files.
there is much to be said about this company data on local pc - almost any user that has access to any company data ends up with having it on their PC's - simple fact of opening a excel spreadsheet with sales results for example does put company data on the PC. this subject alone is out of scope on this thread (and it would be a very very long subject).
in my opinion any situation that required an output file to be generated from any SP being executed should normally (exceptions exist) be done outside SQL Server - being it through a SQL Server Agent job or through an external application.
solution below can be executed on a SSA job
<#
this script requires SQLSERVER PS Module to be installed - SQLPS shipped with SQL Server does not contain all required functionality
install-module SQLSERVER
Latest versions of SSMS now use the lastest PS module which should contain the required
note that if using a SQL Server Agent job Powershell step this WILL NOT work - running from SQL Agent should be done through a command line script step which kicks off normal powershell
the sql statements to execute can be a combination of multiple SQL Statements - directly selects, execution of SP's and so on
#>
Import-Module SQLSERVER
$ServerInstance = "xxxx" # sql server instance in the format of SERVER or SERVER\INSTANCENAME to connect to
<#
multiple recordsets returned
add the -OutputAs Dataset so we have multiple tables being returned and we can process each one individually
#>
$sql = "select serverproperty('MachineName') machinename
, serverproperty('ProductVersion') productversion
, serverproperty('InstanceName') instancename
, serverproperty('InstanceDefaultDataPath') instancedefaultdatapath
, serverproperty('InstanceDefaultLogPath') instancedefaultlogpath
, db_name() databasename
;
select name
from msdb.sys.databases;"
$ds = SQLSERVER\invoke-sqlcmd -ServerInstance $ServerInstance -Query $sql -OutputAs dataset
$ds.tables.table[0]|Export-Csv -Path "c:\temp\serverproperties.csv" -NoTypeInformation -Encoding ASCII
$ds.tables.table[1]|Export-Csv -Path "c:\temp\databasenames.csv" -NoTypeInformation -Encoding ASCII
# single recordset returned -- output directly to the file
$sql = "select name
, compatibility_level
, collation_name
, is_read_only
, state_desc
, snapshot_isolation_state_desc
, is_read_committed_snapshot_on
, recovery_model_desc
from sys.databases;"
$ds = SQLSERVER\invoke-sqlcmd -ServerInstance $ServerInstance -Query $sql|Export-Csv -Path "c:\temp\databasedetails.csv" -NoTypeInformation -Encoding ASCII
March 12, 2023 at 10:39 pm
water490 wrote:Jeff Moden wrote:It doesn't close any "security hole". Only the people that can enable it can use it. Turning it off won't prevent that.
As to your "access denied" error, where is the "C:\Users\Me\:" directory? On the SQL Server? If so, it's likely that even SQL Server doesn't have the privs to write to your user directory.... nor should it. It shouldn't ever write to the C: drive on the server. This is one of the reasons why people do call xp_CmdShell a security risk. And, if it's on your local machine, you'd have to use a UNC to your local drive and five SQL Server privs to write there... again... bad idea and THAT would be a security risk.
Of course, having PowerShell or anything else do the same would also be a security risk. You need to setup a dedicated and protected directory when you're doing such things.
I'll also ask, regardless of the method you setup to actually do this, do you have written permission from you security officer to create this "out of server" file, never mind send it to some place else?
The directory is located on my personal machine. It is on the same drive where SS is stored and running from.
Then, I have to ask again, did you get permission from the Information Security department (or, failing to have one of those), permission from the Director of the IT department to put company information on a "personal" machine?
And, I'm sorry... I cannot, in good conscience, help you do such a thing. Again, this is one of the reasons people think that xp_CmdShell is dangerous but any method to do this is dangerous. If someone found out that you did this, it could lead to your termination as an employee, as well. Try getting another job after such a black mark.
There is a serious misunderstanding here....there is no company data etc. it is my personal stuff on my computer.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply