July 5, 2003 at 9:49 am
Dear SQL Gurus
Recently we upgraded to SQL 2000 from 6.5. Here we are facing a particular sort of trouble. After upgrading we recreated startup procedure. This startup procedure is also having some specialty this first goes for setting database to single user mode in order to maintain data consistency avoiding users from modifying data while this bit lengthy procedure runs.
The structure of the startup procedure is some thing like
i)Goes to single user mode
ii)Procedure 1
iii)Procedure 2
iv)Procedure 3
v)….
vi)…
vii)Goes to normal mode
In the procedure 3 , procedure stops and it comes out ,and leaves the system in single user mode.
Any one experienced similar kind of problem. I am not system administrator. System administrator asks to rewrite the procedure. But the same works fine with SQL 6.5 startup as well as an individual procedure in 2000.
July 7, 2003 at 6:22 am
Any error handling in your startup procedure to reset the db to multi-user mode in case of an error?
Are you getting any errors in your procedure 3?
Joseph
July 8, 2003 at 8:47 am
One curious error we are getting in the procedure 3,its in an insert statement which is includes a select statement date time out of range.
The interesting thing is there is only one date filed in the table and the corresponding field in the select also belongs to same type ! .
As per the advice of my friend we gave a set datefotmat ‘mm/dd/yyyy’ before the statement and now its working fine.
But my doubts are remaining,
i)How an error in a statement will step over the following steps
ii)In an insert statement why it is rejecting the data in with same datetime type
July 8, 2003 at 11:11 am
Concerning the date issue.
It depends on what you are doing with the date data.
For example:
Let's say you are trying to input a date...
01/28/03
SQL Server doesn't know how to interpret that correctly. It might think it's dd/mm/yy and obviously there aren't 28 months in a year. That's why SET DATEFORMAT mdy works. Most people (I believe) use the unambiguous date format of yyyy-mm-dd. That way there's less confusion.
-SQLBill
July 8, 2003 at 11:12 pm
Let me clarify the topic once again
I am not doing anything with the data just inserting from one table to another
Like
Insert into Table1 (po_no,po_date ,…..)
Select po_no ,cr_date from Table2
Both are date fields ,
All most other procedures and queries are running well ,one difference I noticed is startup procedure is in dbo schema and other procedures are coming from different schemas.If default dateformat is different in these schemas will create problem or not
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply