asked
Adding merge replication changed datetime fields to Datetime2(7)
we added merge replication to our SQL 2008 DB and it changed all our datetime fields to datetime2(7), and MS Access 2010 sees it as a text field. which breaks everything. WTF WTF WTF. and if I try to change data type back to Datetime, it wont let me. SQL Server was a good product until SQL 2005, then it started turning into a complicated pile of crap. searching MS support articles is also a lost cause.
paul@darlingassociates.com
So this is a SQL 2008 Express installation that's causing problems?
OK, just so we know we arent dealing with a live system that is broken.
Hi, I just read through the comments here and am a little surprised that the date column is being changed. That certainly isn't what should happen! That being said, your issue is probably more to do with how SSMS was coded/designed rather than SQL server itself. SSMS is terrible when it comes to table schema changes, it basically copies the old table into a new one with the new schema and drops the old table. This doesn't work with a replicated table as you could imagine. I would suggest you look at making your desired changes using T-SQL in SSMS. That way you know what commands are being sent to SQL Server. Could you give that a try and get back to us?
I didn't mean that the data type change was desired, I meant that the planned reversal back to datetime should be done via T-SQL as opposed to using SSMS' "assistance". Restoring a 2005 database onto a 2008 server will not change your schema in that way, I am guessing that something else is being done when you activate replication on the table(s). The issue of synching the subscriber can be solved by creating the subscription on the publisher side. The jobs are then created on the publisher and can then be run there when the subscriber is connected. This is a (designed) limitation of SQL Express that you have to work around I'm afraid.
I do think that SSMS is the cause for your issues, it is a scenario that I have run across a few times before. When the desired change was run via "vanilla" t-sql it worked fine. As to 2012 subscriber against a 2008 publisher, that shouldn't be a problem either IMO. A snapshot for replication is just a bunch of bcp commands and raw bytes in file containers, 2012 can read those just fine. Give it a go and let us know how you get on.
Hi Willam, I'm not sure I understand. I have no desired changes, other than changing the datetime2(7) back to datetime fields. It complained about defaults on the rowguid field (see earlier msg) and stated update failed during the trigger, but didn't specify the trigger or a line #, or offending statement. I'm just using the product as it was designed. Another clue, the DB was restored from a 2005 server into a 2008 server, but I don't recall seeing date fields changed at that point, until I activated repl. I only see this client once or twice a week. Next time I go there, ill restore again and see if date fields change. How will the express subscriber initiate a synch without the agent? I saw some talk about WMI but couldn't find any details or examples. I can't find anything that explains how to control the synching from the subscriber.
William, I'll work on the ALTER command next time I go there and let you know the results. There's only a couple dozen fields to fix. you dont think other parts of REPL (constraint, triggers, etc) will prevent this from succeeding? you think the problem is Studio Manager not executing everything correctly when I tell it to change the datatype. also, do you think it would be possible to use SQL 2012 express as subscriber to 2008 db? (I had problems when I tried, but other factors may have been in the way) I expect all the compatibility would be set to 2008 for that db. I just figure 2012 is newer, and possibly better than 2008 -for traveling laptops.?.? thanks again for all your help, Paul
13 People are following this question.
