Fetching data from IBM DB2 to SQL Server 2005

Hi,

I am trying to fetch data from IBM DB2 to SQL Server 2005.

The problem I am facing is when I create the OLE DB Connection (I am using the "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider") and see the "Preview", I get "System.Byte[]" in a couple of columns for all the rows, instead of the actual data.

The datatype of the original field is "Byte Stream".

I have tried all options, but, failed. I believe there is something in the "Force Translate" property of the OLE DB Connection. Right now it is set to "65535". I am not sure if that needs to be changed.

I was earlier using a DTS package, where I used ODBC for connecting to the same database. In ODBC, there is a "Translation" tab where there is a check box labelled: "Convert binary text (CCSID 65535) to text".

When I check this box, I am able to see the data correctly.

But, now I have moved to SSIS and I am facing the same problem as I am not using the ODBC connection.

Please help.

Thanks and Regards,

B@.ns

I don't knot if this would resolve your problem; but you can still use the ODBC connection in SSIS by using a Data Reader source component.|||

Hi,

Thank you for the reply.

I will try and use the same ODBC connection and get back.

Thanks and Regards,

B@.ns

|||

Thank you Rafael!

This has indeed solved my problem.

Thanks and Regards,

B@.ns

|||

If you still having problems.

1. You can try to create linkserver

2. Create a view of the target table

3. Access it like a SQL server table

FeedFile Excel Connection Manager Concurrency Issue?

Hi,

RE. SSIS (v9.00.1399.00) FeedFile Excel Connection Manager Concurrency Issue

1. I have 15 completely separate SSIS Packages all of which use the FeedFile Excel Connection Manager to read data from completely separate Excel worksheets.

2. When I invoke, say, 5 of these Packages at the same time then one or more (at random) of them fail with the following error message:-

The AcquireConnection method call to the connection manager "FeedFile Excel Connection Manager" failed with error code 0xC0202009.

3. When I invoke the failed Packages again then they succeed.

So, do you know if there is:-

(a) a concurrency issue with the "FeedFile Excel Connection Manager" ?

(b) a read-only option (in the SSIS Package code) which needs to be specified to overcome file contention issues?

Additional Information about the FeedFile Excel Connection Manager:-

Connection String=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DoesNotNeedToExist.xls;Extended Properties="EXCEL 8.0;HDR=NO";

DelayValidation=True

ExcelFilePath= C:\DoesNotNeedToExist

RetainSameConnection=False

Note1. The actual Excel filename which is read is generated dynamically at run-time by a Script Task. This is why DelayValidation has been set to: True (Note. The file: "C:\DoesNotNeedToExist" does not actually physically exist.)

Note2. We have another SSIS Package which uses the FeedFile FlatFile Connection Manager. When we run this same Package concurrently (i.e. invoke it 5 times, at the same time, with different input files) then all 5 invocations succeed, which is what we would expect.

Thanks.

What is the "FeedFile Excel Connection Manager" as opposed to the regular Excel connection?

Is there no more detail? If running in BIDS, check the output winbdow, or if running unattanded, turn on logging.

I would expect there to be some additional error messages that go with 0xC0202009 error, which give the reason for this generic failure report.

|||

Sorry Darren, ignore the prefix FeedFile, it is the regular Excel connection.

I have not had a chance to look at this problem again but I will return to it later this week. I will let you know how I get on with trying to reproduce it in BIDevStudio etc.

From what I can gather though, there should be no limit to the number of separate SSIS Packages that can be run concurrently on a Server. Is this correct?

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, RobertI'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:
> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL2000
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causing
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, Robert
I'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:

> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL2000
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causing
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, RobertI'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:

> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL200
0
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causin
g
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fatal Error

Need help,

I am trying to update my server connection ( Microsoft.AnalysisServices.Server.update ( ) )and got the following error message:

Microsoft.AnalysisServices.OperationException: Fatal internal error.

at Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadRespon
se(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean throwIfEr
ror)
at Microsoft.AnalysisServices.AnalysisServicesClient.Alter(IMajorObject obj,
ObjectExpansion expansion, ImpactDetailCollection impact, Boolean allowCreate)
at Microsoft.AnalysisServices.Server.Update(IMajorObject obj, UpdateOptions o
ptions, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollection
impactResult)
at Microsoft.AnalysisServices.Server.SendUpdate(IMajorObject obj, UpdateOptio
ns options, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollect
ion impactResult)
at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions options, Updat
eMode mode, XmlaWarningCollection warnings)
at Microsoft.AnalysisServices.MajorObject.Update()
at Test.Program.Main(String[] args) in E:\Programming\C#\Test\Test\Program.cs
:line 64

Please helpp ...

THanks ... :)

See if this is the same issue as reported in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=317697&SiteID=1

Another idea. Try and run SQL Profiler and monitor activity while your application is issuing the request to update. See if there is any error message.

One more thing to try: Execute the update statement with Database object and not the server object.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I've tried to update from database level and there was no error found. Thanks.

One more thing i would like to know is, what's the different between

DimensionAttribute.KeyColumns and DimensionAttribute.NameColumn. I've

searched from the internet and still not found any good answer that can

make me understand the difference. I also got no explanation from MSDN

( I don't know if there is update in MSDN that explain about it more

detail ).

Thanks in advance ....|||

To answer your DimensionAttribute.KeyColumn vs DimensionAttribute.NameColumn.

These allow you to load key and name for dimension member separately. Imagine dimension members can be translated to different languages, you need a key to be able to reference a sinlge memeber. That is only a single example, there are many cases where you need to have key and name separate.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Need Help ...
Why this error can occure?

Microsoft.AnalysisServices.OperationException: Errors in the metadata manager. T
he Branch dimension has either zero or multiple key attributes.

at Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadRespon
se(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean throwIfEr
ror)
at Microsoft.AnalysisServices.AnalysisServicesClient.Alter(IMajorObject obj,
ObjectExpansion expansion, ImpactDetailCollection impact, Boolean allowCreate)
at Microsoft.AnalysisServices.Server.Update(IMajorObject obj, UpdateOptions o
ptions, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollection
impactResult)
at Microsoft.AnalysisServices.Server.SendUpdate(IMajorObject obj, UpdateOptio
ns options, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollect
ion impactResult)
at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions options, Updat
eMode mode, XmlaWarningCollection warnings)
at Microsoft.AnalysisServices.MajorObject.Update()
at Test.Program.Main(String[] args) in E:\Programming\C#\Test\Test\Program.cs
:line 128Press any key to continue . . .

This is the part of my source code:

Server aServer = new Server ( );
Database aDatabase = null;
Cube aCube = null;
CubeDimension aCubeDim = null;
Dimension aDimension = null;
DimensionAttribute anAttribute = null;
MeasureGroup mg = null;
Measure aMeasure = null;
Hierarchy hier;
RegularMeasureGroupDimension regMgDim;
ManyToManyMeasureGroupDimension mmMgDim;
MeasureGroupAttribute mgAttr;

String strProvider = null;
String strDataSource = null;
String strSourceDSN = null;

String CubePath = "E:\\Programming\\C#\\Test\\Test\\TEHSuite.cub";

if ( File.Exists ( CubePath ) )
File.Delete ( CubePath );

strProvider = "PROVIDER=MSOLAP";
strDataSource = "Data Source=" + CubePath;
strSourceDSN = "SOURCE_DSN=TEHSuite";

try
{
aServer.Connect ( strProvider + ";" + strDataSource + ";" + strSourceDSN + ";" );

aDatabase = aServer.Databases.FindByName ( "TEHSuite" );

if ( aDatabase != null )
aDatabase.Drop ( );

aDatabase = aServer.Databases.Add ( "TEHSuite" );
aDatabase.ID = "TEHSuite";
aDatabase.Update ( );

aCube = aDatabase.Cubes.FindByName ( "Sales" );

if ( aCube != null )
aCube.Drop ( );

aCube = new Cube ( );
aCube.Name = "Sales";
aCube.StorageMode = StorageMode.Molap;

aDatabase.Cubes.Add ( aCube );

aDimension = aDatabase.Dimensions.Add ( "Branch" );
aDimension.UnknownMember = UnknownMemberBehavior.Hidden;
aDimension.AttributeAllMemberName = "All Branch";

anAttribute = new DimensionAttribute ( );
anAttribute.ID = "Branch Name";
anAttribute.Name = "Branch Name";
anAttribute.KeyColumns.Add ( new DataItem ( "DimBranch" , "BranchID" ) );
anAttribute.NameColumn = new DataItem ( "DimBranch" , "BranchName" );
aDimension.Attributes.Add ( anAttribute );

anAttribute = new DimensionAttribute ( );
anAttribute.ID = "City Name";
anAttribute.Name = "City Name";
anAttribute.KeyColumns.Add ( new DataItem ( "DimCity" , "CityID" ) );
anAttribute.NameColumn = new DataItem ( "DimCity" , "CityName" );
aDimension.Attributes.Add ( anAttribute );

anAttribute = new DimensionAttribute ( );
anAttribute.ID = "Country Name";
anAttribute.Name = "Country Name";
anAttribute.KeyColumns.Add ( new DataItem ( "DimCountry" , "CountryID" ) );
anAttribute.NameColumn = new DataItem ( "DimCountry" , "CountryName" );
aDimension.Attributes.Add ( anAttribute );

anAttribute = new DimensionAttribute ( );
anAttribute.ID = "Continent Name";
anAttribute.Name = "Continent Name";
anAttribute.KeyColumns.Add ( new DataItem ( "DimContinent" , "ContinentID" ) );
anAttribute.NameColumn = new DataItem ( "DimContinent" , "ContinentID" );
aDimension.Attributes.Add ( anAttribute );

hier = aDimension.Hierarchies.Add ( "Branch Categories" );
hier.AllMemberName = "All Products";
hier.Levels.Add ( "Branch Name" ).SourceAttributeID = "Branch Name";
hier.Levels.Add ( "City Name" ).SourceAttributeID = "City Name";
hier.Levels.Add ( "Country Name" ).SourceAttributeID = "Country Name";
hier.Levels.Add ( "Continent Name" ).SourceAttributeID = "Continent Name";

aDimension.Update ( );
aCube.Dimensions.Add ( aDimension.ID );

Thanks ...

Fastest connection method?

I am trying to find some info on the fastest connection transport for an app that is running on the same box as a SQL 2005 instance. The app does a large number of updates (high volume of data).. win32 using native ODBC. I am trying to find info on which connection mechanism is best... socket, pipes, etc. I read somewhere that there is a file mapped method available but i cannot find info on that either. Also, is there any performance difference between the old SQL OCBD drive and the new SQL Native Client OCBD drive?

Thanks.

For best performance, I recommend you use the SQL Native Client ODBC driver with SQL 2005. I would also recommend you use TCP/IP as the transport.

Regards,

Uwa.

|||Thanks for the info... does anyone know of any benchmarks for these methods? I have seen it written that shared memory should be used for best performance when on the same box as the SQL server... but have never seen numbers to prove or disprove this.

Fake "Broken connection" error

I get the following error when running a particular query, although I know
for sure the server and network are working properly:
"[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
Is this a hint that something else might be wrong with my sql server?
Any funky characters in the query? If memory servers correctly thats the
message I got when I had a } one time.
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:42E86DB8-21EE-4D35-854A-0D73B416BF9B@.microsoft.com...
>I get the following error when running a particular query, although I know
> for sure the server and network are working properly:
>
> "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken"
>
> Is this a hint that something else might be wrong with my sql server?
>
>
|||Hi Chris,
Thank you very much for your reply. No, the query contains no funky
characters such as '{'. If it did that should return a different kind of
error, shouldn't it?
"ChrisR" wrote:

> Any funky characters in the query? If memory servers correctly thats the
> message I got when I had a } one time.
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:42E86DB8-21EE-4D35-854A-0D73B416BF9B@.microsoft.com...
>
>
|||Not sure as it's been a while...
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:14502176-0C71-4B79-941C-8EF965238B15@.microsoft.com...[vbcol=seagreen]
> Hi Chris,
> Thank you very much for your reply. No, the query contains no funky
> characters such as '{'. If it did that should return a different kind of
> error, shouldn't it?
> "ChrisR" wrote:

Fake "Broken connection" error

I get the following error when running a particular query, although I know
for sure the server and network are working properly:
"[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckFor
Data
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
Is this a hint that something else might be wrong with my sql server?Any funky characters in the query? If memory servers correctly thats the
message I got when I had a } one time.
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:42E86DB8-21EE-4D35-854A-0D73B416BF9B@.microsoft.com...
>I get the following error when running a particular query, although I know
> for sure the server and network are working properly:
>
> "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckF
orData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken"
>
> Is this a hint that something else might be wrong with my sql server?
>
>|||Hi Chris,
Thank you very much for your reply. No, the query contains no funky
characters such as '{'. If it did that should return a different kind o
f
error, shouldn't it?
"ChrisR" wrote:

> Any funky characters in the query? If memory servers correctly thats the
> message I got when I had a } one time.
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:42E86DB8-21EE-4D35-854A-0D73B416BF9B@.microsoft.com...
>
>|||Not sure as it's been a while...
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:14502176-0C71-4B79-941C-8EF965238B15@.microsoft.com...[vbcol=seagreen]
> Hi Chris,
> Thank you very much for your reply. No, the query contains no funky
> characters such as '{'. If it did that should return a different kind
of
> error, shouldn't it?
> "ChrisR" wrote:
>

Fake "Broken connection" error

I get the following error when running a particular query, although I know
for sure the server and network are working properly:
"[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
Is this a hint that something else might be wrong with my sql server?Any funky characters in the query? If memory servers correctly thats the
message I got when I had a } one time.
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:42E86DB8-21EE-4D35-854A-0D73B416BF9B@.microsoft.com...
>I get the following error when running a particular query, although I know
> for sure the server and network are working properly:
>
> "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken"
>
> Is this a hint that something else might be wrong with my sql server?
>
>|||Hi Chris,
Thank you very much for your reply. No, the query contains no funky
characters such as '{'. If it did that should return a different kind of
error, shouldn't it?
"ChrisR" wrote:
> Any funky characters in the query? If memory servers correctly thats the
> message I got when I had a } one time.
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:42E86DB8-21EE-4D35-854A-0D73B416BF9B@.microsoft.com...
> >I get the following error when running a particular query, although I know
> > for sure the server and network are working properly:
> >
> >
> > "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> > (CheckforData()).
> > Server: Msg 11, Level 16, State 1, Line 0
> > General network error. Check your network documentation.
> >
> > Connection Broken"
> >
> >
> > Is this a hint that something else might be wrong with my sql server?
> >
> >
> >
>
>|||Not sure as it's been a while...
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:14502176-0C71-4B79-941C-8EF965238B15@.microsoft.com...
> Hi Chris,
> Thank you very much for your reply. No, the query contains no funky
> characters such as '{'. If it did that should return a different kind of
> error, shouldn't it?
> "ChrisR" wrote:
>> Any funky characters in the query? If memory servers correctly thats the
>> message I got when I had a } one time.
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:42E86DB8-21EE-4D35-854A-0D73B416BF9B@.microsoft.com...
>> >I get the following error when running a particular query, although I
>> >know
>> > for sure the server and network are working properly:
>> >
>> >
>> > "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
>> > (CheckforData()).
>> > Server: Msg 11, Level 16, State 1, Line 0
>> > General network error. Check your network documentation.
>> >
>> > Connection Broken"
>> >
>> >
>> > Is this a hint that something else might be wrong with my sql server?
>> >
>> >
>> >
>>

Failure to Connect Locally when Offline

Hi guys,
I sent this same message to the connection newsgroup, but I thought it
wouldn't hurt to post it here too.
We use replication, so a few clients connect to the network database,
and sometimes to MSDE on their computer when outside the office. They use
their domain Windows account to connect to both.
Here what is happening, they are able to connect fine to MSDE when they
are in their office, but when they leave the office, they can no longer
connect locally. They still use their domain account when they are offline.
Any idea why this would happen?
Thanks,
Maer
Is there a chance that MSDE cannot verify the domain account when they are
offline?
S
"Maer" <maer@.auditleverage.com> wrote in message
news:uwwU2Sq3HHA.5212@.TK2MSFTNGP04.phx.gbl...
> Hi guys,
> I sent this same message to the connection newsgroup, but I thought it
> wouldn't hurt to post it here too.
> We use replication, so a few clients connect to the network database,
> and sometimes to MSDE on their computer when outside the office. They use
> their domain Windows account to connect to both.
> Here what is happening, they are able to connect fine to MSDE when they
> are in their office, but when they leave the office, they can no longer
> connect locally. They still use their domain account when they are
> offline.
> Any idea why this would happen?
> Thanks,
> Maer
>
|||what is the connection error that they are getting?
relevantNoise - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Maer" <maer@.auditleverage.com> wrote in message
news:uwwU2Sq3HHA.5212@.TK2MSFTNGP04.phx.gbl...
> Hi guys,
> I sent this same message to the connection newsgroup, but I thought it
> wouldn't hurt to post it here too.
> We use replication, so a few clients connect to the network database,
> and sometimes to MSDE on their computer when outside the office. They use
> their domain Windows account to connect to both.
> Here what is happening, they are able to connect fine to MSDE when they
> are in their office, but when they leave the office, they can no longer
> connect locally. They still use their domain account when they are
> offline.
> Any idea why this would happen?
> Thanks,
> Maer
>

Failure sending mail: The transport lost its connection to the server

More and more often, I am seeing this error message in RS subscriptions. I would assume it means the IIS server can't connect to the corporate exchange server. However I was wondering if there is some timeout setting/configuration in RS or the IIS server (or any other kind of setting) I can change to help alleviate this problem.

Has anyone else experienced this? Or know how to fix it?

Thanks,

Jeff

UPDATE: After looking closer at the subscriptions, I have noticed that subscriptions set up by a RS Admin (who is also a server admin) seem to be working fine. It is the subscriptions that are set up by ordinary users that are failing...

We are getting this error for large reports being sent as Excel files. I think it's actually doing what it says: it's losing it's connection to the Exchange Server. I would think that makes it an Exchange configuration problem. Not sure tho. Any ideas?
|||I checked the log on this. It's an actual "no response" from the email server (0x800ccc13).Error sending email. System.Runtime.InteropServices.COMException (0x80040211): The message could not be sent to the SMTP server. The transport error code was 0x800ccc13. The server response was not available at ReportingServicesCDOInterop.MessageClass.Send() at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)|||

This looks like an email server availability issue. Have you checked with your IT folks about the availability of the server?

There should be no difference between subscriptions created by administrators and those created by end users. All E-mail messages are sent to the email server using the same account.

-Lukasz

|||It's not the email server - according the the logs. It appears to be a problem exporting huge reports to Excel. The email server is not even receiving a HELO request. Reports scheduled just before and after are being sent, and if we reduce the date span or switch the format to CSV, it sends successfully.
|||

There are a number of threads on exporting large reports to excel and other formats.

For a run down of some of the solution vectors you can look at the posts from Brian and Bruce on this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=22652&SiteID=1

Hope that helps,

-Lukasz

|||Thanks, but none of them actually provide a solution...that works.
|||

Are you able to make the export to excel fail when you render the report interactively? How much data are you putting in your excel file?

-Lukasz

|||I have the same problem, I have a large report that renders 26,301 rows for Excel. When I setup the subscription in Report Services with the Excel render option, I get the status message - 'Failure sending mail: The transport lost its connection to the server', even though it renders the report interactively just fine. Any idea on what is causing this problem or how to fix it?|||

Check the email server your using for timeouts on sending messages or size restrictions on outbound messages. It could be that your 20K+ row excel doc maxes out the email server capability. You may be able to find information in the email server logs related to the request. From the error, it appears report server was successfully communicating with the email server, but then the connection was lost part way through the send.

-Lukasz

Failure sending mail: The transport lost its connection to the server

More and more often, I am seeing this error message in RS subscriptions. I would assume it means the IIS server can't connect to the corporate exchange server. However I was wondering if there is some timeout setting/configuration in RS or the IIS server (or any other kind of setting) I can change to help alleviate this problem.

Has anyone else experienced this? Or know how to fix it?

Thanks,

Jeff

UPDATE: After looking closer at the subscriptions, I have noticed that subscriptions set up by a RS Admin (who is also a server admin) seem to be working fine. It is the subscriptions that are set up by ordinary users that are failing...

We are getting this error for large reports being sent as Excel files. I think it's actually doing what it says: it's losing it's connection to the Exchange Server. I would think that makes it an Exchange configuration problem. Not sure tho. Any ideas?
|||I checked the log on this. It's an actual "no response" from the email server (0x800ccc13).Error sending email. System.Runtime.InteropServices.COMException (0x80040211): The message could not be sent to the SMTP server. The transport error code was 0x800ccc13. The server response was not available at ReportingServicesCDOInterop.MessageClass.Send() at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)|||

This looks like an email server availability issue. Have you checked with your IT folks about the availability of the server?

There should be no difference between subscriptions created by administrators and those created by end users. All E-mail messages are sent to the email server using the same account.

-Lukasz

|||It's not the email server - according the the logs. It appears to be a problem exporting huge reports to Excel. The email server is not even receiving a HELO request. Reports scheduled just before and after are being sent, and if we reduce the date span or switch the format to CSV, it sends successfully.
|||

There are a number of threads on exporting large reports to excel and other formats.

For a run down of some of the solution vectors you can look at the posts from Brian and Bruce on this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=22652&SiteID=1

Hope that helps,

-Lukasz

|||Thanks, but none of them actually provide a solution...that works.
|||

Are you able to make the export to excel fail when you render the report interactively? How much data are you putting in your excel file?

-Lukasz

|||I have the same problem, I have a large report that renders 26,301 rows for Excel. When I setup the subscription in Report Services with the Excel render option, I get the status message - 'Failure sending mail: The transport lost its connection to the server', even though it renders the report interactively just fine. Any idea on what is causing this problem or how to fix it?|||

Check the email server your using for timeouts on sending messages or size restrictions on outbound messages. It could be that your 20K+ row excel doc maxes out the email server capability. You may be able to find information in the email server logs related to the request. From the error, it appears report server was successfully communicating with the email server, but then the connection was lost part way through the send.

-Lukasz

Failure sending mail: The transport lost its connection to the server

More and more often, I am seeing this error message in RS subscriptions. I would assume it means the IIS server can't connect to the corporate exchange server. However I was wondering if there is some timeout setting/configuration in RS or the IIS server (or any other kind of setting) I can change to help alleviate this problem.

Has anyone else experienced this? Or know how to fix it?

Thanks,

Jeff

UPDATE: After looking closer at the subscriptions, I have noticed that subscriptions set up by a RS Admin (who is also a server admin) seem to be working fine. It is the subscriptions that are set up by ordinary users that are failing...

We are getting this error for large reports being sent as Excel files. I think it's actually doing what it says: it's losing it's connection to the Exchange Server. I would think that makes it an Exchange configuration problem. Not sure tho. Any ideas?
|||I checked the log on this. It's an actual "no response" from the email server (0x800ccc13).Error sending email. System.Runtime.InteropServices.COMException (0x80040211): The message could not be sent to the SMTP server. The transport error code was 0x800ccc13. The server response was not available at ReportingServicesCDOInterop.MessageClass.Send() at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)|||

This looks like an email server availability issue. Have you checked with your IT folks about the availability of the server?

There should be no difference between subscriptions created by administrators and those created by end users. All E-mail messages are sent to the email server using the same account.

-Lukasz

|||It's not the email server - according the the logs. It appears to be a problem exporting huge reports to Excel. The email server is not even receiving a HELO request. Reports scheduled just before and after are being sent, and if we reduce the date span or switch the format to CSV, it sends successfully.
|||

There are a number of threads on exporting large reports to excel and other formats.

For a run down of some of the solution vectors you can look at the posts from Brian and Bruce on this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=22652&SiteID=1

Hope that helps,

-Lukasz

|||Thanks, but none of them actually provide a solution...that works.
|||

Are you able to make the export to excel fail when you render the report interactively? How much data are you putting in your excel file?

-Lukasz

|||I have the same problem, I have a large report that renders 26,301 rows for Excel. When I setup the subscription in Report Services with the Excel render option, I get the status message - 'Failure sending mail: The transport lost its connection to the server', even though it renders the report interactively just fine. Any idea on what is causing this problem or how to fix it?|||

Check the email server your using for timeouts on sending messages or size restrictions on outbound messages. It could be that your 20K+ row excel doc maxes out the email server capability. You may be able to find information in the email server logs related to the request. From the error, it appears report server was successfully communicating with the email server, but then the connection was lost part way through the send.

-Lukasz

