Hey everyone! Last SQL Something post for the year 2013! :-)
Pretty happy with how the year went blog-wise (not as many posts per month as I'd hoped but still I managed to stick with it).
Anyhoo, enough about that and on to the post! Today we are looking at how to view the last few queries that ran on your databases.
"Musings from a DBA in training." - Geon Bell
Tuesday, 31 December 2013
Monday, 16 December 2013
Mini Post: SQL Server Configuration Manager "The Request Failed..." Error
Happy Holidays! And here is a Mini Post for you!
It's a simple error to run into. You open SQL Server Configuration Manager to start a service and this pops up: The request failed or the service did not respond in a timely fashion.
Hmmm.
It's a simple error to run into. You open SQL Server Configuration Manager to start a service and this pops up: The request failed or the service did not respond in a timely fashion.
 |
| Fig. 1: Failure... |
Hmmm.
Monday, 18 November 2013
Getting the Time Difference Between Inserted Rows (Given a DateTime Column) - SQL Server 2008
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.
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.
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. :-)
Subscribe to:
Posts (Atom)