Post reply

BCP PROBLRM

  • March 8, 2009 at 7:53 am

    #103698

    HAI,

    DECLARE @FileName varchar(50),

    @bcpCommand varchar(2000)

    SET @FileName = REPLACE('C:\sqlscript'+'.txt ','/','-')

    SET @bcpCommand = 'bcp "SELECT * FROM JPCFHONYVLDATA.dbo.tbl_users" queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -c -T /t "|"-SINFOBIZ4'

    EXEC JPCFHONYVLDATA..xp_cmdshell @bcpCommand

    PRINT @bcpCommand

    when i execute above code ,I got follwing Error.

    How do i Solve, plzzzzzzz........

    SQLState = 08001, NativeError = 2

    Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].

    SQLState = HYT00, NativeError = 0

    Error = [Microsoft][SQL Native Client]Login timeout expired

    SQLState = 08001, NativeError = 2

    Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti

    ons.

    NULL

  • March 8, 2009 at 8:04 am

    #955660

    Hello

    Just read the error messages 😉

    Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].

    SQLState = HYT00, NativeError = 0

    Error = [Microsoft][SQL Native Client]Login timeout expired

    You the server is not available. Try a ping to the server or maybe start the sql server service.

    Greets

    Flo

  • March 8, 2009 at 9:18 am

    #955669

    Heh... That's not actually a BCP error... I'm pretty sure that the JPCFHONYVLDATA database doesn't actually have a copy of xp_CmdShell in it. The following command...

    [font="Arial Black"]EXEC [highlight="YELLOW"]JPCFHONYVLDATA..[/highlight]xp_cmdshell @bcpCommand[/font]

    ... should probably be...

    [font="Arial Black"]EXEC [highlight="YELLOW"]Master.dbo.[/highlight]xp_cmdshell @bcpCommand[/font]

    There may be other problems, but that's where I'd start. Try this, to be sure...

    EXEC JPCFHONYVLDATA..xp_CmdShell 'Dir C:\'

    ... if you get nothing back, then that's certainly part of the problem. Then try the following...

    EXEC Master.dbo.xp_CmdShell 'Dir C:\'

    ... if xp_CmdShell has actually been enabled, you should see the directory listing for "C:\" on the screen. If you can see that, then we can start troubleshooting the rest of the code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 8, 2009 at 4:21 pm

    #955734

    Hi Jeff

    Sure the xp_cmdshell is part of the master. First I saw the error messages of the BCP and I thought this is the only problem.

    Thanks for the correction!

    Greets

    Flo

  • March 8, 2009 at 5:25 pm

    #955745

    I may be missing something - so please correct me if I am wrong, but don't you need to specify which server you are going to connect to and whether or not you are using trusted connections?

    DECLARE @FileName varchar(50),

    @bcpCommand varchar(2000)

    SET @FileName = REPLACE('C:\sqlscript'+'.txt ','/','-')

    SET @bcpCommand = 'bcp "SELECT * FROM JPCFHONYVLDATA.dbo.tbl_users" queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -c -T /t "|"-SINFOBIZ4'

    EXEC JPCFHONYVLDATA..xp_cmdshell @bcpCommand

    PRINT @bcpCommand

    Without the server, the command is looking for the default local instance - and i would bet that the this is a named instance.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • March 8, 2009 at 6:07 pm

    #955753

    Jeffrey Williams (3/8/2009)

    I may be missing something - so please correct me if I am wrong, but don't you need to specify which server you are going to connect to and whether or not you are using trusted connections?

    DECLARE @FileName varchar(50),

    @bcpCommand varchar(2000)

    SET @FileName = REPLACE('C:\sqlscript'+'.txt ','/','-')

    SET @bcpCommand = 'bcp "SELECT * FROM JPCFHONYVLDATA.dbo.tbl_users" queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -c [highlight="YELLOW"]-T[/highlight] /t "|"[highlight="YELLOW"]-SINFOBIZ4[/highlight]'

    EXEC JPCFHONYVLDATA..xp_cmdshell @bcpCommand

    PRINT @bcpCommand

    Without the server, the command is looking for the default local instance - and i would bet that the this is a named instance.

    He did... see the highlighted section of the original code above...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 9, 2009 at 1:19 am

    #955810

    hai..

    actually I am new in sqlserver,

    EXEC JPCFHONYVLDATA..xp_CmdShell 'Dir C:\'

    when i execute above code ,I got "Could not locate entry in sysdatabases for database 'JPCFHONYVLDATA'. No entry found with that name. Make sure that the name is entered correctly." this message.

    How to i Solve,PLz Give Some Idea

  • March 9, 2009 at 4:24 pm

    #956459

    arasan_lotus (3/9/2009)

    hai..

    actually I am new in sqlserver,

    EXEC JPCFHONYVLDATA..xp_CmdShell 'Dir C:\'

    when i execute above code ,I got "Could not locate entry in sysdatabases for database 'JPCFHONYVLDATA'. No entry found with that name. Make sure that the name is entered correctly." this message.

    How to i Solve,PLz Give Some Idea

    Actually, I already told you... go back and look at my posts, please.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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