Hi. I would like to enumerate all the databases in a given Sql Server instance.
I've searched and found no information. Would somebody post a piece of VB code to do that?
Thanks in advance
Rafael
You can do it with an SQL string:
use masterselect *from sys.databases|||
SELECT
*FROM
master.sys.databases
Should work, but there is probably a more standard way.
|||There is also a stored procedure called:sp_databasesI am using EnumAvailableSqlServer to build the standard select Sql Server dialog with controls to select the server, instance and database. This all works fine - other than the well reported problem of detected multiple instances - however I have noticed that when disconnected from the network the call fails to even detect the locally installed sql servers.
What is the recommended method of determine whether an instance of sql server is installed locally. I can call Server->Databases, but this leads to a timeout / excpetion if no instances are detected which is slow. Is there a recommended registry key I can check that won't change between versions or another API I can try.
hx, Nick
Checking registry seems the simplest ...
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
with InstalledInstances value.
Thx, Nick
I am using EnumAvailableSqlServer to build the standard select Sql Server dialog with controls to select the server, instance and database. This all works fine - other than the well reported problem of detected multiple instances - however I have noticed that when disconnected from the network the call fails to even detect the locally installed sql servers.
What is the recommended method of determine whether an instance of sql server is installed locally. I can call Server->Databases, but this leads to a timeout / excpetion if no instances are detected which is slow. Is there a recommended registry key I can check that won't change between versions or another API I can try.
hx, Nick
Checking registry seems the simplest ...
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
with InstalledInstances value.
Thx, Nick
I implemented a merge replication publication on a SQL Server 2005
Enterprise instance on a test server. On my laptop I have VS.NET 2005
installed with SQL Server 2005 Express.
It's my understanding that I can subscribe to a publication with
Express. I also have SQL Server Workgroup installed on my laptop so I'm
able to use Management Studio to control the Express instance.
The first issue I encountered was that you cannot subscribe to a
publication if you are not on the same network as the publishing
instance (it requires the actual server name). And in lieu of this I
tried to implement Web synchronization, but had problems with SSL
(which is my problem since I'm not entirely familiar with how to
implement SSL certs so they don't show the security warning on the
client without purchasing a certificate).
So then I decided to go ahead and create an account on my laptop and
add it to the domain of the test publishing server. I was then able to
subscribe to the published replication.
So then I look at my Express instance and the synchronization never
runs. In the documentation it says that you're supposed to be able to
right-click on the subscription and "View Synchronization" and force it
to run. This is not available in the Express instance, but it is
available in the Workgroup instance.
So I'm looking at cleaning up my web synchronization issues to make
that work, but I see two issues here.
1) Why do I have to specify the "real name" of the publishing database?
Why can't I access it like any other sql server via an IP address? This
seems like a highly restrictive implementation of replication to me.
2) Why do I have to implement SSL for replication? I understand the
need for security, but that should be left to me to implement. I
shouldn't be forced to use SSL as my security layer.
3) Why doesn't Express have "View Synchronization"?
Any help here is greatly appreciated.
David Cornelson
Hi David,Regarding your questions:
1) Not sure what you mean here. You need to have the actual and real publishing database name. Without it, we cannot know which database the subscription is referring to.
2) SSL is required for Web Synchronization. It is for Security Purposes. A security feature cannot be optional.
3) SQL Server Express does not have SQL Server Agent. Hence you do not have 'View Synchronization' (Workgroup has it and hence you see the option). You need to either use RMO or ActiveX sync components or use the Window Sync Manager to synchronizing.|||
re 1) So why can't I direct a subscriber to ipaddress\instanceName? Why does it have to be serverName\instanceName?
2) If all of my replication is done within my own company, say I have Smart Clients that are on different segments of my network, so they can't actually "see" the publishing database, I might want to use web synchronization and _not_ use SSL. You're making a security decision that I don't need. I understand that this should be default behaviour, but I should be able to turn it off. You're actually making my architecture more complex by forcing SSL into my replication strategy.
3) Where can I find information on Windows Sync Manager?
Thanks,
David C.
|||1) Not sure, but looks like there is no option for you to provide an ipaddress of the publisher to connect to. Servername/instance may be the only way.2) Security is always a big concern and that is the reason for mandating the SSL. If you wish, you could workaround with a test certificate. MSDN has a tool for installing a test certificate.
For IIS6.0: http://www.microsoft.com/downloads/details.aspx?FamilyID=56fc92ee-a71a-4c73-b628-ade629c89499&displaylang=en
For IIS5.0: http://support.microsoft.com/default.aspx?scid=kb;en-us;228984
3) Windows Sync manager comes with Standard windows installation. Books online has more information about synchronizing using Sync Manager. To open Sync Manager follow either of the steps:
a) Windows Explorer-->Tools-->Synchronize
b) Start-->AllPrograms-->Accessories-->Synchronize|||1) Okay
2) Alright
3) It only has a Sql Server 2000 and an IE sync listed. Where's 2005?
Thanks for the help.
David C.|||3) I dont understand what you are saying. Where do you see SQL Server 2000 listed?
Windows sync manager is a windows mechanism for synchronization which can be launched using the 2 methods I described in my previous post. You have to enable the subscription on the Subscriber to be using the Windows Sync Manager. Once you have done that, you will start seeing the subscription in the Sync Manager and you can synchronize the subscription with the publication at the Publisher.
I have both SQL2000 (default) and SQL2005 (second instance) on my laptop. Just to see if it could be done, I registered my (Local) SQL2000 default instance in the Registered Servers part of SQL2005 Mgmt Studio. It worked fine. I can see all the SQL2000 databases, etc in the Mgmt Studio.
But now the SQL2000 default instance will not connect in Enterprise Mgr nor can any of the programs that used to connect to the SQL2000 databases now connect to them. The (local) instance shows in Ent Mgr with the green arrow like it is there but Ent Mgr cannot connect when icon is clicked.
Is there anything I can do to bring the default SQL2000 instance back to be able to connect with anything other than the SQL2005 Mgmt Studio?
Do you get an error ? Which one ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
The local node shows in the tree with a white circle with a green arrowhead inside the circle. Which indicates to me that the SQL instance is running and the Ent Mgr sees it. But when I click on the plus beside the node, I get the error:
A connection could not be established to (LOCAL)
Reason: SQL Server does not exist or access denied
ConnectionOpen(Connect())....
Please verify SQL Server is running and check your SQLServer registration properties (by right-clicking on the (LOCAL) node) and try again.
But I can go to the SQL2005 Mgmt Studio and click on (LOCAL) and see all the stuff in it. In Mgmt Studio, the properties for (LOCAL) show version 8.x of SQLServer.
I am trying to install the SQL Server 2005 Enterprise Edition, but on the components selection page, the only options I can check are Sample Book/Docs and Tools(Business Intelligence Studio, SQL Management Studio).
Even on the advanced page, those are the only options. Everything runs with no problem, except the SQL server instance.
I checked on the SQL Server Configuration Manager, and it does not show any SQL instances installed.
I tried adding a new instance using the add/remove>change for Microsoft SQL Server 2005 by selecting the setup.exe file in the Enterprise CD, but the it has the same options. If I select the setup.exe file from my Workgroup Edition CD, the option for adding new instances works.
What should I Do?
Cant I add SQL server instances using the Enterprise Edition? I was unable to install even the first instance.
The computer I am using meets all requirements and no SQL versions have been installed yet.
Thanks for the help.
go to the tools and servers folders respectively and install the components individually,.....dunno if it works just a surmise.....also you can refer this link might be useful........
http://dumthata.com/sqlchn/articles/install/insql.htm
|||Enterprise Edition REQUIRES a server OS.
As you can see from the chart on this page, it does NOT install on Windows XP or VISTA.
