SQL table cell value comes back as a square

Question

I successful cloned my SQL 2008 database to Azure yesterday. I've been testing today.

Everything came across without any trouble, I can read and write nearly everything no problem.  However, for some reason I have 2 cols, both nvarchar(max), that come back with a blank value (IE 8 shows a square), and not the value in the column.  I'm using ASP Classic for my site, and the majority of the data points come across aside from this one and possibly another.

The asp is connecting via secure connection, using adodb.  The data is requested and stored in a variable using adodb.recordset, and then the value is written to the html side using <%= %> tags. I've tested <% response.write %> as well with the same result.

Answer 1

It is a bit improper to say that a SQL table cell value comes back as a square. You are talking from the perspective on what is being rendered in a certain browser by your application, but you will have to consider that there is a very long way in your execution stack between the database layer and a "square" on the screen.

Reading your question, I am assuming that your application previously used a SQL Server 2008 database and worked correctly and now it switched to a SQL Azure database and this raised the problem that you mention. If that is the case, then the problem would indeed be database related.

The first thing I would recommend is checking your application error logs. By the way, if I correctly understand, except the column values that are displayed as "squares", all the other sections / pages in your application are properly displaying database data. If that is not the case, then you most probably have a connection issue.

The next thing to do is to manually check if the two database columns that you mention are properly populated. Use the SQL Server Management Studio or the Windows Azure Management Portal to connect to the SQL Azure database used by your application. Manually execute the query that your application is issuing when rendering your mentioned squares. If the returned cells are empty, check if there wasn't any problem porting their values when you made the clone from the SQL Server 2008 db.

---

Read my technical blog: ducons.com/blog | Connect with me on twitter: @fdumitrescu

Answer 2

FlorinDumitrescu,

Thanks for the response. You are correct, in that the data originates on a SQL 2008 Express server. I had run the sql query before I posted, the columns are populated with the correct data. I was able to do more troubleshooting after hours on the system and found that the data came across correctly without with any trouble when I dropped the secure connection from connection string.  This isn't optimal, but it is still better than what I had running earlier. before Azure.  I know the square is rendering, what I was getting at was that some kind of data was coming across, not just an empty value, which would have caused my if statement to skip out, rather, there is some "data" that causes my if statement to think there is something.  This is all an ASP application, so there are no error logs for me to check, I can simply plug and trouble shoot the causes. 

I can confirm that the data points that are not nvarchar(max) do come through, in any configuration, correctly.

Answer 3

Hi Hatrickwah,

It sounds more like an application level problem (in the classic ASP code) rather than a database problem. If you deploy the application on premises and use a SQL Server 2008 as the database, are the nvarchar(max) columns properly displayed?

---

Read my technical blog: ducons.com/blog | Connect with me on twitter: @fdumitrescu

Answer 4

Florin..

Yes, when I was pulling from the Server 2008 database, the nvarchar(max) was working fine. Since I switched to Azure it does not.  Or more, it did not when I have the secure connection set true. At the moment it is false, and it was working up until about an hour ago. Now I'm back tracing my steps today to determine what has changed.

Answer 5

Hi Hatrickwah,

I find it a bit strange that a setting on a database connection string will influence how data is being read from a column in a database table, but not influence anything else. As far as I know connection strings will have a consistent effect on any operation you are performing though them to the database.

If you make your application work again, could you please post the connection string that works and the one that doesn't? Before posting, please double check that the ONLY thing that you are updating between the working and not working scenarios is just the connection string.

---

Read my technical blog: ducons.com/blog | Connect with me on twitter: @fdumitrescu