Solved

SQL Type Conversion

Posted on 2013-01-06
12
269 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Overlapping reports 2 38
MS SQL BCP Extra Lines Between Records 2 28
SQL Server - Getting the most recent engagement for each contact 9 45
Negative isnull? 3 25
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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