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.