Rick Caminiti dotCOM

Short and sweet. Accept a varchar input parameter, try to convert it to a number. If the conversion fails for any reason, return FALSE, else, return TRUE.

CREATE OR REPLACE FUNCTION is_numeric (

   p_data   VARCHAR2

)

   RETURN NUMBER

IS

   v_number   NUMBER;

   o_bool     BOOLEAN := TRUE;

BEGIN

   BEGIN

      v_number := p_data;

   EXCEPTION

      WHEN OTHERS THEN

         o_bool := FALSE;

   END;

 

   RETURN o_bool;

END isnumber;

In a like statement, “%” is a wildcard for everything after or before it and “_” is a wildcard for one character.  Observe:

LIKE ‘%Z’ would return ANYthing that ended with Z.

 

LIKE ‘%Z%’ would return ANYthing with a Z in it.

 

LIKE ‘Z__’ would return anything that started with Z and had at least 2 characters after it, like:

ZOO, ZXY, ZAP

but would not return:

ZA, ZO, Z, or XYZ

 

LIKE ‘Z%’ would return anything that STARTED with Z.

 

LIKE ‘_Z%’ would return anything that had one character, then a Z, then anything or nothing else like:

YZX, YZ, ZZZ

but would not return:

XYZ, ABCZ

 

However, if you want to look for the literal value of ‘%’ or ‘_’, you need to escape it.  For instance, if you want to look for anything that begins with ‘Z_’, then you would need to escape the ‘_’, otherwise, you would be looking for anything that started with ‘Z’ followed by one wildcard character ‘_’, followed by a wildcard of any number of characters ‘%’, returning anything that just started with ‘Z’ and had at lease one character after it.

select table_catalog, column_name, table_schema, table_name
from information_schema.columns
where column_name like 'Z\_%' escape '\'

This would only return data that started with 'Z_'.

Here’s a quick and dirty way to send email via MSSQL.  For some reason, we’re not allowed to send email using the Email Task in SSIS, so I chose this option.  Unfortunately, even if you have access to execute sp_SQLSMTPMail, you might not have access to run xp_cmdshell.

 

I ended up having to create my SSIS package with a SQL Task that runs this proc, then I had to run it with a SQL Agent Job with a proxy account that had access to xp_cmdshell.  Fun stuff.

 

 

 

 

declare @developerperson varchar(80)

declare @message_body varchar(1000)

 

set @message_body =

‘Dear ‘ + @developerperson + ‘Use \n for line breaks and escape your ”s’

 

 

EXECUTE dbo.sp_SQLSMTPMail

      @vcTo = ‘You@you.com’,

      @vcCC= ‘Them@them.com’,

      @vcSubject = ‘Super Cool SQL Email’,

      @vcBody = @message_body,

      @vcFrom = ‘Me@me.com’,

      @vcSMTPServer = ‘SMTPName’,

      @vcSenderName = ‘Some One’,

      @vcServerName = @@SERVERNAME –no need to specify because this is the default anyway

Or, lets say you want to email query results as an attachment:

EXECUTE dbo.sp_SQLSMTPMail

  @vcTo=‘you@you.com’

, @vcCC=‘them@them.com’

, @vcSubject=‘Here is your wonderful report that is terribly formatted as a txt file attachment’

, @vcquery= ‘select col1, col2, col3, col4 from database.schema.table’

 

 

 

 

 

 

 

 

Here’s the actual Proc definition: Read more…

I recently needed a way to execute a script overnight to count the number of records that were being updated in a given hour.  I needed it to run at a specific time and to execute in 10 minute incriments and stop .  I was hoping to log the results to a file, but this does just as well.  When it’s done executing, it spits it all to the output window so I can see it in the morning when I get back.

 

This script below will start at 11pm and end at midnight and it will execute every 10 minutes and display the current time and the count.  You could just as easily use a counter and have it execute a certain number of  times.

 

–don’t even start until 11pm

WAITFOR TIME ’23:00:00.000′

declare @endtime datetime

set @endtime = cast(’2009-05-09 00:00:00.000′ as datetime)

–stop at midnight

while getdate() < @endtime

begin

      –every 10 minutes, execute

      WAITFOR DELAY ’00:10:00′;

      select getdate(), count(*)

      from tablename

      where datetimecolumn > ’5/7/2009′

      and datetimecolumn < ’5/8/2009′

end

 

 

Kudos to Jerry for helping me with this.

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 

 

 
UPDATE

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