SqlDataAdapter Will Not Always Return a Result, SSMS Will
The Issue
I ran into a strange issue with an ASP.NET site (MVC2 – 4.0 Framework) where when executing a stored proc, SqlDataAdapter.Fill(DataTable) will not return any rows some of the time, but SQL Server Management Studio will almost immediately return results. The full method calling the stored procedure is below : (Auto-generated from LLBL Pro V2.6)
public virtual bool CallRetrievalStoredProcedure(string storedProcedureToCall, SqlParameter[] parameters, DataTable tableToFill)
{
SqlCommand command = new SqlCommand(CreateCorrectStoredProcedureName(storedProcedureToCall));
command.Connection = (SqlConnection)base.GetActiveConnection();
if(base.IsTransactionInProgress)
{
command.Transaction = (SqlTransaction)base.PhysicalTransaction;
}
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = base.CommandTimeOut;
using(SqlDataAdapter adapter = (SqlDataAdapter)CreateNewPhysicalDataAdapter())
{
adapter.SelectCommand = command;
for(int i=0;i<parameters.Length;i++)
{
command.Parameters.Add(parameters[i]);
}
adapter.Fill(tableToFill);
}
command.Dispose();
return true;
}
This issue occurs on both our Development and Testing servers. The difference however is that on the development server, it will usually fail once or twice then work, but on the testing server it will return zero rows every time.
What I have done so far
Application
The code I was testing in the application was just a calling the LLBL code above to see if it got any results from the stored proc, and throwing an exception if none were returned. I added in a loop which repeated 30 times, waiting a second between each iteration to alot for SQL Server to finish the inserts. I also set the SetArithAbortFlag flag and the compatibility level for the LLBL adapter, no real difference. In addition, I re-wrote the above method (CallRetrievalStoredProcedure) using a SQLDataReader instead, but would still return no rows from the server.
Note : Putting a break point in the code, I can see the adapter getting the metadata back about the column names, but zero rows. This tells me it’s getting to the server, but not returning the data.
Stored Procedure
The loop worked part of the time on my dev machine, working on average after 5 seconds. After running the stored procedure in SSMS, I would have results almost immediately. So, I looked at issues which caused the application to take much longer, and came across a few posts/questions with a few suggestions (These refer to time out issues, but I thought I’d go with it). So I set ARITHABORT ON, cleared the plans in cache, trying to disable parameter sniffing, etc… but no luck. After reading another StackOverflow question, I also cleared out the parameters and included them one by one, but still behaved the same way. Note : If you are working through the same issue and need to figure out what options you have on in SQL Server, Greg Robidoux at mssqltips.com wrote a quick wayto view just that.
DECLARE @options INT SELECT @options = @@OPTIONS PRINT @options IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE' IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'
SQL Profiler
I did run the SQL Profiler to make sure ARITHABORT was getting set at the proper times, etc… and all looked fine. It seemed everything was running at reasonable levels and not timing out, which confuses me more on what the issue could be. SQL Server After reading about a few issues where nothing was coming back due to permissions, I decided to compare the permissions between the dev and the QA user. There were some differences, however all the correct permissions were enabled for the QA user. (Select, Update, Execute, etc…)
Comparing Sql Servers
An idea from tgyoga (which I feel like an idiot not trying before hand), I hooked up my local code to my qa server and stepped through. It worked just like when hooked up to my dev database, so that finally pointed me at a config issue deep in the code.
Other Resources
If my post didn’t help you, here are some similar questions which I did some of my research from : StackOverflow
- http://stackoverflow.com/questions/12740617/datatable-not-returning-records-in-net
- http://stackoverflow.com/questions/769128/sqldataadapter-fill-timeout-underlying-sproc-returns-quickly
- http://stackoverflow.com/questions/4924930/sqldataadapter-filldatatable-returns-no-rows
ASP.Net Forums
