Data Source passwords in Config Files

Anybody find a sound approach to being able to store DataSource connection information in a configuration and be able to access a Data Source that requires a password for login?

Storing it as a configuraiton in the DB is not an option, as you require the pw to access the DB that would store it.

Can security be applied to an environment variable? Registry?

Anyone successfully use the registry to store configuration values?I'm not sure what your criterion for sound is, so I don't know if this meets it :), but you can store credentials (username & password) in a configuration file, and then make an environment variable which gives its path, and then configure a Package Configuration in the SSIS package to use the environment variable.
Of course you have to configure the environment variable for each machine (and for each user, if two users on one machine will use different credentials -- therefore, different configuration files).
Then, the credentials being now in one or more files, secure access to these files via traditional NTFS ACLs (file pemissions).
|||

I guess by sound, I meant, any way that works... when i store the pw in the config the .xml file represents it like the following:

- <Configuration ConfiguredType="Property" Path="\Package.Connections [MDB].Properties[Password]" ValueType="String">

<ConfiguredValue />

</Configuration>

This results in the error:
Error 1 Validation error. Data Flow Task: OLE DB Source [1]: The AcquireConnection method call to the connection manager "MDB" failed with error code 0xC0202009. Package2.dtsx 0 0

Have you been able to do this?.. am I missing something? I am also storing the connection string, initial catalog, and username in the config file.

|||When the file is generated by Visual Studio, it will not write the pw. I was able to get it to work by modifying the file directly.. duh.|||I'm assuming that you're not on a domain and so cannot use integrated authentication?
K|||Yes, Joe, We've been using the solution I explained above, with different configurations for different developers on the same machine (so they can use different login names, so the processes are distinguishable in SQL Server Enterprise Manager).
But, we've been using SQL Server logons (not integrated), because we're using a SQL Server 2000 backend. What you quoted sounds like you're using an mdb backend, which we've not tried.
To set up a new configuration with a new set of credentials, I first add the environment variable for that user, pointing it to where the config file is going to be. Then I put a config file there -- I take an existing one, and using a text editor, change the logon name & password (stored in plaintext in the file).
The very first time we did this, SSIS generated the config file for us; since then, we've been hand editing it for each machine and/or credential combination.

|||Here is an edited copy of our config file. Everything beginning with Zz is something that I edited before posting (along with the nifty DEADBEEF guid).
Also I added the carriage returns and whitespaces -- the raw config file is a whitespaceless mess.
******************

<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="ZzMachineName\ZzUserName" GeneratedFromPackageName="Zz Our Nifty Package" GeneratedFromPackageID="{DEADBEEF-AAAA-BBBB-CCCC-DEADBEEFBEEF}" GeneratedDate="7/26/2005 4:25:01 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_CONN_NAME].Properties[Password]" ValueType="String">
<ConfiguredValue>ZzBigSecretPassword</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_CONN_NAME].Properties[UserName]" ValueType="String">
<ConfiguredValue>ZzLogonNameForThisDeveloperHere</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_NOTHER_CONN].Properties[Password]" ValueType="String">
<ConfiguredValue>ZzBigSecretPassword</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_NOTHER_CONN].Properties[UserName]" ValueType="String">
<ConfiguredValue>ZzLogonNameForThisDeveloperHere</ConfiguredValue>
</Configuration>
</DTSConfiguration>

Data Source access

Hi All,
I'm looking to create an ad-hoc query generator so that I can create a
report on the fly, but I would like to use RS Shared Data Sources for this.
I have run into a problem though, in that I can't retrieve the password for
the Shared Data Source. Is there any way to get the Data Source back as a
connection to the database in ADO?
Thanks in advacne,
CathieNo you cannot get the data source back as connection. But you could try to
implement a custom RS data extension and use it then in the shared data
source.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cathie" <cathie@.somewhere.com> wrote in message
news:eqY8s64SFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I'm looking to create an ad-hoc query generator so that I can create a
> report on the fly, but I would like to use RS Shared Data Sources for
> this. I have run into a problem though, in that I can't retrieve the
> password for the Shared Data Source. Is there any way to get the Data
> Source back as a connection to the database in ADO?
> Thanks in advacne,
> Cathie
>|||Hi Robert,
I'm sorry I don't understand how a custom RS data extension would help me.
I am want to be able to get any datasource's (within RS) tables, views,
stored procedures etc. How would a custom RS data extension help?
Cathie
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OIBjdIgTFHA.1404@.TK2MSFTNGP09.phx.gbl...
> No you cannot get the data source back as connection. But you could try to
> implement a custom RS data extension and use it then in the shared data
> source.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Cathie" <cathie@.somewhere.com> wrote in message
> news:eqY8s64SFHA.2128@.TK2MSFTNGP14.phx.gbl...
>> Hi All,
>> I'm looking to create an ad-hoc query generator so that I can create a
>> report on the fly, but I would like to use RS Shared Data Sources for
>> this. I have run into a problem though, in that I can't retrieve the
>> password for the Shared Data Source. Is there any way to get the Data
>> Source back as a connection to the database in ADO?
>> Thanks in advacne,
>> Cathie
>|||If you go through a custom data extension, you would need to implement our
IDBConnection etc. interfaces. Hence, in the custom data extension you would
see the actual connection string etc. used to connect to the data source.
You could use this information to do then some adhoc query generation. What
I'm basically saying is that you cannot easily do the adhoc query generation
from within the report in RS 2000.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cathie" <cathie@.somewhere.com> wrote in message
news:e5GQ9F4TFHA.2712@.TK2MSFTNGP09.phx.gbl...
> Hi Robert,
> I'm sorry I don't understand how a custom RS data extension would help me.
> I am want to be able to get any datasource's (within RS) tables, views,
> stored procedures etc. How would a custom RS data extension help?
> Cathie
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OIBjdIgTFHA.1404@.TK2MSFTNGP09.phx.gbl...
>> No you cannot get the data source back as connection. But you could try
>> to implement a custom RS data extension and use it then in the shared
>> data source.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Cathie" <cathie@.somewhere.com> wrote in message
>> news:eqY8s64SFHA.2128@.TK2MSFTNGP14.phx.gbl...
>> Hi All,
>> I'm looking to create an ad-hoc query generator so that I can create a
>> report on the fly, but I would like to use RS Shared Data Sources for
>> this. I have run into a problem though, in that I can't retrieve the
>> password for the Shared Data Source. Is there any way to get the Data
>> Source back as a connection to the database in ADO?
>> Thanks in advacne,
>> Cathie
>>
>

Data sequence in tables, please help

I'm new to sql server 2005. Previously I used Access. I've noticed that in sql server when a record is inserted into the database table, the data does not get entered sequentially. I looked at the id column and saw that the order numbers are mixed up. Is this something that is common in sql server, and how can I change this. I need the data to be ordered in the order that it was inserted into the database.

Can someone please help.

SQLServer does not enforce any order of data for storage purposes.

If you wish to have the data presented in a sorted, or ordered, scheme, you MUST use an ORDER BY on the query.

IF you have an IDENTITY field (Column) in the table, it will sequentially number all rows that are added to the table. Verify that your table has the [ID] column defined as IDENTITY.

Also, you may wish to review the list of reserved words to be sure that you do not name tables and columns with words that have special meaning for SQL Server. You may also wish to avoid common words for your Table and Column names in order to reduce confustion. You will have to use square brackets anytime your table or column names violate the reserved word list.

Refer to Books Online, Topic: 'Reserved Words [SQL Server]'

|||

it seems like your table is a heap. ie. there is no clustered index. in that case the data will be in the order how u have inserted. In a relational database it does not really matter since while selecting you can order the records set. But first of all you need to define a primary key for this table and you should create a clustered index(not necessary to create on PK) .

Madhu

Data security

My boss would like to store sensitive information in our company SQL Server
database. At the momnt everyone has write access to all data in the
database. in addition the developers can basically do anything with the
database.
I am using Windows security and need to deny acces to the data in the table
to eeryone apart from a couple of groups. In some cases i need to deny
access to data just in one column. I know you can do this, but how reliable
is it?
I am trying to figure out where to start on this.
Should the sensitive data only live in the live database? Should we release
the database and revoke rights at tat moment in time? Presumably i could
write a script to do this.
Is there any way to have the sensitive data in a database that the
developers are developing and be comfortable that the data is secure?
Is the only really safe harbour encryption?
Also, if anyone out there knows a good book on security architecture for SQL
Server, a recommendation would be greatly appreciated.
TIA
SarahSQL Server security is reliable and it works... Regarding DEV... Some
companies treat highly sensitive data by jumbling up anything that
uniquelly identifies the individual prior to making a copy of the data for
devs.
I worked in Healthcare Development, and we all tested with copies of live
sensitive data most of the time... However there were occasions when we
would get copies of data from hospitals that came in with all identifying
data scrambled or removed.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Why not put it in a seperate database or table, linked to your original
database, and then you have complete control on permissions, without as much
chance of making an 'error' and granting access to the wrong peeps ?
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||look into using Stored procedures. these can be used with logins. and grant
rights.
it is a lot of work, but will give you the best protection.
Next is to use the encrypt table. I don't like that option my self.
the quickest way to isolate a DB is to user another DB to make the calls to
the stored procedures.
Note: if you SQL server, can be physically accessed by anyone, then all
these procedures are useless.
"Sarah" <Sarah@.TT.com> wrote in message
news:#mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Use a copy of the database and run a qry on User & Company identifying
details and Swap all the identifying records around.
Did this when I pass on a production db to another developer.
Don Grover
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>

Data security

My boss would like to store sensitive information in our company SQL Server
database. At the momnt everyone has write access to all data in the
database. in addition the developers can basically do anything with the
database.
I am using Windows security and need to deny acces to the data in the table
to eeryone apart from a couple of groups. In some cases i need to deny
access to data just in one column. I know you can do this, but how reliable
is it?
I am trying to figure out where to start on this.
Should the sensitive data only live in the live database? Should we release
the database and revoke rights at tat moment in time? Presumably i could
write a script to do this.
Is there any way to have the sensitive data in a database that the
developers are developing and be comfortable that the data is secure?
Is the only really safe harbour encryption?
Also, if anyone out there knows a good book on security architecture for SQL
Server, a recommendation would be greatly appreciated.
TIA
SarahSQL Server security is reliable and it works... Regarding DEV... Some
companies treat highly sensitive data by jumbling up anything that
uniquelly identifies the individual prior to making a copy of the data for
devs.
I worked in Healthcare Development, and we all tested with copies of live
sensitive data most of the time... However there were occasions when we
would get copies of data from hospitals that came in with all identifying
data scrambled or removed.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Why not put it in a seperate database or table, linked to your original
database, and then you have complete control on permissions, without as much
chance of making an 'error' and granting access to the wrong peeps ?
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||look into using Stored procedures. these can be used with logins. and grant
rights.
it is a lot of work, but will give you the best protection.
Next is to use the encrypt table. I don't like that option my self.
the quickest way to isolate a DB is to user another DB to make the calls to
the stored procedures.
Note: if you SQL server, can be physically accessed by anyone, then all
these procedures are useless.
"Sarah" <Sarah@.TT.com> wrote in message
news:#mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Use a copy of the database and run a qry on User & Company identifying
details and Swap all the identifying records around.
Did this when I pass on a production db to another developer.
Don Grover
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>

Data Reader Source cannot be configured

Hi,

I am using a Ado Connection Manager to connect to a M S Access source.

But when I use this connection Manager in Data Reader Source, I am Not able to Configure Data reader Source. It gives exception "Cannot Acquire Managed Connection From Run Time Connection Manager".

Can anyone help on this.

Thanks

Dharmbir

Wenyang,

The .mdb Does not matter, If I create a ADO Connection Manager, Data Reader Does Not Support.

Thanks

Dharmbir

|||I have new Integration Services Project in Microsoft Visual Studio version 8.0.50727.42, under .NET framework version 2.0.50727. I'm trying to set up a simple replication from an ODBC data source to a new MSSQL 2005 database. I created two entries in the Connection Managers list and successfully tested the connection for each one.

I see the same "Cannot acquire a managed connection from the run-time connection manager." error after setting the Connection Manager in a DataReader Source object's Advanced Editor dialog box to either entry in Connection Managers. The DataReader Source appears to be the only Data Flow Source that will allow me to connect to an ODBC datasource.

Is there a log somewhere or a list of possible reasons for this inability to connect? Does Visual Studio allow for the creation of a package that uses a non-managed connection?

Also, I made a data source for each of my connection managers in the Data Sources folder of my project. Are they necessary?

Thanks for any help you can give us.|||

It works for me fine. There are a couple things to check

1) You used ".Net Provider for OleDb/Microsoft Jet 4.0 OLEDB Provider", correct?

2) When you created your ADO.Net connection, did you set username/passwd correctly - did "Test Connection" return success?

Thanks

Wenyang

|||

Hi Wenyang,

1.) I used ADO\Microsoft Jet 4.0 OLEDB Provider

2.) Test connection shows its passed.

Thanks

Dharmbir

|||

If picking which .mdb file does not matter the repro, please pass me a small sample mdb which failed for you and I'll try to see why we saw different results. Pls send to Wenyang.Hu at Microsoft.com

Thanks

Wenyang

Data Reader Source cannot be configured

Hi,

I am using a Ado Connection Manager to connect to a M S Access source.

But when I use this connection Manager in Data Reader Source, I am Not able to Configure Data reader Source. It gives exception "Cannot Acquire Managed Connection From Run Time Connection Manager".

Can anyone help on this.

Thanks

Dharmbir

It works for me fine. There are a couple things to check

1) You used ".Net Provider for OleDb/Microsoft Jet 4.0 OLEDB Provider", correct?

2) When you created your ADO.Net connection, did you set username/passwd correctly - did "Test Connection" return success?

Thanks

Wenyang

|||

Hi Wenyang,

1.) I used ADO\Microsoft Jet 4.0 OLEDB Provider

2.) Test connection shows its passed.

Thanks

Dharmbir

|||

If picking which .mdb file does not matter the repro, please pass me a small sample mdb which failed for you and I'll try to see why we saw different results. Pls send to Wenyang.Hu at Microsoft.com

Thanks

Wenyang

|||

Wenyang,

The .mdb Does not matter, If I create a ADO Connection Manager, Data Reader Does Not Support.

Thanks

Dharmbir

|||I have new Integration Services Project in Microsoft Visual Studio version 8.0.50727.42, under .NET framework version 2.0.50727. I'm trying to set up a simple replication from an ODBC data source to a new MSSQL 2005 database. I created two entries in the Connection Managers list and successfully tested the connection for each one.

I see the same "Cannot acquire a managed connection from the run-time connection manager." error after setting the Connection Manager in a DataReader Source object's Advanced Editor dialog box to either entry in Connection Managers. The DataReader Source appears to be the only Data Flow Source that will allow me to connect to an ODBC datasource.

Is there a log somewhere or a list of possible reasons for this inability to connect? Does Visual Studio allow for the creation of a package that uses a non-managed connection?

Also, I made a data source for each of my connection managers in the Data Sources folder of my project. Are they necessary?

Thanks for any help you can give us.sql

Data Reader Source cannot be configured

Hi,

I am using a Ado Connection Manager to connect to a M S Access source.

But when I use this connection Manager in Data Reader Source, I am Not able to Configure Data reader Source. It gives exception "Cannot Acquire Managed Connection From Run Time Connection Manager".

Can anyone help on this.

Thanks

Dharmbir

It works for me fine. There are a couple things to check

1) You used ".Net Provider for OleDb/Microsoft Jet 4.0 OLEDB Provider", correct?

2) When you created your ADO.Net connection, did you set username/passwd correctly - did "Test Connection" return success?

Thanks

Wenyang

|||

Hi Wenyang,

1.) I used ADO\Microsoft Jet 4.0 OLEDB Provider

2.) Test connection shows its passed.

Thanks

Dharmbir

|||

If picking which .mdb file does not matter the repro, please pass me a small sample mdb which failed for you and I'll try to see why we saw different results. Pls send to Wenyang.Hu at Microsoft.com

Thanks

Wenyang

|||

Wenyang,

The .mdb Does not matter, If I create a ADO Connection Manager, Data Reader Does Not Support.

Thanks

Dharmbir

|||I have new Integration Services Project in Microsoft Visual Studio version 8.0.50727.42, under .NET framework version 2.0.50727. I'm trying to set up a simple replication from an ODBC data source to a new MSSQL 2005 database. I created two entries in the Connection Managers list and successfully tested the connection for each one.

I see the same "Cannot acquire a managed connection from the run-time connection manager." error after setting the Connection Manager in a DataReader Source object's Advanced Editor dialog box to either entry in Connection Managers. The DataReader Source appears to be the only Data Flow Source that will allow me to connect to an ODBC datasource.

Is there a log somewhere or a list of possible reasons for this inability to connect? Does Visual Studio allow for the creation of a package that uses a non-managed connection?

Also, I made a data source for each of my connection managers in the Data Sources folder of my project. Are they necessary?

Thanks for any help you can give us.

Data not visible??

I recently imported some Access tables into SQL Server. I can go into enterprise manager and return all rows and the data is there.

I build an ASP page to edit the data. After the connection the data for the record is displayed in a form. For some reason some fields are not coming through?

Is this a problem when converting to SQL Server. It just doesn't make sense that the table in Enterprise manager says the field is populated but when I do a pull via ASP the data doesn't come through.

Does anyone know anything about this?

Any help or pointing in the right direction would be greatly appreciated.

Thanks,

JWExecute your query in Query Analyzer. Compare the results to those obtained through your ASP page.|||There is nothing special about the query. It's select all from the table but by the vaiable ID number that is passed.

See the weird thing is, is that it doesn't bomb out or anything and brings back some fields in the recordset but not all. But then I return all rows in query analyzer and the data for those fields is populated.|||Please post the ASP code that executes the query and displays the data from the recordset.|||<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=golfinam;UID=golfinam;PWD=XXXXXX;DATABASE=XXXX XX"
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tbl_Courses where CourseID = " & request.querystring("CourseID")
objRS.Open strSQL, objConn%>
<% if ObjRS.eof then %><% else %>

'Then this is where the form is with each forms default set to a field,
<textarea rows="7" name="PTPDirections" cols="53"><%=objRS("PTPDirections")%></textarea>

<%End If%>
<%'Close the Recordset object
objRS.Close

'Delete the Recordset Object
Set objRS = Nothing

'Close the Connection object
objConn.Close

'Delete the Connection Object
Set objConn = Nothing
%>|||Is there a setting or something in the import that would cause it to act this way?

Data Not Copying to MS Access Table

Hello,

I have a Data Flow Source that uses a SQL Command to pull data. In the SQL statement, I used CAST to change all varchar types to Nvarvchar to suit MS Access. I can preview the data from the source. In testing, the SQL statement only pulls about ten records.

I have a Microsoft 2000 Access database table as a destination. Data in each column in the table is required, and all columns have defaults.

I also have a grid data viewer set up. I have the DefaultBufferMaxRows set to 2 so that I can see data going across. When I execute this dataflow, no data is transfered to the Access database table. No data shows up in the dataviewer. There are no errors. The 'Execution Results' tab does not show errors, but indicates that zero rows were transfered. There are no warnings.

How do I begin to isolate the problem? The following is the SQL Statement in the Data Flow Source. Thank you for your help! - cdun2

DECLARE @.CategoryTable TABLE
(ColID Int,
ColCategory varchar(60),
ColValue varchar(500)
)

--and fill it

INSERT INTO @.CategoryTable
(ColID, ColCategory, ColValue)
SELECT
0,
LEFT(RawCollectionData,CHARINDEX(':',RawCollectionData)),
LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',RawCollectionData)+1,255))
FROM Collections_Staging

--Assign an ID to each block of data for each occurance of 'Reason:'

DECLARE @.ID int
SET @.ID = 1
UPDATE @.CategoryTable
SET [ColID] = CASE WHEN ColCategory = 'Reason:' THEN @.ID - 1 ELSE @.ID END,
@.ID = CASE WHEN ColCategory = 'Reason:' THEN @.ID + 1 ELSE @.ID END

--Then put the data together

SELECT --cast to Nvarchar for MSAccess
a.ColID,
CAST(a.ColValue as Nvarchar(30)) AS OrderID,
COALESCE(CAST(b.ColValue as Nvarchar(30)),'') AS SellerUserID,
COALESCE(CAST(c.ColValue as Nvarchar(100)),'') AS BusinessName,
COALESCE(CAST(d.ColValue as Nvarchar(15)),'') AS BankID,
COALESCE(CAST(e.ColValue as Nvarchar(15)),'') AS AccountID,
COALESCE(CAST(SUBSTRING(f.ColValue,CHARINDEX('$',f.ColValue)+1,500)AS DECIMAL(18,2)),0) AS CollectionAmount,
COALESCE(CAST(g.ColValue as Nvarchar(10)),'') AS TransactionType,
CASE
WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL
ELSE CAST(h.ColValue AS SmallDateTime)
END AS DisbursementDate,
--COALESCE(h.ColValue,'') AS DisbursementDate,
CASE
WHEN i.ColValue LIKE '%Matching Disbursements%' THEN NULL
WHEN CAST(LEFT(REVERSE(i.ColValue),4)AS INT) > 1000 THEN CAST(i.ColValue AS SmallDateTime)
WHEN LEFT(REVERSE(i.ColValue),4) = '1000' THEN NULL
END AS ReturnDate,
--COALESCE(i.ColValue,'') AS ReturnDate,
COALESCE(CAST(j.ColValue as Nvarchar(4)),'') AS Code,
COALESCE(CAST(k.ColValue as Nvarchar(255)),'') AS CollectionReason
FROM @.CategoryTable a
LEFT JOIN @.CategoryTable b ON b.ColID = a.ColID AND b.ColCategory = 'Seller UserId:'
LEFT JOIN @.CategoryTable c ON c.ColID = a.ColID AND c.ColCategory = 'Business Name:'
LEFT JOIN @.CategoryTable d ON d.ColID = a.ColID AND d.ColCategory = 'Bank ID:'
LEFT JOIN @.CategoryTable e ON e.ColID = a.ColID AND e.ColCategory = 'Account ID:'
LEFT JOIN @.CategoryTable f ON f.ColID = a.ColID AND f.ColCategory = 'Amount:'
LEFT JOIN @.CategoryTable g ON g.ColID = a.ColID AND g.ColCategory = 'Transaction Type:'
LEFT JOIN @.CategoryTable h ON h.ColID = a.ColID AND h.ColCategory = 'Disbursement Date:'
LEFT JOIN @.CategoryTable i ON i.ColID = a.ColID AND i.ColCategory = 'Return Date:'
LEFT JOIN @.CategoryTable j ON j.ColID = a.ColID AND j.ColCategory = 'Code:'
LEFT JOIN @.CategoryTable k ON k.ColID = a.ColID AND k.ColCategory = 'Reason:'

WHERE a.ColCategory = 'Order ID:'

Are you doing this on an x64 machine. Is it possible that your package is executed in 64-bit mode? There is no 64-bit version of the JET provider.

There have been many posts about this before. Seaarch this forum to get instructions for making sure the packages are executed in 32-bit mode.

Thanks,

Bob

|||

Hi Cdun,

When you preview the Source, do you find any rows existing for the query? If yes, When you execute the dataflow task can you see any rows in Data grid viewer?

In fact if there are rows, it must be transfered to the destination table.

Thanks

Subhash Subramanyam

|||

Subhash Subramanyam wrote:

When you execute the dataflow task can you see any rows in Data grid viewer?

There are rows in the preview, but no rows in the data grid viewer. I'll check into the 64 bit setting.

|||

Bob Bojanic - MSFT wrote:

Seaarch this forum to get instructions for making sure the packages are executed in 32-bit mode.

Thanks,

Bob

I went to the package properties, debugging, and set Run64BitRuntime to False. I still get the same result The data will preview, but will not transfer into the Access table.

In the Data Flow Task, I'm using an OLEDB source to execute the sql statement. Should I be using an Execute SQL Task instead? The sql statement also uses a TABLE variable. Could this be a problem?

|||There must be something wrong with the way I'm trying to deliver the data to the Access table, because I can't get data to a sql server table destination either.|||

Never mind on this. I resorted to creating a table UDF as the source, and it looks like that will work.

cdun2

Data Modeling Question: One Entity or Two

I am evaluating an existing MS Access database for a new client - with the
intent to migrate it to SQL Server and possibly change/improve the
relational database design in the process.
The client is a medical specialty office in which patients show up with lab
results taken by their primary care physician (PCP) prior to the patient
showing up at my client's specialty office. My client's office then proceeds
to take additional lab measurements over time. The data collected by the PCP
and my client's office is practically the same.
My client's existing MS Access database stores this information in two
tables - one for labs taken at the PCP office (and apparently only the most
recent set of results prior to the patient showing up at my client's
office); and another table for labs taken at my client's office (and
measured over time). In the existing database these two tables have similar
(almost identical) columns.
The client's in-house DBA sees these tables as representing two entities,
not one (one entity is "last set of labs measured by the PCP" and the other
entity is "labs measured in-house; over time"). I understand all these lab
results as one entity ("lab results"); and therefore we can/should move all
this data into one table when we migrate the data to the new SQL Server
database.
What do you think? Do these lab results represent one entity or two?
FWIW: The business managers do not differentiate between the two types of
labs (measured by PCP vs in-house).
Thanks!Sounds to me like one entity. I would add a column to one existing table
(possibly a CHAR(1)) that specified the source of the results. Be careful
in how you approach it though, as they might understand some arcane medical
regulation as specifying that they have to keep these data items physically
separated in some fashion (usually a misunderstanding of the regulations by
management, but I've seen stranger things...)
"Jeff" <A@.B.COM> wrote in message
news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
>I am evaluating an existing MS Access database for a new client - with the
>intent to migrate it to SQL Server and possibly change/improve the
>relational database design in the process.
> The client is a medical specialty office in which patients show up with
> lab results taken by their primary care physician (PCP) prior to the
> patient showing up at my client's specialty office. My client's office
> then proceeds to take additional lab measurements over time. The data
> collected by the PCP and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the
> most recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have
> similar (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the
> other entity is "labs measured in-house; over time"). I understand all
> these lab results as one entity ("lab results"); and therefore we
> can/should move all this data into one table when we migrate the data to
> the new SQL Server database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
>|||A good case for horizontal partitioning. With sql2k, I would keep them as 2
seperate entities (i.e 2 tables). But if you're on sql2k5, you might want to
look into table partitioning. There is definitely a performance gained by
partitioning them 'cuz the data for each will be smaller. Though, it would
require a union/join to look for data in both entities - a minor drawback in
this case.
-oj
"Jeff" <A@.B.COM> wrote in message
news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
>I am evaluating an existing MS Access database for a new client - with the
>intent to migrate it to SQL Server and possibly change/improve the
>relational database design in the process.
> The client is a medical specialty office in which patients show up with
> lab results taken by their primary care physician (PCP) prior to the
> patient showing up at my client's specialty office. My client's office
> then proceeds to take additional lab measurements over time. The data
> collected by the PCP and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the
> most recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have
> similar (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the
> other entity is "labs measured in-house; over time"). I understand all
> these lab results as one entity ("lab results"); and therefore we
> can/should move all this data into one table when we migrate the data to
> the new SQL Server database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
>|||Jeff wrote:
> [...] Do these lab results represent one entity or two?
I would use separate tables only if there is some information that
should be handled differently, for example if:
- the in-house lab results should be linked to a table about payments
(whereas the PCP resuls should not);
- the in-house lab results should have a MeasurerID column that refers
to our Employees table;
- etc.
Think about the such differences between the two types of lab results;
of course, if there are very few differences, they can be handled by
using a null-able column in the unified table (along some check
constraints, to enforce that for a particular type that column should
not be null). However, if there are more differences, having a lot of
null-able columns would not be "a Good Thing", so a two-tables approach
may be better in such a case.
Razvan|||If one table only has the most recent results, and the other tracks history,
you may be better off keeping them seperate. It could be a nightmare
cleaning up the "old" rows from the PCP if they are in the same table with
the history data. Also, if the foreign keys behave differently in these two
tables then you would want to keep them seperate.
"Jeff" <A@.B.COM> wrote in message
news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
> I am evaluating an existing MS Access database for a new client - with the
> intent to migrate it to SQL Server and possibly change/improve the
> relational database design in the process.
> The client is a medical specialty office in which patients show up with
lab
> results taken by their primary care physician (PCP) prior to the patient
> showing up at my client's specialty office. My client's office then
proceeds
> to take additional lab measurements over time. The data collected by the
PCP
> and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the
most
> recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have
similar
> (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the
other
> entity is "labs measured in-house; over time"). I understand all these lab
> results as one entity ("lab results"); and therefore we can/should move
all
> this data into one table when we migrate the data to the new SQL Server
> database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
>|||Jeff wrote:
> I am evaluating an existing MS Access database for a new client - with the
> intent to migrate it to SQL Server and possibly change/improve the
> relational database design in the process.
> The client is a medical specialty office in which patients show up with la
b
> results taken by their primary care physician (PCP) prior to the patient
> showing up at my client's specialty office. My client's office then procee
ds
> to take additional lab measurements over time. The data collected by the P
CP
> and my client's office is practically the same.
> My client's existing MS Access database stores this information in two
> tables - one for labs taken at the PCP office (and apparently only the mos
t
> recent set of results prior to the patient showing up at my client's
> office); and another table for labs taken at my client's office (and
> measured over time). In the existing database these two tables have simila
r
> (almost identical) columns.
> The client's in-house DBA sees these tables as representing two entities,
> not one (one entity is "last set of labs measured by the PCP" and the othe
r
> entity is "labs measured in-house; over time"). I understand all these lab
> results as one entity ("lab results"); and therefore we can/should move al
l
> this data into one table when we migrate the data to the new SQL Server
> database.
> What do you think? Do these lab results represent one entity or two?
> FWIW: The business managers do not differentiate between the two types of
> labs (measured by PCP vs in-house).
> Thanks!
In principle if two potential entities have the same attributes then
they are only one entity.
In fact you can go further and say that entities that overlap (sharing
a common key and a common subset of non-key attributes) should be
represented as a single entity.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the feedback Jim. Can you please clarify "if the foreign keys
behave differently"?
What do you mean by "behave differently?
Thanks!
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eKkGd9ljGHA.3588@.TK2MSFTNGP02.phx.gbl...
> If one table only has the most recent results, and the other tracks
> history,
> you may be better off keeping them seperate. It could be a nightmare
> cleaning up the "old" rows from the PCP if they are in the same table with
> the history data. Also, if the foreign keys behave differently in these
> two
> tables then you would want to keep them seperate.
> "Jeff" <A@.B.COM> wrote in message
> news:%23n1oTddjGHA.3496@.TK2MSFTNGP02.phx.gbl...
> lab
> proceeds
> PCP
> most
> similar
> other
> all
>|||If the two tables have different constraints, or foreign keys that point to
different tables, with different validation, then two tables is probably a
good idea.
If all of the columns in the tables share the same validation, against the
same tables, then one makes sense.
Here is one consideration, however...
If you did put all the data in one table, how do you enforce the business
rule that the PCP data have only one row and the internal data be allowed
history? Also, you need to discern between the two in the app. If you can
accomplish both of those (I'm sure there are many ways) then you should be
fine with one table.
OK, that was two considerations...
If I am being too vague I apologize. Without truly understanding the
business reason for keeping the data separate to begin with it is hard to
say exactly what should be done. From your own description one table seems
to make sense, but if their in house DBA were to explain it my opinion might
change.
"Jordan Richard" <A@.B.COM> wrote in message
news:eUY2mKqjGHA.3496@.TK2MSFTNGP02.phx.gbl...
> Thanks for the feedback Jim. Can you please clarify "if the foreign keys
> behave differently"?
> What do you mean by "behave differently?
> Thanks!
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eKkGd9ljGHA.3588@.TK2MSFTNGP02.phx.gbl...
with
patient
the
entities,
of
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:O6Ve%238ujGHA.4660@.TK2MSFTNGP03.phx.gbl...
> If you did put all the data in one table, how do you enforce the business
> rule that the PCP data have only one row and the internal data be allowed
> history? Also, you need to discern between the two in the app. If you
> can
> accomplish both of those (I'm sure there are many ways) then you should be
> fine with one table.
You can address issue #1 via trigger. #2 can be addressed by adding a
column that specifies where the data came from (i.e., CHAR(1), 'L' = Local
source, 'P' = Partner source, etc.) Of course existing apps would have to
be modified.
Another consideration here might be how often you currently have to combine
the data from the two tables in a query. If you combine them often for
reporting or other purposes, combining them on the server makes sense. If
you don't ever combine them when querying, combining them might not be a
high priority.|||>> What do you think? Do these lab results represent one entity or two? ..
FWIW: The business managers do not differentiate between the two types of l
abs (measured by PCP vs in-house). <<
What is the LOGICAL difference between them? Apparently, none. Ergo,
you use one table and column for the lab_type. Leave this encoding
open enough that you can extend it lately, when add other sources.

Data migration from MSAccess to SQL Express 2005

Hi ,

I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?

Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access. The constraint here is that the data needs to be migrated to a completely new schema. I just need to migrate data only and no other objects.

Thanks

Mahesh

Hello,

I am not replying here with any solution as such.

I would like to do same thing.

I have built complete application using MS Access 2003. Some of the highlights of this application are:

Customized login for each user without using User Level Workgroup Security features.

Each user is assigned 1 of 10 different roles. One of the roles is Admin role

Only Admin role has access to database window and all objects like tables, queries, forms, macros, modules etc.

Shift key is disabled so no one can access database window.

Admin can enabled shift key and get temporary access to database window. Shift key gets disabled on exit again.

Application has data capture front-end forms, one-click reports, quick query tool using front-end forms without query grid etc.

Only certain role can add new data, only certain role can edit data, data gets locked after certain time or status of data etc. Only ceratin role can upload/downlaod data etc.

Application is also password protected. Regular user can open application without knowing password as password is integrated in vba code. This password is essential as no one can export data from other database.

Currently all tables are stored in seperate database and linked in main application.

I would like to move all tables to SQL Server Express. I am assuming that by doing this I will be able to secure all tables better and it will also help me increasing size of application beyond 2 GB.

Please let me know step by step process to move Access Tables to SQL server express.

Thanks

|||

Hi Mahesh,

refer http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1056639&SiteID=1 which is answered.

Welcome on a board Adukio,

using SSMA you may migrate your Access DB to SQL 2005 Refer the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1056639&SiteID=1

I would suggest to refer this thread too http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033679&SiteID=1

Hemantgiri S. Goswami

|||

Hemant,

Thanks for the reply. In fact i had downloaded the SSMA and trying a few things. I don't see a option where in some kind of a column mapping can be done in this tool. What i mean is that :

Table1 (Access Tale) Table2 (SQL Express Table)

Column1 Column1

Column2 Column2

Column3

If we assume a scenerio like the one above where i need to migrate table from a access table to SQL table , if the number of columns do not match (this is very much possible as my target schema has been completely redesigned) , SSMA fails to migrate the data. So i need to know is there a provision for handling a scenerio like this in SSMA?

Thanks

Mahesh

|||

Hi Mahesha,

SSMA does not handle data transformation, which is what you're wanting to do. (The Access Upsizing Wizard doesn't do this either.) The only SQL tool I know of that can do this is SSIS, wich is not included in SQL Express. If you have another version of SQL Server 2005 available, say SQL Dev, you can use SSIS to create a data transformation.

If you don't have another edition of SQL available, you will need to do this manually. I would suggest migrating the data from Access to a new database on your SQL Server, and then use queries to transform the data into your new tables. If this is a process you have to do regularly you can probably work out a process of pulling data into temporary tables and then appending them to your new schema all using Stored Procedures.

Mike

|||

Thanks Mike, I think that answers my query. The data migration is a one time activity here , so i think i don't really need to use temp tables here. May be i'll go with queries and stored procedures.

Thanks!

Mahesh

Data migration from Access to SQL server 2005

Hi,

I am trying to migrate the data from the Access database to Microsoft SQL server on my machine using some scripts. I have enabled the OPENROWSET and OPENDATASOURCE support through the surface area configuration. The script that accesses the excel file works fine but when i run the script that trys to open the .mdb file it gives me following error.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.".

Msg 7303, Level 16, State 1, Line 35

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Any help would be greatly appreciated. Thanks.

-padu

Make sure that the startup account for sqlserver is not a service account (i.e. localsystem/service/etc.). Just use a domain acct.|||

Hi oj,

How do you check the startup account is not a service account? I am pretty new to SQL server.

Thanks.

-padu

|||You can run SQL Server Configuration Manager. It allows you to change service account there (if desired).

Data Management tool for SQL Server 2000

Hi,
We're shopping for a data management tool with the
following core features
- import data from MS Access/MS Excel/CVS to SQL Server
2000
- export data from SQL Server to MS Access/MS Excel/CVS
- Web-based application
- application security for user groups/roles
Do you know/use such product tool?
Thanks in advance for your input.
Tuan
Tuan
You should have a look at myLittleAdmin on
http://www.myLittleTools.net/mla_sql
A free lite version is download-able for your tests.
A live demo is also available on
http://www.myLittleTools.net/livedemo/mla_sql
Best regards
Elian Chrebor
Tuan Le typed:
> Hi,
> We're shopping for a data management tool with the
> following core features
> - import data from MS Access/MS Excel/CVS to SQL Server
> 2000
> - export data from SQL Server to MS Access/MS Excel/CVS
> - Web-based application
> - application security for user groups/roles
> Do you know/use such product tool?
> Thanks in advance for your input.
> Tuan

Data Issues Very frustrated

I have various sql, sybase & access databases that i need to report from onto
the 1 table. A number of joins link them all together. As you can only use 1
dataset, what would be the best way to report on my data into the one table.
I have spent weeks trying to work this out. ThanksYou could use a stored procedure or view to do all the joins and then call
the one view or stored procedure from your dataset.
John K.
===
"Tango" wrote:
> I have various sql, sybase & access databases that i need to report from onto
> the 1 table. A number of joins link them all together. As you can only use 1
> dataset, what would be the best way to report on my data into the one table.
> I have spent weeks trying to work this out. Thanks|||Thanks John,
Whats your preference here. Id prefer to only have to research 1 options as
time is limited at this stage. Let me know if you need any more info.
"John K" wrote:
> You could use a stored procedure or view to do all the joins and then call
> the one view or stored procedure from your dataset.
> John K.
> ===> "Tango" wrote:
> > I have various sql, sybase & access databases that i need to report from onto
> > the 1 table. A number of joins link them all together. As you can only use 1
> > dataset, what would be the best way to report on my data into the one table.
> > I have spent weeks trying to work this out. Thanks

data import tool

I'm trying to find a way to import data from a local access database into a
remote sql server database where the databases are similar and of the same
product (but different versions) and different enough with constraints, keys,
columns, that I can't use the SQL Server Import and Export Wizard (with my
current knowledge of it) to map table columns without things like null value
errors and adding insert values to disparate columns while doing a bulk copy.
So what I'm looking for is a tool that is a combination of an sql insert
statement and the bulk copy import wizard found in the SQL Server Management
studio.
The differences between product versions require more flexibility than I can
currently find without doing everything programatically with ado.net.
Any advice regarding this data migration greatly appreciated - thanks.So far the easiest way I can think of is to pre-modify the access tables
before I do an import. This is working - however now I'm looking for a way
for the sql server manager interface to remember all the table imports and
mappings - can everything be saved somehow to some kind of configuration file
in SQL Server Management Studio?
"segue" wrote:
> I'm trying to find a way to import data from a local access database into a
> remote sql server database where the databases are similar and of the same
> product (but different versions) and different enough with constraints, keys,
> columns, that I can't use the SQL Server Import and Export Wizard (with my
> current knowledge of it) to map table columns without things like null value
> errors and adding insert values to disparate columns while doing a bulk copy.
> So what I'm looking for is a tool that is a combination of an sql insert
> statement and the bulk copy import wizard found in the SQL Server Management
> studio.
> The differences between product versions require more flexibility than I can
> currently find without doing everything programatically with ado.net.
> Any advice regarding this data migration greatly appreciated - thanks.|||You can always do it with ADO.NET. But it may be simpler to just import the
access data as is into a staging SQL Server table using the Import Wizard,
and then write T-SQL script to modify the data to your specs.
Linchi
"segue" wrote:
> So far the easiest way I can think of is to pre-modify the access tables
> before I do an import. This is working - however now I'm looking for a way
> for the sql server manager interface to remember all the table imports and
> mappings - can everything be saved somehow to some kind of configuration file
> in SQL Server Management Studio?
> "segue" wrote:
> >
> > I'm trying to find a way to import data from a local access database into a
> > remote sql server database where the databases are similar and of the same
> > product (but different versions) and different enough with constraints, keys,
> > columns, that I can't use the SQL Server Import and Export Wizard (with my
> > current knowledge of it) to map table columns without things like null value
> > errors and adding insert values to disparate columns while doing a bulk copy.
> >
> > So what I'm looking for is a tool that is a combination of an sql insert
> > statement and the bulk copy import wizard found in the SQL Server Management
> > studio.
> > The differences between product versions require more flexibility than I can
> > currently find without doing everything programatically with ado.net.
> >
> > Any advice regarding this data migration greatly appreciated - thanks.

data import tool

I'm trying to find a way to import data from a local access database into a
remote sql server database where the databases are similar and of the same
product (but different versions) and different enough with constraints, keys,
columns, that I can't use the SQL Server Import and Export Wizard (with my
current knowledge of it) to map table columns without things like null value
errors and adding insert values to disparate columns while doing a bulk copy.
So what I'm looking for is a tool that is a combination of an sql insert
statement and the bulk copy import wizard found in the SQL Server Management
studio.
The differences between product versions require more flexibility than I can
currently find without doing everything programatically with ado.net.
Any advice regarding this data migration greatly appreciated - thanks.
So far the easiest way I can think of is to pre-modify the access tables
before I do an import. This is working - however now I'm looking for a way
for the sql server manager interface to remember all the table imports and
mappings - can everything be saved somehow to some kind of configuration file
in SQL Server Management Studio?
"segue" wrote:

> I'm trying to find a way to import data from a local access database into a
> remote sql server database where the databases are similar and of the same
> product (but different versions) and different enough with constraints, keys,
> columns, that I can't use the SQL Server Import and Export Wizard (with my
> current knowledge of it) to map table columns without things like null value
> errors and adding insert values to disparate columns while doing a bulk copy.
> So what I'm looking for is a tool that is a combination of an sql insert
> statement and the bulk copy import wizard found in the SQL Server Management
> studio.
> The differences between product versions require more flexibility than I can
> currently find without doing everything programatically with ado.net.
> Any advice regarding this data migration greatly appreciated - thanks.
|||You can always do it with ADO.NET. But it may be simpler to just import the
access data as is into a staging SQL Server table using the Import Wizard,
and then write T-SQL script to modify the data to your specs.
Linchi
"segue" wrote:
[vbcol=seagreen]
> So far the easiest way I can think of is to pre-modify the access tables
> before I do an import. This is working - however now I'm looking for a way
> for the sql server manager interface to remember all the table imports and
> mappings - can everything be saved somehow to some kind of configuration file
> in SQL Server Management Studio?
> "segue" wrote:

data import task

Hello - suggestions greatly appreciated.
I'm trying to import data from similar tables between a local
access database and a network accessible sql server.
After using the Microsoft SQL Server Management Studio import wizard
I get the following error message on execute.
- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)
Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
that the column metadata is valid.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
(61) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)Hi
I can't say exactly what the issue is, but searching google for your error
80040E21 seems to be related most of the time to the data or datatypes. You
may want to save the import as a SSIS package or create a SSIS package from
scratch and then you would have more control over it.
"segue" wrote:
> Hello - suggestions greatly appreciated.
> I'm trying to import data from similar tables between a local
> access database and a network accessible sql server.
> After using the Microsoft SQL Server Management Studio import wizard
> I get the following error message on execute.
> - Pre-execute (Error)
> Messages
> Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> 0x80040E21.
> An OLE DB record is available. Source: "Microsoft SQL Native Client"
> Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done.".
> (SQL Server Import and Export Wizard)
> Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> that the column metadata is valid.
> (SQL Server Import and Export Wizard)
> Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> (61) failed the pre-execute phase and returned error code 0xC0202025.
> (SQL Server Import and Export Wizard)
>|||Thanks for the response.
The problem after changing a few things has manifested into what seems to be
data type constraints.
Iâ've tried suggestions â' changing column values varchar(10) to varchar(MAX)
or whatever or running an sql delete or update at the old data to remove
nulls but Iâ'm still getting these null constraint errors even though a select
shows there are no null values in the old database.
If anything comes to mind regarding dealing with constraints - appreciated.
Thanks.
"John Bell" wrote:
> Hi
> I can't say exactly what the issue is, but searching google for your error
> 80040E21 seems to be related most of the time to the data or datatypes. You
> may want to save the import as a SSIS package or create a SSIS package from
> scratch and then you would have more control over it.
> "segue" wrote:
> > Hello - suggestions greatly appreciated.
> >
> > I'm trying to import data from similar tables between a local
> > access database and a network accessible sql server.
> >
> > After using the Microsoft SQL Server Management Studio import wizard
> > I get the following error message on execute.
> >
> > - Pre-execute (Error)
> > Messages
> > Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> > 0x80040E21.
> > An OLE DB record is available. Source: "Microsoft SQL Native Client"
> > Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> > errors. Check each OLE DB status value, if available. No work was done.".
> > (SQL Server Import and Export Wizard)
> >
> > Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> > that the column metadata is valid.
> > (SQL Server Import and Export Wizard)
> >
> > Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> > (61) failed the pre-execute phase and returned error code 0xC0202025.
> > (SQL Server Import and Export Wizard)
> >|||First verify that the SQL Agent Account has Full Permissions to the Access
database.
You can build a package as anybody, but it will be executed by the Agent.
Second, Build the package from the server it will be executed from.
At Pre-Execute the data has not become visible.
so the issue revolves around connection. Permissions or naming.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4BC6F16B-7941-4734-BD16-7FC4571D5417@.microsoft.com...
> Hi
> I can't say exactly what the issue is, but searching google for your error
> 80040E21 seems to be related most of the time to the data or datatypes.
> You
> may want to save the import as a SSIS package or create a SSIS package
> from
> scratch and then you would have more control over it.
> "segue" wrote:
>> Hello - suggestions greatly appreciated.
>> I'm trying to import data from similar tables between a local
>> access database and a network accessible sql server.
>> After using the Microsoft SQL Server Management Studio import wizard
>> I get the following error message on execute.
>> - Pre-execute (Error)
>> Messages
>> Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error
>> code:
>> 0x80040E21.
>> An OLE DB record is available. Source: "Microsoft SQL Native Client"
>> Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation
>> generated
>> errors. Check each OLE DB status value, if available. No work was done.".
>> (SQL Server Import and Export Wizard)
>> Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor.
>> Verify
>> that the column metadata is valid.
>> (SQL Server Import and Export Wizard)
>> Error 0xc004701a: Data Flow Task: component "Destination -
>> CustomerShipping"
>> (61) failed the pre-execute phase and returned error code 0xC0202025.
>> (SQL Server Import and Export Wizard)|||Hi
If this was a spreadsheet I would guess there are blank cells at the end of
the range, but as access is the data source I am not sure why this occurs. I
am not sure if you have already created the destination table, but you need
to make the column nullable if you want it to be added. Have you pre-created
the destination table or does the package create it?
Also check out the topic "Handling Errors in Data" in Books online which
will allow you to move any rows that are not inserted into the database
elsewhere so you can see what they are.
John
"segue" wrote:
> Thanks for the response.
> The problem after changing a few things has manifested into what seems to be
> data type constraints.
> Iâ've tried suggestions â' changing column values varchar(10) to varchar(MAX)
> or whatever or running an sql delete or update at the old data to remove
> nulls but Iâ'm still getting these null constraint errors even though a select
> shows there are no null values in the old database.
> If anything comes to mind regarding dealing with constraints - appreciated.
> Thanks.
>
> "John Bell" wrote:
> > Hi
> >
> > I can't say exactly what the issue is, but searching google for your error
> > 80040E21 seems to be related most of the time to the data or datatypes. You
> > may want to save the import as a SSIS package or create a SSIS package from
> > scratch and then you would have more control over it.
> >
> > "segue" wrote:
> >
> > > Hello - suggestions greatly appreciated.
> > >
> > > I'm trying to import data from similar tables between a local
> > > access database and a network accessible sql server.
> > >
> > > After using the Microsoft SQL Server Management Studio import wizard
> > > I get the following error message on execute.
> > >
> > > - Pre-execute (Error)
> > > Messages
> > > Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> > > 0x80040E21.
> > > An OLE DB record is available. Source: "Microsoft SQL Native Client"
> > > Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> > > errors. Check each OLE DB status value, if available. No work was done.".
> > > (SQL Server Import and Export Wizard)
> > >
> > > Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> > > that the column metadata is valid.
> > > (SQL Server Import and Export Wizard)
> > >
> > > Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> > > (61) failed the pre-execute phase and returned error code 0xC0202025.
> > > (SQL Server Import and Export Wizard)
> > >|||In the select convert the columns to isnull([Column],'ISNULL') for strings
Then after the import you should be able to better locate them.
But if the select is failing to return records becuase views with join
columns haveing nulls.
then you will have to fix the underlying views.
-Randy Pitkin
-Information Systems Consultant since 1983
"segue" <segue@.discussions.microsoft.com> wrote in message
news:34CC113F-B137-4EB9-A6C7-507D733B0088@.microsoft.com...
> Thanks for the response.
> The problem after changing a few things has manifested into what seems to
> be
> data type constraints.
> I've tried suggestions - changing column values varchar(10) to
> varchar(MAX)
> or whatever or running an sql delete or update at the old data to remove
> nulls but I'm still getting these null constraint errors even though a
> select
> shows there are no null values in the old database.
> If anything comes to mind regarding dealing with constraints -
> appreciated.
> Thanks.
>
> "John Bell" wrote:
>> Hi
>> I can't say exactly what the issue is, but searching google for your
>> error
>> 80040E21 seems to be related most of the time to the data or datatypes.
>> You
>> may want to save the import as a SSIS package or create a SSIS package
>> from
>> scratch and then you would have more control over it.
>> "segue" wrote:
>> > Hello - suggestions greatly appreciated.
>> >
>> > I'm trying to import data from similar tables between a local
>> > access database and a network accessible sql server.
>> >
>> > After using the Microsoft SQL Server Management Studio import wizard
>> > I get the following error message on execute.
>> >
>> > - Pre-execute (Error)
>> > Messages
>> > Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error
>> > code:
>> > 0x80040E21.
>> > An OLE DB record is available. Source: "Microsoft SQL Native Client"
>> > Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation
>> > generated
>> > errors. Check each OLE DB status value, if available. No work was
>> > done.".
>> > (SQL Server Import and Export Wizard)
>> >
>> > Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor.
>> > Verify
>> > that the column metadata is valid.
>> > (SQL Server Import and Export Wizard)
>> >
>> > Error 0xc004701a: Data Flow Task: component "Destination -
>> > CustomerShipping"
>> > (61) failed the pre-execute phase and returned error code 0xC0202025.
>> > (SQL Server Import and Export Wizard)
>> >|||Thanks for the great ideas. I'm building (through the wizard?) the
"package"?
I'm not sure what "package" means exactly (in what context).
As far as permissions go the access is local to my computer and the sql is
remote. I'm not using an account to the access db which doesn't seem
necessary and I am using an account naturally for the SQL server and
connection. I'm able to perform ado.net w/asp.net and sql queries with the
same account
Any tasks to disect what's below greatly appreciated.
At Pre-Execute the data has not become visible so the issue revolves around
connection, permissions or naming.
Regards
"Randy Pitkin" wrote:
> First verify that the SQL Agent Account has Full Permissions to the Access
> database.
> You can build a package as anybody, but it will be executed by the Agent.
> Second, Build the package from the server it will be executed from.
> At Pre-Execute the data has not become visible.
> so the issue revolves around connection. Permissions or naming.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4BC6F16B-7941-4734-BD16-7FC4571D5417@.microsoft.com...
> > Hi
> >
> > I can't say exactly what the issue is, but searching google for your error
> > 80040E21 seems to be related most of the time to the data or datatypes.
> > You
> > may want to save the import as a SSIS package or create a SSIS package
> > from
> > scratch and then you would have more control over it.
> >
> > "segue" wrote:
> >
> >> Hello - suggestions greatly appreciated.
> >>
> >> I'm trying to import data from similar tables between a local
> >> access database and a network accessible sql server.
> >>
> >> After using the Microsoft SQL Server Management Studio import wizard
> >> I get the following error message on execute.
> >>
> >> - Pre-execute (Error)
> >> Messages
> >> Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error
> >> code:
> >> 0x80040E21.
> >> An OLE DB record is available. Source: "Microsoft SQL Native Client"
> >> Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation
> >> generated
> >> errors. Check each OLE DB status value, if available. No work was done.".
> >> (SQL Server Import and Export Wizard)
> >>
> >> Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor.
> >> Verify
> >> that the column metadata is valid.
> >> (SQL Server Import and Export Wizard)
> >>
> >> Error 0xc004701a: Data Flow Task: component "Destination -
> >> CustomerShipping"
> >> (61) failed the pre-execute phase and returned error code 0xC0202025.
> >> (SQL Server Import and Export Wizard)
> >>
>
>|||The table is already created and the table's database is a 3rd party .NET
based app., so I'm trying to import into a table that already exists.
The columns for that table have keys that are not nullable of course. The
error messages haven't mentioned those columns. The columns that are
mentioned in the null value error messages I've tried adding values for to
that access table with no luck.
I can't change the columns I'm importing to other than value types and sizes
when I bring up their design view in VS2005.
Suggestions greatly appreciated - thanks.
"John Bell" wrote:
> Hi
> If this was a spreadsheet I would guess there are blank cells at the end of
> the range, but as access is the data source I am not sure why this occurs. I
> am not sure if you have already created the destination table, but you need
> to make the column nullable if you want it to be added. Have you pre-created
> the destination table or does the package create it?
> Also check out the topic "Handling Errors in Data" in Books online which
> will allow you to move any rows that are not inserted into the database
> elsewhere so you can see what they are.
> John
> "segue" wrote:
> >
> > Thanks for the response.
> >
> > The problem after changing a few things has manifested into what seems to be
> > data type constraints.
> >
> > Iâ've tried suggestions â' changing column values varchar(10) to varchar(MAX)
> > or whatever or running an sql delete or update at the old data to remove
> > nulls but Iâ'm still getting these null constraint errors even though a select
> > shows there are no null values in the old database.
> >
> > If anything comes to mind regarding dealing with constraints - appreciated.
> >
> > Thanks.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I can't say exactly what the issue is, but searching google for your error
> > > 80040E21 seems to be related most of the time to the data or datatypes. You
> > > may want to save the import as a SSIS package or create a SSIS package from
> > > scratch and then you would have more control over it.
> > >
> > > "segue" wrote:
> > >
> > > > Hello - suggestions greatly appreciated.
> > > >
> > > > I'm trying to import data from similar tables between a local
> > > > access database and a network accessible sql server.
> > > >
> > > > After using the Microsoft SQL Server Management Studio import wizard
> > > > I get the following error message on execute.
> > > >
> > > > - Pre-execute (Error)
> > > > Messages
> > > > Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> > > > 0x80040E21.
> > > > An OLE DB record is available. Source: "Microsoft SQL Native Client"
> > > > Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> > > > errors. Check each OLE DB status value, if available. No work was done.".
> > > > (SQL Server Import and Export Wizard)
> > > >
> > > > Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> > > > that the column metadata is valid.
> > > > (SQL Server Import and Export Wizard)
> > > >
> > > > Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> > > > (61) failed the pre-execute phase and returned error code 0xC0202025.
> > > > (SQL Server Import and Export Wizard)
> > > >|||Hi
Randy has given an option to overcome the potential nullability of the
columns, another would be to change the table you load into and allow null
columns etc and then you can view the data and sort it out or make the
process a two stage process permanently and rectify the data as you import it.
John
"segue" wrote:
> The table is already created and the table's database is a 3rd party .NET
> based app., so I'm trying to import into a table that already exists.
> The columns for that table have keys that are not nullable of course. The
> error messages haven't mentioned those columns. The columns that are
> mentioned in the null value error messages I've tried adding values for to
> that access table with no luck.
> I can't change the columns I'm importing to other than value types and sizes
> when I bring up their design view in VS2005.
> Suggestions greatly appreciated - thanks.
>
> "John Bell" wrote:
> > Hi
> >
> > If this was a spreadsheet I would guess there are blank cells at the end of
> > the range, but as access is the data source I am not sure why this occurs. I
> > am not sure if you have already created the destination table, but you need
> > to make the column nullable if you want it to be added. Have you pre-created
> > the destination table or does the package create it?
> >
> > Also check out the topic "Handling Errors in Data" in Books online which
> > will allow you to move any rows that are not inserted into the database
> > elsewhere so you can see what they are.
> >
> > John
> >
> > "segue" wrote:
> >
> > >
> > > Thanks for the response.
> > >
> > > The problem after changing a few things has manifested into what seems to be
> > > data type constraints.
> > >
> > > Iâ've tried suggestions â' changing column values varchar(10) to varchar(MAX)
> > > or whatever or running an sql delete or update at the old data to remove
> > > nulls but Iâ'm still getting these null constraint errors even though a select
> > > shows there are no null values in the old database.
> > >
> > > If anything comes to mind regarding dealing with constraints - appreciated.
> > >
> > > Thanks.
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I can't say exactly what the issue is, but searching google for your error
> > > > 80040E21 seems to be related most of the time to the data or datatypes. You
> > > > may want to save the import as a SSIS package or create a SSIS package from
> > > > scratch and then you would have more control over it.
> > > >
> > > > "segue" wrote:
> > > >
> > > > > Hello - suggestions greatly appreciated.
> > > > >
> > > > > I'm trying to import data from similar tables between a local
> > > > > access database and a network accessible sql server.
> > > > >
> > > > > After using the Microsoft SQL Server Management Studio import wizard
> > > > > I get the following error message on execute.
> > > > >
> > > > > - Pre-execute (Error)
> > > > > Messages
> > > > > Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code:
> > > > > 0x80040E21.
> > > > > An OLE DB record is available. Source: "Microsoft SQL Native Client"
> > > > > Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
> > > > > errors. Check each OLE DB status value, if available. No work was done.".
> > > > > (SQL Server Import and Export Wizard)
> > > > >
> > > > > Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify
> > > > > that the column metadata is valid.
> > > > > (SQL Server Import and Export Wizard)
> > > > >
> > > > > Error 0xc004701a: Data Flow Task: component "Destination - CustomerShipping"
> > > > > (61) failed the pre-execute phase and returned error code 0xC0202025.
> > > > > (SQL Server Import and Export Wizard)
> > > > >

data import sql server management studio

I went through the data import wizard and it successfully
went through all the procedures to import data from an
access table into an sql server table, however, there is no
data in the table?
What could I be missing?
ThanksFor troubleshooting, I'd try to import from a different source (perhaps a
different Access database or even a flat file) to make sure there is nothing
wrong with the import wizard, the drivers, or the steps.
Linchi
"segue" wrote:
> I went through the data import wizard and it successfully
> went through all the procedures to import data from an
> access table into an sql server table, however, there is no
> data in the table?
> What could I be missing?
> Thanks

Data Import from Access

Hi,
Recently we upgraded from an Access database to a SQL
Server 2000 database. Everything imported fine. However in
our applications, we used to use an auto number field
which was automatically incremented by Access. After
import into SQL Server, the field with Autonumber got
imported as "int". Because of this none of our
applications seem to be able to work without changing the
Autonumber logic.
Is there an equivalent to the Autonumber field of Access
in SQL Server?Hi Peter,
Thanks for Ryan's help. I agree with him. Also, I'm including some
supplemental information below:
IDENTITY (Property)
Creates an identity column in a table. This property is used with the
CREATE TABLE and ALTER TABLE Transact-SQL statements.
Syntax
IDENTITY [ ( seed , increment ) ]
Arguments
seed
Is the value that is used for the very first row loaded into the table.
increment
Is the incremental value that is added to the identity value of the
previous row that was loaded.
You must specify both the seed and increment or neither. If neither is
specified, the default is (1,1).
For additional information regarding this issue please refer to the
following article below:
Topic: "IDENTITY (Property)"
Please feel free to let me know if this solves your problem or if you would
like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.