SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Severe Server Error with function in default value


Severe Server Error with function in default value

Author
Message
autoexcrement
autoexcrement
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 889
I've been playing around on a test database today, and one of the things I've done is created a function to retrieve the current Pacific datetime (server is on Mountain).

CREATE FUNCTION [dbo].[fn_get_pst_datetime] (@serverdatetime datetime = NULL)
RETURNS datetime
AS
BEGIN
IF @serverdatetime IS NULL
SET @serverdatetime = getdate()

DECLARE @pstdatetime datetime
SET @pstdatetime = dateadd(hour,(-1),@serverdatetime)
RETURN @pstdatetime
END



Function seems to work when I call it in queries. But if I include "[dbo].[fn_get_pst_datetime](DEFAULT)" as the default value of any table's datetime fields, the table gets corrupted and I have to delete it and start over.

- Unable to modify table.
A severe error occurred on the current command. The results, if any, should be discarded.


Is my function bad? Did I do something wrong? What's the prob here? Thanks! Smile


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Andrew G
Andrew G
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4035 Visits: 2279
Function is ok (although if it runs on a server in a different timezone/regional setting it wont be correct, if it was named get_time_minus_1_hour it wouldn't matter).

How are you using the function?

This works fine:
CREATE TABLE testfunc (checkdate datetime)

INSERT INTO testfunc (checkdate)
SELECT dbo.fn_get_pst_datetime(DEFAULT)


autoexcrement
autoexcrement
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 889
Maybe it's just an SSMS problem then. I didn't have any trouble creating the table and the default via SQL:

CREATE TABLE testfunc (checkdate datetime)

ALTER TABLE [dbo].[testfunc] ADD CONSTRAINT [DF_testfunc_checkdate] DEFAULT (dbo.fn_get_pst_datetime(DEFAULT)) FOR [checkdate]
GO



But if I then try to open the table in "design" mode via SSMS, I get that severe server error.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62123 Visits: 17954
autoexcrement (4/22/2013)
Maybe it's just an SSMS problem then. I didn't have any trouble creating the table and the default via SQL:

CREATE TABLE testfunc (checkdate datetime)

ALTER TABLE [dbo].[testfunc] ADD CONSTRAINT [DF_testfunc_checkdate] DEFAULT (dbo.fn_get_pst_datetime(DEFAULT)) FOR [checkdate]
GO



But if I then try to open the table in "design" mode via SSMS, I get that severe server error.


Why do you want to open this in design mode? Honestly design mode should rarely be used, if at all. It is easier and faster to modify your tables using t-sql. That being said I don't see anything wrong with your table or function that would cause those types of errors.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
autoexcrement
autoexcrement
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 889
I prefer SSMS's GUI.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62123 Visits: 17954
Now that I am at my desk I tested some stuff with your functions and I can't reproduce the issues you are seeing.

I did however make some adjustments to your scalar function that will make it a little faster and simpler.

Here is my proposed version.


CREATE FUNCTION [dbo].[fn_get_pst_datetime] (@serverdatetime datetime = NULL)
RETURNS datetime WITH SCHEMABINDING
AS
BEGIN
RETURN dateadd(hour, -1, ISNULL(@serverdatetime, GETDATE()))
END



Then here is the code I used to test:


CREATE TABLE testfunc (checkdate datetime)

--Checked designer here all is fine

INSERT INTO testfunc (checkdate)
SELECT dbo.fn_get_pst_datetime(DEFAULT)

--Checked designer here all is still fine

ALTER TABLE [dbo].[testfunc] ADD CONSTRAINT [DF_testfunc_checkdate] DEFAULT (dbo.fn_get_pst_datetime(DEFAULT)) FOR [checkdate]

--Checked designer here all is still fine

INSERT INTO testfunc (checkdate)
SELECT dbo.fn_get_pst_datetime(DEFAULT)

--Checked designer here all is still fine



Digging around the web it sounds like many people have run into the issue you are facing when there is a corrupt index on the base table. Obviously the table you posted here is an example and not the actual table you were having issues with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
autoexcrement
autoexcrement
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 889
Thanks for the continued replies!

This database is hosted at a web hosting company on SQL 2K5. Maybe their box doesn't have all the necessary hotfixes installed or something, because I am able to reproduce this error over and over, including with the exact code shown above. Sad I think I'll contact the host, since you have shown that this shouldn't be a problem.

And thanks also for the "WITH" tip! Smile


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62123 Visits: 17954
autoexcrement (4/23/2013)
Thanks for the continued replies!

This database is hosted at a web hosting company on SQL 2K5. Maybe their box doesn't have all the necessary hotfixes installed or something, because I am able to reproduce this error over and over, including with the exact code shown above. Sad I think I'll contact the host, since you have shown that this shouldn't be a problem.

And thanks also for the "WITH" tip! Smile


Well I am on 2008r2 so maybe there is some difference there.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
autoexcrement
autoexcrement
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 889
Oh, okay, thought you were on 2K5 as well. Perhaps someone with 2K5 can test?


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62123 Visits: 17954
autoexcrement (4/23/2013)
Thanks for the continued replies!

This database is hosted at a web hosting company on SQL 2K5. Maybe their box doesn't have all the necessary hotfixes installed or something, because I am able to reproduce this error over and over, including with the exact code shown above. Sad I think I'll contact the host, since you have shown that this shouldn't be a problem.

And thanks also for the "WITH" tip! Smile


I do however have a 2005 box. I just added the function, then the table. All is well. Once I added the constraint to the table I was unable to open it in the designer.

My version:


Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: )


I would suggest you don't use the designer anyway. When you modify tables it can do some pretty nasty things sometimes. It isn't likely to corrupt anything but sometimes a simple change in the GUI can take a LONG time when just doing it in t-sql takes milliseconds.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search