Concatenating filename and variable/string

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

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenating filename and variable/string

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-03-31 : 08:48:30

I'm trying to write a stored procedure in SQL that will make a copy of a file and then append the current date to the end of the copy's filename. I'm storing the date in a variable called @currentdate which is defined as:

declare
@currentdate varchar(10)
set @currentdate='' + 'select datepart(month,getdate())' + '-' + 'select datepart(day,getdate())' + '-' + 'select datepart(year,getdate())'

Here is the SQL code I'm using:

exec xp_cmdshell 'copy "C:\Development\Participant Limits Report\Participant Limits Report template.xls" "C:\Development\Participant Limits Report\Participant Limits Report ' + @currentdate + '.xls"'
GO

The resulting file should have a filename something like "Participant Limits Report 3-31-2008". I get an "Incorrect syntax near '+'." error.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-31 : 08:50:26

declare @cmd varchar(1000)
select @cmd = 'copy "C:\Development\Participant Limits Report\Participant Limits Report template.xls" "C:\Development\Participant Limits Report\Participant Limits Report ' + @currentdate + '.xls"'
exec xp_cmdshell @cmd

I would also use yyyymmdd if you want a date - it's easier to deal with.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 08:53:43

[code]declare
@currentdate varchar(10), @cmd varchar(4000)
set @currentdate='' + 'select datepart(month,getdate())' + '-' + 'select datepart(day,getdate())' + '-' + 'select datepart(year,getdate())'

set @cmd = 'copy "C:\Development\Participant Limits Report\Participant Limits Report template.xls" "C:\Development\Participant Limits Report\Participant Limits Report ' + @currentdate + '.xls"'

exec master..xp_cmdshell @cmd[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-03-31 : 08:54:43

Well I could do that, but that's not the problem. The issue is in the concatenating of the filename and date.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-31 : 09:13:38

No - the issue is that you are trying to do it in the xp_cmdshell command rather than create a string to execute.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-03-31 : 09:14:40

Okay so now I have the following code:

declare
@currentdate varchar(10), @cmd varchar(4000)
set @currentdate=select convert(varchar(8),getdate(),1) AS [YYYYMMDD]
set @cmd = 'copy "C:\Development\Participant Limits Report\Participant Limits Report template.xls" "C:\Development\Participant Limits Report\Participant Limits Report ' + @currentdate + '.xls"'

exec master..xp_cmdshell @cmd

I get the following error now:

Incorrect syntax near the keyword 'select'.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-31 : 09:20:50

select @currentdate = convert(varchar(8),getdate(),112)

You are using sql server aren't you?
Try looking at the statement sytax in bol. Everything you have had trouble with is in there.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-03-31 : 09:22:53

Thanks a lot nr. I am using SQL Server for the first time so I'm very new to it. What is bol?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-31 : 09:30:44

books on-line.
Try help from management studio or looking for it under sql server\documentation in programs

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-03-31 : 09:52:16

Thanks.

I have the preliminary file copy done now, although I'm not sure why I had to create a variable and then run the command referencing the variable rather than just putting it all in one line.

Now I'm experiencing a similar issue when I try to copy data from a table into sheets in the Excel file I created. I'm running 18 different commands so I'd prefer to be able to add the date to the string in the command rather than create 18 different variables to accomplish what I'm trying to do.

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Development\Participant Limits Report\Participant Limits Report ' + @currentdate + '.xls;',
'SELECT * FROM [Arbor$]') select * from Participant_Limits_temp where Weeks_Left<=0 and (CDE_PROJ='NC0005' or CDE_PROJ='WS0500')

Help?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-31 : 09:58:12

it doesn't work that way. you'll need to construct the string as a variable the same way you did before

Em

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-03-31 : 09:59:39

Okay. If that's just how it has to be done, that's what I'll have to do.

Thanks a lot guys. You're very helpful.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-31 : 10:01:28

>> although I'm not sure why I had to create a variable and then run the command referencing the variable rather than just putting it all in one line.

Because xp_cmdshell doesn't allow operations in the string it executes. Have a look at the syntax in bol.
Same as openrowset doesn't.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-03-31 : 10:07:17

So if I can't append strings into these syntaxes, can I do something like this, loading variables:

declare
@currentdate varchar(10),
@location varchar(4000),
@filename varchar(4000)
set @currentdate = convert(varchar(8),getdate(),112)
set @location = 'C:\Development\Participant Limits Report'
set @filename = 'location\Participant Limits Report ' + @currentdate + '.xls;'

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=@filename',
'SELECT * FROM [Arbor$]') select * from Participant_Limits_temp

It doesn't seem to work

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 10:09:45

No you can't. You will have to use dynamic sql to parameterize OPENROWSET().

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-03-31 : 10:12:16

So is my only option to make a temp version of the Excel file, insert all the data into that file using OPENROWSET, and then rename the file afterwards?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 10:17:13

If you are using OPENROWSET, then Yes. You could do this thing much more easily and dynamic way using SSIS, though.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-03-31 : 10:21:18

How?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-31 : 10:23:32

try
declare @sql varchar(4000)
select @sql = 'insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
'Excel 8.0;Database=''' + @filename + ''',
''SELECT * FROM [Arbor$]'') select * from Participant_Limits_temp'
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-03-31 : 10:35:02

quote:
Originally posted by harsh_athalye

If you are using OPENROWSET, then Yes. You could do this thing much more easily and dynamic way using SSIS, though.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

How do I do this?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 10:41:04

You need to create SSIS package (assuming you are using SQL Server 2005) and then you can paramaterize the file name and export data to it.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources