Search Text in a Sub Procedure in SQL Server

Find Text in sub procedures SO.Type = ‘P’ means Procedure, other system object types are:

P – Procedures

FN – Scalar-Valued Functions

TF – Table-valued functions

V – Views

D – Database Diagrams

X – Tables

 

I have commented that line in bold and it will search everything for entered string value in this case.

 

To search text in a sub-procedure or otherwise

 

CREATE PROCEDURE [dbo].[Find_Text_In_SP]

@StringToSearch varchar(100)

AS

   SET @StringToSearch = '%' +@StringToSearch + '%'

   SELECT Distinct SO.Name

   FROM sysobjects SO (NOLOCK)

   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID

   --AND SO.Type = 'P'

   AND SC.Text LIKE @stringtosearch

   ORDER BY SO.Name

 

To search for a string within subprocedure name:

 

CREATE PROCEDURE [dbo].[Find_SPName_With_Text]

   @StringToSearch varchar(100)

AS

   SET @StringToSearch = '%' + @StringToSearch + '%'

   SELECT DISTINCT SO.NAME

   FROM SYSOBJECTS SO (NOLOCK)

   WHERE SO.TYPE = 'P'

   AND SO.NAME LIKE @StringToSearch

   ORDER BY SO.Name

 

-SB

Remove Delete Permission on Windows 2003 Server

In order to remove delete permissions so that a user is able to write files to a shared location on File Server but is unable to delete those files:

-         Give the user Write access and take away Modify access

-         Also, take away CREATED OWNER access on the parent folder and child objects; else the user who copies the file is able to delete the file as CREATED OWNER gets FULL CONTROL on the files he/she copies to the shared location.

-         Make sure you check the box to “Replace permission entries on all child objects with entries shown here that apply to child objects” under Advanced Security Settings option on the parent folder and every time you change permission for the whole tree.

 

Regards,

Savitur Badhwar

 

 

 

 

How do I search for special characters (e.g. %) in SQL Server?

 

There are several characters that have special meaning within a SQL query, for example the percent sign (%) in a LIKE query is a wildcard that essentially means "any number of characters can go here." Likewise, the underscore (_) is a wildcard that says "any single character can go here." So what if you are actually looking for a value that contains a literal percent sign? You will end up with bizarre results if you try the following: 
 

SELECT columns FROM table WHERE      column LIKE '%%%'

 
Instead, you can try one of the following solutions: 
 

SELECT columns FROM table WHERE      column LIKE '%[%]%'    -- or    SELECT columns FROM table WHERE      column LIKE '%\%%' ESCAPE '\'

 
The first query 'delimits' the special character with square brackets, telling the engine to treat it as a normal literal character instead of a character with special meaning. The second query uses a custom escape character -- you can use any character you like, just be careful that you aren't also expecting to use it as part of the literal string. 
 
Now, you might be wondering, how do I escape a square bracket? If you have something like this: 
 

SELECT columns FROM table WHERE      column LIKE '%[SQL Server Driver]%'

 
The results won't be what you expect, because an opening square bracket is considered a special character. Surprisingly, you can avoid this problem in much the same way, by one of the following two queries: 
 

SELECT columns FROM table WHERE      column LIKE '%[[]SQL Server Driver]%'    -- or    SELECT columns FROM table WHERE      column LIKE '%\[SQL Server Driver]%' ESCAPE '\'

 
You can do this replacement at the ASP side, before passing the string in, or within the SQL Server code itself.

 

-Savitur

TIFF attachments may not be shown correctly in OWA 2007 and show RED "X" instead

Issue: In a Microsoft Exchange Server 2007 64-bit standard version environment, using windows 2003 64bit-version. We send e-mail message by outlook 2000 in Rich Text Format (RTF) format and attach with tiff attachment.

We found that the tiff image attachment cannot display correctly. the icon shows as a red "X".

Other attachment icons are shown correctly. The problem also exist when recipient use OWA2007.

Issue description: The icons that represent TIFF attachments may not be shown correctly in OWA 2007

The problem was that Internet Explorer (IE) cannot render TIFF images. It can render JPG, GIF, BMP and etc, but not TIFF. In Outlook 2003 and earlier, Outlook Express, and OWA, all use IE to render HTML. Since IE cannot display the TIFF, we get the red X. Outlook 2007 uses Wordmail to render all messages, and since Word can render TIFF files, the messages are displayed properly.

Workarounds:

1.       Use HTML or Plain Text instead of Rich Text when composing the message.

2.       Change the image format to something supported by IE (GIF, BMP and etc)

3.       Don’t send TIFF’s inside the message body, and attach them in the zip file

Fixes:

·                                 To resolve this problem, install Update Rollup 3 for Exchange Server 2007 Service Pack 1 (SP1) on the Exchange server. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

949870  (http://support.microsoft.com/kb/949870/ ) Description of Update Rollup 3 for Exchange Server 2007 Service Pack 1

 

 

 

Regards,

 

Savitur Badhwar