Searching Objects for Text

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Solomon Rutzky » Searching Objects for Text

Searching Objects for Text

Rate Topic

Display Mode

Topic Options

Author

Message

Solomon Rutzky

Posted Tuesday, May 27, 2008 9:47 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 285, Visits: 1,378

Comments posted to this topic are about the item Searching Objects for Text

SQL# - http://www.SQLsharp.com/

Post #507401

Jaroslaw Janik

Posted Wednesday, May 28, 2008 12:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 24, 2010 12:38 AM
Points: 1, Visits: 22

Hi Solomon
Very useful SP!
Some time ago I wrote something similar to it but never had time to work on supporting comments and strings.
So, I let myself to add multiple database support (see the attachment for the modified code). Additionally it is good to take the linked server name in brackets.
Thanks
Jaroslaw

Post Attachments

SearchObjectsForText.sql.txt (19 views, 17.58 KB)

Post #507951

Solomon Rutzky

Posted Wednesday, May 28, 2008 1:17 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 285, Visits: 1,378

Thanks, Jaroslaw, for the compliment and for the code. I will incorporate that into the next version.

PS. Regarding the Linked Server name: yes, it is best in brackets when there is an Instance along with the ServerName, but I figured it best to leave it out so that the user could decide to include them or not; if I included them then they would always be there. But either way :).

SQL# - http://www.SQLsharp.com/

Post #507962

david.murden

Posted Thursday, May 29, 2008 7:44 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 27, 2011 1:29 AM
Points: 54, Visits: 141

Hi

Have you any idea why i would be getting a String or Binary data would be Truncated error? I have poured over the script for quite a while and not found anywhere vars or columns are overstuffed.

Looks great though, better than the one i wrote a while back

Post #508389

Solomon Rutzky

Posted Thursday, May 29, 2008 9:22 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 285, Visits: 1,378

david.murden (5/29/2008)

Have you any idea why i would be getting a String or Binary data would be Truncated error? I have poured over the script for quite a while and not found anywhere vars or columns are overstuffed.

Looks great though, better than the one i wrote a while back

Hello David and thanks :). I am not sure about that error since I have never gotten it using this proc. However, I would add PRINT statements between each of the SQL statements to help narrow down which one is causing the error. If you can provide me more info I might be able to help.

SQL# - http://www.SQLsharp.com/

Post #508475

david.murden

Posted Thursday, May 29, 2008 10:13 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 27, 2011 1:29 AM
Points: 54, Visits: 141

Thanks for the quick response.

I have run it through the T-sql debugger in query analyser and it errors right at the FOR statement below when it tries to populate the cursor.

I have isolated the create table for #Objects and the insert statements, then created the table, populated it, created the cursor and populated it in a seperate connection outside of the sproc and it works.

The debugger boke at **ERROR** a few seconds

--Use a cursor to step through the objects so we can reconstrunct them
DECLARE crsObjects CURSOR LOCAL FAST_FORWARD
**ERROR** FOR SELECT obj.[DBId], obj.[ObjectId], obj.[Text], obj.XType
FROM #Objects obj
ORDER BY obj.[ObjectId], obj.[ColId]

Is there anything else you would find useful to know?

Thanks

Post #508508

Solomon Rutzky

Posted Thursday, May 29, 2008 10:26 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 285, Visits: 1,378

david.murden (5/29/2008)

Is there anything else you would find useful to know?

Do you have it searching JobSteps? Which is the default. If so, try setting that parameter to 0 and let me know if it works.

SQL# - http://www.SQLsharp.com/

Post #508519

david.murden

Posted Friday, May 30, 2008 2:19 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 27, 2011 1:29 AM
Points: 54, Visits: 141

Hi,

Eliminating Jobs from the query worked. It seems to be working quite well now. Do you know why the Jobs section caused it to fail? i am using sqlsvr2000.

I will make some modifications to this for my purpose and if it turns out well i will post my code.

Thanks for all you hard work on this

Post #508877

Solomon Rutzky

Posted Friday, May 30, 2008 9:01 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 285, Visits: 1,378

david.murden (5/30/2008)

Do you know why the Jobs section caused it to fail? i am using sqlsvr2000.

I have a pretty good idea of what is wrong. It is either this:

((CONVERT(INT, CONVERT(VARBINARY(50), LEFT(CONVERT(VARCHAR(36), sj.job_id), 3))) * 100) + sjs.step_id) AS 'ObjectId'

although I don't really think so. It is more likely this:

sj.name + ' :: ' + sjs.step_name AS 'ObjectName'

The issue is that the ObjectName field in #Objects is defined as a SYSNAME datatype which is an alias for nvarchar(128). However, sysjobs.name and sysjobsteps.step_name are both SYSNAME and so they could potentially combine, if both are maxed out, to be a NVARCHAR(256). The JobSteps portion of this search is the only part that combines two strings to create an ObjectName so I guess I assumed (incorrectly) that the combination of those two would not likely overflow the 128 bytes of SYSNAME. So this is an easy fix: just edit the definition of #ObjectNames towards the top and define [ObjectName] as a NVARCHAR(260) instead of SYSNAME. I got the 260 from combining two SYSNAMEs (which are each NVARCHAR(128)) and adding 4 characters for the ' :: ' that separates those names in the concatenation shown above.

Lemme know if that works.

Take care,
Solomon...

SQL# - http://www.SQLsharp.com/

Post #509172

« Prev Topic | Next Topic »

Permissions