Problems with T-SQL select convert
in [Programming SQL]
|
Prev: www.EvilSQL.com
Next: what is that?
From: Gisele BP Gisele on 4 Jun 2007 16:10 When I used the statment select convert in a field datetime, for example: "select convert(varchar(20), field, 111142) from table" The service MSSQLServer is down, and in the SQL Server log shows: SqlSecurityHandler is invoked. Potential buffer overrun detected - server is terminating. Using 'dbghelp.dll' version '4.0.5' *Dump thread - spid = 53, PSS = 0x45419210, EC = 0x45419540 *Stack Dump being sent to e:\MSSQL$SUPORTE\log\SQLDump0003.txt * ******************************************************************************* * * BEGIN STACK DUMP: * 05/31/07 11:34:20 spid 53 * * ex_terminator - Last chance exception handling I found and applied the Kb: http://support.microsoft.com/kb/916287/en-us. But this problems persist. My servers are: Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) I think this is a SQL´s Bug. Somebody have some Idea??? What Hot fix Should I apply? Thanks!
From: rpresser on 4 Jun 2007 16:59 On Jun 4, 4:10 pm, Gisele BP <Gisele B...(a)discussions.microsoft.com> wrote: > When I used the statment select convert in a field datetime, for example: > "select convert(varchar(20), field, 111142) from table" > > The service MSSQLServer is down, and in the SQL Server log shows: > > SqlSecurityHandler is invoked. Potential buffer overrun detected - server is > terminating. 111142 is much, much too large to be used as a datetime convert style, at least in SQL Server 2000. BOL says that the largest value allowed is 130. What datetime style were you trying to generate?
From: Gisele BP on 5 Jun 2007 10:33 See this example: CREATE TABLE [dbo].[TESTE] ( [dPrevisaoColeta] [datetime] NULL , ) select convert(varchar(20), fbfr112.dPrevisaoColeta, 111142) from TESTE Unhappyly the analyst typed this command wrong and the SQL did not return message error, but the service is down. "rpresser" wrote: > On Jun 4, 4:10 pm, Gisele BP <Gisele B...(a)discussions.microsoft.com> > wrote: > > When I used the statment select convert in a field datetime, for example: > > "select convert(varchar(20), field, 111142) from table" > > > > The service MSSQLServer is down, and in the SQL Server log shows: > > > > SqlSecurityHandler is invoked. Potential buffer overrun detected - server is > > terminating. > > 111142 is much, much too large to be used as a datetime convert style, > at least in SQL Server 2000. BOL says that the largest value allowed > is 130. What datetime style were you trying to generate? > > >
From: rpresser on 5 Jun 2007 12:08 On Jun 5, 10:33 am, Gisele BP <Gisel...(a)discussions.microsoft.com> wrote: > See this example: > > CREATE TABLE [dbo].[TESTE] ( > [dPrevisaoColeta] [datetime] NULL , > ) > > select convert(varchar(20), fbfr112.dPrevisaoColeta, 111142) from TESTE > > Unhappyly the analyst typed this command wrong and the SQL did not return > message error, but the service is down. Ah, so it was an accidentally wrong command that by surprise brought down the server. Got it. On my system, using SQL 2000 Query Analyzer, I get an error from the client connection (not the server): [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()). Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation. But the SQL Server service doesn't go down, and nothing is logged in the error log. A KB search turned up this, maybe related, maybe not: ----------------------- http://support.microsoft.com/kb/913438 FIX: The SQL Server process may end unexpectedly when you turn on trace flag -T1204 and a profiler trace is capturing the Lock:DeadLock Chain event in SQL Server 2000 SP4 SYMPTOMS A computer that is running the Microsoft SQL Server 2000 SP4 may unexpectedly end the SQL Server process and you may receive the following message in the SQL Server log: <Date> <Time> spid4 SqlSecurityHandler is invoked. Server is terminating. You experience this problem when all the following conditions are true: · A deadlock is detected by the lock manager. · The -T1204 trace flag is enabled. · The Profiler is running and is capturing the Lock:Deadlock Chain event. · The SPID that is reported in the deadlock is orphaned. Note You may run the following command in Query Analyzer to see if the -T1204 trace flag is enabled: dbcc traceon (3604) dbcc tracestatus(-1) ---------------------------- The total crash is probably a bug; I'd report it to MS.
From: --CELKO-- on 5 Jun 2007 17:44
>> When I used the statement select convert in a field [SIC: columns are not fields] DATETIME , for example: "SELECT CONVERT (VARCHAR (20), field, 111142) FROM Foobar" << You missed most of the foundations of SQL. Field and column are totally different ideas. Why are you formatting data in the back end? The basic principle of a tiered architecture is that display is done in the front end and never in the back end. This a more basic programming principle than just SQL and RDBMS. Please learn the ISO-8601 temporal formats and use them so that you never have this issue again. |