| Author |
Topic |
|
mdhingra01
Posting Yak Master
179 Posts |
 Posted - 2004-05-31 : 15:49:13
|
| I am trying to copy tables on a server to another database on a differnt server. I am using the import/export wizard, but when I run my package, I get an errorMicrosoft SQL-DMO (ODBC SQLSTATE:42000)[Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]Line 4: Incorrect Syntax near COLLATE.I checked the script (.tab file in C:\Program Files\Microsoft SQL Server\80\Tools and removed the 'COLLATE SQL_Latin1_General_CP1_CI_AS', but when I try to re run the package, same error.Anyone know what is going on and how to fix this?Thanks |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-06-01 : 01:01:33
|
| The keyword COLLATE is recognized only by SQL Server 2000...is the other server running SQL Server 7 by any chance? In that case you will not be able to use SQL2K specific features such as column level collations, UDF's etc.OS |
 |
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-01 : 08:54:04
|
| Both servers are on SQL 2000. I tried the HELP to see what COLLATE actually does, the description wasn't much help.Still looking for a wy to fix this problem.Thanks |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-06-01 : 09:13:26
|
| Can you post the part of the file that's causing the error?OS |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-01 : 14:43:17
|
Here are the LOG  and TAB files |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-01 : 14:51:39
|
| The images that you posted can't be viewed by us because they are on your machine and don't get transferred to sqlteam.com. Please provide us with a link or type in the information here.Tara |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-01 : 15:37:07
|
| This is the LOG file:::Error::In file HQ_B510_SECOND.InputData.TAB in statement on line 1Transfer Status: Creating User defined datatypes on destination database[Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near 'COLLATE'. This is the TAB file:/****** Object: Table [dbo].[test] Script Date: 5/31/2004 3:47:42 PM ******/if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGINCREATE TABLE [dbo].[test] ( [Col001] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Col002] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Col003] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Col004] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Col005] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )ENDGO |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-01 : 15:41:16
|
| What version of SQL Server? What compatibility mode is the database set to? The script runs fine on my 80 compatibility mode on SQL Server 2000.To check version, SELECT @@VERSION in Query Analyzer. To check compatibility mode, right click on db in EM, go to properties, then to options tab.Are you transferring the objects for disaster recovery or for a standby server? If so, you should be using log shipping instead.Tara |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-01 : 15:46:51
|
| This is what the SELECT returnsMicrosoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3) The Compatibility under options shows:level 80 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-01 : 15:48:49
|
| Try running the TAB file in Query Analyzer.You aren't running the latest service pack for SQL Server 2000. You should install sp3a plus the security patch to get up to date.Tara |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-01 : 16:12:42
|
| I am downloading the patches now, and tried running the TAB in Analyzer, it ran.So do you think the patch and updates will fix this in EM? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-01 : 16:23:15
|
| Not sure. Why are you transferring objects using the wizard? What is the goal of what you are doing? Standby server? Perhaps we can come up with a better solution for you. DTS just doesn't need to be used here from what I can tell.Tara |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-02 : 11:19:14
|
| I am transferring objects to another server for backups. I want to actually backup to a network drive, but couldn't do that, I am limited to backing up to physical drives on the server. There is no TAPE drive on the server. The only space on the server is drive D, but that is where SQL SERVER is installed. There is a drive C and E, but there is about 4GB of space on each. I then thought, maybe if I could backup locally and transfer it to a remote server, that would work, or better yet, backup to the remote server.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-02 : 12:21:22
|
| I would backup to the local disk. Then copy the .BAK file to the backup server. That's what we do as a small part of our disaster recovery plan. The backup server is actually located over 300 miles away in another state.Tara |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-06-03 : 00:49:46
|
| I agree with Tara. You'd be better off scheduling a backup to disk and then copying it over to a network share. Besides, using DTS to copy data will do just that - copy DATA, but some parts of your database structure such as primary keys, and constraints are NOT copied, so you will have a dangerously incomplete copy of your database.How big is your average backup? If space is a major issue, an investment in a product like SQLLiteSpeed would be worthwile.OS |
|
|
|
|
Copying objects from one server to another