SQL Query String Works in SQL Server Management Studio, But Not in VB.net with SQLCommand.ExecuteReader

I have an ASPX page using VB.net code, and it is set up to run a series of SQL queries and populate a SQLDataReader with the results. Each SQL query is pulled from a specific field in a table within the SQL database. This code works perfectly for literally every query I run through it, except one.

The VB code involved is as follows.

Dim SQL_Template_Query As String
Dim cmd3 As SqlCommand = New SqlCommand
Dim r3 As SqlDataReader    

cmd3 = New SqlCommand(SQL_Template_Query)
cmd3.CommandTimeout = 1200 ' Extended to 1200 sec = 20 min
cmd3.CommandType = CommandType.Text
cmd3.Connection = Global_Objects.SQLLocalDB_Connection
Try
    r3 = Nothing
    r3 = cmd3.ExecuteReader() ' THIS LINE IS WHERE THE ERROR OCCURS
Catch
    Session("Main_Error_Code") = 1
    Session("Main_Error_Message") = "The SQL statement for the following address is invalid. Please examine the GCDB_Excel_Reports_Templates record, and correct." &
                    Chr(13) & "ID " & ti.ID & ", " & ti.Sheet_Name & ", " & ti.Cell_Address

    ' Write to error log
    cxlErrorsSheetLog.Cell("A" & cxlErrorsCurRow).Value = Session("Main_Error_Message")
    cxlErrors.SaveAs(cxlErrorsPath)
    cxlErrors = Nothing
    cxlDoc = Nothing
    Return
End Try

As you can see, I have code in place to catch an error, so I know exactly where the error is happening, but I’m not sure why. The error occurs when

SQL_Template_Query = “DECLARE @Start_Year int; SET @Start_Year=2018; DECLARE @Reporting_Year int; SET @Reporting_Year=2021; declare @First_Year int; set @First_Year = ( case when (@Reporting_Year-@Start_Year+1)<=5 then @Start_Year else @Reporting_Year – 4 end ); drop table if exists #Reporting_Years; create table #Reporting_Years ( Reporting_Year int ); insert into #Reporting_Years select distinct YEAR(Single_Date) from Single_Dates where YEAR(Single_Date) between @Reporting_Year-4 and @Reporting_Year; drop table if exists #Has_ACT; create table #Has_ACT (Building_ID int,Start_Date datetime,End_Date datetime); insert into #Has_ACT select distinct Building_ID,MIN(Start_Date),MAX(End_Date) from Tracking_Periods where Utility_Type_ID between 16 and 19 and YEAR(Start_Date)<=@Reporting_Year and (YEAR(End_Date)>=@First_Year or End_Date is null) group by Building_ID; select i1.Reporting_Year, SUM(case when i2.Asset_Class like ‘Office’ then GFA else null end) as Office_GFA, SUM(case when i2.Asset_Class like ‘Retail’ then GFA else null end) as Retail_GFA, SUM(case when i2.Asset_Class like ‘Industrial’ then GFA else null end) as Industrial_GFA from #Reporting_Years i1 left join ( select z1.Reporting_Year,x.Building_ID,y.Building,y.Asset_Class,x.Start_Year,x.End_Year,y.Most_Recent_GFA_Year, case when z1.Reporting_Year<=y.Most_Recent_GFA_Year then (select GFA from Annual_GFA where Building_ID=x.Building_ID and Reporting_Year=z1.Reporting_Year) else (select GFA from Annual_GFA where Building_ID=x.Building_ID and Reporting_Year=y.Most_Recent_GFA_Year) end as GFA from ( select Building_ID, case when YEAR(Start_Date)<@First_Year then @First_Year else YEAR(Start_Date) end as Start_Year, case when End_Date is null then @Reporting_Year when YEAR(End_Date)>@Reporting_Year then @Reporting_Year else YEAR(End_Date) end as End_Year from #Has_ACT) x left join ( select a.Building_ID,a.Building,a.Asset_Class,MAX(a.Reporting_Year) as Most_Recent_GFA_Year from View_All_Buildings_Annual_GFA a inner join View_All_Buildings b on a.Building_ID=b.Building_ID inner join View_All_Building_Ownership_By_Year c on c.Building_ID=a.Building_ID and c.Reporting_Year=@Reporting_Year inner join Building_Ownership d on a.Building_ID=d.Building_ID left join Override_Building_Count e on a.Building_ID=e.Building_ID and e.Report_ID=5 inner join #Has_ACT f on f.Building_ID=a.Building_ID where b.Asset_Manager like ‘Anonymous’ and b.Not_On_Program=0 and b.Exclude_From_Reporting=0 and b.Tenant=0 and d.Year_Removed is null and (e.Building_Count<>0 or e.Building_Count is null) group by a.Building_ID,a.Building,a.Asset_Class) y on x.Building_ID=y.Building_ID cross join #Reporting_Years z1 where z1.Reporting_Year between @First_Year and @Reporting_Year and z1.Reporting_Year>=x.Start_Year and z1.Reporting_Year<=x.End_Year and y.Building_ID is not null) i2 on i1.Reporting_Year=i2.Reporting_Year where i1.Reporting_Year between @Start_Year and @Reporting_Year group by i1.Reporting_Year order by i1.Reporting_Year; “

I’m providing the string as is, in case anyone wants to know how VB.net is parsing it. The more developer-friendly parsing of this query looks like this.

DECLARE @Start_Year int;
SET @Start_Year=2018;
DECLARE @Reporting_Year int;
SET @Reporting_Year=2021;

declare @First_Year int;
set @First_Year =  
(  
case   
when (@Reporting_Year-@Start_Year+1)<=5 then @Start_Year   
else @Reporting_Year - 4 end  
);       

drop table if exists #Reporting_Years;       

create table #Reporting_Years  
(
Reporting_Year int
);   

insert into #Reporting_Years 
select distinct YEAR(Single_Date) 
from Single_Dates where YEAR(Single_Date) 
between @Reporting_Year-4 and @Reporting_Year;   

drop table if exists #Has_ACT;   

create table #Has_ACT (Building_ID int,Start_Date datetime,End_Date datetime);   

insert into #Has_ACT  
select distinct Building_ID,MIN(Start_Date),MAX(End_Date)  from Tracking_Periods  
where Utility_Type_ID between 16 and 19 
and YEAR(Start_Date)<=@Reporting_Year  
and (YEAR(End_Date)>=@First_Year or End_Date is null)  
group by Building_ID;     

select i1.Reporting_Year,  
SUM(case when i2.Asset_Class like 'Office' then GFA else null end) as Office_GFA,  
SUM(case when i2.Asset_Class like 'Retail' then GFA else null end) as Retail_GFA,  
SUM(case when i2.Asset_Class like 'Industrial' then GFA else null end) as Industrial_GFA  
from #Reporting_Years i1  
left join
(
select z1.Reporting_Year,x.Building_ID,y.Building,y.Asset_Class,x.Start_Year,x.End_Year,y.Most_Recent_GFA_Year,  
case  when z1.Reporting_Year<=y.Most_Recent_GFA_Year   
then 
(select GFA from Annual_GFA where Building_ID=x.Building_ID and Reporting_Year=z1.Reporting_Year)  
else 
(select GFA from Annual_GFA where Building_ID=x.Building_ID and Reporting_Year=y.Most_Recent_GFA_Year)
end as GFA  
from   
(  
select Building_ID,  
case  
when YEAR(Start_Date)<@First_Year then @First_Year  
else YEAR(Start_Date) end as Start_Year,  
case  
when End_Date is null then @Reporting_Year  
when YEAR(End_Date)>@Reporting_Year then @Reporting_Year  
else YEAR(End_Date) end as End_Year from #Has_ACT) x  
left join   
(
select a.Building_ID,a.Building,a.Asset_Class,MAX(a.Reporting_Year) as Most_Recent_GFA_Year  
from View_All_Buildings_Annual_GFA a      
inner join View_All_Buildings b on a.Building_ID=b.Building_ID      
inner join View_All_Building_Ownership_By_Year c on c.Building_ID=a.Building_ID and c.Reporting_Year=@Reporting_Year      
inner join Building_Ownership d on a.Building_ID=d.Building_ID      
left join Override_Building_Count e on a.Building_ID=e.Building_ID and e.Report_ID=5      
inner join #Has_ACT f on f.Building_ID=a.Building_ID    
where b.Asset_Manager like 'Anonymous'      
and b.Not_On_Program=0 and b.Exclude_From_Reporting=0 and b.Tenant=0      
and d.Year_Removed is null    
and (e.Building_Count<>0 or e.Building_Count is null)  
group by a.Building_ID,a.Building,a.Asset_Class) y on x.Building_ID=y.Building_ID  
cross join #Reporting_Years z1 where z1.Reporting_Year between @First_Year and @Reporting_Year  
and z1.Reporting_Year>=x.Start_Year and z1.Reporting_Year<=x.End_Year  
and y.Building_ID is not null) i2 on i1.Reporting_Year=i2.Reporting_Year  
where i1.Reporting_Year between @Start_Year and @Reporting_Year  
group by i1.Reporting_Year  
order by i1.Reporting_Year;

When I run this query in SQL Server Management Studio, character for character, it returns exactly the results I expect.

However, when the VB code runs, it triggers the catch code. Having read posts for similar problems, I tried modifying the query to yield 0 instead of NULL, but the issue persists. Also, other queries yield NULL values but work fine with the VB code.

Any advice would be appreciated. I would also like to know if there’s a way to get SQLDataAdapter or SQLCommand to return the error. It would make things much easier to diagnose.

UPDATE: If I comment out the Try…Catch…End Try, and allow Visual Studio to generate an error, I receive the following.

Answer

So, it appears that @AndrewMorton was on the right track, with respect to multiple SQL queries using a single connection. I created a whole new VB.net project, added code to pull the specific stored SQL statement that was causing the problem, and then ran it. It worked without issue. Knowing that, in the original project, I substituted code to run the particular SqlCommand on a separate connection, disposing of the connection instance after each loop.

The code now works as expected. It is still unclear why this particular SQL statement was causing the issue, but not the others. However, regardless, I will be reworking all the code in this project to move off a single connection object.

The link provided in @AndrewMorton’s other comment, https://ubitsoft.com/t-sql-beautilyzer/ was also helpful. I’d actually been looking for a way to independently check SQL queries, without the necessity of the underlying database.

Advertisement

Answer