I'm trying to execute a stored procedure (which i know works) in T-SQL that then gets those results into a CSV file and puts that file into a directory. I'm not sure how to formulate that query, exactly though. Here's what i've tried thus far to no avail:
EXECUTE CLR_ExportQueryToCSV @QueryCommand = 'execute databaseName.dbo.StoredProcedureName', @FilePath = 'C:\Directory', @FileName = '\FileToExport.csv', @IncludeHeaders = 1 I realize CLR_ExportQueryToCSV doesn't exist. Is there any system stored procedure that will do what i'm wanting?
7 Answers
Answers 1
bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T Answers 2
Invoking CMD is one way to achieve it and can automate it
Declare @sql VARCHAR(max) declare @CsvFile NVARCHAR(500) DECLARE @cmd NVARCHAR(4000) set @sql = 'Exec [dbo].[Usp_CSVextract]' set @CsvFile = 'C:\Test.csv' SET @cmd = 'bcp '+CHAR(34)+@sql+CHAR(34)+' queryout '+CHAR(34)+@CsvFile+CHAR(34)+' -S '+@@servername +' -c -t'+CHAR(34)+','+CHAR(34)+' -r'+CHAR(34)+'\n'+CHAR(34)+' -T' exec master.dbo.xp_cmdshell @cmd Answers 3
Unfortunately there's no generic/supported method in SQL Server to do what you're asking.
If you're simply looking for a way to dump the results of a SQL query to CSV then I'd be more inclined to either write an SSIS package to do the job or a C# console app, either of which can be scheduled.
Here's an example in C#:
static void Main(string[] args) { WriteQueryResultsToCsv(@"c:\SqlResults.csv", "MyDbConnectionStringName", "select * from MyTable where x > @x", new SqlParameter("@x", SqlDbType.Int) {Value = 1}); } private static void WriteQueryResultsToCsv(string csvPath, string connectionStringName, string sql, params SqlParameter[] parameters) { // Requires reference to System.Configuration var connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString; using (var db = new SqlConnection(connectionString)) using (var cmd = new SqlCommand(sql, db)) { db.Open(); cmd.Parameters.AddRange(parameters); using (var dr = cmd.ExecuteReader()) using (var dw = new StreamWriter(csvPath)) using (var csv = new CsvWriter(dw)) // Requires CsvHelper package from NuGet { // Write column headers for (var c = 0; c < dr.FieldCount; c++) csv.WriteField(dr.GetName(c)); // Write data rows while (dr.Read()) { csv.NextRecord(); for (var c = 0; c < dr.FieldCount; c++) { csv.WriteField(dr.GetValue(c).ToString()); } } } } } Answers 4
There IS one way of doing what you're asking in SQL, but it's not neat or supported (AFAIK).
EXEC xp_cmdshell 'SQLCMD -S . -d MyDatabase -Q "select * from MyTable" -s "," -o "\\servername\output\result.csv" -W' You can find documentation for SQLCMD here, but essentially what this does is use the xp_cmdshell SP to execute the SQLCMD command line utility on the server and execute a sql statement, piping the output to a CSV file.
The params I've used are as follows:
-S: SQL Server name - . means current server -d: database name -Q: run SQL query and exit -s: column separator -o: output file. This is relative to the SQL Server, not your PC -W: dynamic column witdth By default SQL Server does not allow you to run xp_cmdshell, so you may need to run the following SQL to enable it.
-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO Answers 5
Your best bet I have found is to use a SSIS. Create a Execute SQL Task and run your stored procedure. Then create a data flow set of tasks to move the data from a temporary data table to your CSV file.
Do a search om this web site for how to make a SSIS package. There are a few really nice examples on how to do this.
Good luck to you.
Answers 6
CLR_ExportQueryToCSV is a clr stored procedure located at codeplex.
you need to install project at clr integration enabled sql server
Answers 7
i think it is possible to use bcp command check if this link is help full to you or not
0 comments:
Post a Comment