TSQL - Returning Random Numbers

Someone was updating a table with test data, and asked me how to fill a column with random numbers.

I was sure rand() would do it.  I was wrong:

declare @tmpTable table (

                ID int

                ,random float

)

declare @intID int=100

while @intID>0

begin

                insert into @tmpTable (ID) values (@intID)

                set @intID=@intID-1

end

update @tmpTable set random=rand()

select * from @tmpTable

 

It turns out that rand() generates only one number per statement, not per record.  Boo.

After mucho Googling, I found two ways of returning random values per record.  They are newid() and crypt_gen_random().  Actually, I found more.  I saw some people that created functions, but I didn't want to to create any new objects just for this.

Between the two options, I went for the crypt_gen_random() route.  I guess using either would be equivalent at some level, but using crypt_gen_random() looked more self-explanatory than newid() for returning random numbers, and crypt_gen_random() was completely new to me.

declare @Lower int=1 -- lower boundary of random numbers, inclusive

declare @Upper int=1000 -- upper boundary of random numbers, inclusive

declare @tmpTest table (

                ID int

                ,random int

)

declare @intID int=10

while @intID>0

begin

                insert into @tmpTest (ID) values (@intID)

                set @intID=@intID-1

end

update @tmpTest set

                random=cast(@Lower+(@Upper-@Lower+1)*(cast(crypt_gen_random(6) as bigint) / power(256.0, 6)) as int)

select * from @tmpTest order by random desc

crypt_gen_random(nnn) returns a random number "nnn" bytes long.  I had to divide it by its maximum value possible [power(256.0, nnn)] to return a decimal value <= 1.0.  For the power function, I had to use a floating point base (256.0) due to arithmetic overflows against the integer datatype.  Then, I worked in a formula to range the values between the upper and lower boundaries.  Then, cast the whole thing as an integer, to remain faithful to the resulting datatype we need.  I suppose you could cast it as a bigint or float, but my friend only needed an integer, so there you go.

I stress tested this to return random numbers up to 10M.

I hope the hours I spent on this saves you time!

Add Description to Scheduled Task Using Powershell

You want to create a Scheduled Task?  From the Scheduled Task GUI, it's simple.

You want to do so from the Scheduled Task command line (schtasks.exe)?  A bit more involved, but possible.

You want to set the Description field in a Scheduled Task from the command line in Powershell?  It CAN be done, with some clever use of schtasks.exe command line options, and Powershell's ability to manipulate XML.

In this post, I'll show you how to set the Description field of a Scheduled Task.

You certainly do not have to use Powershell, but its xml manipulation commands make it easier.  For those of you who don't use Powershell, you'll just have to cobble something together for some of these steps.

Here's a summary:

Step 1. Set a variable to be the XML export file.

Step 2. Set a variable to be the task name.

Step 3. Set a variable to be the Description.

Step 4. Set a variable to be the executable and arguments.

Step 5. Create the scheduled task.

Step 6. Export the task to an XML file.

Step 7. Delete the task.

Step 8. Load the XML file into a variable.

Step 9. In the XML variable, add a Description field, and set the Description field to that variable.

Step 10. Write the XML variable to a file.

Step 11. Create a scheduled task, this time from the XML file.

Step 12. You're done!

Step 1. Set a variable to be the XML export file.

Let's call the variable "$strOutputXMLFile".
$strOutputXMLFile="c:\scheduled_task.xml"

Step 2. Set a variable to be the task name.

Let's call the variable "$strScheduledTaskName".
$strScheduledTaskName="New Scheduled Task"

Step 3. Set a variable to be the Description.

Let's call the variable "$strDescription".
$strDescription="Here is content for the Description field."

Step 4. Set a variable to be the executable and arguments.

Let's call the variable "$strExecuteableAndArguments".
$strExecuteableAndArguments="backup.exe /all" # - totally bogus command, but you get the idea

Step 5. Create the scheduled task.

schtasks.exe /rl limited /create /tn "$strScheduledTaskName" /tr "e:\ScheduledTaskUpdateTools\run-scheduled-processes.bat daily_10_min_import_processes" /ru DOMAINNAME\DOMAINUSERNAME /rp "$3cr3t_4a$$w0rd" /sc weekly /d sun /st 02:00 /sd 07/12/2012

Let me dissect the command above for you:
schtasks.exe: Scheduled Task executable
'/rl limited': run with limited priveges. This is the default value, and you can omit it if you wish.
Use '/rl highest' to run with highest privileges.  I'm not really sure what this means, but I think it is the equivalent to "run as administrator."
'/create': Make a new scheduled task.
'/tn "$strScheduledTaskName"': This will be the name of your new scheduled task.
The name must be unique. If you're automating this, you'll need to delete the existing task that matches this name. You'll see how to do this.
'/tr "e:\ScheduledTaskUpdateTools\run-scheduled-processes.bat daily_10_min_import_processes"': This will be the executable the task should run, and the parameters to pass to the executable.
'/ru DOMAINNAME\DOMAINUSERNAME': This will be the domain account to run the executable under.
'/rp "$3cr3t_4a$$w0rd"': This will be the account password that works for DOMAINNAME\DOMAINUSERNAME.
Just to make sure the task will run as it should, I use "runas /profile /user:DOMAINNAME\DOMAINUSERNAME cmd.exe", enter the account password, then in the new command window, "e:\ScheduledTaskUpdateTools\run-scheduled-processes.bat daily_10_min_import_processes".
'/sc weekly /d sun /st 02:00 /sd 07/12/2012': Run the task every Sunday at 2am, starting on July 12, 2012.
If you omit the '/sd' parameter, Task Scheduler will assume today's date.
You may need to adjust the date format for your locale.

Just to help you out, here are parameters for some other schedules I use:
'/sc daily /st 06:00 /ri 5 /du 0013:00': Daily, between 6am and 7pm, every 5 minutes. /ri 5 ("run interval") means "run every 5 minutes". /du 0013:00 (duration 13 hrs) means run the task for 13 hours, and 6am+13hrs=19 -> 7pm.
'/sc daily /st 04:00': Daily at 4am
'/sc daily /st 22:00': Daily at 10pm
'/sc monthly /mo third /d sun /st 03:00': On the third Sunday of every month at 3am

Step 6. Export the task to an XML file.

schtasks.exe /query /tn "$strScheduledTaskName" /xml > "$strOutputXMLFile"
'/query': Look at a task, but don't change it.
'/tn "$strScheduledTaskName"': Name of the task to be exported
'/xml > "$strOutputXMLFile"': output the task as XML into c:\scheduled_task.xml.
Note that '> "c:\scheduled_task.xml"' must be the last parameter.

If you're not using Powershell, review the "c:\scheduled_task.xml" file.
It should read something like this:

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
  <RegistrationInfo>
    <Date>2012-07-12T09:58:09</Date>
    <Author>DOMAINNAME\DOMAINUSERNAME</Author>
  </RegistrationInfo>
  <Triggers>
    <CalendarTrigger>
      <StartBoundary>2012-07-15T04:00:00</StartBoundary>
      <Enabled>true</Enabled>
      <ScheduleByDay>
        <DaysInterval>1</DaysInterval>
      </ScheduleByDay>
    </CalendarTrigger>
  </Triggers>
  <Settings>
    <MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
    <DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
    <StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
    <AllowHardTerminate>true</AllowHardTerminate>
    <StartWhenAvailable>false</StartWhenAvailable>
    <RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
    <IdleSettings>
      <Duration>PT10M</Duration>
      <WaitTimeout>PT1H</WaitTimeout>
      <StopOnIdleEnd>true</StopOnIdleEnd>
      <RestartOnIdle>false</RestartOnIdle>
    </IdleSettings>
    <AllowStartOnDemand>true</AllowStartOnDemand>
    <Enabled>true</Enabled>
    <Hidden>false</Hidden>
    <RunOnlyIfIdle>false</RunOnlyIfIdle>
    <WakeToRun>false</WakeToRun>
    <ExecutionTimeLimit>PT72H</ExecutionTimeLimit>
    <Priority>7</Priority>
  </Settings>
  <Actions Context="Author">
    <Exec>
      <Command>x:\ScheduledTaskUpdateTools\run-scheduled-processes.bat</Command>
      <Arguments>daily_multihour_update_processes</Arguments>
    </Exec>
  </Actions>
  <Principals>
    <Principal id="Author">
      <UserId>DOMAINNAME\DOMAINUSERNAME</UserId>
      <LogonType>Password</LogonType>
      <RunLevel>LeastPrivilege</RunLevel>
    </Principal>
  </Principals>
</Task>

Step 7. Delete the task.

Why?  Because you will re-create the task soon. You can't have two Scheduled Tasks with the same name.
schtasks.exe /delete /tn "$strScheduledTaskName" /f
'/delete': delete the task
'/tn "New Scheduled Task"': Name of the task to be deleted
'/f': Do not confirm; delete the task with no warning message.

