Total beginner can''t even connect with OSQL

Question

Total beginner can''t even connect with OSQL

Michael J. Corey

SSC Enthusiast

Points: 154
More actions
January 21, 2005 at 1:24 pm

#361920

Dear Experts,

Here is a question from a hyper-newbie. I’m trying to learn how to fit the whole SQL idea into my VB application program, which currently uses highly traditional random-access files (my skills are left-over from 25 years ago, before I became a biochemist). So I have downloaded thinks like MS Desktop Edition 2000 Release A of Microsoft SQL and installed it, yielding an instance with the following pathname:
C:\Program Files\Microsoft SQL Server\MSSQL$LEARNSQL

I have also downloaded instpubs.sql, among other things, so that I can learn with a sample database. As far as I know I have OLE DB, adodb.dll, an SDK, and everything else I need, although something could be missing or in the wrong place.

My immediate goal is to install the sample "pubs" database, but at this point I can't connect to any SQL server at all. When I type in osql -E to the command prompt, the reponse is

[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).

When I type osql -L, I get

(local)
CMSFP01
SERVER1

Could someone please kindly, kindly help?
Thanks in advance,
Michael Corey

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rhunt SSCertifiable Points: 5868 More actions · Answer 1

Michael, are you a local administrator on the machine you installed MSDE (desktop engine) on? By default, MSDE should allow administrators to connect with the "-E" option because of a SQL security group called "buitin\administrators".

This brings me to my next point. MSDE is absolutely a great product - probably one of the best free products I can think of. However, if you are truly trying to learn SQL, you should really consider SQL Personal edition or SQL Developer edition. I believe developer is approximately $50.00 but comes with SQL Enterprise Manager and SQL Query Analyzer - some visual tools that will DRAMATICALLY increase your comprehension of SQL server's cababilities.

Check out this link:

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0361.mspx

go to SQL server versions for special uses...

Good Luck! I think you will enjoy SQL server as much as VB!

Ryan Hunt

Michael J. Corey SSC Enthusiast Points: 154 More actions · Answer 2

Hi Ryan,

Thanks so much for the advice. That is certainly a reasonable price for a more powerful version.

Yes, I am an administrator on this machine, which makes the whole thing all the more mystifying.

Best,

Michael

rhunt SSCertifiable Points: 5868 More actions · Answer 3

RUN: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.exe

This is the SQL Server Network Utility. Enable Name Pipes and TCP/IP. Then try running OSQL -E at a command line.

However, please note that your workstation's SQL instance will be visible to others. Therefore, domain administrators would be able connect to your SQL Server.

Let me know if this works.

Ryan

Michael J. Corey SSC Enthusiast Points: 154 More actions · Answer 4

Well, Ryan, I hate to take up your day with this. Here's what happened. I successfully turned on Name Pipes and TCP/IP. Then I got a message saying that I would have to turn SQL services off and on again for the enablements to take effect. I tried to do that, but the manager listed only the two servers that were there before, and I was not allowed to connect to either of them. My instance was not there. Now I tried OSQL -E, but I got a DIFFERENT error message from before:

[Shared Memory]Invalid Connection.

[Shared Memory]ConnectionOpen (Invalid Instance).

So I restarted the machine, and NOW my instance (MJC\LEARNSQL) was there in the manager, and I was allowed to stop and start services. The manager responded "Starting services..." and then "Running services..." or whatever. But when I tried OSQL -E, I got the same "Invalid Connection" and "Invalid Instance" messages.

I wonder why my instance was not listed in the manager before the reboot.

Thanks again,

Michael

rhunt SSCertifiable Points: 5868 More actions · Answer 5

Micheal, instances can be tricky - they usually indicate that more than one set of SQL executables are running on your machine.

First - go back to the utility and make sure that your MCH\LEARNSQL instance is selected - and not your default instance (MCH). Make sure that Named Pipes and TCP/IP are enabled for your specific instance. Then restart services.

You can restart services by go to Start-->Settings-->Control Panel-->Administrative Tolols-->Services: MSSQLSERVER (or maybe MSSQLSERVER$LEARNSQL...I don't have an "instance" to experiment with) and right click on the service to select "RESTART".

Now try OSQL - E

If it still doesnt work, try: OSQL -S MCH\LEARNSQL -E

Let me know if that works.

Ryan

Michael J. Corey SSC Enthusiast Points: 154 More actions · Answer 6

Ryan,

Thanks to your latest advice, I made sure those items were enabled, restarted services, worked out my syntax and flags, and successfully created the pubs database, so I'm on my way to even worse problems. Thanks a million. For some reason I had to use your second suggestion (OSQL -S MJC\LEARNSQL -E), I guess because LEARNSQL is not the default.

Michael