Please help to use BCP

Question

Post reply

Please help to use BCP

panwar.jt

Default port

Points: 1418
More actions
June 24, 2009 at 12:36 am

#131460

I am using following command
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM [demo2_rmps]..mms_menu " queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U mogatest -P mogatest -c'
EXEC master..xp_cmdshell @bcpCommand
But it is given following error
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL
Please help me
Thnks
KD

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

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

Eswin SSCrazy Eights Points: 8320 More actions · Answer 1

See if xp_cmdshell is enabled or not

you can find that in surface area config,

Using xp_cmdshell you can only run one command at a time unless you give "&" between the commands.

example:

xp_cmdshell 'ping ip_add1 & ping ip_add2'

Tanx 😀

Pandian S SSChasing Mays Points: 625 More actions · Answer 2

Try this....

Microsoft SQL Server 2005 --> Configuration Tools -->

SQL Server Configuration Manager -->

SQL Server 2005 Network Configuration --> Enable TCP-IP

panwar.jt Default port Points: 1418 More actions · Answer 3

Thanks for reply

I want to know how we can make a CSV file instead of Txt file

Eswin SSCrazy Eights Points: 8320 More actions · Answer 4

Eswin

SSCrazy Eights

Points: 8320

June 24, 2009 at 3:25 am

#1014492

Give .csv extension instead of .txt

Tanx 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

panwar.jt (6/24/2009)
I am using following command
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM [demo2_rmps]..mms_menu " queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U mogatest -P mogatest -c'
EXEC master..xp_cmdshell @bcpCommand
But it is given following error
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL
Please help me
Thnks
KD

You are missing a switch on the BCP command line that tells bcp the server, -Sservername[\instancename]