SSMS 2012 - Template Modification

Permalink 02/05/14 05:31, by admin, Categories: SQL Server, 2008, 2005, 2012

Ran into a situation where i needed to modify the standard templates that SSMS uses to Create Stored Procedures, Functions, and some other scripts.


I did not know where to find these templates.

First Solution:

Goto Code Snippet Manager and modify snippets.
Problem with this is that it requires you to create the XML snippet file and even then requires you to put it in, either the Public or Your Profile's, My Snippets folder. This does not solve the immediate problem as i would rather use the Context menu's New Stored Procedure (Function, etc).

This solution though is great for things such as doing standard Select Statements for your organization.

Second Solution:

Edit the Template used, via Template Explorer.
This created a solution and was exactly what i needed, BUT this is Profile based on not Application based.

This solution is great if you want to do some edits to various templates, but understand that these edits will not be transferrable to the SSMS implementation and used.

Any edits to the templates, via Template Explorer, will be stored in C:\Users\\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates\Sql and as such generally not accessible to SSMS as most of us use it.

This does however provide us a way to do mass edits to various SQL templates, thus providing a g> Part of the solution i need.

Final Solution

Take Second Solution combined with Cut & Paste to actual directory (Need to be able to execute process as Administrator, when the prompt shows up.

1. So take the steps completed from the Second Solution, you now need to open and explorer window to the path C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql (Or if you did a custom installation for SSMS, then its respective location, with notable subfolder being SqlWorkbenchProjectItems from the root location of SSMS.exe.

2. Select either the file or folder, pends on how many edits you made, from the . If you made alot of SQL file edits acrossed multiple folders, then select either the folder(s) you made the edits in, or the parent folder of them.

3. Now the most import, AND SUPER CRITICAL STEP, Copy & Paste the folder(s), from Step 2, and paste them into C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql. This of course pending on how many edits, but i would localize the paste into the folder(s) that you made the edits.


Now you can take these SQL Template files and use them from the Context menu in the various options available.

WebPart - InPlace Custom Property editing

Permalink 04/08/14 05:25, by admin, Categories: Projects, Microsoft, Sharepoint, Sharepoint 2013 , Tags: sharepoint, sharepoint 2013, webpart, webpart custom property

Bear with the formatting, this Blog engine is old and getting on my nerves.  Time for an upgrade after 8 yrs :P

Spent some time working on a WebPart implementation, where i wanted to edit the webpart in place instead of constantly clicking the glorious Edit WebPart for the WebPart object. So after some research, found a great article from MATHIEU DESMARAIS about the design patterns and logic flow. The main difference i had from his design, was i was prescribing the way the View/Edit Modes would be represented with the HTML syntax.

I will not bore you with the details of getting up to this point, as there are numerous amounts of posts out there. I will although focus on the details of how i took Mathieu's implementation and modified for a Static set. Some of the pitfalls i had, probably because his was on demand control creation and mine is predefined.

Notes to take mind of:

  • This Implementation is for a SQL Connection Webpart. As such, you will notice some SQL Connection familiars.
  • This is not a publicly available webpart, so i will not put ALL of the details of how this works, just the sections that pertain to the problem i got solved and the difference from Mathieu's implementation.
  • Currently this is Configure Once, and done. I am working on a Edit Configuration once it has been configured.

Design: WebPart.ascx

<asp:Panel ID="pnlView" Visible="true" runat="server">
<p id="txtContent" class="SQLTextContent reaonly" runat="server"></p>
<asp:Panel ID="pnlEdit" Visible="false" runat="server">
<asp:Panel ID="pnlEditConfig" Visible="false" runat="server">
<p>WebPart has been configured</p>
<asp:Panel ID="pnlEditNoConfig" Visible="true" runat="server">
<h3>SQLText WebPart Configuration:</h3>
<hr />
<td><asp:TextBox ID="txtEditServer" runat="server" /></td>
<td><asp:TextBox ID="txtEditDatabase" runat="server" /></td>
<td><asp:TextBox ID="txtEditSchema" runat="server" /></td>
<td><asp:TextBox ID="txtEditSProc" runat="server" /></td>
<tr style="border-top: 1px solid black">
<td><asp:Button ID="btnSubmit" Text="Submit" runat="server" /></td>
<td><asp:Button ID="btnClear" Text="Clear" runat="server" /></td>

Logic:  WebPart.ascx.cs:

public bool PropertiesSet () {
return ( !string.IsNullOrWhiteSpace( Server )
&& !string.IsNullOrWhiteSpace( Database )
&& !string.IsNullOrWhiteSpace( Schema )
&& !string.IsNullOrWhiteSpace( SProc ) );

protected void Page_Load ( object sender , EventArgs e ) {
	switch ( SPContext.Current.FormContext.FormMode ) {
		case SPControlMode.Display:
			pnlEdit.Visible = false;
			pnlView.Visible = true;

			//Make sure ALL properties have a value before trying to execute.
			if ( !PropertiesSet() ) {
				txtContent.InnerText = "WebPart Not Configured!";
			} else {
				txtContent.InnerText = "";
				_conn = new SqlConnectionStringBuilder() {
				_cmd = Schema + '.' + SProc;

				var conn = new SqlConnection( _conn );
				try {

					var cmd = new SqlCommand( _cmd , conn ) {
						CommandType = System.Data.CommandType.StoredProcedure

					using ( var rdr = cmd.ExecuteReader() ) {
						while ( rdr.Read() ) {
							txtContent.InnerText += rdr[ "result" ] + Environment.NewLine;
				} catch ( Exception ex ) {
					txtContent.InnerHtml += "Please take a ScreenShot!<br/>" +
					"Connection String: " + _conn + "<br/>" +
					"Unable to connect to the SQL Server.<br/>" + 
					ex.Message + "<br/>" + 
				} finally {
		case SPControlMode.Edit:
			pnlView.Visible = false;
			pnlEdit.Visible = true;

			if ( PropertiesSet() ) {
				pnlEditConfig.Visible = true;
				pnlEditNoConfig.Visible = false;
			} else {
				pnlEditConfig.Visible = false;
				pnlEditNoConfig.Visible = true;

				if ( PropertiesSet() ) {
					txtEditServer.Text = Server;
					txtEditDatabase.Text = Database;
					txtEditSchema.Text = Schema;
					txtEditSProc.Text = SProc;

				btnSubmit.Click += btnSubmit_Click;
				btnClear.Click += btnClear_Click;

		//case SPControlMode.Invalid:
		//	break;
		//case SPControlMode.New:
		//	break;


void btnClear_Click ( object sender , EventArgs e ) {
	txtEditServer.Text = Server;
	txtEditDatabase.Text = Database;
	txtEditSchema.Text = Schema;
	txtEditSProc.Text = SProc;

void btnSubmit_Click ( object sender , EventArgs e ) {
	Server = txtEditServer.Text;
	Database = txtEditDatabase.Text;
	Schema = txtEditSchema.Text;
	SProc = txtEditSProc.Text;


	pnlEditNoConfig.Visible = false;
	pnlEditConfig.Visible = true;

Now to the difference:
Mathieu had done most of his implementation in the CreateChildControls event for the control, whereas i preformatted mine in the HTML code of the control and then performed the logic evaluation in the Page_Load event handler.

One word of advise: DO NOT execute SetPersonalizationDirty from the WebPart class (like this.SetPersonalizationDirty()), cause for some reason the Page object will not recognize the execution.

Access 2010 - Dynamic CrossTab Form

Permalink 08/16/12 08:19, by admin, Categories: Microsoft, Office, 2010, VBA


  1. Create your Static/Dynamic Cross-Tab Query ('ctQuery')
  2. Create a SQL Statement that selects what will most likely be a null dataset for values but will create a Field Defition.
    • Select * From ctQuery Where FALSE
  3. Create a Blank Form:
    1. Make sure the form does not have its RecordSource defined
    2. Set the default view to DataSheet
    3. Make sure all other Views are disabled
    4. be continued

Iterating through Workbook Tables

Permalink 04/24/12 09:51, by admin, Categories: Microsoft, Office, 2007, VBA , Tags: excel 2007, excel vba, vba 2007

Nothing special just want to put a culmination of information available out there into one useful code-snippet

Dim wb As Workbook
Set wb = ActiveWorkbook
Dim ws As Worksheet
Dim lo As ListObject
Dim lc As ListColumn
Dim lr As ListRow
Dim frm As New frmListRanges
Dim txt As String

For Each ws In wb.Worksheets
For Each lo In ws.ListObjects
txt = ""
For Each lr In lo.ListRows
txt = "{ "
For Each lc In lo.ListColumns
txt = txt & " " & CStr(lo.DataBodyRange.Cells(lr.Index, lc.Index).Value) & ","
Next lc
Trim (txt)
If StrComp(Right(txt, 0), ",", vbTextCompare) Then
txt = Left(txt, Len(txt) - 1)
End If
txt = txt & " }"
frm.AddNamedRange ("WS: " & ws.Name & " - LO: " & lo.Name & " " & txt)
Next lr
Next lo
Next ws


Iterating through Excel Named Ranges

Permalink 04/24/12 09:49, by admin, Categories: Microsoft, Office, 2007, VBA , Tags: excel 2007, excel vba, vba 2007

Simple code to iterate through named ranges

Dim fm as Form
Dim nm As Name
Dim val As Double

For Each nm In ActiveWorkbook.Names
'Check MacroType and Sheet References (in Name and Value)
'If (nm.MacroType < 0 And InStr(1, nm.Name, "!", vbTextCompare) = 0 And InStr(1, nm.Value, "!", vbTextCompare) = 0) Then 'If (nm.MacroType < 0 And InStr(1, nm.Name, "!", vbTextCompare) = 0) Then If (nm.MacroType > 0) Then
'Is not a standard XIXLMMacroType (1, 2, 3)
frm.AddNamedRange (nm.Name & "( " & CStr(nm.Value) & " )")
'frm.AddNamedRange (nm.Name & "( " & CStr(nm.MacroType) & " )")
End If
Next nm


:: Next >>


Programming Blog about the different projects we are working on.


April 2014
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30      

XML Feeds

powered by b2evolution