Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Type Conversion

Posted on 2013-01-06
12
Medium Priority
?
273 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 70

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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview

926 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