Jason567530


Hello all. I am a student attempting to run the 'Creating a Mobile Application with SQL Server Compact Edition' walkthrough on the MSDN site. Unfortunately I am running into a problem I can't seem to get through...

I am executing the following steps in the walk-through:

Create the publication snapshot

  1. In SQL Server Management Studio, in Object Explorer, expand the (local) computer node.
  2. Expand the Local Publications folder, select the publication name, right-click SQLMobile, and then click View Snapshot Agent Status.
  3. In the View Snapshot Agent Status dialog box, click Start.

Make sure that the snapshot job has succeeded before you continue.

I have checked all the services and permissions, IDs, etc. on my box. They all look good.

I am running WinXP with SP2. The version of SQL Server 2005 is Developer Edition. I have installed SQL Server SP2 to see if that would fix my problems but it didn't...

The 'Snapshot Agent Status' gets as far as 18% and then I get the following error:

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

This is the full text of the error message:

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)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider..ctor(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)
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.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.WorkerThread.AgentThreadProc()
at Microsoft.SqlServer.Replication.MainWorkerThread.AgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQL_REPL, Error number: MSSQL_REPL55012)
Get help:
http://help/MSSQL_REPL55012

Clicking the link from within the Replication Monitor takes you here:

http://www.microsoft.com/products/ee/transform.aspx ProdName=Microsoft+SQL+Server&ProdVer=09.00.0758.00&EvtSrc=MSSQL_REPL&EvtID=MSSQL_REPL55012

and it is not helpful at all. In addition, a Google search for 'MSSQL_REPL55' returned no hits either.

Any help is greatly appreciated. Thanks all.




Re: MSSQL_REPL55012: An unspecified error had occurred in the native SQL Server connection component.

enaksanamun


Hello Jason,

You should configure Distributor, Publisher and Subscriber frame first.

I think you are trying to publish a publication before configuring these components

Can you inform me if you configured Distributor and Publisher already

Ekrem Onsoy
MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE





Re: MSSQL_REPL55012: An unspecified error had occurred in the native SQL Server connection component.

Jason

Hi Ekrem,

As I mentioned, I am a student and this is my first attempt at working with SQL Server. I am not familiar with what you mean by "configure Distributor, Publisher and Subscriber frame first." I can tell you that I am closely reading and executing each step of the walkthrough (link provided in my original post) in the order it is written.

So far, I have done the following in this order:

* Create a database and populate with data (Successful)

* Prepare the Server for Publishing Data

- Create the snapshot user account (Successful)

- Create the snapshot folder (Successful)

- Create a publication (Successful)

* Set permissions

- Set database permissions (Successful)

- Grant publication access (Successful)

* Create the Publication Snapshot

- Create the publication snapshot (Error occurs at this step)

If you think I should be completing/configuring something else, please let me know. I apologize in advance, but you'll need to explain to me what to do. Thanks again for your help Ekrem. It is much appreciated.






Re: MSSQL_REPL55012: An unspecified error had occurred in the native SQL Server connection component.

Raymond Mak

Hi Jason,

According to the stack trace, the snapshot agent is having difficulty establishing a native SNAC OLEDB connection for performing the bcp out operation. And given the lack of details about the failure, chances are good that this is due to some machine configuration issues such as mismatched dlls etc. Unfortunately, such failures are extremely difficult to diagnose even with help from Microsoft customer support although I can suggest the following venues of investigation which should hopefully narrow down the problem somewhat:

1) Check the version stamp of %windir%\system32\sqlncli.dll, it should be 2005.90.3042(or above) if you have SP2 installed correctly. You may also consider running regsvr32 /u sqlncli.dll followed by regsvr32 sqlncli.dll and see if that resolves the issue.

2) Force the snapshot agent to perform bcp operation on a single thread. To do this, you can either add the "/MaxBcpThreads 1" option to the command line of the snapshot agent job. Or, you use the snapshot agent command line and run the %ProgramFiles%\Microsoft SQL Server\90\com\snapshot.exe executable directly with the "/MaxBcpThreads 1" option.

3) You may also want to try specifying the unofficial "/ForceOdbcBcp" option to the snapshot agent and see if the problem is limited to SNAC OLEDB

4) Is the machine running some kind of non-Microsoft firewall If so, you may want to temporarily disable it and see if that resolves the issue.

5) Is SynAttackProtect enabled via the registry If so, you may want to disable that and see if that resolves the issue.

-Raymond





Re: MSSQL_REPL55012: An unspecified error had occurred in the native SQL Server connection component.

Jason

Raymond Mak wrote:

1) Check the version stamp of %windir%\system32\sqlncli.dll, it should be 2005.90.3042(or above) if you have SP2 installed correctly. You may also consider running regsvr32 /u sqlncli.dll followed by regsvr32 sqlncli.dll and see if that resolves the issue.

The file version is 2005.90.3042.0 for sqlncli.dll on my computer. I also successfully unregistered and registered the dll. Another attempt to the Snapshot Agent resulted in the same error.

Raymond Mak wrote:

2) Force the snapshot agent to perform bcp operation on a single thread. To do this, you can either add the "/MaxBcpThreads 1" option to the command line of the snapshot agent job. Or, you use the snapshot agent command line and run the %ProgramFiles%\Microsoft SQL Server\90\com\snapshot.exe executable directly with the "/MaxBcpThreads 1" option.

