ansaurus

Question

using different data connection in visual studio

Answer 1

A:

No (AFAIK). But you could perhaps deconstruct the connectionstring from your web.config to get the db location, and re-create your sql (in a SP) from there?

Tobiasopdenbrouw 2010-07-29 14:19:04

Firstly thank you for your effort! Something I've understood, but the way I should do, still I need more details, please...please, can you explain or give me any example, like step by step :) ...

gaponte69 2010-07-29 14:23:17

Does this help? http://stackoverflow.com/questions/1355472/using-the-web-config-to-set-up-my-sql-database-connection-string

Tobiasopdenbrouw 2010-07-29 14:33:44

Answer 2

+2 A:

If your using Microsoft SQL Server and MYSQL then you can use Microsoft SQL Server Linked Sever to accomplish this. A Linked Server is a server linked via OLEDB. This linked server will allow you to connect to the MYSQL database from SQL Server and run remote queries.

Example

SELECT * from MyLinkedServer.[Database].[Schema].TableName  T1
INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK

OR

SELECT * FROM OPENQUERY([NameOfLinkedSERVER], 'SELECT * FROM TABLENAME') T1
INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK

John Hartsock 2010-07-29 14:23:47

But you'd still be firing this query at one dataconnection, wouldn't you? (I'm learning here, so your answer is good either way).

Tobiasopdenbrouw 2010-07-29 14:31:31

Yes it would only be one dataconnection in your application but this one data connection will allow you to execute remote queries on the MYSQL Server.

John Hartsock 2010-07-29 15:26:26

Dear Hartsock,Your query was useful, but still is not solving my issue in query builder of visual studio, where I have 2 different data connections:1.mysql connection named as dimAccess2.mssql server connection named as te-sharepoint\sqlexpress.dimAccess.dboNow I want to select data from these 2 connections in a single select query and the query below is my case:SELECT * FROM OPENQUERY(MYSQLDIMACCESS, 'SELECT * FROM tb_user) T1INNER JOIN te-sharepoint\sqlexpress.dimAccess.dbo.tblUserProfileT2 ON T1.id = T2.iEmpIDThis query is having correct result only in SqlServerManagement,butNotInVS

gaponte69 2010-07-30 07:08:53

Thank you Mr. Hartsock ...I found the solution based on your examples... My only mistake was related to bad naming of data connections, using by default special characters, like '-' or '\' ... anyway...good experience...thank once again...

gaponte69 2010-07-30 07:33:26

Dear Hartsock,I have another issue related to this openquery closure:How can I use a parameter inside sql openquery, such as:SELECT * FROM OPENQUERY([NameOfLinkedSERVER], 'SELECT * FROM TABLENAME where field1=@someParameter') T1INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK

gaponte69 2010-07-31 12:27:42

ansaurus

tags:

views:

answers:

using different data connection in visual studio

related questions