Trouble Enumerating SQL Server Instances With SQL 2000/2005 On Network
Dec 4, 2007
Hi,
We have (after several weeks of testing in all kind of environments) send out a new version of our application to several of our customers. Within days problems where drippin in; After looking for the problem on various customer situations we found a problem which I think is rather disturbing and very odd. I'll describe the situation, on which we finally managed to recreate the problem, here.
In my problem I use the following configuration:
Windows 2003 (standard edition) AD network with 2 domain controllers, multiple Windows XP workstations, some without SQL instances, some with SQL 2000 instances, some with SQL 2005 instances and even one with SQL 7 running.
All run a 32 bit OS.
Tools to reproduce:
ListSQLSvr application (found on SQLDev.net) to enumerate the instances.
Problem description:
--------------------------------------------------
I am running the machine called DEV001, which has SQL 2000 (instancename DRUMIS) and SQL 7.0 (has no instancename so this is the root instance) installed.
In any 'normal' situation all the runnings SQL instances are visible on the network like this:
Notice that the browser service might be off on DEV002, you can still see the EXPRESS instance and a new root instance has appeared (though it doesn't exist!)??
After restarting the Browser service all is OK again.
When I turn on Hide Server in the SQL 2000 TCP/IP properties (or turn it on in the registry [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerDRUMISMSSQLServerSuperSocketNetLibTcp] "TcpHideFlag"=dword:00000001) on the DEV002 computer something real scary is happening..
The list looks as follows:
When someone has entered a database (for example the backoffice database on INSADBACKOFFICEEXACT) the list looks as follows (for a short moment; 5 secs or so):
Notice now that ALL instances are gone and no extended information is available. In the Query Analyser and in the SQL Management Studio when browsing you'll see this as well!
When someone is accessing a database instance it appears for a few seconds again.
Since our installation and applications rely on selecting a existing instance it will fail in the above situations (or at least not showing all available instances).
In my opinion this is a bug somewhere!
Note that even when the SQL Services are stopped on DEV002 (leaving the Browser service running) it still seems to block out ALL instance on the ENTIRE network!
I don't mind that one INSTANCE or even the entire MACHINE is hidden from the network, but ALL instances on ALL machines??
And the SQL Browser issue also worries me a bit since it does not stop the possibily to browse the SQL instances; it removes the SQL2000 instances but adds a root instance which doesn't even exist! Also the extended info is stripped.
Can anyone help me solve this/advise?
Also mind that in any situation there might run a lot of computers with a lot of SQL instances and I cannot tell our customers to find which machine has the SQL TCP/IP properties set to Hide...
It even seems that in some situations SBS 2003 does the hiding automatically on Install? And if so, when and why?
Regards,
Albert van Peppen
Senior System Engineer
Insad Grafisch b.v.
I have a problem enumerating SQL server instances on a 64 bits server W2K3 R2 SP2, running SQL 2000 sp4 (32 bits), SQL 2005 Express sp2 (32 bits) and SQL 2005 Developer sp2 (64 bits). I am using the same way as the sample (found on SQLDev.net) ListSQLSvr application to enumerate the instances.
In my case i have the following instances defined:
When i enumerate the SQL instances for the entire network like this:
ListSQLSvr -X All instances appear (including the instances from other machines in the network, as expected)
When i enumerate the SQL instances on my local machine like this: ListSQLSvr -S MYPC -X Only the 32 bits instances appear..
When compiling the ListSQLSvr application to target 64 bit, the result on the local macine shows only the 64 bits instances !?!?
I am using this enumerating code in my installer to fill a listbox and depending on what kind of SQL instance (32 or 64 bits) is selected by the user, I install the correct extended stored procedure and register accourdingly (note the difference between a 32 extended Proc and a 64 bit extended Proc). In this case I only need the local SQL instances since i want to install the correct extended proc.
Since the installer (MSI file) is one-for-all (32 and 64 bits OS's) it runs the code from a Custom Action DLL in 32 bits. Resulting in showing only the 32 bits instances. When I would use the 64 bit installer and make my CA DLL to target 64 bits, I would only see the 64 bits instances. But i would like to see and use all instances in my 32 bit installer and take the appropriate action to install the correct extended Proc.
I think it has to do with some internals of ODBC (SQLBrowseConnect() most likely); When using a local enumeration it uses Shared Memory and when enumerating the entire network (or any other machine) it uses TCP/IP? If this is true, is there a way to bypass this someway? Or if this isn't true, does anyone know any solution to the problem described?
Regards,
Albert van Peppen Senior System Engineer Insad Grafisch b.v.
I need to find out whether SQL Server client components are installed on a machine through my application.
For that I was expecting "SOFTWARE\Microsoft\Microsoft SQL Server\90" registry key. BUT If I install just native client drivers, this key is not getting created.
What could be another approch to determine whether system has required client components.
Also I need to list all the SQL Server 2005 servers available in the network. (Similar to the Servers combo box being populated while DSN creation in ODBC Admin tool). There are several approches.
1) Use NetServerEnum API 2) Use SQLBrowseConnect ODBC API 3) Use SQLDMO Object 4) OSQL utility.
We have 10+ MSDE 2000 installations on the same network. Each install has a named instance and the machines connect to eachother via VB application. We have a couple SQL 2000 Standard boxes and a SQL 2005 box all running on the same network with no issues. The problem we have recently run into is with a SQL Express box. When the box is on the network OSQL stops finding the MSDE 2000 named instances on the network and only the SQL Express named instance appears in the list. The second the SQL Express box is removed from the network the named instances are visible. I monitored the UDP traffic and suspect there is an issue with the response from SQL Express to OSQL. Can't find any issues for this problem only report I found is if MSDE and Express are on the same machine.
We have 10+ MSDE 2000 installations on the same network. Each install has a named instance and the machines connect to eachother via VB application. We have a couple SQL 2000 Standard boxes and a SQL 2005 box all running on the same network with no issues. The problem we have recently run into is with a SQL Express box. When the box is on the network OSQL stops finding the MSDE 2000 named instances on the network and only the SQL Express named instance appears in the list. The second the SQL Express box is removed from the network the named instances are visible. I monitored the UDP traffic and suspect there is an issue with the response from SQL Express to OSQL. Can't find any issues for this problem only report I found is if MSDE and Express are on the same machine.
I have a server with SQL Server2000 databases in it.. Now i would like to install SQL Server 2005 on the same server with out disturbing SQL Server 2000 databases
I need to create a seperate instance for SQL Server 2005..
is it possible to have both the instances on the same server.
Hi all,I just asked some people to help me out and phone microsoft with thefollowing information, kindly they refused unless we setup a supportcontract with them first, for pre-sales information. (That really doesnot sound like good business sense to me - anyway here is our problem,if anyone could help thanks)."To tell and ask microsoft:We will be setting up a microsoft sql server 2000 instance running on awindows 2003 server.1) We need to check this can run alongside a microsoft 2003 sql server(either workgroup or standard edition), on the same machine. Are thereany .dll clashes if we do this? If there are can we run SQL Server2000, in a virtual machine running windows 2000 professional. (I have alicenced copy we can use for this).2) If we run one instance of 2000, and one of 2003 of the sql servers,can one use the processor licence model, and one use the CAL licencemodel."Thanks for any help, and any idea why they actually force you to usenews groups for pre-sales information?David
I had a server with SQL Server 7.0 I installed a named instance of SQL Server 2000 and then i passed all my DB of the 7.0 instance to the 2000 instance. Then i removed the 7.0 instance, that was the default instance. So at the moment there is only the 2000 version, but it isn't the default instance Can the 2000 instance become the default instance? (So that clients can connect to it simply through computer name, and not creating an alias)
I am getting an error saying incorrect syntax near fIt works in SQL Server 2005, but I cannot get it to work in SQL Server 2000 The error appears to be in the section that I marked in Bold. CREATE PROCEDURE [dbo].[pe_getReport] -- Add the parameters for the stored procedure here @BranchID INT, @InvestorID INT, @Status INT, @QCAssigned INT, @LoanOfficer nvarChar(40), @FromCloseDate DateTime, @ToCloseDate DateTime, @OrderBy nvarChar(50)ASDECLARE @l_Sql NVarChar(4000), @l_OrderBy NVarChar(500), @l_OrderCol NVarChar(150), @l_CountSql NVarChar(4000), @l_Where NVarChar(4000), @l_SortDir nvarChar(4)BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET @l_Where = N' Where 1=1' IF (@BranchID IS NOT NULL) SET @l_Where = @l_Where + N' AND f.BranchID=' + CAST(@BranchID As NVarChar) IF (@Status IS NOT NULL) SET @l_Where = @l_Where + N' AND f.Status=' + CAST(@Status As NVarChar) IF (@InvestorID IS NOT NULL) SET @l_Where = @l_Where + N' AND f.InvestorID=' + CAST(@InvestorID As NVarChar) IF (@QCAssigned IS NOT NULL) SET @l_Where = @l_Where + N' AND f.QCAssigned=' + CAST(@QCAssigned As NVarChar) IF (@LoanOfficer IS NOT NULL) SET @l_Where = @l_Where + N' AND f.LoanOfficer LIKE ''' + @LoanOfficer + '%''' IF (@FromCloseDate IS NOT NULL) SET @l_Where = @l_Where + N' AND f.ClosingDate>=''' + CAST(@FromCloseDate AS NVarChar) + '''' IF (@ToCloseDate IS NOT NULL) SET @l_Where = @l_Where + N' AND f.ClosingDate<=''' + CAST(@ToCloseDate AS NVarChar) + '''' IF @OrderBy IS NULL SET @OrderBy = 'DateEntered DESC' SET @l_SortDir = SUBSTRING(@OrderBy, CHARINDEX(' ', @OrderBy) + 1, LEN(@OrderBy)) SET @l_OrderCol = SUBSTRING(@OrderBy, 1, NULLIF(CHARINDEX(' ', @OrderBy) - 1, -1)) IF @l_OrderCol = 'InvestorName' SET @l_OrderBy = 'i.InvestorName ' + @l_SortDir ELSE IF @l_OrderCol = 'BName' SET @l_OrderBy = 'b.BName ' + @l_SortDir ELSE IF @l_OrderCol = 'StatusDesc' SET @l_OrderBy = 's.StatusDesc ' + @l_SortDir ELSE IF @l_OrderCol = 'QCAssigned' SET @l_OrderBy = 'q.LoginName ' + @l_SortDir ELSE SET @l_OrderBy = 'f.' + @l_OrderCol + ' ' + @l_SortDir SET @l_CountSql = 'SELECT f.FundedID As FundedID FROM FundedInfo AS f LEFT OUTER JOIN Investors AS i ON f.InvestorID = i.InvestorID LEFT OUTER JOIN Branches AS b ON f.BranchID = b.BranchID LEFT OUTER JOIN Status AS s ON f.Status = s.StatusID LEFT OUTER JOIN QCLogins AS q f.QCAssigned = q.LoginID ' + @l_Where + ' ORDER BY ' + @l_OrderBy CREATE TABLE #RsltTable (ID int IDENTITY PRIMARY KEY, FundedID int) INSERT INTO #RsltTable(FundedID) EXECUTE (@l_CountSql)SELECT f.DateEntered As DateEntered, f.LastName As LastName, f.LoanNumber As LoanNumber, f.LoanOfficer As LoanOfficer, f.ClosingDate As ClosingDate, i.InvestorName As InvestorName, b.BName As BName, s.StatusDesc As StatusDesc, q.LoginName As LoginNameFROM FundedInfo AS f LEFT OUTER JOIN Investors AS i ON f.InvestorID = i.InvestorID LEFT OUTER JOIN Branches AS b ON f.BranchID = b.BranchID LEFT OUTER JOIN Status AS s ON f.Status = s.StatusID LEFT OUTER JOIN QCLogins As q ON f.QCAssigned = q.LoginID WHERE FundedID IN(SELECT FundedID FROM #rsltTable) ORDER BY CASE @OrderBy WHEN 'DateEntered ASC' THEN f.DateEntered END ASC, CASE @OrderBy WHEN 'DateEntered DESC' THEN f.DateEntered END DESC, CASE @OrderBy WHEN 'LastName ASC' THEN f.LastName END ASC, CASE @OrderBy WHEN 'LastName DESC' THEN f.LastName END DESC, CASE @OrderBy WHEN 'LoanNumber ASC' THEN f.LoanNumber END ASC, CASE @OrderBy WHEN 'LoanNumber DESC' THEN f.LoanNumber END DESC, CASE @OrderBy WHEN 'LoanOfficer ASC' THEN f.LoanOfficer END ASC, CASE @OrderBy WHEN 'LoanOfficer DESC' THEN f.LoanOfficer END DESC, CASE @OrderBy WHEN 'ClosingDate ASC' THEN f.ClosingDate END ASC, CASE @OrderBy WHEN 'ClosingDate DESC' THEN f.ClosingDate END DESC, CASE @OrderBy WHEN 'InvestorName ASC' THEN i.InvestorName END ASC, CASE @OrderBy WHEN 'InvestorName DESC' THEN i.InvestorName END DESC, CASE @OrderBy WHEN 'BName ASC' THEN b.BName END ASC, CASE @OrderBy WHEN 'BName DESC' THEN b.BName END DESC, CASE @OrderBy WHEN 'StatusDesc ASC' THEN s.StatusDesc END ASC, CASE @OrderBy WHEN 'StatusDesc DESC' THEN s.StatusDesc END DESC, CASE @OrderBy WHEN 'LoginName ASC' THEN q.LoginName END ASC, CASE @OrderBy WHEN 'LoginName DESC' THEN q.LoginName END DESCENDGO
We have an x86 sql 2000 server with 4GB of RAM, a quadcore Xeon, a RAID 5 drive C, and its utilization is generally low. Typical perfmon counters: CPU < 5%; available Mbytes, 1800; typical disk time 5-10 %; Committed bytes in use < 25%, pagessec near 0.0.
For various reasons I need to install a sql 2005 instance on the same box and both instances will be up and running at the same time.
How, using Perfmon (?), can I determine the max amount of memory to assign to each instance? (It is my understanding that I definitiely need to limit the amount of RAM used by each instance.)
I have a cluster with 4 sql server 2000 instances and would like to upgrade 2 of these to sql server 2005 - is there any document or list of things to ensure I do
We currently have multiple instances of MS Sql 2000 and MS SQL 2005 installed on servers. When at other locations that uses different subnets only the default instance is available, published, broadcasted, selectable.
We have TCPIP and name pipes enabled for all instances. This seems to be a common problem for all locations.
We have a SQL2000 database (Publisher) replicating inserts and updates across a 10Mb link to a SQL 2005 database (Subscriber). The Publisher has two tables we are interested in, 1 with 50 columns and 1 with 15. Both tables have 6 insert/update triggers that fire when a change is made to update columns on the publisher database. We have set up a pull transactional replication from the Subscriber to occur against the Publisher every minute. We have limited the subscription/replication configuration to Publsih 6 columns from table 1 and 4 from table 2. Any change occuring on any other columns in the Publisher are of no interest. The SQL 2005 database has a trigger on table 1 and table 2 to insert values into a third table. There are around 7,000 insert/updates on table 1 and 28,000 on table 2 per day. All fields in the tables are text. We are seeing "excessive" network traffic occuring of approximately 1MB per minute (approx 2GB per 24 hrs). We also see that the Distributor databases are getting very large -- upto around 30GB and growing until they get culled. We have reduced the culling intrval from 72 hrs to 24 hours to reduce the size. Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?
I need any information on whether its possible to create more than 16 instances of SQL Server 2000. I know Microsoft says they don't support over 16 but what are the downfalls of doing this? How many could possibly be put on one server?
We have a new failover cluster (Windows 2003 SP1, Microsoft SQL 2000 SP4) with each node of the cluster hosting 7 SQL Server instances in a 2-node active-active configuration connected to a SAN. We are planning to move some SQL Server Instances(from existing stand-alone servers) into this Cluster. Any insight into the process of moving SQL Servers into the cluster would be highly appreciated.
How to get the list of instance of SQL Server 7.0/2000 running on the local machine inside my domain... I need to prepare the list of all sql instances.. pls help if possible to find details using sql query.
I fully understand that to connect to a named instance of SQL Server you need to use the ServerNameSQLInstanceName. The problem I have is that I have a SQL Server in a different zone. I can connect to the Default instance by IP Address or the ServerName.zone.domain.org. (e.g. MySQLServer.zone1.mydomain.org).
However, the same thing does not work for the Named Instance. It seems to be named instance or nothing.
How can I connect to this named instance across network zones?:S
We are having all kinds of issues with named instances for SQL 2000.
I am trying to connect to a SQL Server 2000 named instance on a different subnet and get an error. I cannot connect with ODBC or our web app.
I am using the port number for the alias that I created in the SQL Client Utility. We can connect to default instances without a problem, but not the named instances.
The SQL Server is 2000 build 2040 (Service pack 4 with a hot fix.) The server is listening on port 1223. In the ODBC connection I click on the Network Config and create an alias with the named instance such as SQLVSNSQLNI and specify port 1223. I have also tried adding the port to the connection string in the ASP include file (SQLVSNSQLNI,1223). If I do the same thing with a default instance on the network, both the app and ODBC work fine. It is only when I use a named instance.
Very frustrated. Thanks for any help you can provide
When trying to install SP4, I do not receive any notification of the install i.e. click on setup and then nothing. Checked *.out log and receive the following:
2006-08-08 10:40:57 - ? [100] Microsoft SQLServerAgent version 8.00.194 (x86 unicode retail build) : Process ID 3616 2006-08-08 10:40:57 - ? [101] SQL Server 1LTZ0Q version 8.00.194 (0 connection limit) 2006-08-08 10:40:57 - ? [102] SQL Server ODBC driver version 3.81.9031 2006-08-08 10:40:57 - ? [103] NetLib being used by driver is DBMSSHRN.DLL; Local host server is (local) 2006-08-08 10:40:57 - ? [310] 2 processor(s) and 1016 MB RAM detected 2006-08-08 10:40:57 - ? [339] Local computer is 1LTZ0Q running Windows NT 5.0 (2195) Service Pack 4 2006-08-08 10:40:57 - ? [129] SQLSERVERAGENT starting under Windows NT service control 2006-08-08 10:40:57 - + [260] Unable to start mail session (reason: No mail profile defined) 2006-08-08 10:40:57 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
Novice - Where to set up the mail profile and CPU idle condition?
I have set up a Database Maintenance Plan that does a nightly backup of all of my databases (about 12 of them) to a network folder. The plan works for about 95% of the job, but most nights there will be at least 1 database which will fail with the following error... BackupDiskFile::RequestDurableMedia: failure on backup device '\myfileserverBackupSQLDatabaseDatabaseNameD atabaseName_db_200610081749.BAK'. Operating system error 64(The specified network name is no longer available.). I know that this is not a permission or storage problem, because it works for most of the job. And a database that fails one night, may work fine the next night only to have a DIFFERENT database fail that night, and sometimes all databases work 100%.
Is there a way to fix this problem? And if not, is there a way to be notified of which specific database in the maintenance plan is failing. The message on the job itsself is very non-descriptive and I have to manually search the logs to find out which databases were successful & which where not. It is very time consuming. Any help with this would be greatly appreciated.
I am working on a site's SQL Server 2000 database on a W2k3 machine . I went into Enterprise Manager and saw that their database resides on a named instance. I did not see the default instance listed so I registered that using windows authentication. I noticed that the default instance had a user database that had the same name as the user database on the named instance that I was to work on. I looked at the properties of the databases and saw that on both the default and named instances of SQL Server that the Data Files and Log Files for the user database point to the same location.
Is this a problem? Can anyone see any issues with this? Does this mean that someone can simply connect to the named or the default instance of the SQL Server and connect to the same database?
When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
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
Hi, i am trying to get to my hosting companies database server. Its SQL Sever 2000, but i want to connect through SQL Server Management Studio 2005. The connection is unable to be established, although i have tried all sorts of settings for the connection. I called the hosting company and they told me to connect using the IP address of the name server for my domain. It failed and i called again to check back, and one of the guy told me that u cannot connect to an instance of sql servr 2000 from sql server 2005. Is this true? Please help.
My application is running fine, as i can login to it and also able to view pages. But when i open Order management(having 3K records) its give me error,
General Network Error. Check your network documentation
I have also searched many articles and tried following solutions but nothing working
- connectiontimeout = 0, max pool size = 7500/100, pooling = false
- SSL disabling enforce security false as mentioned in microsoft kb article.
And there's nothing any issue with hardware/firewall as my application's login and other forms are working fine(which use same database with same connection string)
Hello. Is it possible on a server with 2 instances of SQL 2005 running, to have EACH instance get up to 2.8 GB of RAM. I've done that on single instance machines, just not sure about multi-instances, if you can use the same settings to get 2.8 GB each, assuming the overall memory is at least 6GB... Thanks, Bruce
I have 2x sql 2005 servers at home at different machines. I'm logging on both machines by using windows authentication. How can I make a linked server between them?
I just installed sql server 2005 and trying to pick it up before I start a new job as a developer using sql server 2005. The problem is that I have three instances installed, the one that works was installed prior to installing sql server 2005 when I installed System Architect a CASE tool which utilizes sql server for its encyclopedias.
My initial installed I used the default settings with the default instance and that does not work. I later ran set up again and installed another instance and that does not work. For some apparent reason the POKIN10SQL instance is over riding everything rendering every other instance non-functional.
When I try to connect to the one of the other instances, the error message is
"An error has occured while establishing a connection to the server. When connection to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL does not allow remote connections. (Provider Network Interface, error: 26 - Error locating Server/Instance Specified) (Microsoft SQL Server)"
I don't think the remote connection is the problem as I went into the properties settings and checked the connection settings and allow remote connections is checked.
In terms of locating the instance, I installed SQL Server as specified in the handbook ... with the default instance and then a named instance.
Something seems to be wrong with the POKIN10SQL instance which was installed with System Architect, I need System Architect so I need a work around rather then an uninstall.
