Script to search for a string in all varchar columns in all tables in a database?

I have a string which I need to know where it came from in a database.
I don't want to spend time coding this so is there a ready made script
which takes a string as a parameter and searches all the tables which
contain varchar type columns and searches these columns and indicate which
tables contain that string?

Full text search is not enabled.

--
Tony
http://dotNet-Hosting.com - Super low $4.75/month.
Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,
PHP 5 & webmail support.http://vyaskn.tripod.com/search_all..._all_tables.htm

HTH, Jens Suessmeyer

Script Source: Error on truncation etc

I have a script source to deal with a source that has different "record types" (first 3 columns are the same then the remaining 2 to 30 columns are different based on the record type).

Script source was working fine... then one of the columns that I had set to String with length of 2 came in with a length of 3 (which is not per spec)... instead of failing - all the columns after the one that had the bad value were null and the script just stopped as soon as it hit that.. AND said it was success. Which means it imported the data incorrectly and since the script says it was a sucess you'd never know anything went wrong and it only imported 30 rows instead of 10k+

Any ideas on how to capture this error?

Code (shortened with .... but should be enough - sorry the forum butchers the code formatting - if someone has a tip for pasting code from VS let me know):

Imports System

Imports System.Data

Imports System.Math

Imports System.IO

Imports System.Convert

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim oCurrentFile As File

Dim oStreamReader As StreamReader

Dim sCurrentLine As String

Dim aCurrentLine() As String

Try

oStreamReader = oCurrentFile.OpenText(Me.Variables.SourceName)

sCurrentLine = oStreamReader.ReadLine()

Do While sCurrentLine IsNot Nothing

aCurrentLine = sCurrentLine.Split(Chr(44))

Select Case aCurrentLine(2) ' This is the 3rd column.. "Record Type" which tells us what type of record it is and how many columns etc (Chr(44) is a comma)

Case "BF"

BFRecordsBuffer.AddRow()

BFRecordsBuffer.TimeStamp = aCurrentLine(0)

BFRecordsBuffer.EyeCatcher = aCurrentLine(1)

BFRecordsBuffer.RecordType = aCurrentLine(2)

BFRecordsBuffer.Sym = aCurrentLine(3)

....

BFRecordsBuffer.RecordCount = Convert.ToInt32(aCurrentLine(24))

...

If aCurrentLine.GetLength(0) >= 30 Then

BFRecordsBuffer.SeqNo = aCurrentLine(29)

End If

Case "QF"

QFRecordsBuffer.AddRow()

QFRecordsBuffer.Timestamp = aCurrentLine(0)

QFRecordsBuffer.EyeCatcher = aCurrentLine(1)

QFRecordsBuffer.RecordType = aCurrentLine(2)

...

....

End Select

sCurrentLine = oStreamReader.ReadLine()

Loop

BFRecordsBuffer.SetEndOfRowset()

QFRecordsBuffer.SetEndOfRowset()

....

Catch ex As Exception

Me.ComponentMetaData.FireError(1, "Script Component", ex.Message, "", 0, True)

Finally

oStreamReader.Close()

End Try

End Sub

End Class

Chris Honcoop wrote:

Code (shortened with .... but should be enough - sorry the forum butchers the code formatting - if someone has a tip for pasting code from VS let me know):

I can help with that. Go here: http://www.jtleigh.com/people/colin/software/CopySourceAsHtml/

Yuo can see an example of the result here:

http://blogs.conchango.com/jamiethomson/archive/2006/12/19/SSIS-Nugget_3A00_-Extract-last-word-from-a-sentance-using-regular-expressions.aspx

-Jamie

|||

Thanks Jamie, I installed it and don't see it as an option under add-ons.. nor where it mentions it will be in the menus - am I missing something? In re: to your blog welcome to the wonderful world of regex ;)

Any ideas on the script source truncation/data type mismatch "non error"?

|||

Chris Honcoop wrote:

Thanks Jamie, I installed it and don't see it as an option under add-ons.. nor where it mentions it will be in the menus - am I missing something? In re: to your blog welcome to the wonderful world of regex ;)

Any ideas on the script source truncation/data type mismatch "non error"?

It doesn't work in VSA unfortunately so copy and paste it into a regular VB.Net project and copy as html from there.

Not sure about the error you're getting i'm afraid.

-Jamie

|||Its something with the way I do the error trapping.. I comment out the try / catch etc and the script source will fail when the columns are bad. Of course getting a useful error message about which column etc is experiencing the problem is an entirely different headache.|||To have the standard SSIS options of ignore,redirect, and fail

component in your own custom script component, there are a couple of

changes to consider.

1. Add a try/catch inside your loop. As written, when the first error

happens (e.g. "ABC" rather than "AB"), the exception will be caught

outside of the loop, and no rows beyond that will be processed. This

change will allow the implementation of the ignore and/or redirect

options, and of course, continued processing.

2. In the newly added try/catch, add an catch for the

DoesNotFitBufferException. This will "catch" the error. You can also simply ignore this particular

exception type, and the string will be truncated, or re-throw the

exception, and the component will fail, or lastly, redirect it (

essentially adding a row to an "error" output on your component)

3. To report which column name/index is experiencing the problem will

require a cache of column information (names, types,lengths, etc) by

overriding the PreExecute function, and then grabbing a reference to

the the base buffers by overriding the PrimeOutput function. The

column cache and base buffers can then be referenced in your

CreateNewOutputRows implementation.

Hopefully this will help you along your way. If not, I'll find some script component code and post that as well.

Script databsae does not compatible with SQL2000

I've created a databse on SQL2005, and I did not use any new data type for columns. I script the database, and select SQL2000 for "Script for Server version", howvever, the script generated cannot be run on SQL2000, it can be only run on SQL2005.

The script generated by SQL2005 is not backward compatible? like SQL2000 can generated script compatible wtih SQL7.

CREATE TABLE [dbo].[Table1](
[Col1] [nvarchar](16) NOT NULL,
[Col2] [nvarchar](100) NOT NULL,
[Col3] [nvarchar](10) NULL,
[Col4] [datetime] NULL,
[Col5] [nvarchar](10) NULL,
[Col6] [datetime] NULL,
[Col7] [bit] NULL CONSTRAINT [DF_Table1_Col7] DEFAULT ((0)),
[Col8] [bit] NULL CONSTRAINT [DF_Table1_Col8] DEFAULT ((0)),
[Col9] [int] NULL CONSTRAINT [DF_Table1_Col9] DEFAULT ((0)),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Col1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Error detected on the line "IGNORE_DUP_KEY = OFF".

This is a known bug. From the feedback center:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=a6510471-9c40-4184-9611-5656457864d8

Edit: I have tried it and it does seem to be fixed in the SP1 CTM

Louis

|||Generally, you should not rely on the scripting capabilities other than for ad-hoc stuff. It is better to maintain your scripts in a source code control system and use it for reference / maintenance. There are other problems with scripting other than syntax issue with the CONSTRAINT in your example. For example, resolving dependencies is not accurate since the engine itself doesn't guarantee it for all cases. There are also issues with expressions which are normalized / modified by the engine so if you try to compare the script generated from the engine with your source it will not match even though they produce identical results.|||I'm sorry, but I can't hold back. The condescension in your reply to this stranded user is almost palpable. These types of responses are absolutely maddening, especially when the bugs in the SQL Server 2005 tools are so *painfully* obvious upon even casual observation. People don't need lectures on why you shouldn't be using a feature YOU included in the server which doesn't work because YOU did incredibly inadequate testing before you shipped. Seriously, just about any script you create from SQL Mgmt Studio which you want to run on 2000 will not work because it uses sys.objects. That bug should have never been included in the release, and frankly is a sign of a real management problem inside your team which needs to be addressed.|||

I won't dispute the 2005 tools have errors comment, and neither did he (in fact I would sing in that chorus with you if need be). However, he post was not a "well, you shouldn't be using it anyway" comment, I don't think. He goes on to explain that some parts of scripting out objects is just really quite hard (like maintaining precedence of which script to run first.)

As a side note, the sys.objects issue has been changed in the CTP as well for the 2000 compatible scripting.

All he was trying to say was that the best way to use the tools and do development is to maintain scripts of your work, and not rely on the tools to script out a database. I doubt that he was suggesting to never use the scripting tools for any reason. (especially since he advocated it for ad-hoc stuff.) I know I use it quite often, if for no other reason than to post a table structure to a discussion. But for a production system you should generally should have scripts for all objects that were created outside of using the SSMS tools and then scripting the objects.

It is very common to include a mini-lecture with any post where someone sees that a person *might* be abusing a feature. I learned a ton from my early days in the newsgroups because a friendly user or two (and an unfriendly albeit, very intelligent, jerk) lectured me on how something should be done.

|||Thanks, ***. Fix your God damned product or don't ship it. SQL 2005 is ***.

script component Name Ouput0Buffer is not Declared?

Doing a simple test with a script component in a DataFlow to transform some data from a flat file. I have new columns under the default Ouput 0 .. however in my code when I try this, I get the above error.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Output0Buffer.AddRow()

End Sub

There will of course be a lot more code in the Script Component, but not clear on why I can't reference it.

Do you need to add a new row? Or are you just assigning a value to a new column on the existing row? The Row object should expose all of the columns you added.|||

JCNET wrote:

Doing a simple test with a script component in a DataFlow to transform some data from a flat file. I have new columns under the default Ouput 0 .. however in my code when I try this, I get the above error.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Output0Buffer.AddRow()

End Sub

There will of course be a lot more code in the Script Component, but not clear on why I can't reference it.

Is this a synchronous or an asynchronous component? If it is synchronous then calling the AddRow() method is not valid. You cannot add rows to the output of a synchronous component.

-Jamie

script component

i have 2 source columns testsource and testsource1 and 2 output columns

test and test2

i am passing them through a script component to check if the columns are numeric or not along with some more logic...I am able to get the first column evaluated based on the logic but not the second column

Is it that the script component can only look at one column?

this is the code i wrote

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If IsNumeric(Row.testsource) Then

Row.test = Left(Row.testsource, 4) + CStr("/") + Mid(Row.testsource, 5, 2) + CStr("/") + Right(Row.testsource, 2)

Else

If Row.testsource = "N.A" Then

Row.test = "NULL"

End If

Row.test = "NULL"

End If

If IsNumeric(Row.testsource1) Then

Row.test2 = Left(Row.testsource1, 4) + CStr("/") + Mid(Row.testsource1, 5, 2) + CStr("/") + Right(Row.testsource1, 2)

Else

If Row.testsource1 = "N.A" Then

Row.test2 = "NULL"

End If

Row.test2 = "NULL"

End If

End Sub

End Class

example of run

INPUT

,20070930
,20080331
,20070930
,20071130
,20070930
,
,
,
20070504,20070503
20080331,
,20070930
N.A. ,N.A.
N.A. ,N.A.

OUTPUT

NULL,20070930
NULL,20080331
NULL,20070930
NULL,20071130
NULL,20070930
NULL,
NULL,
NULL,
2007/05/04,20070503
2008/03/31,
NULL,20070930
NULL,N.A.
NULL,N.A.

as you can see the first column gets evaluated based on logic but the second column just gets passed through..

Thanks for any help in advance

smathew

Put a data viewer in the path right before the script component and ensure that you have the correct results that you expect (for instance, you don't have that comma in there). Also, you might want to add a derived column before the script component to trim() each field to get rid of any extra spaces.|||

i do have a derived column that does the trim, but it still does not evaluate the second column based on the logic..

smathew

|||

smathew wrote:

i do have a derived column that does the trim, but it still does not evaluate the second column based on the logic..

smathew

The other thing you can do after you've looked at the data viewer, is to add a MsgBox(Row.testcolumn1.ToString) to see what happens.