You can get the most recent backup file for each database like this:

SELECT

r.DBName

,r.type

,r.BackupStart

,s.backup_finish_date

,s.backup_size

,m.physical_device_name

FROM ( --gets most recent of each type of backup for each DB

SELECT

d.name AS DBName

,b.type

,max(b.backup_start_date) AS BackupStart

FROM master.dbo.sysdatabases...

Are you backing up with a maintenance plan? The file name contains a time and date stamp, so it will be different every time.

John

Can you temporarily comment out the INSERT part of the script to see what you are trying to insert? You could be attempting to insert more than one row...

Please will you post your INSERT statement and the error message.

Thanks

John

Of course I do. A good debate is a good debate, and if we can...

All that casting from int to to varchar looks messy to me - that's all...

Are you saying you just want to add "of x" to the Rank column, where x is the row count for the city in question? Do you want to...

Backups can be set to expire - this is when the backup set can be overwritten with a new one. The maintenance cleanup task is different - it deletes...

When you say you've had no luck, do you mean you got an error message, or did you get the wrong results? What did you try? Here's a...

Try this: http://www.sommarskog.se/dynamic_sql.html

John

http://msdn.microsoft.com/en-us/library/ms188001.aspx

If you want more detailed help, post the code and post the error messages.

John

Sandesh

Sounds like you're asking us to do all the work for you without telling us what you've already tried.

You have two options. You could import all data into a...

Build your query string dynamically, then execute it with sp_executesql. If you use an output parameter, you can get the result back to the scope of the calling batch.

John

I'd guess it's because it's evaluating all 65000 rows in the WHERE clause - that's about 10000 years with a freq of 2, which is beyond the range of datetime....

Rolf

Yes, looks OK. My Numbers table starts with 1, so you could try adding WHERE Number > 0, but to be honest I can't see how that would make...