Good day and welcome to SQL Something!
I'm particularly pleased to write this one as it's the first non-MiniPost I've written in a while. Huzzah!
Today we are going to look at how to get the time difference between the insertion of two rows, given a datetime column.
"Musings from a DBA in training." - Geon Bell
Monday, 18 November 2013
Friday, 15 November 2013
Mini Post: Removing all Whitespace/NewLine Characters From a String
Hey and hello! Welcome back or welcome new!
Today really quickly we'll look at something that is very helpful in my day-to-day stuff, that is, stripping all spaces and any new line characters from a string.
You can use the below:
REPLACE(REPLACE(REPLACE(REPLACE(YourString, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''), ' ', '');
This strips Tab spaces ( represented by CHAR(9) ), Line Feed spaces ( represented by CHAR(10) ), Carriage Returns ( represented by CHAR(13) ) and plain old spaces (represented by ' ').
Please see here for CHAR types.
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Today really quickly we'll look at something that is very helpful in my day-to-day stuff, that is, stripping all spaces and any new line characters from a string.
You can use the below:
REPLACE(REPLACE(REPLACE(REPLACE(YourString, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''), ' ', '');
This strips Tab spaces ( represented by CHAR(9) ), Line Feed spaces ( represented by CHAR(10) ), Carriage Returns ( represented by CHAR(13) ) and plain old spaces (represented by ' ').
Please see here for CHAR types.
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Monday, 4 November 2013
Mini Post: Getting distinct random values using 'TOP' and 'NEWID()'
Heya everyone out there, Mini Post time!
...Too many of these don't you think? I really have to buckle down and write a longer post sometime.
Anyway let's focus on something I had to use 5 minutes ago (I have used it before, but 5 minutes ago was the most recent so I figured I'd jot this down quickly while its in my head).
Sometimes we wish to get back random rows from our table. For this we can use 'NEWID()' as follows:
SELECT top 10 Col1
FROM Table1
-- WHERE SomePredicate
ORDER BY NEWID();
The above will pull 10 random Col1 values, regardless if the same value can appear multiple times in Col1.
Now sometimes we wish to pull not only random values, but random distinct values.
It'd be nice if we could do something like:
--Incorrect
SELECT top DISTINCT 10 Col1
FROM Table1
-- WHERE SomePredicate
ORDER BY NEWID();
But alas, we'll get an error. So instead please use (the key point is the 'GROUP BY'):
--Correct
SELECT top 10 Col1
FROM Table1
-- WHERE SomePredicate
GROUP BY Col1
ORDER BY NEWID();
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
...Too many of these don't you think? I really have to buckle down and write a longer post sometime.
Anyway let's focus on something I had to use 5 minutes ago (I have used it before, but 5 minutes ago was the most recent so I figured I'd jot this down quickly while its in my head).
Sometimes we wish to get back random rows from our table. For this we can use 'NEWID()' as follows:
SELECT top 10 Col1
FROM Table1
-- WHERE SomePredicate
ORDER BY NEWID();
The above will pull 10 random Col1 values, regardless if the same value can appear multiple times in Col1.
Now sometimes we wish to pull not only random values, but random distinct values.
It'd be nice if we could do something like:
--Incorrect
SELECT top DISTINCT 10 Col1
FROM Table1
-- WHERE SomePredicate
ORDER BY NEWID();
But alas, we'll get an error. So instead please use (the key point is the 'GROUP BY'):
--Correct
SELECT top 10 Col1
FROM Table1
-- WHERE SomePredicate
GROUP BY Col1
ORDER BY NEWID();
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Tuesday, 29 October 2013
Mini Post: Adding a Column to an Existing Table With a Default Value
Hey again everyone and welcome to SQL Something.
Just thought I'd throw this in as well because I needed to do this earlier this week: Adding a column with a default value.
Initial query was found here (click this, it's a very cool blog).
See my slight variant below and explaination:
ALTER TABLE YourTable
ADD YourNewColumn INT NOT NULL DEFAULT(42)
GO
So as far as an explanation goes, what the above does is alter your existing table (named 'YourTable') by adding a new column (named 'YourNewColumn') of type INT with a default value of 42. Each row in the table will then have a new column filled with a value of 42.
You can change the column type and the default value in the query to suit your needs.
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Just thought I'd throw this in as well because I needed to do this earlier this week: Adding a column with a default value.
Initial query was found here (click this, it's a very cool blog).
See my slight variant below and explaination:
ALTER TABLE YourTable
ADD YourNewColumn INT NOT NULL DEFAULT(42)
GO
So as far as an explanation goes, what the above does is alter your existing table (named 'YourTable') by adding a new column (named 'YourNewColumn') of type INT with a default value of 42. Each row in the table will then have a new column filled with a value of 42.
You can change the column type and the default value in the query to suit your needs.
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Mini Post: Error Specified Cast is Not Valid
Hey everybody! Me again with another Mini Post.
Today we are going to look at a SQL restore error: Specified Cast is Not Valid.
You may see this error when you are attempting to restore a SQL Server Backup via the SQL Server Management Studio. This error could be the result of a couple of different things:
Solutions (in order of the problems above):
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Today we are going to look at a SQL restore error: Specified Cast is Not Valid.
You may see this error when you are attempting to restore a SQL Server Backup via the SQL Server Management Studio. This error could be the result of a couple of different things:
- Corrupt backup
- Attempting to restore a backup of a newer version of SQL Server on a older version of SQL Server
- SQL Server Management Studio GUI bug
Solutions (in order of the problems above):
- Redo the backup and then try to restore it (if this still doesn't work it may indicate a corrupt DB; check the DB with DBCC CHECKDB)
- Verify that you are not attempting to restore a backup on a older version of SQL Server (example don't try to restore a 2008 DB backup on a 2005 instance)
- Try restoring from the command line instead of the GUI
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Mini Post: The Operating System Returned Error 21
Hey everybody and welcome back to SQL Something!
Today we're gonna take a quick look at a very generic error: Error 21.
You will usually get a message along the lines of "The operating system returned error 21" along with something like "The device is not ready". The second part of the error is the important bit as it specifies that, for whatever reason, the 'device' (usually your drive or the actual files on it) is not ready.
This could be due to a number of reasons (hence the generic nature of the error):
I would say after you check out what might be causing the issue as well as rectify it, you run a DBCC CHECKDB to ensure that you do not have any consistency issues with you database(s).
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Today we're gonna take a quick look at a very generic error: Error 21.
You will usually get a message along the lines of "The operating system returned error 21" along with something like "The device is not ready". The second part of the error is the important bit as it specifies that, for whatever reason, the 'device' (usually your drive or the actual files on it) is not ready.
This could be due to a number of reasons (hence the generic nature of the error):
- Drive disk space issue
- Hard drive failure
- SAN failure
- Corrupt database files (MDF/LDF etc)
I would say after you check out what might be causing the issue as well as rectify it, you run a DBCC CHECKDB to ensure that you do not have any consistency issues with you database(s).
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Monday, 30 September 2013
Mini Post: Remote Procedure Call Failed. [0x800706be] (SQL Server 2008 / 2008 R2)
After you have installed SQL Server 2008 or 2008 R2 (on possibly a Windows 7 or 8 machine) and you attempt to go into the Configuration Manager you may see the following:
 |
| Fig. 1: The error in question. |
This seems to be a bug that a few people run into (including myself recently). The solution(s) are as follows:
SQL Server 2008
- Upgrade to Service Pack 3. Found here (worked for me!).
- If the above does not work, use the steps to recreate the WMI Repository found here.
SQL Server 2008 R2
- Upgrade to Service Pack 1 or 2. Found here and here respectively.
- If the above does not work, use the steps to recreate the WMI Repository found here.
If anyone knows the exact cause of this bug, please feel free to share in the comments below. It will benefit everyone. :-)
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Subscribe to:
Posts (Atom)