Hey everyone, Geon here again with another SQL Something post!
This time we are going to look really quickly at modifying a connection string to have a failover partner. What this means is if you have a database setup that includes Mirroring (circa SQL Server 2008), then you should make sure your applications take advantage of it. Your applications (when configured correctly) can automatically failover to the secondary DB when the current DB becomes unavailable.
Which is pretty cool by itself, but also very useful as it minimizes downtime and any manual intervention needed to point the apps to the failover DB.
And it is really simple to do.
"Musings from a DBA in training." - Geon Bell
Wednesday, 8 April 2015
Friday, 20 March 2015
Mini Post: Saving changes is not permitted. The changes you have made require...
Hey Everyone! (Capital 'E' since you are all that important!)
Welcome to or welcome back to SQL Something!
A real quick one today to maintain my quota: Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.
You may encounter this error when you try to make adjustments to columns of a table that has data in it. Examples are dropping/adding a column(s).
Simple enough to 'fix'.
Welcome to or welcome back to SQL Something!
A real quick one today to maintain my quota: Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.
 |
| Fig. 1: Oh Noes! |
You may encounter this error when you try to make adjustments to columns of a table that has data in it. Examples are dropping/adding a column(s).
Simple enough to 'fix'.
Monday, 16 February 2015
Mini Post: List All Stored Procedures/Functions In A Database (Information_Schema.Routines)
Hey all! Welcome to, or welcome back, to SQL Something!
Today we're taking a quick look at how to list all Stored Procedures (and Functions) in a database. As per usual, I needed to do this at some point for my job and so I'm sharing what I found. :-)
In order to get the information we need we will be querying information_schema.routines as follows:
SELECT ROUTINE_NAME as 'Stored Procedure',
ROUTINE_CATALOG as 'Database Name',
ROUTINE_SCHEMA as 'Database Schema',
ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
ROUTINE_DEFINITION as 'Stored Procedure Definition',
CREATED as 'Date Created',
LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME
The above will list all Store Procedures for the database that you run the query on. Very helpful! :-)
Now you'll notice the WHERE clause specifies the PROCEDURE type only. If we wanted info on Functions as well as Stored Procedures, we can remove this clause.
If we wanted info on Functions only and wanted some additional info like what value the Function returns, we tweak the query as follows:
SELECT ROUTINE_NAME as 'Function Name',
ROUTINE_CATALOG as 'Database Name',
ROUTINE_SCHEMA as 'Database Schema',
ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
ROUTINE_DEFINITION as 'Function Definition',
DATA_TYPE as 'Return Value Type',
CREATED as 'Date Created',
LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME
DATA_TYPE returns the data type of the Function's return value or it returns TABLE if it's a table valued function.
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're taking a quick look at how to list all Stored Procedures (and Functions) in a database. As per usual, I needed to do this at some point for my job and so I'm sharing what I found. :-)
In order to get the information we need we will be querying information_schema.routines as follows:
SELECT ROUTINE_NAME as 'Stored Procedure',
ROUTINE_CATALOG as 'Database Name',
ROUTINE_SCHEMA as 'Database Schema',
ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
ROUTINE_DEFINITION as 'Stored Procedure Definition',
CREATED as 'Date Created',
LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME
The above will list all Store Procedures for the database that you run the query on. Very helpful! :-)
Now you'll notice the WHERE clause specifies the PROCEDURE type only. If we wanted info on Functions as well as Stored Procedures, we can remove this clause.
If we wanted info on Functions only and wanted some additional info like what value the Function returns, we tweak the query as follows:
SELECT ROUTINE_NAME as 'Function Name',
ROUTINE_CATALOG as 'Database Name',
ROUTINE_SCHEMA as 'Database Schema',
ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
ROUTINE_DEFINITION as 'Function Definition',
DATA_TYPE as 'Return Value Type',
CREATED as 'Date Created',
LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME
DATA_TYPE returns the data type of the Function's return value or it returns TABLE if it's a table valued function.
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. :-)
Sunday, 18 January 2015
Mini Post: Each GROUP BY expression must contain at least one column that is not an outer reference
Good day and welcome back to SQL Something!
I'm ashamed at how long I've been away and there is really no excuse. It is, however, a new year (2015 people!), so I'm going to try to be much better. Here's hoping!
We'll start this year with a very simple error that you may see when running a query:
"Each GROUP BY expression must contain at least one column that is not an outer reference"
The error itself is a bit vague, but refers to something quite simple. As the above error indicates, a "Group By" is involved. Maybe your query involves a variable like so:
SELECT COUNT([Column1]) AS TotalCount, [Column2], @Variable as VariableName
FROM [Schema1].[Table1]
WHERE [Column2] = @Variable
GROUP BY [Column2], @Variable;
Do you know what's wrong? It's the variable in the GROUP BY clause.
Normally, when using GROUP BY, you are required to put all the columns in the SELECT clause that are not part of a aggregate function into the GROUP BY clause. Following that logic you should also put any local variables that you are using in the SELECT clause. This however is not so.
Removing the variable like so fixes the query:
SELECT COUNT([Column1]) AS TotalCount, [Column2], @Variable as VariableName
FROM [Schema1].[Table1]
WHERE [Column2] = @Variable
GROUP BY [Column2];
Thank you, as usual, for your time. :-)
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. :-)
I'm ashamed at how long I've been away and there is really no excuse. It is, however, a new year (2015 people!), so I'm going to try to be much better. Here's hoping!
We'll start this year with a very simple error that you may see when running a query:
"Each GROUP BY expression must contain at least one column that is not an outer reference"
The error itself is a bit vague, but refers to something quite simple. As the above error indicates, a "Group By" is involved. Maybe your query involves a variable like so:
SELECT COUNT([Column1]) AS TotalCount, [Column2], @Variable as VariableName
FROM [Schema1].[Table1]
WHERE [Column2] = @Variable
GROUP BY [Column2], @Variable;
Do you know what's wrong? It's the variable in the GROUP BY clause.
Normally, when using GROUP BY, you are required to put all the columns in the SELECT clause that are not part of a aggregate function into the GROUP BY clause. Following that logic you should also put any local variables that you are using in the SELECT clause. This however is not so.
Removing the variable like so fixes the query:
SELECT COUNT([Column1]) AS TotalCount, [Column2], @Variable as VariableName
FROM [Schema1].[Table1]
WHERE [Column2] = @Variable
GROUP BY [Column2];
Thank you, as usual, for your time. :-)
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. :-)
Friday, 18 July 2014
Mini Post: Error 0xc02020a1: Data conversion failed. The data conversion for column returned status value 4
Helloooo! And welcome (or welcome back) to SQL Something!
Today's mini-post is about an error you can run into when attempting to use the Import/Export Wizard to import some data into a DB of your choice:
Today's mini-post is about an error you can run into when attempting to use the Import/Export Wizard to import some data into a DB of your choice:
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "COLUMN NAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
 |
| Fig. 1: Annoying Error. |
Thursday, 17 July 2014
Error: The Server Instance Witness Rejected Configure Request
Hey everyone out there! Welcome back to SQL Something!
Today we look at an interesting problem. I'm not sure as to the root cause of it, but we will look at how to solve it.
(P.S. If anyone out there knows why this occurred, please feel free to contribute in the comments below)
The error in question is:
I discovered this error repeating continuously in the Application Log in the Windows Event Viewer.
Naturally I checked all the databases currently being mirrored, however all seemed to be in order (both the Principals as well as the Mirrors). So how can we find out more about the issue and how can we fix it?
Today we look at an interesting problem. I'm not sure as to the root cause of it, but we will look at how to solve it.
(P.S. If anyone out there knows why this occurred, please feel free to contribute in the comments below)
The error in question is:
The server instance Witness rejected configure request; read its error log file for more information. The reason 1427, and state 31, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
 |
| Fig. 1: So it begins. |
I discovered this error repeating continuously in the Application Log in the Windows Event Viewer.
Naturally I checked all the databases currently being mirrored, however all seemed to be in order (both the Principals as well as the Mirrors). So how can we find out more about the issue and how can we fix it?
Monday, 30 June 2014
Mini Post: Creating and Configuring a UDL file
Good day everyone! And welcome back to SQL Something!
Here I am at the end of the month again, trying to fit in a couple last minute posts. Said I wouldn't do this again. Hopefully I spread them out a bit better next month.
Anyway, without any more delay let's look at creating a UDL file.
Firstly a UDL (Universal Data Link) file allows persons to test connectivity to OLE DB Providers. The OLE DB Provider we are interested in, of course, is the one for SQL Server. This is very useful to quickly test credentials on a machine that does not have SQL Server Management Studio installed, but needs access to a SQL Server instance (example testing SQL Server access from an application server to a SQL Server).
Here I am at the end of the month again, trying to fit in a couple last minute posts. Said I wouldn't do this again. Hopefully I spread them out a bit better next month.
Anyway, without any more delay let's look at creating a UDL file.
Firstly a UDL (Universal Data Link) file allows persons to test connectivity to OLE DB Providers. The OLE DB Provider we are interested in, of course, is the one for SQL Server. This is very useful to quickly test credentials on a machine that does not have SQL Server Management Studio installed, but needs access to a SQL Server instance (example testing SQL Server access from an application server to a SQL Server).
Subscribe to:
Posts (Atom)