Problems with T-SQL select convert
in [Programming SQL]
|
Prev: www.EvilSQL.com
Next: what is that?
From: raibeart on 5 Jun 2007 17:51 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...(a)earthlink.net> wrote: > >> 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.
From: rpresser on 6 Jun 2007 10:49 On Jun 5, 5:51 pm, raibeart <raibe...(a)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. "
From: Gisele BP on 7 Jun 2007 11:15 unhappyly the problem still its persists! "rpresser" wrote: > 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: > 7 A deadlock is detected by the lock manager. > 7 The -T1204 trace flag is enabled. > 7 The Profiler is running and is capturing the Lock:Deadlock Chain > event. > 7 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: rpresser on 7 Jun 2007 13:45 On Jun 7, 11:15 am, Gisele BP <Gisel...(a)discussions.microsoft.com> wrote: > unhappyly the problem still its persists! What problem do you mean at this point? The problem that SQL server goes down if you send it an illegal convert value? It is either a bug in SQL server, or a problem with your installation. Contact MS for help if you want a bug investigated or fixed. Reinstall SQL Server if you want to investigate your installation. The problem that your consultant types illegal convert values? Can't help you with that. Maybe hire a new consultant. The problem that your SQL service is still down, and won't start? If that's the case then you have a serious problem with your installation, and we need more data about its inability to start.
From: Olavo Borges on 11 Jun 2007 08:52
I don't think she misses the foundations of SQL. The problem is SQL Server service goes down after an illegal convert format attempt, instead of reporting this error to the user. So, if an normal user sends a simple illegal select statement with convert, it will bring down the whole SQL instance. "--CELKO--" wrote: > >> 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. > > > |