Sql Server Programming :: Problems with T-SQL select convert


	Problems with T-SQL select convert 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 SQLs Bug. Somebody have some Idea??? What Hot fix Should I apply? Thanks! On Jun 4, 4:10 pm, Gisele BP <Gisele B@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? -----------------------------------------------Reply----------------------------------------------- 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. - Hide quoted text - "rpresser" wrote: > On Jun 4, 4:10 pm, Gisele BP <Gisele B@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? On Jun 5, 10:33 am, Gisele BP <Gisel@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. -----------------------------------------------Reply----------------------------------------------- >> 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. -----------------------------------------------Reply----------------------------------------------- Unfortunately Celko, if he is using a system like we do at work, ColdFusion, I have to do the formatting of all dates in the backend and pass it to the front end because the front end is so lame. His real problem is not passing in the correct formatting parameter at the end of the CONVERT statement. He needs to go to books on line and see how to do it. On Jun 5, 4:44 pm, --CELKO-- <jcelko@earthlink.net> wrote: - Hide quoted text - > >> 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. On Jun 5, 5:51 pm, raibeart <raibe@gmail.com> wrote: > Unfortunately Celko, if he is using a system like we do at work, > ColdFusion, I have to do the formatting > of all dates in the backend and pass it to the front end because the > front end is so lame. > His real problem is not passing in the correct formatting parameter at > the end of the CONVERT > statement. He needs to go to books on line and see how to do it. As he explained, somewhat obscurely, the large formatting parameter was an accident; he[1] is trying to find out why this simple mistyping cause THE WHOLE SQL SERVER TO GO DOWN. "Unhappyly the analyst typed this command wrong and the SQL did not return message error, but the service is down. "