Databases
StackOverflow2013
Postsdbo
POASP.NET stored proc call bringing back no rows, but does in Management Studio!
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POASP.NET stored proc call bringing back no rows, but does in Management Studio!
    primarykey
    data
    text
    <p>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!</p> <p>I have created a .NET 4 C# website on my <em>Windows 7</em> PC, with a newly installed <em>SqlServer 2008 Express R2</em>, and all works fine.</p> <p>I have uploaded the database and website. The website is now on a <em>Windows Web Server 2008 R2 (with service pack 1)</em> The Database is on a different server running <em>Windows Server 2008 R2 Standard with SQL Server 2008 R2.</em> </p> <p>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.</p> <p>I have simplified the call to this:</p> <pre><code>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; </code></pre> <p>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!</p> <p>Can anyone shed any light??! I am going insane!</p> <p><strong>Update</strong> 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</p> <pre><code>cmd.CommandType = StoredProcedure; cmd.Parameters.Add(new SqlParameter("@param1", param1)); cmd.Parameters.Add(new SqlParameter("@param2", param2)); cmd.Parameters.Add(new SqlParameter("@param3", param3)); </code></pre> <p><strong>UPDATE</strong></p> <p>Thank you all for your comments! </p> <p>The answer is:</p> <p>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!</p> <p>Everywhere else the user is set to "English", so changing the user to English fixes it!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBex
    1. USBex
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POASP.NET stored proc call bringing back no rows, but does in Management Studio!
      1. This table or related slice is empty.
      1. VTUpMod
    1. COis myConn existing in the web.config of that server ? With valid cridentials ?
      singulars
      1. POASP.NET stored proc call bringing back no rows, but does in Management Studio!
      1. USDavid
    2. CO@David Thanks for the answer, yes it is valid as other sps work fine
      singulars
      1. POASP.NET stored proc call bringing back no rows, but does in Management Studio!
      1. USBex
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload