none
SQL Server 2019 snapshot agent error RRS feed

  • Question

  • Hello,

    I was trying to configure an SQL Server 2019 transactional replication, but the snapshot agent returns the following error:

    Error messages:
    Message: An unspecified error had occurred in the native SQL Server connection component.
    Stack:    at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
       at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowConnectionFailureException(CConnection* pNativeConnectionWrapper)
       at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.InitializeNativeBcpOutProvider(LogMessageCallback logMessageCallback, String strName, String strApplication, String strHost, String strServer, String strNetwork, Int32 encryptionLevel, String strDatabase, SecurityMode securityMode, String strLogin, SecureString strPassword, Int32 bcpBatchSize, Int32 loginTimeout, Int32 queryTimeout, Int32 packetSize, DeadlockPriority deadlockPriority, String strRowDelimiter, String strFieldDelimiter, Byte syncMethod, Boolean forceOdbcBcp, Boolean forceOleDbBcp, Boolean enableMultipleActiveResultSets, Boolean useReadPastHint, Boolean usePageLockHint, Boolean securePassword)
       at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.InstantiateBcpOutProviderWorker(String strBcpConnectionName)
       at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.InstantiateBcpOutProvider(String strBcpConnectionName)
       at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.Initialize()
       at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
       at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQL_REPL, Error number: MSSQL_REPL55012)
    Get help: http://help/MSSQL_REPL55012

    Any idea why?

    Best regards,

    José Cruz

    • Moved by Tom Phillips Friday, January 24, 2020 1:34 PM Replication question
    Saturday, December 14, 2019 11:46 AM

