September 27, 2001 at 3:13 pm
Recently upgraded from 6.5 to 2000. In 6.5, I could transfer tables using the transfer feature in Enterprise manager. In 2000, I've been trying to use the Export Wizard to accomplish the same thing, using option 3 on the Specify Tables to Copy page. It's giving the error "there is an incorrect syntax near Collate". However, it does end up deleting the tables from the receiving database. I've checked the collation and it's just the default SQL_Latin1_General_CP1_CI_AS for both databases. Any ideas?
September 27, 2001 at 6:32 pm
Saw this same error tonight in another context - had dropped a transactional publication and replaced with trans + immediately updating subscriber, got the error when it tried to do the snapshot. Was in a hurry(!) so just made a new db for the subscriber, worked fine.
Not sure why - one thought was that maybe something was schema bound, couldnt be dropped and that broke the script. Just a swag though.
Andy
October 5, 2001 at 10:40 am
This is not a replication situation. We have 2 different databases, one is an intranet and the other is accessible to our clients. Clients can log service tickets and then they transfer to the intranet database for us to respond to their issue. I have to periodically transfer data from 6 tables that need to be the same in both systems. It worked in 6.5, but is not working in 2000, getting the above error message. Any other suggestions?
October 5, 2001 at 2:00 pm
Save this as the DTS package and examine it. It may be including the collate option in the query to v6.5
Steve Jones
October 8, 2001 at 6:48 pm
I saved it as a DTS package. However the only "step" that appears when looking the design of the package is "copy SQL Server objects". What do I check to see if the collation settings are correct? This is my first time using DTS packages. Thanks for your help.
October 9, 2001 at 9:27 am
You can save it as a vb file and then open it using notepad. Or post it and we'll try to decode it for you.
Steve Jones
October 9, 2001 at 12:16 pm
Thanks. I'll give that a shot. I know VB fairly decently and I have access to programmers. Thanks. I'll let you know how that works!
October 9, 2001 at 1:17 pm
I would appreciate your assistance in decoding after all. How do I get this to you? Thanks.
October 9, 2001 at 4:08 pm
Email it to Steve (email address above). Any problems with making it available for everyone to look at? We can upload to the site and post a link here.
Andy
October 24, 2001 at 4:58 pm
Steve, any luck w/ the code I sent you?
October 24, 2001 at 10:20 pm
Ahhhhh
Sorry. I started looking at it and got distracted. I didn't see anything off the top of my head. I started testing with my own package, but didn't finish. I'll try to get to it tomorrow.
One more thing to try: Use Profiler to trace the DTS calls when it runs. You should see what calls are being made on 2000. You may need to do a SQLTrace on the v6.5 server as well.
Steve Jones
November 30, 2001 at 1:00 pm
Sounds like you are as overloaded as the rest of us. Any luck on the code? I haven't tried the trace yet. Both servers are sql 2000, just so you know.
November 30, 2001 at 1:12 pm
I apologize. I ran this through DTS and didn't see any problems. I am stumped here.
Is there any chance of BCP out BCP in to a new table?
Steve Jones
April 16, 2002 at 10:16 am
Here is the link to the Microsoft fix for your issue. http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q288411&LN=EN-US&rnk=1&SD=msdn&FR=0&qry=there%20is%20an%20incorrect%20syntax%20near%20Collate&src=DHCS_MSPSS_msdn_SRCH&SPR=SQL2K&
In a nutshell, Microsoft says you need to either apply the latest Service Pack for MSSQL2K or Checkpoint the database after its been refreshed/restored. I had the same problem and it worked for me.
Hope this helps.
Darias
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply