Searching Objects for Text

Question

Post reply

Searching Objects for Text

Solomon Rutzky

SSCoach

Points: 16430
More actions
May 27, 2008 at 9:47 pm

#188266

Comments posted to this topic are about the item Searching Objects for Text
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jaroslaw Janik Grasshopper Points: 13 More actions · Answer 1

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

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 2

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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

david.murden Old Hand Points: 394 More actions · Answer 3

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

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 4

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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

david.murden Old Hand Points: 394 More actions · Answer 5

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** FORSELECTobj.[DBId], obj.[ObjectId], obj.[Text], obj.XType

FROM#Objects obj

ORDER BYobj.[ObjectId], obj.[ColId]

Is there anything else you would find useful to know?

Thanks

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 6

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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

david.murden Old Hand Points: 394 More actions · Answer 7

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

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 8

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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR