I want to constrain a SQL Server decimal column to only allow -1,0,1 as valid values.

Can you show me the SQL syntax for adding such a constraint. (I would like to know how to do it in both the CREATE TABLE statement and/or the ALTER TABLE ADD CONSTRAINT).

Or can this only be accomplished in a trigger?

Seth

I want to constrain a SQL Server decimal column to only allow -1,0,1 as valid values.

Can you show me the SQL syntax for adding such a constraint. (I would like to know how to do it in both the CREATE TABLE statement and/or the ALTER TABLE ADD CONSTRAINT).

Or can this only be accomplished in a trigger?

Seth

Hello,

I want to constrain a SQL SERVER decimal column to only allow -1,0,1 as valid values.

Can you show me the SQL syntax for adding such a constraint. (I would like to know how to do it in both the CREATE TABLE statement and/or the ALTER TABLE ADD CONSTRAINT).

Or can this only be accomplished in a trigger?

Seth

Can’t wait to see “The Blind Side”. For a summary of the story. http://tinyurl.com/nztkt. Or read the book. http://tinyurl.com/ycbd3sg

Just recovered from a killer headache. How much good a little nap can do.

Good thing CJ Spiller is AWESOME.

Is it me or does clemson REALLY need a kicker.

New blog post – Cool SQL script and batch file for automating SQL Backups http://tinyurl.com/ye86nwx

I had a need to automate SQL Backups to occur on a 10 minute schedule.  Also, the new backups needed to be backed up to another server share on the same schedule.

In the past I did this by creating a sql script that does the backup, calling the script using sqlcmd from a batch file, and calling the batch file using the built-in windows task scheduler.  The backup filename has to be auto-named with a date/time stamp since it occurs every 10 minutes.  I just contrived a file name in the sql script using the Cast operations on the sql getdate() function.

This approach has a big downside, though, in that the BATCH file that calls the script does not know the name of the backup file that was created by the sql script.  Because of this the batch file cannot do a copy of the new backup to another server.

In the past I resolved this by simply installing a backup software solution like SyncBack Freeware or SyncToy and setting it to do a folder syncronization in a 5 minute offset from the backup.  This has worked but it is a shame since DOS batch files can do copies and the the sync’ing solutions, while free, still have to be downloaded, installed, and configured.

The ideal solution would be to have the BATCH file determine the filename and then pass the name to the sqlcmd script.  Then, since the batch file would know the name of the backup file, it could then just copy it as soon as the backup completed.

I always knew this could be done but never took the time to figure it out…until today.

Here are the results of the work.

First the batch file.  Remember, this is what you will put into the windows scheduler.

REM BATCH FILE

@echo off
REM Set the following environment variables to match your environment
SET server-name=.\SQLExpress
SET db-name=YOUR-DB-NAME
SET sql-script-path=FULL-PATH-TO-SQL-SCRIPT\backupDB.sql
REM BE sure that the below path includes the final backslash (\)
SET backup-path=FULL-PATH-FOR-SQL-TO-OUTPUT-TO-INCLUDING-BACKSLASH
SET target-copy-path=PATH-TO-COPY-BACKUP-TO-USUALLY-A-UNC-OR-MAPPED-DRIVE

FOR /f “tokens=1,2,3,4 delims=/ ” %%i in (“%date%”) do set my-date=%%j-%%k-%%l
FOR /f “tokens=1,2,3 delims=:. ” %%m in (“%time%”) do set my-time=%%m-%%n-%%o
SET current-date-time=%my-date%_%my-time%
sqlcmd -S”%server-name%” -E -i”%sql-script-path%”

copy C:\SQL_BACKUPS\%db-name%_db_%current-date-time%.BAK %target-copy-path%

A couple of things to note.

First the only thing you should need to change is the variable assigments at the top.

Second, note that the tricky part of the batch file is the backup filename timestamp.  To figure out that I had to drill into the DOS FOR command with the /f switch.  This is where I put in the most time.  The following webpages were extremely helpful.

http://www.computerhope.com/forhlp.htm

http://www.computerhope.com/issues/ch000987.htm

Now the sql script.

–SQL SCRIPT FOR DOING SQL BACKUPS

DECLARE @dateString VARCHAR(30)
DECLARE @DBNAME VARCHAR(100)
DECLARE @BACKUPPATH VARCHAR(100)
DECLARE @sql VARCHAR(1000)

–date_time var is passed in from the batch file
SET @dateString=’$(current-date-time)’
SET @DBNAME=’$(db-name)’
SET @BACKUPPATH=’$(backup-path)’

SELECT @SQL = ‘BACKUP DATABASE ‘+@DBNAME+’ TO DISK =”’+@BACKUPPATH+@DBNAME+’_db_’ + @dateString +’.BAK” WITH INIT’
EXEC (@SQL)

In this case I assign the DOS CMD variables to SQL variables and then call the script that uses them.  I suppose I could have referenced the DOS CMD variables directly but this way all of the changable params are at the top and, most importantly, the SQL BACKUP statement is re-usable in a non-sqlcmd environment.

I think the coolest thing I learned is that you do not have to pass the SQL CMD variables to the sqlcmd using the -v switch.  You could do this but the following post explains that sqlcmd can use the DOS CMD variables directly.  In fact, they take precedence.

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

Enjoy.

Seth

PS Click below to download the script and batch file.  I also include the old files in case your interested in how to derive the datetime string using getdate(). For the record, I got that script from somewhere else on the internet.

DOWNLOAD SCRIPTS