Archive for category SQL
I had an Oracle query that was returning the error “ORA-00918: column ambiguously defined” when I tried to run it:
SELECT * FROM ( SELECT ID, CASE WHEN something THEN something else ELSE yet more END CASE FROM ScottsTable UNION ALL SELECT ID, CASE WHEN something THEN something else ELSE yet more END CASE FROM ScottsTableHistory ) ORDER BY 1 ASC, 2;
The problem was originating in the first query’s CASE statement. I needed to put an alias on the first CASE statement to avoid the naming duplication that was causing Oracle to hate me. Here is the final, modified query:
SELECT * FROM ( SELECT ID, CASE WHEN something THEN something else ELSE yet more END "MY COLUMN" FROM ScottsTable UNION ALL SELECT ID, CASE WHEN something THEN something else ELSE yet more END CASE FROM ScottsTableHistory ) ORDER BY 1 ASC, 2;
This seemed like a straight-forward issue, and it was, after I straightened out the curves. I had a simple logging mechanism, a SQL stored procedure something like this:
CREATE PROCEDURE [dbo].[LogError] @Message VARCHAR(500) , @Id UNIQUEIDENTIFIER AS INSERT INTO ErrorLog (Message, Id) VALUES (@Message, @Id)
And in a calling stored procedure:
EXEC LogError 'An invalid response was returned.', NEWID
Both stored procedures compiled just fine. However, when I attempted to log the message, I got the error
Error converting data type varchar to uniqueidentifier.
It turns out that just because my SP compiled fine didn’t mean it was actually going to work. Just using “NEWID” wasn’t enough. Instead, I had to modify my logging function to use a variable of type UNIQUEIDENTIFIER, like so:
DECLARE @NewId UNIQUEIDENTIFIER SET @NewId = NEWID() EXEC LogError 'An invalid response was returned.', @NewId
Reading over some MVC documentation at https://msdn.microsoft.com/en-us/library/ff398049(v=vs.100).aspx, I noticed several people complaining that they couldn’t see the database after building the application. I went to my App_Data folder and I saw the same behavior – no database. Even restarting Visual Studio didn’t reveal the database.
However, you should be able to view the database by right clicking on the App_Data folder and selecting “Open Folder in File Explorer”.
You can also click on the “Show All Files” icon, and should reveal it as well:
Do you need to pull the latitude and longitude values in format you know and love into a VARCHAR, like “41.848, -82.690”? No problem:
SELECT CONVERT(VARCHAR(20),[Geometry].STY) + ‘,’ + CONVERT(VARCHAR(20),[Geometry].STX) FROM MyPlaces
Selecting empty geometries is easy. All you need is SQL Server, some data with a column of the Geometry data type, and this query:
SELECT * FROM ScottsFavoriteBars WHERE [Geometry].STIsEmpty() > 0
I was getting this error in my SQL statement, which was dynamically created in some C# code:
Invalid column name “385f35a5-697a-4238-b502-c5a72199d9c2”
An example of the statement:
INSERT INTO Test (ID) VALUES ("385f35a5-697a-4238-b502-c5a72199d9c2")
This error was because my SQL INSERT statement had used double quotes around the text to be inserted instead of single quotes. Modifying the statement fixed it:
INSERT INTO Test (ID) VALUES ('385f35a5-697a-4238-b502-c5a72199d9c2')
While creating a new .NET application, I tried to load the login page and saw this error waiting for me in the Event Viewer:
Login failed for user ‘scott’. Reason: Failed to open the explicitly specified database ‘Test1’
This error had a code of 18456, if you are keeping score at home.
Short story even shorter, I had the wrong name in the web.config connection string that pointed to the database. As you can see in the error, the database named was “Test1”. In the connection string, I had it named “Testv1”.