Programatically Create A DSN For SQL-Server With Non-standard Port-number
Apr 16, 2007
Hi all,
I have to create a DSN for SQL-Server. Therefor I use the function "SQLConfigDataSource" from "ODBCCP32.DLL". But the problem is that the SQL-Server does not use the Standard-Portnumber 1433. I've tried to make the DSN with "SERVERNAME xxxx" where xxxx is the Portnumber, sometimes it works, but often it does not. When it does not work, then the Checkbox in the ODBC-Configuration-Tool is checked, so the connection is searching for the standard-port. How can I tell the system that it shall NOT use dynamic configuration?
I tried creating an alias to the server to get it to connect to analyze the server but it will not recognize the SQL 2000 server as a valid server to analyze. I can use the alias to connect in EM or SSMS. Any ideas? The server is not clustered and is at SP4. I've connected to several others in my environment but this one is causing me grief!
Hi there, I want to change the default sql-server port number 1433 to a new one. Can i do that? If yes, how? Also, i have noticed that there is no port number option in the sql server 2005 management studio login window. So, what if the server has changed its port number? Any suggestions would be appreciated. thanks
How can I configure dbdesigner to connect to a sql server that is listening on a different port number ? For those of you not familiar with debdesigner: http://fabforce.net/dbdesigner4/index.php
I can connect to a local instance just fine but not to a sql server that is listening on a specific port (named instance)
[edited to add] it works ! I just had to use the name instance notation server instance_name
I know the ..Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGErrorLog on the server contains the information. But that needs I to RDP to the server or open the file remotely. Is there any other way to find out the port from my SSMS connection?
I remember in SQL Server 2000 you could find the information by choosing Properties of a connection in EM and click on the Network Configuration button. (I know it works when you are ON THE SERVER! But when I tried from my desktop, pressing the button didn't do anything)
I have TWO named SQL Server instances (on the same machine) and I need to know the port of each of them, how can I do that? Is it write to check the following:
Which one to take: "TCP Dynamic Ports" or "TCP Port"? and what is the difference between them anyways?
Can the two instances (or more) on the same machine use the same port?!
I'm trying to setup SQL Server 2005 Express to communicate through a non standard port. I can connect to SQLServer on a remote machine using port 1433 when it is set as such but when I change the server to another port and try and connect using SSMSE I can't get through. I'm specifying the ip address and a colon followed by port number to connect.
For example: 123.123.123.123:1234
Any ideas?
---------------------------------- Standing on the shoulders of giants
1. Is there a quick way to find out what port number SQL is listening to? I have 10 instances running on one server and I could find out by going to SQL log but it's bit time consuming.
How can i define a different TCP/IP port number , other than the default 1433; to replicate the database over a firewall on the source and the destination servers ? My source servers are SQL SERVER 7.0 with SP3 on Windows NT Server 4.0 and the destination servers are SQL 2000 with SP2 on Windows 2000 .
Where do i have to make changes in SQL server and Windows NT ? And further how secured it is to use this way for security ?
hai all, I need to change the default port number of the SQL server from 1433 to some other port number. i know that it can be done thru SERVER NETWORK UTILITY. Can some one confirm that. It will be helpful. Also i want to make sure that port to given is free port. how can i find that particular port is free.
Where - in SQL Server Profiler - can I see the port number that someone has used to connect to a database?
e.g. given the connect string "tcp:MACHINE1INSTANCE7,3045" - where in Profiler does it tell me that the connection is using port 3045? I looked at "audit login" and "existing connection" but I don't see a port number...
Any other SQL/Windows tools that I can use to monitor connections to databases on specific ports?
In VS2003 I have a user defaults table that I would call in the OnLoad - No Postback section to get the user defaults and then used some of the fields from that recordset as Parameters for other recordsets that would also load in that same section and might even issue a different Sql statement based on the field. In VS2005 (VB.NET), I can no longer figure out how to accomplish this. I can use the SQLDatasource Control which seems much improved but I cannot even find the generated code which I would use as a quick way to create my own datasources in the OnLoad event. Where is this code now stored and how can I accomplish my objectives in VB.NET 2005 with the current framework or programatically create data sources as necessary as I did before?
I need some help with the following issue. I need vb.net code that will create a T-SQL script. For example given the orders table in northwind I would get the following with indexes, RI, ect. :CREATE TABLE [dbo].[Orders] ( [OrderID] [int] IDENTITY (1, 1) NOT NULL , [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EmployeeID] [int] NULL , [OrderDate] [datetime] NULL , [RequiredDate] [datetime] NULL , [ShippedDate] [datetime] NULL , [ShipVia] [int] NULL , [Freight] [money] NULL , [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]GO CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]GO CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]GO CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]GO CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]GO ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY ( [CustomerID] ) REFERENCES [dbo].[Customers] ( [CustomerID] ), CONSTRAINT [FK_Orders_Employees] FOREIGN KEY ( [EmployeeID] ) REFERENCES [dbo].[Employees] ( [EmployeeID] ), CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY ( [ShipVia] ) REFERENCES [dbo].[Shippers] ( [ShipperID] )GO
Hello,I have SQL Server 2000 Enterprise Edition, and I'd like to change thedefault port to something else (we'll use 2033 for this example). I'vetried to connect from another computer on the same subnet using"ServerName:2033" as the server, but it doesn't connect. It will connect ifI change the Server to use the default port and change the server to connectto on the client to "ServerName", but either I"m not referencing the newport number properly or ????Any help you could provide would be great.Thanks In Advance!Rick
Hi allI'm struggling to find an answer to this one!We've configured our SQL boxes to run under a different port number,e.g. from 1433 to 4533. This works fine under our Citrix enviornmnetwhere the dsn is configured to use the new port. The problem comes interms of SQL Server management. For example, when you try andconfigure SQL Replication, it is not possible to add the subscriberunless the SQL Servce runs under port 1433, the default port. Thesame goes when you try to add the server as a registered server withinthe Enterprise Manager MMC.Does anyone know how you can configure SQL Server to run under adifferent port number, without naffecting SQL REPLICATION, etc?Many thnaksNikJoin Bytes!
I am having an issue with SQL 2005 unattended install, I cannot see where in the template.ini file you can specify the TCP port number for SQL 2005 to listen to I want to change it to a specific port number, I know you can do this in an unattended install on SQL 2000.
Also in an unattended install of SQL 2000 and 2005 is there any way you can specify the database and transaction logs location for new databases to be installed on another drive then the system databases. I know you can change this later in SQL server properties but I wanted this to be done during the install or is there a T-SQL script which can do this after?
I am writing some C# code that builds a package programatically. the package contains a flat file connection manager. I have referenced Microsoft.SqlServer.Dts.Runtime.Wrapper in order that I can use the ConnectionManagerFlatFileClass class.
I get a compilation error on the last line saying I cannot implicitly cast as ConnectionManagerFlatFileClass. Fair enough, so I change the last line of code to:
Now it will compile. But when I run it I get the error:
"Unable to cast COM object of type 'System.__ComObject' to class type "Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerFlatFileClass'. COM components that enter the CLR and do not support IProvideClassInfo or that do not have interop assembly referenced will be wrapped in the __ComObject type. instances of this type cannot be cast to any other class; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface"
Can anyone tell me how I can cast the ConnectionManager.InnerObject as a ConnectionManagerFlatFileClass please?
-Jamie
P.S. My ultimate aim here is to set the metdata of each of the columns defined by the flat file connection manager. I can't see how I can do that without referring to the connection manager as a ConnectionManagerFlatFileClass!
I am just wondering what's the difference of sql server 2005 standard and enterprise editions. can I use SQL server 2005 to host a database for a website?
I created a SSIS package, added Script task. created data flow task programatically, trying to add a flat file source component programatically. stuck at this point.
my goal is to add flat file source component to the data flow task and insert into a table in sql server using oledb destination component all programatically.
i am working on a small "Biztalk" engine, by creating dynamic ssis packages that change according to the client source file definition.
in order to create a row-number to each row in my input file, i am tryng to add the Konesans's Row Number component to the dynamic package by using SSIS API, but i get a lot of errors. the component is not created as a"rowNumber" component, but as a General Managed Component, though i use the ComponentClassID as the classID in the RowNumber component.
has anybody try to do this ?
is there any way to get the row number other then this way?
My vb .net app creates programatically a package. I mean, you can provide name, description, number of OLEDB/ADO connections, variables and so on, where it's stored, from a WinForm.
Up to the moment, process only creates boxes separately wihtout links.
My concern is after this successful cycle I only can open that DTSX and see its components but executing button is disabled (only possible with DTEXECUI.exe) from BIDS.
Only when I create a project (DTSPROJ) and then I put that same .DTSX inside I am be able to execute it.
Well, up to here all of this is very good. Things were created this way for some reason.
My question now is how do I from my application in order to create dtsproj and dtsproj.user (I don't know how many files are needed) and be able to create a package and its "father project". My idea is that the user can choose if create a standalone DTSX or a full project.
I can't map other sql servers without creating alias with proper port number on client network utility. Other users using same version of the client tools, MMC, SQL DMF etc. I need to map 70 sql servers on using my client tools. Any help is appreciated.
I have a new named instance (SQL Server 2005 x64 SP2 (Build 3159) Developer edition) setup with dynamic port selection. A default instance was also running on the server prior to the named instance setup.
SQL Server Browser service is running. Remote connections are configured on the named instance. But still remote clients can only connect when the port number is specified after the instance name (servernameinstancename,9999). Without the port number, "error 26 - Error locating Server/instance specified" is thrown.
Doesn't the SQL Server Browser service eliminate the need for clients to specify the port number? What am I missing?
We have an SSIS job that runs once a day and sends emails. For security reasons, IT switched the send-mail porton the smtp server from 25 to a different number (let's say 1234).
So, I changed the SSIS SMTP Connection Manager's smtpServer string to smptserver:1234
However, this does not seem to be working. I keep getting 'Send Mail Failure' error.
Any ideas how to set the port number for sending emails using SSIS?
I hope someone can help as I have searched for a solution but been unable to find one as yet.
My problem is that after installing SharePoint V3.0 and amending the Report Server to <ServerName>:8080, as I don't yet wish to use integrated mode, everything works fine except email delivered subscriptions. The URL in the email body referencing the report only puts in <ServerName> etc. omitting the port number :8080. I have tried to find where this URL link is built-up from but have had no luck in the report manager, report services configuration and the config files, does anyone know where I need to look and if possible what I need to amend to have the link include :8080 after the servername portion of the URL. I can copy the URL and manually insert the port address and it works but this defeats the object of automated subscriptions.
Sorry if this is a dumb question but it is giving me a headache at the moment.
How to create a row number for a consecutive action. Example: I have a listing of people who have either completed a goal or not. I need to count by person the number of consecutively missed goals.
My sql table is this: PersonId, GoalDate, GoalStatus (holds completed or missed)
My first thought was to use the rownumber function however that doesn’t work because someone could complete a goal, miss a goal, then complete one and when they complete a goal after a missed goal the count has to start over.
