Getting print from running of stored procedure 1

[graabein]

Hi,

I have a couple of stored procedures running on SQL Server 2000 that outputs some debug data that's really important for debugging my VB6 application.

Is there any way I can catch these outputs when running a ADODB Command in runtime? One of the procedures returns a Recordset in addition to two output parameters, but I want the print output I can see when I use SQL Query Analyzer...


graabein

 

[ArtieChoke]

Doubt it. I believe they only go to the query analyzer output display.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[graabein]

OK I found a discussion on Google groups here:

http://groups.google.no/group/micro...36e0b?lnk=gst&q=print+output#d8ce3a8bb5f36e0b

Another thing though, I return a timestamp from the server that goes back as input parameter. I'm afraid I loose the part after seconds, is this possible?

I need to know the value after hh:nn:ss<here>




graabein

 

[graabein]

The milliseconds are apparently there, according to this page:

http://www.sql-server-performance.com/article_print.aspx?id=373&type=art

What I can't get around now is passing full value of datetime as input parameter to my ADODB Command object?

Here's my code:

cmd.Parameter.Append cmd.CreateParameter("@LastCheck", adDate, , , FormatDt(dtLastCheck)

With FormatDt function returning 2008-04-21 14:52:48.443


graabein

 

[graabein]

I couldn't get around this problem so I just pass the datetime value as varchar(30) ("2008-04-21 14:52:48.443") and cast it to internal datetime parameter in the stored procedure. Hmmph!


graabein

 

[gmmastros]

Well... if you're going to do that, then I would suggest that you modify the format. There are only 2 un-ambiguous date formats that you can use with SQL Server.

YYYYMMDD hh:mm:ss.mmm
YYYY-MM-DDThh:mm:ss.mmm

Your example date should appear as:

20080421 14:52:48.443 or
2008-04-21T14:52:48.443

Every login for SQL Server has a language associated with it. Each language has a dateformat associated with it. The DateFormat determines how dates are interpreted by SQL Server. The formats mentioned above are unambiguous, meaning that they will ALWAYS be interpreted the same way, no matter what language/dateformat is specified.

Now, you may be thinking, that will never affect me. However, you should consider doing this anyway for 2 reasons.

1. You just never know what your app will be used for in the future.
2. It's easy to change the date format for a string when passing it to SQL Server.

The following languages use a Date-Month-Year format.

[tt][blue]German
French
Danish
Spanish
Italian
Dutch
Norwegian
Portuguese
Finnish
Czech
Polish
Romanian
Slovak
Slovenian
Greek
Bulgarian
Russian
Turkish
British English
Estonian
Brazilian
Arabic
Thai[/blue][/tt]

For a complete list, run this (in Query Analyzer).

[tt][blue]sp_HelpLanguage[/blue][/tt]

To see how the date format affects SQL Server's interpretation of the date....

declare @Temp varchar(30)
Set @Temp = '[!]2008-04-21 14:52:48.443[/!]'

Set DateFormat DMY

Select Cast(@Temp As DateTime)

When you run that code (in Query Analyzer), you will get this error message:

[red]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.[/red]

This occurs because SQL Server is interpreting the date format to mean, 4th day of 21st month in the year 2008. Since there is no 21st month, you get an error.

Now, run these.

declare @Temp varchar(30)
Set @Temp = '[!]20080421 14:52:48.443[/!]'

Set DateFormat DMY

Select Cast(@Temp As DateTime)

declare @Temp varchar(30)
Set @Temp = '[!]2008-04-21T14:52:48.443[/!]'

Set DateFormat DMY

Select Cast(@Temp As DateTime)

The last two execute as you would expect it to.

Now, don't mistakenly think that your original format is ok simply because you do NOT use Set DateFormat within stored procedures. This is simply not right. Like I said earlier, if you have a login that has a language set to Spanish (for example), even if you don't have Set Dateformat in your stored procedure, you will still have a problem.

declare @Temp varchar(30)
Set @Temp = '2008-04-21 14:52:48.443'

Set Language 'Spanish'

Select Cast(@Temp As DateTime)

If your login has it's language set to Spanish, you do not need to use Set Language. It will already be set for you.

Make sense? Better safe than sorry, right?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom