SQL Server new Linked Server
-
hello my friends.
I have a suuuuuper weird question about sql server, at my work we are about to migrate our database from a windows server to an Azure server , currently we use toad for oracle to do our query's and stuff when the new azure server comes we also will change from toad to sql server , so im trying to connect sql server to one of our database in our current windows server, i have created the connection using odbc data source admin and the connection is successful, but when i try to open a table or something an error message saying:
Then a co-worker told me i have to check with a query the connection between my new linked server which if i click in test connection it says the test connection to the linked server succeeded
and sql server, so i checked how the connections query's are checked in our oracle toad database and it says this:
select *
FROM "root"."hagent"@server_name
my question here is how to write that but in sql server ??? , i hope mi previous explanation helps to understand what my question is XD it looks kinda confusing but i wanted to give it a try, or if you know how to fix that error message i would highly appreciate
Thanks a lot if you are able to answer.
Regards.
-
I'm having a hard time following that but I'll give it a stab on the error message. I've seen that message before when they tried to access a database that had the auto_close property set to true. And there was some other process like anti-virus accessing the files for the database. The fix for that is to turn off auto_close and you would want to exclude database files from any antivirus or other tools that could hit the files if that's the issue in your case.
I'm not real sure what you are asking about the query. If you are looking for a basic query to an Oracle instance set up as a linked server in SQL Server, it would be: Select * from LinkedServerName..SchemaName.TableName
Sue
-
hi Sue, thanks for replying, yeah its a rough explanation, to make it simple my problem is :
I've created a linked server, as i commented in the previous post the test connection succeeded but if i try to open a table is when sql server error 7399 appears:
-
Okay...that's a totally different error than the one originally posted. This one is an access denied error so it's however you setup the linked server to access the Oracle instance. Look at your linked server, right click on it, select properties and go to the security page (by selecting Security on the top left when you pull up the properties). That's where you need to setup your login that accessing the Oracle instance. If you are using your Windows login for Oracle and SQL Server, you can select be made using the logins current security context. If you have a specific Oracle login you use, you can select that last option of Be made using this security context and enter the login and password. If you have some other combination, you need to use the security mappings at the top of that screen.
Sue
-
I tried and nothing , stills the same error message T.T , do you think this might be cause due to a bad set up from my odbc data source admin?, when i did the connection i tested it and it was successfull, then i could created the linked server with IBM informix odbc driver set up, and i chosen that option to connect the server, but since it says the connection was successful i don't think it is causing the problem but what do you think?
-
Probably the issue. I have never heard of it being setup in that manner. I've always done mine to Oracle by downloading the version of ODAC (Oracle Data Access Components) I needed and used those. Never used the ODBC administrator or Informix odbc driver. If you haven't done one before, do a quick search on: sql server setup oracle linked server
From there you can find a bunch of articles as well as YouTube videos, whichever works best for you, walking through the setup. I think you just need to get the right pieces in place. Once you do it once, it's pretty easy from there. And you can setup all your coworkers 🙂 It's only confusing the first go around. Please post back - especially if you still have problems after getting everything setup correctly.
Sue
-
And I just did a quick run through some of those articles - this one seems fairly complete:
How to get up and running with Oracle and Linked Servers
Sue
-
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply