We have a SQL 2000 DB1 ( publisher) which is replicated using transactional replcation onto the secondary server DB2 ( subscriber). we have identity columns on the DB1 and we created those tables with 'not for replication' clause. we skipped the following errors in the replication profile '2601:2627:8102:20598'. on DB2 ( the subscriber ) some records are missing in some tables . ( I verified this with record count differences for some tables in both the servers DB1 & DB2.
How to find out what are all the records missing & the cause.
I have listed the msrepl_errors table on DB1 , distribution databases all I'm seeing there are error code 8102 , unable to update identity column.
I am using SSIS 2014 and installed adapter for sharepoint list source and destination and when I refresh the toolbox I don't see them. Is there a way to manually add them?
Greetings from a SSIS newbie still on the learning curve...
I have a SQL query OLE DB Source that yields a result set that I'd like to put into a SQL Server Destination, but only those records that don't already exist in the destination.
Is there a recommended (read: easy) way to accomplish this? Perhaps a handy transformation?
I have tried to incorporate a subquery in my source query along the lines of:
SELECT fields FROM table1 WHERE keyfield NOT IN (SELECT keyfield from table2)
which works in design time but fails at the server with a cryptic:
"Error: Unable to prepare the SSIS bulk insert for data insertion."
and
"Error: component "<component>" (16) failed the pre-execute phase and returned error code 0xC0202071."
Don't mean for that to cloud the issue, though. I would appreciate any help given.
Short Question: How do I delete all records from a destination table prior to appending new data to that table?
I am working with a SQL database that was migrated from MS Access. All relationships, primary keys, and identity columns have been set identically to the MS Access database values. The MS Access database is still being used as the database of record until the SQL database is fully functional with front-end, etc.
I want to delete the information stored in all the SQL tables, and then append the MS Access values to the SQL tables. I was able to write delete and append queries in MS Access to correctly transfer data to the SQL tables. However, I would prefer doing this through SSIS because I have several other sources of data to move to a SQL Server database and most of those other sources are not a MS Access database.
Due to relational entegrity settings, I need to delete the records from 8 tables in a specific order. I have tried independent control objects for each of the 8 tables with data flow objects of either "OLE DB Command" or "OLE DB Source" with the SQL command as "Delete From TableName". Results of the debug indicate everything is "green" but no records were deleted fromt the tables.
I am having a brain-fart or something. I need help!!!
I'm developing a transform that selects data from a SQL script, then transforms that data, and then needs to update the data back into the same source table. The transform is working great, but I can't figure out the update table part. I've got it adding the transformed records to the table, but that's not what I need to do. I need to update the table with the changed data.
Any sample code or blog links would be greatly appreciated!
I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.
The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?
Am I correct in the way I am doing or there is something better to handle this?
I have a table that contains 2 columns. The first column has the datetime, and the second has a value (see the "Before" table). In my SELECT query I want to select all records like 0:00:00 <= Date <= 0:40:00. However the missing 10-minutes value 0:30:00 should be automatically inserted with value = NULL.
Before:
Code:
Date Value ------------------- ------------------- 13/12/2004 0:00:00 15 13/12/2004 0:10:00 17 13/12/2004 0:20:00 21 13/12/2004 0:40:00 12 13/12/2004 0:50:00 13
After:
Code:
Date Value ------------------- ------------------- 13/12/2004 0:00:00 15 13/12/2004 0:10:00 17 13/12/2004 0:20:00 21 13/12/2004 0:30:00 NULL 13/12/2004 0:40:00 12
I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records.
So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records.
How can i do that in SSIS 2005 using different data flow tasks ?
Writing a Control flow that inserts records worked fine, using lookups and all, but i can't find somewhere an example on how to update records of even delete records.
I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.
Can you think about any reason for why when using a transaction after the COMMIT TRAN the inserted new record is not in the table and there is a gap in the identity????
I'm using SQL 2000 SP3, there are no triggers are on the table and it happanes only under heavy load.
hello friends.. i aleays facing this problem while reporting....
if i want to show report for date range then i am not getting records for end date...why???
my report query was
select distinct DwnDate,isnull(D.FileName,'No File Found'),isnull(H.File_ID,0), isnull(C.DownLoadCatname,'No Category Found'),count(H.File_ID) AS TotalCount from DownLoadHistory H inner join DownLoad D on H.File_ID = D.File_ID inner join DownLoadCat C on D.File_Cat = C.DownLoad_CatID where File_DwnDate between '10/01/2006' and '10/31/2006' group by D.File_Name,C.DownLoad_Catname,H.File_ID,File_DwnDate order by 3
i am getting rows 15 here but when i fired this
select distinct DwnDate,isnull(D.FileName,'No File Found'),isnull(H.File_ID,0), isnull(C.DownLoadCatname,'No Category Found'),count(H.File_ID) AS TotalCount from DownLoadHistory H inner join DownLoad D on H.File_ID = D.File_ID inner join DownLoadCat C on D.File_Cat = C.DownLoad_CatID where File_DwnDate between '10/01/2006' and '11/01/2006' group by D.File_Name,C.DownLoad_Catname,H.File_ID,File_DwnDate order by 3
then i am getting rows 16
previous one i always missed records on 10/31/2006...is there any solution or i always add one day to end date and then get values??
I have 2 tables say table1 and table2 with the same structure. Each recordis identified by a field 'SerialNo'. Now there should be a total of 500000records in both tables with serialno from 1 to 500000. Either a record is intable1 or table2. I want to find records (or SerialNo's) that are inneither table (if deleted by accident etc). What would be the sql query?I'm using SQL 6.5thx
I have one table with many records in the table. Each time a record is entered the date the record was entered is also saved in the table. I need a query that will find all the missing records in the table. So if I have in my table:
ID         Date         Location 1          4/1/2015       bld1 2          4/2/2015       bld1 3          4/4/2015       bld1
I want to run a query like
Select Date, Location FROM [table] WHERE (Date Between '4/1/2015' and '4/4/2015') and (Location = bld1) WHERE Date not in (Select Date, Location FROM [table])
Am witnessing a very strange issue when i try to insert some records . I get the message in the SSMSÂ like 5,10 etc rows affected. When i check for the same records in the table none of them is present.
This problem then automatically goes away after some time and all get backs to normal. I only have the access to that database no other user have the access. Totally confused about the all of a sudden new behavior of the database.
We have an SSIS package which runs regularly throughout the day, on 15 minute intervals. This package is moving data between two SQL Server instances, performing some simple identity mapping along the way. The primary source table is large, and we want to move only those records which have changed since the last time the package ran, so we use VersionDatestamps in the table, and pick up the dataset to be transferred by using the pacakge execution time, and the (previously recorded by the package) last-run time.
The problem we are having, is that the initial dataset picked up by SSIS is often missing records. The missing records are clearly within the time window that the package queried for, though they are near the boundary (within a minute, of the boundary, but as much as 30 seconds away) and typically all have an identical version datestamp to each other, within a single execution of the package.
At first, we thought this was an issue with date precision, but we've both truncated the dates, and even expanded the time window, and we still experience the same phenomenon.
The stored procedure which is responsible for updating the records in question, runs as a previous step to the SSIS package, within a single SQL Server Agent Job.
Has anyone experienced anything similar, or have some recommendation on how to track the source of this down?
I have two program (App1 And App2) on two separate system.on App1 there is a database named DB1 and on App2 there is a database named DB2.on DB1 i create a table named Table_1 .i define replication for DB1 to push publication (for table_1) .
I define DB2 as a subscriber to receive all changes on [DB1].[Table_1]. every thing is ok but just one thing. when App1 and App2 both are running if i change the contents of [DB1].[Table_1] this change only shown if one time i exit from App2 and rerun it . how can i refresh App2 without rerun it?
A friend said to me it is possible with trigger, but how? I don't know how can i define a trigger for this reason . or any way that exist for this reason.
Hello,We have a query which returns ~2.8 million rows. This same query isused in a DTS package, which exports to a text file. The number ofrows in this text file, however, is ~2.7 million rows (I'm rounding ofcourse.) So a good chunk of data vanished in the export it appears.Using SQL Server 7.0 on Windows 2000.Anyone see bugs w/ DTS text exports for very large amounts of data?Thanks,DF"Never eat more than you can lift." Miss Piggy
Im wondering if it is possible to write a procedure that check two identical tables for any missing records. The table design is excatly the same, but some records (of the 40,000) have not copied over to the second table.
I am importing records from a flat file to a database table. If a record is in the table but NOT in the flat file, I need to update a date column in the table.
While setting up replication to another server my other replications came with the error could not find sp_MSins_<destinationtabel> Error number 2812. Is there anyway I can rebuild this stored proceduire or what is the easiest way to start synchronizing again, I can't drop the destination database because the amount of data to replicate is too much
i have transactional replication setup. recently i found that some of the new records in the publisher are not replicated to subscriber, but the replication is going on as normal with most of the record replicated.
the only this happen is user stop and restart the synchronization.
why this happen, how to find all the records which are not replicated and how to replicate to subscriber now.
I can't believe this. MSDE was a great product and I'm sure that SQL Express is even better. The difference is that I can't migrate any of my company's applications to it because they rely on a Replication architecture (requiring Replication Publication). And SQL Agent, how are you supposed to keep the database in good shape if you can't run Maintenance Plans at scheduled intervals?
I know, there are third party products out there to compensate for the lack of the SQL Agent, but they don't solve the replication problem. Besides, the Agent still exists in other editions - it's not like it has been dropped completely. Why do we have to lose functionality and features? We have already lost the use of a second CPU and memory > 1GB. Why do they need to keep taking stuff out? Why don't they just limit access to the new feature-set?
I would rather lose the new features such as Report Server and Full Text Indexing and get the SQL Agent and Replication Publication back. I can almost understand that with a free product, they don't want to keep piliing new features in just to give them away - even though I'm sure the sale of the larger editions more than covers the R&D investment in their construction. Why can't we chose? Agent or Report Server, Replication or Full Text Indexing? I don't need some of these new features (others might), I just want the features that I used to have. Perhaps SQL 2005 setup can be made to choose 2 out of the 4 features?
Whats more, I can't even say that I'll just stick with MSDE, as it now has a limited support life. Microsoft announced that any SQL Server version prior to 2005 SP1 will NOT be supported on Vista or Longhorn Server (nor does it support anything less than Windows 2000 SP4 or Windows XP SP2 where MSDE does).
OK - let me get this right. We had something that has been taken away in the new version. Perhaps I don't deserve the new version if I don't pay for it - fine - I'll stick to the old one I already have. BUT wait... I can't use that on the new platforms coming out.
Are my entry-level customers going to pay for the Workgroup Edition (add AUD$1200 to my entry level edition application of AUD$1500) to replace the Replication Publisher and provide a Maintenance plan? Is anyone going to pay for the re-architecting and re-construction of my application to work around these new limitations?
I guess that will be me. Is this what happens for being loyal to MS products?
Please - give back the SQL Agent and Replication publication.
I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@last, only the records where the last name is 'Smith' will be returned if @last = 'Smith', but all records are returned is @last = "". But that's not how it's working with the date. I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of select * from table where (MONTH([AD ENDS]) = @month)MONTH(datefield) always returns an integer from 1 to 12. If @month is empty, I want all the records to be displayed, but nothing is. If @month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not? Diane
I have two tables of book information. One that has descriptions of thebook in it, and the isbn, and the other that has the book title,inventory data, prices, the isbn.Because of some techncal constraints I won't get into now, I can'tcombine them both into one table. No problem. Things are going fine aslong as there is a description in the one table to corrispond to theisbn and other data in the other table.However, about half of the products are not yet entered into thedescrition table. I'd like to run a sql query that pulls up all theisbns that don't exist in the other. In other words, I'd like to get aquery that tells me exactly which isbns do not yet have descrition datain them. I know there is some sql that says to search from one filewhere the number does not exist in the other, but it slips my mind. Cansomeone help me on this please?Thank you!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I am trying to drop replication off a database, and funny enough went trough all the steps and I am still getting the error when I try to Disable publishing.
I also did an EXEC sp_removedbreplication 'databasename'
My company is moving to a SQL Server-based packaged application early next year. We€™re planning our SQL Server architecture but have some questions that I can€™t readily find answers for. I€™m hoping someone here can point me in the right direction.
We have three servers, I€™ll call them A, B, and C. We want to duplicate all changes to certain databases on server A to server B, then duplicate changes to selected databases and tables on server B to server C.
Ideally we€™d run SQL Server 2005 Enterprise Edition on all three servers, but the packaged application vendor does not support SQL Server 2005 yet, only SQL Server 2000. Our license agreement with them does not allow us to use replication on server A. We€™re free to do whatever we want on our other SQL Servers, but server A must sit alone, untouched, like a monolith on a far-away moon. (I€™m lobbying to have the server named Tycho, or TMA2.) Stranger still, they€™re OK with log shipping from server A to other servers. We€™ve tried to explain that replication and log shipping are both core function built into SQL Server, and that if one is acceptable, then both should be. Their fear is that replication could cause performance and stability problems, and to eliminate this possibility they€™re ruling out replication on server A.
Given these constraints we€™re resigned to using SQL Server 2000 Enterprise Edition on servers A and B, and SQL Server 2005 Enterprise Edition on server C. We plan on periodically shipping logs from server A to server B and applying them at server B.
We€™d like to know if it is possible to also use transactional replication on server B to duplicate changes from server B to server C. I€™ve used log shipping and replication in the past, but never at the same time. My understanding is that a database goes into recovery mode while a transaction log is being applied and that any user changes to the database after the log has been applied will cause later log applications to fail. The scripts I€™ve seen that are used to apply the transaction logs put the database into single user mode after the log has been applied to prevent this.
This raises a few questions:
If we try to RESTORE a log to a database being used as a source for transactional replication articles, will the RESTORE fail? Or will the RESTORE start and break the transactional replication? I€™ll test this on my own, but it€™d be nice to know if anyone has already experienced this.
Is it possible for us to have a database in read-only mode serve as the source for transactional replication articles? (I can€™t imagine why not, ever though it seems counter-intuitive - why would you want to replicate transactions from a database that has no transactions?)
If the answer to number two is yes, can we suspend transactional replication on a database, RESTORE a log to the database, put the database into read-only mode after the RESTORE, and restart the replication on the database? Thanks in advance for sharing your wisdom, everyone!
In Transactional Replication -while Publisher, Distributer & Subscriber are on same instance- can I have additional columns in destination Table & set "Action if name is in use" for related Article to "Keep existing object unchanged"?
I do this but even Snapshot is not transfered to destination table!!
