SQL Server selection

[TomasDill]

This is question about application installation on user computers and configuration of it. I will very appreciate help on this question, at least star guaranteed for good solution
I have SQL Server database that will work on servers of many clients. What is sad is that SQL Server names (it matches to machine name of server in network) are different for different clients. So, in client application I cannot specify SQL Server name exactly. I need som way to configure it. On the setup form for client application I have a field 'SQL Server Name'. What is bad is that user need to fill this field manually. It will be much more better if application will take all server names from network, determine which is accessable for current user, determine which contain database for my application and allow to choose such SQL Server in combobox.
Question:
How I can obtain list of SQL Servers installed all over the local network? I guess this is possible because most SQL Server tools do this, showing SQL Server name in combobox or list instead of typing in text box. Note that this should work on client computer where SQL Server driver only installed and no other SQL Server software.
Maybe, I should post this question to another forum? Who can tell me where I can find answer? [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[Xaplytz]

I Thing the problem is VFP connect to the SQL-Server via ODBC and by VFP, I didn’t find a function for create or set the OBDC, even the command “Modify Connection” doesn’t work in a EXE only in VFP environment, try look in MSDN library for VFP6 chapters 19 to 22 I guest, if you don’t find a function there, you have to try with VB6 or VC++6 to set the OBDC [sig][/sig]

 

[TomasDill]

No, VFP don;t have its own functions for that. Need to use API for most cases.
What I need is just to know API function for that. Anyone can point me to the help on ODBC/MS Server API? [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[foxdev]

Vlad, why not not refer to the servers by UNC name (such as \\ServerName), but instead use the server's IP address?

You'll probably have to use TCP/IP rather than Named Pipes for the protocol, but in my experience TCP/IP is faster anyway. I've always used TCP/IP, both with NT authentication and SQL Server authentication, and it works fine. I can connect to machines anywhere in the world without knowing what that machine is called within its domain. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TomasDill]

Robert, looks like you was not understood question.
Question is not how to connect. question is how to obtain list of all SQL Servers in local network... [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[foxdev]

I understood your question. My answer is: why do you need a list of the servers? If you know the server's IP address, its UNC name is unimportant. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TomasDill]

Because user that tries to work with client application don't know the IP address, and, possible, even don't know SQL Server name. For large network and many users it is quite a bit of problem to get them understand what is needed to install application (I meant need to know server name). They want 'plu&n&paly'.
That is what asked for and I try to figure out if it is possible in VFP.
[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[jonscott8]

Robert,
I think Vlad's wanting to present the server names to the user and allow the user to select which server to connect to. And presenting the user with an IP address is, more than likely, going to lose the user.

Vlad,
I don't use SQL Server, but I'd bet my money that the names are stored in the registry(if other programs are retrieving the server names).

Try exploring the following registry keys:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
&
HKEY_LOCAL_MACHINE\Software\ODBC

Whatever the result, I'd like to hear how you resolve this. [sig]<p>Jon Hawkins<br><a href=mailto: jonscott8@yahoo.com> jonscott8@yahoo.com</a><br><a href= > </a><br>Focus on the solution....Not the problem.[/sig]

 

[TomasDill]

Jon, I found it. Will try if it will work. Thanks!
'Try' I meant to investigate if these values could be found on client computers.
In addition, a little question - how they refreshed, for example, when new SQL Server appears in the network? I have a laptop with database, I connect it to network, than I want to configute client application to work with that database...

And presenting the user with an IP address is, more than likely, going to lose the user.[color]

You're more than right here )

[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[foxdev]

Your answer: sp_helpserver system stored procedure.
Downside: requires you first to connect to any one of the local servers.

You could also try the system stored procedure sp_linkedservers. This obviously will only show the servers that one of the servers knows about, and requires you first to connect to one of them. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[jonscott8]

Vlad,
I did some testing and here's what I found:

I configured two DSN's to connect to two different SQL Server databases. After this, there were two additional registry key values added to the following key:

HKEY_LOCAL_MACHINE
\Software\Microsoft\MSSQLServer\Client\ConnectTo

The values were 'Claims' & 'SpecCap' which were the names of the respective SQL Server databases I just connected to.

When I went to reconfigure the DSN's, in the 'Which SQL Server do you want to connect to?' combobox, I noticed both of my database names. I then deleted one of the registry values ('SpecCap') and went to reconfigure the DSN again. This time the 'SpecCap' database name did not appear in the combobox.

So, it appears that this is where the server names are being extracted. [sig]<p>Jon Hawkins<br><a href=mailto: jonscott8@yahoo.com> jonscott8@yahoo.com</a><br><a href= > </a><br>Focus on the solution....Not the problem.[/sig]

 

[TomasDill]

I found answer, finally. Thanks to all for suggestions.
See 'SQLBrowseConnect' function in MSDN. It allows to get list of SQL servers. [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[JDurbin]

This might be a good 'Tip' or something to look up quick. Not really a FAQ since it wouldn't frequently be needed. Anybody make their own Help with tips or tidbits according to category? Or things you always have to look up in a place you know where to look. [sig]<p>John Durbin<br><a href=mailto: john@johndurbin.com> john@johndurbin.com</a><br><a href=

http://www.johndurbin.com>

</a><br>MCP Visual FoxPro<br>
ICQ VFP ActiveList #73897253[/sig]