ODBC - can see out, but can't see in 6

[Will192]

Windows 2000 Advanced Server (latest service pack)
SQL Server 2000 Enterprise Edition (8.00.2187)

1. Server can see other machines in network through Windows Explorer
2. Server can connect to other machines in network through ODBC
3. Other machine can't connect to my server through ODBC
4. Other machine can browse shared folders on server through Windows Explorer
5. Worked fine yesterday, ODBC went out sometime before midnight last night
6. No events pointing to problems in the event log.
7. Problem is fixed with reboot.
8. Automatic updates is turned off
9. All updates were downloaded and installed about 1 1/12 weeks ago.
10. Server can be seen through Enterprise manager on server
11. Query analyzer cannot connect from remote machine
12. Query analyzer can connect if ran from server

What should I be looking for to make sure that this doesn't happen again? (I know that there has been more updates since then)

 

[AlexCuse]

Will192 -

I see you have posted quite a few questions, and not once have you thanked anyone for their advice. Is this because you have not found any of the advice you have gotten helpful?

Anyways, to aid you in your quest to get an answer, here is some information that might be helpful (I am not a DBA,so I'm sure I'll miss a few)

1. What SQL service pack level do you have (I am not familiar with Enterprise edition's versioning but it seems low)?
2. Where are you connecting from via ODBC (I imagine it is some kind of application, the type of application and what it is written in would be helpful)


A couple general suggestions - if this is an application that runs all the time, I would take a look at using OLEDB connections, and trying to work with recordsets in your app as much as possible (rather than doing your work directly in the database accross your ODBC connection). I have heard that a network hiccup will sometimes interrupt an ODBC connection, and the application will need to be restarted to re-establish the connection. If you open and close the connection when you need it, then you can set up your app to attempt to connect a few times, and if it still can't connect then display some kind of error.

Added benefits of this will be better performance, less issues due to locked tables, and less strain on your server.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Will192]

(I would have posted this yesterday, but it seems that my account had been flagged)

2187 is a hotfix for the latest service pack (SP4) for SQL Server 2000.

I got the same results from trying to setup a connection in the ODBC manager in Admin Tools.

The connection is opened and closed when needed. The application pulls from another system, massages the data, and then uploads to SQL Server.

I assume that you are referring to 'giving stars'. I was not aware of this function until a coworker just pointed this out to me. I will go back into old posts and give them out where appropriate. Thank you for bringing this up to me, although it could have been brought up in a better way.

If you would have actually read some of my posts before accusing me of being ungrateful for help you would have seen that I usually post something like 'thanks in advance' or thank someone when they have helped me. I pulled up 8 random threads and I had 'verbally' thanked people on at least 6 of the threads, multiple times in the thread. I will not thank someone if they ask me to try stuff that I have already posted that I have tried.

Wouldn't it have been more courteous to ask me if I knew about the stars instead of accusing me first of being ungrateful? I don't read every single word of every single page that I visit, so I missed the small 5pt text at the bottom of the post that referred to the stars. Thanks for getting me locked out of the site so that I could not respond to your post.

I have tried to read posts to solve others problems, but they seem to get solved by others before I can post my thoughts. I tend to not post unless I am going to add significantlly to the discussion. I know how irritating it can be when someone posts an 'I agree' type message or a message that is off subject. (driveby posting as I call it)

Just out of curiosity, what would working with recordsets have anything to do with my connectivity issues? If I can't connect, then it doesn't matter what I'm trying to do after I connect?

You have really made me feel like you took 2 sec to look at my problem, 2 sec to look at my history, then decided that you were going to preach forum etiquite to me and give me a lesson on how to communicate with my server through my apps without even knowing how my applications are written. This situation could have came to a much better end if you would have just taken a moment to reread your post before submitting.

 

[ptheriault]

Will,
Have you tried changing your ODBC connection type to named pipes when ODBC fails? By default ODBC uses TCP/IP. You can change the connection type in the advanced section of the ODBC connection manager. If named pipes works and TCP doesn't your problem may be a known issues with SQL Server. You would have to contact MS to get the hot fix. Did you check the SQL logs as well as the event view logs?

Also,
I think Alex was just trying to be helpful and I didn't take his post as "preaching forum etiquite".




- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Will192]

ptheriault -

Nothing in the SQL Server log.

I will look into the ODBC settings that you suggested.

come one, you gotta agree with me that AlexCuse could have went about it a little more politely, instead of accusing me.

By the way, I have been going through my old posts and already have given out around 30 stars. (with a couple going to AlexCuse I think)

 

[Will192]

I have finished going through my old posts and have given out 42 stars.

 

[AlexCuse]

Will - I am sorry that you read an accusatory tone into my post, but it was certainly not intended. I only wanted to point you in the direction of the link. I imagine that your stars will be enjoyed by their recipients though. I also imagine that seeing that you have given people a few is going to help you get more prompt replies

(I am a pretty frequent poster, and I check that before replying to some time-consuming questions. It may not always be right, but it is a quick and easy way to check if someone appreciates the help they have been given. Why spend a lot of time explaining something to someone who has not been grateful for help they received in the past, and all that)

And I did not (to the best of my knowledge) get you locked out of the site. I red flagged your duplicate post in the DTS forum for deletion (fyi - this is how you could have asked for it to be deleted in a way that would be noticed immediately). It was actually seeing your post to please delete that made me think maybe you had not noticed the row of links on the bottom there.

as far as your real question - Without knowing what your app does or how it is written, it is very hard to tell what your best course of action would be. So I thought that some general advice may be helpful. ODBC tends to be very slow, and I only use it as a last resort.

Regardless, do you attempt to connect multiple times, or only once when your app starts? If it was a network hiccup, then attempting a few times could be helpful (if you are not doing this already). It could have been a network issue, or it could be what ptheriault suggests (as I said before, I am not a DBA so I only dabble in things like this). Have you tried changing the connection type to named pipes as Paul suggests?

Sorry about the confusion ,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Will192]

We do try to reconnect, 3 times, all failed. I don't think the problem is just ODBC. I found out that the nightly FTPs that we have coming in also failed. Everything coming in failed, everything going out was fine. In my opinion that means that someone is tweaking a firewall, router or something outside my server and I didn't get the changes applied till I rebooted.

No offense taken, now. I've had a lot of drive-by posts on other forums and I'm a little burned by it. You did not make any reference to a link, you just said that I didn't thank anyone for their help. I have thanked people many times, just not using the stars. Had you made a direct reference to the link, I would have thanked you and given you a star for pointing it out to me.

From what I can tell, your Red-Flagging my other post got me locked out. It may have been some kind of glitch, but it doesn't seem like it. I emailed the admin yesterday and I guess he let me back in sometime this morning. I don't know, I never heard anything back from him. I mostly put the post up to make sure that no one posted replies. I don't think red-flagging one post should lock you out of the site.(if that's actually what locked me out)

I would like to post more, but I just don't have the time at work. I spend too much time working from home to want to lengthen my time spent in the office. If I have a few minutes here or there, I might try and help. But like I said before, usually someone else has already posted my suggestion.

 

[SQLBill]

Two things that I check when ODBC connections go bad and the obvious seems to be ok.

1. Is it using TCP/IP for the connection or Named Pipes? I have seen this setting mysteriously change.

2. Is it using the correct port? If you are not using the default port for SQL Server, this setting may be incorrect. I usually test by changing from Dynamic to a specified port and see if that works.

-SQLBill

Posting advice: FAQ481-4875

 

[Will192]

The connection is only using TCP/IP.
The port sql server is using is the default.(I will double check though)

 

[Will192]

SQLBill -

Ok, I admit that I missed the whole give-a-star-link thing. This site's pages aren't setup very well for the infreguent poster like myself. When I just glance at this forum it seems to have a million little 5pt text links in multiple colors, in different fonts all over the place. Not the exact kind of thing that you want when you are trying to make an intuitive application. This is why I didn't see the whole, give stars as thanks thing. My bad, I have went through all of my old posts and given out stars to make up for missing this point.

Thank you for pointing me to the FAQ. Just out of curiosity, how would I have ever seen this FAQ? There is no link to FAQs on the homepage of this site. The main page has no link to the FAQ! That link that you gave me was actually in the VERITAS: Backup Exec back-up FAQ forum. How would I have ever seen that link? If it's an important link that everyone should be reading, then put a link to it on the front page.

(warning - sort-of-off-subject-rant) In my opinion, most web-applications are un-intuitive. It seems to me that the main theme for most web apps that I see on the internet is - make it pretty, then worry about the flow later. The whole concept of common-look-and-feel is either not remotely understood or just blatantly thrown out the window. I come from a client-server background where your rapid development depended heavily on foundation classes that nearly forced all of your applications to look and work the same, thereby greatly reducing the learning curve for users.

I don't mean to rant and I am grateful for all of the insight that you guys have given me. I guess I would just rather have an ugly application that works well, then a pretty one that is confusing to the casual user. Just ranting and just my opinion.

(for someone who never posts, I seem to be long winded and getting of subject rather easily in this thread. Maybe I should red-flag myself!)

 

[gmmastros]

Will,

I suspect that the link that SQLBill posted is part of his signature. It was probably not meant specifically for you. Most new forum members do not understand the rules of the forum, or even the proper etiquette to use when posting. While you are an admitted "infrequent poster", nobody will ever more mistake you for a newbie.

In Alex's original post, he put 2 sentences in there regarding proper forum usage. You read it, understood it, and applied the advice that Alex gave you. Enough said.

By the way... I have a quote from a moron in my signature. The moron is actually one of my customers, not you. Please do not think that it is directed towards you.

-George

"the screen with the little boxes in the window." - Moron

 

[Will192]

The is my favorite conversation with a Win95 user:
(I have actually had this same conversation many times, honest!)

Me : You are running Windows 95, right?
User : Right
Me : Do you see the Start button in the lower left hand of your screen?
User : (very quick response) No
Me : Look at the LOWER LEFT corner of your screen, is there a button that says Start?
User : (very quick response) No
Me : On the lower left hand side of the screen, is there a button?
User : Yes
Me : Good, what does it say
User : Start. Oh there it is!

(Where were they looking before?????)

Keep in mind that these are users with locked down desktops with nonhideable taskbars. They are also users that spend their day sitting behind computers. THEY COULDN'T FIND THE START BUTTON, I AM NOT MAKING THIS UP. Multiple occasions, different sites, honestly. I wouldn't have believed it if I wouldn't have happened to me.

 

[kaht]

Will, you can pretty much assume that anything that comes after a person's name in their post is part of their signature. I think that's pretty common across most message boards

And no, I did not put this homer simpson quote out there specifically for you

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[monksnake]

It seems to me that the main theme for most web apps that I see on the internet is - make it pretty, then worry about the flow later.

I've seen applications like that. There's one web application I saw that anytime you wanted to go to a different page within the application, you had to return to a main menu page first.


<.

 

[kaht]

Or any time you refresh the page or hit the back button you had to log back in

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[Will192]

monksanke & kaht - Bite me. You two obviously don't know what the heck you two are talking about. Don't post unless it is something constructive. You guys seem like trouble makers to me.


(I work with kaht and monksnake, it's meant to be a joke)


Yes, I understand what a signature at the bottom of a post is. I went back and read some of SQLBill's old posts and it seems that his signature over the past couple of months includes that link. I originally thought that it was intended for this thread because it was directly related to the subject matter.

 

[Will192]

I just noticed that I can go into a thread that someone else started and thank someone else for a thread that I didn't post in or start.

What's to stop me from creating two accounts and going into all of my old posts and thank myself for everything I've ever posted?

 

[kaht]

Nothing, but if they get caught they'll get booted. Management can see who's giving the stars to who - so if you're not giving quality comments and you're getting a lot of stars from the same user it'll be pretty obvious.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[Will192]

So wouldn't the logical thing to do would be to only allow stars to be given out by the originator of the thread or by someone who has posted in the thread? Just a observation.

I won't critique this site any further. It seems to be a good site and I don't come here often enough or have used the site enough to form a valid opinion.