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.

Moving to a new home

I have moved my site from Google site to WordPress.com and hopefully this will allow me to provide better content for my potential customers.  I am in the process of trying to bring my old posts over and hopefully will have them over this weekend.  Until then, please excuse the empty cupboards.

Bikes for Kids

During the school year, I was proud to support the 2nd annual Bikes for Kids project sponsored by the Eastern Henrico Business Association.  As and incentive to prepare for their SOL exams, the business association is looking to secure two (2) boys bikes and two (2) girls bikes and helmets for each of the elementary schools in the Varina district.   SQL Data Partners has been able to contribute one bike and helmet to this cause.

As a result of the tremendous success of this program, Henrico County and EHBA are looking to expand the program to all the districts in the county.

More information about the project can be found at the EHBA website.

http://www.ehba.us/bikes-for-kids

Grant access to tables

Often, when setting up database security where it hadn’t been before, there is a need to assign users to roles and then grant those roles permission to tables.  I was tasked with granting read access to all the tables in the database for some reporting users.  These users have access to all tables in the database so I didn’t need to exclude any.  Because I didn’t want to write out all the grant statements, I took advantage of the information_schema views using tables option.
  Select   ‘GRANT SELECT ON ‘+ Table_Name +‘ TO Reader_Role’
FROM
information_schema.tables
I change my query output to text and voilà, I have the code I need to grant to the role.

Using SSIS to write to SharePoint

I was experimenting with the new (to me) SharePoint Source and Destination utilities available in SSIS and came across this error

Error: 0×0 at Data Flow Task, SharePoint List Destination: Error on row ID=”2″: 0×81020016 – List item referred to in the request does not exist.

What I was trying to do was write a result set to a SharePoint List.  What I didn’t realize, was in the column mapping if you want to INSERT a row into Sharepoint you can’t map the ID column, you have to let SharePoint assign that itself.  It is when you update that you map the SharePoint ID column.

This example will all the Records in my source table to be inserted into a list.  If I wanted to update a record, I map the ID column and all the columns that I map would update with the source data.

Column Mapping to SharePoint ListWhat is a little weird is that if you want to keep the source table and the SharePoint list in synch, the ID columns have to match up or when you update the record, the source table will override what is in the SharePoint list.  For my solution, it was not a big deal, but I could see it biting me one day.

IIS Log Import

A client asked me about extracting some data from an IIS log.  I thought it would be straightforward; however, the trick was the file is delimited in two ways.  Most of the columns are tab delimted, but one column–cs-uri-query is delimited with an ampersand(&) and a column header.   Parsing was going to be difficult.

I think did a little searching and came across Microsoft’s LogParser Tool.   It seemed like the right fit; however, I wanted to load the data into a database as we would need to do other manipulations to it.  I also found that the LogParser tool was fairly snappy and I could import my file in about 20 minutes.  Other tools/ways I tried took much longer–one lasted over 12 hours.

At the end of the day, this is what my batch script looked like.  I will put in the detail below.  The items in [ ] should be replaced.

c:\”program files”\”log parser 2.2″\LogParser “SELECT TO_TIMESTAMP(date, time) AS LogDate, REVERSEDNS(c-ip), cs-method, sc-bytes, time-taken, cs-uri-stem, cs-username, cs(user-agent), Extract_Value(cs-uri-query,’DeviceId’) AS DeviceId, Extract_Value(cs-uri-query,’DeviceType’) AS DeviceType, Extract_Value(cs-uri-query,’Cmd’) AS Cmd, Extract_Value(cs-uri-query,’Log’) AS Log FROM [ex111231.log] TO IISLogsLP” -o:SQL -server:[SQLDB] -driver:”SQL Server” -database:[DBName] -username:[Carlos] -password:[pwd] -createtable:ON

the -o parameter allows me to insert the data directly into a table.  This is nice because I don’t have to double hop (export to another file and then import).

Date and Time are actually two seperate columns for some reason and the To_TimeStamp allow you to join the two and create ad datetime column in SQL Server.

The Extract_Value was the most valuable function for me.  I use that to extract from a column that looks like this:  User=carlos&DeviceId=XXXXXXXXXXXXXXXXXXX&DeviceType=iPhone&Cmd=Ping&Log=V121_LdapC0_LdapL0_RpcC10_RpcL123_Hb4321_Rto1_Pk987654321_S1_ 443

It then creates nice columns for me to be used for other reporting.

A list of the functions you can use with the LogParser tool can be found herehttp://logparserplus.com/Functions

Examples of other parsing can be found here.

http://felipeferreira.net/?p=823
http://support.microsoft.com/kb/296085/EN-US

I didn’t use these examples, but may in the future
http://technet.microsoft.com/en-us/library/ee692659.aspx