locked
sql 2008 r2 export result in csv RRS feed

  • Question

  • User1765954050 posted
    Im using the following command in SQL Powershell to generate csv of data returned by SQL.

    Invoke-Sqlcmd -Query "myp" -ServerInstance "." | Export-Csv -Path "d:\data\MyDatabaseSizes.csv" -NoTypeInformation
    The above command works perfectly fine in SQL Power Shell. but when I tried to run in from SQL using the following code

    DECLARE @cmd varchar(1000)
    SET @cmd = 'Invoke-Sqlcmd -Query "myp" -ServerInstance "." | Export-Csv -Path "d:\data\MyDatabaseSizes.csv" -NoTypeInformation'
    EXEC xp_cmdshell @cmd
    it give error that

    'Invoke-Sqlcmd' is not recognized as an internal or external command,

    Anyone please help me in running the command from SQL.

    Thanks
    Wednesday, June 29, 2016 1:12 PM

Answers

  • User-595703101 posted

    Please check https://msdn.microsoft.com/en-us/library/ms162773.aspx MSDN article

    Invoke is not used when command is executed on SQL Server

    In this sample tutorial, I inserted data returned from SQLCMD into a temp table and used in a SQL Server stored procedure.

    And a sample from MSDN is as follows

    sqlcmd -d AdventureWorks2012 -Q "SELECT TOP 5 FirstName FROM Person.Person;SELECT TOP 5 LastName FROM Person.Person;"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 30, 2016 6:10 AM
  • User77042963 posted

    You can save your powershell script and call it from xp_cmdshell:

     EXEC master..xp_cmdshell 'PowerShell.exe   "c:\temp\mypsscript.ps1" '

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 30, 2016 6:07 PM

All replies

  • User-595703101 posted

    Please check https://msdn.microsoft.com/en-us/library/ms162773.aspx MSDN article

    Invoke is not used when command is executed on SQL Server

    In this sample tutorial, I inserted data returned from SQLCMD into a temp table and used in a SQL Server stored procedure.

    And a sample from MSDN is as follows

    sqlcmd -d AdventureWorks2012 -Q "SELECT TOP 5 FirstName FROM Person.Person;SELECT TOP 5 LastName FROM Person.Person;"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 30, 2016 6:10 AM
  • User-595703101 posted

    Hi smtaqi,

    If you want to export SQL query results from SQL Server to text file in CSV format, please check SQL Server BCP command for SQL output to file

    Instead of SQLCMD you will be using BCP utility for data export to text file in csv format from SQL Server database query

    I hope it helps

    Here is how BCP sample looks like, please review the mentioned SQL tutorial for more detail

    SET @sql = 'bcp "SELECT ....." queryout "c:\filename.csv" -c -UTF8 -T -Slocalhost'

    Thursday, June 30, 2016 6:17 AM
  • User77042963 posted

    You can save your powershell script and call it from xp_cmdshell:

     EXEC master..xp_cmdshell 'PowerShell.exe   "c:\temp\mypsscript.ps1" '

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 30, 2016 6:07 PM