Category: SQL Server

SQL Server

Never use SELECT * in SQL Server Views

Posted on by rivdiv

So, as I discovered, SQL Server kind of “caches” View columns, which can be a problem if you use SELECT *.

Let’s demonstrate:

Create the tables and the view:

CREATE TABLE Categories(CategoryID INT, CategoryName VARCHAR(50))
INSERT INTO Categories
SELECT 1, 'Category1'
UNION SELECT 2, 'Category2'

CREATE TABLE Products (ProductID INT, Description VARCHAR(50), CategoryID INT)
INSERT INTO Products
SELECT 1, 'Product1', 1
UNION SELECT 2, 'Product2', 1
UNION SELECT 3, 'Product3', 2

GO
CREATE VIEW TestView
AS
SELECT c.*, p.Description FROM Categories c JOIN Products p ON c.CategoryID = p.CategoryID
GO

Expected output of SELECT * FROM TestView:

 

 

Now let’s add a column to the Categories table:

ALTER TABLE Categories ADD NewColumn VARCHAR(50) NULL

Output of SELECT * FROM TestView:
Output2

 

 

And… our Description column is showing the data of our NewColumn! As you see, things can get real messy. The best thing would be not to use SELECT * at all, but if it’s too late and you’re seeing the issue described, you can use 2 options to refresh the View metadata and fix the problem:

  1. Use sp_refreshview
  2. ALTER or DROP and RECREATE the View

 

As a side point, it seems SQL Server Management Studio automatically converts SELECT * to individual column names when executing or saving the View in the designer / UI – probably for this reason.

0
SQL Server, Windows Azure

Connecting to External Sql Server from Windows Azure Website

Posted on by rivdiv

After publishing an application from Visual Studio to a Windows Azure website (which, for the record, was incredibly easy) my website loaded fine, but after trying to login using an external SQL Server it threw me an “Access denied.” error. I resolved that by adding the IP address of the website to the external SQL Server.

The error that came up next was strange. It basically told me that my Stored Procedure which exists does not exist. My connection strings were fine in my application in Visual Studio so I couldn’t imagine what was going on. At last, I decided to check the actual web.config file which was uploaded.

Now, I had 1 Entity Framework connection string in there, with 2 standard SQL Server connection strings.

The EF one looked fine, but the standard ones had the following appended to it:

MultipleActiveResultSets=True;Application Name=EntityFramework

I’m not sure where that came from – I’m assuming it happened in the process of being published – but replacing the altered connection strings with the original, correct ones did the job. I uploaded the newly changed web.config file manually via FTP to ensure it won’t get altered again behind my back.

Hope this helps someone else.

0