Grant Execute Rights To All SPs

Many times you can have a database that has access to it restricted to SPs.  If the database is application specific, then there is a good chance that 1 user account will need to access all these SPs to run the application.

My friend Phil found and updated this script to run in SQL Server 2005 to generate the necessary code to grant execute rights to all SPs in a database for a given user.

SELECT ‘GRANT EXECUTE ON ‘ + + ‘ TO AccountName’ + 

CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) 

FROM sysobjects 

WHERE type = ‘P’ and category = 0 AND name not like ‘sp_%’


SQL Server Query Results To Excel

There are many times when I just want to run a query and send the results to someone in an excel spreadsheet.

You can easliy copy the and past the results, but you don’t get column headers, and you lose formatting in many cases.

Dinakar Nethi has a good article about how to directly import the results of a query into excel.

I just used his method and it worked great.

VS2005 VB.Net Create Properties From Private Fields

“Refactor!” has the ability to turn your fields into public accessible properties, but it kinda sucks because 1) you have to do it 1 at a time, and 2) the naming convetion is all wrong. 

For example if you have a private integer called myInt then your property will be MyInt1.  Not exaclty what I am looking for.

So I updated my 2002 macro for creating these properties.  It should allow you to follow a variety of naming conventions “m_XXX”, “ciXXX”, “strXXX” etc.

Just include the macro below, then just highlight your private fields, and run the macro.

   ' highlight the private properties
Public Sub AddClassProperties()

Dim oTextSelection As TextSelection = DTE.ActiveWindow.Selection
Dim iLinesSelected = oTextSelection.TextRanges.Count
Dim colPropertyList As New Collection()
Dim iIndex As Integer
Dim oStart As EditPoint = oTextSelection.TopPoint.CreateEditPoint()
Dim oEnd As TextPoint = oTextSelection.BottomPoint

'Create an Undo context object so all the changes can be
'undone by CTRL+Z
Dim oUnDo As UndoContext = DTE.UndoContext

'Supress the User Interface. This will make it run faster
'and make all the changes appear once
DTE.SuppressUI = True


oUnDo.Open("Comment Line")

Dim sProperty As String
Dim sLineOfText As String

Do While (oStart.LessThan(oEnd))

sLineOfText = oStart.GetText(oStart.LineLength).Trim
'*** do some kind of simple check to make sure that this line
'*** isn't blank and isn't some other kind of code or comment
If (sLineOfText.IndexOf(" As ") >= 0 And ( _
(sLineOfText.IndexOf("Public ") >= 0) Or _
(sLineOfText.IndexOf("Private ") >= 0) Or _
(sLineOfText.IndexOf("Dim ") >= 0) Or _
(sLineOfText.IndexOf("Protected ") >= 0) Or _
(sLineOfText.IndexOf("Friend ") >= 0) Or _
(sLineOfText.IndexOf("ReDim ") >= 0) Or _
(sLineOfText.IndexOf("Shared ") >= 0) Or _
(sLineOfText.IndexOf("Static ") >= 0) _
)) Then

sProperty = oStart.GetText(oStart.LineLength).Trim.Replace(" New ", " ").Replace("()", "")

End If



If colPropertyList.Count > 0 Then

For Each sProperty In colPropertyList
Call InsertProperty(sProperty)

MsgBox("You must select the class properties")
End If

Catch ex As System.Exception

If MsgBoxResult.Yes = MsgBox("Error: " & ex.ToString & vbCrLf & "Undo Changes?", MsgBoxStyle.YesNo) Then
End If


'If an error occured, then need to make sure that the undo context is cleaned up.
'Otherwise, the editor can be left in a perpetual undo context
If oUnDo.IsOpen Then
End If

DTE.SuppressUI = False
End Try

End Sub

Private Sub InsertProperty(ByVal sProp As String)
Dim oTextSelection As TextSelection = DTE.ActiveWindow.Selection
Dim sMember As String = sProp.Substring(sProp.IndexOf(" ")).Trim
Dim sDataType As String
Dim sName As String
Dim i As Integer
Dim iAscVal As Integer

i = sMember.IndexOf("(")
If Not i = -1 Then
sMember = sMember.Substring(0, i)
End If

i = sMember.IndexOf("=")
If Not i = -1 Then
sMember = sMember.Substring(0, i)
End If

sDataType = sMember.Substring(sMember.IndexOf(" As ") + 1)

For i = 0 To sMember.Length - 1
'iAscVal = Asc(Mid(sName, i, 1))
iAscVal = Asc(sMember.Chars(i))
If iAscVal > 64 And iAscVal < 91 Then
sName = sMember.Substring(i)
Exit For
End If
Next i

sName = sName.Substring(0, sName.IndexOf(" As ") + 1).Trim

If sName.Length = 0 Then
MsgBox("Unable to process the class property: " & sMember & ". This is usually caused by an incorrect naming convention (e.g. not cxName)")
End If

sMember = sMember.Substring(0, sMember.Length - sDataType.Length).Trim

With oTextSelection

.Text = "Public Property " & sName & "() " & sDataType
.Text = "Return " & sMember
.StartOfLine(vsStartOfLineOptions.vsStartOfLineOptionsFirstColumn, True)
.LineDown(False, 3)
.Text = "Me." & sMember & " = Value"
.LineDown(False, 2)

End With

End Sub



Update1: When using code that you cut and paste from here, you might need to first paste it into Word or Wordpad.

Update2: When using this macro, you need a space (new line) between the last private field variable and your end of class line.

applicationSettings, appSettings. app.config, web.config and userSettings

I have seen lots of posts from people in the newsgroups trying to figure out what the deal is with the new config options in .net 2.0.

The main reason people run into problems is due to MS making a number of changes, including changes between the beta and the RC product, resulting in different answers based on how early you encountered the issues.

You can still do things the way you did with .Net 1.x.


              <add key=MyKeyName value=somevalue/>


and then you can access it with:

Dim myvalue As String = System.Configuration.ConfigurationSettings.AppSettings.Item(“ConnectionString”)

However, if you try this, VS.Net will warn you:

This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings

It is important to note that the “!” above indicates that the fully quallified class listed, is located in the System.Configuration assembly, which of course you have to add to your project. 

So unlike previous versions, you need to manually add a reference to System.Configuration in order to make this new call.

Now, .net 2.0 and 2005 have teamed up to offer a new option for storing user settings which may seem more complicated at first if you don’t know exactly what you are doing.  In the project properties you can select a “Settings” tab where you are able to modify application settings, which are in turn stored in app.config.  These settings can either be “User” settings, or “Application” settings. 

Application settings are stored in the app.config (or web.config), and are read-only.  User settings have a default value that is stored in the app.config, but your application can overwrite these default values as needed, and the users settings will be stored in:

%USERPROFILE%Local SettingsApplication Data<Company Name><appdomainname>_<eid>_<hash><verison>user.config. (non roaming)


%USERPROFILE%Application Data<Company Name><appdomainname>_<eid>_<hash><verison>user.config (roaming)

The other nice feature of the 2.0 way of accessing these settings is that the settings are saved as a specific type from a wide collection of available types (String, System.DateTime, System.Drawing.Color, System.Guid, Bool, StringCollection etc) and when you access them from your code they are available in intellisense.

This may seem like it isn’t important, but it means you can’t mistype a setting key, or accidently try an invalid cast from one of your settings.  Also, storing a collection was a real pain in 1.x.    Now you can create a collection quickly and it will be added to the user settings like this:

   <setting name=MyCol serializeAs=Xml>


            <ArrayOfString xmlns:xsi=


                <string>I am first</string>


                <string>me third</string>

                <string>I am number four</string>




Pretty nice!

To access these properties you have 2 different ways depending on if you are using VB.Net or C#.

For VB.Net you use the “My” namespace and access it like this:

Dim mySetting As String = My.Settings.MySetting

For C# you access these settings through the “Properties” object

       string mySetting = Properties.Settings.Default.MyUserSetting;

That’s it!

Virtual Server Networking With External Access

I was having such a hard time getting MS Virtual Server setup so that my virtual servers could access the public network and vice versa.

In the end the problem was caused by a service that is running to enable my VPN connections.

The service is called:

Cisco Systems, Inc. VPN Service.

As soon as I turned off that service, everything with my virtual servers network worked just fine.