In this project I m using SQL Server 2005 express edtion.I can run select queries but I couldnt run insert and update query.Is there any protection for adding data to the Sql Server express edition ?
thanks.
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a database of about 300,000 records.
The records were imported from a csv file.
One of the fields is duration.
The data in duration are like ths:
1 second: 0:01
26 minutes: 26:00
If i put the format of the field as time, the data are messed up.
0:01 becomes 1 minute.
26:00 becomes 1 day 2 hours.
I currently have duration as text.
How can i use sql or visual basic to replace all the data so that they can have the format "00:00:00"?
(0:01 becomes 00:00:01, 26:00 becomes 00:26:00)
I need the duration in time format in order to be able to make sum calculations.
I will be doing the same calculations every month so i need the above procedure to be able to execute it every time i need to.
Thank you in advace
George
There are 4 tables in a database. such as university, student, professor and worker. university table contains fields named- university_name, student_name, professor_name and worker_name. and student, professor and worker tables contain person_id and person_name.
Now, I want to replace the student_name, professor_name and worker_name data from university table with student table's person_id, professor table's person_id and worker table's person_id.
I am working with a database containing time series data. In many, cases there is missing data. For example, while there might be a value for 2001-01-01T23:00:00, there is none for 2001-01-01T23:0100 (one minute later). I would like to replace the missing data with data from the previous record (if the previous record is the same date). Is that possible with T-SQL?
View 6 Replies View RelatedI would like to replace data of some tables from STG to DEV database daily using SSIS package. Should I use "Transfer SQL Server Objects Task" to do that? Thanks.
View 3 Replies View RelatedHello guys,
I would like to execute a t-sql query and replace a string by another in all records.
I did this :
UPDATE myTable
SET myColumn = replace(myColumn,'old string','new string')
The error I get is :
Argument data type ntext is invalid for argument 1 of replace function.
Thank you very much for any help!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
Hello,
I have a table in witch i would like to replace all the data form one columm following a criteria.
The criteria is:
Where 1010222222 will be 0020222222
So i want to make a script that enables me to substitute all the data in that columm that begin by "101" for "002"
Any help?
Thanks
Hi, I was wondering if it is posible as in derived columns to replace an input column with the output one in Data Conversion?
What I can see that when a data conversion is done, only we can do it in a new column and not in the same one we are using as input.
Do you know if this it is posible?}
Thanks for your help!
Beli
I am working with a database named €śDocuments€? that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:
1 = Text
2 = Report
3 = Background
4 = Index
I would like to create a new column named €śDocType€? in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I). I was able to easily create the new column and cast the data type from integer to varchar:
--CREATE NEW COLUMN €śDocType€? WITH VARCHAR DATATYPE
ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL
Go
--UPDATE NEW COLUMN WITH CAST STRING
UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))
Go
But I have problems with the REPLACE method for replacing the numbers with letters. First I tried this based on the examples in MSDN Library:
--REPLACE NUMBERS WITH LETTERS
UPDATE Fulldocuments REPLACE (DocType,"1","T")
Which produced an error message: €śIncorrect syntax near 'REPLACE'.€?
Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:
UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")
Which produced the same error message: €śIncorrect syntax near 'REPLACE'.€?
I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.
What is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.
I currently have a LoginStatus and LoginName control on my asp.net 2.0 master page. When successfully logged in a message displays "You are logged in as" LoginName. Since I am using the user's e-mail address as the UserName the message will display as "You are logged in as johndoe@foo.com".
What I want to do is have the message display as "You are logged in as " FirstName LastName. FirstName and LastName are two new fields that I added to the table, aspnet_Users.
What I have done is to use the FormView control to display FirstName and LastName; however this always displays the first record in the aspnetdb database.
Is there a cleaner way (than the FormView control) to display the requested data, using the currently logged in user? I have access to Microsoft Expression Web and Visual Studio 2005, but I prefer Expression Web.
Thank you in advance
Hi I have a table named UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out
View 4 Replies View RelatedI am writing an SQL query to find an replace data in a column. I have a table that is filled with
C:usersXXXXappdata
C:usersYYYYappda
C:usersZZZZZZappdata
I would like to replace the c:usersXXXXX part with %userprofile%
The end result would be %userprofile%appdata
I know how to dot the replacement in powershell. it's quite easy
-replace "c:users[^]+","%userprofile%"Â Â
Basically how would transfer this into SQL...
Hello,
I have a simple question. Is it at all possible to replace columns which has nulls with blank spaces for a float data type column.
The columns has null values( written)) in it in some rows and has numbers in other rows . I want to remove nulls before copying it to another file.
Thanks
I would like to replace data of some tables from STG to DEV database daily using SSIS package. Should I use "Transfer SQL Server Objects Task" to do that? Thanks.
View 5 Replies View RelatedW2k3 server, SQL 2005.
@@version = Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Standard Edition on Windows NT 5.2
(Build 3790: Service Pack 1)
I have my first SSIS package almost working, but I'm having an odd problem and can't find any information to help resolve it.
I'm importing from a flat file (csv) to an existing table (append). I've got a Derived Column transformation in the middle to do some data cleanup. It's all working except for one little problem...
One of the transformations is 'REPLACE([Column 3],"^","; ")', output to a new column. (The input file has a field that uses carets as delimiters between an unknown number of items; I'm changing that to semicolons for easier reading.) Not all rows have data in this column, some will have one item, some will have multiple items.
The REPLACE works except that it fills in repeated data for all the blank rows.
Example:
Incoming data is:
1 Smith,Jane^Jones,Jane
2 Brown,John
3
4 Adams,James^Adams,Jim
5
6 White,Debra
Data inserted into the table is:
1 Smith,Jane; Jones,Jane
2 Brown,John
3 Brown,John
4 Adams,James; Adams,Jim
5 Adams,James; Adams,Jim
6 White,Debra
I've tried to use a Conditional to skip the empty rows, but I can't get that working at all (get syntax errors no matter what I put in).
Any suggestions on how to fix this would be most appreciated!
Thank you.
How I can measure the volume of data created temporarily to replace usage of physical tables in an SQL query.
View 1 Replies View RelatedI need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
View 5 Replies View RelatedI have an Excel add-in that connects to a SQL Server Express 2005database. I've decided to create a configuration piece for this add-inin Visual Studio 2005 Express. I added a data connection using the dataconnection wizard and all appeared to go well. Anyways when I attemptto open SQL Server Express to administer the database, it was corruptedand I had to restore it.I eventually got it to work correctly (I'm pretty sure I followedpretty much the same steps as before), but I was just wondering ifanyone had experienced problems like this? I find it a bit scary thatit may be that easy to corrupt the database by just creating a dataconnection.
View 2 Replies View RelatedHi -
I've never used SQL for anything but simple copies and queries, and now I need to do something that's probably simple, as well, but I don't know how to start. I need to update values in a text field with new values from another table. I can do it individually with an update statement (Set [field1] = 'newvalue' where [field1] = 'oldvalue'). But I have 400 different values and a bunch of different tables that need to be updated, and can't imagine that's the only solution. I can make up a simple table that will have all the old values in one column, and the new values in the next. I need a statement that look at the old value, then fetch the new value from the table I made, and replace the old value with the new.
I've looked at various help files and tried to search for a solution elsewhere, but I'm not coming up with anything. I guess I just don't know where to look. Thanks in advance for any help you can give me.
Rebekkah
I have a laptop and a desktop that I both installed VWD and SQL Express. from my laptop, i see my laptop's name in the Server Name under data connections, but on the desktop, I only see an older Win2K Server with SQL 2000 in the list. Anyone know why my desktop doesn't show it's NAME?I can create new tables, users and all that stuff from the Management area. Thanks
View 3 Replies View Related
Hi There,
Could someone please tell me why I am getting the above error on this code:
select (replace
(replace
(replace
(replace (serviceType, 'null', ' ')
, '<values><value>', ' ')
, '</value><value>', ',')
, '</value></values>', ' '))
from credit
serviceType (text,null)
Thanks,
Rhonda
Hi,
I'm trying to learn some VB programming with the VB 2005 Express Absolute Beginner Series video tutorials (which I think is great) and have come across a problem that I can't solve.
When I follow the instructions in Lesson 9 (Databinding Data to User Interface Controls) my application will display the data from the database correctly and I can edit it (and as long as the debugger is running the data remains changed). However, the changes won't propagate back to the database. I don't get any error messages but after I edit the data, save (with the save button on the BindingNavigator toolbar), and end debugging the data in my database remains unchanged. When I use a MessageBox to show how many rows where edited/updated in the
Me.myTableTableAdapter.Update(Me.myDatabaseDataSet.myTable)
I get the correct number back. I'm sure the problem is not due to coding errors since I've also tried running the accompanying Lesson 9 project file that can be downloaded from MSDN and the problem persists.
I'm using Windows XP SP2, SQL Server 2005 Express Edition and VB 2005 Express Edition. I've tried installing SQL Server 2005 Express with a number of different settings, including default settings, but it doesn't make any difference.
Would greatly appreciate any feedback on this as I'm keen to resolve this problem so I can get on with the next tutorial lesson.
Thanks,
Ieyasu
Hi all,
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
Your prompt response is greatly appreciated.
Thanks!!
Tram
This problem only occurs after deployment, not when debugging. In the table I am having a problem with I have an ID field designated as the primary key with the identity increment set to 1 and the identity seed set to 1. There is no data in the table when deployed. I can add records to the datagridview control but when I try updating the dataset I get this error indicating that the ID field already has a value of 1 present. If I close the application and re-start it, the exception no longer occurrs when I update the dataset. I am including the code to update the tables incase something is wrong there. Any suggestions?
Private Sub UpdateMemberTable()
Me.Validate()
Me.TblMembersBindingSource.EndEdit()
Me.TblMembersTableAdapter.Update(Me.ChurchFamilyDataSet.tblMembers)
Me.TblMemberDependentsBindingSource.EndEdit()
Me.TblMemberDependentsTableAdapter.Update(Me.ChurchFamilyDataSet.tblMemberDependents)
Me.TblMemberContributionsBindingSource.EndEdit()
Me.TblMemberContributionsTableAdapter.Update(Me.ChurchFamilyDataSet.tblMemberContributions)
End Sub
String or binary data would be truncated. I get this error when entering data using sql server management studio express.
I am not running a sql to insert or update the table. This is through the EDI.
The data type is varchar(100). I enter one character and it errors on me. So this isn't a string being too long problem.
Any ideas?
After my upgrade from MSDE SP 4 to SQL express (backup and restore) my application will not see the data any more.
If I use my application (VB Dot.NET 1.x ; DB= SQL Express) to add new data (rows) this new rows are visible.
Both old and new datas are seen in SQL management studio express.
If i add rows with the SQL management studio express the appliction will not see this rows.
In both cases I connect with sa - account. (Administrator even doesn't work). I search for any restrictions on row basis but i wonder why the management tool display all rows.
Any ideas!
Hi all,
We have access database with lots of data in it. I want to move all the dat to the new database which has completely differenet database structure (e.g different tables, different rows).
What I was thinking is that there are 2 ways. one is manually, which will take long tome probably, and the other is to rum queries that will move the data from access to the new sql express. The question is how to that. I know that I used onw CSV files exported for ms access, but now the queries will be much more complex and I will use more then one table for te query.
How can I query from 2 different databases, which from one I read data(access), and from the other I write data (express)?
Thanks alot.
I have SQL Server Express installed on my local development machine and on a remote server. I have identical databases set up on each. How can I copy the data from one to the other? How can I copy the data from one to SQL Server 2005? And can I create an Access database from SQL Server Express? Diane
View 3 Replies View RelatedI have SQL Server Express installed on my local development machine
and on a remote server. I have identical databases set up on each and I'm trying to keep them co-ordinated to make testing easier.
How can I copy the data from one to the other? How can I copy the data
from one to SQL Server 2005? And can I create an Access database from
SQL Server Express?Diane
Hi,
I have sucessfully used the Web Data Administrator on another machine using msde, but having trouble with sqlExpress. Can anyone help answer all or some of the following:
1- does the Web Data Administrator work with sqlExpress ?
2- I'm not sure I have sql server authenitication or windows authentication set for sqlExpress ? How can I check ? I need to have sql authentication.
3- I used to use access 2000 to manage/setup my sql server databases and found this the fastest and easiest method. Does access 2003 still have the same support for this ?
4- Is there anyone who knows when the free microsoft sql express admin tool will be available ? I think its called 'XS' or something.
any answers welcome.
Introduction: Data Synchronization in SQL Express
Data synchronization feature is available only in the SQL Server. In order to mimic that feature in SQL Express, currently we are using TableDiff utility method. This document proposes a new Stored Procedure method to speedup the entire data synchronization process as well as overcome some issues found in TableDiff utility method.
Data Synchronization using TableDiff utility method:
We can use TableDiff utility to generate a Transact-SQL script (containing delete/insert/update statements) to fix discrepancies at the destination server to bring the source and destination tables into convergence. Since this utility compare one table at a time, we need to call it in a loop in case we have N number of tables to synchronize. Within the loop, we have to accumulate (i.e., append) the generated Transact-SQL script into a local file (say CompleteFixSQL.sql). At end of the loop, we have a complete script file that need to be executed at destination server. sqlcmd utility can be used to run that script file (CompleteFixSQL.sql) into the destination server to bring the source and destination tables into convergence.
However, there are some drawbacks using these utilities
•Both TableDiff & sqlcmd utilities are external application need to be called from client application code. TableDiff need to be called N times if we have N number of tables to be synchronized which incurs I/O overhead.
•Sqlcmd utility executes statements that are contained in the CompleteFixSQL script one by one in a sequence manner which is a time consuming process in case we have large data to be synchronized at destination.
•TableDiff utility has some limitations. It would not generate FIX script file for LOB datatypes such as text, ntext & image.
Data Synchronization using Stored Procedure:
A Stored Procedure (SP) can be used to compare the difference between source and destination tables and then synchronize the destination tables with source table data. To bring the source and destination tables into convergence,
1.Find the records that need to be deleted from Destination database table
2.Find the records that need to be inserted into Destination database table
3.Find the records that need to be updated in the Destination database table
Subsequently, we have to execute delete, insert and update statements in the destination database for the records that are found in the above steps 1, 2 & 3 respectively.
The advantages of this SP method over TableDiff method are
•The stored procedure is already compiled and stored within the destination database. Client application code is just need to call this SP using the connection string
•Both table Compare & Synchronization will be done at a single query(one per each delete, insert & update)
•Records are processed (deleted, inserted & updated) in bulk manner.
•LOB datatypes are supported
Step 1: Records to be deleted from Destination database table
•Select the records that does not exist in Source database table, but exist in the Destination database table
•Then delete them from the Destination database table
delete from
DestinationDB.dbo.TableName DestinationDBTable
where
not exists
(select
1
from
SourceDB.dbo.TableName SourceDBTable
where
SourceDBTable.PrimaryColumnName1 = DestinationDBTable.PrimaryColumnName1 and
SourceDBTable.PrimaryColumnName2 = DestinationDBTable.PrimaryColumnName2 and
...
SourceDBTable.PrimaryColumnNameN = DestinationDBTable.PrimaryColumnNameN
)
If the table contains an Identity column then we can simply use that column rather than primary key column in the join condition of the WHERE clause. This will reduce the size of the join condition especially when the table having composite primary keys and an Identity column. Also it will improve the performance of the delete statement
delete from
DestinationDB.dbo.TableName DestinationDBTable
where
not exists
(select
1
from
SourceDB.dbo.TableName SourceDBTable
where
SourceDBTable.IdentityColumn = DestinationDBTable.IdentityColumn
)
Step 2: Records to be inserted into Destination database table
•Select the records that are exist in the Source database table, but does not exist in the Destination database table
•Then insert them into the Destination database table
insert into
DestinationDB.dbo.TableName DestinationDBTable
(ColumnList)
select
SourceDBTable.Columnlist
from
SourceDB.dbo.TableName SourceDBTable
where
not exists
(select
1
from
DestinationDB.dbo.TableName DestinationDBTable
where
DestinationDBTable.PrimaryColumnName1 = SourceDBTable.PrimaryColumnName1 and
DestinationDBTable.PrimaryColumnName2 = SourceDBTable.PrimaryColumnName2 and
...
DestinationDBTable.PrimaryColumnNameN = SourceDBTable.PrimaryColumnNameN
)
Column with TimeStamp datatype should be excluded from the Column list of the above insert statement as we cannot explicitly set values for TimeStamp column.
As specified in the Step 1 we can use identity column rather than primary key column as follows
insert into
DestinationDB.dbo.TableName DestinationDBTable
(ColumnList)
select
SourceDBTable.Columnlist
from
SourceDB.dbo.TableName SourceDBTable
where
not exists
(select
1
from
DestinationDB.dbo.TableName DestinationDBTable
where
DestinationDBTable.IdentityColumn = SourceDBTable. IdentityColumn
)
If the table having identity column then the above insert statement must be enclosed by the “set identity_insert on/off� as follows
set identity_insert TableName On
... above insert statement
set identity_insert TableName off
Step 3.Records to be updated in the Destination database table
•Select the records that are differ from Source & Destination database table
•Then update them in the Destination database table with the source database table data
update
TableName
set
ColumnName1 = SourceDBTable.ColumnName1,
ColumnName2 = SourceDBTable.ColumnName2,
...
ColumnNameN = SourceDBTable.ColumnNameN
from
DestinationDB.dbo.TableName DestinationDBTable,
(
select
max(TableName) as TableName, columnlist
from
(
select
'SourceTableName' as TableName, columnlist
from
SourceTableName
union all
select
'DestinationiTableName' as TableName, columnlist
from
DestinationTableName
) AliasName
group by
columnlist
having
count(*) = 1
and max(TableName) = 'SourceTableName'
) SourceDBTable
where
SourceDBTable.PrimaryColumnName1 = DestinationDBTable.PrimaryColumnName1 and
SourceDBTable.PrimaryColumnName2 = DestinationDBTable.PrimaryColumnName2 and
...
SourceDBTable.PrimaryColumnNameN = DestinationDBTable.PrimaryColumnNameN
Column with TimeStamp datatype should be excluded from the SET clause of the above update statement as we cannot explicitly set values for TimeStamp column.
Column with LOB datatypes (Text,nText & Image) should be converted to respective Large Value datatypes[Varchar(max),nVarchar(max) & varbinary(max)] from the queries that are combined by UNION ALL of the above statement. This is because the UNION ALL causes the sorting mechanism which prohibits LOB datatypes.
As specified in the Step 1 & 2 we can use identity column rather than primary key column in the WHERE clause of the update statement.
Stored procedure for Data Synchronization:
The below Data Synchronization - Beta Version - Script contains following view and stored procedures (SP) to implement the Data Synchronization
1.v_DTS_ColumnInformation
2.stp_DTS_GetCommaSeperatedColumnString
3.stp_DTS_GetIdentityOrPrimaryKeyColumnDetails
4.stp_DTS_SetDestinationColumnWithSourceColumnString
5.stp_DTS_DataSynchronization
v_DTS_ColumnInformation: This view will be used to populate the column details such as Data Type, Primary Key, Null constraint, Identity property, Column size constraint (Length for character datatype, Precision and scale of number datatype)
stp_DTS_GetCommaSeperatedColumnString: This SP generates various strings comma separated column string for a given table
stp_DTS_GetIdentityOrPrimaryKeyColumnDetails: This SP generates various strings for an Identity or Primary key columns of a given table.
stp_DTS_SetDestinationColumnWithSourceColumnString: This SP generates the SET clause for the update statement described in the step 3.
stp_DTS_DataSynchronization: This is the main SP will be used to synchronize the destination tables with the source tables data.
Details of the parameters used in all of the above procedures are described along with the header of each SP.
Assumption:
•Both source and destination tables’ schema are identical.
•Both source and destination data sources are different.
•Destination server has a linked server with source server in case both are remotely connected.
•All the SP’s and view listed above are stored & compiled in the destination database.
•All Foreign key constraints & Trigger (that affects another tables) of the destination tables are disabled before executing stp_DTS_DataSynchronization
•The main SP stp_DTS_DataSynchronization will be executed at destination database.
•User calls the main SP stp_DTS_DataSynchronization with valid parameters.
Limitation:
•Column with Timestamp datatype excluded from the data synchronization
.
Features of future version:
•The Stored Procedure will be extended to include parameter validation.
•It will be extended in such a way to disable the Foreign key constraints & Triggers of the destination tables before starting synchronization. After completion of data synchronization (either success or failure), all Foreign key constraints & Triggers will be enabled back to maintain data integrity.
•It will be extended to provide Log of the data synchronization process. So that user can know, how many records are deleted/inserted/updated and what are they. Hint: The OUTPUT clause can be used to achieve this.
Conclusion:
Data synchronization using Stored Procedure method is faster than the TableDiff method. The genuine feedback from readers will be helpful to achieve a better solution than this proposed method. Together we will meet that goal.
References:
1.The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION (http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx)
2.TableDiff Utility (http://technet.microsoft.com/en-us/library/ms162843.aspx)
Acknowledgment:
My sincere thanks to all the experts who participated and spend their valuable time to discuss the technique of Table Comparison in the Jeff's SQL Server Blog (http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx).
•Jeff: For his UNION ALL method to compare tables
•Click: For his NOT EXISTS method to compare tables
•David L. Penton: For his explanation of the issues found in NOT EXISTS method
•John: For his powerful coding to generate comma separated list with a single SELECT
Data Synchronization - Beta Version.sql - Script
/**********************************************************************
' FILE NAME : v_DTS_ColumnInformation.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This view populates column details
' This view used by most of the stp_DTS.xxx stored procedures
' COLUMN : 1. Table Name
' 2. Column Name
' 3. Data Type
' 4. A flag indicates whether the column is part of the Primary Key or not
' 5. A flag indicates whether the column is nullable or not
' 6. A flag indicates whether the table has identity column or not
' 7. Length of character data type column
' 8. Precision of numeric data type column
' 9. Sclae of numeric data type column
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Retrieve column information
select * from vColumnInformation
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_DTS_ColumnInformation]') )
drop view [dbo].[v_DTS_ColumnInformation]
GO
create view v_DTS_ColumnInformation
as
select
TableName = isc.table_name
,ColumnName = isc.column_name
,DataType = isc.data_type
,IsPrimaryKey = case when iskcu.ordinal_position is null then 0 else 1 end
,IsNullable = sc.isnullable
,IsIdentity = case when sc.status =128 then 1 else 0 end
,CharacterMaximumLength = isc.Character_Maximum_Length
,NumericPrecision = isc.Numeric_Precision
,NumericScale = isc.Numeric_Scale
from
information_schema.columns isc
left outer join information_schema.key_column_usage iskcu
on isc.table_name = iskcu.table_name and isc.column_name = iskcu.column_name
inner joinsysobjects so
on isc.table_name = so.name
inner join syscolumns sc
on so.id = sc.id and isc.column_name = sc.name
go
/**********************************************************************
' FILE NAME : stp_DTS_GetCommaSeperatedColumnString.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure generates the comma seperated
' column string for a given table
' This SP called from stp_DTS_DataSynchronization
' PARAMTERS : 1. Table Name (Input)
' 2. Column String (Output)
' 3. Column String without TimeStamp datatype column(Output)
' 4. Column String With casting LOB to Large Value Datatype (Output)
' 5. Column String With casting Large Value to LOB Datatype(Output)
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Execute stp_DTS_GetCommaSeperatedColumnString procedure
declare @v_ColumnString varchar(max), @v_ColumnStringWithoutTimeStampDataType varchar(max) , @v_ColumnStringWithCastingLOBToLargeValueDataType varchar(max), @v_ColumnStringWithCastingLargeValueToLOBDataType varchar(max)
exec dbo.stp_DTS_GetCommaSeperatedColumnString 'TableName',@v_ColumnString out, @v_ColumnStringWithoutTimeStampDataType out, @v_ColumnStringWithCastingLOBToLargeValueDataType out, @v_ColumnStringWithCastingLargeValueToLOBDataType out
select
ColumnString = @v_ColumnString
,ColumnStringWithoutTimeStampDataType = @v_ColumnStringWithoutTimeStampDataType
,ColumnStringWithCastingLOBToLargeValueDataType = @v_ColumnStringWithCastingLOBToLargeValueDataType
,ColumnStringWithCastingLargeValueToLOBDataType = @v_ColumnStringWithCastingLargeValueToLOBDataType
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].stp_DTS_GetCommaSeperatedColumnString') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_GetCommaSeperatedColumnString]
GO
create procedure stp_DTS_GetCommaSeperatedColumnString
(@p_TableName varchar(254)
,@p_ColumnString varchar(max) out
,@p_ColumnStringWithoutTimeStampDataType varchar(max) out
,@p_ColumnStringWithCastingLOBToLargeValueDataType varchar(max) out
,@p_ColumnStringWithCastingLargeValueToLOBDataType varchar(max) out
)
as
begin
set nocount on
select
@p_ColumnString = ''
,@p_ColumnStringWithCastingLOBToLargeValueDataType = ''
,@p_ColumnStringWithCastingLargeValueToLOBDataType = ''
,@p_ColumnStringWithoutTimeStampDataType = ''
select
@p_ColumnString = @p_ColumnString +
case
when len(@p_ColumnString)>0 then ', '
else ''
end +
'[' + ColumnName + ']'
,@p_ColumnStringWithoutTimeStampDataType = @p_ColumnStringWithoutTimeStampDatatype +
case
when len(@p_ColumnStringWithoutTimeStampDatatype)>0 and DataType != 'timestamp' then ', '
else ''
end +
case
when DataType = 'timestamp' then ''
else '[' + ColumnName + ']'
end
,@p_ColumnStringWithCastingLOBToLargeValueDataType = @p_ColumnStringWithCastingLOBToLargeValueDataType +
case
when len(@p_ColumnStringWithCastingLOBToLargeValueDataType)>0 then ', '
else ''
end +
case
when DataType = 'image' then 'convert(varbinary(max),[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'text' then 'convert(varchar(max),[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType in ('ntext','xml') then 'convert(nvarchar(max),[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
else '[' + ColumnName + ']'
end
,@p_ColumnStringWithCastingLargeValueToLOBDataType = @p_ColumnStringWithCastingLargeValueToLOBDataType +
case
when len(@p_ColumnStringWithCastingLargeValueToLOBDataType)>0 then ', '
else ''
end +
case
when DataType = 'image' then 'convert(image,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'text' then 'convert(text,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'ntext' then 'convert(ntext,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'xml' then 'convert(xml,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
else '[' + ColumnName + ']'
end
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
end
go
/**********************************************************************
' FILE NAME : stp_DTS_GetIdentityOrPrimaryKeyColumnDetails.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure generates the various string
' for an Identity or Primary Key column for a given table
' This SP called from stp_DTS_DataSynchronization
' PARAMTERS : 1. Table Name (Input)
' 2. Equi Join String (Output)
' 3. Column Structure String for the temporary table (Output)
' 4. Comma seperated column strinig for DELETED table (Output)
' 5. Comma seperated column strinig for INSERTED table (Output)
' 6. Comma seperated column strinig for temporary table that hold detailed log (Output)
' 7. Comma seperated column string of Identity or Priamry key column (Output)
' 8. A flag indicates whether the table contains identity column or not (Output)
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Executes stp_DTS_GetIdentityOrPrimaryKeyColumnDetails procedure
declare
@p_EquiJoinString varchar(max),@p_StructureString varchar(max),@p_DeletedString varchar(max),@p_InsertedString varchar(max),@p_TmpTableColumnListString varchar(max),@p_IdentityOrPrimaryKeyColumnString varchar(max),@p_IsIdentity bit
exec stp_DTS_GetIdentityOrPrimaryKeyColumnDetails 'TableName',@p_EquiJoinString OUT, @p_StructureString OUT, @p_DeletedString OUT, @p_InsertedString OUT, @p_TmpTableColumnListString OUT,@p_IdentityOrPrimaryKeyColumnString out, @p_IsIdentity OUT
select
EquiJoinString = @p_EquiJoinString
,StructureString = @p_StructureString
,DeletedString = @p_DeletedString
,InsertedString = @p_InsertedString
,TmpTableColumnListString = @p_TmpTableColumnListString
,IdentityOrPrimaryKeyColumnString = @p_IdentityOrPrimaryKeyColumnString
,IsIdentity = @p_IsIdentity
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_DTS_GetIdentityOrPrimaryKeyColumnDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_GetIdentityOrPrimaryKeyColumnDetails]
GO
create procedure [dbo].[stp_DTS_GetIdentityOrPrimaryKeyColumnDetails]
(@p_TableName varchar(254)
,@p_EquiJoinString varchar(max) out
,@p_StructureString varchar(max) out
,@p_DeletedString varchar(max) out
,@p_InsertedString varchar(max) out
,@p_TmpTableColumnListString varchar(max) out
,@p_IdentityOrPrimaryKeyColumnString varchar(max) out
,@p_IsIdentity bit out
)
as
begin
set nocount on
select
@p_EquiJoinString = ''
,@p_StructureString = ''
,@p_DeletedString = ''
,@p_InsertedString = ''
,@p_TmpTableColumnListString = ''
,@p_IdentityOrPrimaryKeyColumnString = ''
,@p_IsIdentity=1
-- Construct the EquiJoinString using identity column if the table contains Identity column
select
@p_EquiJoinString = @p_EquiJoinString +
case
when len(@p_EquiJoinString)>0 then ' and '
else ''
end +
@p_TableName + '.[' + ColumnName + '] = SrcDBTable.[' + ColumnName + ']'
,@p_StructureString = @p_StructureString +
case
when len(@p_StructureString)>0 then ', '
else ''
end +
'[' + ColumnName + ']' + ' ' +
CASE DataType
WHEN 'char' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varchar' THEN Datatype + '('+convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nvarchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'decimal' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'numeric' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'float' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ')'
WHEN 'binary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varbinary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
else
DataType
END
,@p_DeletedString = @p_DeletedString +
case
when len(@p_DeletedString)>0 then ', '
else ''
end +
'deleted.[' + ColumnName + ']'
,@p_InsertedString = @p_InsertedString +
case
when len(@p_InsertedString)>0 then ', '
else ''
end +
'inserted.[' + ColumnName + ']'
,@p_TmpTableColumnListString = @p_TmpTableColumnListString +
case
when len(@p_TmpTableColumnListString)>0 then '+'',''+'
else ''
end +
'convert(varchar(max),[' + ColumnName + '])'
,@p_IdentityOrPrimaryKeyColumnString = @p_IdentityOrPrimaryKeyColumnString +
case
when len(@p_IdentityOrPrimaryKeyColumnString)>0 then ', '
else ''
end +
'[' + ColumnName + ']'
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
and IsIdentity = 1 -- Include only Identity column
-- Construct the EquiJoinString using primary column if the table does not contains Identity column
if @p_EquiJoinString = ''
begin
set @p_IsIdentity = 0
select
@p_EquiJoinString = @p_EquiJoinString +
case
when len(@p_EquiJoinString)>0 then ' and '
else ''
end +
@p_TableName + '.[' + ColumnName + '] = SrcDBTable.[' + ColumnName + ']'
,@p_StructureString = @p_StructureString +
case
when len(@p_StructureString)>0 then ', '
else ''
end +
'[' + ColumnName + ']' + ' ' +
CASE DataType
WHEN 'char' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varchar' THEN Datatype + '('+convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nvarchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'decimal' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'numeric' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'float' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ')'
WHEN 'binary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varbinary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
else
DataType
END
,@p_DeletedString = @p_DeletedString +
case
when len(@p_DeletedString)>0 then ', '
else ''
end +
'deleted.[' + ColumnName + ']'
,@p_InsertedString = @p_InsertedString +
case
when len(@p_InsertedString)>0 then ', '
else ''
end +
'inserted.[' + ColumnName + ']'
,@p_TmpTableColumnListString = @p_TmpTableColumnListString +
case
when len(@p_TmpTableColumnListString)>0 then '+'',''+'
else ''
end +
'convert(varchar(max),[' + ColumnName + '])'
,@p_IdentityOrPrimaryKeyColumnString = @p_IdentityOrPrimaryKeyColumnString +
case
when len(@p_IdentityOrPrimaryKeyColumnString)>0 then ', '
else ''
end +
'[' + ColumnName + ']'
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
and IsPrimaryKey = 1 -- Include only primary key column
end
end
go
/**********************************************************************
' FILE NAME : stp_DTS_SetDestinationColumnWithSourceColumnString.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure generates the set string
' of an update statement for a given table
' This excludes TimeStamp datatype column
' This SP called from stp_DTS_DataSynchronization
' PARAMTERS : 1. Table Name (Input)
' 2. Set String (Output)
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Execute stp_DTS_SetDestinationColumnWithSourceColumnString procedure
declare @v_SetString varchar(max)
exec stp_DTS_SetDestinationColumnWithSourceColumnString 'TableName', @v_SetString out
select @v_SetString
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_DTS_SetDestinationColumnWithSourceColumnString]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_SetDestinationColumnWithSourceColumnString]
GO
create procedure stp_DTS_SetDestinationColumnWithSourceColumnString
(@p_TableName varchar(254)
,@p_SetString varchar(max) out
)
as
begin
set nocount on
set @p_SetString = ''
select
@p_SetString = @p_SetString +
case
when len(@p_SetString)>0 then ', '
else ''
end +
'[' + ColumnName + '] = SrcDBTable.[' + ColumnName + ']'
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
and IsPrimaryKey = 0 -- Include only non key column
and IsIdentity = 0 -- Include only non identity column
and DataType != 'timestamp' -- Exclude TimeStamp data type column
end
go
/**********************************************************************
' FILE NAME : stp_DTS_DataSynchronization.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure synchronize the destination tables
' data with the Source tables data
' This SP should be executed at Destination database
' PARAMTERS : 1. Linked Server Name of the Source Database (Input)
' 2. Source Database Name (Input)
' 3. Comma seperated List of Tables to be synchronized (Input)
' 4. Program Mode (Input)
' -1 -> Table Compare only
'0 -> Table Compare & Data Synchronization
'1 -> Data Synchronization Only
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Execute Data Synchronization procedure at destination database to sync Destination table with Source table data
-- Table Compare Only
-- exec [DestinationServerInstance].DestinationDB.dbo.stp_DTS_DataSynchronization 'SourceServerInstance','SourceDBName','TableName1,TableName2,TableName3',-1
-- Table Compare & Data Synchronization
-- exec [DestinationServerInstance].DestinationDB.dbo.stp_DTS_DataSynchronization 'SourceServerInstance','SourceDBName','TableName1,TableName2,TableName3',0
-- Data Synchronization Only
-- exec [DestinationServerInstance].DestinationDB.dbo.stp_DTS_DataSynchronization 'SourceServerInstance','SourceDBName','TableName1,TableName2,TableName3',1
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_DTS_DataSynchronization]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_DataSynchronization]
GO
create procedure stp_DTS_DataSynchronization
(
@p_LinkedServerNameofSourceDatabase varchar(254)
,@p_SourceDatabaseName varchar(254)
,@p_TablesToBeSynchronized varchar(max)
,@p_ProgramMode int = 1
)
as
begin
set nocount on
declare
@v_TableName varchar(254)
,@i_CommaPosition int
,@i_StringLength int
,@i_StartPosition int
,@i_EndPosition int
,@v_sql nvarchar(max)
,@v_ColumnList varchar(max)
,@v_ColumnlistWithoutTimeStampDataType varchar(max)
,@v_ColumnStringWithCastingLOBToLargeValueDataType varchar(max)
,@v_ColumnStringWithCastingLargeValueToLOBDataType varchar(max)
,@v_EquiJoinString varchar(max)
,@v_StructureString varchar(max)
,@v_DeletedString varchar(max)
,@v_InsertedString varchar(max)
,@v_TmpTableColumnListString varchar(max)
,@v_IdentityOrPrimaryKeyColumnString varchar(max)
,@v_SetDestinationColumnWithSourceColumnString varchar(max)
,@b_IsTableHavingIdentityColumn bit
,@i_errorcode int
,@v_ErrorMessage varchar(400)
,@i_return int
,@v_newline varchar(1)
,@v_DetailedLog varchar(max)
,@i_DeletedRowCount int
,@i_InsertedRowCount int
,@i_UpdatedRowCount int
,@i_TotalDeletedRowCount int
,@i_TotalInsertedRowCount int
,@i_TotalUpdatedRowCount int
,@i_TotalRowsSynchronized int
,@v_Line varchar(80)
,@v_Line1 varchar(80)
,@i_LogColumnWidth int
,@d_StartDate datetime
,@d_EndDate datetime
,@v_FullyQualifiedSourceTableName varchar(254)
,@v_FullyQualifiedDestinationTableName varchar(254)
,@v_LinkedServerNameofDestinationDatabase varchar(254)
,@v_DestinationDatabaseName varchar(254)
,@v_FullyQualifiedSourceDatabaseName varchar(254)
,@v_FullyQualifiedDestinationDatabaseName varchar(254)
,@b_SynchronizationTransactionStarted bit
-- Initialize local variables
select
@i_errorcode = 0
,@i_return = 0
,@v_newline = char(10)
,@i_TotalDeletedRowCount = 0
,@i_TotalInsertedRowCount = 0
,@i_TotalUpdatedRowCount = 0
,@i_TotalRowsSynchronized = 0
,@v_Line = replicate('*',80)
,@v_Line1 = replicate('-',32)
,@i_LogColumnWidth = 30
,@d_StartDate = getdate()
,@v_LinkedServerNameofDestinationDatabase = @@servername
,@v_DestinationDatabaseName = db_name()
,@b_SynchronizationTransactionStarted = 0
-- Derive the fully qualified name of the Database
select
@v_FullyQualifiedSourceDatabaseName = '[' + @p_LinkedServerNameofSourceDatabase + '].['+ @p_SourceDatabaseName + ']' + '.dbo.'
,@v_FullyQualifiedDestinationDatabaseName = '[' + @v_LinkedServerNameofDestinationDatabase + '].['+ @v_DestinationDatabaseName + ']' + '.dbo.'
-- start synchronization transaction only if the program mode is "Table Compare & Data Synchronization" or "Data Synchronization only"
if @p_ProgramMode >= 0
begin
-- Start Synchronization transaction
begin transaction Synchronization
set @b_SynchronizationTransactionStarted = 1
end
-- Loop through all the TableName from the 'Comma seperated List of Tables to be synchronized' input parameter
while 1=1
begin
select
@i_CommaPosition = patindex('%,%',@p_TablesToBeSynchronized)
,@i_StringLength = len(@p_TablesToBeSynchronized)
,@i_StartPosition = 1
select
@i_endPosition = case when @i_CommaPosition = 0 then @i_StringLength else @i_CommaPosition - 1 end
-- Derive the Table Name to be synchronized
select
@v_TableName = substring(@p_TablesToBeSynchronized,@i_StartPosition,@i_endPosition)
if @v_TableName is not null
begin
-- Derive the column list
exec stp_DTS_GetCommaSeperatedColumnString
@v_TableName
,@v_ColumnList out
,@v_ColumnlistWithoutTimeStampDataType out
,@v_ColumnStringWithCastingLOBToLargeValueDataType out
,@v_ColumnStringWithCastingLargeValueToLOBDataType out
-- Perform Table Compare
if @p_ProgramMode <= 0
begin
select
@v_FullyQualifiedSourceTableName = @v_FullyQualifiedSourceDatabaseName + @v_TableName
,@v_FullyQualifiedDestinationTableName = @v_FullyQualifiedDestinationDatabaseName + @v_TableName
set @v_sql = 'select ''' + @v_FullyQualifiedSourceTableName + ''' as TableName, ' + @v_ColumnStringWithCastingLOBToLargeValueDataType +
' from ' + @v_FullyQualifiedSourceTableName + ' union all select ''' + @v_FullyQualifiedDestinationTableName + ''' as TableName, ' +
@v_ColumnStringWithCastingLOBToLargeValueDataType + ' from ' + @v_FullyQualifiedDestinationTableName
set @v_sql = 'select max(TableName) as [TableName : ' + @v_TableName + '], ' + @v_ColumnStringWithCastingLargeValueToLOBDataType +
' from (' + @v_sql + ') a group by ' + @v_ColumnList +
' having count(*) = 1'
exec sp_executesql @v_sql
-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler
-- Jump to next iteration if the program mode is Table compare only
if @p_ProgramMode < 0
goto NextIteration
end
-- Populate the Identity Or Primary Key Column Details
exec stp_DTS_GetIdentityOrPrimaryKeyColumnDetails
@v_TableName
,@v_EquiJoinString OUT
,@v_StructureString OUT
,@v_DeletedString OUT
,@v_InsertedString OUT
,@v_TmpTableColumnListString OUT
,@v_IdentityOrPrimaryKeyColumnString out
,@b_IsTableHavingIdentityColumn out
-- Step 1.Records to be deleted from Destination database table
--Select the records that does not exist in Source database table, but exist in the Destination database table
--Then delete them from the Destination database table
-- Start constructing dynamic sql required for Step 1.
set @v_sql ='delete from' + ' '
+ @v_TableName + ' '
+ 'where not exists' + ' '
+ '(select 1 from' + ' '
+ @v_FullyQualifiedSourceDatabaseName + @v_TableName + ' '
+ 'as SrcDBTable' + ' '
+ 'where' + ' '
+ @v_EquiJoinString + ' '
+ ')'
-- Execute the constructed dynamic sql for Step 1
exec sp_executesql @v_sql
-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler
-- Step 2.Records to be inserted into Destination database table
--Select the records that are exist in the Source database table, but does not exist in the Destination database table
--Then insert them into the Destination database table
-- Start constructing dynamic sql required for Step 2.
-- 'set identity_insert on' before the insert statement if the table has an Identity column
if @b_IsTableHavingIdentityColumn = 1
set @v_sql = 'set identity_insert' + ' '
+ @v_TableName + ' ' +
+ 'on'
else
set @v_sql = ''
set @v_sql =@v_sql + ' '
+ 'insert into' + ' '
+ @v_TableName + ' '
+ '(' + @v_ColumnlistWithoutTimeStampDataType + ')' + ' '
+ 'select' + ' '
+ @v_ColumnlistWithoutTimeStampDataType + ' '
+ 'from' + ' '
+ @v_FullyQualifiedSourceDatabaseName + @v_TableName + ' '
+ 'as SrcDBTable' + ' '
+ 'where not exists ' + ' '
+ '(select 1 from' + ' '
+ @v_TableName + ' '
+ 'where' + ' '
+ @v_EquiJoinString + ' '
+ ')'
-- ''set identity_insert off' after the insert statement if the table has an Identity column
if @b_IsTableHavingIdentityColumn = 1
set @v_sql = @v_sql + ' '
+ 'set identity_insert' + ' '
+ @v_TableName + ' ' +
+ 'off'
-- Execute the constructed dynamic sql for Step 2
exec sp_executesql @v_sql
-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler
-- Step 3.Records to be updated in the Destination database table
--Select the records that are differ from Source & Destination database table
--Then update them in the Destination database table with the source database table data
-- Populate the set string
exec stp_DTS_SetDestinationColumnWithSourceColumnString
@v_TableName
,@v_SetDestinationColumnWithSourceColumnString out
-- if the table does not have a non key column then no need to do this step 3.
if isnull(@v_SetDestinationColumnWithSourceColumnString,'') = ''
begin
set @i_UpdatedRowCount = 0
goto NextIteration
end
-- Start constructing dynamic sql required for Step 3.
set @v_sql ='update' + ' '
+ @v_TableName + ' '
+ 'set' + ' '
+ @v_SetDestinationColumnWithSourceColumnString + ' '
+ 'from' + ' '
+ @v_TableName + ' '
+',' + ' '
+ '(select' + ' '
+ 'max(TableName) as TableName,' + ' '
+ @v_ColumnStringWithCastingLargeValueToLOBDataType + ' '
+ 'from' + ' '
+ '(' + ' '
+ 'select' + ' '
+ '''SourceDBTableName'' as TableName,' + ' '
+ @v_ColumnStringWithCastingLOBToLargeValueDataType + ' '
+ 'from' + ' '
+ @v_FullyQualifiedSourceDatabaseName + @v_TableName + ' '
+ 'union all' + ' '
+ 'select' + ' '
+ '''DestinationiTableName'' as TableName,' + ' '
+ @v_ColumnStringWithCastingLOBToLargeValueDataType + ' '
+ 'from' + ' '
+ @v_TableName + ' '
+ ') tmp' + ' '
+ 'group by' + ' '
+ @v_ColumnList + ' '
+ 'having' + ' '
+ 'count(*) = 1 and max(TableName) = ''SourceDBTableName''' + ' '
+ ')' + ' '
+ 'as SrcDBTable' + ' '
+ 'where' + ' '
+ @v_EquiJoinString
-- Execute the constructed dynamic sql for Step 3
exec sp_executesql @v_sql
-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler
end
-- Derive the next iteration values
-- If no further iternation is required then exit the loop
NextIteration:
if @i_CommaPosition = 0
begin
-- Break the loop if no more tables exist to synchronize
break
end
else
begin
-- Derive the remaining Tables to be synchronized
select
@p_TablesToBeSynchronized = substring(@p_TablesToBeSynchronized, @i_CommaPosition + 1, @i_StringLength)
end
end
-- If the program mode is "Table Compare & Data Synchronization" or "Data Synchronization only"
-- then commit the synchronization transaction and log the remaining
if @p_ProgramMode >= 0
begin
-- Commit the synchronization transaction as no error occured
commit transaction Synchronization
end
-- Return success
return 0
-- Error Handler
ErrorHandler:
begin
-- check for error
if ( @i_errorcode <= 0 )
begin
raiserror 99999 @v_ErrorMessage
select @i_return = -100
end
else if ( @i_errorcode > 0 )
begin
select @i_return = -100
end
-- Rollback the synchronization transaction if it is started and an error occured
if @b_SynchronizationTransactionStarted = 1
rollback transaction Synchronization
-- Return Failure
return @i_return
end
end
go