SQL server – ISNUMERIC and checking for valid numeric tyopes

During a large ETL process from  a staging table  , UPDATE was creating an error on a CONVERT.

Msg 245, Sev 16, State 1, Line 17 : Conversion failed when converting the nvarchar value '慬浩ų㈁　　㄀　㈀　㄀ĀĀ畁桴牯摥䈁獵湩獥⁳癅湥Ŵ潃浭牥楣污䤠敲慬摮ā䤁呃䥓㐳㌱㠵Ķ昶㐱挴㠹戭㕣ⵥㄴて戭㔶ⴰ挶㠱挴敥户晦ਁ1217' to data type int. [SQLSTATE 22018]

 

It was necessary to create a step , where values could be checked , if they were a valid numeric type.  ISNUMERIC returns 1 if it’s a valid numeric data type ( int, bigint, smallint, tinyint, decimal,numeric,money,smallmoney,float,real)

 

SELECT  unique_id, created_by ,handler_id ,team_id 
FROM
dbo.stage_table WHERE  isnumeric(rtrim(created_by)) = 0
OR isnumeric(rtrim(handler_id)) = 0 
OR  (isnumeric(rtrim(team_id )) = 0 

See Also

Arithmetic overflow error and isdate sql

Arithmetic overflow error and isdate sql

Executing a t-sql statement with a convert function

 convert(datetime, my_date_column)            ,           

 threw an arithmetical overflow error with the message :

Message
 Arithmetic overflow error converting expression to data type datetime. [SQLSTATE 22003] (Error 8115)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

 

Use  the ISDATE function  , to identify invalid dates (or valid dates). Clean up as necessary.

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

select seq_number
from   dbo.STAGING_TABLE 
where  ISDATE(my_date_column ) = 0
and  my_date_column  

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Quotename in SQL

Quotename() is a SQL Server String function.

It accepts an input string of of up to 128 characters. Which is a limitation , although you could easily create a workaround with a UDF

Returns a Unicode string  of a valid delimited identifier.

 Why would you use Quotename()?

  1)   Reviewing for SQL Injection Attacks

 2)    Dynamic statements  for object management , such as ALTER SCHEMA

 3)    Tokens in T-SQL job step scripts

 4)   Quote object name

Examples

 

select quotename('sqlserver-dba.com') -- [sqlserver-dba.com]

select quotename('SQL [Server] DBA') -- [SQL [Server]] DBA]

