When To Use LOWER/UPPER for comparing data in SQL Server – Alternatives

In SQL Server LOWER Can be used to convert uppercase/mixedcase string to lower case string. More about LOWER can be found here

Similarly, UPPER Can be used to convert lowercase/mixedcase string to upper case string. More about UPPER can be found here

Example:
[sql]
DECLARE @String VARCHAR(10)

SET @String = ‘ABcDeFGH’

SELECT @String STRING,LOWER(@String) LowerCaseSrting
[/sql]

Output:
[text]
STRING LowerCaseSrting
———- —————
ABcDeFGH abcdefgh

(1 row(s) affected)
[/text]

Comparing UpperCase and LowerCase strings
[sql]
DECLARE @String VARCHAR(10),@String1 VARCHAR(10)
SET @String = ‘ABcDeFGH’
SET @String1 = ‘abcdefgh’

IF @String = @String1
PRINT ‘Equal’
ELSE
PRINT ‘Not Equal’
[/sql]

Output:
[text]
Equal
[/text]
SQL Server by default creates databases with case insensitive collation. Hence we are getting output as Equal.

Now if we create a database in case sensitive collation and try the above query, we can notice that we get Not Equal as output.

Script:
[sql]
CREATE DATABASE testLower COLLATE SQL_Latin1_General_CP1_CS_AS
GO

USE testLower
GO

DECLARE @String VARCHAR(10),@String1 VARCHAR(10)
SET @String = ‘ABcDeFGH’
SET @String1 = ‘abcdefgh’

IF @String = @String1
PRINT ‘Equal’
ELSE
PRINT ‘Not Equal’

[/sql]

Output:
[text]
Not Equal
[/text]

The comparison will also fail when the tables are created with columns having case sensitive collation.

Script:
[sql]
CREATE TABLE TestTable(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS)
GO
CREATE TABLE ReferTable(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS)
GO

INSERT INTO TestTable(Value)
SELECT ‘lowercase’
UNION ALL
SELECT ‘LowerCase’
UNION ALL
SELECT ‘LOWERCASE’
GO

INSERT INTO ReferTable(Value)
SELECT ‘lowercase’
GO

–This query gives all three rows
–No need of lower
SELECT * FROM TestTable T JOIN ReferTable R
ON T.Value = R.Value

———————————————————
CREATE TABLE TestTable1(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS)
GO
CREATE TABLE ReferTable1(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS)
GO

INSERT INTO TestTable1(Value)
SELECT ‘lowercase’
UNION ALL
SELECT ‘LowerCase’
UNION ALL
SELECT ‘LOWERCASE’
GO

INSERT INTO ReferTable1(Value)
SELECT ‘lowercase’
GO

–This query gives only one row
SELECT * FROM TestTable1 T JOIN ReferTable1 R
ON T.Value = R.Value
[/sql]

In such scenarios as discussed above, we have to specify LOWER along with the column names to compare UpperCase with Lower Case.

[sql]
–Needs Lower to give all the tree rows
SELECT * FROM TestTable1 T JOIN ReferTable1 R
ON LOWER(T.Value) = LOWER(R.Value)
[/sql]

Observations:
LOWER Can be ignored if the database and all the table columns that are specified in the query are in CASE INSENSITIVE Collation.

Alternatives:
We can specify the CASE INSENSITIVE Collation name with the column name to compare the column using Case insensitive Collation.
[sql]
–Alternative to LOWER/UPPER
–Using CASE InSensitive Collation names in the query
SELECT * FROM TestTable1 T JOIN ReferTable1 R
ON T.Value COLLATE SQL_Latin1_General_CP1_CI_AS =
R.Value COLLATE SQL_Latin1_General_CP1_CI_AS
[/sql]

Clean Up:
[sql]
USE master
GO

DROP DATABASE testLower
[/sql]

How to get number of rows in a table without using count function

How to get number of rows in a table without using count function?

In SQL Server you can get the approximate number of rows in the table using the below sample script.

[sql]
USE AdventureWorks
GO
DECLARE @tablename AS sysname = ‘Production.Product’;

DBCC UPDATEUSAGE (0, @tablename)
WITH NO_INFOMSGS;

SELECT [ROWS]
FROM SYS.indexes AS IND
INNER JOIN
SYS.partitions AS PRT
ON IND.object_id = PRT.object_id
AND IND.index_id = PRT.index_id
WHERE IND.object_id = OBJECT_ID(@tablename)
AND IND.index_id <= 1;
[/sql]

This script uses sys.partitions to fetch the number of rows in the table.
More about sys.partitions can be found here http://msdn.microsoft.com/en-us/library/ms175012(v=sql.105).aspx

Deadlock Internals with Real Time Scenario – Explanation and Resolution

What is deadlock?
A deadlock is a situation in which two or more competing actions are each waiting for the other to finish, and thus neither ever does.
Refer: http://en.wikipedia.org/wiki/Deadlock

Scenario:
Data Preparation Script: (Script-a)

[sql]
–Create Sample Database
CREATE DATABASE SampleDeadLockDatabase
GO

USE SampleDeadLockDatabase
GO

–Create Sample Tables
CREATE TABLE SampleTable1(ID INT IDENTITY(1,1),VALUE UNIQUEIDENTIFIER)
GO

CREATE TABLE SampleTable2(ID INT IDENTITY(1,1),VALUE UNIQUEIDENTIFIER)
GO

–Populate Sample tables
INSERT INTO SampleTable1(VALUE)
SELECT NEWID()
GO 10

INSERT INTO SampleTable2(VALUE)
SELECT NEWID()
GO 10

–Retreive data for verification
SELECT * FROM SampleTable1
SELECT * FROM SampleTable2
[/sql]

Transaction1 Script: (Script-b)

[sql]
Select @@SPID — Note down this value here (68)
——————Transaction1——————-
–Step1 Begins–
USE SampleDeadLockDatabase
GO
BEGIN TRAN

UPDATE SampleTable1
SET VALUE = ‘1DF1EBE1-322E-4698-A5BD-2E2C6D85AEB6’
WHERE ID=1
–Step1 Ends–

—RUN Step2 in Transaction2

–Step3 Begins–
SELECT * FROM SampleTable2 WHERE ID=4

ROLLBACK TRAN
–Step3 Ends–
——————Transaction1——————-

[/sql]

Transaction2 Script: (Script-c)

[sql]
Select @@SPID — Note down this value here — 65
——————Transaction2——————-
–Step2 Begins–
USE SampleDeadLockDatabase
GO
BEGIN TRAN

UPDATE SampleTable2
SET VALUE = ‘23154CCE-0919-4D3A-92EB-E6B4062F8715’
WHERE ID=3
–Step2 Ends–

–Step4 Begins–
SELECT * FROM SampleTable1 WHERE ID=5

ROLLBACK TRAN
–Step4 Ends–
——————Transaction2——————-
[/sql]

Data Preparation:
In SQL Server Management Studio, Click on new query
Paste the Data Preparation Script, and execute it.

Dead Lock Scenario Creation:
Paste the two code snippets transaction1 and transaction 2 in separate query windows.
In each of them highlight the code (SELECT @@SPID) and run it to note down the SPID
Now run the Step1 in transaction1
This step updates SampleTable1 with a new value for id 1
Now run the Step2 in transaction2
This step updates SampleTable2 with a new value for id 3
Now run the Step3 in transaction1
This step selects the values of SampleTable2 with id 4
You will notice that this step3 is still executing. Why? We will analyze this later in the post.
Now run the Step4 in transaction2
This step selects the values of SampleTable1 with id 5
You will notice that this step executes for few seconds and completes. Also one of the session (Transaction1 or Transaction2 will be deadlocked.) with the errormessage


Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Analysis:
Why Step3 was waiting (Blocked?)
So now let us replicate the scenario again, by running the above steps1 and 2.
In the new query window (again a new Query window 🙂 ) Run the below script to understand what locks are placed on the tables pages and rows.

Script-d
[sql]
–Update this id with the @@spid of transaction1
SELECT resource_type,
resource_description,
resource_associated_entity_id,
request_mode,
request_type,
request_status FROM sys.dm_tran_locks
where request_session_id = 68

–Update this id with the @@spid of transaction2
SELECT resource_type,
resource_description,
resource_associated_entity_id,
request_mode,
request_type,
request_status FROM sys.dm_tran_locks
where request_session_id = 65

–Fetch the partition_id of the table
select p.partition_id,t.name,t.object_id From sys.partitions p join sys.tables t
on t.object_id = p.object_id
where t.name in (‘SampleTable1′,’SampleTable2’)
[/sql]

Update the values above with the respective @@spud’s that we noted down earlier. (SPID’s of the respective queries)
Output:

Table Locks

When you execute this you can find that each session has created exclusive lock (X) on the row level and intent exclusive (IX) locks on the page and table level.
Let’s find what exactly those rows are.
Copy the resource_description values for resource_type RID to the below script and run it.

Script-e:
[sql]
select * From SampleTable1 (nolock) where %%lockres%% = ‘1:154:0’
select * From SampleTable2 (nolock) where %%lockres%% = ‘1:156:2’
[/sql]

This would output the rows that we updated in respective transactions.

Lockres Output

Now run the step3 in transaction1, and run the script-d pasted above.
Output:
Shared Lock Waiting

You will notice that transaction1 now has the new lock for RID 1:156:2 which is in waiting state.
It’s waiting for an exclusive lock to be released on a row on SampleTable2
In order to find out the row for which transaction 1 is waiting copy the resource_description values to the below script and run it.
Script-f:
[sql]
select * From SampleTable2 (nolock) where %%lockres%% = ‘1:156:2’
[/sql]

Shared Lock Waiting For Row

So transaction1 is waiting for exclusive lock on row ID 3 to be released.
But in no way we are referring to rowid3 of transaction1., we were actually referring to row ID 4.

Why is SQL Server waiting for this row?

In order to understand this stop the step3 in transaction1 (it’s still getting executed) , select step3 code and click on Display estimated execution plan button ( or Query – Display estimated execution plan button) (or Ctrl + L )
You will find a table scans in the execution plan.

Waiting Transaction Execution Plan

So this table scan is forcing SQL Server to actually scan all the rows in the table(for ID=4) , as the ID=3 is blocked by transaction 2 it is waiting for transaction 2 to actually complete in order to take shared lock on the row and compare its value with 4.

Similarly when step4 is executed it is blocked by transaction 1, as step 4 has to do a table scan on the table SampleTable1 for ID = 5, but the transaction 1 has exclusively locked the row ID = 1.
So,
Transaction 1 is blocked by Transaction 2
AND
Transaction 2 is blocked by Transaction 1
This causes deadlock situation.
As both transactions would stay for infinite time blocking each other.
So, one of the transaction that is easier to rollback is terminated by SQL Server giving deadlock error: 1205

Resolutions:
1. Change to Snapshot Isolation.
[sql]
ALTER DATABASE SampleDeadLockDatabase
SET READ_COMMITTED_SNAPSHOT ON
[/sql]

Run the above query after disconnecting all the queries connected to SampleDeadLockDatabase
Now if you run the above Dead Lock Scenario steps you will notice that the deadlock is not occurring now.

2.Create index on the table so that there will be alternates for Table Scan.
Before testing the below approach remove snapshot isolation

[sql]
ALTER DATABASE SampleDeadLockDatabase
SET READ_COMMITTED_SNAPSHOT OFF
[/sql]

Script to Create clustered indexes on tables.
[sql]
ALTER TABLE SampleTable1
ADD CONSTRAINT SampleTable1_PK PRIMARY KEY (ID)
GO

ALTER TABLE SampleTable2
ADD CONSTRAINT SampleTable2_PK PRIMARY KEY (ID)
[/sql]

Now if you run the above Dead Lock Scenario steps you will notice that the deadlock is not occurring now. But you can reproduce dead lock if you force SQL Server to do an index /table scan instead of index seek. Using WITH (INDEX (0)) hint in Step 3 and Step 4.

[sql]
–Step3 Begins–
SELECT * FROM SampleTable2 WITH (INDEX(0)) WHERE ID=4

ROLLBACK TRAN
–Step3 Ends—

–Step4 Begins–
SELECT * FROM SampleTable1 WITH (INDEX(0)) WHERE ID=3

ROLLBACK TRAN
–Step4 Ends–
[/sql]

SSMS ISSUE: Connection information does not change in SSMS when a new Query is created from Dedicated Administrator Connection

Description:
Connection information does not change in SQL Server Management Studio for SQL Server 2008 r2 when a new Query is created from Dedicated Administrator Connection.

Steps:
1. Open SQL Server management studio and connect to SQL Server in Dedicated Administrator Connection.
In order to connect to SQL Server in Dedicated Administrator Connection (DAC) you have to add ADMIN: before the SERVER\INSTANCE name.

Dedicated Administrator Connection

2. After connecting the the Server with DAC click on new query.

3. You will get the error that you cannot connect as you are already connected to the server with DAC and only one DAC Connection is allowed.

[text]
TITLE: Connect to Database Engine
——————————

Cannot connect to ADMIN:..

——————————
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 – An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error: 10053)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10053&LinkId=20476

——————————
BUTTONS:

OK
——————————
[/text]

4. Now in the error message popup click OK and change the connection to any other SQL Server.

5. Now you will find that the connection details displayed when you keep the mouse pointer over the SQL Server Query are still showing that of the DAC.

BUG SSMS DAC

How To View Data in Hidden System Tables.

SQL Server has System base tables which are hidden cannot be directly queried.

For example:

[sql]
SELECT * FROM sys.sysrscols
[/sql]

Output:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.sysrscols’.

In order to view data from the hidden System base tables you have to connect to SQL Server using Dedicated Administrator Connection.

In order to connect using Dedicated Administrator Connection, Add ADMIN: before the name of the server\instance in SQL Server Management studio.

Dedicated Administrator Connection

Now you can query all the System tables. 🙂

[sql]

——————————————————————————————————————————————————
SELECT * FROM sys.sysrscols
GO
SELECT * FROM sys.sysrowsets
GO
SELECT * FROM sys.sysallocunits
GO
SELECT * FROM sys.sysfiles1
GO
SELECT * FROM sys.syspriorities
GO
SELECT * FROM sys.sysdbfrag
GO
SELECT * FROM sys.sysfgfrag
GO
SELECT * FROM sys.syspru
GO
SELECT * FROM sys.sysbrickfiles
GO
SELECT * FROM sys.sysphfg
GO
SELECT * FROM sys.sysprufiles
GO
SELECT * FROM sys.sysftinds
GO
SELECT * FROM sys.sysowners
GO
SELECT * FROM sys.sysdbreg
GO
SELECT * FROM sys.sysprivs
GO
SELECT * FROM sys.sysschobjs
GO
SELECT * FROM sys.syslogshippers
GO
SELECT * FROM sys.syscolpars
GO
SELECT * FROM sys.sysxlgns
GO
SELECT * FROM sys.sysxsrvs
GO
SELECT * FROM sys.sysnsobjs
GO
SELECT * FROM sys.sysusermsgs
GO
SELECT * FROM sys.syscerts
GO
SELECT * FROM sys.sysrmtlgns
GO
SELECT * FROM sys.syslnklgns
GO
SELECT * FROM sys.sysxprops
GO
SELECT * FROM sys.sysscalartypes
GO
SELECT * FROM sys.systypedsubobjs
GO
SELECT * FROM sys.sysidxstats
GO
SELECT * FROM sys.sysiscols
GO
SELECT * FROM sys.sysendpts
GO
SELECT * FROM sys.syswebmethods
GO
SELECT * FROM sys.sysbinobjs
GO
SELECT * FROM sys.sysaudacts
GO
SELECT * FROM sys.sysobjvalues
GO
SELECT * FROM sys.sysclsobjs
GO
SELECT * FROM sys.sysrowsetrefs
GO
SELECT * FROM sys.sysremsvcbinds
GO
SELECT * FROM sys.sysxmitqueue
GO
SELECT * FROM sys.sysrts
GO
SELECT * FROM sys.sysconvgroup
GO
SELECT * FROM sys.sysdesend
GO
SELECT * FROM sys.sysdercv
GO
SELECT * FROM sys.syssingleobjrefs
GO
SELECT * FROM sys.sysmultiobjrefs
GO
SELECT * FROM sys.sysguidrefs
GO
SELECT * FROM sys.syschildinsts
GO
SELECT * FROM sys.syscompfragments
GO
SELECT * FROM sys.sysftstops
GO
SELECT * FROM sys.sysqnames
GO
SELECT * FROM sys.sysxmlcomponent
GO
SELECT * FROM sys.sysxmlfacet
GO
SELECT * FROM sys.sysxmlplacement
GO
SELECT * FROM sys.sysobjkeycrypts
GO
SELECT * FROM sys.sysasymkeys
GO
SELECT * FROM sys.syssqlguides
GO
SELECT * FROM sys.sysbinsubobjs
GO
SELECT * FROM sys.syssoftobjrefs
GO
[/sql]

