Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Severe Server Error with function in default value Expand / Collapse
Author
Message
Posted Monday, April 22, 2013 3:59 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:14 PM
Points: 153, Visits: 590
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! :)



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1445223
Posted Monday, April 22, 2013 8:09 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:29 PM
Points: 1,236, Visits: 1,718
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)

Post #1445240
Posted Monday, April 22, 2013 9:08 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:14 PM
Points: 153, Visits: 590
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
Post #1445251
Posted Monday, April 22, 2013 10:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 13,007, Visits: 12,425
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 Moden's 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)
Post #1445262
Posted Tuesday, April 23, 2013 12:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:14 PM
Points: 153, Visits: 590
I prefer SSMS's GUI.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1445285
Posted Tuesday, April 23, 2013 7:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 13,007, Visits: 12,425
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 Moden's 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)
Post #1445437
Posted Tuesday, April 23, 2013 10:12 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:14 PM
Points: 153, Visits: 590
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. 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! :)



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1445556
Posted Tuesday, April 23, 2013 10:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 13,007, Visits: 12,425
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. 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! :)


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 Moden's 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)
Post #1445563
Posted Tuesday, April 23, 2013 10:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:14 PM
Points: 153, Visits: 590
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
Post #1445565
Posted Tuesday, April 23, 2013 10:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 13,007, Visits: 12,425
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. 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! :)


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 Moden's 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)
Post #1445566
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse