How to write a SQL Server Management Studio Add-in for Denali (CTP3) (Part 2)

In my previous post on writing a SSMS Add-in for Denali I detailed the steps that you would need to take to get the Add-in loaded and running but it didn’t do a great deal.

Now we take it a step further. But not too far(!)

I’ve done a number of things;

• Hooked up to the events class in DTE, so we can get a view on what’s happening inside the Add-in.
• Added some T-SQL code formatting functionality, to show how to access the contents of a query window.
• Added a context menu to the Object Explorer 
• Put all the source on Codeplex under a new project called ‘’SQL Server Management Studio Add-in for Denali (SSMS Addin)” Url: http://ssmsaddindenali.codeplex.com/ Feel free to download or/and join the project and contribute.

 

1) As we all know, SSMS Add-in’s only work because of a deep dark magic, but there are a few ways we can peek inside to see what’s happening.  

The DTE object we get during the OnConnection method has a class full of events – so lets hook them up and see what happens.

 

 

 

2)

  EnvDTE.TextDocument doc = (EnvDTE.TextDocument)ServiceCache.ExtensibilityModel.Application.ActiveDocument.Object(null);

Returns a TextDocument object from which you can get the contents of the window (SQL).

 

3) Just like previous versions add-ins, you use the same code to pick up Object Explorer events.

   1:                 objExplorerService = (ObjectExplorerService)ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService));

   2:                  cs = (ContextService)objExplorerService.Container.Components[0];

   3:                  cs.ObjectExplorerContext.CurrentContextChanged += new NodesChangedEventHandler(ObjectExplorerContext_CurrentContextChanged);

Download the source from codeplex, check it out, play with it, check it in!

http://ssmsaddindenali.codeplex.com/

SQL Relay - 3 Countries, 4 Days, 16 Events.

You wait ages for a User Group, then a whole load turn up all at once!!

Between the 3rd and 6th of October, up and down the UK the SQL community is coming together; this phenomena is known as “SQL Relay”.

Events are happening in (deep breath): Birmingham, Manchester, Farnham, Kent, Bristol, London, Hatfield, Leeds, Southampton, Edinburgh, Cardiff, Exeter, Cambridge,Essex and Maidenhead.. {and a partridge in a pear tree}.

3 Countries, 4 Days, 16 Events.

So if you have never been to a User Group or go all the time, have only just heard about SQL or are waiting for Denali to hit the streets – get along to one of the SQL Relay events.

[Updated 7/9/2010]  Itzik Ben-Gan (SQL Server Author and MVP, Co-Founder of SolidQ) will be presenting at Microsoft’s London HQ, Cardinal Place on the 6th October.

And all this right after SQLBits 9 in Liverpool - 3 days of top SQL Server content given by SQL Server Experts from around the world, including Microsoft Certified Architects (MCA), Microsoft Certified Masters (MCM) and Microsoft Most Valued Professionals (MVP).  If you have not already registered, then you better hurry!

Southampton SQL Server User Group is now a PASS chapter

The Southampton SQL Server User Group (SSSUG) has now been accepted as a PASS chapter (The Professional Association of SQL Server).

We are the 5th UG in the UK to become a chapter.

It's really Jonathan Allen’s fault. I saw his User Group up on the PASS site and thought we need to be part of this!

So what is PASS? Well, its mission statement says it all; ‘Empower the global Microsoft SQL Server and Business Intelligence community to connect, share, learn and be inspired through networking, knowledge sharing, peer-based learning, and the ability to influence the direction of Microsoft SQL Server data platform.’

It’s certainly a lot to live up to, but I’m sure we are up to it!!

How to draw a Polygon in SQL Server

In my previous post 'Drawing a circle in SQL Server (almost)' I showed how to plot 360 points to draw a circle. {Well} It looked like a circle, but to SQL Server, it was just a series of points.

We can use a LineString to draw a shape, but the most appropriate type is the Polygon.

The polygon is a closed [at least 3 sided] shape, i.e. the first point is the same as the same point. If it’s not, SQL will throw an exception. SQL Server will also throw an exception if you attempt to create a polygon with just two sides..

 

   1:  -- Drawing a polygon

   2:  declare @Sides float = 3

   3:  declare @count int = 0

   4:  declare @size int = 10

   5:  declare @firstpoint varchar(400)

   6:   

   7:  declare @sql varchar(8000);

   8:   

   9:   

  10:  set @sql =  'POLYGON((' 

  11:   

  12:  while(@count < @Sides)

  13:  begin

  14:   

  15:  set @sql = @sql + cast(SIN(RADIANS((CAST(@count AS FLOAT)

  16:              /CAST(@Sides AS FLOAT)) 

  17:              * 360)) * @size as varchar) 

  18:             + ' ' +

  19:              cast(COS(RADIANS((CAST(@count AS FLOAT)

  20:              /CAST(@Sides AS FLOAT))

  21:              * 360)) * @size as varchar) + ','

  22:   

  23:  if @count = 0

  24:  set @firstpoint  = cast(SIN(RADIANS((CAST(@count AS FLOAT)

  25:              /CAST(@Sides AS FLOAT)) 

  26:              * 360)) * @size as varchar) 

  27:             + ' ' +

  28:              cast(COS(RADIANS((CAST(@count AS FLOAT)

  29:              /CAST(@Sides AS FLOAT))

  30:              * 360)) * @size as varchar) 

  31:              

  32:  set @count = @count + 1

  33:   

  34:  end

  35:   

  36:  set @sql = @sql + @firstpoint + '))'

  37:   

  38:  print @sql

  39:  DECLARE @g geometry;

  40:  SET @g = geometry::STGeomFromText(@sql, 0);

  41:  select @g

  42:   

Simply change

   1:  declare @Sides float = 3

to be how ever many sides you need.  Simple.

Here are a few examples.

All scripts can be found on my SkyDrive using the link below.

SQL Server in the Evening (3rd Event)

Last night I did my first slot as a presenter.  I’d like to thank Gavin Payne, the event organiser for giving me the opportunity to speak.

So how I did go? Okay, I think.  It is very difficult to know how you go down until you get feedback (so if you attended, please feel free you email me your comments). 

The initial feedback was positive and incredibly useful (Thank-you Christian), its given me ideas of how to structure my next presentation… 

But back to the evening…

My presentation was on Geospatial data types in SQL Server 2008 (r2 Express) – to try and make my presentation more memorable I used props, balls for Points, string for LineString and Polygons –> I hoped that it would be different , not in code or PowerPoint would help the concepts stick..   

If you look really closely you can see the string.. (Thanks again Christian – I took no pictures all evening).

After me, Vilosh (Smart Reporting Solutions) did a demo of his BI product.

Then the buffet,  although I feel the term buffet doesn’t do justice to food.  I was bowled over by the quality and quantity.  The Ramada did a great job.

Then Jen Stirrup presented iPad and PowerPivot- Mobile Business Intelligence in Action.  A really thought provoke presentation on the future of mobile BI and BI in general.  The iPad is a game changer and a great business tool.  Check her previous and next SQLBits sessions out.

Next up was Keith Burns presenting Denali CTP3 - An overview and update.  I was excited about CTP1 of Denali, but it was a bit of a non-starter, yes, it was a new engine and new SSMS, but the cool sexy features, Crescent, Column Store indexes, file table, Juneau etc etc were all missing, but now in CTP3 – they are all there!!!!!!

I feel like it’s time to get enthusiastic about Denali again.  Keith briefly mentioned Feature pack for Denali…Check out the goodies!!

One of the great things about going to User Groups is seeing wonderful presenters talk about subjects they love, but its a fantastic place to meet up with the twitterati – people you only know by cryptic code names are real flesh and blood people.  

I’d like to thank Gavin again for putting on a great evening and I’m really looking forward to the next one.

'Denali' Community Technology Preview 3 (CTP 3) Feature Pack

I was at Keith Burn’s presentation of ‘What’s new in Denali CTP3’ at SQL Server in the Evening (Gavin Payne’s User Group) last night and I heard about Denali Feature pack.

A small about of googling with Bing later, this popped up -  ‘Feature Pack pour SQL Server "Denali" CTP3’

I had a Homer Simpson moment ”Hmmm Feature Pack...”

How to write a SQL Server Management Studio Add-in for Denali (CTP3) (Part 1)

Writing a SSMS Add-in is not for the faint hearted or those with weak constitutions, but for those who persevere the rewards are small and fleeting, yet worth while.

This post is for people who know roughly what they are doing, so without any more non-sense, here we go.

You will need;
                       Visual Studio 2010 (I have Premium - I've not tried other editions)
                       SQL Server Denali CPT3 (Should work with CTP1 and CTP2 - but I've not tried)
                      
1) Start up VS2010

2) Create a new project, a Visual Studio Add-in, you should find this under 'Other Project Types \ Extensibility'  (You may not have this project type, do not panic! I will make this source available)

3) Give the project a sensible name - like MyAddin1 :-)

4) The Add-in Wizard should start, 'Click Next'

5) Pick you language of choice, I picked 'Create an Add-in  using C#' (C++/CLR and C++/ ATL are both greyed out for me) - Click Next.

6) Un-Select 'Microsoft Visual Studio 2010 Marcos' - Click Next.

7) Give your Add-in a sensible name and description. - Click Next.

8) Tick 'Yes, create a Tools menu Item...' and 'I would like my Add-in to load...' - Click Next.

9) Its up to you if you want a 'Help About' box. - Click Next.

10) Click Finish.


You are almost ready, we just need to sort out the Add-in registration.

Your new Add-in will be already setup to be a Visual Studio 2010 Add-in.
We just need to customise it a bit, so that SSMS recognises it.

11) VS2010 has created you two .Addin files, delete the 'MyAddin1 - For Testing.AddIn'

12) Locate the 2nd .Addin file, it will be somewhere like; c:\users\XXXX\documents\visual studio 2010\Projects\addin\MyAddin1\MyAddin1\MyAddin1.AddIn
Move it to C:\Users\Public\Documents\microsoft\MSEnvShared\AddIns
This is the folder that SSMS is looking for Add-in files.
[Update 19/08/2011: You may need to create the MSEnvShared\AddIns path.  Thanks to Raphael for spotting this]

[Update 19/05/2012: For the final release of SQL Server 2012, the .Addin file must go in the C:\ProgramData\Microsoft\MSEnvShared\AddIns folder]13) You can now delete the MyAddin1.Addin from your project.

14) Edit the C:\Users\Public\Documents\microsoft\MSEnvShared\AddIns\MyAddin1.Addin' file.
Change the top section from :

<HostApplication>   <Name>Microsoft Visual Studio</Name>
    <Version>10.0</Version>
</HostApplication> 

to be:

<HostApplication>   <Name>Microsoft SQL Server Management Studio</Name>
    <Version>*</Version>
</HostApplication> 

and

Update the Assembly to be the full path to your DLL.

<Assembly>c:\users\XXXXX\documents\visual studio 2010\Projects\addin\MyAddin1\MyAddin1\bin\Debug\MyAddin1.dll </Assembly>

14) Update the Project Properties, in the Debug section, update Start external program to be C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe (Of course, update this if you have installed Denali somewhere else)

15) Turn off P-Invoke warnings. Press CRLT + ALT + E - In Managed Debugging Assistants, find PInvokeStackImbalance, untick it.

16) Add a break point to the Connect() line, Hit F5 and run for the hills - because you should have a SQL Server Management Studio Add-in working.

The Add-in doesn't do very much yet, I will save that for another post. 

But you have an Add-in running, which was easier to write than Add-ins for SQL 2005 and 2008.

The only tricky, bit was the location and contents of the .Addin file.

Have fun writing your own Add-ins!!

[Updated 19/08/2011: I’ve got to say thanks for Mladen Prajdić for the telling me what the version needs to be in the XML]

Can not connect to SQL Server remotely

Having trouble connecting to a new instance of SQL on a new Windows 2008 install?

Took me longer to work out than I care to admit.

I checked all the basics, allow remote connections, browser service running etc.etc..

Answer: Turn off Windows Firewall!! DOH! (Or at least add the SQL ports to the allowed list!)

SQL Server User Group in Southampton 10th August

The next Southampton SQL Server User Group is on the 10th of August.

Neil Hambly (@Neil_Hambly | web ) and John Martin (@SQLServerMoney) are both presenting - for more details check out the SQLServerFAQ.com website.

See you there!!