Different results when executing the SQL Server query

Question

In the program there is a part of the code, which executes the query:

string SPROC = "#Some MSSQL Query#";
comm.CommandType = CommandType.Text;
comm.CommandText = SPROC;
comm.Parameters.Add(new SqlParameter("@Parameter1", SqlDbType.VarChar, 50)).Value = PARAM1;
comm.Parameters.Add(new SqlParameter("@Parameter2", SqlDbType.VarChar, 20)).Value = PARAM2;
comm.CommandTimeout = CurrentSettings.SQLTimeout;

conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(comm);
DataSet TempDS = new DataSet();
adapter.Fill(TempDS);

The dataset (TempDS) table is supposed to have around 20000 records (row count) but inside the program it is saying only 20 records while in debugging mode.

I ran the script separately on the same database table, which is being used in the SQL Server connection string, but in SQL Server Management Studio, it returned 20000 records as it is supposed to do.

The script looks some what like:

Select 
    [Table1].Column1,[Table2].Column1 
From 
    [Table1]
Inner Join 
    [Table2] on ([Table2].Column1 = [Table1].Column2)
Where 
    [Table2].Column1 = @Parameter1 
    and ([Table1].Column1 in (Select * from dbo.Split(@Parameter2,','))

It is the same script text, which the code executes as well.

Does anybody have any idea why the program code is only bringing back part of the record whereas the script in SQL Server Management Studio returns all the records?

P.S. I have also checked the SQLTimeOut and it is set to 240, so more than enough time to return the records properly. And @Parameter1 and @Parameter2 are string variables.

string PARAM1 = "Random1"; //in C#
string PARAM2 = "Random2,Random3,Random4,Random5"; //in C#
@Parameter1 = PARAM1
@Parameter2 = PARAM2

First use profiler to look at the query being sent from your application to SQL and verify it is the query you expect it to be. — Tab Alleman
– Tab Alleman, Commented Jan 26, 2016 at 16:14
Check you are actually using the same database when debugging. — Polyfun
– Polyfun, Commented Jan 26, 2016 at 16:15
Have you tried running the stored procedure directly on SQL directly by Right clicking the Stored procedure and choosing "execute"? If so, make sure you are passing in the same parameter values that your program is — DiscipleMichael
– DiscipleMichael, Commented Jan 26, 2016 at 16:15
Should not CommandType here be StoredProcedure and not Text? Is that a stored procedure name? (SPROC) msdn.microsoft.com/en-us/library/… — Mark Schultheiss
– Mark Schultheiss, Commented Jan 26, 2016 at 16:18
Debug and break right before conn.Open() check comm.Parameters["@Parameter1"] and comm.Parameters["@Parameter2"] — DiscipleMichael
– DiscipleMichael, Commented Jan 26, 2016 at 16:20

Mark Schultheiss · Accepted Answer · 2016-01-26 16:35:42Z

2

The value of string exceeds the parameter length in the C#

string PARAM2 = "Random2,Random3,Random4,Random5"; //in C#

comm.Parameters.Add(new SqlParameter("@Parameter2", SqlDbType.VarChar, 20)).Value = PARAM2;

so you need to allocate more length here

Try using AddWithValue : https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx

NOTE: IF you do this, also be aware of the SPLIT function capacity of the input; it might need to be adjusted to some large value - search for some split functions that take large inputs

answered Jan 26, 2016 at 16:35

Mark Schultheiss

34.3k13 gold badges75 silver badges116 bronze badges

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

2 Comments

Tab Alleman Over a year ago

OP should have caught this in the profiler trace though.

Mark Schultheiss Over a year ago

Frankly I have made this mistake; and caught it sometimes a second set of eyes is very productive. Experience and making mistakes and fixing them has use.

Collectives™ on Stack Overflow

Different results when executing the SQL Server query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related