October 6, 2011 at 12:59 pm
Sean Lange (10/6/2011)
GSquared (10/6/2011)
Sean Lange (10/6/2011)
Jack Corbett (10/6/2011)
I refuse to feel old until the president of the U.S. is younger than me. Eventually it will happen, but not yet.Never really listened to the Ramones, although I'm sure I've heard some of their music at some point.
Big Beatles fan though, even though I'm still too young to remember them being together.
I can just about guarantee you have heard the Ramones at some point. Their music has been making into commercials and stuff over the last few years. So strange that all the punk rock I grew up with is so mainstream that is can be background music for commercials today.
Commercial, industrially distributed, "punk rock" was always an amusing concept to me.
But, yes, I remember The Ramones, and The Beatles broke up in my lifetime.
I go back an forth between being amazed at how young I am, and shocked at how old I've gotten, sometimes within the same train of thought.
And the Ramones were anything but commercial punk rock at the time.
Being a child of the 60s, I too was alive when the Beatles broke up. I guess the point we are at now is sort of the definition of mid-life crisis. When you still feel young but realize that you are getting old.
The Ramones were always frustrated that they never managed to be "commerical" punk rock.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 6, 2011 at 1:03 pm
Lynn Pettis (10/6/2011)
Jack Corbett (10/6/2011)
So working on a system where there is a UDF that calls a view and the view definition is:
create view SYSTEM_DATE_VIEW
as select getdate() SYSTEM_DATE
GO
So other code calls the UDF. It's more effort than calling GETDATE() directly.
There may have been a reason. On my blog I provide a function for SQL Server 2005 that will generate a random length string. To make it work I had to create a view whose definition is:
create view dbo.MyNewID as
select newid() as NewIDValue;
go
I have not tried this in SQL Server 2008 or 2008 R2 yet so I guess I should test it tonight without the view definition.
CREATE FUNCTION WontWork ()
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
RETURN NEWID();
END
Msg 443, Level 16, State 1, Procedure WontWork, Line 5
Invalid use of a side-effecting operator 'newid' within a function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2011 at 1:07 pm
GilaMonster (10/6/2011)
Lynn Pettis (10/6/2011)
Jack Corbett (10/6/2011)
So working on a system where there is a UDF that calls a view and the view definition is:
create view SYSTEM_DATE_VIEW
as select getdate() SYSTEM_DATE
GO
So other code calls the UDF. It's more effort than calling GETDATE() directly.
There may have been a reason. On my blog I provide a function for SQL Server 2005 that will generate a random length string. To make it work I had to create a view whose definition is:
create view dbo.MyNewID as
select newid() as NewIDValue;
go
I have not tried this in SQL Server 2008 or 2008 R2 yet so I guess I should test it tonight without the view definition.
CREATE FUNCTION WontWork ()RETURNS UNIQUEIDENTIFIER
AS
BEGIN
RETURN NEWID();
END
Msg 443, Level 16, State 1, Procedure WontWork, Line 5
Invalid use of a side-effecting operator 'newid' within a function.
Well, saves me from seeing if things had changed when I get home from officiating a high school soccer game. Now what am I going to do? 😉
October 6, 2011 at 1:09 pm
GilaMonster (10/6/2011)
Lynn Pettis (10/6/2011)
Jack Corbett (10/6/2011)
So working on a system where there is a UDF that calls a view and the view definition is:
create view SYSTEM_DATE_VIEW
as select getdate() SYSTEM_DATE
GO
So other code calls the UDF. It's more effort than calling GETDATE() directly.
There may have been a reason. On my blog I provide a function for SQL Server 2005 that will generate a random length string. To make it work I had to create a view whose definition is:
create view dbo.MyNewID as
select newid() as NewIDValue;
go
I have not tried this in SQL Server 2008 or 2008 R2 yet so I guess I should test it tonight without the view definition.
CREATE FUNCTION WontWork ()RETURNS UNIQUEIDENTIFIER
AS
BEGIN
RETURN NEWID();
END
Msg 443, Level 16, State 1, Procedure WontWork, Line 5
Invalid use of a side-effecting operator 'newid' within a function.
Didn't GETDATE() use to (or still get) the same error?
October 6, 2011 at 1:25 pm
Lynn Pettis (10/6/2011)
Didn't GETDATE() use to (or still get) the same error?
Yes, in SQL 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2011 at 1:29 pm
GilaMonster (10/6/2011)
Lynn Pettis (10/6/2011)
Didn't GETDATE() use to (or still get) the same error?Yes, in SQL 2000.
Then the code Jack ran into my be a vestige left over when the database was running using SQL Server 2000.
I thought I remembered having to do that way back when.
October 6, 2011 at 1:32 pm
Lynn Pettis (10/6/2011)
GilaMonster (10/6/2011)
Lynn Pettis (10/6/2011)
Didn't GETDATE() use to (or still get) the same error?Yes, in SQL 2000.
Then the code Jack ran into my be a vestige left over when the database was running using SQL Server 2000.
I thought I remembered having to do that way back when.
I've used it that way. Been years though.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2011 at 1:39 pm
Lynn Pettis (10/6/2011)
GilaMonster (10/6/2011)
Lynn Pettis (10/6/2011)
Didn't GETDATE() use to (or still get) the same error?Yes, in SQL 2000.
Then the code Jack ran into my be a vestige left over when the database was running using SQL Server 2000.
If the function used the date I'd agree, but all it does is query the view and return the date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2011 at 1:56 pm
GilaMonster (10/6/2011)
Lynn Pettis (10/6/2011)
GilaMonster (10/6/2011)
Lynn Pettis (10/6/2011)
Didn't GETDATE() use to (or still get) the same error?Yes, in SQL 2000.
Then the code Jack ran into my be a vestige left over when the database was running using SQL Server 2000.
If the function used the date I'd agree, but all it does is query the view and return the date.
Well, possibly. Without seeing the UDF, are we sure? Not doubting Jack here, but it would have been nice to see the UDF as well.
Plus, where else is the UDF being called? Could be the UDF and VIEW were created for one thing, and then others just starting using the UDF as well even when not needed.
October 6, 2011 at 2:12 pm
Lynn Pettis (10/6/2011)
GilaMonster (10/6/2011)
Lynn Pettis (10/6/2011)
GilaMonster (10/6/2011)
Lynn Pettis (10/6/2011)
Didn't GETDATE() use to (or still get) the same error?Yes, in SQL 2000.
Then the code Jack ran into my be a vestige left over when the database was running using SQL Server 2000.
If the function used the date I'd agree, but all it does is query the view and return the date.
Well, possibly. Without seeing the UDF, are we sure? Not doubting Jack here, but it would have been nice to see the UDF as well.
Plus, where else is the UDF being called? Could be the UDF and VIEW were created for one thing, and then others just starting using the UDF as well even when not needed.
Okay here's the function:
create function [dbo].[SYSDATE]()
returns datetime
as
begin
declare @sysdate_l datetime
select @sysdate_l = sdv.system_date
from system_date_view sdv
return @sysdate_l
end
Really funny in my opinion. As Gail has mentioned, if there was something else done with the data in the function I could see it being needed.
Of course the database is a port from ORACLE and maybe you had to do something like this in ORACLE. I wouldn't know. I assume ORACLE has a function that returns the current date and time.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 6, 2011 at 2:18 pm
Yes, Oracle does, and thankfully I'm forgetting Oracle rather quickly.
Based on that, it could simply be someone not familiar with T-SQL porting Oracle code and not recognizing what could be changed. Or, they didn't have time to make the changes and never got around to going back either. The old adage, if it ain't broke don't fix it.
October 6, 2011 at 2:22 pm
When I was coming from a VB background into SQL, I was used to the Now() function to get the current date and time, and didn't yet know about GetDate(). Not knowing what to search for, I couldn't find the equivalent SQL function, and had to resort to some oddball workaround until I discovered GetDate(). I don't remember what I used (it was 10 years ago and it really was better off forgotten), but this might be the result of something like that.
After all, just try Googling "now" and see if you get anything useful!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2011 at 2:24 pm
Lynn Pettis (10/6/2011)
Yes, Oracle does, and thankfully I'm forgetting Oracle rather quickly.Based on that, it could simply be someone not familiar with T-SQL porting Oracle code and not recognizing what could be changed. Or, they didn't have time to make the changes and never got around to going back either. The old adage, if it ain't broke don't fix it.
We're querying some data from older Oracle instances and we have HUGE problems with Oracle's date format.
EDIT: Here's what I mean
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 6, 2011 at 2:29 pm
Stefan Krzywicki (10/6/2011)
Lynn Pettis (10/6/2011)
Yes, Oracle does, and thankfully I'm forgetting Oracle rather quickly.Based on that, it could simply be someone not familiar with T-SQL porting Oracle code and not recognizing what could be changed. Or, they didn't have time to make the changes and never got around to going back either. The old adage, if it ain't broke don't fix it.
We're querying some data from older Oracle instances and we have HUGE problems with Oracle's date format.
If you can build views in Oracle over the tables that you are extracting data, convert the Oracle date values to ISO standard character format in the views that SQL can convert back to SQL Server datatime data. About the only advice I can give you.
October 6, 2011 at 2:33 pm
Lynn Pettis (10/6/2011)
Stefan Krzywicki (10/6/2011)
Lynn Pettis (10/6/2011)
Yes, Oracle does, and thankfully I'm forgetting Oracle rather quickly.Based on that, it could simply be someone not familiar with T-SQL porting Oracle code and not recognizing what could be changed. Or, they didn't have time to make the changes and never got around to going back either. The old adage, if it ain't broke don't fix it.
We're querying some data from older Oracle instances and we have HUGE problems with Oracle's date format.
If you can build views in Oracle over the tables that you are extracting data, convert the Oracle date values to ISO standard character format in the views that SQL can convert back to SQL Server datatime data. About the only advice I can give you.
I appreciate the advice, but I can't touch that server. Any of the Oracle servers, really.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 30,706 through 30,720 (of 66,749 total)
You must be logged in to reply to this topic. Login to reply