ASP.NET stored proc call bringing back no rows, but does in Management Studio!

Question

I have an unbelievably strange problem which I have been trying to fix for almost a day, and I'm now against a brick wall and need help!

I have created a .NET 4 C# website on my Windows 7 PC, with a newly installed SqlServer 2008 Express R2, and all works fine.

I have uploaded the database and website. The website is now on a Windows Web Server 2008 R2 (with service pack 1) The Database is on a different server running Windows Server 2008 R2 Standard with SQL Server 2008 R2.

The rest of the website is running fine and connecting to the database fine, except for one page, and I have narrowed it down to a stored procedure call returning no rows.

I have simplified the call to this:

DataSet ds = new DataSet();
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ToString());
        SqlCommand cmd = new SqlCommand("MYSP 'param1', param2, param3", conn);
        cmd.CommandType = CommandType.Text;
        conn.Open();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        Response.Write("ROWS COUNT IN GET DS " + ds.Tables[0].Rows.Count);
        conn.Close();
        cmd.Dispose();
        return ds;

For any of the above if I run this on my local PC against an exact copy of the database it returns 2 rows, and if I run it directly in Sqlserver Management Studio on the live server it returns 2 rows, but if I run this via ASP.net on the live server it returns no rows!

Can anyone shed any light??! I am going insane!

Update even though I am using command type text, I have tried it as command type stored procedure with separated params (as follows) but it makes no difference

cmd.CommandType = StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@param1", param1));
            cmd.Parameters.Add(new SqlParameter("@param2", param2));
            cmd.Parameters.Add(new SqlParameter("@param3", param3));

UPDATE

Thank you all for your comments!

The answer is:

The db user that the website was using was set at "British English" (by default) .. I didn't realise this as it's a new server!

Everywhere else the user is set to "English", so changing the user to English fixes it!

is myConn existing in the web.config of that server ? With valid cridentials ? — David, Jul 14 '11 at 12:48
@David Thanks for the answer, yes it is valid as other sps work fine — Bex, Jul 14 '11 at 12:51

score 5 · Accepted Answer · answered Jul 14 '11 at 13:36

5

usual things to try: When you're logging on to the database to run your query, are you doing so as the SAME user that the asp.net process is using? You may find that if you're using two users, they may be set-up differently (one as EN-GB, one as EN-US), which would give you odd date based problems? Also, you might find priveldges are different for both, so one'll have tempDb rights, the other not?

answered Jul 14 '11 at 13:36

Oxonhammer

190
3
14

Ok, the answer is partly this and the convuluted SP my colleague wrote! The users one the live server are defaultin to "British English" and everywhere else "English" and the stored procedure does some date parsing! So I changed my DB user to "English" and all was fine! Yay! – Bex Jul 14 '11 at 14:00
Thank you for the vote. I speak with much experience here, as this is ALWAYS happening on our servers! ;-) – Oxonhammer Jul 14 '11 at 14:21

marc_s · Answer 2 · 2011-07-14T12:55:32.757

2

If it's a stored procedure you're calling, you should set the SqlCommand.CommandType to stored procedure:

DataSet ds = new DataSet();

using(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ToString()))
using(SqlCommand cmd = new SqlCommand("MYSP 'param1', param2, param3", conn))
{  
    cmd.CommandType = CommandType.StoredProcedure;  <== STORED PROCEDURE !!
     ......

}

Update: since you're executing the stored proc with parameters, you should probably use this instead:

using(SqlCommand cmd = new SqlCommand("exec dbo.MYSP 'param1', param2, param3", conn))

and then use CommandType.Text again.

edited Jul 14 '11 at 12:55

answered Jul 14 '11 at 12:48

marc_s

732,580
175
1,330
1,459

I know this is usually the case, but I am passing my param's in along with the SP (for testing purposes) so it is actually text. If I change it to stored procedure it throws an error saying "can't find stored procedure MYSP, param1, param2, param3". Either way it works fine on my local machine – Bex Jul 14 '11 at 12:53
@Bex: then you need to add the params separately...... or change the "text" to `exec MYSP .......` – marc_s Jul 14 '11 at 12:53
This is how the live website was before I tried to narrow down my problem (I mean with separate params) but it was returning nothing then. This is why I changed it to just text. And still it works fine this way on my local machine! – Bex Jul 14 '11 at 12:55
2

Did you verify your connection string is pointing to the same database that the query works? Also, I just tested using SqlCommand, you don't have to specify cmd type stored procedure, with or without the the exec, didn't know that. Anyway, I think you need to verify you are hitting the same db. – Ta01 Jul 14 '11 at 12:59
@Bex. have you tried separating the parameters into "normal" `SqlParameter` for the `SqlCommand` ?? – marc_s Jul 14 '11 at 13:00
@kd7 It's definitely the same database.. I know it's nuts! – Bex Jul 14 '11 at 13:01
@marc_s this is how it all started with "normal" parameters, but I have just tested it again, (just incase) and it still makes no difference! – Bex Jul 14 '11 at 13:07
@kd7 Sorry I can't post that.. it was written by a colleague years ago and is ridiculously long and complicated and contains data I'm not allowed to show. I don't think it's the Stored Proc though as it works everywhere else but I am open to an options and will try and see if there is anything in there.. – Bex Jul 14 '11 at 13:15

score 1 · Answer 3 · answered Jul 14 '11 at 13:34

Use SQL Server Profiler to capture the actual stored procedure call as it's issued by your production ASP.NET server, then do the same for your development setup. Make sure the parameters going in are the same.

If the parameters match at that level, the only answer that seems possible is that your colleague's complicated SP has some non-deterministic component that is screwing up your results. Do you understand the entire SP?

score 1 · Answer 4 · answered Jul 14 '11 at 13:40

Ok, wild shot here, but I notice that you are doing ds.Tables[0].Rows.Count and I wonder if your stored procedure is returning multiple tables of data? The best answer so far is from @pseudocoder to use the profiler to see what is actually getting called. I would also suggest putting a break point in your code and examining the dataset that is coming back.

ASP.NET stored proc call bringing back no rows, but does in Management Studio!

4 Answers4

Linked