Synchronizing the Default Installation Language and Date Syntax
I'm having a problem with Query Analyzer. When I write the following query
SELECT WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems
WHERE
DateCreated > '28/02/2001'
the query fails and returns the following error message:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
But if I write the same query this way:
SELECT WorkItemKey,DateCreated,DateDue,
DateCompleted from WorkItems
WHERE
DateCreated > '2001-02-28'
OR
SELECT WorkItemKey,DateCreated,DateDue,
DateCompleted from WorkItems
WHERE
DateCreated > '28/Feb/2001'
the query works fine. Why do I get these results?
Query Analyzer bases its date interpretations on SQL Server's default installation language and date syntax. If the default installation language is US English, SQL Server interprets a date with the syntax 28/02/2001 as US English's default date format mm/dd/yyyy, or 28th month, second day, 2001—a date that doesn't exist. When you write the same date with the syntax 2001-02-28, SQL Server interprets the date by using a different conversion semantic. To see the default formats in SQL Server Books Online (BOL), look up SQL Server's default installation language (for example, US English), then follow the links to the defaults for that language. See Kalen Delaney, Inside SQL Server, "Solving the Datetime Mystery," September 2000, and "Inside Datetime Data," October 2000, for more datetime information.
Increasing the Maximum Number of Characters in a Column
I need to display data in a text column in Query Analyzer as well as in our Java-based front-end tool. Query Analyzer limits the number of displayed characters to 256 bytes. I've been looping through each column in 256-byte increments by using the READTEXT statement, but do you know of a better technique? Furthermore, what's more efficient: using the READTEXT clause or the SUBSTRING clause?
The path Tools, Options, Results in Query Analyzer 2000 takes you to a dialog box that lets you increase the maximum number of characters per column. The default ceiling is 256, so bump it up to a larger number, such as 8000. For reading text data, the most efficient clause is READTEXT.
Running a Maintenance Plan in Multiple-User Mode
I'm running SQL Server 2000 on Windows 2000 Service Pack 1 (SP1). I've encountered a problem when I run a maintenance plan. The integrity checking and backups fail for the master and msdb databases as well as for user database ODS1. I can't put these databases into single-user mode. For some reason, the connections appear to stay open. When I stopped and restarted SQL Server, the ODS1 integrity check worked, although the master and msdb checks failed again. Do you have any ideas about what's happening?
You've probably set the option attempt to repair any minor problems in the Database Maintenance Wizard, which behind the scenes runs DBCC, CHECKDB, or CHECKTABLE with the repair option set. When you request the repair option, the database has to be in single-user mode. To avoid the problem, edit the plan and remove the repair option.
End of Article
Prev. page
1
[2]
next page -->
