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

iOS MonoTouch Developement

Will Need:

1. Mac running OSX 10

2. Install latest version of Apple's Developer Tools / Xcode
     https://developer.apple.com/technologies/tools/

3. Once installed, in Xcode go to "XCode"-->"Preferences"-->"Downloads" and install "Command Line Tools" (will need this if you want to "make" any bindings to additional Objective-C components).

4. Download and install MonoTouch.  This will include: 1) Mono, an open source .NET implementation; 2) MonoDevelop, an open source IDE similar to Visual Studio; and, 3) MonoTouch, the proprietary set of libraries that allow you to compile C# code into iOS apps.

5. Your now ready to do the first tutorials found here:
    http://docs.xamarin.com/ios

To this point you can write code and run it in the simulator.


To actually deploy it to a device you will need a licensed copy of Monotouch and one of the following:

Apple Developer Program ($99 a year) - provision ad-hoc deployment on up to 100 different devices for testing and distribute/sell apps via app store.

https://developer.apple.com/programs/ios/

Apple Enterprise Program ($299 a year) - deploy .ipa bundled apps to corporate owned or corporate employee owned devices.

https://developer.apple.com/programs/ios/enterprise/

Error creating database diagrams

Error: Cannot insert the value NULL into column 'diagram_id', table 'mydb.dbo.sysdiagrams'; column does not allow nulls. INSERT failes. The statement has been terminated. The 'sp_creatediagram' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead (.Net SqlClient Data Provider) Fix: Add the following trigger

CREATE TRIGGER tr_sysdiagrams_i ON dbo.sysdiagrams INSTEAD OF INSERT
AS 
BEGIN
   SET NOCOUNT ON;

   DECLARE @id int

          SELECT @id = max(A.diagram_id) + 1
            FROM dbo.sysdiagrams A

     INSERT INTO dbo.sysdiagrams 
               ( name
               , principal_id
               , diagram_id
               , [version]
               , [definition] )
          SELECT name
               , principal_id
               , @id
               , [version]
               , [definition]
            FROM inserted
END --tr_sysdiagrams_i
GO

Explanation: I seem to remeber getting this same error when I was using the SQL Server 2008 Management Studio (SSMS) on a SQL Server 2005 instance. I got this again using the SSMS 2012 on a 2008R2 instance. Either the diagram_id is supposed to be an identity, or the id should be generated by SSMS. In either case, just making the column allow NULLs leads to other problems. So either have to recreate the table with the column as an identity, or add this trigger.

How to find objects modified since a certain date

   SELECT *
     FROM sys.all_objects
    WHERE modify_date > '20120628 00:00:00'

Remote table-valued function calls are not allowed

Error: Remote table-valued function calls are not allowed

Fix: use "WITH (NOLOCK)" instead of just "(NOLOCK)


Explanation:  When issuing a select that has uses four part naming to address the table and the table has a (nolock) hint, the t-sql will fail to execute with the error "Remote table-valued function calls are not allowed."

If you execute the query with 3 or 2 part naming it runs without error.

To get the query to work using 4 part naming you have to put the "with" keyword before the (nolock).



http://connect.microsoft.com/SQLServer/feedback/details/126162/remote-table-valued-function-calls-are-not-allowed

‘SERVERNAME’ is not configured for RPC

Error: Server ‘SERVERNAME’ is not configured for RPC
Code:

EXEC [LINKEDSERVERNAME].[DATABASENAME].dbo.[STOREDPROCNAME]

Problem: Attempting to execute a stored procedure across a SQL Server Linked Server

Fix: Enable "rpc out"

EXEC master.dbo.sp_serveroption 
     @server=N'LINKEDSERVERNAME'
   , @optname=N'rpc out'
   , @optvalue=N'true'

GO

Specifying SQL Server instance

Connecting to a SQL Server instance using jdbc drivers, you need to append ‘;instance=INSTNAME’ to the connection string like the following:

jdbc:jtds:sqlserver://<SQLServer>:<PORT>/<DBName>;instance=<SQLInstanceName>

Example:
jdbc:jtds:sqlserver://localhost:1433/mydb;instance=SQLEXPRESS

sp_CodeGenerator

USE [datastage]
GO
/****** Object:  StoredProcedure [dbo].[sp_CodeGenerator]    Script Date: 09/30/2013 13:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--      Author: John Olsen
-- Create date: 20120323
-- Update date: 20120425
-- Review date: 
-- Description: Generate column related t-sql
--              and c# code for specified table
--              because i have had carpul
--              tunnel surgery once already
-- =============================================
ALTER PROCEDURE [dbo].[sp_CodeGenerator] ( @tabname varchar(128) )
AS
BEGIN
-------------------------------------
--DECLARE @tabname varchar(128)
--SET @tabname = 'truck_transfers'
-------------------------------------
SET NOCOUNT ON;
DECLARE @types TABLE ( xtype tinyint
                     , sql_type varchar(128)
                     , csharp_type varchar(30)
                     , db_type varchar(128)
                     , use_len tinyint
                     , use_prec tinyint
                     , use_scale tinyint
                     , type_class tinyint )
       INSERT INTO @types (xtype, sql_type, csharp_type, db_type, use_len, use_prec, use_scale, type_class)
            SELECT 127, 'bigint', 'int', 'BigInt',0,0,0,1
      UNION SELECT 173, 'binary', 'byte[]', 'Binary',0,0,0,1
      UNION SELECT 104, 'bit', 'int', 'Bit',0,0,0,1
      UNION SELECT 175, 'char', 'string', 'Char',1,1,0,2
      UNION SELECT 40, 'date', 'string', 'VarChar',1,1,0,3
      UNION SELECT 61, 'datetime', 'string', 'DateTime',0,0,0,3
      UNION SELECT 42, 'datetime2', 'string', 'DateTime',0,0,0,3
      UNION SELECT 43, 'datetimeoffset', 'string', 'VarChar',1,1,0,3
      UNION SELECT 106, 'decimal', 'float', 'Decimal',0,1,1,1
      UNION SELECT 62, 'float', 'float', 'Float',0,1,1,1
      UNION SELECT 240, 'geography', 'SqlGeography', 'Geography',0,0,0,4
      UNION SELECT 240, 'geometry', 'SqlGeometry', 'Geometry',0,0,0,4
      UNION SELECT 240, 'hierarchyid', 'string', 'NVarChar',1,1,0,4
      UNION SELECT 34, 'image', 'byte[]', 'Image',0,0,0,4
      UNION SELECT 56, 'int', 'int', 'Int',0,0,0,1
      UNION SELECT 60, 'money', 'float', 'Money',0,0,0,1
      UNION SELECT 239, 'nchar', 'string', 'NChar',1,1,0,2
      UNION SELECT 99, 'ntext', 'string', 'NText',0,0,0,2
      UNION SELECT 108, 'numeric', 'float', 'Decimal',0,1,1,1
      UNION SELECT 231, 'nvarchar', 'string', 'NVarChar',1,1,0,2
      UNION SELECT 59, 'real', 'float', 'Real',0,1,1,1
      UNION SELECT 58, 'smalldatetime', 'string', 'SmallDateTime',0,0,0,3
      UNION SELECT 52, 'smallint', 'int', 'SmallInt',0,0,0,1
      UNION SELECT 122, 'smallmoney', 'float', 'SmallMoney',0,0,0,1
      UNION SELECT 98, 'sql_variant', 'object', 'Variant',0,0,0,4
      UNION SELECT 231, 'sysname', 'string', 'VarChar',1,1,0,2
      UNION SELECT 35, 'text', 'string', 'Text',0,0,0,2
      UNION SELECT 41, 'time', 'string', 'VarChar',0,0,0,3
      UNION SELECT 189, 'timestamp', 'string', 'Timestamp',0,0,0,3
      UNION SELECT 48, 'tinyint', 'int', 'TinyInt',0,0,0,1
      UNION SELECT 36, 'uniqueidentifier', 'SqlGuid', 'UniqueIdentifier',0,0,0,4
      UNION SELECT 165, 'varbinary', 'byte[]', 'VarBinary',1,1,0,4
      UNION SELECT 167, 'varchar', 'string', 'VarChar',1,1,0,2
      UNION SELECT 241, 'xml', 'string', 'Text',0,0,0,2
--SELECT * FROM @types
DECLARE @cols TABLE ( id int identity(1,1)
                    , colname varchar(128)
                    , parmname varchar(128)
                    , sql_type varchar(128)
                    , db_type varchar(128)
                    , csharp_type varchar(30)
                    )
DECLARE @i int
      , @crlf char(2)
      , @classname varchar(128)
      , @colname varchar(128)
      , @parmname varchar(128)
      , @sql_type varchar(128)
      , @db_type varchar(128)
      , @csharp_type varchar(30)
      , @s_dec varchar(8000)
      , @s_assn varchar(8000)
      , @s_assnB varchar(8000)
      , @s_collist varchar(8000)
      , @s_parmlist varchar(8000)
      , @c_dec varchar(8000)
      , @c_update varchar(8000)
      , @c_pop varchar(8000)
      , @c_parm varchar(8000)
      
SET @crlf = char(13) + char(10)
     INSERT INTO @cols ( colname, parmname, sql_type, db_type, csharp_type )
          SELECT B.name "colname"
               , '@' + B.name "parmname"
               , (case when C.use_scale = 1 then C.sql_type + '(' + cast(B.prec as varchar(30)) + ',' + cast(B.scale as varchar(30)) + ')'
                       when C.use_prec = 1 then C.sql_type + '(' + (case when B.prec = -1 then 'max' else cast(B.prec as varchar(30)) end) + ')'
                       else C.sql_type end) "sql_type"
               , (case when C.use_len = 1 and B.prec > 0 then 'SqlDbType.' + C.db_type + ', ' + cast(B.prec as varchar(30))
                       else 'SqlDbType.' + C.db_type end) "db_type"
               , C.csharp_type
            FROM sysobjects A (NOLOCK)
      INNER JOIN syscolumns B (NOLOCK)
              ON A.id = B.id
      INNER JOIN @types C
              ON B.xtype = C.xtype
           WHERE A.type = 'U'
             AND A.name = @tabname
        ORDER BY A.name ASC
               , B.colorder DESC
          SELECT @i = max(id) 
            FROM @cols
   SET @classname = dbo.fnTitleCase(@tabname)
   SET @s_parmlist = ''
   SET @s_collist = ''
   SET @s_dec = ''
   SET @s_assn = ''
   SET @s_assnB = ''
   SET @c_dec = '   public class ' + @classname + 'DAO : DAO' + @crlf + '   {'
   SET @c_pop = '      public void populate(DataRow row)' + @crlf + '      {'
   SET @c_update = '      public void update()' + @crlf + '      {' + @crlf + '         SqlCommand oCmd = new SqlCommand();' + @crlf + '         oCmd.CommandType = CommandType.StoredProcedure;' + @crlf + '         oCmd.CommandText = "pUpdate";'
   SET @c_parm = ''
   WHILE @i > 0
    BEGIN
       SELECT @colname = A.colname
            , @parmname = A.parmname
            , @sql_type = A.sql_type
            , @db_type = A.db_type
            , @csharp_type = A.csharp_type
         FROM @cols A
        WHERE A.id = @i
        SET @s_collist += @crlf + '   , ' + @colname
        SET @s_parmlist += @crlf + '   , ' + @parmname
        SET @s_dec += @crlf + '   , ' + @parmname + ' ' + @sql_type
        SET @s_assn += @crlf + '   , A.' + @colname + ' = ' + @parmname
        SET @s_assnB += @crlf + '   , A.' + @colname + ' = B.' + @colname
        SET @c_dec += @crlf + '      public ' + @csharp_type + ' ' + @colname + (case when @csharp_type = 'string' then ' = "";' else ' = 0;' end)
        SET @c_pop += @crlf + '         this.' + @colname + ' = ' 
                   + (case when @csharp_type in ('float','int') then @csharp_type + '.Parse(row["' + @colname + '"].ToString());'
                           when @csharp_type in ('string') then 'row["' + @colname + '"].ToString();'
                           else '('+@csharp_type+') row["' + @colname + '"];' end )
        SET @c_update += @crlf + '         oCmd.Parameters.Add("' + @parmname + '", ' + @db_type + ').Value = this.' + @colname + ';'
       SET @i -= 1
    END
   PRINT @s_collist + @crlf
   PRINT @s_parmlist + @crlf
   PRINT @s_dec + @crlf
   PRINT @s_assn + @crlf + @crlf
   PRINT @s_assnB + @crlf + @crlf
   PRINT @c_dec + @crlf + @crlf
   PRINT @c_pop + @crlf +  '      }//populate()' + @crlf + @crlf
   PRINT @c_update + @crlf +  '      }//update()' + @crlf + @crlf
   PRINT @c_parm + @crlf
   PRINT '   } //' + @classname
END --sp_CodeGenerator()

Cannot insert the value null into column ‘diagram_id’, table dbo.sysdiagrams

Error Condition: Cannot insert the value null into column ‘diagram_id’, table dbo.sysdiagrams

Problem: This sometimes occurs when a SQL Server database is ported from one version to another (such as 2000 to 2005 or 2005 to 2008) or possibly also when using the SSMS for a newer version of SQL Server against and older database.

Fix: Drop and recreated dbo.sysdiagrams making the column diagram_id an identity column. 

use [mydatabase]

GO

DROP TABLE [dbo].[sysdiagrams]
GO

CREATE TABLE [dbo].[sysdiagrams]

(   [name] [nvarchar](128) NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] identity(1,1) NOT NULL,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL
)
GO

NOTE: If you want to preserve the diagrams, then rename the table and create a new one, then SELECT - INTO to copy the data into the new table.

Problem: Unable to run a query through a linked server SQL Server 2008 R2. This problem occurs with a non-sysadmin account.

Error Message:

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)

Cause: When creating a linked server with the parameter @provstr and you use a local SQL Server non-admin or non-Windows account, you have to add the parameter "User Name"   into the @provstr

Note: Also do not use 'SQL Server' (note space) as product name, as you cannot specify a provider or any properties for product 'SQL Server'

Resolution : Add "User ID=remoteLogin" into the provider string on your linked server

EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @provider=N'SQLNCLI',@srvproduct = 'SQLSERVER', @provstr=N'SERVER=serverName\InstanceName;User ID=remoteLogin'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName', @locallogin = 'localLogin' , @useself = N'False', @rmtuser = N'remoteLogin', @rmtpassword = N'*****'

Check:

SELECT * FROM OPENQUERY ([LinkServerName], 'SELECT * FROM sysobjects')

Searching Procedures for Key Terms

   SELECT name
        , object_definition(object_id) 
     FROM sys.procedures 
    WHERE object_definition(object_id) LIKE '%SomeSearchTerm%'

Code Generator

-- =============================================
--      Author: John Olsen
-- Create date: 20120323
-- Update date: 20120425
-- Review date: 
-- Description: Generate column related t-sql
--              and c# code for specified table
--              because i have had carpul
--              tunnel surgery once already
-- =============================================
ALTER PROCEDURE [dbo].[uCodeGenerator] ( @tabname varchar(128) )
AS
BEGIN

-------------------------------------
--DECLARE @tabname varchar(128)
--SET @tabname = 'truck_transfers'
-------------------------------------

SET NOCOUNT ON;

DECLARE @types TABLE ( xtype tinyint
                     , sql_type varchar(128)
                     , csharp_type varchar(30)
                     , db_type varchar(128)
                     , use_len tinyint
                     , use_prec tinyint
                     , use_scale tinyint
                     , type_class tinyint )

       INSERT INTO @types (xtype, sql_type, csharp_type, db_type, use_len, use_prec, use_scale, type_class)
            SELECT 127, 'bigint', 'int', 'BigInt',0,0,0,1
      UNION SELECT 173, 'binary', 'byte[]', 'Binary',0,0,0,1
      UNION SELECT 104, 'bit', 'int', 'Bit',0,0,0,1
      UNION SELECT 175, 'char', 'string', 'Char',1,1,0,2
      UNION SELECT 40, 'date', 'string', 'VarChar',1,1,0,3
      UNION SELECT 61, 'datetime', 'string', 'DateTime',0,0,0,3
      UNION SELECT 42, 'datetime2', 'string', 'DateTime',0,0,0,3
      UNION SELECT 43, 'datetimeoffset', 'string', 'VarChar',1,1,0,3
      UNION SELECT 106, 'decimal', 'float', 'Decimal',0,1,1,1
      UNION SELECT 62, 'float', 'float', 'Float',0,1,1,1
      UNION SELECT 240, 'geography', 'SqlGeography', 'Geography',0,0,0,4
      UNION SELECT 240, 'geometry', 'SqlGeometry', 'Geometry',0,0,0,4
      UNION SELECT 240, 'hierarchyid', 'string', 'NVarChar',1,1,0,4
      UNION SELECT 34, 'image', 'byte[]', 'Image',0,0,0,4
      UNION SELECT 56, 'int', 'int', 'Int',0,0,0,1
      UNION SELECT 60, 'money', 'float', 'Money',0,0,0,1
      UNION SELECT 239, 'nchar', 'string', 'NChar',1,1,0,2
      UNION SELECT 99, 'ntext', 'string', 'NText',0,0,0,2
      UNION SELECT 108, 'numeric', 'float', 'Decimal',0,1,1,1
      UNION SELECT 231, 'nvarchar', 'string', 'NVarChar',1,1,0,2
      UNION SELECT 59, 'real', 'float', 'Real',0,1,1,1
      UNION SELECT 58, 'smalldatetime', 'string', 'SmallDateTime',0,0,0,3
      UNION SELECT 52, 'smallint', 'int', 'SmallInt',0,0,0,1
      UNION SELECT 122, 'smallmoney', 'float', 'SmallMoney',0,0,0,1
      UNION SELECT 98, 'sql_variant', 'object', 'Variant',0,0,0,4
      UNION SELECT 231, 'sysname', 'string', 'VarChar',1,1,0,2
      UNION SELECT 35, 'text', 'string', 'Text',0,0,0,2
      UNION SELECT 41, 'time', 'string', 'VarChar',0,0,0,3
      UNION SELECT 189, 'timestamp', 'string', 'Timestamp',0,0,0,3
      UNION SELECT 48, 'tinyint', 'int', 'TinyInt',0,0,0,1
      UNION SELECT 36, 'uniqueidentifier', 'SqlGuid', 'UniqueIdentifier',0,0,0,4
      UNION SELECT 165, 'varbinary', 'byte[]', 'VarBinary',1,1,0,4
      UNION SELECT 167, 'varchar', 'string', 'VarChar',1,1,0,2
      UNION SELECT 241, 'xml', 'string', 'Text',0,0,0,2

--SELECT * FROM @types

DECLARE @cols TABLE ( id int identity(1,1)
                    , colname varchar(128)
                    , parmname varchar(128)
                    , sql_type varchar(128)
                    , db_type varchar(128)
                    , csharp_type varchar(30)
                    )
DECLARE @i int
      , @crlf char(2)
      , @classname varchar(128)
      , @colname varchar(128)
      , @parmname varchar(128)
      , @sql_type varchar(128)
      , @db_type varchar(128)
      , @csharp_type varchar(30)
      , @s_dec varchar(8000)
      , @s_assn varchar(8000)
      , @s_collist varchar(8000)
      , @s_parmlist varchar(8000)
      , @c_dec varchar(8000)
      , @c_update varchar(8000)
      , @c_pop varchar(8000)
      , @c_parm varchar(8000)
      
SET @crlf = char(13) + char(10)

     INSERT INTO @cols ( colname, parmname, sql_type, db_type, csharp_type )
          SELECT B.name "colname"
               , '@' + B.name "parmname"
               , (case when C.use_scale = 1 then C.sql_type + '(' + cast(B.prec as varchar(30)) + ',' + cast(B.scale as varchar(30)) + ')'
                       when C.use_prec = 1 then C.sql_type + '(' + cast(B.prec as varchar(30)) + ')'
                       else C.sql_type end) "sql_type"
               , (case when C.use_len = 1 then 'SqlDbType.' + C.db_type + ', ' + cast(B.prec as varchar(30))
                       else 'SqlDbType.' + C.db_type end) "db_type"
               , C.csharp_type
            FROM sysobjects A (NOLOCK)
      INNER JOIN syscolumns B (NOLOCK)
              ON A.id = B.id
      INNER JOIN @types C
              ON B.xtype = C.xtype
           WHERE A.type = 'U'
             AND A.name = @tabname
        ORDER BY A.name ASC
               , B.colorder DESC

          SELECT @i = max(id) 
            FROM @cols

   SET @classname = dbo.fnTitleCase(@tabname)
   SET @s_parmlist = ''
   SET @s_collist = ''
   SET @s_dec = ''
   SET @s_assn = ''
   SET @c_dec = '   public class ' + @classname + 'DAO : DAO' + @crlf + '   {'
   SET @c_pop = '      public void populate(DataRow row)' + @crlf + '      {'
   SET @c_update = '      public void update()' + @crlf + '      {'
   SET @c_parm = ''

   WHILE @i > 0
    BEGIN
       SELECT @colname = A.colname
            , @parmname = A.parmname
            , @sql_type = A.sql_type
            , @db_type = A.db_type
            , @csharp_type = A.csharp_type
         FROM @cols A
        WHERE A.id = @i

        SET @s_collist += @crlf + '   , ' + @colname
        SET @s_parmlist += @crlf + '   , ' + @parmname
        SET @s_dec += @crlf + '   , ' + @parmname + ' ' + @sql_type
        SET @s_assn += @crlf + '   , A.' + @colname + ' = ' + @parmname
        SET @c_dec += @crlf + '      public ' + @csharp_type + ' ' + @colname + (case when @csharp_type = 'string' then ' = "";' else ' = 0;' end)
        SET @c_pop += @crlf + '         this.' + @colname + ' = ' 
                   + (case when @csharp_type in ('float','int') then @csharp_type + '.Parse(row["' + @colname + '"].ToString());'
                           else 'row["' + @colname + '"].ToString();' end )
        SET @c_update += @crlf + '         oCmd.Parameters.Add("' + @parmname + '", ' + @db_type + ').Value = this.' + @colname + ';'

       SET @i -= 1
    END

   PRINT @s_collist + @crlf
   PRINT @s_parmlist + @crlf
   PRINT @s_dec + @crlf
   PRINT @s_assn + @crlf + @crlf
   PRINT @c_dec + @crlf + @crlf
   PRINT @c_pop + @crlf +  '      }//populate()' + @crlf + @crlf
   PRINT @c_update + @crlf +  '      }//update()' + @crlf + @crlf
   PRINT @c_parm + @crlf
   PRINT '   } //' + @classname

END --uCodeGenerator()

EXEC - INSERT

You can return the results of a dynamic sql statement into a temp table or table variable:

DECLARE @tab TABLE ( id int, val varchar(100) )
DECLARE @sql varchar(8000)

SET @sql = "SELECT id, name FROM dbo.mytable"

INSERT INTO @tab ( id, val )
EXEC( @sql )

Installing Reporting Services

You have to open your browser (on server console or during a remote session) using "Run as Administrator".

Then go to http://localhost/Reports (NOTE: do not put http:///Reports - it doesn't work).

Go to Folder Settings and assign role browser and content manager to your administrator account. It should start working now.

If you are running it on Windows server 2008 don't forget to create inbound rule in windows firewall to port 80.

SQL Server Limiting Data Access for a Server Admin

If a user is granted the following server roles they still cannot access the data of databases they are not a user or dbo.


serveradmin - can change server configuration parameters and shut down the server.

setupadmin - can add or remove linked servers, manage replication, create, alter or delete extended stored procedures, and execute some system stored procedures, such as sp_serveroption.

securityadmin - can create and manage server logins and auditing, and read the error logs.

processadmin - can manage the processes running in SQL Server.

dbcreator - can create, alter, and resize databases.

diskadmin - can manage disk files.



----------- example script -----------

use master
go



EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'securityadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'serveradmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'setupadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'processadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'diskadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'dbcreator'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'bulkadmin'
GO

----------- example script -----------
To allow backing up of a database they do have access to, then can use the following:


use [user_database]
go

grant backup database to [testuser]
go

Plan Cache Stats

Special thanks to Kimberly L. Tripp for this query

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go 

Full article: http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx#ixzz00P7SU8xp

SQL Server Browser Service

Quick Tip from a respected MS Instructor: Disable SQL Server Browser Service

This causes a LOT of wasted network traffic and opens up a vulnerability making it easier for hackers to find your data servers.

Turn this off and advise your users to explicity enter the server name and not use the "browse" feature.

SQLActiveScriptHost Example

SQLActiveScriptHost.Print "Hello World"

Dim conn, rs
Dim sql, sProvider, sCString

''sProvider = "Microsoft.JET.OLEDB.4.0"
sProvider = "Microsoft.ACE.OLEDB.12.0"
sCString = "Data Source= c:\temp\tryme.mdb"

sql = "SELECT * FROM tryme;"

Set conn = SQLActiveScriptHost.CreateObject("ADODB.Connection")
With conn
.Provider = sProvider
.Mode = adModeRead
.ConnectionString = sCString
.Open
End With

SQLActiveScriptHost.Print "Connected..."

Set rs = conn.Execute(sql)

While Not rs.EOF
rs.MoveNext
WEnd

SQLActiveScriptHost.Print "Executed SQL..."

conn.Close
Set conn = Nothing

SQLActiveScriptHost.Print "Connection Closed."


Note: SQLActiveScriptHost is being deprecated in a future version of SQL Server (Post 2008 R2)

PowerShell Connect to MSAccess

$adOpenStatic = 3
$adLockOptimistic = 3
$adStatusOpen = 1
$sql = "Select * from dispatch;"
$cstr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = c:\temp\mydata.mdb; Jet OLEDB:Database Password=mypass;"

$conn = New-Object -comobject ADODB.Connection
$rs = New-Object -comobject ADODB.Recordset

$conn.Open($cstr)

if ($conn.State -eq $adStatusOpen)
{
   $rs.Open($sql, $conn, $adOpenStatic, $adLockOptimistic)

   if ($rs.State -eq $adStatusOpen)
   {
      $rs.MoveFirst()

      while (!$rs.EOF)
      {
         $rs.Fields.Item(1).Value;
         $rs.MoveNext()
      }
      $rs.Close()
   }

   $conn.Close()
}

varchar(MAX)

30 January 2012
by Rob Garrison
www.simple-talk.com

* Where appropriate, use VARCHAR(n) over VARCHAR(MAX) for reasons of good design if not performance benefits, and because VARCHAR(MAX) data does not compress

* Storing large strings takes longer than storing small strings.

* Updating an in-row VARCHAR(MAX) value from below 8,000 to over 8,000 will be relatively slow, but the difference for a single transaction will likely not be measurable.

* Updating an in-row VARCHAR(MAX) value from over 8,000 to below 8,000 will be faster than if the table is set to store data out-of-row.

* Using the out-of-row option for VARCHAR(MAX) will cause slower writes until the strings are very long.


Full article
http://www.simple-talk.com/sql/database-administration/whats-the-point-of-using-varchar%28n%29-anymore/

restored database owner

Ran into an interesting problem with a SQL2005 database being restored to a SQL2008R2 instance.

Turned out the database owner was correctly represented as the login that restored the database, however the login mapped to the "dbo" user was still mapped to the old login on the source server.

To fix this, ran the following:

alter authorization on database::[database_name] to [login_name]
go

Invalid Column Error When One Stored Proc Calls Another

I've run into a problem where one stored procedure calls another and I get an "Invalid Column" error. The child stored proc works fine when run separately.

Some similar errors could easily be cleaned up by using sp_recompile or sp_refreshsqlmodule.

But, turns out there is a little bug in the column name validation when there are temp tables in a child stored proc that have the same name as a temp table in the parent proc.

Solution: rename the temp table in one proc or another. Or, could use variable tables or some other structure in one or the other.

Enable CLR

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

http://msdn.microsoft.com/en-us/library/ms131048.aspx

Vertical Text

<HTML>
<HEAD>
  <STYLE>
     .verticaltext 
     {
        writing-mode:tb-rl;
        filter: flipv fliph;
        -webkit-transform:rotate(270deg);
        -moz-transform:rotate(270deg);
        -o-transform: rotate(270deg);
        white-space:nowrap;
     }


     .heading
     {
        background: #000000;
        color: #FFFFFF;
        font-weight: bold;
        height: 90px;
     }
  </STYLE>
</HEAD>


<BODY>

<TABLE BORDER="1"><TR class="heading">
  <TH class="verticaltext">Column-A</TH>
  <TH class="verticaltext">Column-B</TH>
  <TH class="verticaltext">Column-C</TH>
</TR>
<TR>
  <TD>Row 1 Col A</TD>
  <TD>Row 1 Col B</TD>
  <TD>Row 1 Col C</TD>
</TR>
<TR>
  <TD>Row 2 Col A</TD>
  <TD>Row 2 Col B</TD>
  <TD>Row 2 Col C</TD>
</TR>
</TABLE>
</BODY>

</HTML>

DROP all VIEWS, FUNCTIONS, PROCEDURES

DECLARE @obj TABLE ( id int identity, objname sysname, objtype sysname )
DECLARE @i int
      , @sql nvarchar(max)


INSERT INTO @obj ( objname, objtype )
          SELECT A.name
               , (case when A.type = 'V' then 'VIEW'
                       when A.type in ('FN','IF','TF') then 'FUNCTION'
                       when A.type = 'P' then 'PROCEDURE'
                       else '' end ) 
            FROM sysobjects A (NOLOCK)
           WHERE A.type IN ( 'V', 'FN', 'IF', 'TF', 'P' )
        ORDER BY (case A.type when 'V' then 3
                              when 'P' then 1
                              else 2 end )
               , A.name DESC

SELECT @i = max(id) FROM @obj

WHILE @i > 0
 BEGIN
   SELECT @sql = 'DROP ' + A.objtype + ' ' + A.objname
     FROM @obj A
    WHERE A.id = @i
   PRINT @sql
   SET @i = @i - 1
 END

This is one example where using a table variable and while loop on a relatively small set of data is preferred, simply because the "sysobjects" table will be mutating through the process of the loop. Could still get away with it in a cursor if locks and hints are properly applied, but I still prefer to avoid it when possible.

Using Cursors Optimally

sqlwithmanoj demonstrated cases where using optimization hints such as FAST_FORWARD, FORWARD_ONLY, READ_ONLY can result in cursors that are more performant than while loops on table variables.



http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/