getProcedureColumns behavior changed from SQLServer 2000 to 2005?

A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2), and one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.

jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.

Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.

Thanks,

-- Damodar PeriwalCan someone from Microsoft verify if this is a bug in SQL Server 2005 or not? Thanks.

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2), and one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.

jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.

Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.

Thanks,

-- Damodar PeriwalCan someone from Microsoft verify if this is a bug in SQL Server 2005 or not? Thanks.sql

GETDATE() with a user defined function

Hi,
I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated.
Regards,
Samcute.Nope sorry...

Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?

Anyone know how to create an external sproc?

USE Northwind
GO

CREATE PROC mySproc99 @.myDate99 datetime OUTPUT AS SELECT @.myDate99 = GetDate()
GO

DECLARE @.myDate99 datetime

EXEC mySproc99 @.myDate99 OUTPUT

SELECT @.myDate99

CREATE FUNCTION udf_myFunction99(@.x datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
RETURN @.myDate99
END
GO

SELECT dbo.udf_myFunction99(0)
GO

DROP FUNCTION udf_myFunction99
DROP PROC mySproc99
GO|||Nope sorry...

Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?

Anyone know how to create an external sproc?

USE Northwind
GO

CREATE PROC mySproc99 @.myDate99 datetime OUTPUT AS SELECT @.myDate99 = GetDate()
GO

DECLARE @.myDate99 datetime

EXEC mySproc99 @.myDate99 OUTPUT

SELECT @.myDate99

CREATE FUNCTION udf_myFunction99(@.x datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
RETURN @.myDate99
END
GO

SELECT dbo.udf_myFunction99(0)
GO

DROP FUNCTION udf_myFunction99
DROP PROC mySproc99
GO

Pass GetDate() as a function argument.

Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!

-PatP|||Pass GetDate() as a function argument.
-PatP

Did I already say that?

Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!

oooo scary...

Did you see Hendersons work for Arrays in SQL Server?

And you're right...I chickened out...was going to build them...but I figured why bother...a tables an array, and with the table variable is was even easier...|||If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!

Yeah...I called that "midlife crisis", and a few thousands of dollars and several handcuff burns later...I tend to agree ;) Although..."that which does not kill us..." ;)|||Thousands of dollars ?!?! Was she worth it?

-PatP|||*LOL* Nope...not even a "she" dammit (though, it's probably best to assume the "she" connection first in any such situations)...what a wasted midlife crisis...just partyin' too much with m'homeboys Jose' C and Jack D|||Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...|||Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...No, no, no! He didn't say it was a normal Tuesday afternoon. This was something special!

I figured with thousands of dollars and handcuff burns, there just HAD to be a "she" in there somewhere!

You do have to be wary when playing with Jack and Jose. Those fellas play kinda rough sometimes. Glad to know that you survived it anyway!

-PatP|||Thanks...and no, no trees or blood involved, but Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it ;)

Perhaps needless to say, I lost quite a few brain cells that would come in handy now trying to figure out how to debug in SQL Server, and how to put non-deterministic functions into user-defined functions!

(hey, how's THAT for coming back on-topic ;) )|||Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it

Did you get a room with the view of the lake?

This is good Yak Corral stuff...

And Pat do you use debugger or not?|||On my own code, I've only used the debugger once or twice in order to show other people how my code worked. I've never needed it for actually debugging code that I've written.

The debugger has come in handy more than once trying to finger out what in blazes some of the code that I've inherited actually does. Some of that stuff can be most charitably described as bizarre.

-PatP

GetDate() in User Defined Functions, Parameters in View

1) Am I right in believing I can't access the GetDate() function from within
a table valued UDF ... ? That seems to be what the syntax checker is
telling me. Any suggested workarounds ?
2) I tried create a view which used a table valued UDF, with " getdate() "
as a parameter, in the view's from clause and the system didn't like that
either ... Any suggestions ? Are there ways of parameterizing a view ?
3) I need the view construct because I need to reference the returned
dataset from Analysis Services as a dimension. I prefer the UDF construct
(as opposed to selecting off a table with a where clause) because I don't
have to build a process to add new date records to the hypothetical table.
Performance isn't really an issue, because the only place the view is invoke
d
is in processing a cube in Analysis Services.
4) Some SQL
...
CREATE FUNCTION dbo.tfun_Date (
@.EndDate smalldatetime) --added when getdate didn't work
RETURNS @.DateTable table (
DateValue smalldatetime )
BEGIN
Declare @.DateIdx smalldatetime
Declare @.StartDate smalldatetime
Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
Set @.DateIdx = @.StartDate
--Set @.EndDate = getdate()
While @.DateIdx <= @.EndDate
Begin
Insert @.DateTable (DateValue) values (@.DateIdx)
Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
End
Return
END
--
CREATE VIEW dbo.v_date
AS
SELECT top 10000 DateValue
, DateYYYY = Datepart(yyyy, DateValue)
, DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
, DateQtr = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN 'Q3' WHEN 8
THEN
'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN 11 THEN
'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
, DateMM = Datepart(mm, DateValue)
, DateMMAbbrev = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN
8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
, DateDD = Datepart(dd,
DateValue), DowNbr = Datepart(dw, DateValue)
, DowAbbr = CASE Datepart(dw, DateValue)
WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7 THEN 'Sun' ELSE
'Err' END
, DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
, WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 7 - Datepart(dw
,
DateValue)) / 7
, WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue), DateValue)
, Workday = Case
When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
Else 'Workday' End
, HolidayName
, PublicHolidayFlag
--THIS IS THE BOGUS LINE
FROM tfun_date( getdate() ) D
--ENDS HERE
Left outer Join ZR_PublicHols on D.DateValue = ZR_PublicHols.HolidayDate
order by DateValueYou could pass in to the UDF a new parameter which when called, you send it
the GETDATE/CURRENTTIMESTAMP function.
Then just use that new parameter as your getdate()
hth
Eric
MarcusW wrote:
> 1) Am I right in believing I can't access the GetDate() function from
> within a table valued UDF ... ? That seems to be what the syntax
> checker is telling me. Any suggested workarounds ?
> 2) I tried create a view which used a table valued UDF, with "
> getdate() " as a parameter, in the view's from clause and the system
> didn't like that either ... Any suggestions ? Are there ways of
> parameterizing a view ?
> 3) I need the view construct because I need to reference the returned
> dataset from Analysis Services as a dimension. I prefer the UDF
> construct (as opposed to selecting off a table with a where clause)
> because I don't have to build a process to add new date records to
> the hypothetical table. Performance isn't really an issue, because
> the only place the view is invoked is in processing a cube in
> Analysis Services.
> 4) Some SQL
> ...
> CREATE FUNCTION dbo.tfun_Date (
> @.EndDate smalldatetime) --added when getdate didn't work
> RETURNS @.DateTable table (
> DateValue smalldatetime )
> BEGIN
> Declare @.DateIdx smalldatetime
> Declare @.StartDate smalldatetime
> Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
> Set @.DateIdx = @.StartDate
> --Set @.EndDate = getdate()
> While @.DateIdx <= @.EndDate
> Begin
> Insert @.DateTable (DateValue) values (@.DateIdx)
> Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
> End
> Return
> END
> --
> CREATE VIEW dbo.v_date
> AS
> SELECT top 10000 DateValue
> , DateYYYY = Datepart(yyyy, DateValue)
> , DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
> , DateQtr = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN
> 'Q1' WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN
> 'Q3' WHEN 8 THEN
> 'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN
> 11 THEN 'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
> , DateMM = Datepart(mm, DateValue)
> , DateMMAbbrev = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN
> 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7
> THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
> , DateDD = Datepart(dd,
> DateValue), DowNbr = Datepart(dw, DateValue)
> , DowAbbr = CASE Datepart(dw, DateValue)
> WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN
> 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7
> THEN 'Sun' ELSE 'Err' END
> , DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
> Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
> , WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime,
> CONVERT(char(4), Datepart(yyyy, DateValue)) + '01' + '01', 112),
> DateValue) + 7 - Datepart(dw, DateValue)) / 7
> , WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue),
> DateValue) , Workday = Case
> When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
> When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
> Else 'Workday' End
> , HolidayName
> , PublicHolidayFlag
> --THIS IS THE BOGUS LINE
> FROM tfun_date( getdate() ) D
> --ENDS HERE
> Left outer Join ZR_PublicHols on D.DateValue =
> ZR_PublicHols.HolidayDate order by DateValue|||Marcus

> 1) Am I right in believing I can't access the GetDate() function from
within
> a table valued UDF ... ? That seems to be what the syntax checker is
> telling me. Any suggested workarounds ?
Correct. However, you can create a view that has the GetDate() in it and
then call that view from your UDF. This workaround may or may not continue
to work in future versions.

> 2) I tried create a view which used a table valued UDF, with " getdate() "
> as a parameter, in the view's from clause and the system didn't like that
> either ... Any suggestions ? Are there ways of parameterizing a view ?
An in-line table-valued UDF is a parameterized view. I think the problem
is still the GetDate() which cannot be a parameter to a UDF either. (Why
not? Because. Technically it is the issue of whether a function always
returns the same value or not.)
Russell Fields

getdate() in user defined function

Hi,

It is possible to use getdate() in userdefined function. If so, how to do the same ?

The following code throws error :

create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................If I recall correctly, a scalar user defined function must return a deterministic value (ie, if you pass in the same parameters, you will get the same results). A non-deterministic function, would take a randomizer (such as GetDate()) and return a different result everytime you called it (even when calling it with the same parameters). I don't believe that this is allowed.

Regards,

hmscott

Hi,

It is possible to use getdate() in userdefined function. If so, how to do the same ?

The following code throws error :

create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................|||Create view v_getdate as
Select ThisDate = getdate()

Then reference v_getdate.ThisDate in your function.

GetDate inside a Function

Hi,
I tried to use GetDate inside a SQL server 2000 user defined function and
got error:
Invalid use of GetDate within a function
Here is my code snippet:
DECLARE @.Today smallDateTime
SET @.Today = GetDate()
What should I do?
TIAGETDATE() is nondeterinistic and, thus, cannot be used in a function. You
can, however, create a view and use it:
create view Now
as
select getdate () RightNow
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
Hi,
I tried to use GetDate inside a SQL server 2000 user defined function and
got error:
Invalid use of GetDate within a function
Here is my code snippet:
DECLARE @.Today smallDateTime
SET @.Today = GetDate()
What should I do?
TIA|||do this
declare @.date datetime
select @.date =max(last_XXXXX) from master.dbo.sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>|||oops sorry, nasty typo there
declare @.date datetime
select @.date =max(last_bAtch) from sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>|||I was wondering if maybe the developer was having a bad day with the
wife/mother-in-law when he designed the Last_XXXXX column <grin>
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
> oops sorry, nasty typo there
>
> declare @.date datetime
> select @.date =max(last_bAtch) from sysprocesses
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "John" <someone@.microsoft.com> wrote in message
> news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
>|||ROFL!
I must admit, it was the very first thing that caught my eye when I
glanced at Hilary's code snippet. Great Freudian slip there Hilary!
*mike hodgson*
http://sqlnerd.blogspot.com
Dave Frommer wrote:

>I was wondering if maybe the developer was having a bad day with the
>wife/mother-in-law when he designed the Last_XXXXX column <grin>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
>
>
>|||Thanks for pointing that out. Needed a good laugh in the morning. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave Frommer" <anti@.spam.com> wrote in message news:eQaWLxWHGHA.1028@.TK2MSFTNGP11.phx.gbl.
.
>I was wondering if maybe the developer was having a bad day with the
> wife/mother-in-law when he designed the Last_XXXXX column <grin>
>|||:-))))))))))))))))
"Dave Frommer" <anti@.spam.com> wrote in message
news:eQaWLxWHGHA.1028@.TK2MSFTNGP11.phx.gbl...
>I was wondering if maybe the developer was having a bad day with the
>wife/mother-in-law when he designed the Last_XXXXX column <grin>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
>|||last_XXXXX? Must be a dog show database. :)
ML
http://milambda.blogspot.com/|||The mask has slipped... ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
oops sorry, nasty typo there
declare @.date datetime
select @.date =max(last_bAtch) from sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>