(newbie ques) Access 2000 to SQL Server migrate sync

I am a db newbie and am looking for advice on how to migrate a (fairly large - 900mb) Access b/e to SQL server 2000. The f/e is Access. I've read most of the upsizing info, but need help in understanding how to merge in new records/data which occur while the upsizing is done. (i estimate it will take several days to sort out the upsizing mess). This is a very active b/e, and not a lot of downtime is avail.

thanks for any expert advice

crunchy f.alter the access tables to add an extra column, let's call it Converted, with Required=yes, Allow zero length=No, Default='n'

run the extract to begin the upsizing process, then set Converted='y' for all records before putting the access database back online

all your existing apps can then continue as before

when the first stage of upsizing is done, do another extract, pulling only records where Converted='n' and set these to 'y' before putting the database back online

lather, rinse, repeat

rudy
http://rudy.ca/|||I am also interested in this. But just for my curiosity, can you let me know the significance of this new column etc.,
By the way, my knowledge of access and SQL and Vb is limited as I have learnt through forums like this
Thanks for your time and patience|||the significance of this new column? it marks which rows have been upsized

when you first add the column to the table, all rows have N

then you upsize all rows, and mark them Y (converted)

then the access application adds some more rows into the table, and they all get N by default

now you want to upsize only the recent ones, so you simply select them with converted='N' so that you don't pick up any rows that have already been upsized

rudy

"WHERE" condition in MSSQL2005 diff. with Oracle DB

Hi,

I got one question about MSSQL2005.

I am using "Microsoft SQL Server Migration Assistant for Oracle" to migrate Oracle Database to Microsoft SQL server 20005.

[Oracle DB]

In Oracle DB, there are two records in table

CREDITOR_ID CREDIT_ID CREDIT_LIMIT CREDIT_BALANCE CREDIT_ONHAND ACCTSTATUSID CREDITTERMSID EDIT_TIME Record 1 YT4996 XX 1 0 0 A 15 1/1/1999 14:10 Record 2 YT4996 XX 2 0 0 B 30 1/1/1998 17:21

In record 1, CREDITOR_ID='YT4996 ', there is a space after string 'YT4996'.

In record 2, CREDITOR_ID='YT4996', there is no space after string 'YT4996'.

SQL(1)

select xxx from CUSTOMER_ID='YT4996'

--> output 1 rows.

SQL(2)

select xxx from CUSTOMER_ID='YT4996 '

--> output 1 rows.

[MSSQL2005]

After migrate data to MSSQL, there are two identical records as Oracle DB.

CREDITOR_ID CREDIT_ID CREDIT_LIMIT CREDIT_BALANCE CREDIT_ONHAND ACCTSTATUSID CREDITTERMSID EDIT_TIME Record 1 YT4996 XX 1 0 0 A 15 1/1/1999 14:10 Record 2 YT4996 XX 2 0 0 B 30 1/1/1998 17:21

In record 1, CREDITOR_ID='YT4996 ', there is a space after string 'YT4996'.

In record 2, CREDITOR_ID='YT4996', there is no space after string 'YT4996'.

SQL(1)

select xxx from CUSTOMER_ID='YT4996'

--> output 2 rows.

SQL(2)

select xxx from CUSTOMER_ID='YT4996 '

--> output 2 rows.

Question:

==> In Oracle DB, 'YT4996 ' <> 'YT4996'

==> In MSSQL2005, 'YT4996 ' = 'YT4996'

Do any setting, affect MSSQL2005, make 'YT4996 ' = 'YT4996' ?

I want MSSQL2005 behaviour as Oracle....

Anyone have hint?

You must have defined the datatype for the column as varchar. This will force sqlserver to strip the trailing blank spaces. If you change the datatype to char, the trailing spaces will be preserved.|||

Thanks for info.

The column is defined as varchar(12).

but I cannot use char(12), since this column will contain variable width data, e.g. 'WT123', 'WT123456'.

If using char(12), all records in this column will become

'WT123 '

'WT123456 '

which will waste space when using char(12).

|||

"Set Ansi_Padding ON" (non-default) before creating the column will force the trailing blanks not to be truncated. This setting needs to be set also during your execution if you decide to create varchar variables and preserve the trailing blanks.

Here is a good example (from bol):

Code Snippet

SET ANSI_PADDING ON
GO
PRINT 'Testing with ANSI_PADDING ON'
GO

CREATE TABLE t1
(charcol char(16) NULL,
varcharcol varchar(16) NULL,
varbinarycol varbinary(8))
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee)
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00)

SELECT 'CHAR'='>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
GO

SET ANSI_PADDING OFF
GO
PRINT 'Testing with ANSI_PADDING OFF'
GO

CREATE TABLE t2
(charcol char(16) NULL,
varcharcol varchar(16) NULL,
varbinarycol varbinary(8))
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee)
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00)

SELECT 'CHAR'='>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
GO

DROP TABLE t1
DROP TABLE t2
GO

|||

Thanks for your info.

I had test your script, it work.

<<<BUT>>>.......

I find this "SET ANSI_NULLS ON" is not work if the COULMN defined as NOT NULL.

Here is my original script for table [CUSTOMER_CREDIT]

===============================================

USE DBSYSTEM
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [MAIN].[CUSTOMER_CREDIT]
(
[CREDITOR_ID] varchar(10) NOT NULL,
[CREDIT_ID] varchar(6) NOT NULL,
[CREDIT_LIMIT] numeric(12, 2) NULL,
[CREDIT_BALANCE] numeric(12, 2) NULL,
[CREDIT_ONHAND] numeric(12, 2) NULL,
[ACCTSTATUSID] varchar(3) NULL,
[CREDITTERMSID] varchar(3) NULL,
[EDIT_TIME] datetime NULL
)
GO
ALTER TABLE [MAIN].[CUSTOMER_CREDIT]
ADD CONSTRAINT [PK_CUSTOMER_CREDIT]
PRIMARY KEY
CLUSTERED ([CREDITOR_ID] ASC, [CREDIT_ID] ASC)

GO

If I had create the table like this:

CREATE TABLE [MAIN].[CUSTOMER_CREDIT]
(
[CREDITOR_ID] varchar(10) NULL,
[CREDIT_ID] varchar(6) NOT NULL,
.....

If the COULMN [CREDITOR_ID] defined as NULL, "SET ANSI_PADDING ON" is work, such that I have two records:

[CREDITOR_ID] = 'ABCD ' , ie space after string "ABCD".

[CREDITOR_ID] = 'ABCD' , ie no space after string "ABCD".

But if the COULMN [CREDITOR_ID] defined as "NOT NULL", "SET ANSI_PADDING ON" is NOT work.

Any ideal?

|||

Here is the script to create table and testing:

USE DBSYSTEM
GO
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MAIN].[CUSTOMER_CREDIT]') AND type in (N'U'))
BEGIN

DECLARE @.drop_statement varchar(500)

DECLARE drop_cursor CURSOR FOR
SELECT 'alter table '+quotename(schema_name(ob.schema_id))+
'.'+quotename(object_name(ob.object_id))+ ' drop constraint ' + quotename(fk.name)
FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id
WHERE fk.referenced_object_id = OBJECT_ID(N'[MAIN].[CUSTOMER_CREDIT]')

OPEN drop_cursor

FETCH NEXT FROM drop_cursor
INTO @.drop_statement

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC (@.drop_statement)

FETCH NEXT FROM drop_cursor
INTO @.drop_statement
END

CLOSE drop_cursor
DEALLOCATE drop_cursor

DROP TABLE [MAIN].[CUSTOMER_CREDIT]
END
GO

USE DBSYSTEM
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE
[MAIN].[CUSTOMER_CREDIT]
(
[CREDITOR_ID] varchar(10) NOT NULL,
[CREDIT_ID] varchar(6) NOT NULL,
[CREDIT_LIMIT] numeric(12, 2) NULL,
[CREDIT_BALANCE] numeric(12, 2) NULL,
[CREDIT_ONHAND] numeric(12, 2) NULL,
[ACCTSTATUSID] varchar(3) NULL,
[CREDITTERMSID] varchar(3) NULL,
[EDIT_TIME] datetime NULL
)
GO
ALTER TABLE [MAIN].[CUSTOMER_CREDIT]
ADD CONSTRAINT [PK_CUSTOMER_CREDIT]
PRIMARY KEY
CLUSTERED ([CREDITOR_ID] ASC, [CREDIT_ID] ASC)

GO
===============================================================

SET ANSI_PADDING ON

GO

INSERT INTO [MAIN].[CUSTOMER_CREDIT] VALUES ('YT4996', 'XX','2400','0','0','C','30',CURRENT_TIMESTAMP)

GO

SET ANSI_PADDING ON

GO

INSERT INTO [MAIN].[CUSTOMER_CREDIT] VALUES ('YT4996 ','XX','0' ,'0','0','S','15',CURRENT_TIMESTAMP)

GO

SELECT 'VARCHAR'='>' + CREDITOR_ID + '<' FROM [MAIN].[CUSTOMER_CREDIT]
GO

"Command text was not set for the command object" Error

Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:

Description: An OLE DB Error has occured. Error code: 0x80040E0C.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".

.........

Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".

This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:

private bool BuildPackage()

{

// Create the package object

oPackage = new Package();

// Create connections for the Foxpro and SQL Server data

Connections oPkgConns = oPackage.Connections;

// Foxpro Connection

ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");

oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere

oFoxConn.Name = "SourceConnectionOLEDB";

oFoxConn.Description = "OLEDB Connection For Foxpro Database";

// SQL Server Connection

ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");

oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere

oSQLConn.Name = "DestinationConnectionOLEDB";

oSQLConn.Description = "OLEDB Connection For SQL Server Database";

// Add Prepare SQL Task

Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");

TaskHost thSQLTask = exSQLTask as TaskHost;

thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");

thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);

thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);

thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @."C:\LPFMigrate\LPF_Script.sql");

thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);

thSQLTask.FailPackageOnFailure = true;

// Add Data Flow Tasks. Create a separate task for each table.

// Get a list of tables from the source folder

arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");

for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)

{

// Get the name of the file from the array

sDataFile = Path.GetFileName(arFiles[iCount].ToString());

sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);

oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;

oDataFlow.AutoGenerateIDForNewObjects = true;

// Create the source component

IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();

oSource.Name = (sDataFile + "Src");

oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper srcDesignTime = oSource.Instantiate();

srcDesignTime.ProvideComponentProperties();

// Add the connection manager

if (oSource.RuntimeConnectionCollection.Count > 0)

{

oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;

oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);

}

// Set Custom Properties

srcDesignTime.SetComponentProperty("AccessMode", 0);

srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);

srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);

// Re-initialize metadata

srcDesignTime.AcquireConnections(null);

srcDesignTime.ReinitializeMetaData();

srcDesignTime.ReleaseConnections();

// Create Destination component

IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();

oDestination.Name = (sDataFile + "Dest");

oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper destDesignTime = oDestination.Instantiate();

destDesignTime.ProvideComponentProperties();

// Add the connection manager

if (oDestination.RuntimeConnectionCollection.Count > 0)

{

oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;

oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);

}

// Set custom properties

destDesignTime.SetComponentProperty("AccessMode", 2);

destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);

destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");

// Create the path to link the source and destination components of the dataflow

IDTSPath90 dfPath = oDataFlow.PathCollection.New();

dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);

// Iterate through the inputs of the component.

foreach (IDTSInput90 input in oDestination.InputCollection)

{

// Get the virtual input column collection

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the column collection

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

// Call the SetUsageType method of the design time instance of the component.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

}

//Map external metadata to the inputcolumn

foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)

{

IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();

externalColumn.Name = inputColumn.Name;

externalColumn.Precision = inputColumn.Precision;

externalColumn.Length = inputColumn.Length;

externalColumn.DataType = inputColumn.DataType;

externalColumn.Scale = inputColumn.Scale;

// Map the external column to the input column.

inputColumn.ExternalMetadataColumnID = externalColumn.ID;

}

}

}

// Add precedence constraints to the package executables

PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);

pcTasks.Value = DTSExecResult.Success;

for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)

{

pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);

pcTasks.Value = DTSExecResult.Success;

}

// Validate the package

DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);

// Check if the package was successfully executed

if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))

{

string sErrorMessage = "";

foreach (DtsError pkgError in oPackage.Errors)

{

sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "\n";

sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "\n";

sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "\n";

sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "\n";

sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "\n";

sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "\n";

sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "\n";

sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;

}

MessageBox.Show("The DTS package was not built successfully because of the following error(s):\n\n" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);

return false;

}

// return a successful result

return true;

}

So the OLE-DB Destination is not happy and I cannot see anything obvious. A better way to debug this would be to just add a Save method before validation to save your package to disk. You can then open it in Visual Studio/BIDS, and enjoy the full GUI experience to help find the problem. I've found this method very useful for just checking packages when building programmatically.

|||

I have inserted code to save the package just before the validation but it throws an exception. And I don't find the help in MSDN very useful!! I want to save the package in the folder C:\LPFMigrate and call it Package.dtsx. The code is as follows:

oApp = new Microsoft.SqlServer.Dts.Runtime.Application();

oApp.SaveToDtsServer(oPackage, null, @."C:\LPFMigrate\Package", "MARS");

Any idea why an exception is being thrown? The exception message is:

The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))