.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

call to stored procedure occasionally returns empty recordset

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server


I have a devil of an intermittent problem that I have so far found nothing to help me solve.

I have a web application/module in Dotnetnuke that requires data for a simple form from another SQL server DB on a different server.  The app uses a connection string in the web.config like this:

<add name="CompanyUpdate" connectionString="Data Source=mail.mydatabase.be;Initial Catalog=MA_Online;Persist Security Info=True;User ID=Gus;Password=xxxxxx" providerName="System.Data.SqlClient" />

The application calls a stored proc and most of the time data is returned as expected. There should always be a row returned from the SP.  However, if the form is idle for a while or the module has only just loaded the call to the stored proc returns as empty recordset. A 'table not found' error occurs.

There is no connection issue otherwise there would be a connection error instead of an empty recordset right?. But I cannot work out why sometimes no data is returned. I can go onto the server and run management studio and run the sp over and again and always get data.

I don't have control over the target DB but the person who does thinks it's something to do with connection pooling. I tried turning this off in the connection string and found that I got an empty recordset every time the sp was called wit

View Complete Post

More Related Resource Links

Call SQL Stored Procedure Asyncronously



I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.

Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background. Also, I do not want the web server processes to be busy with the running stored procedure.

Any help, please.

Best regards,


stored procedure call to C#

Hi I created the Only stored procedure  for dynamically  I want to call that procedure in C# stored procedure CREATE   PROC SearchAndReplace ( @SearchStr nvarchar(100), @ReplaceStr nvarchar(100) ) AS BEGIN   -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.   -- Purpose: To search all columns of all tables for a given search string and replace it with another string   -- Written by: Narayana Vyas Kondreddi   -- Site: http://vyaskn.tripod.com   -- Tested on: SQL Server 7.0 and SQL Server 2000   -- Date modified: 2nd November 2002 13:50 GMT   SET NOCOUNT ON   DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int   SET @TableName = ''   SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')   SET @RCTR = 0   WHILE @TableName IS NOT NULL   BEGIN   SET @ColumnName = ''   SET @TableName =     (   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))   FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_TYPE = 'BASE TABLE'   AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName   AND OBJECTPROPERTY(   OBJECT_ID(   QUOTENAME(TABLE_SCHEM

New to WCF, how can i call a stored procedure via WCF?

Ok, i may be completely off track.  But i'm trying to get away from the old webservices, so i'm reading up on WCF.  I can't seem to get my head around what i'm doing wrong.  I'm attempting a simple call to a stored procedure on a sql server express. Here is my web.config <system.serviceModel> <services> <service behaviorConfiguration="ndfService" name="ndfService"> <endpoint address="http://localhost:8080/NDFService" binding="wsHttpBinding" contract="NDFService.IService" /> <host> <baseAddresses> <add baseAddress="http://localhost:8080/NDFService"/> </baseAddresses> </host> </service> </services> <behaviors> <serviceBehaviors> <behavior name="ndfService"> <!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment --> <serviceMetadata httpGetEnabled="true"/> <!-- To receive exception details in faults for debugging purposes, set the value below to true. Set to false before deployment to avoid disclosing exception information --> <serviceDebug includeExceptionDetailInFaults="true"/> </beha

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Entity Model- Add Function Import - selected stored procedure returns no columns

I am using stored procedures returns columns from pivot table generated dynamically "Execute (@PivotTableSQL)".So the resultset columns cannot be identified. Please let us know how to add function import and Get Column information(create complex Type). Thanks in advance. My stored procedure is alter PROCEDURE spGetQuestGrid( @QID as int)ASBEGIN SET NOCOUNT OFF SET FMTONLY OFF  Declare @optId varchar(10) set @optId = (select DISTINCT Optid from  QuestOptions where  QID = @QID)    DECLARE @PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders =  COALESCE(@PivotColumnHeaders + ', [' + cast(Caption as varchar) + ']' ,  '[' + cast(Caption as varchar)+ ']' ) FROM OptionsDetail where OptId = @optId  DECLARE @PivotTableSQL NVARCHAR(MAX)  SET @PivotTableSQL = 'select * from  ( select  Caption,Optid  from OptionsDetail ) as dt PIVOT  ( min(Optid) FOR Caption IN ( '+ @PivotColumnHeaders + '))as pt'     Execute (@PivotTableSQL)  END 

stored procedure returns numbers ()

I would like to write a stored procedure which will return some numbers based on query, and it will go something like this: - find an id of a person, if his username exists - if  id is not found retun 0 - else if the id is found, look for person`s password - if password is NOT found return 1 - else if password IS found return 2   I did some coding, but it seems its not working somehow. It always retuns null value: ps: It is not important if I use "IF EXISTS", it can also be used "IF IS NOT NULL". And I tried to use if its null or not, but I got the same result - nothing (only null)   ALTER PROCEDURE myStoredProcedure01 ( @strValue varchar(50) ) AS BEGIN SET NOCOUNT ON IF EXISTS(SELECT PersonID FROM Person WHERE UserName = @strValue) BEGIN DECLARE @password varchar(50) SELECT @password = Password FROM Person WHERE UserName = @strValue IF(@password IS NULL ) RETURN 2 ELSE RETURN 1 END ELSE RETURN 0 END    

can I create a job to call a stored procedure?




Can I create a job to call a stored procedure everyday? If yes, how to do it?



JDBC - calling stored procedure always returns 1 result set


I am running the following against Northwind on Sql Server 2005. It always returns a result set of 1 row regardless of the parameters passed:

Connection conn;
// not showing conn creation

CallableStatement callStmt = conn.prepareCall("{ call [Sales by Year] (?,?) });
callStmt.setDate(1, new Date (long_Jan_1_1996));
callStmt.setDate(2, new Date (long_Jan_1_1997));
ResultSet rs = callStmt.executeQuery();

Why do I only get 1 row of results back?

thanks - dave

Very funny video - What's your Metaphor?

Stored procedure returns two select results


Hey guys

I am creating a buddy list and I need to select my entire buddy list and then the ones that are online

I have two querries one is your standard


FROM buddies


the other is a bit more complicated


SELECT Buddy as Online
FROM Buddies
FROM online 
WHERE online.Buddy = Buddies.Buddy)

I can't use the union command since I create another column

What I would like is to append the column to my original result so I can access it from some vb script using the sqlclient.datareader


How do I call an SSRS Report from a stored procedure, and pass parameters?


Hi all,

I have a Report, that I want to run ad-hoc from a Stored Procedure. 

I want to render this report to PDF format, and save it to a drive.

The difference between this post and most threads I've seen is I don't want to run a stored proc within the report and send parameters to the stored proc.

I DO want to call a report FROM the stored proc, and send parameters TO the report.

How can I do this?

Many thanks,


help inserting an empty string and a default date thru a stored procedure


I want to insert an empty string in a nvarchar field and a default date like a base date in a datetime field inside a stored procedure without passing it the value thru a parameter

how is this done?

INSERT INTO deals_SoldItems (











this dont work



How to call Oracle Stored Procedure which has an output parameter from SSIS?


I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:


    PROCEDURE Interface_Begin

    (p_from_dttm       OUT varchar2,

     p_error_code            OUT number,

     p_error_text       OUT            varchar2,

     p_proc_name        OUT varchar2);  


dropdown list fill with Linq and convert string to datetime and ordering and stored procedure call.


I wanted to share this more than anything,  it took me most of the morning searching and finding various post on various sites but I got it figured out and working.  It might be better coded, but it is a start for some one trying to understand how linq and ASP.Net things work.

What I have is a database table with a datetime in string format and a stored procedure which returns all the data.
What I want to do is load a dropdown list with MonthName and Year for a selection choice on generating monthly report.

The stored procedure is in a Linq to SQL class dbml and the connection string is dynamic, i.e., made througha call to another class.

here is the code.  Enjoy understanding how it works.

// Miscellaneous Details are filled in for helping you get the big picture.
// Some of the using statements are for other things in the code behind, 
// but I left it in so don't get confused,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections.ObjectModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using MYProject.LinqtoSQLStuff;

namespace MYProject.ChooseReportPage
	 public partial class TheReportPage : System.Web.UI.Page  
	     protected void Page_Load(object sender, EventArgs e)


Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Inserting rows via stored procedure and under certain conditions


I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

PersonID (FK)
LocationID (FK)

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Creating A Stored Procedure Which Searches Team Names



I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.

Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend