I have a system using asp pages & ADO & SQL Server 2000, which processes files, builds a SQL insert statement from the file content and then executes it.
If the insert SQL fails, I need to know whether there was something wrong with the insert SQL, or something wrong with the database (e.g. SQL Server times out), and handle those differently,
Questions
=======
1. Is there an easy way to do this without checking against a list of error codes?
2. Can anyone point me to a list of errorcodes? Can't find this anywhere
Hi, I have a page with only a Datasource and a Gridview that allows Delete. In the database there is a referential integrity (RI) to the one of the columns. When a user tries to delete the row, the RI stops the delete and throws an error message the way it should. But it also create a hard failure on the page. How do I trap the error message so I can display a message "Referential Integrity denied deletion of this record." rather than having it fail ?
I have a database with primary and Unique key contraints on SQL Server 2000. I'm front ending it with Access 2k.
I want to trap the error when the Unique key constraint is hit, but i can't capture the error number. All attempts return error 0.
The error i get is [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY constraint 'IX_lut_Referral_Source'. Cannot insert duplicate key in object 'lut_Referral_Source'. (#2627)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (#3621)
I've tried the soultion given here http://support.microsoft.com/kb/q185384 but now get a Type mismatch error on errStored
SaveRecODBCErr: ' The function failed because of an ODBC error. ' Below are a list of some of the known error numbers. ' If you are not receiving an error in this list, ' add that error to the Select Case statement. For Each errStored In DBEngine.Errors
Greeting All, I have a stored proc that dynamically truncates all thetables in my databases. I use a cursor and some dynamic sql for this:......create cursorLoop through sysobjects and get all table names in my database.....exec ('truncate table ' + @TableName)Now, I want to be able to determine if an error occurred or not nad logthat error to a table in another database.However, when I try to trap the value of @@ERROR after theexec ('truncate table ' + @TableName) when an actual error occurs itfails. My error was synthetically created by placing a foreign key onthe table which precludes the option of truncation:Server: Msg 4712, Level 16, State 1, Line 1Cannot truncate table 'MyTable' because it is being referenced by aFOREIGN KEY constraint.The actual relevant code snippet is:BEGINBEGINSET @v_RowCount = (SELECT rowcntFROM sysindexesWHERE id = (SELECT idFROM sysobjectsWHERE name = @v_Name)AND indid IN (0,1))EXEC('truncate table ' + @v_Name)-- If there was an error truncating the current table.-- Write the event to the MessageLog table.IF (@@ERROR <> 0)BEGINSET @v_OutputMessage = ('There was an error ' + @v_name)INSERT INTO MessageLog (message) values (@v_outputmessage)RETURN (-1)ENDLike I was saying, when the error is generated because of the foreignkey the variable @@error is never set to 4712, in fact if I were to puta "select @@ERRROR" directly below the "exec('tru..')" statement itwould never be executed. The only thing that would show up inEnterprise Manager would be the:Server: Msg 4712, Level 16, State 1, Line 1Cannot truncate table 'MyTable' because it is being referenced by aFOREIGN KEY constraint.Any ideas as to what is going on here?Thanks, TFD.
Hello, I have the following code to iterate through each view in a SQLServer and call the "sp_refreshview" command against it. It worksgreat until it finds a view that is damaged, or otherwise cannot berefreshed. Then the whole routine stops working.Can someone please help me re-write this code so that any views thatfail the "sp_refreshview" command get skipped. I'm sure it's just amatter of putting some basic error trapping into the loop, but I've hada few goes at it and failed.Many thanks.DECLARE @DatabaseObject varchar(255)DECLARE ObjectCursor CURSORFOR SELECT table_name FROM information_schema.tables WHERE table_type ='view'OPEN ObjectCursorFETCH NEXT FROM ObjectCursor INTO @DatabaseObjectWHILE @@FETCH_STATUS = 0BEGINEXEC sp_refreshview @DatabaseObjectPrint @DatabaseObject + ' was successfully refreshed.'FETCH NEXT FROM ObjectCursor INTO @DatabaseObjectENDCLOSE ObjectCursorDEALLOCATE ObjectCursorGO
Below is the script. The problem is when I simulated the Oracle linkdrop, my SQL2K never have to a chance to head to the GOTO section as itdies with this error msg and exit. Any idea on a workround? Thanks.Server: Msg 7399, Level 16, State 1, Procedure USP_Link_Check, Line 8OLE DB provider 'MSDAORA' reported an error.[OLE/DB provider returned message: ORA-12154: TNS:could not resolveservice name]OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializereturned 0x80004005: ].----------------------------------------------ALTER PROCEDURE [USP_Link_Check] ASDECLARE @myERROR int -- Local @@ERROR, @myRowCount int -- Local @@ROWCOUNT--- Verify network connectionsselect *from openquery(OraLink,'select count(*) from Oracle.table')IF @myERROR != 0 GOTO HANDLE_ERRORHANDLE_ERROR:Print ' Error in Oracle Link'RETURN @myERROR---------------------------------------------
On my aspx Web page, I want to delete a member from database table 'tblMember', but if this MemberID is used as FK in another table, I want to display a user friendlier message like "You cannot delete this member, ....." I am using Try, Catch blocks in my Web Page.
Currently it display this message: "DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_..._....' The conflict occurred in database '...', table 'tblMembers', column 'MemberID'. The statement has been terminated. "
So how should I precisely trap this error? Does anybody know what Exception is it? or what error number in SQL server?
I have another question about ADO (using C++). I have searched the MSDN but I haven't found a answer to my question (maybe I'm just too stupid)... If I make a connection to my SQL Server there may occur some errors, but how to find out what went wrong ?
In terms of code I have e.g. something like that: try { connection->Open(ConnectionString,Username,Password,ADODB::adConnectUnspecified); ...} catch (_com_error& e) { long numErrors = connection->Errors->Count; for (long i=0; i<numErrors; i++) { ADODB::ErrorPtr pErr = connection->Errors->GetItem(i); ..... }}
Now I could get the error number by pErr->GetNumber(). But with which symbolic constant has this to be compared to find out which error occured ?? I didn't find any...Or is there another better way to do this ?
Using the following syntax, sometimes non-zero error codes are returned: D:SQL Server x86Servers>start /wait setup D:SQL Server x86Servers>echo %errorlevel% 3010
Searching BOL for 3010 returns nothing.
Other error codes that were seen are 1603 and 9009.
What do these error codes mean? Is there a place where they can be looked up?
I am designing a SSIS package where i get text file as input. This file contains transaction details.
I have to map the members in each transaction for data integrity. For this i am using Lookup object.
Lookup object works very well upto my expectations but i am facing problem in mapping the error code sent bythe this object whenever a record fails. I am storing all the invalid records into text file.
All the records get stored into text file along with some SQL Server error_code and error_column. I cant interpret these last two columns as it displays some numeric code and column name.
Is there any way to map these error codes and columns and get relevant information out of it? Example error code = -1071607778 error column = 0
Does anyone know where to obtain a complete list of error codes which can occur in SQL Server ? For example : errror number 547 is a foreign key violation - where can I get a complete list of these numbers and there descritions ?
Send Mail works fine when I just want to send a message. However, when I try to send the results from a query as an attachment, I get a ODBC error. Here is example of the code and the error code that I recieve.
Use MyDatabase go exec master.dbo.xp_sendmail 'Doe,John', @query = 'Select * from tblMyTable', @subject = 'SQL Server Report', @message = 'The contents of the tblMyTable:', @attach_results = 'TRUE', @width = 250 go
I have one application through which i m installing SQL Server 2005 Express Edition.
If some failure occures, all I get is one exitCode which is a return code of SQL Express installer. Is there any way by which I can find out the exact reason of failure.
One example is error code 28062.
This error code is for "sql is unable to install on compressed folder / drive".
Dim cmdLogin As New SqlCommand("select * from UserName where ='" & txtusername.Text & "' and Password='" & txtpassword.Text & "'", conn) Dim dr As SqlDataReader dr =cmdLogin.ExecuteReader()
I am running a .Net application (VB.Net, .Net Framework 2.0) on a Windows CE 5.0 handheld device. I am relatively having good success with SQL CE replication, however sometimes (2 users out of 30 every 2 weeks), are freezing up in the application.
Here is what CE SQl software versions I installed: sqlce30.dev.ENU.wce5.armv4i.CAB sqlce30.repl.wce5.armv4i.CAB sqlce30.wce5.armv4i.CAB
I have a trace log in my application, and here is what pops up before the hang (warm rebooting the handheld works)
NativeError: 28037 SQL Msg: A request to send data to the computer running IIS has failed. For more information, see HRESULT.
NativeError: 28600 SQL Msg: The database is currently synchronizing with the server.
I am using a Thread to syncronize (microsofts built in class), as you can see I am well aware of 28600, it just means some other thread is already syncing, so I can wait until next time to syncronize. However, my users apps are freezing just after both error codes occur. Anything in the code below causing issues??? (maybe _repl.Dispose() is freezing up my handheld, because its freezing after this line of code (see below code):
"MTM.SPUD.Utilities.Utilities.Log("[DEBUGQLThread#" + threadId + "]: End of Thread Life.")"
Public Sub AsyncSyncronize(ByRef PendingData As Boolean, ByVal Publication As String, ByVal Subscription As String, ByRef LastSyncDateTime As DateTime) Dim _repl As New SqlCeReplication() Try ... savedAsyncResult = _repl.BeginSynchronize(objDelegate1, objDelegate2, objDelegate3, objDelegate4, _repl) Catch ex As Exception Finally End Try
End Sub
Public Sub OnSynchronizeComplete(ByVal asyncResult As IAsyncResult) Dim _repl As SqlCeReplication = Nothing Dim threadId As String = "" Try _repl = DirectCast(asyncResult.AsyncState, SqlCeReplication) threadId = _repl.InternetPassword _repl.EndSynchronize(asyncResult) dataIsPending = False
dataIsPending = True If exce.NativeError.ToString = "28600" Then MTM.SPUD.Utilities.Utilities.Log("[WARNINGQLThread#" + threadId + "]: Will try again next time") Return End If
Catch ex As Exception Return 'MTM.SPUD.Utilities.Utilities.Log("App Exception: " + ex.Message) 'Throw New Exception("EndSynchronize Failed: " + ex.Message)
Finally MTM.SPUD.Utilities.Utilities.Log("[DEBUGQLThread#" + threadId + "]: End of Thread Life.") If _repl IsNot Nothing Then _repl.Dispose() _repl = Nothing End If End Try End Sub
I am trying to create a trap to catch one of my developers which is messing around on my Live system, where he doesn't belong. I have already created a trigger which is logging and catching the guy out, but i need to know which queries he is executing on this table.
Here is my trigger:
Code Block ALTER TRIGGER [dbo].[ctr_SQLTrapDelete] ON [dbo].[Members] AFTER DELETE AS INSERT INTO SQLTrap ([User], TYPE, DateValue, HostName, SystemUser, TableName) SELECT USER_NAME(), 'DELETE', GETDATE(), HOST_NAME(), SYSTEM_USER, 'Members'
I want to add a Query field at the end of the table with the query that the guy executed.
Will anyone be able to tell me how where i am going to find the query that he executed, i am unsure of where it will reside.
I think possible in the Master.dbo.syscomments table but i'm unsure of how i am going to track the query to insert it into the table with the timestamp.
We are running SQL 2000 sp3a on Win2003 Server with all patches and SPs applied.
I want to run a stored procedure from a SQL Agent job. I want the stored procedure to run as a TRANSACTION to force a ROLLBACK in case of failure. However, if execute the procedure and an error of severity level 20 (I think) or greater happens I get blown out of the job immediately. Therefore I cannot call RAISEERROR or use @@ERROR. So, how do I assure that I can ROLLBACK the TRANSACTION?
I have searched the web and BOL and cannot find anything that addresses this specifically.
I'm using System.Net.Mail in a script task to send HTML email via SMPT client as follows:
Imports System Imports Microsoft.SqlServer.Dts.Runtime Imports System.Net.Mail Imports System.Net
Public Class ScriptMain
Public Sub Main()
Dim myHtmlMessage As MailMessage Dim mySmtpClient As SmtpClient
myHtmlMessage = New MailMessage("<from>", _ "<to>, _ "Test HTML Message", _ Dts.Variables("HtmlText").Value.ToString) myHtmlMessage.IsBodyHtml = True mySmtpClient = New SmtpClient("<smtpservername") mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials mySmtpClient.Send(myHtmlMessage)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I need to be able to trap an error when the email address is undeliverable. Is there a way to do this in SSIS? I am validating the email address before sending, but it can still come back as undeliverable. I need to be able to catch this.
hi i am running a stored procedure and i want to trap the error of that stored procedure and pass it on the user in the asp.net prog.
my stored procedure are running thru a class library using C#. i tried various options of creating a new sql connnection with out using the classlibrary, but i dont get any errors. the code s used is as follows Dim conn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("dbconnection1"))
Dim cmd As New SqlCommand("usp_ImportPlanDetails", conn)
and also tried doing this oImportPlan = New ImportPlan(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))
With oImportPlan Try .ClientID = Session("ClientId") ' .FileName = strFilePath1 .save() Catch sqlex1 As SqlException LogException(sqlex1) End Try
End With Catch sqlex As SqlException LogException(sqlex) End Try Catch ex As Exception lblErrorMsg.Text = "Error: Import Failed" + ex.Message lblErrorMsg.Visible = True
End Try but have no luck in displaying the errors, My stored procedure has raiseerrors, Please help me out
Is there any way to prevent unimportant errors in a stored procedure from causing exceptions in my C# code? This is preventing the SqlAdapter from filling the query results into my DataSet.
The Setup:
I have a Stored Procedure in Sql Server 2000 which has a text parameter called @Xml. I send in an Xml document to process. This document contains several "records" to process. The format of the xml really isn't important.
I create a temporary table called #Results to hold the results of processing each record in the xml.
To process the xml I have a Cursor which loops over a SELECT from the xml.
For each record, the sproc attempts to make a series of INSERTs and UPDATEs inside of a transaction. Any one of these commands may fail because of constraint violations or attempts to insert NULL into non-null columns, or such. After each command I check @@ERROR. If it is not zero, I stop processing the record and rollback the transaction. The cursor loops around and tries the next record. Each time the success or failure of the transaction is recorded into the #Results table.
When the cursor is done looping I 'SELECT * FROM #Results'.
I've tested this many times in the Query Analyzer and each time, regardless of any errors, I can see the result set from the SELECT of the #Results table in the Grids tab. The Messages tab shows each of the errors that occurred.
I try to call this stored procedure using the following code:
The value of c will always be zero, if there were any errors during the execution of the stored procedure. The DataSet does not get filled, even though the stored procedure is returning a result set. This is a problem for me because I expect errors to occur, and I need to know which records from the Xml caused those errors.
Is there any way to clear the errors in my stored procedure so that they don't turn into exceptions in my code? Or, is there anyway to get the Adapter to fill the DataSet regardless of any errors that were encountered?
I've also tried this with a SqlDataReader. The reader never gets assigned to because SqlCommand.ExecuteReader() throws an exception.
I have an SSIS package that fires an OnTaskFailed event whenever any of my tasks fail. I would like to put any SSIS task failure message into a user defined variable. Any idea how to do this? Any help appreciated. Regards, Paul.
I am running into a null reference exception on replication with SQL Server 2005 Compact Edition. The problem occurs at repl.synchronize(). Even though the statement is inside a try-catch block, the exception is not caught, and the application quits.
Device O/S: Windows Mobile 5.0 v 5.1.195 Build 14957.2.3.1 Hardware: Dell Axim X51v, Intel PXA270 Database: SQL 2005 SP2 (no post SP2-patches)
Exception shows as "an unexpected error has occured in AppName.exe. Select Quit and then restart this program, or select Details for more information." Details: .. at NullReferenceException at AppName.Form1.Synch() .. at .... (various lines) .. at AppName.Form1.Main()
The application is based on the tutorial (Creating a Mobile Application with SQL Server Compact Edition) at http://msdn2.microsoft.com/en-us/library/ms171908.aspx.
The application was successfully working initially but then stopped working on replication after some minor modifications which were not related to the replication code, e.g. adding some buttons to the form.
Three things are puzzling me here..
1. The fact that this is a NullReferenceException and not a SqlCeException 2. Why the try-catch block doesn't work 3. Why the exception occurs
Hi all, Suppose I have set a CHECK constraint to a column where Salary field is not permitted to be less than 1000 or greater than 10000. In this situation, when I insert a new record with salary as 10, using a stored procedure from Visual Studio, how will I trap the error from C# Code? Thanks Tomy
Hi , I have a database that records the users entrance to a building.The dates are recorded also .I have written some codes that to detect the period of dates that the person has entered.Lets say that a person named as jhon has entered the building on the days 02/08/2006 and 05/08/2006 and 11/08/2006 .Those dates are formated as dd/mm/yyyy . So that john has entered building for 3 times and the periods for the dates are one after another entrance is 3 days + 6 days =9 days .If you divide 9 by 3 we get the arithmetic average number 3 .So that we can say as john visits this building every 3 days and we can guess the next day that he may come. I have coded this and works great .I will paste the codes to the end of my message.But a master programmer friend of mine has said that I should have get this result by without writing code , by just using sql database .But some kind of stored procedure I mustn't use.So that I thought it can be done by views in sqldb .By using sql server enterprise manager I tried to use views but I could not succees. Can anyone guess this ? Here are my vb codes ... database data types : entry_id : int , identity user_id : int movie : nvarchar (50) dateenter : datetime stored procedure that selects the data from db: CREATE PROCEDURE veri_al ( @user_id int ) AS SELECT entry_id ,user_id, movie, dateenter from uye_aktiviteleri where (user_id=@user_id) ORDER BY entry_id ASC GO Code:Dim conn As New SqlClient.SqlConnection conn.ConnectionString = "data source=localhost;initial catalog=AFM;uid=nusret;pwd=araz" Dim command As New SqlClient.SqlCommand command.CommandText = "[veri_al]" command.CommandType = CommandType.StoredProcedure command.Connection = conn command.Parameters.Add("@user_id", SqlDbType.Int, 4).Value = Val(TextBox1.Text) If Not IsNumeric(TextBox1.Text) Then Exit Sub End If Try Dim adapter As New SqlClient.SqlDataAdapter adapter.SelectCommand = command Dim ds As New DataSet adapter.Fill(ds, "uyeler") DataGrid1.DataSource = ds.Tables("uyeler") Dim recordcount As Integer 'Found the recordcount recordcount = ds.Tables("uyeler").Rows.Count Dim mydatarow_ilk As DataRow Dim mydatarow_son As DataRow 'in stored procedure I used ORDER BY entry_id ASC so that first datarow 'will always be the first visit date and the last record of the 'datarow will be the last visit date mydatarow_first= ds.Tables("uyeler").Rows(0) mydatarow_last = ds.Tables("uyeler").Rows(recordcount - 1) Dim first_date As String Dim last_date As String first_date = mydatarow_first("dateenter") last_date =mydatarow_last("dateenter") Dim average_ As Integer 'What is the aveare of the visits ortalama = DateDiff("d", first_date , last_date ) / recordcount Label2.Text = "Member had visited for " & recordcount & "times" Label3.Text = "by average he/she comes here " & ortalama & " day to another." Dim last_time_visit As Integer last_time_visit = DateDiff("d", last-date , DateTime.Today()) Label4.Text = "Until the last visit it had been" & last_time_visit & " days" Label5.Text = "Guess for the next visit is" & DateAdd("d", average_ , last_date) ' MsgBox("average visits" & ortalama & " days" ) Catch z As Exception MsgBox("error : " & z.Message()) Finally MsgBox("Successfully calculated") End Try End Sub
Hi,I have a table of Locations around the country. My system produces reportsbased on these Locations. I also have a table containing Brick Codes e.g.Brick Post CodeAB51 AB51AB52 AB52AB55 AB55AB56 AB56AL01 AL1AL02 AL2AL03 AL3AL04 AL4How can I compare the first 3 or 4 letters of the postcode in the Locationstable to the corresponding entry in the Brick Codes table so I can add it tomy report?Thanks for your help
How can I test sql codes and don't want to connect to company database? My company is using sql server 2000. Below is the test codes which create a simple table, calculate and just want to check before writing more complicated codes.
create table EquipmentUptime ( equipmentId int not null ,transactionId int not null ,dateOccured datetime not null ) go -- insert into EquipmentUptime values (1,200,'01/01/2007') insert into EquipmentUptime values (2,200,'01/01/2007') insert into EquipmentUptime values (3,200,'01/01/2007') insert into EquipmentUptime values (2,100,'02/12/2007') insert into EquipmentUptime values (1,100,'02/25/2007') insert into EquipmentUptime values (3,100,'03/10/2007') insert into EquipmentUptime values (1,200,'03/14/2007') go -- select equipmentId ,100*(cast((cast(sum(UpDays)as decimal(6,3))/cast(365 as decimal(6,3))) as decimal(4,3))) 'PctUpTime' from ( select c.equipmentId ,datediff(dd,c.[UpDate],c.DownDate) 'UpDays' from ( select a.equipmentId ,a.dateOccured as 'UpDate' ,coalesce( (select top 1 b.dateOccured from EquipmentUptime b where transactionId=100 and b.equipmentId=a.equipmentId and b.dateOccured >= a.dateOccured order by b.dateOccured asc ),'01/01/2008') as 'DownDate' from EquipmentUptime a where a.transactionId=200 ) c ) d group by d.equipmentId
I have a table with a primary key, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do, automatically incrementing as fields are inserted.
I apologize for the length of this message, but I think I need to include all this info so that the problem is understood. I am having what appears to be a problem capturing the return code from a failed BCP.
I create a stored proc to use BCP to load a table:
create procedure sp_bcp_load as declare @RC int execute @RC = master..xp_cmdshell "bcp JON..W4KPV in e:inetpubftprootfinreslaw4kpv.g4000.data /Sdbmtss1 /m 0 /f d:mssqluserdatafinresW4KPV.fmt /Usa /P /e d:mssqluserdatafinrescp1.err /t""|"" /r "
select 'Return code from bcp = ', @RC if @RC <> 0 BEGIN print 'BCP Error.' return (8) END GO
If I execute the SP, and encounter a transaction log full error, the return code is still zero:
1000 rows sent to SQL Server. 45000 total 1000 rows sent to SQL Server. 46000 total Msg 1105, Level 17, State 2: Server 'DBMTSS1', Line 1:
Can't allocate space for object 'Syslogs' in database 'Jon' because the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
(54 row(s) affected)
----------------------- ----------- Return code from bcp = 0
If I execute the SP again, it correctly returns a non-zero value:
Msg 1105, Level 17, State 2: Server 'DBMTSS1', Line 1: Can't allocate space for object 'Syslogs' in database 'Jon' because the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
(6 row(s) affected)
----------------------- ----------- Return code from bcp = 1
(1 row(s) affected)
BCP Error.
Does anybody have an idea why this behaves this way? Any suggestions on how to trap an error on the first call?
hey all i am stuck with this little problem I have a table with people's names and addresses and i have one column for the zip codes. sometimes it includes 5 digit zip codes like '70820' and some times it includes all nine digits like '70820-4565'
is there anyway to move the last 4 digits of the long zip codes into a new column? and remove the dash?
I am posting this information simply as an FYI. This is in reference to the MS-SQL command "CONVERT"
I spent over 2 hours :rolleyes: screwing around trying to find out different ways of formatting dates from MS-SQL into something that makes sense for what I needed. I googled everything I could think of and found multiple references that said the info is available on MSDN.. but I could not find it. What I did find were thousands of relatively useless references to "format codes" for converting dates, but with no references to what the different format codes would ultimately yield, or what format codes were available to use.
What I ended up doing was writing a small script to generate a list of all of the variations I could find.
Below is the script, and the output that it yielded. Now, before I get bombarded with "there is a better way" I know there probably is. But this is the way that I needed to do it this particular time. If there are technical errors in my explanation, anyone is welcome to correct them. But after 2 hours of messing with this for what should have been a super simple single .0009 second command, I am just irritated beyond belief that it had to be this complicated to find any useful information on the subject. That is why I am creating this. Hopefully it helps someone else.
The format for the MS SQL CONVERT command is :
CONVERT( length_of_output, date, format_code )
length_of_output : is exactly that . the number of characters that you want returned as your result. If you use a length of 6 you will only see the first 6 characters that are returned. I found the longest valid length to be 28 characters, but I went as high as 128 just for giggles and to see if it revealed any secrets.
date : is a valid date, I used directly the getdate() function
format_code : well.. that's the tricky part. See below.
What I did was ran a script that originally went from 1 to 20,000. It crashed at 15. Apparently the format codes are not totally sequential. So I put in an on error resume next.
What I found is that : 1) the codes are not uninterrupted sequential numbers. 2) the code output repeats every 255 3) negative numbers can be used, but its pointless. 4) useful valid codes are in the ranges of : 0-14, 20-25, 100-114, 120, 121, 126, 130 and 131 5) 0-25 typically represent "short dates" with the year being only 2 digits, but there are exceptions 6) 100 and above always returned a 4 digit year. the exception was 130 and 131, I don't know what it was trying to do.
Here is the script i ran
<% on error resume next
for iintCounter = 0 to 256 SQL = "SELECT CONVERT(CHAR(128), getdate(), " & iintCounter & " ) as TheDate" Set rsTheDateFormat = TheDatabase.Execute(SQL)
I have files with area codes that are several years old. Everything I've seen about updating area codes deals with area codes that are current and are about to split in the near future. How would I go about bringing old area codes up to date?
I am working with a table that has zip codes listed in lengths of 9,8,5 and 4 digits. The table is created this way and I have no way of changing the data outside of SQL. I am trying to get the last four digits off of all the zip codes so that I only have to work with zip codes in lengths of 5 and 4
