sql-server-2000 – Tarik Billa

How to add a column with a default value to an existing table in SQL Server?

January 9, 2024 by Tarik

Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} WITH VALUES Example: ALTER TABLE SomeTable ADD SomeCol Bit NULL –Or NOT NULL. CONSTRAINT D_SomeTable_SomeCol –When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)–Optional Default-Constraint. WITH VALUES –Add if Column is Nullable and you want the Default Value for Existing Records. Notes: … Read more

TSQL – Is it possible to define the sort order?

December 31, 2023 by Tarik

It’s incredibly clunky, but you can use a CASE statement for ordering: SELECT * FROM Blah ORDER BY CASE MyColumn WHEN ‘orange’ THEN 1 WHEN ‘apple’ THEN 2 WHEN ‘strawberry’ THEN 3 END Alternately, you can create a secondary table which contains the sort field and a sort order. TargetValue SortOrder orange 1 apple 2 … Read more

How do you deal with transport-level errors in SqlConnection?

December 17, 2023 by Tarik

I posted an answer on another question on another topic that might have some use here. That answer involved SMB connections, not SQL. However it was identical in that it involved a low-level transport error. What we found was that in a heavy load situation, it was fairly easy for the remote server to time … Read more

Restore a SQL Server 2000 backup on SQL Server 2012

December 14, 2023 by Tarik

You are trying to restore a SQL Server 2000 database on SQL Server 2012. This is not supported. You will need to restore your database on an instance of SQL Server 2005, 2008 or 2008 R2 first, then back it up from there, then restore the new backup on SQL Server 2012. Microsoft explains this … Read more

t-sql replace on text field

December 2, 2023 by Tarik

Here is the sample query to update table with text column using REPLACE function. Hope this is useful for you. UPDATE <Table> set textcolumn= REPLACE(SUBSTRING(textcolumn,1,DATALENGTH(textcolumn)),’findtext’,’replacetext’) WHERE <Condition>

SQL update query syntax with inner join

December 2, 2023 by Tarik

The SET needs to come before the FROM\JOIN\WHERE portion of the query. UPDATE CE SET sJobNumber = AD.JobNumber FROM CostEntry CE INNER JOIN ActiveCostDetails As AD ON CE.lUniqueID = AD.UniqueID WHERE CE.SEmployeeCode=”002″ AND SubString(CostCentre, 1, 1) = sDepartmentCode AND substring(CostCentre, 3, 1) = sCategoryCode AND substring(CostCentre, 5, 2) = sOperationCode

WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value

September 16, 2023 by Tarik

Here is how you can solve this using a single WHERE clause: WHERE (@myParm = value1 AND MyColumn IS NULL) OR (@myParm = value2 AND MyColumn IS NOT NULL) OR (@myParm = value3) A naïve usage of the CASE statement does not work, by this I mean the following: SELECT Field1, Field2 FROM MyTable WHERE … Read more

T-SQL List Tables, Columns

September 16, 2023 by Tarik

Please check out the information schema. select * from MyDatabaseName.information_schema.columns order by table_name, ordinal_position

What exactly does the T-SQL “LineNo” reserved word do?

August 14, 2023 by Tarik

OK, this is completely undocumented, and I had to figure it out via trial and error, but it sets the line number for error reporting. For example: LINENO 25 SELECT * FROM NON_EXISTENT_TABLE The above will give you an error message, indicating an error at line 27 (instead of 3, if you convert the LINENO … Read more

What is the syntax to drop a Stored Procedure in SQL Server 2000?

July 30, 2023 by Tarik

Microsoft recommended using the object_id() function, like so: IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[YourProcedure]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1) DROP PROCEDURE [dbo].[YourProcedure] GO . object_id() helps resolve owner conflicts. If you do SELECT name FROM sysobjects WHERE name=”my_procedure” , you may see many different procedures with the same name — all … Read more