Debugging Scalar database functions in vS .NET (Parameter issue)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Shannon Broskie (anonymous_at_discussions.microsoft.com)
Date: 07/22/04 

Date: Wed, 21 Jul 2004 18:23:14 -0700

Hello,

I'm running VS .NET 2003 Ent Arch., tapping into a MS SQL
2000 database.

I am trying to debug a function that I wrote on the SQL
Server. The
function has three parameters including two smalldatetime
parameters.

CREATE FUNCTION dbo.DaysBetweenByAccrual (@Day1
smalldatetime, @Day2
smalldatetime,
 @AccrualType varchar(8))
RETURNS int
...

My issue is when trying to run or step through the
function using VS, I
cannot enter (or do not know the proper format) the date
parameters in the
parameter input dialog. I've tried entering the
following formats and I
also show the resulting value in the locals window:

Date Format Result
mm/dd/yyyy 1/1/1900 12:00:00 AM
mm/dd/yyyy 12:00:00 AM Does not run
'mm/dd/yyyy' Error: @Day1:
this input parameter
cannot be converted
#mm/dd/yyyy# Error: @Day1:
this input parameter
cannot be converted

The database output is the following: If I placed
01/01/2004 and 02/01/2004
for the dates.

Running dbo."DaysBetweenByAccrual" ( @Day1 = 01/01/2004,
@Day2 = 02/01/2004,
@AccrualType = 30/360 ).

I can get around this issue by editing the value of the
dates in the locals
window but there's got to be a way to pass these
parameters through the
dialog so that it works and not always pass them in as
1/1/1900.

Any suggestions?

Thanks in advance.

Relevant Pages