Sorry Raymond, but I am not sure if I did this correctly. I could not find anyway to add the flag within the Management Studio. From the command prompt, I ran the following command: snapshot /Publication Create_SQLMobile_Publication.sql /MaxBcpThreads 1

and it produced the following output:

2007-04-14 03:19:24.09

2007-04-14 03:19:24.09 Microsoft (R) SQL Server Snapshot Agent

2007-04-14 03:19:24.09 [Assembly Version = 9.0.242.0, File Version = 9.00.3042.00]

2007-04-14 03:19:24.09 Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.

2007-04-14 03:19:24.10 The timestamps prepended to the output lines are expressed in terms of UTC time.

2007-04-14 03:19:24.10 User-specified agent parameter values:

2007-04-14 03:19:24.10 --------------------------------------

2007-04-14 03:19:24.10 -Publication Create_SQLMobile_Publication.sql

2007-04-14 03:19:24.10 -MaxBcpThreads 1

2007-04-14 03:19:24.10 --------------------------------------

2007-04-14 03:19:24.10 Connecting to Distributor 'LAPTOP'

2007-04-14 03:19:24.26 The replication agent had encountered an exception.

2007-04-14 03:19:24.26 Source: Replication

2007-04-14 03:19:24.26 Exception Type: Microsoft.SqlServer.Replication.ConnectionFailureException

2007-04-14 03:19:24.26 Exception Message: Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.

2007-04-14 03:19:24.26 Message Code: 18452

2007-04-14 03:19:24.26

I am attributing the exception that was thrown to the fact that I may not have run the snapshot agent correctly. Just in case though, here is the full Publication script below:

Create_SQLMobile_Publication.sql wrote:

/****** Scripting replication configuration for server LAPTOP. Script Date: 04-03-2007 1:02:21 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server LAPTOP as a Distributor. Script Date: 04-03-2007 1:02:21 AM ******/
use master
exec sp_adddistributor @distributor = N'LAPTOP', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @data_file_size = 4, @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\LAPTOP\snapshot', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', '\\LAPTOP\snapshot', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = N'LAPTOP', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'\\LAPTOP\snapshot', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

use [SQLMobile]
exec sp_replicationdboption @dbname = N'SQLMobile', @optname = N'merge publish', @value = N'true'
GO
-- Adding the merge publication
use [SQLMobile]
exec sp_addmergepublication @publication = N'SQLMobile', @description = N'Merge publication of database ''SQLMobile'' from Publisher ''LAPTOP''.', @sync_mode = N'character', @retention = 14, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'false', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = null, @publication_compatibility_level = N'80RTM', @replicate_ddl = 0, @allow_subscriber_initiated_snapshot = N'false', @allow_web_synchronization = N'true', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'publisher', @automatic_reinitialization_policy = 0
GO


exec sp_addpublication_snapshot @publication = N'SQLMobile', @frequency_type = 4, @frequency_interval = 14, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 500, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'LAPTOP\snapshot_agent', @job_password = null, @publisher_security_mode = 1


use [SQLMobile]
exec sp_addmergearticle @publication = N'SQLMobile', @article = N'FlightData', @source_owner = N'dbo', @source_object = N'FlightData', @type = N'table', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000030034FF1, @identityrangemanagementoption = N'manual', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = null, @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0
GO


use [SQLMobile]
exec sp_addmergearticle @publication = N'SQLMobile', @article = N'MembershipData', @source_owner = N'dbo', @source_object = N'MembershipData', @type = N'table', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000030034FF1, @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = null, @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0
GO

Raymond Mak wrote:

3) You may also want to try specifying the unofficial "/ForceOdbcBcp" option to the snapshot agent and see if the problem is limited to SNAC OLEDB

As I mentioned in part 2, I don't think I know how to do this...

Raymond Mak wrote:

4) Is the machine running some kind of non-Microsoft firewall If so, you may want to temporarily disable it and see if that resolves the issue.

Yes, it is running Symantec Client firewall. Though even with it disabled (which I always try to remember when running things like this for school) the error persists.

Raymond Mak wrote:

5) Is SynAttackProtect enabled via the registry If so, you may want to disable that and see if that resolves the issue.

I don't understand this. I type 'regedit' and did a full search for 'SynAttackProtect' and 'SynAttack' but no results were found.

Thanks Raymond. I know what you are saying about the problem stemming from my local configuration but I hope you'll be able to follow up and provide some more helpful assistance. Thanks again.





Re: MSSQL_REPL55012: An unspecified error had occurred in the native SQL Server connection component.

Raymond Mak

Hi Jason, you can get the command line for running the snapshot agent by doing a "select * from msdb..sysjobsteps", the row that you are looking for is the one with Subsystem = "Snapshot". If there is only one publication on your system, you should only have one row for that. Once you have the snapshot agent command line, you can start adding the other options that I mentioned. You may also want to use SQL Profiler to find out whether the snapshot agent actually manages to make a BCP connection to the local server. You may also want to see if you can use %ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe to connect to your server. Don't worry about the SynAttackProtect stuff, it is probably not the problem.

-Raymond