Msg 207, Level 16, State 1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Msg 207, Level 16, State 1

Author

Topic

shas2255
Starting Member

2 Posts

Posted - 2011-11-07 : 01:35:43

i am 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 04:30:16

have you included columns like Preliminary_Cause_of_Death inside derived table Changes? I think thats whats causing the error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shas2255
Starting Member

2 Posts

Posted - 2011-11-07 : 08:53:21

These are the automated provisioning scripts , we dont do anything in this, apart from calling the provisioning function.Tables are already present in source database and destination database is an empty databse.I am trying to sync source DB with empty Destination DB and at that time getting this exception .So please tell me how do i check these columns are included in derived table changes.?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources