CODEWITHSUNDEEP
sql-serverexport-to-csvbcp
ger
ger

Reputation: 414

Trying to save a txt file create with BCP on the local SQL Server

I´m trying to export a text file, with this instructions...

DECLARE @selectText VARCHAR(999)
DECLARE @output INT
DECLARE @result INT

EXEC @output = master.dbo.xp_fileexist  'DIR "C:\TextoPlano\" /B', @result OUTPUT

print @output


IF @output = 1
      PRINT 'File Donot exists'--CREATE THE DIRECTORY
ELSE
BEGIN
      PRINT 'File exists'
      SELECT  @selectText = 'bcp "SELECT * FROM [pruebaBD].[dbo].[Cliente]" queryout "C:\TextoPlano\ViewOrdenCompra.txt" -c -S xxxxxxx -U sa -P xxxxxx'
      PRINT @selectText
      EXEC master..xp_cmdshell @selectText
END

...but sql server shows me this

when execute this

EXEC master..xp_cmdshell 'hostname'

appears my SqlServer name, and all files was saved in c:\ sqlServer

HOW Do I make to save on the local computer that is installed SQL Server???

ex: My Computer C:\TextoPlano

Upvotes: 0

Views: 947

Answers (1)

Rajesh Ranjan
Rajesh Ranjan

Reputation: 537

  1. Enable XP_CMDSHELL

    -- 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

  2. Verify if the specified path exists
  3. Verify do you have permission to write to write the directory?
  4. Verify does SQL Server service account has permission to write the directory?

Thanks

Upvotes: 1

Related Questions