bcp export problem

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 1

bcp is not supposed to show column headers. Try sqlcmd instead.

Oracle765 SSCrazy Points: 2862 More actions · Answer 2

ok i changed bcp to sqlcmd as follows but it does not work

Any ideas

USE [TestData]

GO

/****** Object: StoredProcedure [dbo].[exporttocsv] Script Date: 06/27/2013 12:23:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[exporttocsv]

@importedquery nvarchar(1000), /* The query to be executed */

@importedcsvname nvarchar(150)/* To name the exported file back to the original name */

as

BEGIN

DECLARE @path varchar(50)

DECLARE @filename varchar(30)

DECLARE @dbname varchar(30)

DECLARE @sql varchar(2000)

SELECT @path = 'C:\inetpub\wwwroot\cleansed\'

SELECT @filename = @importedcsvname + ' -c -t, -d '

SELECT @dbname = 'TestData'

SELECT @sql = 'sqlcmd "' + @importedquery + '" queryout ' + @path + @filename + @dbname

--print @sql

EXEC master..xp_cmdshell @sql

end

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 3

Queryout is not a keyword in sqlcmd. You should use -Q "select * from table".

-c (batch terminator) and -t (timeout) are not necessary.

But what is necessary is -S (server name), -U (login) or -E (trusted connection).

Before executing it in xp_cmdshell, run it in DOS prompt, until you'll get needed result.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Hopefully your more recent post asking about calling sqlcmd from PHP means you are not going to be using xp_cmdshell...which is a very wise choice.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato