Create a Linked server without using administrative tools

Question

Create a Linked server without using administrative tools

SpeedSkaterFan

SSCrazy

Points: 2716
More actions
October 6, 2017 at 11:30 am

#323925

Hallo all,
I am having difficulty creating a link between SQL Server and mySQL.
I had to buy a new computer. This has Windows 7 on it, but apparently it is a downgraded version from Windows 10.
Whenever I try to create a data source (in administrative tools / system DSN) I get the answer that I am loggod on without administrative privileges.
I have tried to come up with a solution for this but whatever I try, nothing works.
In the meantime I have multiple users added (supposedly administrators) but I keep getting the same message.
In stead of working with SSIS, what I used to do on my old computer, I think that maybe working with a linked serve is a solution.
I believe the general statement to use a linked server is: select * from openquery (covas,'select * from tbCountries')
The database I want to connect to is Covas
The path where it can be found: C:\ProgramData\MySQL\MySQL Server 5.7\Data\db_covas
If I use: " select * from openquery (covas,'select * from C:\ProgramData\MySQL\MySQL Server 5.7\Data\db_covas\tbCountries') " I get the message: " Could not find server 'covas' in sys.servers "
Is there a way to connect to mySQL, without creating an ODBC data source?
Thanks in advance
Hein

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

SpeedSkaterFan - Friday, October 6, 2017 11:30 AM
Hallo all,
I am having difficulty creating a link between SQL Server and mySQL.
I had to buy a new computer. This has Windows 7 on it, but apparently it is a downgraded version from Windows 10.
Whenever I try to create a data source (in administrative tools / system DSN) I get the answer that I am loggod on without administrative privileges.
I have tried to come up with a solution for this but whatever I try, nothing works.
In the meantime I have multiple users added (supposedly administrators) but I keep getting the same message.
In stead of working with SSIS, what I used to do on my old computer, I think that maybe working with a linked serve is a solution.
I believe the general statement to use a linked server is: select * from openquery (covas,'select * from tbCountries')
The database I want to connect to is Covas
The path where it can be found: C:\ProgramData\MySQL\MySQL Server 5.7\Data\db_covas
If I use: " select * from openquery (covas,'select * from C:\ProgramData\MySQL\MySQL Server 5.7\Data\db_covas\tbCountries') " I get the message: " Could not find server 'covas' in sys.servers "
Is there a way to connect to mySQL, without creating an ODBC data source?
Thanks in advance
Hein

To create the data source, you can try right clicking on ODBC Data Sources app and select Run As Administrator. Run As Administrator is not the same as being in the Administrators group,

Another option is to use openrowset. It's similar to openquery except you put in the connection string rather than referencing a linked server. What you use for the connection string depends upon what driver you are using. A general example would be something like:
SELECT YourColumns FROM OPENROWSET(N'MSDASQL',N'Driver={MySQL ODBC 5.2 Driver};Server=MySQLServername;Database=MySQLDatabasename; User=MySQLUserName;Password=MySQLPassword;Option=3;', N'SELECT YourColumns FROM YourMySQLTable')

You will need to figure out what driver you and using to create the correct connection string. This is a good reference for the connection strings:
MySQL connetion strings

Sue

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 2

Thank you Sue for your reply.

I have tried to right click the ODBC data source and open it as an admin.
This gives me the same response as usual (no admin privileges)

I will, of course, give your other suggestion a try.
If I read the code correctly I have to sum up the columns needed 2 times.

I will get back to you with my results,
For now, thanks again
Hein

Sue_H SSC Guru Points: 90860 More actions · Answer 3

SpeedSkaterFan - Friday, October 6, 2017 12:24 PM
Thank you Sue for your reply.
I have tried to right click the ODBC data source and open it as an admin.
This gives me the same response as usual (no admin privileges)
I will, of course, give your other suggestion a try.
If I read the code correctly I have to sum up the columns needed 2 times.
I will get back to you with my results,
For now, thanks again
Hein

Not necessarily sum up the columns two times, not really summing anything. Maybe you meant refer to the columns two times?.
In any case, You can use select * as you did in the openquery - I prefer specifying the columns, especially for remote data. In openquery or openrowset, you query the data source and then from that result set you query again - they are the same with the difference being a reference to the connection string or a reference to the linked server. And where the from clause is used.

Sue

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 4

Sorry Sue
I need more time
This is all new for me, and a whole strange world opens when i look for clues to solve my problem
Time is running out tonight so I will try again tomorow
Hein