April 22, 2013 at 3:59 pm
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! 🙂
April 22, 2013 at 8:09 pm
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)
April 22, 2013 at 9:08 pm
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.
April 22, 2013 at 10:04 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 12:24 am
I prefer SSMS's GUI.
April 23, 2013 at 7:56 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 10:12 am
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! 🙂
April 23, 2013 at 10:26 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 10:29 am
Oh, okay, thought you were on 2K5 as well. Perhaps someone with 2K5 can test?
April 23, 2013 at 10:32 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 10:38 am
Thanks again, really appreciate the assistance and advice.
I came up on MS Access--if it weren't for Access, I'd never have gotten into databases at all and eventually fallen in love with them--and I just "see" databases, so using a GUI is kind of ingrained now for me. But I do use SQL text as well and realize it's a purer form of interaction. 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply