Post reply

xp_cmdshell

  • April 23, 2009 at 5:29 am

    #133409

    I got this following problem below and my database is existing in my server.

    when i am trying to replace my select query to fetch data frominformation_schema.tables its working fine.

    but when i am trying this coding

    DECLARE @FileName varchar(100),@bcpCommand varchar(2000)

    SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

    SET @bcpCommand = 'bcp " SELECT ApplicationID, Portname, IPaddress, RootPath, BannerImagePath, PrizeImagePath, ImagePath, Description, CreatedDate FROM [eLOTTERY]..[TB_eL_ApplicationPath] " queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -c '

    EXEC master..xp_cmdshell @bcpCommand

    i got following problem

    Password:

    SQLState = S0002, NativeError = 208

    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'eLOTTERY..TB_eL_ApplicationPath'.

    SQLState = 37000, NativeError = 8180

    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

    NULL

    thanks in advance

  • April 23, 2009 at 6:16 am

    #982793

    You might not have select permission on [eLOTTERY]..[TB_eL_ApplicationPath] !!

    Please check.

    "Don't limit your challenges, challenge your limits"

  • April 23, 2009 at 6:31 am

    #982805

    You don't specify any authentication information in your command line string.

    Greets

    Flo

  • April 23, 2009 at 6:53 am

    #982825

    I tried without giving login information also.

    if i copy the select statement and run it. its seems to be working fine.

  • April 23, 2009 at 7:22 am

    #982859

    Hi

    I just tried your script. Since I don't specify either "-T" or "-U user -P pwd" I also get an error. Try this to connect to a server by windows authentication:

    DECLARE @FileName varchar(100),@bcpCommand varchar(2000)

    SET @FileName = 'D:\Temp\Test\test.csv'

    SET @bcpCommand = 'bcp " SELECT TOP(10) N FROM Sandbox..Tally " queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -c -S MyServer -T'

    EXEC master..xp_cmdshell @bcpCommand

    Greets

    Flo

  • April 23, 2009 at 7:46 am

    #982894

    i tried login as windows authetication but its failed for me.

    it tried with code

    but it shows error

    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.

    any other solution

  • April 23, 2009 at 7:56 am

    #982912

    I'm still not sure if your BCP call is correct.

    If you want to connect to your server with windows authentication use "-T" (no user name and password):

    bcp "SELECT your query" queryout "D:\Anywhere\file.txt" -c -S YourServer -T

    If you want to connect to your server with sql server authentication use "-U usr -P pwd":

    bcp "SELECT your query" queryout "D:\Anywhere\file.txt" -c -S YourServer -U usr -P pwd

    You should also always use "-S" to specify the server.

    Could you please post your current statement? Sure, with pseudo user information, if included.

    Greets

    Flo

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

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