SQLServerCentral / Discuss Content Posted by Solomon Rutzky / Article Discussions / Article Discussions by Author / Searching Objects for Text / Latest Posts

RE: Searching Objects for Text

Solomon Rutzky — Fri, 30 May 2008 09:01:38 GMT

[quote][b]david.murden (5/30/2008)[/b][hr]Do you know why the Jobs section caused it to fail? i am using sqlsvr2000.[/quote]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...

RE: Searching Objects for Text

david.murden — Fri, 30 May 2008 02:19:54 GMT

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

RE: Searching Objects for Text

Solomon Rutzky — Thu, 29 May 2008 10:26:31 GMT

[quote][b]david.murden (5/29/2008)[/b][hr]Is there anything else you would find useful to know?[/quote]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.

RE: Searching Objects for Text

david.murden — Thu, 29 May 2008 10:13:32 GMT

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

RE: Searching Objects for Text

Solomon Rutzky — Thu, 29 May 2008 09:22:02 GMT

[quote][b]david.murden (5/29/2008)[/b][hr]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[/quote]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.

RE: Searching Objects for Text

david.murden — Thu, 29 May 2008 07:44:38 GMT

HiHave 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

RE: Searching Objects for Text

Solomon Rutzky — Wed, 28 May 2008 13:17:30 GMT

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 :).

RE: Searching Objects for Text

Jaroslaw Janik — Wed, 28 May 2008 12:59:00 GMT

Hi SolomonVery 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.ThanksJaroslaw

Searching Objects for Text

Solomon Rutzky — Tue, 27 May 2008 21:47:11 GMT

Comments posted to this topic are about the item [B]Searching Objects for Text[/B]