Archive for category SQL

Error converting data type varchar to uniqueidentifier

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

Leave a comment

ASP.NET membership database not appearing in the App_Data folder

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”.

ASP.NET membership database

ASP.NET membership database

You can also click on the “Show All Files” icon, and should reveal it as well:

Show All Files

Show All Files

Leave a comment

Getting Latitude / Longitude from a SQL Server Geometry

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

Leave a comment

Selecting empty SQL geometry values

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

Leave a comment

‘Invalid column name’ error in SQL update statement

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')

Leave a comment

SQL Server error: Login failed for user ‘user’

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”.

 

Leave a comment

Using a VARCHAR(MAX) parameter in .NET

So you have a stored procedure with a parameter as a VARCHAR(MAX):

CREATE PROCEDURE [dbo].[GetAwesomeDude]
@CustomerName VARCHAR(MAX)
AS

SELECT * FROM CoolAwesomeTable
WHERE ID = @CustomerName

How do you designate this in C#? You use “-1” as the parameter size:

SqlParameter param = new SqlParameter(“@CustomerName”, SqlDbType.VarChar, -1);
param.Value = poly.Points;
comm.Parameters.Add(param);

Not too difficult, but not too intuitive either.

Leave a comment