We help IT Professionals succeed at work.

BCP not extractin to csv

Hello,
I have this exact SQL that I am trying to execute in  Sql server management studio but tis not creating a csv extract at all…please see the attachments pic…no file is getting created….pleas help with this BCP thing

Thank you
declare @s varchar(30)
select @s = 'bcp ' +
     ' "SELECT * FROM [toyDB]..[a_toyTable] " ' +
     'queryout "C:\Test1\test.CSV" ' +
     '-T -c ' +
     '-S '
  exec master..xp_cmdshell @s 

Open in new window

error-null-result-no-csv.jpg
Comment
Watch Question

Author

Commented:
I tried the BCP another way...see pics II....still getting error....this is frustrating...
bcp-type-2-error.jpg
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Two things:

1.

The xp_cmdshell it's disabled by default. Did you enable it?

2.

localhost isn't a recognized server or instance name for SQL Server. You may want to replace it by servername or servername\instancename if it's a named instance.

Author

Commented:
Hello Victor,
Thank you replying...
How do i activate the cmdshell?
Also, where can find the named instance within  [my computer]? I know the server name if that whats you meant?
IT Engineer
Distinguished Expert 2017
Commented:
You can check if the xp_cmdshell is enabled my running the following code:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell'
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Open in new window

If the returned column has an 1 then it's enabled. Zero is disabled.

For the instance name you can check in the SQL Server Configuration Manager but if you only have a SQL Server instance and if you used the default values then the server name should be used.

Author

Commented:
sorry was out of state for a while