select quotename('sqlserver-dba.com', '''') -- 'sqlserver-dba.com'

select quotename('sqlserver-dba.com', '"') -- "sqlserver-dba.com"

select quotename('sqlserver-dba.com''s DBA', '''') -- 'sqlserver-dba.com''s DBA'

select quotename('sqlserver-dba.com', '|') -- NULL

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server ISOLATION LEVELs ,UDF and table hints

SET TRANSACTION ISOLATION LEVEL  cannot be executed in a UDF (SQL Server function) , but  can be applied with the use of TABLE HINTS.SQL Server Locking and row versioning are central to isolation levels

 SET TRANSACTION ISOLATION LEVEL controls the  locking levels  and row versioning characteristics of DML statements.

 Locking levels and row versioning methods are used to maintain integrity of transactions.

 SQL Row versioning maintains different versions of  rows  during a transaction. During a transaction ,rows from the transaction start are available. Row versioning decreases the possibility of locking.

 Locking levels are applied at a row, page or table level. They are applied in a way that minimises problems for the current transaction. The relevant locks stop other transactions from accessing data, when the current transaction completes the locks are released.

 An application can choose an isolation level – defining the levels of access another transaction has on the current data. The choice of isolation level defines the row versioning and locking levels

 The isolation levels available are : READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SNAPSHOT,SERIALIZABLE

 Firstly, only one isolation level can be set per connection – unless changed .

 Secondly, a UDF can’t execute the SET TRANSACTION ISOLATION LEVEL. The alternative for a UDF is a table hint. The table hint overrides the optimizer choice for the duration of the DML. An example of a table hint is :

 USE MyDB

GO

SELECT col1,col2 FROM myTable WITH (TABLOCK)

WHERE col1 = ‘a_value’

GO

Locks on resources need to be managed effectively. An interesting by-product of locks is SQL Server SLEEPING MODE , locks and transactions

Although this restriction applies to a SQL Server UDF ,the TABLE HINTS offer sufficient flexibility to achieve a similar approach

Source:Jack Vamvas (http://www.sqlserver-dba.com)

OpenXML - more than 8000 characters

An excellent script to deal with XML documents that are greater than 8000 characters. This deals with the SQL Server 2000 limitation. SQL Server 2005 and beyond deasl with more than 8000 characters

Use it like :

DECLARE @hDoc int
EXEC usp_OpenXML_From_File 'c:\myXMLfile.xml', @hDoc OUTPUT

SELECT *
FROM OPENXML(@hDOC, '/rss/channel/item',2)
WITH 
(
  title varchar(200),
  link varchar(400),
  description nvarchar(2000)
  
)

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc usp_OpenXML_From_File(@strPathToFile varchar(500), @hdoc int output) as
declare @objStream int, @retval int, @filesize int, @chunknum int
declare @chunkstr nvarchar(4000), @strSQL nvarchar(4000)
declare @str1 nvarchar(4000), @str2 nvarchar(4000), @str3 nvarchar(4000), @str4 nvarchar(4000), @str5 nvarchar(4000),
     @str6 nvarchar(4000), @str7 nvarchar(4000), @str8 nvarchar(4000), @str9 nvarchar(4000), @str10 nvarchar(4000),
     @str11 nvarchar(4000), @str12 nvarchar(4000), @str13 nvarchar(4000), @str14 nvarchar(4000), @str15 nvarchar(4000),
     @str16 nvarchar(4000), @str17 nvarchar(4000), @str18 nvarchar(4000), @str19 nvarchar(4000), @str20 nvarchar(4000)
declare @ErrorSource varchar(255),  @ErrorDesc varchar(255)

-- create Stream Object from file
exec @retval = sp_OACreate 'ADODB.Stream', @objStream OUT
--open the stream object.  default stream type is text and should fit our needs
exec sp_OASetProperty @objStream, 'Type', 2 -- text(default)
exec sp_OASetProperty @objStream, 'CharSet', 'utf-8'
exec sp_OAMethod @objStream, 'Open'
--load file content into stream
exec @retval = sp_OAMethod @objStream, 'LoadFromFile', Null, @strPathToFile
IF @retval = 0 -- succesfully loaded file
begin
     -- get file size
     exec sp_OAGetProperty @objStream, 'Size', @filesize OUT
     -- you need to check file size and decide if it's too big or not
     -- depending on the number of @strN you declared : 
     -- eg with @str1 to @str20, you can handle files up to 20*3700 = 74000 characters
     if @filesize <= 74000
     begin
          set @chunknum = 1
          while @filesize > 0
          begin
               exec sp_OAMethod @objStream, 'ReadText', @chunkstr out, 3700
               set @chunkstr = replace(@chunkstr, char(39), char(39) + char(39))
               select      @str1 = isnull(case when @chunknum = 1 then @chunkstr else @str1 end,''),
                    @str2 = isnull(case when @chunknum = 2 then @chunkstr else @str2 end,''),
                    @str3 = isnull(case when @chunknum = 3 then @chunkstr else @str3 end,''),
                    @str4 = isnull(case when @chunknum = 4 then @chunkstr else @str4 end,''),
                    @str5 = isnull(case when @chunknum = 5 then @chunkstr else @str5 end,''),
                    @str6 = isnull(case when @chunknum = 6 then @chunkstr else @str6 end,''),
                    @str7 = isnull(case when @chunknum = 7 then @chunkstr else @str7 end,''),
                    @str8 = isnull(case when @chunknum = 8 then @chunkstr else @str8 end,''),
                    @str9 = isnull(case when @chunknum = 9 then @chunkstr else @str9 end,''),
                    @str10 = isnull(case when @chunknum = 10 then @chunkstr else @str10 end,''),
                    @str11 = isnull(case when @chunknum = 11 then @chunkstr else @str11 end,''),
                    @str12 = isnull(case when @chunknum = 12 then @chunkstr else @str12 end,''),
                    @str13 = isnull(case when @chunknum = 13 then @chunkstr else @str13 end,''),
                    @str14 = isnull(case when @chunknum = 14 then @chunkstr else @str14 end,''),
                    @str15 = isnull(case when @chunknum = 15 then @chunkstr else @str15 end,''),
                    @str16 = isnull(case when @chunknum = 16 then @chunkstr else @str16 end,''),
                    @str17 = isnull(case when @chunknum = 17 then @chunkstr else @str17 end,''),
                    @str18 = isnull(case when @chunknum = 18 then @chunkstr else @str18 end,''),
                    @str19 = isnull(case when @chunknum = 19 then @chunkstr else @str19 end,''),
                    @str20 = isnull(case when @chunknum = 20 then @chunkstr else @str20 end,''),
                    @chunknum = @chunknum + 1, @filesize = @filesize - 3700
          end
     end
end

exec('declare @handle int;exec sp_xml_preparedocument @handle out, ''' + @str1 + @str2 + @str3 + @str4 + @str5
 + @str6 + @str7 + @str8 + @str9 + @str10 + @str11 + @str12 + @str13 + @str14 + @str15
 + @str16 + @str17 + @str18 + @str19 + @str20+ ''';declare c1 cursor global for select @handle')
open global c1
fetch c1 into @hdoc
close global c1
deallocate global c1
exec sp_OAMethod @objStream, 'Close'
exec sp_OADestroy @objStream

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 Related Posts

SET ARITHABORT ON

Author: Jack Vamvas (http://www.sqlserver-dba.com)