Need An Explanation Of Msdtssrvr.ini.xml And Named Instances
Apr 30, 2008
We've read about a single installation of SSIS applies to all SQL instances on a server. We've also read how by default SQL Server stores SSIS packages in the MSDB database on the default instance and if you want to have packages stored on a named instance you must modify msdtssrvr.ini.xml. Here are our questions.
1 - If msdtssrvr is modified to reference a named instance, such as <ServerName>SQLTESTINST1<ServerName>, when I connect to Integration Services through Management Studio do I still specify the default instance name in the login window? That's the only way we have been able to connect and despite specifying the default instance name, the packages displayed are those of the named instance.
2 - If I am correct that you must always specify the default instance name when connecting to Integration Services, then in order to eliminate confusion regarding what SQL instance's SSIS packages I am viewing under the folder structure I can create SQL Server folders by modifying msdtssrvr.ini.xml. For example:
I then restart the SSIS service and can now see the following folder structure.
Running Packages
Stored Packages
+ File System
+ MSDB
+ MSDB-INST1
+ MSDB-INST2
If I expand MSDB-INST1 I will only see packages saved on the instance named INST1. The same is true for INST2. If I expand MSDB I will only see packages saved on the default instance.
I've read how I need to update MsDtsSrvr.ini.xml to replace "." with the name of my named instance. For example, in the script below I replaced "." with "MYSERVERNAMEMYINSTANCENAME". When I connect to Integration Services do I specify the named instance in the connection string or the default instance? I'm unable to connect specifying the named instance.
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)
We have a vendor product that can only handle up to 18 characters for the server name and the instance name (we did not know this when we created the named instance) and of course our combined name is 19 characters, so it trys to pass the 18 charater name and then fails with the following error:
[LOG] Unable to read local eventlog (reason: The data area passed to a system call is too small)
So, can i rename the instance, or do i have to create a new named instance?
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
We are using a system that has uses four instances. On each client to the server we used the ODBC settings to connect to the different instances. We have since reinstalled the server, and now we are unable to find or 'see' any of the databases, except for locally.
E.g. The first database we install is named Foo, with database Bar. It is set up on port 1433. The second named instance is Foo2, with database Bar2. It is set up on a dynamic port.
We now try to connect to the database with ODBC. We connect to SERVERFOO, and then select the BAR db. It works fine. We then try to connect to the database SERVERFoo2, it lets us in, and then all we can select is the BAR db. I don't know what is causing this. We never before had to edit the settings in the client config for the odbc settings (e.g. to set manually to TCP port 1433) and I have checked the sql config manager to ensure that tcpip settings are allowed and will "ListenAll".
What more do we need to do? I think the browser should be installed, but my collegue disagrees.
Any ideas as to why we can only see the database referenced to 1433 port, and why no other port works, when we never had to do that before?
Using VS05 SP1 Pro SQL Express€¦ In converting a program from VS03/MSDE, long ago I installed SQL 2005 Express with Advanced Services and everything was fine. Windows Update has recently been failing to install SQL 2005 Express with Advanced Services SP2. After no answers from the forums, and logs that were of no help, I decided to uninstall SQL 2005 Express with Advanced Services and reinstall SQL 2005 Express (basic version). As a test run, I did it on a desk top, no problems what so ever. However, on my laptop, it has been a nightmare.
Question 1) in control panel, add/remove programs, all of SQL 2005 Express with Advanced Services uninstalled fine except one item - I am unable to uninstall MS SQL 2005 Backward Compatibility, getting the error message A network error occurred while attempting to read from the file c:windowsinstallersqlserver2005_bc.msi That file is not on the hard disk. After installing SQL 2005 Express (the basic version), this problem persists and I cannot uninstall MS SQL 2005 Backward Compatibility for the same reason. How can I get rid of MS SQL 2005 Backward Compatibility?
Question 2) Windows update still is trying to download and install SQL 2005 Express with Advanced Services SP2, even though I have uninstalled it. How to manually get rid of whatever settings are triggering this?
I have a server computer named 'X' and I would like to install 2 instances of SQL Server 2005
1. SQLTEST
2. SQLDEV
During installation. if I select Default instance then it will name my sql server instance X . I don't want that. I want to have essentially 2 named instances to make it clear to the users that they are using a test and dev sql server. Whats the best way to do this? I am open to suggestions if there is a different way to do this.
Is it required to have a default instance? Can I have 2 named instances XSQLDEV and XSQLTEST?
Also, on the SQLTEST I will be installing reporting services. By not having a default instance will it cause issues for the SSRS installation.
I'm struggling a little with this. I hope someone can help.
Ho do i install SQL Server 2005 Express so i can connect to it with "localhost", not "<MACHINE_NAME>/SQLEXPRESS". I cant seem to find any obvious setting in setup.
I am using robocopy to copy my sql server backups to a fileshare. This is working great except for named instances.I use Ola Hallegren's backups which create a folder called xxxx$xxxx. Robocopy can't seem to handle the $.
Hello, I am after a bit of advice about running the Upgrade Advisor tool on a server that has more than 1 SQL Server instance. My particular focus is DTS on each server. I tried running the tool, setting up an XML file per SQL Server (including named instances). An example of the XML is I used is included below): <Configuration> <Server>SQL01</Server> <Instance>INSTANCE01</Instance> <Components> <DataTransformationServices /> </Components></Configuration>
When the tool runs, folders get created for the relevant named instances, but the DTS runs against the default instance only. In other words, the default instance DTS.xml file gets overwritten several times. Does anyone know if the Upgrade Advisor tool can handle multiple SQL instances on the same server, particularly in realtion to DTS? I had thought this might have been a limitation as the options in the Report Viewer are "Server" then "Instance or Component", when I am really after "Server", "Instance" AND "Component".
I installed SQL 2005 Standard Edition first, then MSDE plus its SP4. Both instances are named (MC05WXPPROSQLMC for 2005 and MC05WXPPROUCTO for MSDE). I inadvertently had not allowed the TCP/IP protocol for MSDE during the installation, so I did it manually later, through SVRNETCN.exe which also ships with SQL 2005.
Now: I CAN connect to the MSDE instance with any SQL2005 tools through shared memory. But I CANNOT connect to it through TCP/IP. I guess that this is because wrong port binding for the SQL2005 instance, MSDE instance and SQL Server Browser, but I'm not sure.
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
Hello, I'm trying to list the default and named instances on our domain. When I run SqlBrowseConnect all I get back is a list of our servers, for example: COMP01 COMP50
I'd like to get all the named instances as well, a list with: COMP01 COMP01DD COMP50
When I run "osql -L" I get the full list, but not with SqlBrowseConnect. I have tried to set SQL_COPT_SS_BROWSE_SERVER to (SQLPOINTER)NULL which, according to the docs, "[...] returns information for all servers in the domain." I have also set the SQL_COPT_SS_BROWSE_CONNECT to SQL_MORE_INFO_YES without any luck.
Hello, I tried this and it doesnt work, I just started up the service with -T flag on default instance but I havent been able to start it with -T flag 1400 on named instances.
How can I Know that the directory MSSQL.4 is the MIRROR instance??
C:Archivos de programaMicrosoft SQL ServerMSSQL.4MSSQLBinn>sqlservr.exe -T 1400 MIRROR
I tried LUCHOMIRROR, MSSQLSERVER$MIRROR
and It doesnt work
Thanks
Another question I have, in the documentation it says that mirroring is only for evaluation purposes and not for production environments, what does it suppose to mean? MSFT didnt test the mirroring feature enough? If its not suppoerted why its included..?
Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL).) Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.
Ok, here's the deal. This has been killing me for months now.
Here's the setup - SQL 2000 database on a Shared Infrastructure (cluster). I have a database on a named instance. My port number is xxxxx. I have dbo (through Windows Auth) permissions on my database. When I connect at the office, I get through fine, no question. When I connect at home, I get error 26 in my asp.net 2 app (using System.Data.SqlClient), cannot find SQL Server database. I can connect through the SQL 2005 Management Studio using the same servernameinstance name I use at the office. I can connect through the SQL 2000 Query Analyzer using servernameinstancename;(semicolon)portnumber. I've tried all sorts of combinations in my web.config - servernameinstancename,(comma) portnumber servernameinstancename;(semicolon - causes web.config to blowup) portnumber ipaddressinstancename, (comma) portnumber
and i still get nothing. usually it's 'error 26' - but if i use this:
Data Source=ipaddressinstancename,portnumber;Network Library=DBMSSOCN;Initial Catalog=xxx;Trusted_Connection=Yes;
I get 'Login failed for user domainuser'
I've tried making an alias. I've tried using ODBC. I've even tried a freakin' DSN. No dice.
Nothing seems to work - I'm at the end of my rope. Can someone please help?
I have a 2012 AlwaysOn DB Mirroring environment set up with two nodes. Both have 5 installs of SQL named instances.
The issue we are having is when we patch one server and fail everything over, some of the applications will error. Some of the applications had to have their web.config files updated with hostinstance name because it seems to not work with DNS.
We are planning to build a cluster environment on MS SQL 2005. We want to install multiple named instances in a single database server and use cluster for that database server.
Is this option possible ? Any relevant documentation would be helpful
Hi folks I have deployed a package from file resources to sql server named instance. I did not receive any errors. msdb..dtspackages90 table have rows for my package. But when I try to connect to integration services using ssms like 'serverinstance', I am getting an error saying that 'serverinstance' names are not supported by integrated services and I should use just a servername. What am I doing wrong? Is that really integration services are not supported on named instances or i miss something?
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.
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.
In our current environment, we are running about 10,000 packages per day on a given 64 bit SSIS server.
The MSDtsSrvr.exe (SSIS service) process' memory working set size continues to grow until it consumes the memory on the machine.
Some of the packages do use a custom component. Could this be responsible for leaking memory in the MSDtsSrvr.exe process? Has a memory leak been confirmed in this process for the "first-party" components anyway?
I have changed the location of the MsDtsSrvr.ini.xml file and the same updated the changed location in registry key SOFTWAREMicrosoftMSDTSServiceConfigFile.
After making the changes I restarted SSIS. When I tried viewing stored packages using SQL Server Management studio, I was not able to see File System folder or MSDB folder
When I checked error log, the below message was recorded.
"Microsoft SSIS Service configuration file does not exist. Loading with default settings."
