Getting 'Invalid column name' exception during provision

Question

when i tried to sync two sql 2008 r2databases with the help of sync framework 2.1  getting this sql exception during provision

System.Data.SqlClient.SqlException was caught Message=Invalid column name 'Name'. Invalid column name 'Address'. Invalid column name 'City'. Invalid column name 'Contact_Number'. Source=.Net SqlClient Data Provider ErrorCode=-2146232060 Class=16 LineNumber=16 Number=207 Procedure=patient_bulkinsert Server=local\sqlexpress State=1 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Synchronization.Data.SqlServer.SqlSyncProcedureHelper.CreateBulkInsert(SqlTransaction trans, DbSyncCreationOption option) at Microsoft.Synchronization.Data.SqlServer.SqlSyncTableProvisioning.Apply(SqlTransaction trans) at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.ApplyScope(SqlConnection connection) at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.ApplyInternal(SqlConnection connection) at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.Apply() at AmisSync.frmAmisSync.Provision() in C:\Users\admin\Documents\Visual Studio 2010\Workfolder\AmisSync\AmisSync\frmAmisSync.cs:line 106 InnerException: 

These are four columns after the Primary key and someone told me  try  to make primary key field as 1st column filed in the table and it worked for some tables...Any help appreciate..thanks

Answer 1

can you post the schema for the table encountering an error?

try generating the script for provisioning via the SqlSyncScopeProvisioning.Script() method to get the sql statement generated by the provisioning api, then run them manually in Sql Management Studio to see where and why its failing.

Answer 2

iam trying to sync two databases with sync framework.And when i tried to provision my database iam getting this System.Data.SqlClient.SqlException
Message=Invalid column name 'patient_external_id'.
Invalid column name 'Preliminary_Cause_of_Death'.
Invalid column name 'Patient_Death_Date'.
Invalid column name 'patientreminder'.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
LineNumber=16
Number=207
Procedure=patient_bulkinsert
Server=local\sqlexpress
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Synchronization.Data.SqlServer.SqlSyncProcedureHelper.CreateBulkInsert(SqlTransaction trans, DbSyncCreationOption option)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncTableProvisioning.Apply(SqlTransaction trans)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.ApplyScope(SqlConnection connection)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.ApplyInternal(SqlConnection connection)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.Apply()
at AmisSync.frmAmisSync.Provision() in C:\Users\admin\Documents\Visual Studio 2010\Workfolder\AmisSync\AmisSync\frmAmisSync.cs:line 106
InnerException:


provision Script was given below.

CREATE PROCEDURE [patient_bulkinsert]
@sync_min_timestamp BigInt,
@sync_scope_local_id Int,
@changeTable [patient_BulkType] READONLY
AS
BEGIN
-- use a temp table to store the list of PKs that successfully got updated/inserted
DECLARE @changed TABLE ([ID] int, PRIMARY KEY ([ID]));

SET IDENTITY_INSERT [patient] ON;
-- update/insert into the base table
MERGE [patient] AS base USING
-- join done here against the side table to get the local timestamp for concurrency check
(SELECT p.*, t.local_update_peer_timestamp FROM @changeTable p LEFT JOIN [patient_tracking] t ON p.[ID] = t.[ID]) AS changes ON changes.[ID] = base.[ID]
WHEN NOT MATCHED BY TARGET AND changes.local_update_peer_timestamp <= @sync_min_timestamp OR changes.local_update_peer_timestamp IS NULL THEN
INSERT ([mrn], [contactid], [dob], [ssn], [weight], [height], [occupation], [sex], [married], [refphyid], [priphyid], [guarantorid], [emgcnt1], [emgcnt2], [emgphone1], [emgphone2], [priphyname], [refphyname], [verificationstatus], [upddate], [updperson], [patienttype], [blockpatientapt], [alert], [last_statement_print_date], [last_statement_print_by], [last_statement_mail_date], [last_statement_mail_by], [allocationsetid], [Claim_Class], [blockstatement], [blockstatement_reason], [Language], [Race], [Ethnicity], [Death_date], [Blockappointment_reason], [Blockappointment_note], [ID], [Patient_Death_Date], [Preliminary_Cause_of_Death], [patientreminder], [patient_external_id]) VALUES (changes.[mrn], changes.[contactid], changes.[dob], changes.[ssn], changes.[weight], changes.[height], changes.[occupation], changes.[sex], changes.[married], changes.[refphyid], changes.[priphyid], changes.[guarantorid], changes.[emgcnt1], changes.[emgcnt2], changes.[emgphone1], changes.[emgphone2], changes.[priphyname], changes.[refphyname], changes.[verificationstatus], changes.[upddate], changes.[updperson], changes.[patienttype], changes.[blockpatientapt], changes.[alert], changes.[last_statement_print_date], changes.[last_statement_print_by], changes.[last_statement_mail_date], changes.[last_statement_mail_by], changes.[allocationsetid], changes.[Claim_Class], changes.[blockstatement], changes.[blockstatement_reason], changes.[Language], changes.[Race], changes.[Ethnicity], changes.[Death_date], changes.[Blockappointment_reason], changes.[Blockappointment_note], changes.[ID], changes.[Patient_Death_Date], changes.[Preliminary_Cause_of_Death], changes.[patientreminder], changes.[patient_external_id])
OUTPUT INSERTED.[ID] INTO @changed; -- populates the temp table with successful PKs

SET IDENTITY_INSERT [patient] OFF;
UPDATE side SET
update_scope_local_id = @sync_scope_local_id,
scope_update_peer_key = changes.sync_update_peer_key,
scope_update_peer_timestamp = changes.sync_update_peer_timestamp,
local_update_peer_key = 0,
create_scope_local_id = @sync_scope_local_id,
scope_create_peer_key = changes.sync_create_peer_key,
scope_create_peer_timestamp = changes.sync_create_peer_timestamp,
local_create_peer_key = 0
FROM
[patient_tracking] side JOIN
(SELECT p.[ID], p.sync_update_peer_timestamp, p.sync_update_peer_key, p.sync_create_peer_key, p.sync_create_peer_timestamp FROM @changed t JOIN @changeTable p ON p.[ID] = t.[ID]) AS changes ON changes.[ID] = side.[ID]
SELECT [ID] FROM @changeTable t WHERE NOT EXISTS (SELECT [ID] from @changed i WHERE t.[ID] = i.[ID])
END

Thanks in advance.

Answer 3

have you previously provisioned the table on another scope? check the patient_BulkType  if the column definition is invalid.