Solved

SQL Type Conversion

Posted on 2013-01-06
12
258 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Expert Comment

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

Expert Comment

by:ScottPletcher
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to remove non-duplicated row 5 25
using & in TSQL 18 21
Caste datetime 2 25
Syntax using Declare 3 16
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now