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

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 开发者_如何学Cand 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!

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?

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.

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?

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.