Failure sending mail: The transport lost its connection to the ser

When I create e-mail subscriptions they have an error with the status:
"Failure sending mail: The transport lost its connection to the server."
Has someone run into this before?
Thanks,
NathanI have seen this happen when the smtp server is busy. The best way to
configure the email is to have it use the local SMTP servers pickup
directory and have the local smtp server relay the messages to your actual
SMTP server.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nate Dogg" <NateDogg@.discussions.microsoft.com> wrote in message
news:9CA05874-3791-48C6-858E-67A174849D78@.microsoft.com...
> When I create e-mail subscriptions they have an error with the status:
> "Failure sending mail: The transport lost its connection to the server."
> Has someone run into this before?
> Thanks,
> Nathan

failure in starting the process for the user instance

High everybody,

I have a set of winforms (.NET 2.0 assemblies) ( 6 total ) that each tries to open a connection to the same Database file using the same connection string. In the connection string, UserInstance is set to true.

If each winform is started manually after opening the session on the computer, the connection to the database performs well for every winform.

If the same winforms are automatically started at the opening of the user session, the the following error is reported by each application when trying to open the connection :

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

The only way to overcome this problem is :

to stop the winforms, to stop the SQLExpress service, to kill the remaining sqlserv.exe running task, then restarting SQLExpress service, and finally manualy start the winforms.

Any help would be greatly appreciated.

Hi Henri,

When you say "a set of winforms" do you mean a single applications or six different applications?

Are all these forms being opened by the same user?

Is the database embedded in the application or is it located somewhere else? (If somewhere else, where?)

You can frequently find more detailed information about this error in the User Instance error log which is located in the user profile directories of the user who is starting the user instance: C:\Documents and Settings\<user name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. Check out this file and let us know what it says.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

I get a similar problem. I get this message anytime I try to open the database from within VB Express. I was following Lesson 08 by trying to Add a SQL Server Item Template to my project.

Here is what I found in my logs.

2006-04-08 17:11:44.62 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.

2006-04-08 17:11:44.07 spid4s Starting up database 'mssqlsystemresource'.
2006-04-08 17:11:44.32 spid4s Error: 15466, Severity: 16, State: 1.
2006-04-08 17:11:44.32 spid4s An error occurred during decryption.
2006-04-08 17:11:44.50 spid4s The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

|||

Does this only happen when connecting to the user instance? Can you connect to the main instance?

Thanks
Laurentiu

|||

Hi Mike,

Thank you for your help and sorry for the late answer, but I am still on holidays. I will try to get the User Instance log at the customer site.

my application consists of six different exe that are started at the same time when opening the user session ( on a windows 2000 server). Each of these exe makes a connection to the same database file using the same connection string. When the user session is opened, there are other softwares that are automatically started which makes the CPU usage about 100% for a few minutes.

However, when each exe is started manually one after the other, the connection to the User Instance is done without error.

regards,

Henri d'Orgeval

|||

Hi Mike,

here is the error log content :

2006-04-13 10:41:30.09 Server Microsoft SQL Server 2005 - 9.00.1314.06 (Intel X86)
Sep 2 2005 21:10:31
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

2006-04-13 10:41:30.09 Server (c) 2005 Microsoft Corporation.
2006-04-13 10:41:30.09 Server All rights reserved.
2006-04-13 10:41:30.09 Server Server process ID is 2468.
2006-04-13 10:41:30.09 Server Logging SQL Server messages in file 'C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log'.
2006-04-13 10:41:30.09 Server Registry startup parameters:
2006-04-13 10:41:30.09 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-04-13 10:41:30.09 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-04-13 10:41:30.09 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-04-13 10:41:30.09 Server Command Line Startup Parameters:
2006-04-13 10:41:30.09 Server -d C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\master.mdf
2006-04-13 10:41:30.09 Server -l C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\mastlog.ldf
2006-04-13 10:41:30.09 Server -e C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log
2006-04-13 10:41:30.09 Server -c
2006-04-13 10:41:30.09 Server -S SQLEXPRESS
2006-04-13 10:41:30.09 Server -s F2DAFDAF-4081-43
2006-04-13 10:41:30.09 Server -w 60
2006-04-13 10:41:30.09 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-04-13 10:41:30.09 Server Detected 2 CPUs. This is an informational message; no user action is required.
2006-04-13 10:41:30.46 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-04-13 10:41:30.79 Server Database mirroring has been enabled on this instance of SQL Server.
2006-04-13 10:41:30.81 spid5s Starting up database 'master'.
2006-04-13 10:41:30.90 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-04-13 10:41:31.04 spid5s SQL Trace ID 1 was started by login "sa".
2006-04-13 10:41:31.10 spid5s Starting up database 'mssqlsystemresource'.
2006-04-13 10:41:31.21 Server Server local connection provider is ready to accept connection on [ \\.\pipe\F2DAFDAF-4081-43\tsql\query ].
2006-04-13 10:41:31.21 spid7s Starting up database 'model'.
2006-04-13 10:41:31.21 spid5s Server name is 'IT-SHAPER1\F2DAFDAF-4081-43'. This is an informational message only. No user action is required.
2006-04-13 10:41:31.21 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2006-04-13 10:41:31.21 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-04-13 10:41:31.34 spid5s Starting up database 'msdb'.
2006-04-13 10:41:31.48 spid7s Clearing tempdb database.
2006-04-13 10:41:32.12 spid7s Starting up database 'tempdb'.
2006-04-13 10:41:32.18 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-04-13 10:41:32.18 spid10s The Service Broker protocol transport is disabled or not configured.
2006-04-13 10:41:32.18 spid10s The Database Mirroring protocol transport is disabled or not configured.
2006-04-13 10:41:32.34 spid10s Service Broker manager has started.
2006-04-13 10:41:32.54 spid51 Starting up database 'C:\PROGRAM FILES\W.A.I.S. TECHNOLOGY\DISTRIBUTION MANAGER\DISTRIBUTIONMANAGER.MDF'.

There are other error logs named errorN.log that contains the same content

Best regards,

Henri d'Orgeval

|||

This appears to be a successful errorlog. Can you post the errorlog that contains the errors you have extracted and posted in your earlier post?

Also, based on your description of the problem, have you considered starting the six instances of your program in succession, with a small delay between them. This might be a workaround until we determine the cause of this failure.

Thanks
Laurentiu

|||

Hi Henri,

I'm still not clear on when you're getting the error. You state that when each exe is started one after another the connection is done without error, when do you get the error?

As for the error log, could you confirm that you pulled that error log for the User Instance and not the main instance? The User Instance error log would be located at C:\Documents and Settings\<user>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS where <user> is the user that experienced the problem.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hi Henri,

Could you give a bit more information as requested by both Laurentiu and me? It's still not clear to use how your application is actually starting and where the error is occuring.

Have you tried the process that Laurentiu suggests and did it work for you?

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Hi Mike and Laurentiu,

I confirm that if each exe is started one after the other, waiting a little bit between each launch, everything is right. This is the only workaround I found that works every time.

If the 6 exes are started at the same time at the opening of the user session ( ie the 6 exes are started from the Startup folder of the User Session), then each exe reports the same error which is the one I mentioned at my first post.

To reproduce the problem the following is done :

1°) the station is rebooted,

2°) as soon as the Login/Password screen appears on the screen, the operator opens the session, which starts the 6 exe at the same time.

3°) then each exe immediately tries to open the connection by attaching the same database file and using the same connection string.

About the error log file I posted :

There are many error log files in the User Instance SQL folder, but the content of each of these error log files is similar to what I posted.

I agree with you, surprisingly it does not look like an error has occured !

I am currently rewriting the software in order to have only one exe that will start 6 different threads one after the other.

Best regards

Henri d'Orgeval

|||

I actually expect that only 5 exes are failing and the 6th succeeds. Can you please confirm this?

The process of connecting to the user instance works like this (this is a high level description):

1. if the instance exists, connect to it
2. otherwise start up the instance
3. connect to the instance

I expect all exe's try step 1 and find that there's no instance, then they all try to start it up, but only one will succeed and the others will fail. This is to be expected and can be resolved in several ways:

(a) start the exe's in succession. This was already proposed and verified to be a valid solution.
(b) start one exe first and after some delay start all the others.
(c) implement code in the exe to handle the connection failure and attempt to reconnect for several times with some delay in between.

Thanks
Laurentiu