All replies

  • Hi José Cruz,

    Could you please share us more detailed error message about you issue? Please refer to Find errors with the Snapshot Agent.

    >> An unspecified error had occurred in the native SQL Server connection component.

    If SQL Server is 64-bit, your OLE DB driver also needs 64-bit. If it is 32-bit, SQL Server also needs 32-bit. You can download it from here.

    In addition, you could try to repair it by Control Panel-> Programs-> Programs and Features -> right-click Microsoft OLE DB Driver for SQL Server -> Repair.

    Here is a similar thread might helpful to you.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 16, 2019 7:52 AM
  • Hi Amelia,

    I've alreaddy read that thread.

    On the event viewer there's an error:

    Microsoft OLE DB Driver for SQL Server: Unable to load msoledbsqlr.rll due to either missing file or version mismatch. The application cannot continue.

    The file exists both on System32/1033 and SysWOW64\1033 (not sure if this is the place they should be).

    I've repaired the OLE DB instalation but the error remains.

    Best regards,

    José Cruz

    Monday, December 16, 2019 9:54 AM
  • Hi José Cruz,

    Could you please try to uninstall OLE DB Driver for SQL Server and reinstall it both x86 and x64 MSIs?

    If that doesn’t work, I recommend you to submit it to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server.

    This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 17, 2019 8:55 AM
  • Hi Amelia,

    I can confirm that I have the same problem. Tried re-installing both SQL Server and OLE DB Driver but with no success. I'm still getting the same error.

    The SQL installation is a clean one, only database engine and replication installed. I restored AdventureWorks2017 DB directly from its backup and set owner to it. Replication is configured then, with publisher and distributor on the same machine, which is my laptop. When I create a new Transactional Replication Publication, it fails at the Snapshot creation.

    I also gave permission to proper accounts for snapshot folder, with no end. Why an out-of-the-box installation and simple replication creation would cause this issue?

    Best Regards,

    Harun

    Tuesday, December 17, 2019 10:44 AM
  • Hello,

    I've the latest OLE DB driver installed. I've installed the x64 version. The x86 version does not install.

    Running the snapshot from the command line i get some more detail on the error, but didn't get me closer to the solution :-)

    2019-12-18 12:54:33.91 Command Text: select count_big(*) from sys.tables
    2019-12-18 12:54:33.91 Parameters:
    2019-12-18 12:54:33.91 [0%] The replication agent had encountered an exception.
    2019-12-18 12:54:33.91 Source: Replication
    2019-12-18 12:54:33.91 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentException
    2019-12-18 12:54:33.91 Exception Message: An unspecified error had occurred in the native SQL Server connection component.
    2019-12-18 12:54:33.91 Message Code: 55012
    2019-12-18 12:54:33.91  
    2019-12-18 12:54:33.91 Call Stack:
    2019-12-18 12:54:33.91 Microsoft.SqlServer.Replication.ReplicationAgentException: An unspecified error had occurred in the native SQL Server connection component.
    2019-12-18 12:54:33.91    at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
    2019-12-18 12:54:33.91    at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowConnectionFailureException(CConnection* pNativeConnectionWrapper)
    2019-12-18 12:54:33.91    at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.InitializeNativeBcpOutProvider(LogMessageCallback logMessageCallback, String strName, String strApplication, String strHost, String strServer, String strNetwork, Int32 encryptionLevel, String strDatabase, SecurityMode securityMode, String strLogin, SecureString strPassword, Int32 bcpBatchSize, Int32 loginTimeout, Int32 queryTimeout, Int32 packetSize, DeadlockPriority deadlockPriority, String strRowDelimiter, String strFieldDelimiter, Byte syncMethod, Boolean forceOdbcBcp, Boolean forceOleDbBcp, Boolean enableMultipleActiveResultSets, Boolean useReadPastHint, Boolean usePageLockHint, Boolean securePassword)
    2019-12-18 12:54:33.91    at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.InstantiateBcpOutProviderWorker(String strBcpConnectionName)
    2019-12-18 12:54:33.91    at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.InstantiateBcpOutProvider(String strBcpConnectionName)
    2019-12-18 12:54:33.91    at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.Initialize()
    2019-12-18 12:54:33.91    at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
    2019-12-18 12:54:33.91    at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper()
    2019-12-18 12:54:33.91 [0%] The replication agent had encountered an exception.
    2019-12-18 12:54:33.91 Source: Replication
    2019-12-18 12:54:33.91 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentException
    2019-12-18 12:54:33.91 Exception Message: An unspecified error had occurred in the native SQL Server connection component.
    2019-12-18 12:54:33.91 Message Code: 55012

    Best regards,

    José Cruz


    Wednesday, December 18, 2019 7:31 PM
  • Hi José Cruz,

    Sorry for not being able to help you.

    It seems this issue has happened in SQL Server 2019 RC: https://dba.stackexchange.com/questions/252194/replication-problem-in-sql-server-2019

    And this issue is not resolved.

    I recommend you submit this issue to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server.

    Best Regards,

    Amelia Gu

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 19, 2019 7:46 AM
  • In my case, I had the same error message.
    My error was that I had installed my new server (Windows 2019 Std) in English (Canada) and that when I updated the Snapshot via the agent, I always had the error "An unspecified error had occurred in the native SQL Server connection component. ".

    To correct the problem, I reinstalled my server in English (United States) and everything worked afterwards.

    Hope my case helps other people.

    Have a good day.
    Thursday, January 16, 2020 12:03 PM
  • Dear José Cruz,

    This appears to be a common issue.

    The advice you received about installing the latest OLE drivers is correct. However, even with the latest drivers installed the snapshot agent will still use the older version drivers that come with SQL Server 2019.

    This has to do with the Path variable in your environment. Have a look at System Properties, Advanced System Settings, Environment Variables, System variables, Path.

    Unless you have changed your defaults you will find %SystemRoot%\system32, %SystemRoot% at the top and C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\  further down.

    What you will not find is a path reference to %SystemRoot%\SysWOW64\ and %SystemRoot%\SysWOW64\1033 which is where the latest OLE drivers get installed on 64 bit systems. The driver files are msoledbsql.dll and msoledbsqlr.rll

    So, when the snapshot agent starts it will use the OLE driver files installed relative to its binary somewhere under the C:\Program Files\Microsoft SQL Server\ branch as it cannot find any others in the Path.

    When you insert %SystemRoot%\SysWOW64\ and %SystemRoot%\SysWOW64\1033 into your system Path variable (make sure this is above any references to C:\Program Files\Microsoft SQL Server\...) and the restart your SQL Server instance you should find that your problem goes away.

    All the best,

    Dan

    Friday, January 24, 2020 11:08 AM
  • OMG! You "£$&"£*% HERO!  I've spent 4 hours battling with this, adding those 2 EV's in the SYSTEM PATH (Not User Path EV) fixed it.  This needs fixing in the installer.  Thank you!!
    Thursday, January 30, 2020 4:29 AM
  • As Dan says adding %SystemRoot%\SysWOW64\ and %SystemRoot%\SysWOW64\1033  to the SYSTEM Path resolves this. Thanks
    Thursday, January 30, 2020 5:16 AM
  • I was unable to setup replication because of this problem.

    Updating the OLEDB drivers and adding these entries to the PATH fixed the problem.

    Thank you !

    Friday, February 28, 2020 2:02 PM
  • Path change fixed my problem - thanks :)

    Magnus Kristinsson

    Tuesday, April 7, 2020 9:55 AM
  • Fix works (change system path) but is a dangerous one.  Any program running under SYSTEM attempting to load a 64-bit dll from System32 could pick up a 32-bit one (with the same name) from SysWow64 and crash.

    Much simpler is to copy the folder 1033 (and its contents) from C:\Windows\System32 to C:\Program Files\Microsoft SQL Server\150\COM.  The problem appears to be loading msoledbsqlr.rll (not msoledbsql.dll).  (Works for a US English installation anyway)

    This works for me.  Try it - it's safer than playing with the system PATH.

    -----
    Graham



    • Edited by Graywing Wednesday, April 15, 2020 1:46 PM
    Wednesday, April 15, 2020 1:44 PM
  • Updating PATH as Dan68 advised, works! Thanks!
    Monday, May 18, 2020 9:50 AM
  • Thank you sir, I have spent a full day wrestling with this. Sorted now!
    Wednesday, June 17, 2020 4:50 PM