Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Searching Objects for Text Expand / Collapse
Author
Message
Posted Tuesday, May 27, 2008 9:47 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:01 PM
Points: 368, Visits: 1,956
Comments posted to this topic are about the item Searching Objects for Text




SQL# - http://www.SQLsharp.com/
Post #507401
Posted Wednesday, May 28, 2008 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, May 28, 2008 1:17 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:01 PM
Points: 368, Visits: 1,956
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
Posted Thursday, May 29, 2008 7:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Thursday, May 29, 2008 9:22 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:01 PM
Points: 368, Visits: 1,956
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
Posted Thursday, May 29, 2008 10:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Thursday, May 29, 2008 10:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:01 PM
Points: 368, Visits: 1,956
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
Posted Friday, May 30, 2008 2:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Friday, May 30, 2008 9:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:01 PM
Points: 368, Visits: 1,956
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 »

Add to briefcase

Permissions Expand / Collapse