Data Source / ODBC error

Hi all,

My replication of those SQL 2000 servers gave errors: Data source (11): General Network Error. Check your network documentation... and ODBC (08S01): Communication link failure. The replication was across the WAN. I don't know where to start to troubleshoot this problem. Please help!

Thanks in advance.

John

Communication link failure means some network or communication problem between the Publisher/Distributor and Subscriber. Check the connection between them. Try connecting to the other servers from each of the servers and ensure that you are able to connect and then retry the Sync.

Also note for any firewalls that need to be taken care of or if the ports are different or enabled.

See if the domains are different and if so ensure trust or use SQL authentication.

Also is this the first time the sync ran or it previously used to succeed?

|||

Hi,

This is not the first time the replication failed. It worked perfect before.

DNS is fine since I did check.

Since this is a site to site interconnected (site2site VPN across internet). Therefore, the domains are different.

It has failed several times a day.

I don't know if we have any testing tools for replication available so I can narrow down the problems and troubleshoot more efficiently.

thanks much for your help!!!

|||This mostly has to do with network connectivity then. For merge agent to run successfully, the connection needs to be established. Try rerunning it and see if it succeeds. Also see if there are any other external factors affecting the connection. Peak load, network flakiness, disconnects or downtime during certain periods of time etc.|||On the machine where the failure occurred, try creating a UDL file (create a new text document, then rename it to "test.udl"). Open the UDL file by double-clicking it, then try to establish a connection to your database using the same sorts of parameters (ie provider [SQL Server native / ODBC etc.], server name, login parameters). You can use this to narrow down the cause of the problem (eg. whether it's the DB provider, or a lack of connectivity which can be checked using other means such as a traceroute etc.).|||

Thanks much. I will implement this method. In the meantime, is the packet from replication UDP or TCP when it is sent to the subcribed machine from distributed machine. I have thought my network is being flooding at the switch before data reached the nodes of the cluster. Therefore, it is redirected elsewhere but the destination. What I did was trying to send data from host inside network through that switch to the gateway...and I was only successful 35%. This means packets drops no matter incoming or outgoing through that switch.

Having some methods to test the replication directly will help a lot.

Thanks for your helps, guys.

|||Doesn't UDP run on top of TCP, hence they're both going to be TCP? Also, it'd depend on whether you've got the IIS machine on the Publisher machine, the Subscriber machine, or another machine?
Also try playing around with the configuration of the Client/Server Network Protocol - if you're having problems using TCP/IP, then try using Named Pipes instead. Make sure to configure this on both the publisher and subscriber machine.|||

Problem Fixed, Guys. It was just network problem. The Fixwall is misconfigured so that it operated half duplex instead of full. There Ain't nothin to do with the replication!!!

However, I have to deal with new problem......queue reader failed with the error message: Queue reader aborting. The steps failed. What does it mean? Where should I start to troubleshoot this error?

Thanks a lot!

|||Is there anything more helpful in the Event log?|||What I 've got is Failed while applying queued message to publisher!|||

I'm not an expert in SQL Server replication, so I can't help you more with your specific error (I've just recently spent a few days reading lots about SQL Mobile replication, hence the reason I came across your post).

If I was in your position, however, I'd start by getting everything working on on one box (even if it's just a staging box), and once it all works, you can move bit by bit elsewhere (eg. Publisher DB / IIS virtual dir / IIS server / Subscriber DB).

As you try to move things bit by bit you'll realise where the problem is. Sometimes the 'longest' way is the shortest because by being systematic, you don't end up pulling your hair out looking at something that 'should work', but doesn't!

|||

Oh, by the way, if you can't get it to work, I suggest starting a new thread, because some people will see this thread as answered, and not read it.

Cheers.

sql

Data Source / ODBC error

Hi all,

My replication of those SQL 2000 servers gave errors: Data source (11): General Network Error. Check your network documentation... and ODBC (08S01): Communication link failure. The replication was across the WAN. I don't know where to start to troubleshoot this problem. Please help!

Thanks in advance.

John

Communication link failure means some network or communication problem between the Publisher/Distributor and Subscriber. Check the connection between them. Try connecting to the other servers from each of the servers and ensure that you are able to connect and then retry the Sync.

Also note for any firewalls that need to be taken care of or if the ports are different or enabled.

See if the domains are different and if so ensure trust or use SQL authentication.

Also is this the first time the sync ran or it previously used to succeed?

|||

Hi,

This is not the first time the replication failed. It worked perfect before.

DNS is fine since I did check.

Since this is a site to site interconnected (site2site VPN across internet). Therefore, the domains are different.

It has failed several times a day.

I don't know if we have any testing tools for replication available so I can narrow down the problems and troubleshoot more efficiently.

thanks much for your help!!!

|||This mostly has to do with network connectivity then. For merge agent to run successfully, the connection needs to be established. Try rerunning it and see if it succeeds. Also see if there are any other external factors affecting the connection. Peak load, network flakiness, disconnects or downtime during certain periods of time etc.|||On the machine where the failure occurred, try creating a UDL file

(create a new text document, then rename it to "test.udl"). Open the

UDL file by double-clicking it, then try to establish a connection to

your database using the same sorts of parameters (ie provider [SQL

Server native / ODBC etc.], server name, login parameters). You can use

this to narrow down the cause of the problem (eg. whether it's the DB

provider, or a lack of connectivity which can be checked using other

means such as a traceroute etc.).|||

Thanks much. I will implement this method. In the meantime, is the packet from replication UDP or TCP when it is sent to the subcribed machine from distributed machine. I have thought my network is being flooding at the switch before data reached the nodes of the cluster. Therefore, it is redirected elsewhere but the destination. What I did was trying to send data from host inside network through that switch to the gateway...and I was only successful 35%. This means packets drops no matter incoming or outgoing through that switch.

Having some methods to test the replication directly will help a lot.

Thanks for your helps, guys.

|||Doesn't UDP run on top of TCP, hence they're both going to be TCP?

Also, it'd depend on whether you've got the IIS machine on the

Publisher machine, the Subscriber machine, or another machine?

Also try playing around with the configuration of the Client/Server

Network Protocol - if you're having problems using TCP/IP, then try

using Named Pipes instead. Make sure to configure this on both the

publisher and subscriber machine.|||

Problem Fixed, Guys. It was just network problem. The Fixwall is misconfigured so that it operated half duplex instead of full. There Ain't nothin to do with the replication!!!

However, I have to deal with new problem......queue reader failed with the error message: Queue reader aborting. The steps failed. What does it mean? Where should I start to troubleshoot this error?

Thanks a lot!

|||Is there anything more helpful in the Event log?|||What I 've got is Failed while applying queued message to publisher!|||

I'm not an expert in SQL Server replication, so I can't help you more with your specific error (I've just recently spent a few days reading lots about SQL Mobile replication, hence the reason I came across your post).

If I was in your position, however, I'd start by getting everything working on on one box (even if it's just a staging box), and once it all works, you can move bit by bit elsewhere (eg. Publisher DB / IIS virtual dir / IIS server / Subscriber DB).

As you try to move things bit by bit you'll realise where the problem is. Sometimes the 'longest' way is the shortest because by being systematic, you don't end up pulling your hair out looking at something that 'should work', but doesn't!

|||

Oh, by the way, if you can't get it to work, I suggest starting a new thread, because some people will see this thread as answered, and not read it.

Cheers.

Data Source / ODBC error

Hi all,

My replication of those SQL 2000 servers gave errors: Data source (11): General Network Error. Check your network documentation... and ODBC (08S01): Communication link failure. The replication was across the WAN. I don't know where to start to troubleshoot this problem. Please help!

Thanks in advance.

John

Communication link failure means some network or communication problem between the Publisher/Distributor and Subscriber. Check the connection between them. Try connecting to the other servers from each of the servers and ensure that you are able to connect and then retry the Sync.

Also note for any firewalls that need to be taken care of or if the ports are different or enabled.

See if the domains are different and if so ensure trust or use SQL authentication.

Also is this the first time the sync ran or it previously used to succeed?

|||

Hi,

This is not the first time the replication failed. It worked perfect before.

DNS is fine since I did check.

Since this is a site to site interconnected (site2site VPN across internet). Therefore, the domains are different.

It has failed several times a day.

I don't know if we have any testing tools for replication available so I can narrow down the problems and troubleshoot more efficiently.

thanks much for your help!!!

|||This mostly has to do with network connectivity then. For merge agent to run successfully, the connection needs to be established. Try rerunning it and see if it succeeds. Also see if there are any other external factors affecting the connection. Peak load, network flakiness, disconnects or downtime during certain periods of time etc.|||On the machine where the failure occurred, try creating a UDL file

(create a new text document, then rename it to "test.udl"). Open the

UDL file by double-clicking it, then try to establish a connection to

your database using the same sorts of parameters (ie provider [SQL

Server native / ODBC etc.], server name, login parameters). You can use

this to narrow down the cause of the problem (eg. whether it's the DB

provider, or a lack of connectivity which can be checked using other

means such as a traceroute etc.).|||

Thanks much. I will implement this method. In the meantime, is the packet from replication UDP or TCP when it is sent to the subcribed machine from distributed machine. I have thought my network is being flooding at the switch before data reached the nodes of the cluster. Therefore, it is redirected elsewhere but the destination. What I did was trying to send data from host inside network through that switch to the gateway...and I was only successful 35%. This means packets drops no matter incoming or outgoing through that switch.

Having some methods to test the replication directly will help a lot.

Thanks for your helps, guys.

|||Doesn't UDP run on top of TCP, hence they're both going to be TCP?

Also, it'd depend on whether you've got the IIS machine on the

Publisher machine, the Subscriber machine, or another machine?

Also try playing around with the configuration of the Client/Server

Network Protocol - if you're having problems using TCP/IP, then try

using Named Pipes instead. Make sure to configure this on both the

publisher and subscriber machine.|||

Problem Fixed, Guys. It was just network problem. The Fixwall is misconfigured so that it operated half duplex instead of full. There Ain't nothin to do with the replication!!!

However, I have to deal with new problem......queue reader failed with the error message: Queue reader aborting. The steps failed. What does it mean? Where should I start to troubleshoot this error?

Thanks a lot!

|||Is there anything more helpful in the Event log?|||What I 've got is Failed while applying queued message to publisher!|||

I'm not an expert in SQL Server replication, so I can't help you more with your specific error (I've just recently spent a few days reading lots about SQL Mobile replication, hence the reason I came across your post).

If I was in your position, however, I'd start by getting everything working on on one box (even if it's just a staging box), and once it all works, you can move bit by bit elsewhere (eg. Publisher DB / IIS virtual dir / IIS server / Subscriber DB).

As you try to move things bit by bit you'll realise where the problem is. Sometimes the 'longest' way is the shortest because by being systematic, you don't end up pulling your hair out looking at something that 'should work', but doesn't!

|||

Oh, by the way, if you can't get it to work, I suggest starting a new thread, because some people will see this thread as answered, and not read it.

Cheers.

Data loss in Transactional Replication

We have setup transactional replication across several databases using SQL Server 2000 spread across multiple sites in a fully connected network. There is one main table from which data is replicated from the publisher to the destination. Horizontal filtering is being used on this table to enable sending/routing of the records to the correct DB(site). It has been observed that the documents/records are getting lost between some sites. Say 10 documents are being sent fron the publishing database but only 5 are being received at the destination database although the sent history for all the 10 documents is available at the publishing database.

Can anyone guide on how to analyse and resolve this problem? Can unreliable network be the issue, If the network is not reliable and the connection is lost during replication how does replication ensure that no data is lost?

Hi,

If you didn't see replication reports error, unreliable network should not be the cause of data loss. Replication delivers the transaction with order. If it can't deliver it for whatever reason, it will keep retry or report error unless you explicitly specify skiperror.

To troubleshoot, you first need to see if your horizontal filter actually filters the data at publisher. You can also use sp_browsereplcmds to see if the transactions is in distribution DB. From here, you can determine if it is a logreader issue or distribution agnet issue.

Please let me know if you need more assistance.

Peng

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 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:
[vbcol=seagreen]
> 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:
|||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...[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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: