January 12, 2011 at 12:35 pm
T SQL GURUS, I NEED YOUR HELP !!!
DECLARE @fileCode CHAR(4)
DECLARE @T table (iT int identity, fileCode char(4))
DECLARE @iT int
SET NOCOUNT ON
DECLARE @DelDir varchar(200),
@Command varchar(200)
-- SPECIFY LOCATION
SET @DelDir = '\\sysName\folderName\'
SET @Command = 'dir ' + @DelDir + '*.doc'
DECLARE @bk table (f_output varchar(100))
insert into @bk
EXEC master.dbo.xp_cmdshell @Command
insert into @t (filecode)
SELECT replace(substring(f_output, charindex('ABCD', f_output) + 4, 50), '.doc', '')
FROM @bk where isnumeric(replace(substring(f_output, charindex('ABCD', f_output) + 4, 50), '.doc', '') ) = 1
order by 1
SELECT * FROM @T
It supposed to go to the specified directory read all .doc file names which are in the following format:
ABCD and any combination of four digits, for example – ABCD1234
And then insert all the last four digits in the temp table @T
However file naming format has changed and new one is ABCD1234ZX…..
I cannot figure out with all these replace\substring\charindex statements how to make sure that those last two characters
Are being read and inserted in the @T table
So if the file name not in the original format ABCD1234……
@T table is empty
It does not read new file format ABCD1234ZX
Thanks,
January 12, 2011 at 12:44 pm
Try this:
SELECT substring(f_output, charindex('ABCD', f_output) + 4, 4)
FROM @bk
WHERE f_output LIKE '%ABCD[0-9][0-9][0-9][0-9].doc';
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 12:54 pm
looks like your stmnt it's still missing ZX at the end....
will not work
January 12, 2011 at 1:58 pm
SELECT substring(f_output, charindex('ABCD', f_output) + 4, 4)
FROM @bk
WHERE f_output LIKE '%ABCD[0-9][0-9][0-9][0-9].doc'
OR f_output LIKE '%ABCD[0-9][0-9][0-9][0-9]ZX.doc';
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 2:24 pm
my fault...
above script is a beginning of the loop...
here is what following the script above:
set @iT=1
WHILE @iT<=(SELECT MAX(iT) FROM @T)
BEGIN
SELECT @fileCode=fileCode FROM @T where iT=@iT
SELECT @fileCode
January 12, 2011 at 4:28 pm
Change your table definition:
From
DECLARE @T table (iT int identity, fileCode char(4))
To
DECLARE @T table (iT int identity, fileCode char(6))
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 13, 2011 at 6:27 am
i did....
still, 'select from' is coming up with no records
January 13, 2011 at 7:04 am
Can you post the whole script/procedure? Might help if we can see what this is doing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2011 at 7:42 am
Sorry.... 🙂
Here's the script. Actually, it's a dynamic database restore script.
It's reading all the backup files names in the specified directory.
Then, it inserts in the @T table all digits that comes after ABCD0401.bak (backup file name) for example.
What i need is for this query to see the rest of the file name...everything that comes after ABCD
and insert it in the @T table, row by row.
DECLARE @fileCode CHAR(4)
DECLARE @T table (iT int identity, fileCode char(4))
DECLARE @iT int
DECLARE @RESTORECODE VARCHAR(max)
SET NOCOUNT ON
DECLARE @DelDir varchar(200),
@restore_filename varchar (100),
@Command varchar(200)
-- SPECIFY LOCATION
SET @DelDir = '\\sysname\filename\'
SET @Command = 'dir ' + @DelDir + '*.bak'
DECLARE @bk table (f_output varchar(100))
insert into @bk
EXEC master.dbo.xp_cmdshell @Command
insert into @t (filecode)
SELECT replace(substring(f_output, charindex('ABCD', f_output) + 4, 50), '.bak', '')
FROM @bk where isnumeric(replace(substring(f_output, charindex('ABCD', f_output) + 4, 50), '.bak', '') ) = 1
order by 1
--SELECT * FROM @T
------------------------------------
set @iT=1
WHILE @iT<=(SELECT MAX(iT) FROM @T)
BEGIN
SELECT @fileCode=fileCode FROM @T where iT=@iT
-- SPECIFY LOCATION
SELECT @RESTORECODE='
RESTORE DATABASE ABCD'+@fileCode+' FROM DISK=''Q:\restDir\ABCD'+@fileCode+'.BAK''
WITH
REPLACE,
RECOVERY,
MOVE ''ABCD'+@fileCode+''' TO ''x:\DATA\ABCD'+@FileCode+'.mdf'',
MOVE ''ABCD'+@fileCode+'_LOG'' TO ''z:\LOG\ABCD'+@fileCode+'_LOG.LDF'''
--SELECT @RESTORECODE
EXECUTE (@RESTORECODE)
January 13, 2011 at 7:49 am
As I said before, the table variable which holds fileCode need altering - currently it can only handle 4 characters.
Also, your WHERE clause prevents you selecting fileCodes like '0192ZZ' because isnumeric('0192ZZ') = 0.
edit:
Try replacing the WHERE with something like
WHERE f_output like '%ABCD[0-9][0-9][0-9][0-9][A-Z.]%'
Which means f_output is a string of any characters followed by the literal ABCD followed by any 4 digits followed by either a letter between A and Z or a "." and then followed by anything else, which may not be quite the pattern you want, but will work better than the current WHERE clause
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 13, 2011 at 8:10 am
thank you for your time guys
i am not really a programmer....
can you show me how to replace original 'where' with yours ...syntax wise
does this looks proper to you?
don't know how to reuse that ' ) = 1' with your script
January 13, 2011 at 9:27 am
I suggest you ask a DBA or a programmer to look at this for you - anything we tell you here should not be used unless you understand it.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 13, 2011 at 9:29 am
I wouldn't build this in this pattern.
Automated restores almost always work better if they query the backupset data from msdb. If you do that, you don't need all this complexity.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2011 at 9:38 am
unfortunately i can't use msdb database...
those files are collections of prod backup files from different servers that needed to be restored
on the QA server
January 14, 2011 at 8:39 am
I am not too clear on exactly what you need here, but I will say that the way to handle complex string manipulations is to break them down into single steps and add in pieces of code to the process one at a time, testing the output with your expectations with each step.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply