Thank this author by sharing:
By Andy Warren,
Back on September 4th I posted an article
about using DMO to attach a LOT of databases very quickly and easily. If you
haven't read it yet, take a couple minutes to do so before continuing. I also
challenged our readers to find a way to implement the solution using T-SQL.
Whether you use DMO or T-SQL to solve a problem usually depends on your
background. I use VB quite often, so being able to set a reference to the DMO
library and keep working in the same environment is a productive way for me to
work. So my thought was - how would someone really comfortable with T-SQL solve
I'm pleased to announce Wynn Muse as the winner of the contest. I'm including
the entire script as he forwarded to me, you can download the file here,
and it will also be added to our script
library as well. Wynn will be receiving a copy of JMS Messaging, published
by Wrox (yes, an off topic book, but is any technology book really off topic?).
It's an excellent piece of code - even if you don't need to accomplish this
particular task, I think you'll benefit from working through the solution. Never
know what you'll find when you read code -- I make the point because I was
pleasantly surprised to find an answer to a question I've been asked several
times -- how do you get a list of file names from a folder into a table?
Solutions I've seen or thought of include using the file system object (hard
from T-SQL, easy from DTS) to using xp_cmdshell to execute the dir command and
redirect it's output to a file, then import the file (using DTS). Stop and think
for a minute - how would you do it?
Long pause while you think.
Now take a look at this excerpt from the script:
The real workhorse is still xp_cmdshell - but the trick is to remember it
returns a result set just like any other stored procedure - and in this case
it's the list of files we need! Wynn uses the xp_sprintf procedure to do the
work of building the final string to get the exact directory string he needs - I
think a cleaner solution than using Replace.
Thanks and congratulations again to Wynn - a great job! As always, post your
comments or ideas in the attached discussion forum, I look forward to hearing
i need to attach a timestamp in YYYYMMDD format at the end of the filename
Unexpected Result in sp_send_dbmail file attachment