Renaming File Based on Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Renaming File Based on Query

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-17 : 09:09:47

Hello,

I was wondering if there is a way to rename files based off a query. For example:

SELECT * FROM TABLE

<1 result(s)>

This will output "CAT" (there will always be one row with 1 result)

Is there a way I can rename a file \\SERVER\C:\TEST.txt to \\SERVER\C:\CAT_123011212.txt?

I want it renamed as the results and concatinate the YYMMDDTTTT too.

Is that even possible? or can someone please point me in the right direction?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 09:18:01

You can use xp_cmdshell to execute a command against the OS like shown below:

DECLARE @cmd NVARCHAR(4000);
SELECT @cmd = 'rename \\SERVER\C:\TEST.txt ' + col1 FROM tbl;
EXEC xp_cmdshell @cmd;

If xp_cmdshell is not enabled, you would need to enable it: http://msdn.microsoft.com/en-us/library/ms190693.aspx

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-17 : 09:46:57

Hmm, I found a stored procedure online and tried to alter it with no luck:

Create procedure sp_ReplaceFileOrDirNames 
  (@pathToObject varchar(200),
   @oldName 	varchar(50),
   @newName 	varchar(50))
as
begin
	declare @winCmd  varchar(400)
  	declare @isFileThere     bit
	declare @isDirectory	 bit
	declare @parentDirExists bit
	declare @fullNamewithPath varchar(250)

	set nocount on 

	set @fullNamewithPath = @pathToObject+'\'+@oldName 	

        Create table #temp (isFileThere 	bit,
			    isDirectory 	bit,
			    parentDirExists	bit)

	Insert #temp exec master..xp_fileExist @fullNamewithPath

	select @isFileThere = isFileThere,
	       @isDirectory = isDirectory 
        FROM #temp

	if (@isFileThere = 1)
        begin 
	   set @winCmd = 'rename ' + 
			 @pathToObject+'\'+@oldName + ' ' + @newName
        end 
	else 
        begin 
		if (@isDirectory = 1)
		begin 
		   set @winCmd = 'move /Y ' + @pathToObject+'\'+
				@oldName + ' '+ @pathToObject+'\'+@newName
		end
	end
	print @winCmd
	exec master..xp_cmdShell @winCmd

	drop table #temp
	set nocount off
   
end
go

I want to SET @newname as "SELECT * FROM TABLE"

but was unsuccessful, I couldn't execute xp_cmdshell yet.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 09:55:09

quote:
I want to SET @newname as "SELECT * FROM TABLE"

You should do it differently. See the code below and you will see why I say that. Are you trying to rename just one file, or are you trying to rename a bunch of files? Everything I am saying below assumes you are trying to rename a single file.


-- create a test table with one row.
CREATE TABLE #tmp (NAME VARCHAR(32));
INSERT INTO #tmp VALUES ('test.txt');

-- try "select *"; doesn't work, it gives a syntax error.
DECLARE @x VARCHAR(32);
SELECT @x = * FROM #tmp;

-- instead specify the column name and that works.
DECLARE @x VARCHAR(32);
SELECT @x = NAME FROM #tmp;

-- but even that is not a good idea. If I have more than one
-- row in the table, what you will get in the variable is
-- undefined.
INSERT INTO #tmp VALUES ('test2.txt')

DECLARE @x VARCHAR(32);
SELECT @x = NAME FROM #tmp;
SELECT @x;

drop table #tmp;

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-17 : 10:00:43

Just single file with the select statement + YYMMDDTTTT

The select clause will always return one value.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 10:05:00

Doesn't the query I posted at 09/17/2012 : 09:18:01 work? Try with a simple example and see if you can make that work. The stored procedure you posted is doing the same thing, except, it is adding a few bells and whistles such as checking if it is a directory name etc.

I am not sure about the filename path syntax of "\\SERVER\C:\filename.txt". I have always used it like "\\SERVER\C$\filename.txt".

So I would do the following in this order.
Step1: See if you can rename the file from a command window.
Step2: In T-SQL, execute exactly the same command using xp_cmdshell.
Step3: Add additional features such as detecting the date etc. to step 2.

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-17 : 10:06:11

Let me give this a shot, please stay with me sunitabeck while I struggle to get this to work.

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-17 : 10:35:13

Looks like xp_cmdshell isn't going to be an option. As my DBA won't allow me to run this command

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 11:17:49

I am not surprised - a lot of DBA's don't like xp_cmdshell, for some very valid reasons related to keeping the data and the database secure.

Alternative would be to just run the commands from a command window, a scheduled job, a powershell script or something similar where you are hosting the script outside of SQL Server and querying SQL server as required to get the information (such as what the new name should be).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 11:20:45

i've done similar requirements using SSIS File System Task. Is SSIS an option you can consider?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-17 : 11:47:51

Hey Visakh sorry for the late reply. Yeah, I am attempting to make a query result as a variable and then a file system task. Need some help though maybe you can point me in the right direction.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 13:03:26

have you used SSIS before?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-17 : 15:20:53

Ok again sorry for late reponse, yup I am ok with SSIS.

I created a variable with a SQL TASK and got it to work. New question however:

How I write an insert statement with that variable.

INSERT INTO TABLE 
VALUES(@[VARIABLE])

something like tthis?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 15:43:57

make query like

INSERT INTO TABLE
VALUES(?)

and in parameters mapping tab

add a new parameter and map it to @Variable.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-17 : 16:01:36

Visakh, I got it!

I used the expression tab and just concatented everything like:

"USE DB;

INSERT INTO" + @[USER::TEST]

Thanks vish for the help though!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 16:09:16

welcome

you can even do it way i told

without making it dynamic by means of expression

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources