{"id":1976,"date":"2020-07-04T15:53:28","date_gmt":"2020-07-04T15:53:28","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1976"},"modified":"2020-07-04T15:55:31","modified_gmt":"2020-07-04T15:55:31","slug":"msg-7302-level-16-state-1-with-a-linked-server","status":"publish","type":"post","link":"http:\/\/www.theSQLReport.com\/?p=1976","title":{"rendered":"Msg 7302, Level 16, State 1 with a Linked Server"},"content":{"rendered":"

Problem:<\/strong><\/p>\n

A developer reach out to me about an error they were getting when attempting to run a stored procedure that used a linked server to pull data from an Oracle database using an Active Directory login.\u00a0 This was happen on the production database server, however this stored procedure worked on the development database server.<\/p>\n

Error Message:<\/strong><\/p>\n

Msg 7302, Level 16, State 1, Procedure \u2026, Line \u2026 [Batch Start Line ..]Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server “\u2026”.<\/p>\n

\"Msg<\/a><\/p>\n

Part of the problem when working with linked servers is that SQL Server stores some of its information in the Window Registry, and can not easily be verified.<\/p>\n

The provider for the Oracle OLE DB needs to have the \u201cAllowInProcess\u201d enabled, which again does this with as a DWORD Value in the registry.<\/p>\n

\""AllowInProcess\u201d<\/a><\/p>\n

Thankfully, I don\u2019t have to use the registry editor to find the value.\u00a0 There is a extended stored procedure that lets me view this.<\/p>\n

On my SQL Server 2016 development database server I ran the following:<\/p>\n

EXECUTE master.sys.xp_regread\r\n\u00a0\u00a0\u00a0 'HKEY_LOCAL_MACHINE',\r\n\u00a0\u00a0\u00a0 'SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\Providers\\OraOLEDB.Oracle\\',\r\n\u00a0\u00a0\u00a0 'AllowInProcess';<\/pre>\n
\"AllowInProcess\u201d<\/a><\/p>\n
Notice that the registry key is version dependent on the version of SQL Server running.\u00a0 I am able to see that with the value of \u201c1\u201d, that provider for the Oracle OLE DB \u201cAllowInProcess\u201d is enabled.<\/p>\n
However when I ran the same SQL on my production database server, I see that provider for the Oracle OLE DB \u201cAllowInProcess\u201d was not found.<\/p>\n
\"\u201cAllowInProcess\u201d<\/a><\/p>\n
Resolution:<\/strong><\/p>\n
To enable the \u201cAllowInProcess\u201d for the provider for the Oracle OLE DB, I ran the \u201csp_MSset_oledb_prop\u201d stored procedure:<\/p>\n
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1;\r\nGO<\/pre>\n
\"EXEC<\/a><\/p>\n
The developer tested & now will be able to use all its stored procures with that OLE provider.<\/p>\n
Please note not to just cut & past with the error, make sure that OLE DB provider in the error message is the same in the SQL that is run to enable the \u201cAllowInProcess\u201d.\u00a0 My first attempt was a lazy cut & paste from Google, and I enabled the \u201cAllowInProcess\u201d for \u201cMicrosoft.ACE.OLEDB.12.0\u201d provider.<\/p>\n
\"OLE<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Problem: A developer reach out to me about an error they were getting when attempting to run a stored procedure that used a linked server to pull data from an … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":1977,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-1976","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server"],"_links":{"self":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1976"}],"collection":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1976"}],"version-history":[{"count":2,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1976\/revisions"}],"predecessor-version":[{"id":1984,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1976\/revisions\/1984"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1977"}],"wp:attachment":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1976"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1976"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1976"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}