0

I am trying to create a stored procedure in SSMS to export the query result to CSV. But I am getting below error while creating.

SQL statement:

CREATE PROCEDURE SelectUsers
AS
    SELECT * 
    FROM [IMBookingApp].[dbo].[usertest]
    INTO OUTFILE 'C:/S3/users.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
GO;

Error

Msg 156, Level 15, State 1, Procedure SelectUsers, Line 4 [Batch Start Line 0]
Incorrect syntax near the keyword 'INTO'

Any help would be appreciated.

Rahul Satal
  • 2,107
  • 3
  • 32
  • 53
  • Look at https://stackoverflow.com/questions/16079666/how-to-produce-an-csv-output-file-from-stored-procedure-in-sql-server. THere is no flagged answer there one with a vote that will solve it for you – zip Jan 05 '20 at 18:26
  • 2
    That syntax doesn't exist in T-SQL at all; there is no `INTO OUTFILE` command. This seems like you would be better off with `bcp` in a CLI or `OPENROWSET`. – Thom A Jan 05 '20 at 18:26
  • On using this command in SSMS - `BCP master..[IMBookingApp].[dbo].[userTEST] out c:\S3\users.csv -c -t, -T ` . I am getting error as `Incorrect syntax near '.'` and it also saying `Could not find stored procedure 'BCP'` – Rahul Satal Jan 05 '20 at 18:38
  • Please use single '.' (BCP master.[IMBookingApp].[dbo].[userTEST]). – sacse Jan 05 '20 at 18:40
  • It is giving the same error even with single '.' – Rahul Satal Jan 05 '20 at 18:43
  • 1
    *"On using this command in SSMS"* @RahulSatal ... I said *"with `bcp` in a **CLI**"*. SSMS isn't a CLI, it's an IDE. Powershell is a CLI. – Thom A Jan 05 '20 at 18:51

3 Answers3

0

try the following:

exec master..xp_cmdshell 'bcp "[IMBookingApp].[dbo].[userTEST]" out "c:\S3\users.csv" -c -t, -T'

or try 

bcp "select * from [IMBookingApp].[dbo].[userTEST]" queryout "c:\S3\users.csv" -c -t, -T

from the command line

sacse
  • 3,634
  • 2
  • 15
  • 24
  • It is giving this error -`The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.` – Rahul Satal Jan 05 '20 at 18:46
  • Please follow the steps provided in the link: https://stackoverflow.com/questions/2016669/getting-execute-permission-to-xp-cmdshell – sacse Jan 05 '20 at 18:48
  • I have looked into that it and it seems use of `xp_cmdshell` is not recommended. Also, I am working on production database, So I don't want to make any changes to it. Can you suggest any other way? – Rahul Satal Jan 05 '20 at 18:51
  • You would actually be making changes to the Server, not the database, if you *were* to enable `xp_cmdshell`, @RahulSatal . – Thom A Jan 05 '20 at 18:52
  • Now getting the error - `You do not have permission to run the RECONFIGURE statement.` Upon some research, I found that for making that change I need to be either sysadmin or serveradmin and unfortunately which I am not. – Rahul Satal Jan 05 '20 at 19:02
  • try the following by going to Query-->SQLCMD Mode :OUT c:\S3\users.csv SELECT * FROM [IMBookingApp].[dbo].[userTEST] – sacse Jan 05 '20 at 19:07
  • did you try running the bcp utility from the command prompt? try bcp "select * from [IMBookingApp].[dbo].[userTEST]" queryout "c:\S3\users.csv" -c -t, -T from the command line. – sacse Jan 05 '20 at 19:17
0

CREATE PROCEDURE SelectUsers AS

SELECT * INTO OUTFILE 'C:/S3/users.csv'
FROM [IMBookingApp].[dbo].[usertest]
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';GO;
Sumit Tiwary
  • 29
  • 1
  • 1
  • 9
  • Thanks for responding Sumit. But I am using SQL Server which doesn't support `OUTFILE`. It only works with MYSQL. – Rahul Satal Jan 06 '20 at 12:46
  • You can refer to this blog and this is going to resolve you issue for sure. https://www.sqlservercentral.com/blogs/export-a-ssms-query-result-set-to-csv – Sumit Tiwary Jan 06 '20 at 13:03
0

You can refer to this blog and this is going to resolve you issue for sure. https://www.sqlservercentral.com/blogs/export-a-ssms-query-result-set-to-csv

Sumit Tiwary
  • 29
  • 1
  • 1
  • 9