Importing To New Server

Hello,

I am attempting to import a database from one server (2000) into another one (2005). I have tried a variety of methods to do so, but each seems to have at least one hiccup. The biggest frustration is that, depending upon how I setup the tables prior to import, one of two things ends up kicking me in the rear:

1) I leave the IDENTITY cols set as such, and even though I enable identity insert in the wizard (SQL Server 2005 Management Studio), they end up just incrementing starting at 1 as though it was fresh data. Obviously, once I apply the PKs and FKs, everything falls to pieces.

2) I initialize the identity cols as INT, and the data imports just fine. However, when I try to add the IDENTITY to the cols, it tells me to go screw myself. Actually, it just throws a syntax error. Here is the statement that I'm attempting:

ALTER TABLE myTable
ALTER COLUMN myCol IDENTITY
GO

I have also tried

ALTER TABLE myTable
ALTER COLUMN myCol INT IDENTITY
GO

I'm pulling my hair out with this one. It's played a rather significant role in me getting only 2 hours of sleep in the last 40+ hours. I really try not to post questions without exhausting myself trying to figure out the answer, or to find it with research (which is part of figuring it out). I am a moderator on EasyCFM.com's ColdFusion forums (mquack is the screenname), so I know how annoying it is when people don't put forth any effort on their own. Hopefully I don't come across here as being "one of them".

Any help or guidance to the *right* sources would be very much appreciated.

Thanks in advance. :-)Take backup and restore it to the server you want.

How much the database size are you having...?|||Thank you for the reply. It's only a few hundred megs, so it's not too terribly bad. Is there a way to change the schema that the backup restores to? Either that or a quick way to change the schema by script after the restore is done?|||Is there a way to change the schema that the backup restores to? Either that or a quick way to change the schema by script after the restore is done?

What exactly you want to change ?

Importing MSAccess data into SQL Tables

I am attempting to import data from an MS Access 2000 table into a SQL
Server 2000 table, but NULL values in the Access table are causing the DTS
import to fail.
I thought that if I defined a column in SQL as 'NOT NULL', but assigned a
default value of '', then it would allow an import containing a NULL, which
it would convert to a ''. But I guess I was wrong? Is there is a way to
import MSAccess data containing nulls into a SQL table where the nulls would
be automatically be converted to ''?
Thanks!
Mark
Here is the definition for the SQL table:
CREATE TABLE [dbo].[creditcard] (
[CARD_NAME] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CARD_ABB] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACTIVE] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[creditcard] ADD
CONSTRAINT [DF_creditcard_CARD_NAME] DEFAULT ('') FOR [CARD_NAME],
CONSTRAINT [DF_creditcard_CARD_ABB] DEFAULT ('') FOR [CARD_ABB],
CONSTRAINT [DF_creditcard_ACTIVE] DEFAULT ('') FOR [ACTIVE]
GO
Mark,
make sure that on the options tab of the transform data task, 'check
constraints' is enabled.
HTH,
Paul Ibison
|||I'm sorry but I don't see the option 'check constraints'. I am running the
'Data Transformation Services Import/Export wizard'.
Here are the screens I see:
1) Choose a data source: I choose the access database
2) Choose a destination: I choose the target database
3) Specify Table Copy or Query: I choose 'Copy table(s) and view(s) from the
source database
4) Select Source Tables and Views: I select my source table and target table
here
5) Save, schedule and replicate package: I choose 'Run Immediately'
6) Completing the DTS Import/Export Wizard: I choose 'Finish' to run the
import.
At this point it appears to import a number of records and then I get the
popup indicating that it can not import the record with the NULL value in
the column.
In all of that I'm afraid I could not see a transform data task. I hate to
be dense, but could you elaborate on where I should look for that?
Many thanks!
Mark
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eNw%23ZyPQEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Mark,
> make sure that on the options tab of the transform data task, 'check
> constraints' is enabled.
> HTH,
> Paul Ibison
>
|||DTS can only carry over constraint if you go between SQL Servers. I think that Access comes with some upsizing
wizard, you might want to check in an Access groups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark Findlay" <mfindlay@.speakeasy.org> wrote in message news:%238kPFkQQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I'm sorry but I don't see the option 'check constraints'. I am running the
> 'Data Transformation Services Import/Export wizard'.
> Here are the screens I see:
> 1) Choose a data source: I choose the access database
> 2) Choose a destination: I choose the target database
> 3) Specify Table Copy or Query: I choose 'Copy table(s) and view(s) from the
> source database
> 4) Select Source Tables and Views: I select my source table and target table
> here
> 5) Save, schedule and replicate package: I choose 'Run Immediately'
> 6) Completing the DTS Import/Export Wizard: I choose 'Finish' to run the
> import.
> At this point it appears to import a number of records and then I get the
> popup indicating that it can not import the record with the NULL value in
> the column.
> In all of that I'm afraid I could not see a transform data task. I hate to
> be dense, but could you elaborate on where I should look for that?
> Many thanks!
> Mark
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eNw%23ZyPQEHA.3708@.TK2MSFTNGP10.phx.gbl...
>
|||Mark,
the package uses a data transform task in the background which has loads of
properties that are not exposed in the wizard, so the best thing to do is to
save the package without running it. After that, open it and double-click
the data transform task. On the final tab, you'll see the option I'm talking
about. The check constraints option should initiate the default's use.
HTH,
Paul Ibison

Importing data from excel spread sheet to SQL 2005

I'm attempting to import data from an Excel spread sheet into an SQL 2005
this is what I try
Select *
Into SQLServerTable
From OPENROWSET(‘Micorsoft.jet.OLEDB.4.0’,’Excel 5.0, Database =
C:\example.xls;HDR = YES’,’Select * From [example])
I got error message abour Micorsoft.jet.OLEDB.4.0’OLEDB.4.0
I also try link server option linkink excel
is there an easier way to import excel?
Thanks
In Management Studio you can right click the database name and choose Tasks
> Import Data... to open the "SQL Server Import and Export Wizard. But that
only works well for on demand import.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>
|||Kim,
Could it be that you are missing the final single quote after [example]?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>
|||Try the IMPORT wizard , as a one off
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>

Importing data from excel spread sheet to SQL 2005

I'm attempting to import data from an Excel spread sheet into an SQL 2005
this is what I try
Select *
Into SQLServerTable
From OPENROWSET(‘Micorsoft.jet.OLEDB.4.0’,’Excel 5.0, Database =
C:\example.xls;HDR = YES’,’Select * From [example])
I got error message abour Micorsoft.jet.OLEDB.4.0’OLEDB.4.0
I also try link server option linkink excel
is there an easier way to import excel?
ThanksIn Management Studio you can right click the database name and choose Tasks
> Import Data... to open the "SQL Server Import and Export Wizard. But that
only works well for on demand import.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>|||Kim,
Could it be that you are missing the final single quote after [example]?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>|||Try the IMPORT wizard , as a one off
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>

Importing data from excel spread sheet to SQL 2005

I'm attempting to import data from an Excel spread sheet into an SQL 2005
this is what I try
Select *
Into SQLServerTable
From OPENROWSET(â'Micorsoft.jet.OLEDB.4.0â',â'Excel 5.0, Database = C:\example.xls;HDR = YESâ',â'Select * From [example])
I got error message abour Micorsoft.jet.OLEDB.4.0â'OLEDB.4.0
I also try link server option linkink excel
is there an easier way to import excel?
ThanksIn Management Studio you can right click the database name and choose Tasks
> Import Data... to open the "SQL Server Import and Export Wizard. But that
only works well for on demand import.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database => C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>|||Kim,
Could it be that you are missing the final single quote after [example]?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database => C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>|||Try the IMPORT wizard , as a one off
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database => C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>