September 3, 2015 at 1:29 am
I'm trying to run the following script and I get the following error, would you please let me know what I'm doing wrong?
error message:
Conversion failed when converting date and/or time from character string.
Thank you in advance!
/* LOADS ALL TRN FILES IN A GIVEN DIRECTORY AND THEN RESTORES THEM TO THE APPROPRIATE DATABASE */
SET NOCOUNT ON
-- 1 - Variable declarations
DECLARE @CMD1 varchar(5000)
DECLARE @CMD2 varchar(5000)
DECLARE @FilePath varchar(500)
DECLARE @SQLCmd nvarchar(2500)
DECLARE @DBToRunOn nvarchar(15)
SET @DBToRunOn = 'DATABASENAME'
DECLARE @DBAbbr nvarchar(2)
SET @DBAbbr = ''
-- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list
CREATE TABLE #OriginalFileList (
Col1 varchar(1000) NULL
)
-- 3 - Create the #ParsedFileList temporary table to suppor the cleansed file list
CREATE TABLE #ParsedFileList (
PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
DateTimeStamp datetime NOT NULL,
LSN int,
FileSize varchar(150) NOT NULL,
FileName1 varchar (255) NOT NULL
)
-- 4 - Initialize the variables
SELECT @CMD1 = ''
SELECT @CMD2 = ''
SELECT @FilePath = '\\FILE LOCATION'
-- 5 - Build the string to capture the file names in the restore location
SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + '\*.trn' + char(39)
-- 6 - Build the string to populate the #OriginalFileList temporary table
SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) + 'EXEC ' + @CMD1
-- 7 - Execute the string to populate the #OriginalFileList table
EXEC (@CMD2)
-- 8 - Delete unneeded data from the #OriginalFileList
DELETE FROM #OriginalFileList WHERE COL1 IS NULL
DELETE FROM #OriginalFileList WHERE COL1 LIKE '%Volume%'
DELETE FROM #OriginalFileList WHERE COL1 LIKE '%Directory%'
DELETE FROM #OriginalFileList WHERE COL1 LIKE '%<DIR>%'
DELETE FROM #OriginalFileList WHERE COL1 LIKE '%bytes%'
-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList
(DateTimeStamp,
LSN,
FileSize,
FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
ORDER BY LSN
-- ********************************************************************************
-- Process the data from the #ParsedFileList table
DECLARE @Count int,
@TotalRecs int,
@File varchar(75)
SET @TotalRecs = (SELECT COUNT(1)
FROM #ParsedFileList)
SET @Count = 1
WHILE @Count <= @TotalRecs
BEGIN
SET @File = (SELECT FileName1
FROM #ParsedFileList
WHERE PFLID = @Count)
PRINT('Processing File ' + CAST(@Count as varchar(2)) + '/' + CAST(@TotalRecs as varchar(2)))
IF @Count = @TotalRecs
BEGIN
SET @SQLCmd = '
RESTORE LOG ['+ RTRIM(@DBToRunOn) + ']
FROM DISK = N''\\FILE LOCATION' + RTRIM(@File) + '''
WITH RECOVERY,
NOUNLOAD,
STATS = 10'
END
EXEC sp_executesql @SQLCmd
SET @count = (@count + 1)
END
-- ********************************************************************************
-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE #ParsedFileList
SET NOCOUNT OFF
GO
I now have the following error message:
Conversion failed when converting date and/or time from character string.
The line it seems to have an issue with is at the beginning of the following script:
CREATE
TABLE#ParsedFileList(
PFLID
INTPRIMARYKEYIDENTITY (1,1)NOTNULL,
DateTimeStamp
datetimeNOTNULL,
LSN
int,
FileSize
varchar(150)NOTNULL,
FileName1
varchar (255)NOTNULL
)
September 3, 2015 at 2:30 am
in section 9
-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList
(DateTimeStamp,
LSN,
FileSize,
FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
ORDER BY LSN
comment out the INSERT part and run the query - instead of running the results into the table, it will return them to your screen. Check that column 1 values can be converted to date.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2015 at 4:21 am
Thank you for your reply!
I commented out the insert into statement but all I get in return is the details about the files, e.g. file name file extension. LSN etc But I need the following to run which will perform the restore of the latest transaction log file:
-- Process the data from the #ParsedFileList table
DECLARE @Count int,
@TotalRecs int,
@File varchar(75)
SET @TotalRecs = (SELECT COUNT(1)
FROM #ParsedFileList)
SET @Count = 1
WHILE @Count <= @TotalRecs
BEGIN
SET @File = (SELECT FileName1
FROM #ParsedFileList
WHERE PFLID = @Count)
PRINT('Processing File ' + CAST(@Count as varchar(2)) + '/' + CAST(@TotalRecs as varchar(2)))
IF @Count = @TotalRecs
BEGIN
SET @SQLCmd = '
RESTORE LOG ['+ RTRIM(@DBToRunOn) + ']
FROM DISK = N''\\FILE LOCATION' + RTRIM(@File) + '''
WITH RECOVERY,
NOUNLOAD,
STATS = 10'
END
EXEC sp_executesql @SQLCmd
SET @count = (@count + 1)
END
-- ********************************************************************************
-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE #ParsedFileList
SET NOCOUNT OFF
GO
Thank you!
September 3, 2015 at 4:25 am
tt-615680 (9/3/2015)
Thank you for your reply!I commented out the insert into statement but all I get in return is the details about the files, e.g. file name file extension. LSN etc But I need the following to run which will perform the restore of the latest transaction log file:
-- Process the data from the #ParsedFileList table
DECLARE @Count int,
@TotalRecs int,
@File varchar(75)
SET @TotalRecs = (SELECT COUNT(1)
FROM #ParsedFileList)
SET @Count = 1
WHILE @Count <= @TotalRecs
BEGIN
SET @File = (SELECT FileName1
FROM #ParsedFileList
WHERE PFLID = @Count)
PRINT('Processing File ' + CAST(@Count as varchar(2)) + '/' + CAST(@TotalRecs as varchar(2)))
IF @Count = @TotalRecs
BEGIN
SET @SQLCmd = '
RESTORE LOG ['+ RTRIM(@DBToRunOn) + ']
FROM DISK = N''\\FILE LOCATION' + RTRIM(@File) + '''
WITH RECOVERY,
NOUNLOAD,
STATS = 10'
END
EXEC sp_executesql @SQLCmd
SET @count = (@count + 1)
END
-- ********************************************************************************
-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE #ParsedFileList
SET NOCOUNT OFF
GO
Thank you!
You're jumping the gun a little...
"Check that column 1 values can be converted to date."
Did you check?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2015 at 4:41 am
I have now changed the date time to be converted to date but I still get the following message:
Conversion failed when converting date and/or time from character string.
-- 9 - Populate the #ParsedFileList table with the final data
--INSERT INTO #ParsedFileList
--(DateTimeStamp,
-- LSN,
-- FileSize,
-- FileName1)
SELECT --LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
CONVERT(datetime, col1) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
ORDER BY LSN
Thank you!
September 3, 2015 at 6:02 am
tt-615680 (9/3/2015)
I have now changed the date time to be converted to date but I still get the following message:Conversion failed when converting date and/or time from character string.
-- 9 - Populate the #ParsedFileList table with the final data
--INSERT INTO #ParsedFileList
--(DateTimeStamp,
-- LSN,
-- FileSize,
-- FileName1)
SELECT --LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
CONVERT(datetime, col1) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
ORDER BY LSN
Thank you!
Good, we've identified a point where the error occurs.
I'll try this again:
"Check that column 1 values can be converted to date."
Did you check?
What did you see?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2015 at 6:34 am
CONVERT(date, col1) AS 'DateTimeStamp', is the line I've chabged unless I need to change it to something else?
and I get the following error:
Conversion failed when converting date and/or time from character string.
Thank you!
September 3, 2015 at 7:14 am
tt-615680 (9/3/2015)
CONVERT(date, col1) AS 'DateTimeStamp', is the line I've chabged unless I need to change it to something else?and I get the following error:
Conversion failed when converting date and/or time from character string.
Thank you!
The first lesson of course 101 for SQL Server goes something like this: "Look at the data". That's what I'm asking you to do. Look at the first column of the output, the column which you are attempting to convert to a date, and report back in general terms what you see. Is all of it/some of it/none of it convertible to datetime? You don't have to look at every row, just a sample. If it's not too tough, copy and paste a few samples up here.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2015 at 8:32 am
Try running the following code:
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
WHERE TRY_PARSE(LTRIM(SUBSTRING (Col1, 1, 20)) AS DATETIME) IS NULL
ORDER BY LSN
This should list all rows where it cannot convert the first substring to DATETIME.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 3, 2015 at 8:53 am
I'm sorry if I'm not being clear but I can only see what I've already sent you, basically, when I run the following:
-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList
(DateTimeStamp,
LSN,
FileSize,
FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
ORDER BY LSN
I get the following error message:
Msg 242, Level 16, State 3, Line 56
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
When change the conversion statement and run the following query:
-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList
(DateTimeStamp,
LSN,
FileSize,
FileName1)
SELECT
CONVERT(DATE, CONVERT(CHAR(8), Col1)) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
ORDER BY LSN
I get the following error:
Msg 241, Level 16, State 1, Line 56
Conversion failed when converting date and/or time from character string.
I'm sure it is an easy fix but I really cannot see where it is going wrong.
Thank you again!
September 3, 2015 at 9:03 am
tt-615680 (9/3/2015)
I'm sorry if I'm not being clear but I can only see what I've already sent you, basically, when I run the following:-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList
(DateTimeStamp,
LSN,
FileSize,
FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
ORDER BY LSN
I get the following error message:
Msg 242, Level 16, State 3, Line 56
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
When change the conversion statement and run the following query:
-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList
(DateTimeStamp,
LSN,
FileSize,
FileName1)
SELECT
CONVERT(DATE, CONVERT(CHAR(8), Col1)) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
ORDER BY LSN
I get the following error:
Msg 241, Level 16, State 1, Line 56
Conversion failed when converting date and/or time from character string.
I'm sure it is an easy fix but I really cannot see where it is going wrong.
Thank you again!
Neither can we - because you haven't responded to any requests. Did you try running the code Drew posted? We can't help you if you keep refusing to carry out our suggestions.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2015 at 9:13 am
Run this after running the first 8 steps you posted earlier and show us the results. There are no conversions from character to data/time occurring so there should be no conversion errors:
SELECT
LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING(Col1, 71, 6)) AS 'LSN',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM
#OriginalFileList
ORDER BY
LSN
September 3, 2015 at 12:26 pm
Likely the string isn't directly castable to datetime or date (like the others I'm guessing because we can't see the data).
Think it'll be based on your OS localisation.
The convert function allows you to tell it what format the date is in to help it out, like this: convert(datatype, data, format)
If your OS localisation dates are UK format (DD/MM/YYYY), try 103:
SELECT
CONVERT(DATETIME, LTRIM(SUBSTRING (Col1, 1, 20)), 103) AS 'DateTimeStamp',
if US format (MM/DD/YYYY), try 101:
SELECT
CONVERT(DATETIME, LTRIM(SUBSTRING (Col1, 1, 20)), 101) AS 'DateTimeStamp',
If neither of these formats, there's a full list at https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(CAST_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply