-
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
-
Try this:
'bcp "Select * from ##Test; select getdate()" queryout C:\Test\JobForDB.txt -S servername -U user -P password -c'
-
-
Try GO instead of ;
'bcp "Select * from ##Test go select getdate()" queryout C:\Test\JobForDB.txt -S servername -U user -P password -c'
-
Nope, that didn't work either.
-
you could do this
After executing your BCP execute the following commands
echo %date% %time% >>C:\Test\JobForDB.txt
-
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?
-
Where is the data file? On sql server's c:\ drive?
-
Data file for this database is on another drive.
-
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.
-
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?
-
"\\ServerDev\c$\Test\Reports\report' + @datetime + '.txt"
-
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
-
What's sql service account? Does it have permission to open that file?
-
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
-
Forum Rules
|
|