So just to get this one out of the way (was bugging me that I didn't write it as yet), here's how to change the collation of a database. (See bottom of post for links to other related collation posts)
Use only one of the following solutions: EITHER Management Studio OR TSQL.
"Musings from a DBA in training." - Geon Bell
Sunday, 21 July 2013
Friday, 19 July 2013
SQL Server 2008: Change Instance Collation Via Rebuilding The System Databases
Hey everyone, welcome back to the first proper length SQL Something in a while.
In an earlier post I outlined how to get queries to ignore collation. In a future mini post I will show how to change a database's collation.
Today's focus is the instance's collation.
Changing the collation of a SQL Instance is not something to be taken lightly (which is why it's good practtice to choose the correct collation the first time you set up the instance). In order to change it you have (as far as I know) two options:
So without further ado, let's look at the second option.
In an earlier post I outlined how to get queries to ignore collation. In a future mini post I will show how to change a database's collation.
Today's focus is the instance's collation.
Changing the collation of a SQL Instance is not something to be taken lightly (which is why it's good practtice to choose the correct collation the first time you set up the instance). In order to change it you have (as far as I know) two options:
- Reinstall the instance.
- Rebuild The System Databases.
So without further ado, let's look at the second option.
Tuesday, 16 July 2013
Mini Post: Get Sizes Of Tables In A Database
Hey all, here's something I've been meaning to put up for a while.
Its a very common question that pops up from time to time so I thought I'd share the answer in one more place on the internet.
Well two answers actually: The Report Solution and The Query Solution.
Its a very common question that pops up from time to time so I thought I'd share the answer in one more place on the internet.
Well two answers actually: The Report Solution and The Query Solution.
Sunday, 14 July 2013
Mini Post: View Queries Currently Running On Your Instance
Hey all! Below is a quick and easy way you can see what queries are currently running on your instance.
SELECT QueryText.TEXT AS Query,
DB_Name(Requests.database_id) AS DBName,
Requests.session_id AS Session_ID,
Requests.status AS Status,
Requests.command AS Command_Type,
Requests.cpu_time AS CPU_Time,
Requests.total_elapsed_time/1000 AS Time_In_Seconds,
Requests.total_elapsed_time AS Time_In_MilliSeconds
FROM sys.dm_exec_requests Requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS QueryText
The query makes use of the sys.dm_exec_requests and sys.dm_exec_sql_text Dynamic Management Objects (DMOs). Check the links for additional columns that may be useful to you.
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
SELECT QueryText.TEXT AS Query,
DB_Name(Requests.database_id) AS DBName,
Requests.session_id AS Session_ID,
Requests.status AS Status,
Requests.command AS Command_Type,
Requests.cpu_time AS CPU_Time,
Requests.total_elapsed_time/1000 AS Time_In_Seconds,
Requests.total_elapsed_time AS Time_In_MilliSeconds
FROM sys.dm_exec_requests Requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS QueryText
The query makes use of the sys.dm_exec_requests and sys.dm_exec_sql_text Dynamic Management Objects (DMOs). Check the links for additional columns that may be useful to you.
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Monday, 1 July 2013
Mini Post: Adjust Queries to Ignore Collation
Heya people, gonna quickly run through how to adjust queries to ignore collation. I will return with another Mini Post to show how to actually change the collation of an instance and a DB.
You may have run into an scenario where your database has a different collation from that of your instance
(for example, the database could have been taken from an instance with a different collation than that of your current instance).
When you attempt to compare two columns, one with collation A and one with collation B, you will get the following error:
"Cannot resolve the collation conflict"
along with the names of collation A and B as well as the comparison operation that was attempted (whether it was '=', BETWEEN', 'IN' etc)
To resolve this, you can set the collation of A (the previously foreign DB) to be the collation of the current instance. In your WHERE clause where you are doing your comparisons place "COLLATE DATABASE_DEFAULT" immediately after the name of the column used for comparison like so:
...WHERE A.Col1 COLLATE DATABASE_DEFAULT in (SELECT B.Col1 FROM B)
No more error. :-)
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
You may have run into an scenario where your database has a different collation from that of your instance
(for example, the database could have been taken from an instance with a different collation than that of your current instance).
When you attempt to compare two columns, one with collation A and one with collation B, you will get the following error:
"Cannot resolve the collation conflict"
along with the names of collation A and B as well as the comparison operation that was attempted (whether it was '=', BETWEEN', 'IN' etc)
To resolve this, you can set the collation of A (the previously foreign DB) to be the collation of the current instance. In your WHERE clause where you are doing your comparisons place "COLLATE DATABASE_DEFAULT" immediately after the name of the column used for comparison like so:
...WHERE A.Col1 COLLATE DATABASE_DEFAULT in (SELECT B.Col1 FROM B)
No more error. :-)
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Monday, 24 June 2013
Recovering A Database With Only The MDF File: These Are Your Options
Hello out there and welcome back to SQL Something!
This is one of a few blog posts I had time to complete and post (busy times). Without further ado, let's get started.
This post was inspired by a recent event where we lost the drive that our log files were on (not gonna go into the gory details about that).
Thankfully, prior to this we knew hardware weirdness was going on with the server so we moved all our production DBs to our secondary server. Whew.
Thus, losing the drive on what was formerly the production server wasn't that bad. And it then gave me some MDF files to attempt to restore at my leisure, just for trying sake.
This is one of a few blog posts I had time to complete and post (busy times). Without further ado, let's get started.
This post was inspired by a recent event where we lost the drive that our log files were on (not gonna go into the gory details about that).
Thankfully, prior to this we knew hardware weirdness was going on with the server so we moved all our production DBs to our secondary server. Whew.
Thus, losing the drive on what was formerly the production server wasn't that bad. And it then gave me some MDF files to attempt to restore at my leisure, just for trying sake.
Tuesday, 4 June 2013
Mini Post: Using sys.dm_exec_connections to Get Info About Current Connections to SQL Server
Hey guys! Another bite sized mini post here!
Today we take a quick look at sys.dm_exec_connections (2012 equivelent here) to get a little info on what/how sessions are connected to our instance.
As stated in the links sys.dm_exec_connections provides server level info on SQL Server connections. Using the below for example:
SELECT top 50 session_id, auth_scheme, connect_time, client_net_address
FROM sys.dm_exec_connections
order by session_id
Would result in the below:
The client_net_address, I find particularly useful as it shows the host address of the client that is connected to instance. Nice. Auth_scheme is also nice as it shows the 'how' of the connection (is it a SQL login etc).
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Today we take a quick look at sys.dm_exec_connections (2012 equivelent here) to get a little info on what/how sessions are connected to our instance.
As stated in the links sys.dm_exec_connections provides server level info on SQL Server connections. Using the below for example:
SELECT top 50 session_id, auth_scheme, connect_time, client_net_address
FROM sys.dm_exec_connections
order by session_id
Would result in the below:
 |
| Fig. 1: Results of querying sys.dm_exec_connections |
The client_net_address, I find particularly useful as it shows the host address of the client that is connected to instance. Nice. Auth_scheme is also nice as it shows the 'how' of the connection (is it a SQL login etc).
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Subscribe to:
Posts (Atom)