Foreach Loop, Data Flow task buffer failed

I have a package that runs fine by itself.But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops.Here are a couple of the error lines:

A buffer failed while allocating 49085616 bytes.

The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

I already played around with the Data Flow task’s DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task.I haven’t install SP1.Maybe SP1 fixes this issue?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

Foreach Loop, Data Flow task buffer failed

I have a package that runs fine by itself.But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops.Here are a couple of the error lines:

A buffer failed while allocating 49085616 bytes.

The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

I already played around with the Data Flow task’s DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task.I haven’t install SP1.Maybe SP1 fixes this issue?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

ForceEncryption Parameter

Hi All,

I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.

I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.

Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.

But, I do get the following error when I set Encrypt = Yes

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

and, If I set Encrypt=No, I do not get any error again.

My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?

Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.

Could you please help me understand the results of various options like this?

Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

The Encrypt = No setting again does not give any error.

Thanks and Regards,

-Ajay Kumar Suri

Hi Ajay,

The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx

Il-Sung.

|||

Hi,

Thanks very much for your reply.

Could you please verify if my understanding now is correct for SQL Server 2005.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

I hope I have understood it correctly.

Please advise.

Best Regards,

-Ajay Kumar Suri

|||

Hi Ajay,

Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

> Correct.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

> Correct, although the login packet will always be encrypted.

Hope this clears things up.

Il-Sung.

|||

Hi Sung,

Thanks very much for your comments.

It does clear a lot of things for me.

Best Regards,

-Ajay Kumar Suri

ForceEncryption Parameter

Hi All,

I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.

I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.

Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.

But, I do get the following error when I set Encrypt = Yes

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

and, If I set Encrypt=No, I do not get any error again.

My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?

Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.

Could you please help me understand the results of various options like this?

Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

The Encrypt = No setting again does not give any error.

Thanks and Regards,

-Ajay Kumar Suri

Hi Ajay,

The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx

Il-Sung.

|||

Hi,

Thanks very much for your reply.

Could you please verify if my understanding now is correct for SQL Server 2005.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

I hope I have understood it correctly.

Please advise.

Best Regards,

-Ajay Kumar Suri

|||

Hi Ajay,

Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

> Correct.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

> Correct, although the login packet will always be encrypted.

Hope this clears things up.

Il-Sung.

|||

Hi Sung,

Thanks very much for your comments.

It does clear a lot of things for me.

Best Regards,

-Ajay Kumar Suri

ForceEncryption Parameter

Hi All,

I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.

I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.

Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.

But, I do get the following error when I set Encrypt = Yes

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

and, If I set Encrypt=No, I do not get any error again.

My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?

Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.

Could you please help me understand the results of various options like this?

Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

The Encrypt = No setting again does not give any error.

Thanks and Regards,

-Ajay Kumar Suri

Hi Ajay,

The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx

Il-Sung.

|||

Hi,

Thanks very much for your reply.

Could you please verify if my understanding now is correct for SQL Server 2005.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

I hope I have understood it correctly.

Please advise.

Best Regards,

-Ajay Kumar Suri

|||

Hi Ajay,

Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

> Correct.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

> Correct, although the login packet will always be encrypted.

Hope this clears things up.

Il-Sung.

|||

Hi Sung,

Thanks very much for your comments.

It does clear a lot of things for me.

Best Regards,

-Ajay Kumar Suri

ForceEncryption Parameter

Hi All,

I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.

I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.

Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.

But, I do get the following error when I set Encrypt = Yes

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

and, If I set Encrypt=No, I do not get any error again.

My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?

Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.

Could you please help me understand the results of various options like this?

Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.

"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"

The Encrypt = No setting again does not give any error.

Thanks and Regards,

-Ajay Kumar Suri

Hi Ajay,

The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx

Il-Sung.

|||

Hi,

Thanks very much for your reply.

Could you please verify if my understanding now is correct for SQL Server 2005.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

I hope I have understood it correctly.

Please advise.

Best Regards,

-Ajay Kumar Suri

|||

Hi Ajay,

Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.

Server : Encryption On (Self Signed Certificate)

Client : Encryption off or not specified.

The channel is encrypted.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate not installed on client.

The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.

> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption off or not specified. Root Certificate is installed on client.

The channel is encrypted using the server SSL certificate.

> Correct.

Server : Encryption On (Certificate Issued by a Trusted Authority)

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified or client has the root certificate.

> Correct.

Server : Encryption Off.

Client : Encryption On.

Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.

> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.

Server : Encryption Off.

Client : Encryption Off.

No encryption.

> Correct, although the login packet will always be encrypted.

Hope this clears things up.

Il-Sung.

|||

Hi Sung,

Thanks very much for your comments.

It does clear a lot of things for me.

Best Regards,

-Ajay Kumar Suri

for xml raw problem

Hi
I'm running a simple query with for xml raw on a table
one of columns in select clause here is of type text and stores xml
file itself.
The problem i'm facing is when I get this text back from this column I
get back unformatted xml
e.g
instead of getting <GPF> I get <GPF> etc
How do I get this column as formatted xml text ?
Thanks for help
Vishy>
> I'm running a simple query with for xml raw on a table
> one of columns in select clause here is of type text and stores xml
> file itself.
> The problem i'm facing is when I get this text back from this column I
> get back unformatted xml
> e.g
> instead of getting <GPF> I get <GPF> etc
> How do I get this column as formatted xml text ?
>
Hello
signs '<' and '>' are very important in XML because they show begin and end
of xml element, so when an element or attribute value has such signs it must
be changed into < and >. But when you open XML which contains such
substitution - using DOM or OPENXML in SQL you will get values with signs
'<', '>'. So I think that it sould not be a problem - siply try to process
XML which was generated and you will see that it has valid values of
elements/attributes.
I hope it helps,
Alwik|||Thanks Alwik for reply
I tried to create object on XmlDocument using LoadXml() method
it loads up the xml successfully but if I want to run any Xpath; it
fails
as <GPF> is not treated as <GPF> so I can't find GPF element
is there any work around?
Vishy|||Are you using SQL Server 2000 or 2005?
In the case of SQL Server 2000, you will have to use the explicit mode and
its !xml directive, if you want to have string data be inlined into the XML
structure.
In the case of SQL Server 2005, you can stick with the FOR XML RAW query,
but you need to cast the string data to XML in the select clause.
Best regards
Michael
"Vishy" <vishal.halbe@.gmail.com> wrote in message
news:1128445808.595093.141180@.g49g2000cwa.googlegroups.com...
> Thanks Alwik for reply
> I tried to create object on XmlDocument using LoadXml() method
> it loads up the xml successfully but if I want to run any Xpath; it
> fails
> as <GPF> is not treated as <GPF> so I can't find GPF element
> is there any work around?
> Vishy
>|||Thanks Michael for reply
I'm using SQL 2000
Vishy

for xml raw problem

Hi
I'm running a simple query with for xml raw on a table
one of columns in select clause here is of type text and stores xml
file itself.
The problem i'm facing is when I get this text back from this column I
get back unformatted xml
e.g
instead of getting <GPF> I get <GPF> etc
How do I get this column as formatted xml text ?
Thanks for help
Vishy
>
> I'm running a simple query with for xml raw on a table
> one of columns in select clause here is of type text and stores xml
> file itself.
> The problem i'm facing is when I get this text back from this column I
> get back unformatted xml
> e.g
> instead of getting <GPF> I get <GPF> etc
> How do I get this column as formatted xml text ?
>
Hello
signs '<' and '>' are very important in XML because they show begin and end
of xml element, so when an element or attribute value has such signs it must
be changed into < and >. But when you open XML which contains such
substitution - using DOM or OPENXML in SQL you will get values with signs
'<', '>'. So I think that it sould not be a problem - siply try to process
XML which was generated and you will see that it has valid values of
elements/attributes.
I hope it helps,
Alwik
|||Thanks Alwik for reply
I tried to create object on XmlDocument using LoadXml() method
it loads up the xml successfully but if I want to run any Xpath; it
fails
as <GPF> is not treated as <GPF> so I can't find GPF element
is there any work around?
Vishy
|||Are you using SQL Server 2000 or 2005?
In the case of SQL Server 2000, you will have to use the explicit mode and
its !xml directive, if you want to have string data be inlined into the XML
structure.
In the case of SQL Server 2005, you can stick with the FOR XML RAW query,
but you need to cast the string data to XML in the select clause.
Best regards
Michael
"Vishy" <vishal.halbe@.gmail.com> wrote in message
news:1128445808.595093.141180@.g49g2000cwa.googlegr oups.com...
> Thanks Alwik for reply
> I tried to create object on XmlDocument using LoadXml() method
> it loads up the xml successfully but if I want to run any Xpath; it
> fails
> as <GPF> is not treated as <GPF> so I can't find GPF element
> is there any work around?
> Vishy
>
|||Thanks Michael for reply
I'm using SQL 2000
Vishy