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

0
votes

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.

enter image description here

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.

enter image description here

1
sqlsql-servervb.netsqldatareadersqlcommand
Have you consider using a Stored Procedure? – Grantly
Have you tried "catch ex" so you can get at the exception, and print out the exception's text (ex.Message)? – pmbAustin
@Grantly My understanding of stored procedures is that to create one, parameters need to be stated in advance. The first two parameters shown in the query above are actually created dynamically when the code runs, and vary from template to template. Much like the bulk of the SQL query itself, they are pulled from a table at runtime. Therefore, they are unknowns until the code actually runs. – J.P. Brown
@J.P.Brown Incidentally, if Global_Objects.SQLLocalDB_Connection is one connection object that is used throughout, that is the wrong way to use a connection. The correct way is to instantiate the connection, use it, then call .Dispose() on it. – Andrew Morton
@J.P.Brown If you post the SQL into and use the "Analyze" button at t-sql-beautilyzer it shows some suggestions that could be useful. – Andrew Morton

1 Answers

0
votes

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.