SQL Server CSV Exports using SQL Server Agent & Powershell?

Question

I found this answer using xp_cmdshell

SQL Server Agent Job - Export results to Tab Delimited File?

That would work fine for me except I need to provide a date parameter to a sql query. The date param gets populated via another sql query. Was thinking about using powershell.

Is there someone who has worked with this scenario and is willing to share scripts + experiences?

I could write console app in C# (or a class that is invoked via power shell once I work out how that is done). Just after the simplest, most clutter free way forward.

btw: my last use of xp_cmdshell was mired by permissioning frustrations. Last time I had to teach a sys admin how to do it via email + also troubleshoot their lockdown setup via email in a different timezone. As a result I've got some inbuilt hesitancy ;-). Thankfully I've got free reign in this situation though.

Nonym · Accepted Answer · 2011-11-23 14:24:13Z

Can you try this?:

If you want it as a stored procedure where a date can be manually or programmatically passed:

CREATE PROCEDURE bcp_test_with_date_parameter
    @paramDate DATETIME
AS
BEGIN

    DECLARE @bcpCmd varchar(2000)
    SET @bcpCmd = 'bcp "SELECT * FROM dbname.schema.tablename' 
    SET @bcpCmd = @bcpCmd + 'WHERE CONVERT(VARCHAR, datecolumn, 101) = ''' + CONVERT(VARCHAR, @paramDate, 101) + ''' '
    SET @bcpCmd = @bcpCmd + ' ORDER BY columnnameofchoice" queryout "'
    SET @bcpCmd = @bcpCmd + '"C:\tblNameData.txt" -T -c'

    EXEC master..xp_cmdshell @bcpCmd

END

But if you just want to grab the date from another table you could use this:

DECLARE @bcpCmd varchar(2000)
DECLARE @dateHolder DATETIME
SELECT @dateHolder = CONVERT(VARCHAR, datecolumn, 101) 
    FROM dateparametersourcetable
    WHERE <place conditions here>

SET @bcpCmd = 'bcp "SELECT * FROM dbname.schema.tablename' 
SET @bcpCmd = @bcpCmd + 'WHERE CONVERT(VARCHAR, datecolumn, 101) = ''' + @dateHolder + ''' '
SET @bcpCmd = @bcpCmd + ' ORDER BY columnnameofchoice" queryout "'
SET @bcpCmd = @bcpCmd + '"C:\output.txt" -T -c'

EXEC master..xp_cmdshell @bcpCmd

The one right above can also be placed in a stored procedure, it's really up to how you want to do it..

Chad Miller · Accepted Answer · 2011-11-23 13:29:28Z

1

I would suggest before worrying about SQL Agent to first solve the problem of how to generate the CSV. You can then schedule the Powershell script in SQL Agent. Here's some code I tested to generate a CSV file from a SQL query using date param from another query:

$orderDate = Invoke-Sqlcmd -ServerInstance "Win7boot\sql1" -Database AdventureWorks -Query "select max(OrderDate) As OrderDate from Sales.SalesOrderHeader" | %{$_.OrderDate} 
Invoke-Sqlcmd -ServerInstance "Win7boot\sql1" -Database AdventureWorks -Query "select * from Sales.SalesOrderHeader where OrderDate < '$orderDate'" | Export-Csv -Path "C:\Users\Public\salesorder.csv" -NoTypeInformation -Force

This should work in SQL Agent just as it does in the Powershell console. You'll need to setup a Powershell job step.

Note if the date param is being obtained from the same server instance as the main query you could combine the query into one:

"select * from Sales.SalesOrderHeader where OrderDate < (select max(OrderDate) from Sales.SalesOrderHeader)"

answered Nov 23, 2011 at 13:29

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

3 Comments

sgtz Over a year ago

there are some advantages here - the script can exist as a file on the OS + link into an svn folder. I have done a lot of stored procs in the past, but this approach seems preferable. It looks really lightweight.

sgtz Over a year ago

btw: what does "%{$_.OrderDate}" do? I need to digest this a bit more first I think.

Chad Miller Over a year ago

% is a an alias for foreach-object. See get-alias for full list of aliases. Powershell returns objects which have properties and methods, but we're only interested in the OrderData property. What this does is iterate through each object i.e. the $_ represents the current object in the pipeline (only one object is returned in this case), grabs the OrderDate property and assigns it to the $orderDate variable.

Collectives™ on Stack Overflow

SQL Server CSV Exports using SQL Server Agent & Powershell?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related