Stored Procedure and C# SqlDataReader Returns Less Rows Than Expected

Question

I have been asked to migrate a website application from one server to another. The code was written by previous developer.

He created a stored procedure with the following: [Parameters]

            @SurveyPeriodConfigID int = -1,
            @StartDate DATETIME = NULL,
            @EndDate DATETIME = NULL,
            @StartTime DATETIME = [9:00:00],
            @EndTime DATETIME = [19:00:00],
            @Category INT = -1,
            @SubcategoryID INT = -1,
            @ReportType VARCHAR(50) = null,
            @DepartmentID INT = -1,
            @FloorID INT = -1,
            @RoomID INT = -1,
            @IsLazyLoad BIT = 0,
            @Day INT = -1

and [WHERE caluse]:

            SELECT [fields]
            FROM [tables]
            WHERE 
            (DATEPART(dw, rsdm.DateCreated)) != 7 AND (DATEPART(dw, rsdm.DateCreated)) !=1
                    AND rsdm.DateCreated BETWEEN @StartDate AND @EndDate
                    AND (CONVERT(VARCHAR(8),rsdm.DateCreated,108) BETWEEN @StartTime AND @EndTime)
                    AND (@Category = -1 OR rsdm.CategoryCodeID = @Category)
                    AND (@SubcategoryID = -1 OR rsdm.SubcategoryID = @SubcategoryID)
                    AND (@DepartmentID =-1 OR rsdm.DepartmentID = @DepartmentID)
                    AND (@FloorID =-1 OR rsdm.FloorID = @FloorID)
                    AND (@RoomID =-1 OR rsdm.RoomID = @RoomID)
                    AND (@Day =-1  OR  DATEPART(dw, rsdm.DateCreated) = @Day)

C# Code:

            SqlDataReader sqlDataReader = thisCommand.ExecuteReader(CommandBehavior.CloseConnection);

...calls the stored procedure and returns SqlDataReader.

So far so good.

Now the issue... previously the old site returned 8200 rows but now the new website returns 6500 rows!!!

I setup the code and website on my PC on my copy of sql database... it returns correct value of 8200. I then changed web.config to point to new website sql server... 6500 rows!!!

I backed up live site sql database and restored said same database locally on my PC and tested it... 8200 rows!!!

Old site had Sql server 2005, my PC has developer SQL 2012 whilst live server has SQL 2012.

I then ran sql profiler on new site Sql Server, extracted script and ran script on same sql database 8200 records!!!

I've spent the past 1-2 days on this and still no idea why live Sql Server return 6500 rows via sqldatareader.

Any suggestions would be greatly appreciated.

I am sorry to say, but without the actual data it is almost impossible to answer this question. — Patrick Hofman
– Patrick Hofman, Commented Mar 20, 2014 at 13:51
is CS/CI (case-sensitivity) setting the same? are the ansi-null defaults the same? — Marc Gravell
– Marc Gravell, Commented Mar 20, 2014 at 13:52
OK was hoping someone would say it was something in SqlDataReader that i'm missing something. I've checked the data and it appears to be fine. — Rob
– Rob, Commented Mar 20, 2014 at 13:52
Start removing where clasues and see if you eventually get data, then add them in again and you'll figure out where it goes wrong — Allan S. Hansen
– Allan S. Hansen, Commented Mar 20, 2014 at 13:54
Why are you using CONVERT(VARCHAR(8),rsdm.DateCreated,108) BETWEEN @StartTime AND @EndTime ? casting dates via strings is never a good idea; what is the intent there? (note this does two casts - one implicit, one explicit - not good) — Marc Gravell
– Marc Gravell, Commented Mar 20, 2014 at 13:55

to StackOverflow · Accepted Answer · 2014-03-20 14:04:34Z

3

I would check if options are configured differently on the different servers, in particular ANSI_NULLS (see script below). Note that when you run a script in SSMS, the default options are likely different to those in effect when using ADO.NET (see SSMS Tools / Options / Query Execution / SQL Server / ANSI). This often explains differences in behavior between SSMS and ADO.NET.

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'

answered Mar 20, 2014 at 14:04

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rob Over a year ago

5496 ANSI_WARNINGS ANSI_PADDING ANSI_NULLS ARITHABORT QUOTED_IDENTIFIER ANSI_NULL_DFLT_ON CONCAT_NULL_YIELDS_NULL on both Sql 2012 servers

Marc Gravell Over a year ago

@Rob you need to run it separately from both SSMS and via SqlClient, against separately both servers - the point being to find what is different. SSMS does not use the same defaults as SqlClient (indeed, the SSMS options depend on your own user-specific options inside SSMS)

George Mastros · Accepted Answer · 2014-03-20 14:29:55Z

1

When you connect to the database, there is always a login account associated with it. Are you using a different login for your app than you use when connecting with SQL Server Management Studio? Date formats are based on the default language for your logins.

Please run the following query:

Select  syslogins.name, syslogins.language, syslanguages.dateformat
From    sys.syslogins
        Inner Join sys.syslanguages
            On syslogins.language = syslanguages.name

Check the dateformat column for all the logins returned. If the dateformat is different, then this probably explains why you are getting different results when running from your app vs. running from SSMS.

If this truly is the cause of your problems, then you can change the default language for your logins by following the directions in this blog: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/setting-a-standard-dateformat-for-sql-se/

answered Mar 20, 2014 at 14:29

George Mastros

24.6k5 gold badges56 silver badges64 bronze badges

1 Comment

Rob Over a year ago

Thankyou!!! Select syslogins.name, syslanguages.datefirst From sys.syslogins Inner Join sys.syslanguages On syslogins.language = syslanguages.name ... helped!!!

Marc Gravell · Accepted Answer · 2014-03-20 14:05:46Z

1

Since you mention the results are different between SSMS and SqlClient, there are two options I can think of

there are actually 2 different sprocs in different schemas, and SSMS is choosing one and SqlClient the other (presumably based on identity)
(much more likely) the SET options are different, causing different behavior

To check the latter, run select @@OPTIONS in both SSMS and via SqlClient, and compare. The flags are listed here: http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/

answered Mar 20, 2014 at 14:05

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

1 Comment

Rob Over a year ago

OK appreciate this. 5496 SSMS, 5492 SqlClient. What now? How is one different from each other?

Rob · Accepted Answer · 2014-03-20 14:55:16Z

0

OK the problem was with (DATEPART(dw, rsdm.DateCreated)) != 7 AND (DATEPART(dw, rsdm.DateCreated)) !=1

SqlClient user had DATEFIRST set as 1, where as my SSMS was 7. All other same user connections to other sql server instances had DATEFIRST set as 7. (Thanks also to @"G Mastros" for final confirmation of issue).

All I did was add SQL Script SET DATEFIRST 7; for now and will deal with why users are setup with 1 later!

edited Mar 20, 2014 at 14:55

answered Mar 20, 2014 at 14:40

Rob

3,0844 gold badges35 silver badges32 bronze badges

Collectives™ on Stack Overflow

Stored Procedure and C# SqlDataReader Returns Less Rows Than Expected

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related