Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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.

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:

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. "

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc