Determing All Instances Defined On A Server
Mar 24, 2004
I'm in the process of writing some utility scripts which will
interface SQL2K to our backup storage system, Tivoli. In our shop we
"fire" the backup process from the Tivoli storage server by invoking a
command script on the client server. The issue I've run into is that
some of our servers were installed with Named Instances, either
instead of or in addition to the default instance.
Rather than hard coding these values in the scripts I'm looking for a
way to determine all the instance names on a server. The scripts
could then be generic without custom tailoring for current and future
servers (and instances).
Anyone have a solution?
Thanks,
Fred Prose - Arizona Supreme Court
View 2 Replies
ADVERTISEMENT
May 12, 2008
Sort of new to MSSQL and I have a problem with my maintenance script not deleting files on 2 out of 3 servers so I am trying to determine what patch level I am at.
'Server A' works as expected:
Microsoft SQL Server 2005 - 9.00.2050.00 (Intel X86)
Feb 13 2007 23:02:48
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
These two servers don't:
Server B.
Microsoft SQL Server 2005 - 9.00.2050.00 (Intel X86)
Feb 13 2007 23:02:48
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Server C.
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
So I can see where I should patch server B to SP2 but why does Server C report as 9.00.1399 SP2?
View 5 Replies
View Related
Sep 28, 2007
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.
View 4 Replies
View Related
Jul 18, 2001
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)
thanks
Fede
View 1 Replies
View Related
May 26, 2007
I want to determine name of week day using sql query. I used following:
Select datepart(weekday,mydate) from tablename;
This query returns day number but I want result as: Monday, Tuesday .....
View 1 Replies
View Related
Nov 16, 2005
I am trying to set the WHERE clause in an SQL statement at runtime using a case statement like so
SELECT *
FROM table
WHERE CASE @BrowseBy
WHEN 'Supplier' THEN
Supplier = @Value
WHEN
'PriceRange' THEN Price > 1000
WHEN
'BladeSpan' THEN BladeSpan = @Value
WHEN
'MotorFinish' THEN FinishID = @Value
WHEN
'BladeFinish' THEN BladeFinish1 = @Value or BladeFinish2 = @Value
END
the above code gives me a syntax error... does anyone know of another way to accomplish this?
Thanx
View 5 Replies
View Related
Jan 5, 2006
All of my tables in my database have keys that are autonumbered (datatype int with identity set to 1).
Whenever I go to insert a new entry into the table I execute an INSERT INTO command and leave off the key field, so that it is automatically inserted with the new row.
However, I need to that number, so that I may insert it as foreign key into another table. How would I go about retrieving this number? I thought about doing a Max() on that field, but I am not confident that SQL server would always use a higher number than everything previous. Is there a better way of accomplishing this? Is my design flawed from the start? Any feedback would helpful. Thank you.
View 5 Replies
View Related
Apr 2, 2008
hai,
how can i identify the function is user defined or the system defined function....................
View 1 Replies
View Related
May 14, 2008
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
View 12 Replies
View Related
Mar 5, 2007
Hi Dears, How can i detect the Local SQL Server instance (server name, uid, pwd) installed in a standalone systemusing C# 2.0?I used the fucntions available in odbc32.dll, but it only returns the result while the systemis connected in network.can any one resolve this....?thanks in advance...yenkay
View 5 Replies
View Related
Jul 30, 2001
with sql server 2000, it is possible to have multiple instances on one server. Now I want to make the second server a subscriber for specific tables before there are some tests/major changes. Has anyone experience with this on the same server. Note : sql server subscriptions between different servers in sql server 7 is not the issue.
View 1 Replies
View Related
Sep 20, 2001
I created a named instance of SQL Server 2000 on my notebook. I inadvertantly set the trusted SQL connection to Windows Authentication. This is creating a problem with COM because of username and password. I want to reset it to use SQL Server authentication but I can't find where to do it. Can I do this or do I have to delete this instance and create a new one.
View 1 Replies
View Related
Jun 4, 2008
Hi Gurus,
What process i should folloew to ctrae instance on server.
Thanks,
ServerTeam
View 1 Replies
View Related
Mar 25, 2008
Can someone explain what "named instances" means with regard to SQL?
Is this one Physical Server With multiple installs of SQL SERVER and / or databases ?
We have a list at my small comapy with "raw sql 2000 database instances". Can someone help out, I'm getting conflicting info!
Thanks,
Scott M
View 2 Replies
View Related
Aug 8, 2005
Dear SQL Guru,I just found out this morning that my SQL Server box had been added 1more instance. Below is the example:PPAPP0075FINANCE (Old instance)PPAPP0075ACCOUNTING (Old instance)PPAPP0075Accounting (new instance)This is a case sensitive MS Sql server (third party vendor App). TheACCOUNTING instance is the one we have been using for our currentapplications. I found out the new one(Accounting) when I scan thenetwork by using OSQL -L utility. However, when NT engineers tried toverify the physical files location ofAccounting, and it did not exist. When I tried to register it and itdoes not appear from Enterprise Manager.Please keep in mind NT Engineers have been monkey around on this serverquite a lot in last a few weeks, especially modifying Registry keys.My question is, where did PPAPP0075Acounting come from? We never evertried to install any more instance on this server.Please help,Thanks,Silaphet,
View 1 Replies
View Related
Jan 17, 2006
Hi all,
I already have a copy of SQL SERVER 2000 installed on my current local machine.
How is it possible that I create another second instance for example TEST/silkkeng in SQL Server Enterprise Manager? OR SQL query ?
Is it possible ? Or the only solution is ReInstall SQL to create another instances?
View 1 Replies
View Related
Jul 10, 2006
Hi, I've got an application which uses the DBFactory Provider to enumerate a list of available SQL instances on the local network.
I've just reformatted though and put everything back on, and my application will now see the sql server instances on the network, but not the instances on the local computer (only the server name), eg, it will see "BENSQLEXPRESS" on a computer (BEN) on the local network, and give the version info, etc, but it only sees "DAN" instead of "DANPCP" and "DANSQLEXPRESS" on the local computer (DAN).
Regards,
Ben
View 5 Replies
View Related
Dec 29, 2007
Hai,
One more question with related to SQL Server, I was using SQL Server 2005(Express Edition).
I was using SQL Server Instances as
.SQLExpress
Suppose if i install the Enterprise Edition , what is the default instances and other build-in instances:
Thanks.
Regards
Kashif Chotu
View 1 Replies
View Related
Jul 31, 2007
Hi there,
I was wondering if it's possible to create a new instance in the SQL Server something like this:
(Local)\MSSMLBIZ
(Local)\BUSINESS
Thank you
View 5 Replies
View Related
Nov 20, 2006
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
View 4 Replies
View Related
Jun 23, 2015
I am playing around in a test environment with SQL Server 2014. I have a question about the default location of the report server databases when you have multiple report server instances installed on one server.
I did a very simple install of SQL Server 2014 with the database and Reporting Services in Native Mode (install only) features selected. Accepting the default locations, I ended up with the following locations as you would expect:
C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVER
C:Program FilesMicrosoft SQL ServerMSRS12.MSSQLSERVER
Running the Reporting Services Configuration Manager, I created the Report Server database. After creating the Report Server database, the related files will be located below in the SQL folder as I would expect.
C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA
Next I installed another instance, which I called Test, of SQL Server 2014 like I did above. I now have the following folder structure the Test instance as I expect.
C:Program FilesMicrosoft SQL ServerMSSQL12.TEST
C:Program FilesMicrosoft SQL ServerMSRS12.TEST
Running the Reporting Services Configuration Manager, I select the Test instance and create the Report Server Test instance database.
My first thought would be that the Test instance Report Server database would be in the following location:
C:Program FilesMicrosoft SQL ServerMSSQL12.TESTMSSQLDATA
Instead this database is located with the default instance Report Server database:
C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA
View 2 Replies
View Related
Apr 3, 2007
Hi,
I want to list the instances SQL server in the network and their corresponding database. I found an API which list the server names in the network, but only for the main SQL server it is connecting and listing the database. For others it times out and gives run time error [DBNETLIB] Connection Open () SQL server doesn¡¯t exist or access denied
could anyone help on this
View 1 Replies
View Related
Jan 13, 2005
Hi,
We had 5 instances of SQL Server running on one of our machines and everything looked to be working fine. Today I installed another instance for a new application and once done and rebooted the new instance didn't show in the SQL Server Enterprise Manager. At this point we decided to uninstall it. After that 4 of the existing instances have diapered as well even though they are running and responding requests from their respective applications.
I did some googling and all I could see is something related to a command called nscontrol but I couldn’t find any nscontrol executable on that machine.
Does anyone have any idea what this problem could be?
Thanks
View 5 Replies
View Related
May 22, 2001
I am running SQL 7.0 as the default instance and SQL 2K as a named instance. Each is using a different port....7.0 is using port 1433 and 2K is using 1024. Everything appears to be up and running fine on the server. However, I am not able to access the 2K instance from a client. I have tried to use both TCP IP and named pipes setting them up using the server network utility and the client network utility. No luck anywhere. I continue to get connection errors...check network documentation...
ANY IDEAS??????
View 3 Replies
View Related
Feb 25, 2003
I an involved in a project that will have five sql server instances on an NT cluster.
I cannot find anywhere on Technet info on what happens when a single instance fails. Do they all have to fail over or just the failed instance. This is important so that we get the disk layout correct.
Can anybody help ? or point me to any docs on this.
View 1 Replies
View Related
Apr 11, 2006
Informatica jobs were erroring out with a sql server driver error
while connecting to a named instance in SQL Server.
The issue was because in sql server named instance, the port was allocated dynamically and Informatica 7.1.4 (provides 4.21 version of Datadirect odbc drivers for SQL server) which dont have support for named instances with dynamic port allocation. This feature is only present from version 5 of the Datadirect odbc drivers for SQL server
To fix this issue, I need to allocate the port for the named instance instead of getting it dynamically.
My questions
1) Whats the impact of doing a port allocation for the named instance on the existing environment.
2) Is there any issues which we can foresee in the applications
3) Things which i need to be aware of
Thanks in advance
sreenath
View 3 Replies
View Related
Sep 12, 2006
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?
View 1 Replies
View Related
Apr 29, 2004
How to connect two differect instances of SQL SERVER on LEN or WAN for Data Migration.
View 1 Replies
View Related
May 8, 2008
Is there any limit on SQL server Multiple Instances? if we require to run 60+ Instances on single machine what will be the minimum hardware requirements.
View 3 Replies
View Related
Jun 10, 2008
Hello,
Here is my setup:
2 node active/passive Microsoft cluster running SQL 2005 Enterprise Ed w/ SP2. The SQL setup is configured as a named instance, servernameinstancename. When I tried to install SCOM 2007, I'm getting this error, "Could not get SQL server instances names."
What could be the cause of this error? Any help would be appreciated!!
View 3 Replies
View Related
Oct 29, 2005
So I installed an instance of SQL for an application (WSUS) and all waswell. Having read in many places that I could install multiple instances ofMSDE on the same box, I started the second install today (Live CommunicationServer). The MSDE install portion of LCS gets almost completed and then itrolls itself back and fails with Event ID: 11708. Now when I go back intothe SQL Server Service Manager it says not connected and there are noServices available in the drop down list, including the one that should bethere for WSUS. Should I just reinstall WSUS again (hopefully I won't haveto reconfigure all of the updated approval/deny settings). Any thoughts onwhy the second instance fails? Thanks in advance.
View 1 Replies
View Related
Dec 6, 2007
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
View 2 Replies
View Related
May 5, 2007
Hi,
I nearly went nuts before finding out that SqlExpress Names Instances don't listen on Port 1433. Having discovered that I now think I can nominate a specific port, is that correct.?
My main question. I copied my databases from my development machine to a server, which will be accessed from an internal domain, and also through VPN tunnels. Should I change from User Instances on the server, and if so, how do I do this.?
My original application wasn't using User Instances, but chnaged to them somehow, when my PC had a name change.
Tailor
View 5 Replies
View Related
