RE: Stored procedure / .NET woes

Check the available network protocols on both the server and the client (web
server), using the SQL client and server network tools.

Most likely, the web server is set up with TCP and you do not have this
protocol installed on the server. If this is the case, installing will likely
cure. If it does not, move the TCP protocol up higher on the list and you
should be fine.

NOTE: Query Analyzer is more flexible on protocol than a web app, so it is
not surprising you can still connnect with it.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

"mfos239" wrote:

>
> I have no idea what is going on here, maybe someone can help me out.
>
> I have developed an application for my company's intranet using ASP.net
> and SQL Server. We have two servers, one hosts the .NET apps and the
> other runs SQL Server 2000. The application runs without a hitch most
> of the time, but every morning I experience the same exact problem. One
> (and only one) page which gets its data using a stored procedure call to
> SQL Server throws an exception "General Network Error. Check your
> Network Documentation." It will throw this exception or just return 0
> results.
>
> I did a test to see if there was something else on the page causing
> problems. I created a separate test page with only a call to the stored
> procedure. The procedure still yields the same "General Network Error."
>
> Strangely enough running the same stored procedure through the SQL
> Query Analyzer does not produce an error and instead runs perfectly
> fine and returns the expected results.
>
> A temporary solution I have found is to simplify the stored procedure
> in SQL Server by removing its WHERE clause, apply the changes, then go
> back to my .net app and run the page. The page loads fine. I then go
> back to the procedure again and reinsert the WHERE clause to change it
> back to what it was originally. I apply the changes and my .net app
> still works fine.
>
> It will continue to work fine the rest of the day and then in the
> morning the same problem will crop up again. The only thing we can
> think of at all is that there is a backup performed each night. But why
> would this affect a stored procedure? And even if it did, seems like
> that same stored procedure would have a problem in SQL Query Analyzer
> as well as .NET.
>
> Any ideas?
>
> Here are some code snippets:
>
>
> Code:
> --------------------
> CREATE PROCEDURE usp_GetActiveClients (
> @FirstLetter varchar(5) = '')
> AS
> SELECT c.CompanyID, c.ParentCoID, dbo.MoveTheToEnd(c.CompanyName) as CompanyName,
> dbo.GetCompanyStatusForProgram(c.CompanyID, ParentCoID, 1) as State,
> dbo.GetCompanyStatusForProgram(c.CompanyID, ParentCoID, 2) as Federal,
> dbo.GetCompanyStatusForProgram(c.CompanyID, ParentCoID, 3) as WOTC,
> dbo.GetCompanyStatusForProgram(c.CompanyID, ParentCoID, 4) as BusIncentives,
> dbo.GetCompanyStatusForProgram(c.CompanyID, ParentCoID, 5) as Sales
> FROM tblCompanyHeader c
> WHERE c.CompanyID=ParentCoID AND dbo.IsActiveClient(CompanyID) = 1
> AND (CompanyName LIKE @FirstLetter+'%'
> OR CompanyName LIKE 'The ' + @FirstLetter+'%')
> ORDER BY CompanyName
> GO
> --------------------
>
>
>
> Code:
> --------------------
> CREATE FUNCTION GetCompanyStatusForProgram (
> @CompanyID int,
> @ParentCoID int,
> @CompanyProgramID int)
> RETURNS varchar(20)
> AS
> BEGIN
>
> DECLARE @StatusType varchar(20)
>
> SELECT @StatusType=st.StatusType
> FROM tblCompanyStatus cs INNER JOIN tblListStatusType st ON cs.StatusTypeID=st.StatusTypeID
> WHERE cs.CompanyID=@CompanyID
> AND cs.CompanyProgramID=@CompanyProgramID
>
> IF @@rowcount > 0
> RETURN @StatusType
> ELSE
> SELECT @StatusType=st.StatusType
> FROM tblCompanyStatus cs INNER JOIN tblListStatusType st ON cs.StatusTypeID=st.StatusTypeID
> WHERE cs.CompanyID=@ParentCoID
> AND cs.CompanyProgramID=@CompanyProgramID
>
> RETURN @StatusType
> END
> --------------------
>
>
>
> Code:
> --------------------
> CREATE FUNCTION IsActiveClient
> ( @CompanyID int)
> RETURNS bit
> AS
> BEGIN
> DECLARE @cnt int
> SELECT @cnt = count(*)
> FROM tblCompanyStatus cs
> WHERE cs.CompanyID=@CompanyID
> AND (cs.StatusTypeID = 1 OR cs.StatusTypeID = 6)
> AND (cs.CompanyProgramID = 1
> OR cs.CompanyProgramID = 2
> OR cs.CompanyProgramID = 3
> OR cs.CompanyProgramID = 4)
> RETURN @cnt
> END
> --------------------
>
>
> --
> mfos239
> ------------------------------------------------------------------------
> mfos239's Profile: http://www.msusenet.com/member.php?userid=483
> View this thread: http://www.msusenet.com/t-1869412874
>
>
.

Relevant Pages

  • RE: SQL Server 2000 SP4 - Intermittent timeout/connection issues
    ... With a network problem nothing is ever simple dealing with sql. ... Could you put an LMHosts file on the web servers to the sql server ...
    (microsoft.public.sqlserver.server)
  • Re: Problem using SP as record source with Access2002 & SQL Server 7
    ... I've installed a brand new copy of SQL Server 2000 with the original ... I've created the folowing stored procedure for the resync ... Resync Command: ALL_CUSTOMERS_RESYNC? ... I need to upgrade to a newer version of Microsoft SQL ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Irritating SQL 2000/2005 connection problem
    ... installed on our network anywhere. ... SqlConnection) to connect to a SQL 2000 database with a connection string ... When connecting to SQL ... Server 2005, this failure may be caused by the fact that under the default ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL2005 Failed when installing SP2
    ... Somehow the name of the Virtual Server already existed on the network. ... It may be that the system tried to start SQL on both nodes at the same time. ... I would move both instances to the same node and attempt to complete the SP install. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Connection from remote computer to network SQL Server
    ... by default connectin to SQL Express from the network is disabled ... There is no firewall on the W2K machine acting as the SQL server. ... connection works. ...
    (microsoft.public.access.adp.sqlserver)