sqlserver

SQL Server – Save all DTS Packages

Matt Simner SQL Server ,

Here’s another useful proc I found to back up all your DTS packages (from I can’t remember where).  Put this in your master database: 


Create procedure usp_SavePackages
@Path    varchar(128)
as
/*

*/

    set nocount on

declare @objPackage int
declare @PackageName varchar(128)
declare @rc int
declare @ServerName varchar(128)
declare @FileName varchar(128)
declare    @FilePath varchar(128)
declare    @cmd varchar(2000)
    
    select     @ServerName = @@ServerName ,
        @FilePath = @Path
    
    if right(@Path,1) <> ‘\’
    begin
        select @Path = @Path + ‘\‘
    end
    
    — create output directory – will fail if already exists but …
    select    @cmd = ‘mkdir ‘ + @FilePath
    exec master..xp_cmdshell @cmd
    
    
create table #packages (PackageName varchar(128))
    insert     #packages
        (PackageName)
    select     distinct name
    from    msdb..sysdtspackages
    
    select    @PackageName = ”
    while @PackageName < (select max(PackageName) from #packages)
    begin
        select    @PackageName = min(PackageName) from #packages where PackageName > @PackageName

        select    @FileName = @FilePath + @PackageName + ‘.dts‘

        exec @rc = sp_OACreate ‘DTS.Package‘, @objPackage output
        if @rc <> 0
        begin
            raiserror(‘failed to create package rc = %d‘, 16, -1, @rc)
            return
        end

        exec @rc = sp_OAMethod @objPackage, ‘LoadFromSQLServer‘ , null,
            @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
        if @rc <> 0
        begin
            raiserror(‘failed to load package rc = %d, package = %s‘, 16, -1, @rc, @PackageName)
            return
        end
        
        — delete old file
        select @cmd = ‘del ‘ + @FileName
        exec master..xp_cmdshell @cmd, no_output
        
        exec @rc = sp_OAMethod @objPackage, ‘SaveToStorageFile‘, null, @FileName
        if @rc <> 0
        begin
            raiserror(‘failed to save package rc = %d, package = %s‘, 16, -1, @rc, @PackageName)
            return
        end
        
        exec @rc = sp_OADestroy @objPackage
    end

GO

Colorized by: CarlosAg.CodeColorizer

SQL Server – Get Matrix for two different result sets

Matt Simner SQL Server ,

I had a need today to splice 2 different result sets together and effectively give all permutations of the two tables.  I’d seen different examples on doing this with cursors and loops etc, but I thought – there ‘must’ be an easier way – there’s lots of different join types right?


The first issue I’d got is that there’s no common fields between the two tables I need to merge because otherwise a ‘simple’ FULL OUTER JOIN would do the biz.


In the end it didn’t amount to much more than that because a slightly dodgy hack (but functional nontheless), to guarantee full coverage from the join is to simply place the same literal on both sides of the join 🙂


With 2 sets of ‘different’ data (let’s use Northwind as I’m not about to bore you with my own data).  Say you wanted to generate a list of all products for all regions given the following result sets…


SELECT ProductName
FROM Products


SELECT RegionDescription
FROM Region


You can splice them together with this….


SELECT  R.RegionDescription,
        P.ProductName
FROM Products P
FULL OUTER JOIN Region R
    ON 1 = 1


and you get something like this… (snippet)


RegionDescription                                  ProductName                             
————————————————– —————————————-
Eastern                                            Valkoinen suklaa
Eastern                                            Vegie-spread
Eastern                                            Wimmers gute Semmelknödel
Eastern                                            Zaanse koeken
Western                                            Alice Mutton
Western                                            Aniseed Syrup
Western                                            Boston Crab Meat


If there’s actually a common field then your join can obviously use that.  Any other filtering you want to do can also go into the WHERE clause as required.


This is another reminder of the lesson that if you’re tempted to use a CURSOR, then resist that urge until you’ve exhausted your Google finger!


SQLServerCentral and SQLMag often have the answer you’re looking for.

SQL Server – Kill all users script

Matt Simner SQL Server ,

Found this the other day – for when you’re restoring SQL databases.  You need to kick out all current users right?


Run this little fella into your master database and away you go – just run this step before your restore.



CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as
SET NOCOUNT ON

DECLARE @strSQL varchar(255)
PRINT ‘Killing Users’
PRINT ‘—————–‘

CREATE table #tmpUsers(
spid int,
dbname varchar(50))

INSERT INTO #tmpUsers
SELECT 
[Process ID]    = l.req_spid, db_name(l.rsc_dbid)
from master.dbo.syslockinfo l with (NOLOCK) 
where db_name(l.rsc_dbid) = @dbname
group by l.req_spid, db_name(l.rsc_dbid)


DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname

DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> –1)
BEGIN
    IF (@@fetch_status <> –2)
    BEGIN
    PRINT ‘Killing ‘ + @spid
    SET @strSQL = ‘KILL ‘ + @spid
    EXEC (@strSQL)
    END
    FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers


Colorized by: CarlosAg.CodeColorizer

SQL Server Tips – Optional Parameters and Find Closest Match

Matt Simner SQL Server ,

It’s funny how you can survive for years not knowing some really simple stuff.  I learned two useful things last week.  For instance…


Optional SQL Parameters


To have an ‘optional’ parameter in a SQL ‘search’ Stored Proc you’d often declare a parameter thus…


@MyParam VARCHAR(20) = NULL –to denote that we want to ignore this param


and you’d probably



  1. have some convaluted logic to possibly build dynamic SQL around whether to factor the parameter into your query
  2. build some nasty ‘like’ clause that defaults to ‘%’
  3. derive MIN and MAX values from the input parameter and use a BETWEEN
  4. use a ‘magic value’ in place of your NULL to denote ‘ignore’
  5. something else equally ugly I’ve not considered

…or (assuming this parameter is expecting to do a direct match) you could just simply do


WHERE @MyParam = ISNULL(@MyParam, MyFieldIWantToMatch)


This will ensure if your field is nulled out it just does a simple (IF 1 = 1) type thing.


 


Find Closest Match


I had a need to find the closest match to a particular value within a 10% tolerance +-.  The following does that fairly simply (I did nick the idea from thescripts.com) but these sorts of things do avoid the need for nasty tempdb use with temp tables…


The following is an example – so don’t think I design tables with one column!


DECLARE @Amount INT


SET @Amount = 50


DECLARE @MinAmount INT
DECLARE @MaxAmount INT


SET @MinAmount = @Amount * 0.9
SET @MaxAmount = @Amount * 1.1


SELECT  AT.Amount
FROM AT.AmountTable AT
WHERE Amount BETWEEN @MinAmount AND @MaxAmount –within range
ORDER BY ABS(@Amount – AT.Amount) ASC –Nifty ‘closest’ match


You can then take the first row in your result set to be the closest match.