Solved

SQL Type Conversion

Posted on 2013-01-06
12
266 Views
Last Modified: 2013-01-24
I have modified the code to find text in tables to what you see below.

SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE ' + @column_name + ' LIKE ''%' + @search_string + '%'') PRINT ''select * from ' + @table_name + ' where ' + @column_name + ' like ''%' + @search_string  + '%''' + ''''

I am getting this error message on the second occurence of @search_string.

Conversion failed when converting the varchar value 'select * from IV30500 where DOCNUMBR like ' to data type int.

I believe what is happening is that often the value can be all numeric so SQL converts it to a numeric type field as opposed to just treating it as all text. How do I adjust this portion to simply treat whatever is read as text? Do I need to put a case statement that looks for all digits and then converts to string while leaving any values that do have characters alone? If so, what would the syntax look like?
0
Comment
Question by:rwheeler23
12 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 38749786
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE ' + @column_name + ' LIKE ''%' + @search_string + '%'') PRINT ''select * from ' + @table_name + ' where ' + @column_name + ' like ''%' + @search_string  + '%''' + ''''

Most probably you should not get errors if all the variables used above are declared as varchar.  if there is a numeric field, you can convert as
CAST(@search_string AS VARCHAR(200))
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38749849
I think error is not for the search string
it is for sql_string


Conversion failed when converting the varchar value 'select * from IV30500 where DOCNUMBR like ' to data type int.

as it is saying failed to convert whole sql that is created to int

can share complete code with us
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38749982
Please post your complete code...
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:rwheeler23
ID: 38750752
It is the execute SQL that actuall fails. Here is the complete code. All I Am trying to do is to get thise code to produce the actual select statements I need to look for data in tables. So the end result may be lines that read

select * from MyTable where MyColumn='123456' or
select * from MyTable where MyColumn='abcdef'
FindTextInTables.txt
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38751064
I guess you are looking for script to search particular value  in whole DB...
I have developed my own code....which will perform better then cursor.. have a look and let me know if you have nay concern...

declare @search_string nvarchar(200),@sql nvarchar(max) ,@object_id bigint

set nocount on 

set @search_string='Kentucky'

Create table #temp_t
( object_id bigint default 0,
	query nvarchar(max)
 )

insert into #temp_t (object_id,query)
select s.OBJECT_ID, 'if exists ( select 1 from ' +  object_name(s.object_id) + ' where '  + s.name + ' like  ''%' + @search_string +'%'' )  '+ Char(13) + 
        'Print  ''select * from ' +  object_name(s.object_id) + ' where ' + s.name + ' like  ''''%' + @search_string + '%'''''';' 
 from sys.columns s  join  sys.objects o on s.object_id=o.object_id where o.type='U'


While @@ROWCOUNT > 0
begin
set @sql=''
set @object_id = (select top(1)  t.object_id from #temp_t t )
if @@ROWCOUNT=0
break;

select @sql= @sql + Case when @sql > '' then CHAR(13) else '' end + query from #temp_t t where t.object_id=@object_id
--print @sql
exec (@sql)

Delete from #temp_t  where object_id=@object_id 
end 



drop table #temp_t

Open in new window


Thanks,
saurabh
0
 

Author Comment

by:rwheeler23
ID: 38751283
In addition to looking for text in tables I then want to build the SQL select statements so I can actually seen the data records in each table that it is found. I have a database with 1,500 tables and those tables have relationships. I am just looking for something to build those select statements to aid in the data discovery process.

I will attempt to modify your code later today.
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38751647
Sure ...let me know if you need any help
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38751733
That code is hideously inefficient.

The code should be written such that:

1) it does not check impossible columns
2) it checks all columns for one table at the same time
0
 

Author Comment

by:rwheeler23
ID: 38751914
Just for the record, when I ran the original script it took about 30 minutes hour on an 18GB database. I currently have the revised verison running and it has been over 1 1/4 hours. I appreciate the help. I keep getting asked to do this so I am trying to save myself time by having the Select statement created by the search. Can my original script be corrected so the Execute statement will execute correclty?
0
 
LVL 12

Accepted Solution

by:
Saurabh Bhadauria earned 500 total points
ID: 38753785
I forget to add  system_type_id filter in where clause. in below query..

insert into #temp_t (object_id,query)
select s.OBJECT_ID, 'if exists ( select 1 from ' +  object_name(s.object_id) + ' where '  + s.name + ' like  ''%' + @search_string +'%'' )  '+ Char(13) +
        'Print  ''select * from ' +  object_name(s.object_id) + ' where ' + s.name + ' like  ''''%' + @search_string + '%'''''';'
 from sys.columns s  join  sys.objects o on s.object_id=o.object_id where o.type='U' and s.system_type_id IN (167, 175, 231, 239)

By the way... I have modified your code.. you have missed some quotes....other wise it is fine..

Both codes are attached..you are free to choose any one....
I have made some changes in my script...it should work fine now...

Thanks,
Saurv
your.sql
my.sql
0
 

Author Comment

by:rwheeler23
ID: 38754300
Thank you. I will give these a try later today and let you know.
0
 

Author Closing Comment

by:rwheeler23
ID: 38817313
Thank you. I forgot this was not awarded
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question