Facta Non Verba

Keyboard shortcuts for Windows Server 2012

2013-02-07T09:58:00.001-05:00

Simple batch to unlock Sharepoint Documents

2013-02-04T16:12:00.001-05:00

@echo off

set docname='Document1.docx'
osql -S server -E -d wssdb -Q "update dbo.AllDocs set CheckoutUserId = NULL,
checkoutDate =NULL, CheckoutExpires = NULL where dirname like 'sites/web/library%%' and leafname=%docname% and CheckoutUserId is not NULL"

echo %docname% Unlocked!
pause

For whatever reason some of my infopath forms are getting locked after an event receiver strips the permissions. It doesn't happens very often but it is very annoying. I decided that it will not do much harm if I give our helpdesk guys this simple batch to remove the lock when it is absolutely necessary.

Few notes:
-- the batch and osql must reside in the mapped to a letter drive, wouldn't work from UNC
-- single quotes around a document name are important
-- after doubleclicking on the batch the response should be: "1 rows affected"

SQL script to create audit Trigger for every column in the table

2012-10-15T17:58:00.001-04:00

I had to create a triger for several tables with big number of fields. It seems that I would use this procedure again and it worth saving here.

Select
'Create TRIGGER dbo.mvAudit ON dbo.BenefitFormsEmployees
for UPDATE
AS

BEGIN
IF
@@ROWCOUNT = 0 RETURN;
SET
NOCOUNT ON;'
SELECT

'INSERT INTO [table].[dbo].[ Log] ',

'([tableID] ,[Item],[OldValue] ,[NewValue] ,[LogDate] ,[SignOn])',

'SELECT i.TableID,'''+ c.name +''',d.['+ c.name +'],i.['+ c.name +'],GetDate(),i.SignIn ',

'FROM inserted i FULL OUTER JOIN deleted d ',

'ON i.[TableID] = d.[TableID] where i.'+ c.name +'<>d.'+ c.name

FROMsys.columns c

INNER JOIN

sys.types t ON c.system_type_id = t.system_type_id

LEFT OUTER JOIN

sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id

LEFT OUTER JOIN

sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

WHERE

c.object_id = OBJECT_ID('dbo.Table')

Length of Service in VBA

2012-04-27T16:29:00.002-04:00

'The following function calculates the Length of Service

Function fLengthOfService(DateOfHire As Date, AsOfDate As Date) As String
Dim d As Integer, m As Integer, Y As Integer
Dim dd As Date
m = -1 * DateDiff("m", AsOfDate, DateOfHire)
Y = Fix(m / 12)
m = m - Fix(m / 12) * 12
If m < 0 Then
Y = Y - 1
m = 12 + m
End If
dd = DateAdd("m", 12 * Y + m, DateOfHire)
d = -1 * DateDiff("d", AsOfDate, dd)
If d < 0 Then
m = m - 1
    If m < 0 Then
            Y = Y - 1
            m = 12 + m
    End If
dd = DateAdd("m", 12 * Y + m, DateOfHire)
d = -1 * DateDiff("d", AsOfDate, dd)
End If
fLengthOfService = IIf(Y > 0, Trim(Str(Y)) & "y ", "") & IIf(m > 0, Trim(Str(m)) & "m ", "") & IIf(d > 0, Trim(Str(d)) & "d", "")
End Function

Mistery Solved

2012-03-03T18:45:00.000-05:00

The Microsoft word recovery procedure that we use in our company and was described in my very first post stopped working after upgrading to office 2007 and then to 2010. I was under the assumption that when I copy the asd file it becomes corrupted and that’s why MS Word refuses to open it. As a solution I offered to use an Open Office in this post : http://www.freeofficerecovery.com/2010/10/how-to-open-corrupted-ms-word-2007.html

My colleague researched the issue and found that the copied file can only be opened from its original location. Our problem was in specifying in GPO the UNC path instead of a drive letter that our users are accessing. In the GPO the path to the autorecovery file was set to the UNC: \\servername\users\username, but users were using G: drive mapped to that UNC. After setting auto save path in MS Word file to G: all started to work as it was before.

PowerShell to upload a picture to AD and Exchange

2012-02-03T16:36:00.003-05:00

Friday! Something for fun :)

Import-RecipientDataProperty -Identity User -Picture -FileData ([Byte[]]$(Get-Content -Path "c:\users\me\desktop\mypics.jpg" -Encoding Byte -ReadCount 0))

Users and Groups management on Windows 7 Home Premium

2012-02-02T12:17:00.000-05:00

I had to uninstall Sophos from my home computer and was denied access with
Error 3005 "You are not a member of the Sophos Administrator Group"
There is no GUI to update local users or groups on Windows 7 Home Premium. To grant myself access I had to use a dos command:

net localgroup SophosAdministrator Michael /add
http://www.sophos.com/support/knowledgebase/article/110534.html

Convert InfoPath form to a WebPage

2012-01-31T11:41:00.000-05:00

1. Rename "form.XSN" to "form.ZIP"
2. Extract template.XML and view1.XSL
3. Run the code to convert template.XML to template.HTML
4. Verify that template.HTML looks ok
4. Loop thru other xml docs in your sharepoint library created from that infopath template and convert them all to htmls.

using System;

using System.Collections.Generic;

using System.IO;

using System.Text;

using System.Xml;

using System.Xml.Xsl;

using System.Web ;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

string path;

path = @"R:\\XSN\\EmployeeOrientationForm\\";

　

XslCompiledTransform xslt = new XslCompiledTransform();

xslt.Load(path+"view1.xsl");

// Execute the transform and output the results to a file.

xslt.Transform(path+"template.xml", path+"template.html");

}

};

}

http://msdn.microsoft.com/en-us/library/system.xml.xsl.xslcompiledtransform.aspx
http://code.cmsstores.com/infopath-form-to-html-using-c/
http://vamsheewithyou.blogspot.com/2010/12/infopath-form-to-html-using-csharp.html

Sending Emails with RAISEERROR

2012-01-19T14:29:00.002-05:00

I was going over sql server store procedures and decided to improve error handling with
try-catch code. Everything worked fine except that the RAISEERROR didn't send any emails to an operator.
The solution was simple all I had to do is to add "with log" option.

begin try
...
end try
begin catchraiseerror ('mydb.dbo.myproc failed!',16,1) WITH log

end catch

Email warning when mailbox size is over limit on Exchange 2010 with PowerShell Script

2012-01-06T11:56:00.000-05:00

I had to write a PowerShell script for Exchange 2010 that sends an email to a user when his mailbox size exceeded 1Gb. Here is the script:

If ((Get-PSSnapin | where {$_.Name -match "Exchange.Management"}) -eq $null)
{
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010
}
$toArray=Get-MailboxServer | Get-Mailbox | foreach-object {$email = $_.primarysmtpaddress; $_ | Get-MailboxStatistics | Where {$_.TotalItemSize.Value.ToMB() -gt 1024 } |   select @{ expression={$email}}}
$from="from@from.com"
Foreach ($to in $toArray)
{
     $s=[string]$to
   [int]$Start = $s.IndexOf("=")
   [int]$End = $s.IndexOf("}")
   [string]$ss=$s.Substring($Start+1,$End-$start-1)

$messageParameters = @{
smtpServer = “smtp server”
From = [string]$from
To = $ss
Subject = "Warning!"
Body = “Your mailbox is over 1Gb in size. Please delete old email messages to reduce size of your mailbox”
}
   Send-MailMessage @messageParameters -BodyAsHtml
}

Creating Scheduled Tasks for Exchange 2010 PowerShell Scripts:
http://www.mikepfeiffer.net/2010/02/creating-scheduled-tasks-for-exchange-2010-powershell-scripts/
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command ". 'C:\Program Files\Microsoft\Exchange Server\V14\bin\RemoteExchange.ps1'; Connect-ExchangeServer -auto; c:\Scripts\MoveMailboxes.ps1"

Corrupted InfoPath forms get published to SharePoint

2011-11-14T18:00:00.001-05:00

The issue with corrupted forms are only come up when a user has an older version that was previously installed.

The system is requesting to save the form first and then updates it.

The solution to this is to click on the yellow bar with prompt to “Save and Update Form”, cancel the save dialog box and close the form and then reopen the XSN link again.

We can also run "Infopath /cache clearall " command to achieve the same effect:

It is all not very user friendly.

To fix this for good we changed logon script and deleted previously installed forms from cache during user’s logon with:

Erase %userprofile%\APPDATA\Local\Microsoft\InfoPath\FormCache3\*.* /q /s

MS Access 2010 /pwd command line switch is missing

2011-11-14T17:47:00.001-05:00

To automate a report from encrypted database I used a free uility AutoHotkey.

Here is the source script, that I compiled to mydb.exe and then deleted
-----------------------------------------------------------------------------------------------------------------
run "C:\Program Files\Microsoft Office\OFFICE14\MSACCESS.EXE" c:\projects\mydb.mdb /cmd "auto"

WinWait, Password Required, , 3

sendinput {p}
sendinput {a}
sendinput {s}
sendinput {s}
sendinput {w}
sendinput {r}
sendinput {d}
sendinput {Enter}
return

-----------------------------------------------------------------------------------------------------------------

Then I used mydb.exe to run as a scheduled job.

Microsoft Windows Updates Report for SOX Audit

2011-10-31T17:20:00.004-04:00

Our system center server failed to provide a report that would satisfy the auditor.
He wanted to see the latest updates for every single computer : servers and desktops.
To solve this problem I used a PowerShell script to retrieve system event log and filter the output for event code 19 which is:” Installation Successful: Windows successfully installed the following update:...”

get-content f:\WindowsUpdates\pc.txt | f:\WindowsUpdates\reportevent.ps1 –debug

where

Report-Events.ps1 was taken from here http://jdhitsolutions.com/resources/scripts/Report-Events.txt
and modified query string to filter out all other events:

$query="Select ComputerName,Message,TimeWritten,Type,SourceName,EventCode,Logfile from win32_NTLogEvent WHERE (EventCode=19) and Logfile='System' "

And list computers thet were down or failed to obtain the updates:

$err+=" *** No matching events found for $computername ****
"

To populate pc.txt I copied the script from

http://blogs.technet.com/b/heyscriptingguy/archive/2006/11/09/how-can-i-use-windows-powershell-to-get-a-list-of-all-my-computers.aspx

$strCategory = "computer"
$objDomain = New-Object System.DirectoryServices.DirectoryEntry
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(objectCategory=$strCategory)")
$colProplist = "name"
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}
$colResults = $objSearcher.FindAll()
foreach ($objResult in $colResults)
{$objComputer = $objResult.Properties; $objComputer.name}

Html5 links

2011-09-21T17:26:00.002-04:00

Html5 Links: Dive into Html5 - a great overview of what Html5 has to offer.

http://diveintohtml5.org/IE9

Test Drive - cool applications made by the IE team.

http://ie.microsoft.com/testdrive/

Chrome Experiments - like IE9 Test drive but from the chrome team

http://www.chromeexperiments.com/ Apple Html5 - for Safari

http://www.apple.com/html5

/ Dynamic Pages via Media Queries - a list apart has good articles on web in general

http://www.alistapart.com/articles/responsive-web-design/ Html5 demos - Demos for individual features http://html5demos.com/

Internet Explorer links:• beautyoftheweb: http://www.beautyoftheweb.com/•

Internet Explorer Test Drive: http://ie.microsoft.com/testdrive/•

Pin Site Radio:
http://ie.microsoft.com/testdrive/Browser/Radio/Default.html

• Internet Explorer Developer Center:
http://msdn.microsoft.com/en-us/ie
• IEBlog: http://blogs.msdn.com/b/ie/

Hidding fields on DispForm.aspx in Sharepoint

2011-09-21T17:10:00.002-04:00

http://msdn.microsoft.com/en-us/library/aa544142.aspx http://www.sharepoint911.com/blogs/laura/Lists/Posts/Post.aspx?ID=3 http://www.cleverworkarounds.com/2008/02/28/more-sharepoint-branding-customisation-using-javascript-part-2/

Uninstall/Remove/Deactivate Sandboxed solution

2011-08-12T16:23:00.000-04:00

PS C:\Users\spAdmin2010> uninstall-spusersolution

cmdlet Uninstall-SPUserSolution at command pipeline position 1
Supply values for the following parameters:
Identity: csharepointproject.wsp
Site: http://******

Confirm
Are you sure you want to perform this action?
Performing operation "Uninstall-SPUserSolution" on Target
"CSharePointProject.wsp".
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help
(default is "Y"):
PS C:\Users\spAdmin2010> remove-spusersolution

cmdlet Remove-SPUserSolution at command pipeline position 1
Supply values for the following parameters:
Identity: csharepointproject.wsp
Site: http://****
Confirm
Are you sure you want to perform this action?
Performing operation "Remove-SPUserSolution" on Target
"csharepointproject.wsp".
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help
(default is "Y"):
PS C:\Users\spAdmin2010>

Sharepoint 2010 farm admin admin password change

2011-08-12T15:54:00.000-04:00

cd c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\
stsadm –o updatefarmcredentials –userlogin DOMAIN\username –password password
stsadm -o execadmsvcjobs

PowerShell:
Repair-SPManagedAccountDeployment

http://technet.microsoft.com/en-us/library/ff724280.aspx

Sharepoint 2010. The service is unavailable, error code 503

2011-07-08T12:45:00.000-04:00

App log says:
The Module DLL 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\isapi\owssvr.dll' could not be loaded due to a configuration problem. The current configuration only supports loading images built for a x86 processor architecture.

For some reason creating a site collection from the Central Administration sets "Enable 32-bit Applications" to true in the corresponding Application Pool. For 64-bit machines it must be re-set to false.

ASP.NET and Exchange 2010 powershell remote execution from webserver

2011-06-17T12:31:00.002-04:00

How to create a Distribution group maintenece in ASP.NET for Exchange 2010 with help of PowerShell cmd-lets.

http://social.technet.microsoft.com/Forums/en-US/exchangesvrdevelopment/thread/38d223da-2e6d-4a31-aedd-cd6971912bf4/

1. "Winrm quickconfig" must be ran on exchange
2. SystemManagementAutomation.dll v....17514
3. Virtual directory app. pool must have 32-bit enabled



Imports System.Collections.Generic

Imports System.Linq

Imports System.Text

Imports System.Management.Automation 

Imports System.Management.Automation.Runspaces 

Imports System.Security

' For the secure password

Imports System.Collections.ObjectModel

Imports System.Net.Security

Namespace PS2010



    Public Class PS

        Shared Sub AddContact(ByVal strEmail As String, ByVal strName As String, ByVal strDL As String, ByVal Op As String)

            Dim secureString As System.Security.SecureString = New System.Security.SecureString()

            Dim myPassword As String = "adminpass"

            For Each c As Char In myPassword

                secureString.AppendChar(c)

            Next

            Dim credential As PSCredential = New PSCredential("DOMAIN\ADMIN", secureString)

        

            Dim connectionInfo As WSManConnectionInfo = New WSManConnectionInfo(New Uri("http://EXCH/Powershell?serializationLevel=Full"), "http://schemas.microsoft.com/powershell/Microsoft.Exchange", credential:=credential)

            connectionInfo.AuthenticationMechanism = AuthenticationMechanism.Kerberos

            connectionInfo.SkipCACheck = True

            connectionInfo.SkipCNCheck = True

            Dim runspace As Runspace = System.Management.Automation.Runspaces.RunspaceFactory.CreateRunspace(connectionInfo)

            runspace.Open()

        

            Dim command As Command

            Dim cmd As Pipeline

            If InStr(strEmail, "DOMAIN.com") = 0 Then

                strName = "zzz-" & strName

                If Op = "AddAndDL" Then

                    command = New Command("New-MailContact")

                    command.Parameters.Add("Name", strName)

                    command.Parameters.Add("Alias", strName)

                    command.Parameters.Add("ExternalEmailAddress", strEmail)

                    command.Parameters.Add("OrganizationalUnit", "DOMAIN.local/Client Teams/External Contacts")

                    cmd = runspace.CreatePipeline()

                    cmd.Commands.Add(command)

                    cmd.Invoke()

                    Op = "AddToDL"

                End If

            End If

            If Op = "AddToDL" Then

                command = New Command("Add-DistributionGroupMember")

                command.Parameters.Add("Identity", strDL)

                command.Parameters.Add("Member", strName)

                cmd = runspace.CreatePipeline()

                cmd.Commands.Add(command)

                cmd.Invoke()

            End If

          

            If Op = "Delete" Then

                command = New Command("Remove-DistributionGroupMember")

                command.Parameters.Add("Identity", "DOMAIN.LOCAL/Client Teams/" & Trim(strDL))

                If InStr(strEmail, "DOMAIN.com") = 0 Then

                    command.Parameters.Add("Member", "DOMAIN.LOCAL/Client Teams/External Contacts/" & Trim(strName))

                Else

                    command.Parameters.Add("Member", Trim(strName))

                End If

                command.Parameters.Add("Confirm", False)

                cmd = runspace.CreatePipeline()

                cmd.Commands.Add(command)

                cmd.Invoke()

            End If

              

                If Op = "NewDL" Then

                    command = New Command("New-DistributionGroup")

                    command.Parameters.Add("Name", strName)

                    command.Parameters.Add("SamAccountName", strName)

                    command.Parameters.Add("Alias", strName)

                    command.Parameters.Add("Type", "Distribution")

                    command.Parameters.Add("OrganizationalUnit", "DOMAIN.local/Client Teams")

                    cmd = runspace.CreatePipeline()

                    cmd.Commands.Add(command)

                    cmd.Invoke()

                End If

                credential = Nothing

                connectionInfo = Nothing

                runspace.Close()

        End Sub

        

    End Class

End Namespace

Outlook macro to send out email templates from any folder

2011-05-16T16:29:00.000-04:00

The macro below will send out all selected emails from any folder

Sub Send_Bulk()

Dim objNS As NameSpace

Set objNS = Application.Session

Dim Item As Outlook.MailItem

For Each Item In ActiveExplorer.Selection

If Item.Class = olMail Then

Item.Send

End If

End Sub

Auto-reply between specific hours and on weekends from outook.

2011-05-06T15:57:00.000-04:00

------------------------------------------------------------------------------------------------
'--------------------------------------
' outlook VBA
      Dim i As Integer, flag As Boolean

        If ReplyBack("08:00 AM", "07:00 PM", Now()) Then
          If Item.Class = olMail Then
                     If Item.Recipients.Count > 0 And Item.Recipients.Count <= 2 Then

                            For i = 1 To Item.Recipients.Count
                                If InStr(1, Item.Recipients(i), "Desk") > 0 Then
                                    flag = True
                                End If
                            Next
       if flag then
                        Set fwdItem = Item.Reply
                                             fwdItem.Subject = fwdItem.Subject & " *** Auto Reply ***"
                         s = " Call After-Hours Support Line "
                         fwdItem.Body = s
                         fwdItem.Send
    end if
                     End If
                End If
            End If
-----------------------------------------------------------------------------------------------
Function ReplyBack(fromTime As String, toTime As String, curdate As Date) As Boolean
Dim flag As Boolean
flag = False ' dont replay
flag = Not (Val(Replace(Format(curdate, "short time"), ":", "")) < Val(Replace(Format(toTime, "short time"), ":", "")) And Val(Replace(Format(curdate, "short time"), ":", "")) > Val(Replace(Format(fromTime, "short time"), ":", "")))
If flag = False Then ' check dates
flag = (Format(curdate, "w") = 7 Or Format(curdate, "w") = 1)
End If
ReplyBack = flag
End Function

Dynamic GridView in ASP.NET

2011-04-13T17:51:00.000-04:00

I don't do ASP.NET projects very often, but when I need to quickly develop a web application I usually use a GridView(former DataGrid) control and I prefer to build it dynamically in the code.

I am posting my code here so it's easy to find in the future.

DropDown:

conn = new SqlConnection(this.dsAbsTime.ConnectionString);

conn.Open();

SqlCommand cmd = new SqlCommand("mvAbsTimeDropDown", conn);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@Login", UserLogin));

cmbUser.DataSource = cmd.ExecuteReader();

cmbUser.DataTextField = "fullName";

cmbUser.DataValueField = "id";

cmbUser.AutoPostBack = true;

cmbUser.DataBind();

GridView

SqlConnection conn = null;

dgVac.Columns.Clear();

try

{

SqlDataAdapter adapter = new SqlDataAdapter();

conn = new SqlConnection(this.dsAbsTime.ConnectionString);

conn.Open();

SqlCommand cmd = new SqlCommand( "mvAbsTime", conn);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmdDet.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.Add( new SqlParameter("@Login",signon));

adapter.SelectCommand = cmd;

DataSet dataSet = new DataSet( );

adapter.Fill(dataSet);

DataView view = new DataView(dataSet.Tables[0]);

view.RowFilter = "Sort<=200";

view.Sort = "Sort asc";

DataTable dt = new DataTable();

dt=view.ToTable();

this.lblHeader.Text = view[0]["FullName"].ToString() + " Personal Statement For Year " + DateTime.Today.Year;

dt.Columns.Remove("Sort");

foreach (DataColumn col in dt.Columns)

{

BoundField bfield = new BoundField(); bfield.DataField = col.ColumnName;

bfield.HeaderText = col.ColumnName;

bfield.HeaderStyle.Font.Bold = false;

bfield.HeaderStyle.BackColor = System.Drawing.Color.FromArgb(204,204,204);

bfield.ItemStyle.HorizontalAlign = HorizontalAlign.Center;

dgVac.Columns.Add(bfield);

}

dgVac.AutoGenerateColumns = false;

dgVac.DataSource = dt;

dgVac.Columns[0].HeaderText = "Description";

dgVac.DataBind();

catch (Exception e)

{

Response.Write(e.Message.ToString());

}

finally

{

if (conn != null)

{

conn.Close();

}

MS Access Query Timeout

2011-03-09T12:14:00.000-05:00

All of sudden my users start complaining about certain report timeouts when they pick a long date range on a table with 5 million records joined to itself.

I love to play with SQL server performance tools and tune long running queries, but in this case nothing seems to work.

At the end I just had a hunch and replaced all DoCmd.OpenQuery statements with DoCmd.RunSql and miraculously everything started to move fast.

ListBox control with Hyperlinks on MS Access form

2011-03-04T16:35:00.000-05:00

I've tried google and msdn but I couldn't find any workable samples .

By trial and error I came up with these 2 lines of code that worked :)

to add a hyperlink I used this line:

lstPdf.AddItem path & rst!fn & "\Report.pdf"

and for the list's click event this:

Private Sub lstPdf_Click()
Application.FollowHyperlink Me.lstPdf.ItemData([lstPdf].[ListIndex])
End Sub

xp_sendmail: failed with mail error 0x80040115

2011-02-09T17:51:00.000-05:00

After upgrading our Exchange Server from version 2003 to Exchange 2010 I start getting this unpleasant message from SQL Server.

Msg 17925, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040115

I rebuilt Outlook profile, reconfigured SMTP, tested all and everything worked except for xp_sendmail.
MS offered a hot fix, but it was for older version of SQL Server, so I decided to replace xp_sendmail with sp_db_sendmail instead. Firstly I ran these two statements to locate procedures and triggers, and then by quick replace changed xp_sendmail to sp_db_sendmail and @message to @body and the problem was solved.

Declare @StringToSearch varchar(25)
SET @StringToSearch = '%xp_sendmail%'

SELECT OBJECT_NAME(id)
FROM syscomments

WHERE [text] LIKE @StringToSearch AND OBJECTPROPERTY(id, 'IsTrigger') = 1

GROUP BY OBJECT_NAME(id)

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