Impact of TEMPDB Collations on Other Databases

When you create databases with different collations then you have to be very careful when using the temporary tables.

In the below script let us analyze such a scenario.

Creating Sample database with case sensitive collation
[sql]
CREATE DATABASE TESTCOLLATION
COLLATE SQL_Latin1_General_CP1_CS_AS ;
GO
[/sql]

Let us create a sample table in a database and insert data.
[sql]
USE TESTCOLLATION
GO

CREATE TABLE TestTable ( NAME VARCHAR(100))
GO

INSERT INTO TestTable
SELECT ‘TestName’

GO

[/sql]

In the below queries let us query the table.
[sql]
USE TESTCOLLATION
GO
SELECT * FROM TestTable

SELECT * FROM TestTable where NAME = ‘TestName’

SELECT * FROM TestTable where NAME = ‘testname’

GO
[/sql]

output:
[text]
NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-

(0 row(s) affected)
[/text]

It can be found that the third select statement above does not return any rows as the database and the columns in the table are with case sensitive collation.

Now let us create a temp table with same data as above and check the results.

[sql]
USE TESTCOLLATION
GO

CREATE TABLE #TestTable ( NAME VARCHAR(100))
GO

INSERT INTO #TestTable
SELECT ‘TestName’

GO

SELECT * FROM #TestTable

SELECT * FROM #TestTable where NAME = ‘TestName’

SELECT * FROM #TestTable where NAME = ‘testname’
[/sql]

Output:
[text]

(1 row(s) affected)
NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

[/text]

It can be found that the third select statement above gives ‘TestName’ as output.

So when we go with an assumption that the temporary tables have the same collation as the database in which it is created then the code would fail.

Resolution:
[sql]
SELECT * FROM #TestTable WHERE NAME collate SQL_Latin1_General_CP1_CS_AS = ‘TestName’

SELECT * FROM #TestTable WHERE NAME collate SQL_Latin1_General_CP1_CS_AS = ‘testname’
[/sql]

Output:
[text]
NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-

(0 row(s) affected)

[/text]

Cleanup:
[sql]
DROP DATABASE TESTCOLLATION
GO
[/sql]

Interesting Observation – Declaring Table Variables impact on Tempdb

Following was an intresting observation when creating table variables.
1. Table variables are created in tempdb.
2. Table variables are created in tempdb even before the declare table is called.

Steps to find that the Table variables are created in tempdb:
1. Restart your local SQL Server instance. (So that the tempdb gets cleared)
2. Connect to the SQL Server in SQL Server management studio and check if there are any records in sys.objects in tempdb.
[sql]
SELECT * FROM tempdb.sys.tables
[/sql]
You will find there are 0 records(ideally)
3. In a new query window run the below script.
[sql]
USE MASTER
GO

DECLARE @TableVariable TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @TableVariable
select 1,’A’

WAITFOR DELAY ’00:02:00′
[/sql]
4. While the above code is getting executed run the below script in other query window.
[sql]
SELECT * FROM tempdb.sys.tables
–Take objectid from the above resultset
SELECT * FROM tempdb.sys.columns where OBJECT_ID = 117575457
[/sql]

Result: It can be found that the tempdb.sys.tables and tempdb.sys.columns has records for the table variable. This proves that the table variable is created in tempdb.

Steps to find that the Table variables are created in tempdb even before the declare table is called:
1. Restart your local SQL Server instance. (So that the tempdb gets cleared)
2. Connect to the SQL Server in SQL Server management studio and check if there are any records in sys.objects in tempdb.
[sql]
SELECT * FROM tempdb.sys.tables
[/sql]
You will find there are 0 records(ideally)
3.In a new query window run the below script.

[sql]
SELECT * FROM tempdb.sys.tables

DECLARE @TableVariable TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @TableVariable
select 1,’A’
[/sql]

Result: It can be found that there is a record in the tempdb.sys.tables even before the DECLARE Statement is executed. This proves that the table variables are created in tempdb even before the declare table is called.

Wishing all the blog readers advanced happy new year. 🙂

SSMS Available Databases DropDown Acts weird if Connection Properties Are Changed

SQL Server Management Studio (SSMS) Has a available databases dropdown ( to select a database to execute the query.).

Available Databases Dropdown in SSMS

This dropdown behaves weird when you change the connection properties.

Observation 1:
Run the below query in the query window
[sql]
SET PARSEONLY ON
[/sql]

Now when you try to expand the dropdown, the drop down does not expand.

Run the below query to turn off PARSEONLY
[sql]
SET PARSEONLY OFF
[/sql]

Observation 2:

Run the below query in the query window
[sql]
SET ROWCOUNT 2
[/sql]

Now if you try to click on the dropdown, you will find only 2 available databases.

Run the below query to reset ROWCOUNT to 0
[sql]
SET ROWCOUNT 0
[/sql]

Why is the dropdown behaving this way?
SSMS is using the same connection used by the query to fetch the Available databases list.
SSMS runs the below command to fetch the results for the Availabale Databases DropDown, using the same connection used by the Query.
[sql]
SELECT dtb.name AS [Name], dtb.state AS [State] FROM master.sys.databases dtb
[/sql]

Also, You would also get wrong results count into the @@ROWCOUNT, when you run the query and accidentally touch the availabale databases dropdown.

Observation 3:
Run the below query
[sql]
SELECT * FROM SYS.OBJECTS
SELECT @@ROWCOUNT as ROWSCOUNT
[/sql]
Make a note of the ROWSCOUNT Result.

Now run the query
[sql]
SELECT * FROM SYS.OBJECTS
[/sql]

Click on the available databases dropdown and run the below query.

[sql]
SELECT @@ROWCOUNT as ROWSCOUNT
[/sql]

You can identify the difference in the values of @@ROWCOUNT.

The first result would be the number of objects in the database.
The second result would be the number of databases in the Server.

‘Ӈ耇’ was found in the text of this event

When you are running a SQL Server profiler and the SQL Server Service it is connected to got Stopped/Restarted, then you get the below message in the TextData in SQL Profiler.
[sql]
— ‘Ӈ耇’ was found in the text of this event.
— The text has been replaced with this comment for security reasons.
[/sql]

TSQL Script to remove TAB, LINE FEED and CARRIAGE RETURN

The below script removes the TAB(Horozontal Tab), Line feed(New line), Carriage Return Characters in a variable @String

[sql]
SET NOCOUNT ON
DECLARE @String VARCHAR(100)
DECLARE @CorrectedString VARCHAR(100)

SELECT @String = ‘AB C
D’

PRINT @String

SELECT @CorrectedString = REPLACE(@String, CHAR(9),”)
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),”)
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),”)
PRINT @CorrectedString
[/sql]

Extended script which also provides length of the string:
[sql]
SET NOCOUNT ON
DECLARE @String VARCHAR(100)
DECLARE @CorrectedString VARCHAR(100)

SELECT @String = ‘AB C
D’

PRINT @String
PRINT ‘LENGTH=’+CAST(LEN(@String) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@String, CHAR(9),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))
[/sql]