June 2011 - Posts

VB.NET IF() Coalesce and “Expression Expected” Error

I am trying to use the equivalent of the C# “??” operator in some VB.NET code that I am working in.

This StackOverflow article for “Is there a VB.NET equivalent for C#'s ?? operator?” explains the VB.NET IF() statement syntax which is exactly what I am looking for... and I thought I was going to be done pretty quickly and could move on.

But after implementing the IF() statement in my code I started to receive this error:

Compiler Error Message: BC30201: Expression expected.

image

And no matter how I tried using the “IF()” statement, whenever I tried to visit the aspx page that I was working on I received the same error.

This other StackOverflow article Using VB.NET If vs. IIf in binding/rendering expression indicated that the VB.NET IF() operator was not available until VS2008 or .NET Framework 3.5.  So I checked the Web Application project properties but it was targeting the .NET Framework 3.5:

image

So I was still not understanding what was going on, but then I noticed the version information in the detailed compiler output of the error page:

image

This happened to be a C# project, but with an ASPX page with inline VB.NET code (yes, it is strange to have that but that is the project I am working on).  So even though the project file was targeting the .NET Framework 3.5, the ASPX page was being compiled using the .NET Framework 2.0.  But why?  Where does this get set?  How does ASP.NET know which version of the compiler to use for the inline code?

For this I turned to the web.config.  Here is the system.codedom/compilers section that was in the web.config for this project:

<system.codedom>
    <compilers>
        <compiler language="c#;cs;csharp" extension=".cs" warningLevel="4" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
            <providerOption name="CompilerVersion" value="v3.5" />
            <providerOption name="WarnAsError" value="false" />
        </compiler>
    </compilers>
</system.codedom>

Keep in mind that this is a C# web application project file but my aspx file has inline VB.NET code.  The web.config does not have any information for how to compile for VB.NET so it defaults to .NET 2.0 (instead of 3.5 which is what I need).

So the web.config needed to include the VB.NET compiler option.  Here it is with both the C# and VB.NET options (I copied the VB.NET config from a new VB.NET Web Application project file).

    <system.codedom>
        <compilers>
            <compiler language="c#;cs;csharp" extension=".cs" warningLevel="4" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                <providerOption name="CompilerVersion" value="v3.5" />
                <providerOption name="WarnAsError" value="false" />
            </compiler>
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" warningLevel="4" type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
        <providerOption name="CompilerVersion" value="v3.5"/>
        <providerOption name="OptionInfer" value="true"/>
        <providerOption name="WarnAsError" value="false"/>
      </compiler>
    </compilers>
    </system.codedom>

 

So the inline VB.NET code on my aspx page was being compiled using the .NET Framework 2.0 when it really needed to be compiled with the .NET Framework 3.5 compiler in order to take advantage of the VB.NET IF() coalesce statement.  Without the VB.NET web.config compiler option, the default is to compile using the .NET Framework 2.0 and the VB.NET IF() coalesce statement does not exist (at least in the form that I want it in).  FYI, there is an older IF statement in VB.NET 2.0 compiler which is why it is giving me the unusual “Expression Expected” error message – see this article for when VB.NET got the new updated version.

EDIT (2011-06-20): I had made a wrong assumption in the first version of this blog post.  After a little more research and investigation I was able to figure out that the issue was in the web.config and not with the IIS App Pool.  Thanks to the comment from James which forced me to look into this again.

Posted 18 June 2011 02:58 PM by Jeff Widmer | 2 comment(s)
Filed under: , , , , , ,
Find stored procedures that reference a table column

We recently moved away from SQL Server replication and the database still has all of the rowguid columns and their associated indexes and constraints in it. We wanted to gain all of that disk space back so we went ahead with scripting out the delete of the indexes, constraints, and columns.

One thing though is that there are many stored procedures in use and we needed to make sure none of these referenced the rowguid column.  The stored procedures really should not have used the rowguid column at all since it is solely created and used for SQL Replication, but you never know so we needed to confirm.

The below script is what I created to search all of the stored procedures in the database for the rowguid column.  It could easily be modified to find any information within the stored procedures of a database.  (Note: One thing to watch out for is encrypted stored procedures.  This wouldn’t find anything in those so you would need to handle it through another method.)

SELECT p.name, c.text FROM syscomments c
JOIN sys.procedures p ON p.object_id=c.id
WHERE c.text LIKE '%rowguid%'
ORDER BY p.name

Posted 16 June 2011 10:22 AM by Jeff Widmer | 1 comment(s)
Filed under: , ,
Lock request time out period exceeded

I was trying to drop a foreign key for a table I was working on and I ran into a time out exception from SQL Server Management Studio:

image

TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for ForeignKey 'fk_MyForeignKey'.  (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
------------------------------
BUTTONS: OK
------------------------------

I also tried dropping the foreign key manually using:

ALTER TABLE MyTable DROP CONSTRAINT fk_MyForeignKey

But this time the query was just sitting there running and running.  I let it run for some time and then when I checked the currently executing requests I found it was sitting in a suspended state.  What was interesting about the request was that the wait_time equaled the total_elapsed_time, so it was just waiting there for something else before proceeding.

This is the sql query I used to see the currently executing requests (one of which was mine):

SELECT r.session_id, r.status, r.start_time, r.command, s.text,
r.wait_time, r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level
,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

And this in the particular row in question that made me realize my query was waiting on something else:

image

Now I needed to find out what is blocking my Alter Table command from running.  For that I used a query I found on this blog post Error 1222 Lock Request Time Out Period Exceeded When Set up Replication by Andrew Chen:

select distinct object_name(a.rsc_objid), a.req_spid, b.loginame
from master.dbo.syslockinfo a (nolock) join
master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
where object_name(a.rsc_objid) is not null

I found that another SPID from SQL Server Management Studio was holding onto the table I was trying to alter.  Using sp_who2 with the SPID showed me the owner and where it was coming from, and also that it had been holding onto the table for 2 hours... and guess what!?!  It was me!  Smile

I had been looking at the execution plan and client statistics of a query that I was performance tuning and that SQL Server Management window had a hold of the table I was trying to Alter.  As soon as I closed that window (and canceled that transaction) then I could drop the foreign key without a problem.

Hopefully this will help someone else in the future!

Posted 08 June 2011 01:44 PM by Jeff Widmer | 4 comment(s)
Filed under: , ,
More Posts

Search

Go

This Blog

News

Recommended Blogs

Syndication