Date/Time Conversions Code Correction
Thanks to Susan Perschke for her great article, "Automate Date/Time Conversions"
(March 2007, InstantDoc ID 94954). I just finished a project where I had to
do exactly what Susan described in Listing 1 and created a uniqueness database
file (UDF) that does the same thing. From her article, I thought I might create
a couple more handy date/time-related UDFs. I ran into a snag with her sp_format_dt.
The "set @ result = convert(char(20), convert(datetime, @dt, @code_in), @code_out)"
statement failed for me. After consulting the SQL Server documentation, I believe
it specifies that you can use only the Style parameter when converting to a
STRING type (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).
The part "convert(datetime, @dt, @code_in)" doesn't seem to work. Instead, what
I get is an error: Conversion failed when converting datetime from character
string. Am I right about this, or am I missing something about this statement?
—Jack
Rosa
Thank you for the compliment, Jack, and for the feedback. As sometimes happens,
the original code was modified during the editing process, and I have to confess
that I dropped the ball on retesting the code before the article went to print.
We've corrected the code back to the original for the stored procedure, and
you can access it in the online article's downloadable .zip file. I'm happy
to hear that you've achieved good results with this method.
—Susan
Perschke
A SharePoint Survival Guide for SQL Server Admins
Lately, I've been looking for information on the topic "SharePoint for SQL Server
admins." I run the SQL Server systems in my shop. I'm not a SharePoint developer
or administrator. But under the hood, most of the SharePoint data is on my servers—and
SharePoint is no ordinary app. Once I realized that it needed to be able to
create and destroy databases, I knew it needed its own instance—usually
database owners (DBOs) don't get that permission. But these are databases of
databases. Recently, I tried to move them to a new SQL Server instance by using
detach/copy/attach. We still don't know why that ended in disaster. When we
gave up and reattached the original server to the untouched original files,
that didn't work either. Neither our SharePoint admin nor I know the right answer
to "best way to back it all up." I want to use my usual full/differential/logs
cycle. He thinks he should be using stsadm and copying database dumps across
the network. I know—it depends on what you'll need to restore. Still,
we'd like a better way to make those decisions.
So, a SQL Server admin supporting SharePoint needs to know a fair amount about
SharePoint, without becoming a general expert in SharePoint administration or
development. I could sort through the general SharePoint admin courses and books,
but I've got 137 other databases on three different platforms to look after,
too, so I don't want to spend more time on SharePoint than I need to. And I
really would like to hear from someone who doesn't need to be told what a stored
procedure is but might have little notion of what a Web part is. Does one of
your gurus have the background and desire to write such an article?
—Roger
Reid
