Rick Caminiti dotCOM

I have been in under the impression that you could not drop SQL Agent Jobs without being able to look at the properties and being able to get the JOB ID, since you need the jobid to pass to the sp_delete_job function.  Because of that, whenever I wanted to change a Job, I always had to incude a manual step to log onto the server, right click and delete the job.

However, I found a simple script to automate this, so now I can just include this in my script that will recreate the job.  Simple… should have known.
USE [msdb]
GO
DECLARE
@jobid sysname;
IF
EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'EXACT_JOB_NAME')
BEGIN
SELECT
@jobid = job_id FROM msdb.dbo.sysjobs_view WHERE name = N'EXACT_JOB_NAME'
EXEC  msdb.dbo.sp_delete_job @job_id=@jobid
END 

The other day I was trying to figure out how to send an email via Script Task in SSIS. For some reason, the Send Email Task wasn’t working for me (still can’t figure out why). Anyway, here’s what I ended up with.

Works like a charm…


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net

Public Class ScriptMain
    Public Sub Main()
      Dim myHtmlMessage As MailMessage
      Dim mySmtpClient As SmtpClient

      myHtmlMessage = New MailMessage(“email@somewhere.com”, “email@somewhereelse.com”, “Subject”, “Body”)
      mySmtpClient = New SmtpClient(“SMTP”)
      mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
      mySmtpClient.Send(myHtmlMessage)
      Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

I was trying to figure out how to use Native Dynamic SQL in SQL Server (using late binding) for performance reasons. With late binding, you are able to reuse cached execution plans and therefore can get better performance. This also keeps you from having from having to create the same query multiple times in a stored procedure just to allow for different filter criteria. You can build the SQL on the fly, bind the parameters and execute the SQL on the fly.

Here’s what I ended up with.




USE [database]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[proc]

@Parm1 INT,

@Parm2 INT,

@Parm3 DATETIME,

AS

SET NOCOUNT ON

DECLARE

@SELECT NVARCHAR(1000),

@FROM NVARCHAR(1000),

@WHERE NVARCHAR(1000),

@ORDERBY NVARCHAR(1000),

@SQL NVARCHAR(4000), –MAX SIZE for dynamic SQL is 4000 characters

@DYNSQLPARMLIST NVARCHAR(250),

@vParm3 CHAR(10)

 

– -1 implies ALL. Set -1s to NULL for COALESCE functions in SQL

IF @Parm1 = -1 SET @Parm1 = NULL

IF @Parm2 = -1 SET @Parm2 = NULL

 

SET @vParm3 = CONVERT(char, @Parm3, 101) /*Just a placeholder for easier dyn sql conversion*/

 

/*For some reason, dynamic sql can’t handle s or linefeeds in this section, so they’re all on one line with spaces separating them*/

/*Note, the DATETIME parameter is not in CHAR format. Again, this is just because I’m building a string for the SQL*/

SET @DYNSQLPARMLIST = N’@dynParm1 INT, @dynParm2 INT, @dynParm3 CHAR(10)’

 

/*Build the SQL. Note the parameters match the parameters in the @DYNSQLPARMLIST variable. @dynParm1, not @Parm1*/

SET @SELECT = ‘SELECT *’

SET @FROM = ‘ FROM table1 with (nolock)’

SET @WHERE = ‘ WHERE column1 = COALESCE(@dynParkID, column1) –if null, set column = to itself (no filter)

AND column2 = COALESCE(@dynInformationTypeID, column2)

AND column3 > cast(@dynParm3 as datetime)’

SET @ORDER = ‘ ORDER BY 1?

 

SET @SQL =

@SELECT + ‘ ‘ +

@FROM + ‘ ‘ +

@WHERE + ‘ ‘ +

@ORDERBY

 

EXECUTE sp_executesql

@SQL,

@DYNSQLPARMLIST,

@dynParm1 = @Parm1,

@dynParm2 = @Parm2,

@dynParm3 = @vParm3

 

SET NOCOUNT OFF

For more information, check out this page: http://ping.fm/mOmXI

 

 
UPDATE

TOP(100) table_name
SET column1 = <something>
WHERE blah = bleh;

I’m trying to understand what the difference is between an Index Seek and an Index Scan. It would seem that a Seek is better than a Scan, but I wanted to verify.

It would appear that an Index Scan has to quickly read every single row in the index to determine which rows qualify. In Oracle, I believe this is comparable to a Full-Index Scan. In most cases, I think this is still better than a full table scan, because index files are not nearly as “fat” as a table would be, typically. So it’s easier and faster to read the entire index that it would be to read the entire table. Once the qualifying rows are determined in the index scan, the SQL engine must then go find those rows in the actual table to get the remaining data for the SELECT portion of the query.

An Index Seek only reads rows in the index that qualify based on the filters in the query. It’s able to determine the qualifying rows in the index immediately, and only read those rows, and then if needed, filter those results further. On the surface, it would seem that an Index Seek is much more optimal than an Index Scan, simply because it’s got less of a footprint; it doesn’t have to read or touch as much of the data.
There are ways to trick the optimizer into using an Index Seek with hints and certain ways of formatting your where clause such as using something like “WHERE PKID” > 0, knowing that the ID is always greater than 0.

I’ve also come to find is that you can use an INCLUDE statement in your INDEX to avoid the trip back to the Table. So if you had an Index seek that found a recordset of 500 rows, it would then normally have to go back to the table and find those 500 rows to get the additional columns from the Table for the SELECT statement. For instance, if you were looking up firstname and lastname on the persontable by personID, the engine would find the personID in the index, then find the row(s) in the table so it would return the firstname and lastname. To eliminate that round trip, you could create an index in PersonID with an INCLUDE on firstname and lastname.

Ok, well, this might now be the “equivalent”, and I don’t yet have a solution for LPAD without using a Custom Function, but here’s what I did to perform an RPAD for something I was doing.

I had 2 int fields that I needed to concatenate together. The max size on both were 3 digits, so I decided to make my joint combination of the two keys both 3 digits so keep it as a smart key, always knowing the first 3 meant something and the last 3 meant something in the source databases. Again, these were ints, and I need the combination of the two to be ints.
Forgive the formatting, I’m TRYING to make it easier to read:

select
  cast(
   replace( /*replace spaces with 0s*/
    cast([col1] as nchar(3)) /*casting as nchar to provide training spaces*/
    , ' '
    , '0') +
   replace(
    cast(isnull(col2)], 0) as nchar(3)) --second field is nullable (left join), make them 0s
    , ' '
    , '0')
  as int) /*casting it back to an int*/
from table