Connecting to a Clients SQL server

I am not sure if its possible, hopefully it is. I would like to create a custom page for one of my clients to log into and access information from their SQL server. If I have all the connection information, i.e user id and password etc. Can this be done. I know how to connect to an SQL server locally. If anyone can give me a point in the right direction, it would be greatly appreciative.

If this is possible, I am looking at adding a button on this page to allow the client to transfer records that meet specific criteria in a column in their database into a designated database on my server.

Thanks for any help.If your server can access their SQL Server, sure. Ask them for the TCP/IP connection information, and if you can see their server, you should be able to pull it off.

The difference from what you may be doing locally, is that instead of server=(local), you'll likely have the IP address of their server, and you may be using Windows Authentication locally, but you'll use SQL security to connect to their machine.

Regards,

Xander

Connecting Execute SQL Task with Custom Connection Manager

Hi,

I'm using Integration Service between Firebird and MS SQL 2005. I can able to move data from Firebird db tables to MS SQL db tables using DataReader source (Firebird Connection Manager) and SQL Server Destination (SQL Server Connection Manager) component.

Now I want to execute parametrized SQL statement to fetch data from Firebird DB using Execute SQL Task Component. In this process while trying to set Connection property to this component I can able to view only MS SQL Connection Manager and it is not listing Firebird Connection Manager.

Now I need a help to get connect this Execute SQL Task component with Firebird connection Manager. Help me to find out where I'm making mistake.

FYI. I'm using Firebird .Net Provieder 2.0.1
Thanks in advance.

Suku

More clarification on my query

Here, the mentioned Firebird Connection Manager is a custom created connection manager and we created using Firebird .Net Provider 2.0.1

I tried with Lookup component too, it shows only the destination connection Manager (MS SQL) but not the Source one (Firebird Custom Connection Manager).

Suku

|||

Could you try to set the connection type to Ado .NET in the Execute SQL Task UI?

Lookup can only use OLE DB connections not Ado .NET.

Thanks.

|||

Hi,

Were you able to resolve this issue? I also have a custom connection manager that I am trying to use from Execute SQL Task. Unfortunatelly I don't see my connection object in Execute SQL Task Editor, though it gets added as a new connection successfully. My custom connection manager is basically a very simplified version of the Microsoft's SqlConnectionManager Sample. I am trying to make a custom connection manager which will hide the login info.

Thanks!

Connect to SQL Server Database with custom port

I have a SQL server database that I am trying to connect to from VS2005 in the Server Explorer.

I am not using the standard port for my SQL server listening port and I don't see where I can tell the Server Explorer to use a different port?

Can someone point me in the right direction for connecting to my database?

Thanks.

There are 3 ways to do it. Here's an article detailed all of them:http://www.databasejournal.com/features/mssql/article.php/3689846

Make sure you read carefully in section: "Connecting Clients When using Specific Part Assignments" as the first 2 methods are for client tool connection and the 3rd is specific for the ConnectionString in you config file.

Hope it helps.

|||

Thank you very much. This was extremely helpful!

Connect to SQL Server 2005 Problem

Hi all,

I have created an Outlook Add-in using VS2005, which basically populates a custom WinForm with some data retrived from the backend SQL Server 2005 database.

The SQL Server is installed on the same computer as the development environment (VS 2005).

The add-in code compiles OK and I have created a setup package for this app, which installs the required files with no problem. The package has been tested working on the development machine. But as soon as I install it on another computer (installation was no problem, and outlook launched the add-in as expected) and click on the add-in button I made on the menu bar, the add-in hangs up.

I have checked the tables on the sql server and found that no operations was performed whatsoever. Suspecting there are errors connecting to the server, I also threw in some Try - Catch pairs to catch the exceptions, but still nothing pops up as if the app couldn't even start while it runs just fine on the development machine.

Below is how I connect to the SQL Server 2005 in my code (VB)

Dim cn As ADODB.Connection = New Connection
Dim rst As Recordset, query As String
cn.ConnectionString = "Provider=SQLNCLI;Server=Development;Database=Project1;Trusted_Connection=yes;"
cn.Open()

query = "SELECT * FROM Sample_Tab;"
''' Do something with the connection
Try
rst = cn.Execute(query)
Catch ex As Exception
MsgBox("ADODB Error: " & ex.ToString)
Exit Sub
End Try

cn.Close()

Any help will be greatly appreciated.

-rahx

Did you enable NP and/or TCP on your SQL Server? You need make sure that your sql can accept remote connection. Do you have firewall? Please follow the instruction and give us me info http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

|||Problem solved - my mistake. I used sql native client in my code but forgot to include a sql native client msi in the installation package.

After installing the native client on the test computer, the connection was established successfully.