sp_executesql

I don’t normally have a need to do dynamic sql; however, I found myself at a client site and we were doing a dynamic query where we wanted to capture the return value.  They dynamic nature of the query was giving me grief with the Exec command and this led me the use sp_executesql.  This may be old hat for some, but it was new to me.  The syntax is fairly straight forward.

DECLARE @RetrunVariable INT

DECLARE @SQL NVARCHAR (4000)

DECLARE @ParamDefinition nvarchar(500)

SET @SQL = N’Select @RetrunVariable=Column FROM Table where column = x’

Set @ParamDefinition =N’@TimeIDOut INT OUTPUT’

Exec sp_executesql @sql, @ParamDefinition, @TimeIDOut = @RetrunVariable OUTPUT;

What took me a moment to get was I declare a variable in the @ParamDefinition to retrieve the variable I am passing back and the variable must be assigned first in the exec line.  For example, we had @RetrunVariable = @TimeIDOut and that did not work.  Only when we switched the variables did we get our required result.

Not all operating sytem builds are equal

I was trying to build out a virtual machine for my SQL Azure presentation to the Richmond SQL Server Users Group and I realized something I had taken for granted–ALL OS downloads from Microsoft’s MSDN are not created equally.

I was using Oracle’s VM Virtualbox software and wanted to install SQL 2012 to use with my demo.  It was actually my first time installing SQL 2012 and while I had given a quick glace at the install reqs, I wasn’t too worried because you know–it was for a demo and I have lots of experience installing 2005 and 2008.  For the OS, I was using the en_windows_server_2008_r2_standard_enterprise_datacenter_and_web_with _sp1_debug_checked_build_x64_dvd_619600  edition/version for the OS.  No problem I thought–it is SP1_debug_checked.  Must be good.  This was a little too optimistic.

The installation of the OS went fine and I was able to install items from the server role console and I updated with all the latest OS patches, etc.  When I went to install SQL Server, everything seemed fine until it tried to install .NET framework 4.  Everything went to pieces.  It gave me the following error: .Net framework is unable to install.  That was it–and searching through the logs didn’t give me hints(that I could decifer) either.

I searched the web and tried all the following options

  • Installing the .net framework before I installed SQL 2012
  • downloading the .net framework again
  • Modifing the registry to add entries to ‘trick’ the .net install
  • Installing fewer items in SQL Server, but found the engine itself needs it.
  • Installing a different version of SQL Server (enterprise, standard and development) en_sql_server_2012_enterprise_edition_x86_x64_dvd_813294
  • Installing a different build/version of SQL Server en_sql_server_2012_standard_edition_x86_x64_dvd_813403
  • Hopping on one foot while the install was going on, which was way harder than it sounds.  At least it left me breathless, which was probably good cause I was ready to hurl a few undesirable words out.

Nothing worked.  I ran into one almost side comment that there was an issue with some OS builds that may cause this problem.  I thought no way–this has been out long enough.  Anyway, I tried everything I could think of and was about to give up when I thought–what the heck, lets try another OS version.

I downloaded the OS version en_windows_server_2008_r2_with_sp1_x64_dvd_617601 and installed with en_sql_server_2008_r2_standard_x86_x64_ia64_dvd_521546 and had NO problem whatsoever.  It was a piece of cake.
And that is the rest of the story.  :)  As, for the demo–it went well.  I actually submitted to present at the SQL Saturday in DC in December.