none
Linked Server to Oracle Database in SSDT Project?

    Question

  • Hey,

    we have created an SSDT Solution. The solution holds three database projects (different databases) and an additional database project, that holds the server level objects like Logins and Linked servers. This fourth project exists because the databases have references to each other, so if we would store the logins inside of the databases, visual studio would complain about having the logins multiple times. So we put them into the fourth (Server) project and also added the linked server there. We have one linked server to an oracle database and another connection to Active Directory. Both of them don't work!

    When publishing, it fails and stops at a specific point with the following error message:

    (6780,1): SQL72014: .Net SqlClient Data Provider: Meldung 7403, Ebene 16, Status 1, Prozedur p_Oracle, Zeile 19 Der OLE DB-Anbieter 'OraOLEDB.Oracle' wurde nicht registriert.
    Fehler bei der Batchausführung.

    Ho can we solve this?? We do not want to remove the linked server from the project or put it into the pre/post deployment scripts because some other objects reference it, so they would fail as well without the linked server.

    Regards, s.

    Thursday, August 30, 2012 12:28 PM

Answers

  • It's getting more and more weird...

    A possible solution might be to set the customer specific scripts to Build Action "None". As I expected, the deployment then runs without any errors, because the part that did not work is not deployed anymore. So far so good.

    On the other hand, now a procedure that previously has called the not anymore builded procedure, still works, but does not "know" the calles procedure anymore => you can not "Go to Definition" anymore (of course because it is not included in the build anymore) BUT these procedure calls are also not being thrown as an error and not even as a warning. I really wonder why because there is a schema.procedure called that is not included in the project. This does not even change if you completely exclude the calles procedure from the project.

    Really strange... but after all it solves my problem with customer specific procedures using customer specific linked servers...  :)

    Wednesday, September 05, 2012 3:30 PM

All replies

  • Can you please post the fragment from the deployment script that causes this error?

    Error 7403 The OLE DB provider "%ls" has not been registered. is a SQL Server runtime error, which gets raised when the OLEDB provider that is not installed.

    If you execute exec xp_enum_oledb_providers from a query window, does the Oracle provider show up?


    -GertD @ www.sqlproj.com

    Tuesday, September 04, 2012 8:45 PM
    Moderator
  • Gert, thanks for your feedback.

    I checkd out the providers of the dest. server and it lools like ora is missing. I will install and it try asap again.

    ==> BUT at this point I have another, very important critical problem: I do not have a working project anymore because of another critical (from my point of view) VS/TFS Bug: Meanwhile my colleagues continued work and we ran into a situation where none of us has a working solution anymore. We have conflicts and everyboy another broken version of the project because of a bug that is documented there:

    http://connect.microsoft.com/VisualStudio/feedback/details/630108/issues-with-getting-latest#details

    https://connect.microsoft.com/VisualStudio/feedback/details/761216/tfs-getting-latest-in-ssdt-project-project-file-stays-old#details (this is my post)

    I know you are an authority in database projects / SSDT. This bug seems to be known for years and affects Database Projects as well as SSDT. But got never solved. WE tried VS2010 SP1 as well as VS 2012 RC with TFS 2010 and this bug is always present. Do you have any suggestion what we can do about it? We would love to use SSDT but can't use these great tools as long as we run into check in/out conflicts every day...  :(

    Regards, S.

    Wednesday, September 05, 2012 10:02 AM
  • Can you please post the fragment from the deployment script that causes this error?

    By the way, the fragment of the deployment script was this:

    INSERTINTO[UMS]..[UMS].[IFEMUR] () ... ()

    I wonder if I even if the linked server would work, get this part of the script ever to run, because it is some special call of a customers system, that we do not have in our developer environment... any ideas how to handle this?


              

    Wednesday, September 05, 2012 12:50 PM
  • hi, meanwhile I tried the following to workaround:

    I changed the procedure that is supposed to work with / call the objects on the remote ora server through the linkes server to be almost empty and just do nothing but exist to ensure all schema objects can be validated in the project.

    then I added a script fragment to the post deployment script to change the procedures content after deployment process. I hoped that, since post deployment scripts are not being validated, this would workaround the issue of an unknown oracle database call. it looks like this:

    EXEC dbo.sp_executesql @statement = @stmt 

    where @stmt is an ALTER Script command for the procedures body.

    This did  not help either. Still, when deploying, SQL Server seems to reject this script / linked server call. 

    There must be a more general concept to go around this, because in this case ,we are watching a very tiny fragment of a huge project, wehere a customer specific procedure inserts data to an oracle server, only one customer has. This can't mean, that all of your developer machines (and all of our other cusomers?) should install an Oracle Datanase server and create the required INSERT Table object to make SSDT work? I also can't just dismiss these parts from the project and declare them as customer specific code that lives only at this customer's DB, because it is called by our code, so to make the solution work, we have to include it, don't we?

    Regards, S

    Wednesday, September 05, 2012 2:18 PM
  • It's getting more and more weird...

    A possible solution might be to set the customer specific scripts to Build Action "None". As I expected, the deployment then runs without any errors, because the part that did not work is not deployed anymore. So far so good.

    On the other hand, now a procedure that previously has called the not anymore builded procedure, still works, but does not "know" the calles procedure anymore => you can not "Go to Definition" anymore (of course because it is not included in the build anymore) BUT these procedure calls are also not being thrown as an error and not even as a warning. I really wonder why because there is a schema.procedure called that is not included in the project. This does not even change if you completely exclude the calles procedure from the project.

    Really strange... but after all it solves my problem with customer specific procedures using customer specific linked servers...  :)

    Wednesday, September 05, 2012 3:30 PM