Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Date/Time stamp

  1. #1
    Join Date
    Mar 2006
    Posts
    127

    Date/Time stamp

    Hi All,

    How can I add the date/time stamp to the end of the text file? This file is being created as a result of query output of the bcp command:

    'bcp "Select * from ##Test" queryout C:\Test\JobForDB.txt -S servername -U user -P password -c'

    I need to add date/time stamp to the JobForDB.txt file before the period.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Try this:

    'bcp "Select * from ##Test; select getdate()" queryout C:\Test\JobForDB.txt -S servername -U user -P password -c'

  3. #3
    Join Date
    Mar 2006
    Posts
    127
    Tried it, didn't work.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Try GO instead of ;

    'bcp "Select * from ##Test go select getdate()" queryout C:\Test\JobForDB.txt -S servername -U user -P password -c'

  5. #5
    Join Date
    Mar 2006
    Posts
    127
    Nope, that didn't work either.

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    you could do this
    After executing your BCP execute the following commands

    echo %date% %time% >>C:\Test\JobForDB.txt

  7. #7
    Join Date
    Mar 2006
    Posts
    127
    That didn't work either, but I am trying something else and I am getting an error message. May be you can help me with this.

    This is the script I am running now:

    DECLARE @checkdbcmd varchar(255)
    DECLARE @bcpcmd varchar(255)
    DECLARE @datetime varchar(50)

    CREATE TABLE ##Test (Check_Log VARCHAR(1000))

    SET @checkdbcmd = 'osql -S server -U user -P password -Q "DBCC CHECKDB"'
    SET @datetime = 'osql -S server -U user -P password -Q "select convert(varchar,getdate(),112) + substring(convert(varchar,getdate(),108),1,2) + substring(convert(varchar,getdate(),108),4,2)"'
    SET @bcpcmd = 'bcp "Select * from ##Test" queryout "C:\Test\Integrity For Test' + @datetime + '.txt" -S server -U user -P password -c'

    INSERT ##Test(Check_Log)
    EXEC master..xp_cmdshell @checkdbcmd

    EXEC master..xp_cmdshell @datetime

    EXEC master..xp_cmdshell @bcpcmd


    IF EXISTS (SELECT * FROM ##Test
    WHERE Check_Log = 'CHECKDB found 0 allocation errors and 0 consistency errors in database')
    RETURN
    ELSE
    Notify

    drop table ##Test

    Here is the error message:

    SQLState = S1000, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

    How can I get around it?

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Where is the data file? On sql server's c:\ drive?

  9. #9
    Join Date
    Mar 2006
    Posts
    127
    Data file for this database is on another drive.

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Then you have to use unc name for the file like \\server\share\file, and ensure sql service account has permission to access that location.

  11. #11
    Join Date
    Mar 2006
    Posts
    127
    So if the path to a file is this:

    C:\Test\Reports\report for integrity.txt

    and it is on ServerDev

    then it should look like this:

    "\\ServerDev\Test\Reports\report' + @datetime + '.txt"

    Is this correct?

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    "\\ServerDev\c$\Test\Reports\report' + @datetime + '.txt"

  13. #13
    Join Date
    Mar 2006
    Posts
    127
    I've tried it and still getting this error.

    SQLState = S1000, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    What's sql service account? Does it have permission to open that file?

  15. #15
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    why cant you run the BCP command first and rename the file afterwards?

    Assuming your output file name is output.txt and if you like to rename it to output_01_11_2007_14_17_26.txt, then execute the following DOS command.

    ren output.txt "output_%date:~4,2%_%date:~7,2%_%date:~10,4%_%time :~0,2%_%time:~3,2%_%time:~6,2%.txt"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •