sp_searchit

USE master
GO

ALTER PROCEDURE [dbo].[sp_searchit] ( @val varchar(255), @escape char(1) = NULL, @table sysname = NULL )
AS
BEGIN
   SET NOCOUNT ON

   DECLARE @cols TABLE ( id int identity(1,1), colname sysname, tabname sysname )
   DECLARE @results TABLE ( id int identity(1,1), colval varchar(max), colname sysname, tabname sysname )

   DECLARE @id int
         , @colname sysname
         , @tabname sysname
         , @sql nvarchar(4000)

     INSERT INTO @cols ( colname, tabname )
          SELECT A.name "colname"
               , B.name "tabname"
            FROM syscolumns A (NOLOCK)
      INNER JOIN sysobjects B (NOLOCK)
              ON A.id = B.id
           WHERE A.type IN ( SELECT B.type FROM systypes B (NOLOCK) WHERE B.name IN ( 'char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext', 'sysname' ) )
             AND B.type = 'U'
             AND ( @table IS NULL OR B.name = @table )

          SELECT @id = max(id) 
            FROM @cols

   WHILE @id > 0
    BEGIN
      SELECT @colname = A.colname
           , @tabname = A.tabname
        FROM @cols A
       WHERE A.id = @id

      SET @sql = 'SELECT TOP 1 [' + @colname + '] "result", ''' + @colname + ''' "column", ''' + @tabname + ''' "table" FROM [' + @tabname + '] WHERE [' + @colname + '] LIKE ''%' + @val + '%'''
      IF @escape IS NOT NULL
       BEGIN
         SET @sql = @sql + ' ESCAPE ''' + @escape + ''''
       END

      INSERT INTO @results ( colval, colname, tabname )
      EXEC sp_executesql @sql

      SET @id = @id - 1
    END

          SELECT * 
            FROM @results
END --sp_searchit

GO

'The SECRET' to fast SSIS packages!

Nope, you don't need no stinkin' law of attraction, yoga, contemplative breath prayers, 12 steps to paganism, or 3 points and a poem to follow the road to enlightened performance.  Just follow this simple formula:


Step 1: Rewrite them as stored procedures...

c# display SqlCommand statement

      public static string cmdToString(SqlCommand cmd)
      {
         var c = " ";
         var s = cmd.CommandText;
         for (int i = 0; i < cmd.Parameters.Count; i++)
         {
            s += c + cmd.Parameters[i].ParameterName;
            s += " = " + cmd.Parameters[i].Value;
            c = ", ";
         }
         return (s);
      }

Views in Drag

Ran into a real winner today.  For a completely unexpected reason, several views began timing out and not returning results.

The only thing that worked was recreating the views.

I suspect a possible cause is that the views reference synonyms that in turn reference objects across a linked server.  The linked server options had been modified right before the views stopped working.

Dedupe based on compound key and timestamp

DECLARE @sourcetab TABLE ( id1 int, id2 int, update_on smalldatetime, val varchar(50) )

DECLARE @targettab TABLE ( id1 int, id2 int, update_on smalldatetime, val varchar(50) )


insert into @sourcetab (id1, id2, update_on, val)
      SELECT 10, 1, '20120110', 'testrec1'
UNION SELECT 10, 1, '20120201', 'testrec2'
UNION SELECT 5, 2, '20120201', 'testrec3'
UNION SELECT 5, 1, '20120201', 'testrec4'
UNION SELECT 5, 1, '20120205', 'testrec5'
UNION SELECT 12, 18, '20120201', 'testrec6'
UNION SELECT 12, 18, '20120205', 'testre7'
UNION SELECT 12, 5, '20120201', 'testrec8'
UNION SELECT 17, 3, '20120201', 'testrec9'
UNION SELECT 18, 4, '20120201', 'testrec10'


insert into @targettab (id1, id2, update_on, val)
      SELECT 10, 1, '20120101', 'testrec01'
UNION SELECT 12, 5, '20120101', 'testrec02'
UNION SELECT 20, 19, '20120101', 'testrec03'


--not deduped
          SELECT A.id1, A.id2, A.update_on, A.val  
            FROM @sourcetab A
        ORDER BY A.id1, A.id2, A.update_on


--deduped with most current wins
          SELECT A.id1, A.id2, A.update_on, A.val 
            FROM @sourcetab A
           WHERE A.update_on = ( SELECT max(A1.update_on)
                                   FROM @sourcetab A1
                                  WHERE A1.id1 = A.id1
                                    AND A1.id2 = A.id2 )


--update with most recent
          UPDATE A
             SET A.val = B.val
               , A.update_on = B.update_on
            FROM @targettab A
      INNER JOIN @sourcetab B
              ON A.id1 = B.id1 
             AND A.id2 = B.id2
           WHERE B.update_on > A.update_on
             AND B.update_on = ( SELECT max(B1.update_on)
                                   FROM @sourcetab B1
                                  WHERE B1.id1 = B.id1
                                    AND B1.id2 = B.id2 )



--insert new recs

     INSERT INTO @targettab ( id1, id2, update_on, val )
          SELECT A.id1, A.id2, A.update_on, A.val 
            FROM @sourcetab A
           WHERE A.update_on = ( SELECT max(A1.update_on)
                                   FROM @sourcetab A1
                                  WHERE A1.id1 = A.id1
                                    AND A1.id2 = A.id2 )
             AND NOT EXISTS ( SELECT TOP 1 1 
                                FROM @targettab B
                               WHERE B.id1 = A.id1
                                 AND B.id2 = A.id2 )


--final result
          SELECT A.* 
            FROM @targettab A
        ORDER BY A.id1, A.id2, A.update_on

Passing parameterized string to OpenQuery

OpenQuery can only accept a static string as an argument for the sql statement to execute over the remote link. The only way around this is to execute the OpenQuery as a dynamic sql string and concatenate the parameters as part of the "static" string. The results can then be piped into a temp table or table variable. The trick is that "SELECT INTO" will not work, so the table has to be defined beforehand to match the result set of the dynamic sql and use "INSERT INTO". The most obnoxious bit is the crazy nested tick-quotes...

DECLARE @mycodes AS TABLE (
 [id] [int] NOT NULL,
 [code] [varchar](25) NULL,
 [description] [varchar](80) NULL
)

declare @sql nvarchar(4000)
      , @myparm varchar(255)

SET @myparm = 'someval'

SET @sql = 'select id, code, description from openquery([MYDBLINK],''EXEC mydb.dbo.mystoredproc @myparm = '''' + @localval + '''''' )'


print @sql

insert into @codes 
EXEC sp_executesql @sql

select * from @codes

MonoTouch Bare Bones

using System;

using MonoTouch.Foundation;

using MonoTouch.UIKit;

namespace myNameSpace

{

   public class Application

   {

      static void Main(string[] args)

      {

         UIApplication.Main(args, null, "AppController");

      }

   }

   [Register ("AppController")]

   public class AppController : UIApplicationDelegate 

   {

      UIWindow window;

      public override bool FinishedLaunching(UIApplication app, NSDictionary options)

      {

         // create the main view controller

         var vc = new MainViewController();

         // create main window and add main view controller as subclass

         window = new UIWindow(UIScreen.MainScreen.Bounds);

         window.AddSubview(vc.View);

         window.MakeKeyAndVisible();

         return( true );

      }

      public override void OnActivated(UIApplication application) 

      {

         //required override on iOS 3.0

      }

   }

   [Register]

   public class MainViewController : UIViewController 

   {

      public override void ViewDidLoad()

      {

         base.ViewDidLoad();

         //custom code can start here...

         Console.WriteLine("App Loaded");

      }

   }

}//myNameSpace