Archive for category SQL Server
This is the SQL to get data between two dates, I know lots of people find datediff() confusing so this is a nice cut and paste script to have handy:
select * from tableName where datediff(day,'2011-06-27 12:28:34.480',theDate)>=0 and datediff(day,'2011-06-28 12:28:34.480',theDate)<=0
The first date in that string is the start date and the second is the end date. You can change the ‘day’ parameter to any amount you wish (second, month, year). You should always use datediff() to get the difference between dates in SQL Server as it will handle issues like 30, 31 and 28 day months, leap years, etc. datediff() is odd in that if you run this:
select datediff(day,'2011-06-27 12:28:34.480','2011-06-28 12:28:34.480')
It will give you a whole number, in this case 1. So when the left side is less than the right side we get positive numbers. This seems backwards to the more natural seeming syntax of greater than / less than:
select * where date1 > date2
Unfortunately you can’t use greater than / less than, this is because even though they work, they will compare down to the nanosecond, which is not generally what we want.
If you need to do this in your .Net dataset here are two examples of how to do it. Two because the way you write your query will vary depending on what database driver you’re using.
For SQL Server 2000:
select * from tableName where datediff(day,?,theDate)>=0 and datediff(day,?,theDate)<=0
For SQL Server 2005+:
select * from tableName where datediff(day,@StartDate,theDate)>=0 and datediff(day,@EndDate,theDate)<=0
Remember that if you have issues the default way to use this is with question (?) marks. Question marks were used to denote parameters all the way back to ADO in VB 6. The downside of using question marks is your parameters must be added to your code in the order they are in the query. (In Oracle they always have to be added in the order of parameters in the query, despite being named. Unless they’ve fixed that since I last used the Oracle driver).
Just a quick post with a simple example trigger I put together. I’ve noticed allot of people seem to think that cursors are necessary to write a trigger, not so. In fact cursors are a bad way of doing things in general. They’re slow and run one command at a time, they loose out on the speed increase of running an update in batch. Al-Farooque Shubho touches on this concept in his article “Understanding “Set based” and “Procedural” approaches in SQL”.
Here’s the trigger, it’s written to audit changes on a date field. You need to create a table to handle the insert for this. Remember two things here:
1) The new table needs to have exactly the same data types (and null ability) of the parent table, but NOT the default values.
2) The primary key of the origin table will NOT be the same in the audit table. This is because a row can be changed multiple times, auditing those changes means the same primary key being inserted over and over. In my example I create a new column called EventOccuranceTime which is defaulted to getdate(). I then used the primary tables unique identifier plus the EventOccuranceTime as the PK in the new table. Note that triggers can seriously slow down your data access, putting a primary key on the audit table will slow down updates / inserts into the parent table more yet. If this speed reduction is an issue for you you may not want a primary key on the audit table, just take the hit when reading from the data (no index) instead of writing it with an index. I’ve included the table create script below as well:
CREATE TABLE [dbo].[SomeTable_audit]( [ROWID] [int] NOT NULL, [EventOccuranceTime] [datetime] NOT NULL, [FromDATE] [datetime] NULL, [ToDATE] [datetime] NULL, CONSTRAINT [PK_audit] PRIMARY KEY CLUSTERED ( [ROWID] ASC, [EventOccuranceTime] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[SomeTable_audit] ADD CONSTRAINT [DF_SomeTable_audit_EventOccuranceTime] DEFAULT (getdate()) FOR [EventOccuranceTime] GO
Something to know about triggers, they have two special tables available in them. One is the rows inserted, the other the rows deleted. If you made updates then they are tables that hold all the rows that were updated (treating old data as deleted and new data as inserted), even if only one column was updated.
If you change this trigger to include inserts and deletes the join below will thwart that. You will need to change this join to allow left and / or right joins depending on inserts / deletes, or write multiple queries, one for inserts, one for deletes, one for updates. You can write more than one query in a single trigger.
create trigger dbo.audit_SomeTable on dbo.SomeOriginTable for update -- This could include insert and delete, mine only handles updates. as if update(desired_date) --if the desired_date field is changing do the insert begin --Code thanks to https://anthonystechblog.wordpress.com --Feel free to use, change and re-distribute the code you find on my site, --all I ask is you leave these these comments intact! insert into dbo.SomeTable_audit (rowid,FromDATE,ToDATE) select i.rowid,d.desired_date,i.desired_date from inserted i join deleted d on i.rowid = d.rowid where datediff(day,d.desired_ship_date,i.desired_ship_date)<>0 --added because update() doesn't always seem to work, --I believe it determines it's being updated if it's passed in the query, not if it's actually different. end go
Here’s a great MSDN magazine article with more examples of triggers (including triggers that prevent certain kinds of updates):
Exploring SQL Server Triggers.
Unfortunately sometimes copy / paste from Excel into SQL server doesn’t work. Since moving data from Excel into SQL server (or another DBMS) can sometimes prove trickier than it should be I’ve created this handy script to help with just that. To get it to work:
1) Paste it into your excel project as a macro.
2) Select the sheet you want to copy
3) The first row of your sheet must contain the column names of the table to be inserted into.
4) Update the macro to use the correct table (look for TABLE_NAME_HERE)
That’s it! Put your cursor in the method and press F5. Upon completion you’ll get a message box with “complete” in it. Once the file is generated copy the contents of c:\temp\insert.txt into Management Studio / Toad / etc and run it.
Sub GenerateInsertRecords() 'Code thanks to https://anthonystechblog.wordpress.com 'Feel free to use, change and re-distribute the code you find on my site, 'all I ask is you leave these these comments intact! Dim i As Integer, ii As Double, iColumn as integer Dim str As String, strInsert As String, strComplete As String str = "" str = "insert into TABLE_NAME_HERE (" For i = 1 To 999 If Len(Cells(1, i)) < 1 Then Exit For str = str & Cells(1, i) & "," Next i str = Left(str, Len(str) - 1) str = str & ") values (" Dim oConvert As Variant strInsert = str strComplete = "" For ii = 2 To 65000 If Len(Cells(ii, 1)) < 1 Then Exit For strComplete = strComplete & strInsert str = "" For iColumn = 1 To i - 1 oConvert = getInput(Cells(ii, i)) str = str & oConvert & "," Next iColumn str = Left(str, Len(str) - 1) str = str & ")" strComplete = strComplete & str & vbNewLine Next ii Dim oFS As Scripting.FileSystemObject Set oFS = CreateObject("Scripting.FileSystemObject") Dim oText As Scripting.TextStream Set oText = oFS.OpenTextFile("c:\temp\insert.txt", ForWriting, True) oText.Write (strComplete) oText.Close MsgBox ("complete") End Sub Function getInput(theInput As Variant) As Variant Dim retval As Variant If IsNull(theInput) Then retval = "Null" ElseIf UCase(theInput) = "NULL" Then retval = "Null" ElseIf IsNumeric(theInput) Then retval = theInput Else retval = "'" & theInput & "'" End If getInput = retval End Function
This always seems to be an issue that crops up. Copying a database sounds like something that should be simple to do, in practice there are a number of steps, and varying methods to do so. I had a couple co-workers who wanted a nightly refresh of test data, they were manually copying the database which was taking quite a bit of time so I put this together for them.
I’ve broken the copy into two parts since depending on your needs you many find each useful on their own.
Part 1) How to script a backup of a MS SQL Server Database:
There’s lots of examples on how to do this on the internet, I’ve used variables and separated mine into exec statements since many of the backup and restore commands don’t accept variables as input (annoying!). Here’s the script:
--Code thanks to https://anthonystechblog.wordpress.com --Feel free to use, change and re-distribute the code you find on my site, --all I ask is you leave these these comments intact! use master go declare @dbToCopy varchar(1000), @sql varchar(1000), @dbLogsDir varchar(5000), @dbDatabaseDir varchar(5000) --You will need to point this at your log file directory set @dbLogsDir = 'c:\temp\' --You will need to point this at your DB file directory set @dbDatabaseDir = 'c:\temp\' set @dbToCopy = 'dbToCopy' set @sql = 'DBCC SHRINKDATABASE (N''' + @dbToCopy + ''', 0,TRUNCATEONLY)' exec(@sql) set @sql = '''' + @dbLogsDir + @dbToCopy + '_LogBAK.LDF''' set @sql = 'backup log ' + @dbToCopy + ' to disk = ' + @sql exec(@sql) set @sql = '''' + @dbDatabaseDir + @dbToCopy + '_Data.BAK''' set @sql = 'backup database ' + @dbToCopy + ' to disk = ' + @sql exec(@sql)
If you run that you will have your backup log file and db. To attach it as a different DB name use this:
--Code thanks to https://anthonystechblog.wordpress.com --Feel free to use, change and re-distribute the code you find on my site, --all I ask is you leave these these comments intact! use master go declare @dbToCreate varchar(1000), @filePrefix varchar(1000), @sql varchar(1000), @dbLogsDir varchar(5000), @dbDatabaseDir varchar(5000) set @dbLogsDir = 'c:\temp\' set @dbDatabaseDir = 'c:\temp\' set @filePrefix = 'dbToCopy' --Assuming your DB's logicalName is 'DATABASE_Data' and your log's name is --'DATABASE_Log' @filePrefix will need to be 'DATABASE'. This script doesn't --support logical names that vary more than this (you would have to extend --it to get that) set @dbToCreate = 'dbToCreate' if exists(select * from master.dbo.sysdatabases where name = @dbToCreate) begin set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' --needed to kick users out of our to be created db and keep them out. --(backup restore can create un restore-able files if you don't do this) exec(@sql) end if exists(select * from master.dbo.sysdatabases where name = @dbToCreate) begin set @sql = 'drop database ' + @dbToCreate exec(@sql) end set @sql = 'restore database ' + @dbToCreate + ' from disk = ''' + @dbDatabaseDir + @dbToCreate + '_Data.BAK''' set @sql = @sql + ' with replace, move ''' + @filePrefix + '_Data'' to ''' + @dbDatabaseDir + @dbToCreate + '_Data.MDF'',move ''' + @filePrefix + '_Log'' to ''' + @dbLogsDir + @dbToCreate + '_Log.LDF''' exec(@sql) set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET multi_user' exec(@sql) -- These may fail on your server as xp_cmdshell is probably disabled. --For security reasons it is probably best left disabled, --but if you do want it on (and have admin privilege) this is how you do it. set @sql = 'xp_cmdshell ''del ' + @dbLogsDir + @dbToCreate + '_LogBAK.LDF''' --this will delete the log file back up from the earlier step exec(@sql) set @sql = 'xp_cmdshell ''del ' + @dbDatabaseDir + @dbToCreate + '_Data.BAK''' --this will delete the db file back up from the earlier step exec(@sql)
I decided to take all of this and create an easily run stored procedure from it, this is the stored procedure:
create procedure spCopyDatabaseEx(@dbToCopy varchar(1000), @dbToCreate varchar(1000), @filePrefix varchar(1000), @dbLogsDir varchar(1000), @dbDatabaseDir varchar(1000)) as --Code thanks to https://anthonystechblog.wordpress.com --Feel free to use, change and re-distribute the code you find on my site, --all I ask is you leave these these comments intact! declare @sql varchar(1000) set @sql = 'DBCC SHRINKDATABASE (N''' + @dbToCopy + ''', 0,TRUNCATEONLY)' exec(@sql) if exists(select * from master.dbo.sysdatabases where name = @dbToCreate) begin set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' exec(@sql) end if exists(select * from master.dbo.sysdatabases where name = @dbToCreate) begin set @sql = 'drop database ' + @dbToCreate exec(@sql) end set @sql = '''' + @dbLogsDir + @dbToCreate + '_LogBAK.LDF''' set @sql = 'backup log ' + @dbToCopy + ' to disk = ' + @sql exec(@sql) set @sql = '''' + @dbDatabaseDir + @dbToCreate + '_Data.BAK''' set @sql = 'backup database ' + @dbToCopy + ' to disk = ' + @sql exec(@sql) set @sql = 'restore database ' + @dbToCreate + ' from disk = ''' + @dbDatabaseDir + @dbToCreate + '_Data.BAK''' set @sql = @sql + ' with replace, move ''' + @filePrefix + '_Data'' to ''' + @dbDatabaseDir + @dbToCreate + '_Data.MDF'',move ''' + @filePrefix + '_Log'' to ''' + @dbLogsDir + @dbToCreate + '_Log.LDF''' exec(@sql) set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET multi_user' exec(@sql) set @sql = 'xp_cmdshell ''del ' + @dbLogsDir + @dbToCreate + '_LogBAK.LDF''' exec(@sql) set @sql = 'xp_cmdshell ''del ' + @dbDatabaseDir + @dbToCreate + '_Data.BAK''' exec(@sql) go
To try it run this:
exec spCopyDatabaseEx 'northwind', 'northwindNew', 'Northwind', 'e:\mssql\logs\', 'e:\mssql\data\'
Best of luck! As always feel free to ask questions / make suggestions. I’m happy to answer your questions.
So after yesterday’s post, “Password encryption in SQL Server, how to tell if a user is using a weak password“, I was curious so I prodded a bit further. I wanted to know just how secure (or not) passwords in SQL Server are. Now remember that you have to have access to the sysxlogins to be able to do this, so the very first rule is: NO ONE should have access to sysxlogins! The second one is: Protect your administrator id’s and passwords! The third rule is: Consider migrating to integrated authentication if you’re serious about security, that way the passwords aren’t stored in SQL Server in the first place.
If you’ve not heard of it, there are quite a few sites that list the ‘top worst passwords to use‘. Using a list like this you can easily populate a SQL server database, if you really want to get all the bad passwords add all the words in the dictionary as well (though it will significantly slow down the query you’ll run later).
Your table should be one column, call it ‘password’. Once you have this table you can run this sql: (change the bold text to match your table name)
select l.name, bp.password from master.dbo.sysxlogins as l, master.dbo.badPasswords as bp where pwdcompare(bp.password,l.password)=1
Depending on how many passwords there are in your password database will depend on how long this will take to run. Also it could be more efficient. For example this query is running pwdcompare for every column in the password database, even if it’s already found a match. You could have it exit as soon as a match is found to make it much faster. In any case it will likely give you a scary list showing you just how many people are using ‘bad passwords’.
For completeness you can check how many people have passwords the same as their id by running this:
select l.name from master.dbo.sysxlogins as l where pwdcompare(l.name,l.password)=1 or pwdcompare(lower(l.name),l.password)=1
That one doesn’t require a table be set up, you can run it right now!
So now that I have you sufficiently paranoid, I hope you consider migrating to integrated authentication!
This query is much faster for determining the bad passwords (note: it only saves time for users that are using a bad password, users that can’t be found still have to be checked vs every pwd in the table). On my machine the query above takes 28 seconds, this new one takes 17.
select name, pwd from ( select sysx.name, ( select top 1 bp.password from master.dbo.badPasswords as bp where pwdcompare(bp.password,sysx.password)=1 ) as pwd from master.dbo.sysxlogins as sysx ) as results where pwd is not null
I’ve made some interesting discoveries recently. I’ve used MS SQL Server for years now, but never gave much thought to how user ids and passwords are stored internally, until today.
Today we had an issue deleting a user from the server, for some reason the delete method was unable to delete the user, but it was able to tell me a vague reason why it failed. This is annoying as I think the delete should be able to handle all situations, but really it’s ultimately no one’s fault but my companies for using SQL Server 2000 for so long.
The error I received was:
“Login ‘TheLoginName’ is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. (.Net SqlClient Data Provider)”
Pretty annoying, no? I read a number of articles and wound up cleaning up the issue, I won’t go into that issue in any further detail since this article is about password encryption, perhaps another time. In the process of fixing this issue I started checking out just how SQL server manages user id’s and passwords. If you’ve never seen it there is a table that SQL Server uses that stores all user id’s and passwords, to view it run this:
select * from master.dbo.sysxlogins
apparently this works on newer versions of SQL Server:
SELECT * FROM sys.sql_logins
In this data you’ll notice there is a ‘password’ column. It will look something like this:
Note that if you run it multiple times the hash is different, it will not give you the same hash for a single password.
Note that if you wish to avoid writing the Hash & Salt code necessary to have strong password storage and are eyeing up pwdencrypt, don’t. Microsoft has provided a better (not deprecated) method to do password hashing with hashbytes.
eg of using hashbytes:
DECLARE @HashThis nvarchar(4000); SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf'); SELECT HashBytes('SHA1', @HashThis); GO
So, it seems I’ve been taking a bit of the scenic route getting to the point of this article.. sorry! There were lots of good tidbits I found out on this journey of mine that I wanted to share!
Here’s the guts of how to tell if a user is using a weak password Microsoft SQL Server provides a method for comparing passwords called pwdcompare. This method almost seems like a security issue to me, but here it is. Do you have users that tend to use the word ‘password’ as their password? Run this to see who they are:
SELECT name FROM master.dbo.sysxlogins WHERE PWDCOMPARE('password', password) = 1
Use this on newer installations of SQL Server:
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('password', password_hash) = 1
Now, this method, PWDCOMPARE, seems to me to open up MS SQL Server users to dictionary attacks. It wouldn’t take much to run this method against a 1000 or so passwords to take a bunch of user’s passwords, but I digress. It is pretty useful to tell your users to smarten up and not use weak passwords.
Also interesting on the password migration note. If you need to migrate passwords from Microsoft SQL Server 2005 (or higher I’d imagine) run the script available on this page:http://support.microsoft.com/kb/918992. If you need to migrate passwords from Microsoft SQL Server 2000 replace the sp_help_revlogin procedure with the one below (Thanks Wardy IT).
As always feel free to post your comments / questions below. I always enjoy hearing your thoughts!
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @xstatus int DECLARE @binpwd varbinary (256) DECLARE @txtpwd sysname DECLARE @tmpstr varchar (256) DECLARE @SID_varbinary varbinary(85) DECLARE @SID_string varchar(256) IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' PRINT 'DECLARE @pwd sysname' WHILE (@@fetch_status -1) BEGIN IF (@@fetch_status -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@xstatus & 4) = 4 BEGIN -- NT authenticated account/group IF (@xstatus & 1) = 1 BEGIN -- NT login is denied access SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + '''' PRINT @tmpstr END ELSE BEGIN -- NT login has access SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + '''' PRINT @tmpstr END END ELSE BEGIN -- SQL Server authentication IF (@binpwd IS NOT NULL) BEGIN -- Non-null password EXEC sp_hexadecimal @binpwd, @txtpwd OUT IF (@xstatus & 2048) = 2048 SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')' ELSE SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')' PRINT @tmpstr EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = ' END ELSE BEGIN -- Null password EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = ' END IF (@xstatus & 2048) = 2048 -- login upgraded from 6.5 SET @tmpstr = @tmpstr + '''skip_encryption_old''' ELSE SET @tmpstr = @tmpstr + '''skip_encryption''' PRINT @tmpstr END END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO
Infor ERP – Visual Enterprise is something of a niche product. Try searching the internet for it and you’ll see what I mean. There are very few hits and even fewer sites that offer help documentation on how to develop for it.
Well, finally I found a blog written by a fellow that seems to know quite a bit about Visual, check it out.
One of my favourite articles is where he details how to easily decode the BINARY fields in Visual tables. He uses views for his solution so it requires you to write a query that joins the view to the table.
I extended his solution somewhat, though mine is not for the faint of heart. There is a great feature of SQL server called User Defined Fields (UDFs). To use UDFs to get decoded Binary fields in Visual without having to join to another table follow these steps:
1) Open the Visual database and then open the table you want to ‘fix’ in design mode. (that’s right, we’re changing the design of the Visual tables themselves. I told you this wasn’t for the faint of heart.)
2) Add a new column to the END of the list. I called mine ‘BITS_PLAIN’
3) Don’t give it a datatype, instead (using Microsoft SQL Server Management Studio) expand the ‘Computed Column Specification’ in the bottom and enter the following formula:
This will convert the first 5000 bits to 5000 chars for reading. If you are running on a newer version of SQL server you should be able to use this:
Which won’t truncate the data arbitrarily.
4) Save your changes.
The new SQL won’t slow down queries that don’t include that column in the select statement, but it’s great for standardizing where to get the binary data in your applications. Unfortunately this doesn’t solve the issue of writing the data back to the database as you can’t write to the UDF. More on that in a future article.