Step 8. Load the XML file into a variable.

If you're using Powershell, use this command:
[xml] $xmlScheduledTask = get-content "$strOutputXMLFile"
'[xml] $xmlScheduledTask' typecasts the text content from "c:\scheduled_task.xml" as an xml variable.

If you're not using Powershell, and your language cannot create an xml variable, load the file as a string.

Step 9. In the XML variable, add a Description field, and set the Description variable to that field.

If you're using Powershell, use these commands:
# - the "NamespaceURI" part prevents adding the "xmlns" attribute to the "Description" tag
# - leaving the "xmlns" attribute will let the "create scheduled task" command below fail silently
$xmlDescription=$xmlScheduledTask.CreateElement("Description", $xmlScheduledTask.DocumentElement.NamespaceURI)
$xmlDescription.set_InnerXML("$strDescription")
$xmlScheduledTask.Task.RegistrationInfo.AppendChild($xmlDescription) | out-null

If you're not using Powershell, and your language cannot manipulate xml variables, you'll most likely need to add a <Description> tag to the XML string using string functions. To see where to add the <Description> tag, create a new scheduled task with a Description using the GUI, then use the '/query /xml' parameters of the schdtasks.exe command to output the text, then review that XML.  XML is very particular about hierarchy and tag names.

For example, in my case, the updated XML file (with the new Description tag) read something like this:

<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
  <RegistrationInfo>
    <Date>2012-07-12T09:58:09</Date>
    <Author>DOMAINNAME\DOMAINUSERNAME</Author>
    <Description>Here is content for the Description field.</Description>
  </RegistrationInfo>
  <Triggers>
...

Step 10. Write the XML variable to a file.

If you're using Powershell, use this command:
$xmlScheduledTask.save("$strOutputXMLFile")

Step 11. Create a scheduled task, this time from the XML file.

schtasks.exe /create /tn "$strScheduledTaskName" /ru DOMAINNAME\DOMAINUSERNAME /rp "$3cr3t_4a$$w0rd" /xml "$strOutputXMLFile"

Step 12. You're done!

Your scheduled task now has a Description!

Here is code I use, along with some extras. Please note that I had to change some parts of it for security reasons, but the essentials are there.

# - I use this function to run executables in my powershell scripts.

# - I like it because I have the option of waiting for the executable

# - to finish or not.  Use "-waitforexit $true" to wait until the

# - executable is finished.

function Start-Proc  {

    param (

        [string]$exe = $(Throw "An executable must be specified"),

        [string]$arguments="",

        [switch]$hidden=$false,

        [switch]$waitforexit

    )

    # Build Startinfo and set options according to parameters

    $startinfo = new-object System.Diagnostics.ProcessStartInfo

    $startinfo.FileName = $exe

    $startinfo.Arguments = $arguments

    if ($hidden -eq $true) {

        $startinfo.WindowStyle = "Hidden"

        $startinfo.CreateNoWindow = $true

    }

    $process = [System.Diagnostics.Process]::Start($startinfo)

    if ($waitforexit) { $process.WaitForExit() }

}

$strScheduledTaskName="New Scheduled Task"

$strEnabled="true" # - set to "false" to create, but disable this scheduled task. helpful if you want to create the task now, but you want to enable it manually later.

$strDescription="This is the description for this task."

$strOutputXMLFile="c:\scheduled_task.xml"

$strScheduledTaskAccount="DOMAINNAME\DOMAINUSERNAME"

$strScheduledTaskPassword="$3cr3t_4a$$w0rd"

$strExecuteableAndArguments="backup.exe /all" # - totally bogus command, but you get the idea

$strRLValue="limited" # - use "limited" or "highest"

$strScheduledTaskArgs="/sc daily /st 22:00 /sd {MM/DD/YYYY}" # - daily at 10pm, starting today

# $strScheduledTaskArgs="/sc weekly /d sun /st 01:30 /sd {MM/DD/YYYY_PLUS_7_DAYS}" # - Every Sunday at 1:30am, starting 7 days from today

# $strScheduledTaskArgs="/sc monthly /mo third /d sun /st 03:00 /sd {MM/DD/YYYY_PLUS_3_DAYS}" # - On the third Sunday of every month at 3am, starting 3 days from today

$strHH=(get-date)

$strHH=(get-date $strHH -uformat "%H")

$strHHPlusOne=(get-date) + (new-timespan -hours 1)

$strHHPlusOne=(get-date $strHHPlusOne -uformat "%H")

$strMMDDYYYY=(get-date)

$strMMDDYYYY=(get-date $strMMDDYYYY -uformat "%m/%d/%Y")

$strMMDDYYYYPlus3Days=(get-date) + (new-timespan -days 3)

$strMMDDYYYYPlus3Days=(get-date $strMMDDYYYYPlus3Days -uformat "%m/%d/%Y")

$strMMDDYYYYPlus7Days=(get-date) + (new-timespan -days 7)

$strMMDDYYYYPlus7Days=(get-date $strMMDDYYYYPlus7Days -uformat "%m/%d/%Y")

$strHHMMMinusOneMinute=(get-date) - (new-timespan -minutes 1)

$strHHMMMinusOneMinute=(get-date $strHHMMMinusOneMinute -uformat "%H:%M")

$strScheduledTaskArgs=$strScheduledTaskArgs.replace("{HH}", $strHH)

$strScheduledTaskArgs=$strScheduledTaskArgs.replace("{HH_PLUS_ONE}", $strHHPlusOne)

$strScheduledTaskArgs=$strScheduledTaskArgs.replace("{HH:MM_MINUS_ONE_MINUTE}", $strHHMMMinusOneMinute)

$strScheduledTaskArgs=$strScheduledTaskArgs.replace("{MM/DD/YYYY}", $strMMDDYYYY)

$strScheduledTaskArgs=$strScheduledTaskArgs.replace("{MM/DD/YYYY_PLUS_3_DAYS}", $strMMDDYYYYPlus3Days)

$strScheduledTaskArgs=$strScheduledTaskArgs.replace("{MM/DD/YYYY_PLUS_7_DAYS}", $strMMDDYYYYPlus7Days)

# - create the scheduled task only so we can fetch the xml later

$strArguments="/c schtasks.exe /rl $strRLValue /create /tn `"$strScheduledTaskName`" /tr `"$strExecuteableAndArguments`" /ru $strScheduledTaskAccount /rp `"$strScheduledTaskPassword`" $strScheduledTaskArgs"

$strModifiedArguments=$strArguments

$strModifiedArguments=$strModifiedArguments.replace($strScheduledTaskPassword, "*****") # - output $strModifiedArguments to show the command line without the password

start-proc "cmd.exe" -arguments $strArguments -waitforexit

# - create the xml file

$strArguments="/c schtasks.exe /query /tn `"$strScheduledTaskName`" /xml > `"$strOutputXMLFile`""

start-proc "cmd.exe" -arguments $strArguments -waitforexit

# - delete the task so we can re-create it using the xml file

$strArguments="/c schtasks.exe /delete /tn `"$strScheduledTaskName`" /f"

start-proc "cmd.exe" -arguments $strArguments -waitforexit

# - fetch the xml file

[xml] $xmlScheduledTask = get-content "$strOutputXMLFile"

# - add a description element to the xml object

# - the "NamespaceURI" part prevents adding the "xmlns" attribute to the "Description" tag

# - leaving the "xmlns" attribute will let the "create scheduled task" command below fail silently

$xmlDescription=$xmlScheduledTask.CreateElement("Description", $xmlScheduledTask.DocumentElement.NamespaceURI)

$xmlDescription.set_InnerXML("$strDescription")

$xmlScheduledTask.Task.RegistrationInfo.AppendChild($xmlDescription) | out-null

$xmlScheduledTask.Task.Settings.Enabled=$strEnabled

# - write the xml file

$xmlScheduledTask.save($strOutputXMLFile)

$strArguments="/c schtasks.exe /create /tn `"$strScheduledTaskName`" /ru $strScheduledTaskAccount /rp `"$strScheduledTaskPassword`" /xml `"$strOutputXMLFile`""

$strModifiedArguments=$strArguments

$strModifiedArguments=$strModifiedArguments.replace($strScheduledTaskPassword, "*****")

start-proc "cmd.exe" -arguments $strArguments -waitforexit

"Foreign key constraint" that references more than one table

It's been a while since I've blogged something. I've just been busy!

It will actually use a UDF and check constraints to mimic a foreign key constraint, but it will enforce referential integrity against more than one table.

Some of my developers need to save records to a table from values retrieved from and referenced by one other table.  This is, of course, nothing more than a foreign key constraint.  Simple enough.

Now, they need to save two more special-case values, but cannot add them to the reference table.  You'll see why in a minute.

(Explaining the real scenario would require getting you bogged down in far more details than what I actually implemented, so please allow me to contrive a story for the purposes of greatly shortening things.)

Users work for a company that makes combination locks for toddlers.  (I have a 16-month old, so I guess that's part of where I got the idea from. Roll with me, here.) They have a search dialog box.  In the search dialog, they have select lists for a weird combination lock with colors as the combination.  The lock has 3 positions.  For example, a lock could have red-green-blue.

The current search dialog looks for customers with specific color combinations in those three positions.

For business reasons, you have to save each search to a separate table, called LockSearchHistory.  That LockSearchHistory has foreign key constraints against a table called ColorList.  There are three FK constraints, one for each position.

Now, the company has invented a new lock with 4 positions: Example: orange-blue-yellow-black.

And now, they want to modify the search dialog for all customers, regardless of if it's a 3 position or 4 position lock, and users want to specify the position of only the colors they know.

In other words, for a 3-position lock, they want to search for: (any color)-green-blue-(no color).

For a 4-position lock, they want to search for: orange-(any color)-(any color)-black.

You still have to save each search to the LockSearchHistory table.

You definitely don't want to add "any color" and "no color" values to your ColorList table, because then users might accidentally enter them when adding new customer locks.  I suppose you could add "any" and "none" with some kind of flags, but that requires more application and schema changes than just a search dialog box should need.  (There will be application and schema changes, just smart ones.)

Here's (what I think is) an elegant solution, step-by-step.

1. Delete the three current foreign key constraints, one for each position:

if exists (select * from sys.foreign_keys where object_id = object_id(N'[dbo].[FK_LockSearchHistory_PositionOne]') and parent_object_id = object_id(N'[dbo].[LockSearchHistory]'))

alter table LockSearchHistory drop constraint FK_LockSearchHistory_PositionOne

go

if exists (select * from sys.foreign_keys where object_id = object_id(N'[dbo].[FK_LockSearchHistory_PositionTwo]') and parent_object_id = object_id(N'[dbo].[LockSearchHistory]'))

alter table LockSearchHistory drop constraint FK_LockSearchHistory_PositionTwo

go

if exists (select * from sys.foreign_keys where object_id = object_id(N'[dbo].[FK_LockSearchHistory_PositionThree]') and parent_object_id = object_id(N'[dbo].[LockSearchHistory]'))

alter table LockSearchHistory drop constraint FK_LockSearchHistory_PositionThree

go


2. Add a new column to the LockSearchHistory table, say, called "PositionFour".

3. Create a new table, "ListNoneAny," with "*no color*" and "*any color*" values.

4. Create a user-defined function (UDF), called "InColorListOrListNoneAnyFN":

create function dbo.InColorListOrListNoneAnyFN (@strColor varchar(50))
returns bit as
begin
declare @bitReturn bit=0
if exists(
select 1 from ColorList where PositionOne=@strColor
union
select 1 from ColorList where PositionTwo=@strColor
union
select 1 from ColorList where PositionThree=@strColor
union
select 1 from ColorList where PositionFour=@strColor
union
select 1 from ListNoneAny where Title=@strColor
) set @bitReturn=1
return @bitReturn
end

5. Add four check constraints, one for each position:

alter table LockSearchHistory with nocheck
 add constraint CK_LockSearchHistory_PositionOne check ((dbo.InColorListOrListNoneAnyFN(PositionOne))=1)

go

alter table LockSearchHistory check constraint CK_LockSearchHistory_PositionOne

go

alter table LockSearchHistory with nocheck
 add constraint CK_LockSearchHistory_PositionTwo check ((dbo.InColorListOrListNoneAnyFN(PositionTwo))=1)

go

alter table LockSearchHistory check constraint CK_LockSearchHistory_PositionTwo

go

alter table LockSearchHistory with nocheck
 add constraint CK_LockSearchHistory_PositionThree check ((dbo.InColorListOrListNoneAnyFN(PositionThree))=1)

go

alter table LockSearchHistory check constraint CK_LockSearchHistory_PositionThree

go

alter table LockSearchHistory with nocheck
 add constraint CK_LockSearchHistory_PositionFour check ((dbo.InColorListOrListNoneAnyFN(PositionFour))=1)

go

alter table LockSearchHistory check constraint CK_LockSearchHistory_PositionFour

go

Now, you effectively have a foreign key constraint that references more than one table.

In closing, when tweeting #sqlhelp about my problem, Brent Ozar himself suggested using indexed views.  But, indexed views don't allow unions.  Because of this, I think he assumed that the view would use a join.  So, the only way was with a UDF and check constraints.  I am only saying this because I googled a ton, and found several suggestions and examples to use indexed views for this sort of thing.