July 28, 2011 at 8:01 am
Just create a variable to hold your primary key.
declare @MyID int
Then add the column to your select statement.
select [file name], MyID FROM [Actions Open] WHERE [MAIL] LIKE '' AND [File name] IS NOT NULL
open c1
fetch next from c1 into @Copy, @MyID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 28, 2011 at 9:03 am
Didnt even think of that, managed to get it working along the way like this (query at bottom). I read up on the cursor options and found out that while its "active" i have full control to write back to a field from the same record thats not within the cursor (this works). When i run my query it copies 12 files which is correct and it also updates the description field 12 times on the designated records. The problem i have now is that my xcopy returns a question which is unavoidable, to bypass it i would need to read out filename field, user the first 20 chars (this is always 20) and write that to a variable so i can use it in another cmdshell.. The question is, how can i fill a variable that i define with the output from the filename field where i only want to store the first 20 chars ?
use [DB name]
DECLARE @copy VARCHAR(MAX)
DECLARE @SQLQUERY VARCHAR(MAX);
SET @SQLQUERY = ''
SET @copy = ''
declare c1 cursor for
select [file name] FROM [Actions Open] WHERE [MAIL] LIKE '' AND [File name] IS NOT NULL AND [Description 2] LIKE ''
open c1
fetch next from c1 into @copy
While @@fetch_status <> -1
begin
SET @SQLQUERY =
'xp_cmdshell ''XCOPY /S "d:\copy test\source"'+@Copy+' "D:\copy test\destination"'+@Copy+''''
EXEC (@SQLQUERY)
UPDATE [Actions Open]
SET [Description 2] = 1
WHERE CURRENT OF c1
fetch next from c1 into @copy
end
close c1
deallocate c1
July 28, 2011 at 10:38 am
Didn't exactly follow your description there but to get the first 20 characters just use Left([filename or variable], 20).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 28, 2011 at 10:40 am
Have you looked into SSIS? It can do this kind of stuff really nicely, all wrapped up in a slick package with bows and ribbons.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 28, 2011 at 1:22 pm
Thanks for the reply, i actually did stumble into it but figured it was something for later as i needed a quicker solution (tho i agree with you ssis seems to be the better way). I'm actually no programmer but this got dumped on my desk as we did not have anybody that can do it and i atleast have a interest for the language.
Gonna toy around with the left option to see if it covers my problem, my mumbo jumbo explanation short is that i want to fetch the part out of the filename upto the point where the actual file starts. Need to do this to bypass a limitation thats within xcopy where one can not copy a directory structure if you copy from path\file to path\file (only works with wildcards) for a single file.
tnx again !
July 28, 2011 at 1:53 pm
If you want a little dynamic solution instead of hardcoding 20 characters take a look at this. I wasn't sure if you were trying to parse the file name or the full path so here is an example of doing both. 😛
declare @FileName varchar(255)
set @FileName = 'D:\copy test\destination\MyFileIs20_Chars.txt'
select SUBSTRING(@FileName, DATALENGTH(@FileName) - CHARINDEX('\', REVERSE(@FileName)) + 2, CHARINDEX('\', REVERSE(@FileName)) + 1) as FileName,
SUBSTRING(@FileName, 0, DATALENGTH(@FileName) - CHARINDEX('\', REVERSE(@FileName)) + 2) as FullPath
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 28, 2011 at 1:53 pm
Hmm, getting the LEFT value returned for 20 chars made sense and gave the right result. I'm now trying to implement it within the loop i made but it seems tricky (if not, impossible?) Part of the adjusted query looks like :
begin
SET @SQLQUERY1 =
'xp_cmdshell ''mkdir "d:\copy test\destination"'+SELECT LEFT (@Copy, 26)+''
EXEC (@SQLQUERY1)
SET @SQLQUERY2 =
'xp_cmdshell ''XCOPY /S "d:\copy test\source"'+@Copy+' "D:\copy test\destination"'+@Copy+'"\"'''
EXEC (@SQLQUERY2)
Like i said i'm quite new to SQL but i'm missing the logic on how i can use the @copy variable to get the first 20 chars within the mkdir line. Specifically the quotes and the +'s confuse the hell out of me. Will check later here if there's some pointers 🙂 gonna hit the books in the meanwhile, gotta nail this.. feels i'm quite close.
July 28, 2011 at 1:59 pm
See if this helps.
declare @MakeDirVal varchar(500)
begin
set @MakeDirVal = LEFT(@Copy, 26)
SET @SQLQUERY1 =
'xp_cmdshell ''mkdir "d:\copy test\destination"' + @MakeDirVal + ''''
EXEC (@SQLQUERY1)
SET @SQLQUERY2 =
'xp_cmdshell ''XCOPY /S "d:\copy test\source"'+@Copy+' "D:\copy test\destination"'+@Copy+'"\"'''
EXEC (@SQLQUERY2)
That should be really really close. Think I have all the ' in the right amounts and places. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2011 at 12:12 am
Thanks Sean, truly appreciated and indeed you had it all right 🙂
I did run into two minor issues, posting them back so this post might prove useful to anybody wanting to achieve the same..
XCOPY still asked if the destination was a file or directory, even tho the whole path got created correctly - Found from MSDN that this is a actual limitation in XCOPY method, basically MS says one should use copy if the destination and source are pinpointed to a specific file (single). Reverted to COPY and that did solve the problem
Second was that it had some issues with the spacing that was provided, dont really get why it tried to space in the directory path but i adjusted it so that the static path had no spaces towards the @makedirval add. At the end the query looks as below and works perfectly. Again, many many thanks for helping on this one and compliments on the accurate replies !
use [DB NAME]
DECLARE @copy VARCHAR(MAX)
DECLARE @SQLQUERY1 VARCHAR(MAX)
DECLARE @SQLQUERY2 VARCHAR(MAX)
DECLARE @MakeDirVal VARCHAR(MAX)
SET @SQLQUERY1 = ''
SET @SQLQUERY2 = ''
SET @copy = ''
declare c1 cursor for
select [file name] FROM [Actions Open] WHERE [MAIL] LIKE '' AND [File name] IS NOT NULL AND [Description 2] LIKE ''
open c1
fetch next from c1 into @copy
While @@fetch_status <> -1
begin
SET @MakeDirVal = LEFT (@Copy, 26)
SET @SQLQUERY1 =
'xp_cmdshell ''mkdir "d:\copy test\destination"'+@MakeDirVal+''''
EXEC (@SQLQUERY1)
SET @SQLQUERY2 =
'xp_cmdshell ''COPY /Y "d:\copy test\source"'+@Copy+' "D:\copy test\destination"'+@Copy+''''
EXEC (@SQLQUERY2)
UPDATE [Actions Open]
SET [Description 2] = 1
WHERE CURRENT OF c1
fetch next from c1 into @copy
end
close c1
deallocate c1
July 29, 2011 at 12:15 am
Just noticed the reply on doing it dynamically. With my limited knowledge that actually does look like a much cleaner way to do it. Gonna toy around with it to see if i can learn a bit from that method 🙂
Only been busy with sql like 3 day so my head is spinning already from all the information i'm digging up 🙂
Viewing 10 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply