If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Error Connecting to SQL Server 7

Hi guys, hope you can help me out on this. Sorry if this is the wrong group
to post to. I was not sure if I should post to here or the SQL group. I
did post to the SQL group as well but i have not had a response in a couple
of days so I thought maybe it was the wrong group. But my problem is that
when I am trying to connect to SQL Server I am getting the error:

Now, I do not get the error on the same line of code all the time. It can
be on a different line, in a different function, and I do not get it when
I step through the code, and sometimes it works perfectly. It generally
gives the error in one of the functions *after* I open a different form and
execute a stored procedure that returns records. I have several functions
that return values retrieved from SQL Server. These functions are located
at the module level. Here is a sample of what one of the functions looks
like:

Re: Error Connecting to SQL Server 7

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>Hi guys, hope you can help me out on this. Sorry if this is the wrong group
>to post to. I was not sure if I should post to here or the SQL group.
I
>did post to the SQL group as well but i have not had a response in a couple
>of days so I thought maybe it was the wrong group. But my problem is that
>when I am trying to connect to SQL Server I am getting the error:
>
>-2147467259 - [DBMSSOCN]General network error. Check your network documentation.
>
>
>Now, I do not get the error on the same line of code all the time. It can
>be on a different line, in a different function, and I do not get it when
>I step through the code, and sometimes it works perfectly. It generally
>gives the error in one of the functions *after* I open a different form
and
>execute a stored procedure that returns records. I have several functions
>that return values retrieved from SQL Server. These functions are located
>at the module level. Here is a sample of what one of the functions looks
>like:
>
>Function Get_Product_FBMCONV(ByVal pID As Long) As Double
> On Error GoTo errH
> Dim rs As ADODB.Recordset
>
> Set rs = New ADODB.Recordset
> rs.CursorLocation = adUseClient
> rs.Open "Select [FBMCONV] from INVENTORY_MASTER where [ID]
>= " & pID, "Provider=SQLOLEDB;Server=TestServer;Database=BayridgeTest;
> Trusted_Connection=No;UID=sa;PWD;", adOpenStatic, adLockReadOnly,
>adCmdText
> Set rs.ActiveConnection = Nothing
>
> If rs.EOF Then Get_Product_FBMCONV = 0 Else Get_Product_FBMCONV = rs!FBMCONV
> rs.Close
> Set rs = Nothing
>errH:
> If Err <> 0 Then If ErrP("Get_Product_FBMCONV", Err) = 0 Then Resume
>Next Else Resume
>End Function
>
>I am a little baffled by the error because I am pretty sure there is not
>a problem with the network because I can open any other form and establish
>a connection with no problem.
>
>Any help/suggestions appreciated.
>
>Thanks,
>
>Darcey
check your password declaration in the connection string; it should read
pwd=<password>

Re: Error Connecting to SQL Server 7

Hi j-y,

thanks a lot for the suggestion, but i am still receiving that error. Just
so you know a little more i am using VB 6 SP4 , mdac 2.5, TCP/IP. I only
get the error *after* i open another form and execute a stored procedure
that returns two recordsets *and* if i leave the form open. Here is the code
that executes the stored procedure:

errH:
If Err <> 0 Then If ErrP("INVInquiry.btnView.Click", Err) = 0 Then Resume
Next Else Resume
End Sub

incidently the form that returns that error after i execute the above code
opens and closes connections as needed (approx. 50 times). Is this a good
idea or should I leave a connection open while the form is displayed ? The
users frequently will leave this form open for long periods of time with
no activity (couple of hours).

Thanks a lot for any help/suggestions.

Darcey

"jy" <jy@directdialog.com> wrote:
>
>"darcey" <dbrown@bayridgelbr.com> wrote:
>>
>>Hi guys, hope you can help me out on this. Sorry if this is the wrong
group
>>to post to. I was not sure if I should post to here or the SQL group.
>I
>>did post to the SQL group as well but i have not had a response in a couple
>>of days so I thought maybe it was the wrong group. But my problem is that
>>when I am trying to connect to SQL Server I am getting the error:
>>
>>-2147467259 - [DBMSSOCN]General network error. Check your network documentation.
>>
>>
>>Now, I do not get the error on the same line of code all the time. It
can
>>be on a different line, in a different function, and I do not get it when
>>I step through the code, and sometimes it works perfectly. It generally
>>gives the error in one of the functions *after* I open a different form
>and
>>execute a stored procedure that returns records. I have several functions
>>that return values retrieved from SQL Server. These functions are located
>>at the module level. Here is a sample of what one of the functions looks
>>like:
>>
>>Function Get_Product_FBMCONV(ByVal pID As Long) As Double
>> On Error GoTo errH
>> Dim rs As ADODB.Recordset
>>
>> Set rs = New ADODB.Recordset
>> rs.CursorLocation = adUseClient
>> rs.Open "Select [FBMCONV] from INVENTORY_MASTER where [ID]
>>= " & pID, "Provider=SQLOLEDB;Server=TestServer;Database=BayridgeTest;
>> Trusted_Connection=No;UID=sa;PWD;", adOpenStatic,
adLockReadOnly,
>>adCmdText
>> Set rs.ActiveConnection = Nothing
>>
>> If rs.EOF Then Get_Product_FBMCONV = 0 Else Get_Product_FBMCONV = rs!FBMCONV
>> rs.Close
>> Set rs = Nothing
>>errH:
>> If Err <> 0 Then If ErrP("Get_Product_FBMCONV", Err) = 0 Then Resume
>>Next Else Resume
>>End Function
>>
>>I am a little baffled by the error because I am pretty sure there is not
>>a problem with the network because I can open any other form and establish
>>a connection with no problem.
>>
>>Any help/suggestions appreciated.
>>
>>Thanks,
>>
>>Darcey
>check your password declaration in the connection string; it should read
>pwd=<password>
>
>you need the '=' symbol even if the password is blank.
>
>hope this helps. j-y.

I've read this thread and it sounds to me like a timing issue. I think SQL
Server is probably trying to process each of your requests as soon as it
can as quickly as you ask for them but it's failing at some point.

You said something like, "if I step through the code, it works just fine."
That tells me that the code is fine. It's beyond that. It's got to have something
to do with the speed of the requests, the order of the requests, when SQL
Server can process them and what happens if it doesn't process them fast
enough.

By stalling the processing, even for a moment or two, when stepping the code
in debug mode, you're probably giving the SQL Server just enough time to
accomplish that which you've asked and therefore don't experience the error.

I know this flies in the face of everything you know and hold near and dear
to your heart as a professional VB programmer... but... I would suggest trying
a few DoEvents statements strategically placed in your code to give the system
time to process your requests. _IF_ that resolves the problem, at least you
know what you're up against and you may be able to approach the delivery
of your requests in a different sequence or... whatever, just to give the
system time to complete your requests. Then again, if it doesn't help, this
is a tough problem being "intermittent" and "sometimes", etc.

I've read this thread and it sounds to me like a timing issue. I think SQL
Server is probably trying to process each of your requests as soon as it
can as quickly as you ask for them but it's failing at some point.

You said something like, "if I step through the code, it works just fine."
That tells me that the code is fine. It's beyond that. It's got to have something
to do with the speed of the requests, the order of the requests, when SQL
Server can process them and what happens if it doesn't process them fast
enough.

By stalling the processing, even for a moment or two, when stepping the code
in debug mode, you're probably giving the SQL Server just enough time to
accomplish that which you've asked and therefore don't experience the error.

I know this will fly in the face of everything you know and hold near and
dear to your heart as a professional VB programmer... but... I would suggest
trying a few DoEvents statements strategically placed in your code to give
the system time to process your requests. _IF_ that resolves the problem,
at least you know what you're up against and you may be able to approach
the delivery of your requests in a different sequence or... whatever, just
to give the system time to complete your requests. Then again, if it doesn't
help, this is a tough problem being "intermittent" and "sometimes", etc.

Re: Error Connecting to SQL Server 7

Re: Error Connecting to SQL Server 7

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>Hi dbishop,
>
>Thanks a lot for the suggestion. I will give it a try.
>
>Darcey

Hi dbishop,

I tried your suggestion, but I am still receiving the error. The wierd thing
is that I have to open the one form and execute the stored procdure called
from the program and after that I get the error. If I close the form then
it works fine. If I open a different form that establishes a connection
it works fine. I will keep looking. Thanks for the suggestion.

I think if you release the object reference, then re-establish it, that may

get you the results you want. My reasoning is, you told me if you fire your

code from a DIFFERENT form, you're cool. That's probably because the command

object in the other form is local to that form and a completely different

object. What do you think? I'm hoping I'm helping and not hindering you.

By the way, I tried emailing you directly at your email address below and
I got a mail daemon. Is it correct? I tried because posting to this message
board takes FOREVER!!!! I click post and wait FOREVER!!!!

dbishop

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>"darcey" <dbrown@bayridgelbr.com> wrote:
>>
>>Hi dbishop,
>>
>>Thanks a lot for the suggestion. I will give it a try.
>>
>>Darcey
>
>Hi dbishop,
>
>I tried your suggestion, but I am still receiving the error. The wierd
thing
>is that I have to open the one form and execute the stored procdure called
>from the program and after that I get the error. If I close the form then
>it works fine. If I open a different form that establishes a connection
>it works fine. I will keep looking. Thanks for the suggestion.
>
>Any other suggestions greatly appreciated.
>
>Thanks,
>
>Darcey

Re: Error Connecting to SQL Server 7

Hi dbishop,

I agree with you that it must be a timing issue. I did watch my locals window
and I do have only one connection open at a time according to my app. I
also have been passing an open connection to some of the functions and subs,
which works great. I remember starting to set it up that way everywhere,
but I can not remember why I discarded that idea :-) whoops, maybe I should
have stuck with it. I do have about 400 subs and functions to set up then
though (I am migrating from Access 97 to SQL Server 7 - So I am modifying
a lot of code. Wish I had time to re-architect it, but my boss would never
allow that sort of time - "Just make it work" - Uuugh, I hate doing that
- they do not realize that just creates more problems in the long run, not
to mention they will not gain nearly all the performance benefits from SQL
Server) ;(

Anyway...

What I also noticed was that in SQL Server after running the sp_who stored
procedure was that SQL Server must not release connections right away even
though they are closed in the app. After opening that form and clicking
the button that executs these functions and subs, I would run that sp_who
procedure and there would be up tp 90 'sleeping' connections at one time.
Run the sp_who 30 sec later and there would be the normal few connections
that SQL Server has open for its own use. Now I have been reading a few
threads in the SQL Server group and they were talking about idle connections
and how they do not really take up resources on the server (that was the
general concensus anyway).

So i created a connections class, declared the class object global, created
an instance in my sub main procedure and leave the connection open during
the life of the app and dispose of the object when they exit the app. This
means one permanent connection per app during the life of the app. Guess
what, no error (as you probably know).

Now I was concerned that leaving a connection open all day during the life
of the app would degrade the performance of the server too much, and that
was why i was only going to open the connections as needed. But connecting
takes the most time and there are a lot of buttons and other controls that
the users can click often where the app needs to connect, so I do not think
that this design is very efficient in my case. I also thought about having
one connection per form type of design, but they can easily have 5 - 10 forms
open at once and that would be too many connecitions per workstation. But
there are only 25- 30 users in total using the app (And that will not grow
very quickly - probably will not add 10 in the next 2 - 3 years), so I am
sort of leaning towards one permanant connection per app design - this has
been influenced by our little discussion and the other thread I read in the
SQL Server group. I know passing the connection as you do would solve my
problem, but i still have to deal with the issue of always connecting when
an event occurs that needs data from the server (which is very often - they
do a lot of processing with the app, continually updating and retrieving
data). Some users though will not use the app for several hours, but will
have it open, which is why i was concerned about the permanent connection.

So, to sum it up, I do think the problem was a timing issue that was causes
collisions on the network. And with not very many users connecting to the
server at once I was going to change it to have one permanant connection
per app for the life of the app, since idle connections do not really impact
performace.

What do you think ?

Thanks a lot for all your help/suggestions/ideas. I am the only programmer/developer
here, so it is nice to discuss this with someone else.

Darcey

P.S. It is always nice to be the hero. Sorry about the e-mail and for being
so long winded (almost blue in the face). Our ISP was having problems.

Re: Error Connecting to SQL Server 7

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>
>Hi dbishop,
>
>I agree with you that it must be a timing issue. I did watch my locals
window
>and I do have only one connection open at a time according to my app. I
>also have been passing an open connection to some of the functions and subs,
>which works great. I remember starting to set it up that way everywhere,
>but I can not remember why I discarded that idea :-) whoops, maybe I should
>have stuck with it. I do have about 400 subs and functions to set up then
>though (I am migrating from Access 97 to SQL Server 7 - So I am modifying
>a lot of code. Wish I had time to re-architect it, but my boss would never
>allow that sort of time - "Just make it work" - Uuugh, I hate doing that
>- they do not realize that just creates more problems in the long run, not
>to mention they will not gain nearly all the performance benefits from SQL
>Server) ;(
>
>Anyway...
>
>What I also noticed was that in SQL Server after running the sp_who stored
>procedure was that SQL Server must not release connections right away even
>though they are closed in the app. After opening that form and clicking
>the button that executs these functions and subs, I would run that sp_who
>procedure and there would be up tp 90 'sleeping' connections at one time.
> Run the sp_who 30 sec later and there would be the normal few connections
>that SQL Server has open for its own use. Now I have been reading a few
>threads in the SQL Server group and they were talking about idle connections
>and how they do not really take up resources on the server (that was the
>general concensus anyway).
>
>So i created a connections class, declared the class object global, created
>an instance in my sub main procedure and leave the connection open during
>the life of the app and dispose of the object when they exit the app. This
>means one permanent connection per app during the life of the app. Guess
>what, no error (as you probably know).
>
>Now I was concerned that leaving a connection open all day during the life
>of the app would degrade the performance of the server too much, and that
>was why i was only going to open the connections as needed. But connecting
>takes the most time and there are a lot of buttons and other controls that
>the users can click often where the app needs to connect, so I do not think
>that this design is very efficient in my case. I also thought about having
>one connection per form type of design, but they can easily have 5 - 10
forms
>open at once and that would be too many connecitions per workstation. But
>there are only 25- 30 users in total using the app (And that will not grow
>very quickly - probably will not add 10 in the next 2 - 3 years), so I am
>sort of leaning towards one permanant connection per app design - this has
>been influenced by our little discussion and the other thread I read in
the
>SQL Server group. I know passing the connection as you do would solve my
>problem, but i still have to deal with the issue of always connecting when
>an event occurs that needs data from the server (which is very often - they
>do a lot of processing with the app, continually updating and retrieving
>data). Some users though will not use the app for several hours, but will
>have it open, which is why i was concerned about the permanent connection.
>
>So, to sum it up, I do think the problem was a timing issue that was causes
>collisions on the network. And with not very many users connecting to the
>server at once I was going to change it to have one permanant connection
>per app for the life of the app, since idle connections do not really impact
>performace.
>
>What do you think ?
>
>Thanks a lot for all your help/suggestions/ideas. I am the only programmer/developer
>here, so it is nice to discuss this with someone else.
>
>Darcey
>
>P.S. It is always nice to be the hero. Sorry about the e-mail and for being
>so long winded (almost blue in the face). Our ISP was having problems.

Hey, Darcey. Good to hear back from you. I haven't been on the board for

about 5 or 6 days -- been whacked here.

Anyway, IF you can allow people to remain connected (no timeout problems)

AND you don't have too many users, AND it's a closed system (not like Taco

Bell or JC Penny's or some huge-o-company which a go-jillion
users/connections) you're probably going to be okay with the one connection

per application lifespan design. If you run into timeout issues or lack of

resources due to open connections or anything like that, you're going to

have to change your strategy, I think.

On the other hand, if your environment (read "boss") demands that you "just

get it running", well then, shoot! Get it running! However possible! And

however you can without it crashing or erroring out on you! I mean, keep

that paycheck coming! But for sure keep what you've learned (me too!) from

this experience/discussion/trial and error series in your back pocket for

Re: Error Connecting to SQL Server 7

Hi Dave,
>
>Hey, Darcey. Good to hear back from you. I haven't been on the board for
>
>about 5 or 6 days -- been whacked here.
>
>Anyway, IF you can allow people to remain connected (no timeout problems)
>
>AND you don't have too many users, AND it's a closed system (not like Taco
>
>Bell or JC Penny's or some huge-o-company which a go-jillion
>users/connections) you're probably going to be okay with the one connection
>
>per application lifespan design. If you run into timeout issues or lack
of
>
>resources due to open connections or anything like that, you're going to
>
>have to change your strategy, I think.
>
>On the other hand, if your environment (read "boss") demands that you "just
>
>get it running", well then, shoot! Get it running! However possible! And
>
>however you can without it crashing or erroring out on you! I mean, keep
>
>that paycheck coming! But for sure keep what you've learned (me too!) from
>
>this experience/discussion/trial and error series in your back pocket for
>
>future pitfall avoidance, right?

You got it ! It definitely was a good learning experience and I definitely
will keep it in mind for future reference !