About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Thursday, November 04, 2010

Sending multivalue from SSRS to a stored procedure.

One, of the many, features that I wish they would add to SSRS; is an easier way to send in multivalue selected parameters to a stored procedure.

The best solution to this problem, is to use a function that converts a string of delimited values into a table.

 Here are the quick low down steps:

1) Go to your SSRS dataset properties -> Parameters -> (select the multivalue parameter function [fx]) and change the parameter to send in a sting of delimited values. For example,

=Join(Parameters!MeasurementID.Value,",")

2) Create the function that splits and creates a table:

CREATE FUNCTION dbo.fn_charlist_to_table
(
@list ntext,
@delimiter nchar(1) = N','
)
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),
nstr nvarchar(2000)) AS

BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2
  BEGIN
    SET @chunklen = 4000 - datalength(@leftover) / 2
    SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
    SET @textpos = @textpos + @chunklen
    SET @pos = charindex(@delimiter, @tmpstr)

    WHILE @pos > 0
      BEGIN
        SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
        INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
        SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
        SET @pos = charindex(@delimiter, @tmpstr)
       END
      
    SET @leftover = @tmpstr
  END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))

RETURN
END
GO

3) In your stored procedure, call the function. For example:

Select * from measurment where
measurement.MeasurementID in (select [str] from fn_charlist_to_table(@MeasurementID,Default))

Tuesday, October 05, 2010

Mimic Domain for SQL Server or other executables.

One tiresome thing about being a consultant is the lack of ability to use Windows Authentication when on site. Your computer is on a separate domain and you run into the "I need permissions" issue.

One solution to this is to use the run as command in a shortcut's target.

First create a shortcut of the executable.

For this example, I used SQL Server.

C:\Windows\System32\runas.exe /netonly /user:consultantDomain\wandrus "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

The highlighted yellow is what was added, where I set the domain\user_name to what I would like to use. When this shortcut is then clicked, it will prompt for your password.

 

Wednesday, April 14, 2010

Only display # rows per page in a SSRS report.

One problem I ran into and tried every solution on the net (that I could find) with no luck. I had to display only 22 rows per page, with blank rows -- if the data is not filled in for the other rows.

Step 1: SQL
So, first thing is to get your sql query to return, not row numbers but page numbers. So in this case, I used the row_number function found in SQL-Server subtracting 1 and then divided by the number of rows I needed per page:

((row_number() over(order by license_no)) -1) /22 as 'Page'

Step 2: Add Parent Group
In my report I needed to display a header, a footer, detail information, and empty rows if not exceeding 22. This is of course done with SSRS's Table.

First add a new parent group and in the Group Properties/General add a group on to the "Page" field. This group should span the whole page.

Don't bother with the page break sections, this will just mess things up.

Step 3: Add Child Group with blank rows
The child group is what I used to hold the blank rows.  Within that group I added 22 rows with no information in the textboxes.



Step 4: Change visibility of blank rows
Right-clicking on the row, and select Row Visibility. Select the bullet: "Show or hide based on an expression". Within each row the expression will check if the row count is less than the ((page + 1) * rows_per_page) - (rows_per_page - position)
Example (Note:"section2" is my dataset):
[Row 22 Expression]: =IIF(CountRows("Section2") < ((Fields!Page.Value + 1) * 22),false,true)
[Row 21 Expression]: =IIF(CountRows("Section2") < ((Fields!Page.Value + 1) * 22) - 1,false,true)
...


This is all that is needed to acomplish this task.

If you need to hide a row until the end, I used the following visibility expression:
=IIF(Fields!Page.Value = Last(Fields!Page.Value,"Section2"),false,true)

And of course if you need to repeat the header on each page, like I do. This is found in the Tablix Properites/General and just put a check mark in the "Repeat header rows on each page".

Friday, February 19, 2010

Precision Timer

Ran into a problem yesterday wth timing issues, this is the first time I ever needed to create a timer with extreme precision.

Note: The precision of multithreaded timers depends on the operating system, and is typically in the 10-20 milliseconds region. This class is used to generate greater precision using the P/Invoke interop and calls the Windows multimedia timer; which has a precision of 1 ms. But that increased responsiveness comes at a cost - since the system scheduler is running more often, the system spends more time scheduling tasks, context switching, etc. This can ultimately reduce overall system performance, since every clock cycle the system is processing "system stuff" is a clock cycle that isn't being spent running your application.


So here is some code I found and changed up a bit that uses the winmm.dll timesetevent:

Code Snippet
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Runtime.InteropServices;
  6. using System.Diagnostics;
  7.  
  8. public class PrecisionTimer : IDisposable
  9. {
  10.  
  11.     //Lib API declarations
  12.     [DllImport("Winmm.dll", CharSet = CharSet.Auto)]
  13.     static extern uint timeSetEvent(uint uDelay, uint uResolution, TimerCallback lpTimeProc, UIntPtr dwUser, uint fuEvent);
  14.  
  15.     [DllImport("Winmm.dll", CharSet = CharSet.Auto)]
  16.     static extern uint timeKillEvent(uint uTimerID);
  17.  
  18.     [DllImport("Winmm.dll", CharSet = CharSet.Auto)]
  19.     static extern uint timeGetTime();
  20.  
  21.     [DllImport("Winmm.dll", CharSet = CharSet.Auto)]
  22.     static extern uint timeBeginPeriod(uint uPeriod);
  23.  
  24.     [DllImport("Winmm.dll", CharSet = CharSet.Auto)]
  25.     static extern uint timeEndPeriod(uint uPeriod);
  26.  
  27.     //Timer type definitions
  28.     [Flags]
  29.     public enum fuEvent : uint
  30.     {
  31.         TIME_ONESHOT = 0, //Event occurs once, after uDelay milliseconds.
  32.         TIME_PERIODIC = 1,
  33.         TIME_CALLBACK_FUNCTION = 0x0000, /* callback is function */
  34.  
  35.         //TIME_CALLBACK_EVENT_SET = 0x0010, /* callback is event - use SetEvent */
  36.  
  37.         //TIME_CALLBACK_EVENT_PULSE = 0x0020 /* callback is event - use PulseEvent */
  38.     }
  39.  
  40.     //Delegate definition for the API callback
  41.     delegate void TimerCallback(uint uTimerID, uint uMsg, UIntPtr dwUser, UIntPtr dw1, UIntPtr dw2);
  42.  
  43.     private fuEvent f;
  44.     private uint ms;
  45.  
  46.     //IDisposable code
  47.     private bool disposed = false;
  48.  
  49.     public void Dispose()
  50.     {
  51.         Dispose(true);
  52.         GC.SuppressFinalize(this);
  53.     }
  54.  
  55.     private void Dispose(bool disposing)
  56.     {
  57.         if (!this.disposed)
  58.         {
  59.             if (disposing)
  60.             {
  61.                 Stop();
  62.             }
  63.         }
  64.         disposed = true;
  65.     }
  66.  
  67.     ~PrecisionTimer()
  68.     {
  69.         Dispose(false);
  70.     }
  71.  
  72.     ///
  73.     /// The current timer instance ID
  74.     ///
  75.     uint id = 0;
  76.  
  77.     ///
  78.     /// The callback used by the the API
  79.     ///
  80.     TimerCallback thisCB;
  81.  
  82.     ///
  83.     /// The timer elapsed event
  84.     ///
  85.     public event EventHandler Timer;
  86.  
  87.     protected virtual void OnTimer(EventArgs e)
  88.     {
  89.         if (Timer != null)
  90.         Timer(this, e);
  91.     }
  92.  
  93.     ///
  94.     /// Initialize
  95.     ///
  96.     ///
  97.     ///
  98.     public PrecisionTimer(uint ms, bool repeat)
  99.     {
  100.         //Initialize the API callback
  101.         thisCB = CBFunc;
  102.  
  103.         this.ms = ms;
  104.  
  105.         //Set the timer type flags
  106.         f = fuEvent.TIME_CALLBACK_FUNCTION(repeat ? fuEvent.TIME_PERIODIC : fuEvent.TIME_ONESHOT);
  107.  
  108.         //Tell OS that we are about to need a precision timer.
  109.         PrecisionTimer.timeBeginPeriod(1);
  110.     }
  111.  
  112.     ///
  113.     /// Stop the current timer instance
  114.     /// VERY IMPORTANT TO CALL
  115.     ///
  116.     public void Stop()
  117.     {
  118.         lock (this)
  119.         {
  120.             if (id != 0)
  121.             {
  122.                 timeKillEvent(id);
  123.                 Trace.WriteLine("Timer " + id.ToString() + " stopped " + DateTime.Now.ToString("HH:mm:ss.ffff"));
  124.                 id = 0;
  125.             }
  126.         }
  127.  
  128.         //Tell OS that we are done using the precision timer and that it can continue back to normal.
  129.         PrecisionTimer.timeEndPeriod(1);
  130.     }
  131.  
  132.     ///
  133.     /// Start a timer instance
  134.     ///
  135.     /// Timer interval in milliseconds
  136.     /// If true sets a repetitive event, otherwise sets a one-shot
  137.     public void Start()
  138.     {
  139.         //Kill any existing timer
  140.         //Stop();
  141.  
  142.         lock (this)
  143.         {
  144.             id = timeSetEvent(ms, 0, thisCB, UIntPtr.Zero, (uint)f);
  145.             if (id == 0)
  146.                 throw new Exception("timeSetEvent error");
  147.             Trace.WriteLine("Timer " + id.ToString() + " started " + DateTime.Now.ToString("HH:mm:ss.ffff"));
  148.         }
  149.     }
  150.  
  151.     void CBFunc(uint uTimerID, uint uMsg, UIntPtr dwUser, UIntPtr dw1, UIntPtr dw2)
  152.     {
  153.         //Callback from the PrecisionTimer API that fires the Timer event. Note we are in a different thread here
  154.         OnTimer(new EventArgs());
  155.     }
  156. }




An example of calling the class:


PrecisionTimer timer = new PrecisionTimer(8500, false); //will time for 8.5 seconds before triggering an event
timer.Timer += new EventHandler(timer_Timer);

timer.Start();
//Do Stuff or something until event


My event handler

void timer_Timer(object sender, EventArgs e){

timer.Stop();

DoStuff();

}

Monday, January 11, 2010

List Creator

This is one of my favorite pieces of code I've stolen borrowed from another site. I've had made some minor code changes and additions from the original, but basically I just need to send in a DataReader after running a sql command and it will return a list of objects of that type. (Very similar to Linq to Sql)

You just need to make sure that anything that is return through the query is defined in the object as a write property.

So here is the List Creator code, which does the creation via reflections:

Code Snippet
  1. public class ListCreator where T : new()
  2. {
  3.      public List FindAll(IDataReader iDataReader)
  4.      {
  5.          List returnList = new List();
  6.  
  7.          try
  8.          {
  9.              //need a Type and PropertyInfo object to set properties via reflection
  10.              Type tType = new T().GetType();
  11.              PropertyInfo pInfo;
  12.  
  13.              //x will hold the instance of until it is added to the list
  14.              T x;
  15.  
  16.              //use reader to populate list of objects
  17.              while (iDataReader.Read())
  18.              {
  19.                  x = new T();
  20.  
  21.                  //set property values
  22.                  //for this to work, command’s column names must match property names in object
  23.                  for (int i = 0; i < iDataReader.FieldCount; i++)
  24.                  {
  25.                      pInfo = tType.GetProperty(iDataReader.GetName(i));
  26.  
  27.                      pInfo.SetValue(x, (iDataReader[i] == DBNull.Value? null:iDataReader[i]), null);
  28.                  }
  29.  
  30.                  //once instance of is populated, add to list
  31.                  returnList.Add(x);
  32.              }
  33.          }
  34.          catch (Exception ex)
  35.          {
  36.              Logging.Logging.Error(ex.ToString());
  37.          }
  38.  
  39.          return returnList;
  40.      }
  41. }




So in my DAL I might have something like:

Code Snippet
  1. public List<PartsOrdering> GetAllPartsOrdering()
  2. {
  3.    List<PartsOrdering> li = new List<PartsOrdering>();
  4.    try
  5.    {
  6.       using (SqlConnection cn = new SqlConnection(connString))
  7.       {
  8.           ListCreator<PartsOrdering> PartsOrders = new ListCreator<PartsOrdering>();
  9.           SqlCommand cmd = new SqlCommand("dbo.selPartsOrdering", cn);
  10.           cmd.CommandType = CommandType.StoredProcedure;
  11.           cn.Open();
  12.           li = PartsOrders.FindAll(ExecuteReader(cmd));
  13.       }
  14.   }
  15.   catch (Exception ex)
  16.   {
  17.      Logging.Error(ex.ToString());
  18.   }
  19.   return li;
  20. }

Code Snippet
  1. [Serializable]
  2. public class PartsOrdering : IFormattable
  3. {
  4.     #region Properties
  5.     [DefaultValue(-1)]
  6.     public int PartsOrderingID
  7.     {
  8.         get;
  9.         set;
  10.     }
  11.     [DefaultValue("")]
  12.     public string ToolID
  13.     {
  14.         get;
  15.         set;
  16.     }
  17.     [DefaultValue("")]
  18.     public string PartNumber
  19.     {
  20.         get;
  21.         set;
  22.     }
  23.     [DefaultValue("")]
  24.     public string Description
  25.     {
  26.         get;
  27.         set;
  28.     }
  29.     [DefaultValue(0)]
  30.     public int Quantity
  31.     {
  32.         get;
  33.         set;
  34.     }
  35.     [DefaultValue(1)]
  36.     public int PartsOrderingLocationID
  37.     {
  38.         get;
  39.         set;
  40.     }
  41.     [DefaultValue(1)]
  42.     public int PartsOrderingUrgencyID
  43.     {
  44.         get;
  45.         set;
  46.     }
  47.     [DefaultValue("")]
  48.     public string Comment
  49.     {
  50.         get;
  51.         set;
  52.     }
  53.     public DateTime RequestedDateTime
  54.     {
  55.         get;
  56.         set;
  57.     }
  58.     [DefaultValue("")]
  59.     public string RequestedUser
  60.     {
  61.         get;
  62.         set;
  63.     }
  64.     public string LocationName
  65.     {
  66.         get
  67.          {
  68.              List&lt;PartsOrderingLocation&gt; locations = new PartsOrderingLocation().List();
  69.              return locations.Where(n =&gt; n.PartsOrderingLocationID.Equals(this.PartsOrderingLocationID)).Select(n =&gt; n.Name).First();
  70.          }
  71.         set
  72.          {
  73.              List&lt;PartsOrderingLocation&gt; locations = new PartsOrderingLocation().List();
  74.              this.PartsOrderingLocationID = locations.Where(n =&gt; n.Name.Equals(value)).Select(n =&gt; n.PartsOrderingLocationID).First();
  75.          }
  76.  
  77.     }
  78.     public string UrgencyName
  79.     {
  80.         get
  81.          {
  82.              List&lt;PartsOrderingUrgency&gt; locations = new PartsOrderingUrgency().List();
  83.              return locations.Where(n =&gt; n.PartsOrderingUrgencyID.Equals(this.PartsOrderingUrgencyID)).Select(n =&gt; n.Name).First();
  84.          }
  85.         set
  86.          {
  87.              List&lt;PartsOrderingUrgency&gt; locations = new PartsOrderingUrgency().List();
  88.              this.PartsOrderingUrgencyID = locations.Where(n =&gt; n.Name.Equals(value)).Select(n =&gt; n.PartsOrderingUrgencyID).First();
  89.          }
  90.     }
  91.     public int UrgencyRank
  92.     {
  93.         get
  94.          {
  95.              List&lt;PartsOrderingUrgency&gt; locations = new PartsOrderingUrgency().List();
  96.              return locations.Where(n =&gt; n.PartsOrderingUrgencyID.Equals(this.PartsOrderingUrgencyID)).Select(n =&gt; n.Rank).First();
  97.          }
  98.     }
  99.     [DefaultValue(-1)]
  100.     public int PartsOrderingGroupID
  101.     {
  102.         get;
  103.         set;
  104.     }
  105.     [DefaultValue(true)]
  106.     public bool IsActive
  107.     {
  108.         get;
  109.         set;
  110.     }
  111. }







Friday, January 08, 2010

How to update large count of rows without locking them

Found this interesting, how to update large databases, so that you don't have to lock rows
http://blogs.msdn.com/sqlpfe/archive/2010/01/06/tsql-coding-patterns-i.aspx

So instead of the usual:


 UPDATE dbo.Foo
SET Column = 'Value'


Try:


DECLARE @UpdatedRows(PK_Id int NOT NULL PRIMARY KEY)
DECLARE @var INT
SELECT @var=0 -- this resets @@ROWCOUNT=1


WHILE @@ROWCOUNT >0
BEGIN
UPDATE TOP(1500) BAR
  SET Column='Value'
OUTPUT inserted.PK_ID
INTO  @UpdatedRows
FROM  dbo.BAR as BAR
WHERE NOT EXISTS (SELECT 1 FROM @UpdatedRows UPD WHERE UPD.PK_ID=BAR.PK_ID)
END



I prefer one of the ways the commentator offered: (easier to follow)

WHILE @@ROWCOUNT >0
BEGIN
 UPDATE TOP(1500) BAR
   SET Column='Value'
 WHERE
   Column <> 'Value'
END