Stored Procedure and Date - 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

Stored Procedure and Date

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2008-07-21 : 12:05:03

Hi,

I am using a FTP stored procedure found on this web site, to get files from a FTP server. I wish I could make it get the right file every month. To do this, I need to specify the month number in the file name. My question is How can I do this

With this I can get the last month number:
SELECT DATEPART(month, GETDATE()) - 1

But How I can I add it no the file name. I tried this but it doesn't work.
@remotefile='/mesure/pqln' & SELECT DATEPART(month, GETDATE()) - 1 ' &.dat'

Exec @hr=sp_OAMethod @oPkg,Get_File,@getfile OUT,

@sessionid=@Connected,
@remotefile='/mesure/pqln01.dat',
@newfile='\\devicename\ftp\pqln01.dat',
@failifexists=0,
@flagsandattr=0,
@flags=1,
@context=0

Thanks for any help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 12:21:19

"With this I can get the last month number:
SELECT DATEPART(month, GETDATE()) - 1"
It will break if the current date is January
use select datepart(month, dateadd(month, datediff(month, 0, getdate()) - 1, 0))

"But How I can I add it no the file name. I tried this but it doesn't work.
@remotefile='/mesure/pqln' & SELECT DATEPART(month, GETDATE()) - 1 ' &.dat'"

select @remotefile = '/mesure/pqln' +
convert(varchar(2), datepart(month, dateadd(month, datediff(month, 0, getdate()) - 1, 0))) +
'&.dat'"

KH
[spoiler]Time is always against us[/spoiler]

infodemers
Posting Yak Master

183 Posts

Posted - 2008-07-21 : 12:37:29

Hi khtan,
Firts of all, I thank you for your reply,
This the code with your suggestion.

Exec @hr=sp_OAMethod @oPkg,Get_File,@getfile OUT,

@sessionid=@Connected,
select @remotefile = '/mesure/pqln' + convert(varchar(2), datepart(month, dateadd(month, datediff(month, 0, getdate()) - 1, 0))) + '.dat',
@newfile='\\bhhci7\ftp\pqln01.dat',
@failifexists=0,
@flagsandattr=0,
@flags=1,
@context=0

I received this error message from the SQL server.
Incorrect syntax near the keyword 'select'.

Any Idea ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 12:39:33

You can't do the select while executing the stored procedure. Run the select first, then just pass the variable to the stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

infodemers
Posting Yak Master

183 Posts

Posted - 2008-07-21 : 12:56:14

Give the Man a CIGAR....

Here is my working code I got with your help!

--Get the file and direct it to our local drive
--vb Equivalent Get_File(sessionid, remotefile, newfile, failifexists, flagsandattr, flags, context) As Boolean

--File download

select @remotefile = '/mesure/pqln' + convert(varchar(2), datepart(month, dateadd(month, datediff(month, 0, getdate()) - 1, 0))) + '.dat'
select @newfile = '\\devicename\ftp\pqln' + convert(varchar(2), datepart(month, dateadd(month, datediff(month, 0, getdate()) - 1, 0))) + '.dat'

Exec @hr=sp_OAMethod @oPkg,Get_File,@getfile OUT,

@sessionid=@Connected,
@remotefile=@remotefile,
@newfile=@newfile,
@failifexists=0,
@flagsandattr=0,
@flags=1,
@context=0

Thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources