| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2006-06-08 : 13:11:53
|
| A couple of my databases are giving me replication errors when I attempt to replicate the stored procedures from SQL Server 2000 to 2005. It looks like 2005 is more strict about syntax, if I had to guess. Is there any way I can tell it to ignore all errors and still transfer all the stored procedures? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-08 : 14:14:18
|
| Script out all of the stored procedures that you are replicating in 2000 (using the Generate SQL script wizard), run it on your 2005 server in Management Studio. Does that produce any errors? If so, then you'll need to correct them.What compatibility level are you using on the 2005 database?Tara Kizeraka tduggan |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2006-06-08 : 15:32:53
|
| I've set them all to comp. level 80 (SQL Server 2000). I think I tried running scripts and that also gave errors, but I'll have to verify. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-08 : 15:38:32
|
| I am pretty sure that Vyas has information on how to ignore errors. He has a lot of good replication information. His site is vyaskn.tripod.com. Even if you ignore the errors, the stored procedure that errored out won't be created over there. It'll just allow replication to continue.So fix the errors!Tara Kizeraka tduggan |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2006-06-09 : 16:17:12
|
| I plan on fixing the errors, since we do plan on using SQL Server 2005 for our production server as soon as our ERP system will let us (maybe September). But in the meantime we want to use Reporting Services 2005 with a SQL 2005 db server, and for the most part we only need the data since it's just for reporting, but there are a few stored procedures that we'll need, and it would take me forever to figure out precisely which ones. Thus my desire to ignore the errors until we get more time to update the procedures. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-09 : 16:21:44
|
| Why not just replicate the data? You can get the stored procedures over by generating the script in 2000 and executing it in 2005.Tara Kizeraka tduggan |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2006-06-09 : 18:12:25
|
| Hmm, I was able to run a script of the stored procs with out any errors (which I don't understand), but I'm not sure if there's an easy way to set up a job to create the script on the prod. server and run it on the reporting server. Is there? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-09 : 18:16:16
|
| DTS can do it for you. But I wouldn't automate making changes in a production environment. Production changes should be done manually in case there are any problems, so that you don't impact customers.Tara Kizeraka tduggan |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2006-06-09 : 18:21:24
|
| No changes in production, just creating a script that can create the procs on the reporting server. Have you tried Notification Services? Is that the replacement for DTS in 2005?thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-09 : 18:23:56
|
| Notification Services isn't new. The replacement for DTS is Integration Services. I haven't tried either.If you are just creating a script, then you certainly don't need replication to transfer these.Tara Kizeraka tduggan |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2006-06-09 : 18:30:55
|
| Sorry, I meant Integration Services. I'm only creating a script to avoid using replication, since it gives me errors. I imagine there's not a simple query that scripts out procs, and by what I see in Profiler when I do it thru EM, it looks like I'm correct. |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2006-06-16 : 14:01:27
|
| I've determined that I don't need to replicate the stored procedures, just the data. But one database keeps giving me replication errors, e.g. "The DELETE statement conflicted with the REFERENCE constraint "InvoiceDetails_FKC1". The conflict occurred in database "DBNAME", table "dbo.InvoiceDetails", column 'InvoiceID'." Any idea why I would be getting these errors? |
|
|
|
|
ignore 2000->2005 repl errors?