Fetch data from Multiple SQL Servers

Hi,
I have 10+ SQL Servers, from my head office server i want
to connect to all branch server and execute a sql
statement and results to be inserted into a table of Head
Office Server.
RegardsYou can use a linked server... ( See linked Servers in Books on line.) Set
up a linked server then use the 4 part name
insert into mytable select * from server1.pubs.dbo.titles
etc
Or you could use replication to move the data on a regular basis...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Asmath" <anonymous@.discussions.microsoft.com> wrote in message
news:1071d01c3f3c4$e43bcdd0$a001280a@.phx.gbl...
> Hi,
> I have 10+ SQL Servers, from my head office server i want
> to connect to all branch server and execute a sql
> statement and results to be inserted into a table of Head
> Office Server.
>
> Regards|||Hi,
In my head office server, i found the list of all branch
server in Security->Remote Servers.
I found no entries in Security->LinkedServers, when i try
to add linked server, error appears already exists?
And how to use authentication process?
>--Original Message--
>You can use a linked server... ( See linked Servers in
Books on line.) Set
>up a linked server then use the 4 part name
>insert into mytable select * from server1.pubs.dbo.titles
>etc
>Or you could use replication to move the data on a
regular basis...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Asmath" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1071d01c3f3c4$e43bcdd0$a001280a@.phx.gbl...
>> Hi,
>> I have 10+ SQL Servers, from my head office server i
want
>> to connect to all branch server and execute a sql
>> statement and results to be inserted into a table of
Head
>> Office Server.
>>
>> Regards
>
>.
>

Fetch data from Multiple SQL Servers

Hi,
I have 10+ SQL Servers, from my head office server i want
to connect to all branch server and execute a sql
statement and results to be inserted into a table of Head
Office Server.
RegardsYou can use a linked server... ( See linked Servers in Books on line.) Set
up a linked server then use the 4 part name
insert into mytable select * from server1.pubs.dbo.titles
etc
Or you could use replication to move the data on a regular basis...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Asmath" <anonymous@.discussions.microsoft.com> wrote in message
news:1071d01c3f3c4$e43bcdd0$a001280a@.phx
.gbl...
> Hi,
> I have 10+ SQL Servers, from my head office server i want
> to connect to all branch server and execute a sql
> statement and results to be inserted into a table of Head
> Office Server.
>
> Regards|||Hi,
In my head office server, i found the list of all branch
server in Security->Remote Servers.
I found no entries in Security->LinkedServers, when i try
to add linked server, error appears already exists?
And how to use authentication process?

>--Original Message--
>You can use a linked server... ( See linked Servers in
Books on line.) Set
>up a linked server then use the 4 part name
>insert into mytable select * from server1.pubs.dbo.titles
>etc
>Or you could use replication to move the data on a
regular basis...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Asmath" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1071d01c3f3c4$e43bcdd0$a001280a@.phx
.gbl...
want
Head
>
>.
>

Feedback on Microsoft Connect Site

I don't know how many folks here log into the Microsoft Connect site occasionally to check suggestions and bugs submitted to Microsoft for SQL Server and SSIS (still called DTS on their list). A small pecentage? Almost everyone? (Possibly in this group.) Anyone can vote for feedback they think is important. Theoretically issues with the most votes will get Microsoft's attention first.

Links to a couple new submissions that look interesting:

1. SSMS/QA Style Message Logging for SSIS Execute SQL Tasks

2. ForEach SMO Enumerator Filtering

I vetted these issues in the forum first, so hopefully they're legitimate enough to warrant some useful feedback or even a few high fives!

Hi M.Glenn,

Those are important - thanks for pointing them out.

:{> Andy

|||

Andy, thanks for the encouragement and votes. I wonder if it's common to enter a vote for your own suggestion? I'm inclined against it, but if everyone is legitimately entitled it might be helpful...

|||

I generally don't. But that's just because I forget

|||

Jamie, If that's the only reason in your opinion that's good enough for me. I'm heading for the voting booth!

(Actually, now that I've given it a little more thought it makes sense since voting is the only way to tell Microsoft how important you think the suggestion or bug is.)

|||

M.Glenn wrote:

Jamie, If that's the only reason in your opinion that's good enough for me. I'm heading for the voting booth!

(Actually, now that I've given it a little more thought it makes sense since voting is the only way to tell Microsoft how important you think the suggestion or bug is.)

I would tend to think that of the majority of bug submissions that the author deems it a high priority! ;)

Never-the-less, the voting system doesn't hold a whole lot of merit to it unless the voter gives his/her feedback to back up that vote. Microsoft has listened to plenty of my submissions without even having a single vote on them. And sometimes there are those with a large number of high votes that get closed (not fixed) because of one reason or another. So it all depends, in other words.

The moral is to be sure to leave appropriate feedback when voting so that you can leave your comments which may help clarify to Microsoft what exactly the bug is and how it impacts others.

|||

Phil Brammer wrote:

Microsoft has listened to plenty of my submissions without even having a single vote on them. And sometimes there are those with a large number of high votes that get closed (not fixed) because of one reason or another. So it all depends, in other words.

The moral is to be sure to leave appropriate feedback when voting so that you can leave your comments which may help clarify to Microsoft what exactly the bug is and how it impacts others.

Good information. If that's the case, votes are not as critical as the site design seems to imply. The vote tally would probably be a bigger factor if the ratio of feedback submissions to Microsoft resources was high enough to overwhelm those resources. It sounds like they're able to review pretty much everything--another sign that SSIS is a high-priority product at Microsoft.

|||

I'm a firm believer that leaving a comment is much more valuable than a vote. If I were reviewing them then I would just ignore the votes cos of course most people are gonna vote high. I would want to know WHY this is important to you, how much grief has been caused without it? How could the product best be improved?

Qualititive feedback is much better than quantitive.

-Jamie

|||

I basically agree with what you guys are saying, but partly because Microsoft seems to be reviewing all submissions regardless of vote count. I would add that the vote itself is providing important qualitative information--showing at-a-glance how important the issue is for reviewers/commentators.

As for the original submitter, I could easily imagine voting a 3 or 4 on my own submission if it was cosmetic or less critical to me than other things I had in the queue. Human nature being what it is though, you guys are probably right about the temptation to give one's own submission a 5 in every case.

Feedback on Microsoft Connect Site

I don't know how many folks here log into the Microsoft Connect site occasionally to check suggestions and bugs submitted to Microsoft for SQL Server and SSIS (still called DTS on their list). A small pecentage? Almost everyone? (Possibly in this group.) Anyone can vote for feedback they think is important. Theoretically issues with the most votes will get Microsoft's attention first.

Links to a couple new submissions that look interesting:

1. SSMS/QA Style Message Logging for SSIS Execute SQL Tasks

2. ForEach SMO Enumerator Filtering

I vetted these issues in the forum first, so hopefully they're legitimate enough to warrant some useful feedback or even a few high fives!

Hi M.Glenn,

Those are important - thanks for pointing them out.

:{> Andy

|||

Andy, thanks for the encouragement and votes. I wonder if it's common to enter a vote for your own suggestion? I'm inclined against it, but if everyone is legitimately entitled it might be helpful...

|||

I generally don't. But that's just because I forget

|||

Jamie, If that's the only reason in your opinion that's good enough for me. I'm heading for the voting booth!

(Actually, now that I've given it a little more thought it makes sense since voting is the only way to tell Microsoft how important you think the suggestion or bug is.)

|||

M.Glenn wrote:

Jamie, If that's the only reason in your opinion that's good enough for me. I'm heading for the voting booth!

(Actually, now that I've given it a little more thought it makes sense since voting is the only way to tell Microsoft how important you think the suggestion or bug is.)

I would tend to think that of the majority of bug submissions that the author deems it a high priority! ;)

Never-the-less, the voting system doesn't hold a whole lot of merit to it unless the voter gives his/her feedback to back up that vote. Microsoft has listened to plenty of my submissions without even having a single vote on them. And sometimes there are those with a large number of high votes that get closed (not fixed) because of one reason or another. So it all depends, in other words.

The moral is to be sure to leave appropriate feedback when voting so that you can leave your comments which may help clarify to Microsoft what exactly the bug is and how it impacts others.

|||

Phil Brammer wrote:

Microsoft has listened to plenty of my submissions without even having a single vote on them. And sometimes there are those with a large number of high votes that get closed (not fixed) because of one reason or another. So it all depends, in other words.

The moral is to be sure to leave appropriate feedback when voting so that you can leave your comments which may help clarify to Microsoft what exactly the bug is and how it impacts others.

Good information. If that's the case, votes are not as critical as the site design seems to imply. The vote tally would probably be a bigger factor if the ratio of feedback submissions to Microsoft resources was high enough to overwhelm those resources. It sounds like they're able to review pretty much everything--another sign that SSIS is a high-priority product at Microsoft.

|||

I'm a firm believer that leaving a comment is much more valuable than a vote. If I were reviewing them then I would just ignore the votes cos of course most people are gonna vote high. I would want to know WHY this is important to you, how much grief has been caused without it? How could the product best be improved?

Qualititive feedback is much better than quantitive.

-Jamie

|||

I basically agree with what you guys are saying, but partly because Microsoft seems to be reviewing all submissions regardless of vote count. I would add that the vote itself is providing important qualitative information--showing at-a-glance how important the issue is for reviewers/commentators.

As for the original submitter, I could easily imagine voting a 3 or 4 on my own submission if it was cosmetic or less critical to me than other things I had in the queue. Human nature being what it is though, you guys are probably right about the temptation to give one's own submission a 5 in every case.

FCB::Open failed: Could not open device for virtual dev

Hi, if any one could helpme here,

I am trying to connect to one oof my databases in SQL 2000 server through ASP using similasr coode:

<% @.LANGUAGE = VBSCRIPT %>
<% Option Explicit %>

<html>
<head>
<title>testing our connection</title>
</head>
<body>

<%
Dim adOpenForwardOnly, adLockReadOnly, adCmdTable

adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = 2

Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

' the following connection string is for connecting to a local SQL Server 2000 database
objConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;" & _
"User ID=sa;Password=tdmes102;Initial Catalog=CustomerProfile;" & _
"Data Source=dani;" & _
"Initial File Name=D:\DB\TDMECustomer_Data.MDF"
' the following connection string is for connecting to a remote SQL Server 2000 database

objRS.Open "CustomerProfile", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

While Not objRS.EOF
Response.Write objRS("Principal") & "<BR>"
objRS.MoveNext
Wend

objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
%>

</body>
</html>

I have tried several diferent ways and I keep getting this foollowing messages the same in the log file.

I tried relocating the database,reinstalling SQL , sp2.

HTTP error:
FCB::Open failed: Could not open device D:\DB\TDMECustomer_Data.MDF for virtual device number (VDN) 0.

SQL log file:
udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device D:\DB\TDMECustomer_Data.MDF.
2002-07-15 09:46:59.11 spid51 FCB::Open failed: Could not open device D:\DB\TDMECustomer_Data.MDF for virtual device number (VDN) 0.You don't supply an Initial File Name, maybe something you use for Access but not for SQL Server. At our site we don't even use the Data Source parameter, we add a Server parameter instead.

Here is a website that has connection examples
ConnectionString (http://www.connectionstrings.com/index.htm)

Example

Standard Security:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;")

Trusted Connection:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

Provider=SQLOLEDB.1;Persist Security Info=False;" & _
"User ID=sa;Password=tdmes102;Initial Catalog=CustomerProfile;" & _
"Data Source=dani;

FastLoad OLEDB Destination with Oralce

Hi,

why Table or View – fast load option in Data Access mode is not listed when we connect Oracle Database in the OLEDB Destination.

Thanks

Jegan

When you switch data sources using an OLE DB destination, you are most probably using a different kind and version of the OLE DB provider. I'm guessing that in your case, you are using Microsoft's Oracle OLE DB provider? that `-fast load` option is a feature of the underlying OLE DB Provider, which in your case, I'm guessing is Microsoft's OLE DB provider (SQL Native Client). And that is probably not a feature of the underlying OLE DB Provider for Oracle that you are using, and thus, do not show-up as an option.

|||Thanks Deniz.

Fastest way to transfer data from DB2 on host to SQL Server

Hello all,
I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
via db2 connect and ODBC to transfer the data. The data size is 30GB. It
would take more than 2 day. It abended two times.
What is the fastest way to transfer data other than this way?
How can I make faster in this case?
any comments?
Thanks in advance,
Do.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1Are these two boxes in the same location?
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:533A6B9900AD9@.SQLMonster.com...
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1|||StarQuest has a very fast replication solution, they should be able to
help you reduce considerably the transfer window:
http://www.starquest.com/Productfolder/infoSQDR.html
Good luck,
Bob
Do Park via SQLMonster.com wrote:
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1

Fastest way to transfer data from DB2 on host to SQL Server

Hello all,
I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
via db2 connect and ODBC to transfer the data. The data size is 30GB. It
would take more than 2 day. It abended two times.
What is the fastest way to transfer data other than this way?
How can I make faster in this case?
any comments?
Thanks in advance,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200508/1
Are these two boxes in the same location?
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:533A6B9900AD9@.droptable.com...
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200508/1
|||StarQuest has a very fast replication solution, they should be able to
help you reduce considerably the transfer window:
http://www.starquest.com/Productfolder/infoSQDR.html
Good luck,
Bob
Do Park via droptable.com wrote:
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200508/1

Fastest way to transfer data from DB2 on host to SQL Server

Hello all,
I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
via DB2 connect and ODBC to transfer the data. The data size is 30GB. It
would take more than 2 day. It abended two times.
What is the fastest way to transfer data other than this way?
How can I make faster in this case?
any comments?
Thanks in advance,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200508/1Are these two boxes in the same location?
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:533A6B9900AD9@.droptable.com...
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via DB2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200508/1|||StarQuest has a very fast replication solution, they should be able to
help you reduce considerably the transfer window:
http://www.starquest.com/Productfolder/infoSQDR.html
Good luck,
Bob
Do Park via droptable.com wrote:
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via DB2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200508/1

Fastest SQL Server Library

I need to connect to the Sql Server 2005 on the local machine using C/C++
and am trying to find the fastest way of doing so. I'm dealing with up to a
million records and usually lots of small selects. I also can't use
joins/stored procedures at this point due to accomodating older software.
Initial testing shows that ole/odbc are nearly equivalent performance wise
and that I should stay as far away from ado as possible. I also took a
cursory glance at dblib and am disregarding it as it doesn't support string
fields longer than 255 characters and it doesn't even come with sql server
anymore.
ODBC seems to by far the winner given what a pain OLE is to use and the
relative lack of documentation for it.
Anything else I'm missing?
Here's a few stats as well (I threw in some other databases I'm testing out
using their c libraries just for fun). No stored procedures/prepared
statements were used in these tests. ADO is using non .net version. Time is
listed in milliseconds.
single 1200 record select (all fields):
SELECT * FROM customers;
ole: 15
odbc: 15
ado: 500
mysql: 30
postgresql: 30
1000 identical 1 record select (2 fields) statements:
// almost useless test given the caching taking place
// note that there's an index on customer
SELECT customer,name FROM customers WHERE customer=1025;
ole: 350
odbc: 340
ado: 2900
mysql: 390
postgresql: 1000
1000 different 1 record select (2 fields) statements:
// interesting note
// sqlserver takes twice as long if single quotes don't surround values
// mysql takes four times as long if single quotes don't surround values
// postgresql is unaffected by single quotes around values
// perhaps the databases are caching the statements besides the quoted
values
SELECT customer,name FROM customers WHERE customer='%d'; (%d = 1000 to 2000)
ole: 4250
odbc: 4250
ado: 10200
mysql: 400
postgresql: 1100
10,000 different multi record selects (2 fields),
79,000 of 350,000 total records retrieved overall
there is an index on orderno
SELECT orderno,product FROM shipped WHERE orderno='%07d'; (%d = 0 to 10000)
ole: 7766
odbc: 7550
ado: 51500
mysql: 23400 (subsequent times 15,400)
postgresql: 35600 (subsequent times 14,400)
-GaryPersonally, I would use ADO. Yes, it's a bit slower than OLE DB (which is
generally considered to be faster than the ODBC library) -- but it's
not -that much- slower, and there are huge benefits in terms of productivity
(ADO is easier to program against than either of the other options). Search
the web and you'll find lots of performance tips and tricks.
High-performance ADO access patterns may not be the same as those for OLE DB
or ODBC libraries.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"garyh" <garyh@.spammenot.involved.com> wrote in message
news:e40lad02ca3@.enews4.newsguy.com...
>I need to connect to the Sql Server 2005 on the local machine using C/C++
>and am trying to find the fastest way of doing so. I'm dealing with up to a
>million records and usually lots of small selects. I also can't use
>joins/stored procedures at this point due to accomodating older software.
> Initial testing shows that ole/odbc are nearly equivalent performance wise
> and that I should stay as far away from ado as possible. I also took a
> cursory glance at dblib and am disregarding it as it doesn't support
> string fields longer than 255 characters and it doesn't even come with sql
> server anymore.
> ODBC seems to by far the winner given what a pain OLE is to use and the
> relative lack of documentation for it.
> Anything else I'm missing?
> Here's a few stats as well (I threw in some other databases I'm testing
> out using their c libraries just for fun). No stored procedures/prepared
> statements were used in these tests. ADO is using non .net version. Time
> is listed in milliseconds.
> single 1200 record select (all fields):
> SELECT * FROM customers;
> ole: 15
> odbc: 15
> ado: 500
> mysql: 30
> postgresql: 30
> 1000 identical 1 record select (2 fields) statements:
> // almost useless test given the caching taking place
> // note that there's an index on customer
> SELECT customer,name FROM customers WHERE customer=1025;
> ole: 350
> odbc: 340
> ado: 2900
> mysql: 390
> postgresql: 1000
> 1000 different 1 record select (2 fields) statements:
> // interesting note
> // sqlserver takes twice as long if single quotes don't surround values
> // mysql takes four times as long if single quotes don't surround values
> // postgresql is unaffected by single quotes around values
> // perhaps the databases are caching the statements besides the quoted
> values
> SELECT customer,name FROM customers WHERE customer='%d'; (%d = 1000 to
> 2000)
> ole: 4250
> odbc: 4250
> ado: 10200
> mysql: 400
> postgresql: 1100
> 10,000 different multi record selects (2 fields),
> 79,000 of 350,000 total records retrieved overall
> there is an index on orderno
> SELECT orderno,product FROM shipped WHERE orderno='%07d'; (%d = 0 to
> 10000)
> ole: 7766
> odbc: 7550
> ado: 51500
> mysql: 23400 (subsequent times 15,400)
> postgresql: 35600 (subsequent times 14,400)
> -Gary
>|||From the SQL Server 2005 Books Online:
<Excerpt>
The SQL Native Client OLE DB provider is a low-level, COM API that is used
for accessing data. The SQL Native Client OLE DB provider is recommended for
developing tools, utilities, or low-level components that need high
performance. The SQL Native Client OLE DB provider is a native, high
performance provider that accesses the SQL Server Tabular Data Stream (TDS)
protocol directly.
The SQL Native Client OLE DB provider exposes interfaces to consumers
wanting access to data on one or more computers running an instance of SQL
Server 2005 or earlier.
The SQL Native Client OLE DB provider is an OLE DB version 2.0-compliant
provider.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"garyh" <garyh@.spammenot.involved.com> wrote in message
news:e40lad02ca3@.enews4.newsguy.com...
>I need to connect to the Sql Server 2005 on the local machine using C/C++
>and am trying to find the fastest way of doing so. I'm dealing with up to a
>million records and usually lots of small selects. I also can't use
>joins/stored procedures at this point due to accomodating older software.
> Initial testing shows that ole/odbc are nearly equivalent performance wise
> and that I should stay as far away from ado as possible. I also took a
> cursory glance at dblib and am disregarding it as it doesn't support
> string fields longer than 255 characters and it doesn't even come with sql
> server anymore.
> ODBC seems to by far the winner given what a pain OLE is to use and the
> relative lack of documentation for it.
> Anything else I'm missing?
> Here's a few stats as well (I threw in some other databases I'm testing
> out using their c libraries just for fun). No stored procedures/prepared
> statements were used in these tests. ADO is using non .net version. Time
> is listed in milliseconds.
> single 1200 record select (all fields):
> SELECT * FROM customers;
> ole: 15
> odbc: 15
> ado: 500
> mysql: 30
> postgresql: 30
> 1000 identical 1 record select (2 fields) statements:
> // almost useless test given the caching taking place
> // note that there's an index on customer
> SELECT customer,name FROM customers WHERE customer=1025;
> ole: 350
> odbc: 340
> ado: 2900
> mysql: 390
> postgresql: 1000
> 1000 different 1 record select (2 fields) statements:
> // interesting note
> // sqlserver takes twice as long if single quotes don't surround values
> // mysql takes four times as long if single quotes don't surround values
> // postgresql is unaffected by single quotes around values
> // perhaps the databases are caching the statements besides the quoted
> values
> SELECT customer,name FROM customers WHERE customer='%d'; (%d = 1000 to
> 2000)
> ole: 4250
> odbc: 4250
> ado: 10200
> mysql: 400
> postgresql: 1100
> 10,000 different multi record selects (2 fields),
> 79,000 of 350,000 total records retrieved overall
> there is an index on orderno
> SELECT orderno,product FROM shipped WHERE orderno='%07d'; (%d = 0 to
> 10000)
> ole: 7766
> odbc: 7550
> ado: 51500
> mysql: 23400 (subsequent times 15,400)
> postgresql: 35600 (subsequent times 14,400)
> -Gary
>|||Interesting numbers - thanks.
I know this doesn't answer your question directly, but here goes.
We have client (GUI) and server (console) apps. The server apps
are heavy duty; memory, DB and processor intensive. We
originally developed in Borland and used their ADO Express for
both of the app types. Once the ADO properties were set
properly, it worked pretty good. We support SQL Server, Oracle
and DB2, all with the same code, except for a few SQL language
quirks.
A while back we decided to port the server apps to VC++ 8 in
preparation for doing 64 bit (we have over a hundred of these
server apps). We decided to use ODBC at this point. I created a
set of classes to encapsulate ODBC that had an almost identical
API to the ADO Express stuff. It was a fair amount of work but
made the port go quickly and makes ongoing development fast and
easy. You might want to consider doing something like this. A
brief example follows (C++). Compare it to raw ODBC.
// Set up the ODBC environment, read the Registry, etc.
VConnection conn( "MyDb", "MyUserId", "MyPassword" );
// Create the DB object and open a connection to the DB
VDatabase db( conn );
db.Open();
// Create a query object, set the SQL and a couple of properties
VQuery q( &db );
q.SQL->Text = "SELECT anInt FROM aTable WHERE aCol = :colval";
q.SetLockMode( DirtyRead );
q.CacheSize = 100;
// Set the parameter, open the query, read the results, close the
query
q.ParamByName( "colval" )->AsInteger = 1234;
q.Open();
if ( !q.Eof )
myVal = q.FieldByName( "anInt" )->AsInteger;
q.Close();
- Arnie|||Thanks, I had forgotten about the SQL Native Client . After doing a few
tests I do see a slight improvement of about 5% in both odbc and ole. Not
much of an improvement, but I'll take it.
-Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2Y%23iFWdGHA.1456@.TK2MSFTNGP04.phx.gbl...
> From the SQL Server 2005 Books Online:
> <Excerpt>
> The SQL Native Client OLE DB provider is a low-level, COM API that is used
> for accessing data. The SQL Native Client OLE DB provider is recommended
> for developing tools, utilities, or low-level components that need high
> performance. The SQL Native Client OLE DB provider is a native, high
> performance provider that accesses the SQL Server Tabular Data Stream
> (TDS) protocol directly.
> The SQL Native Client OLE DB provider exposes interfaces to consumers
> wanting access to data on one or more computers running an instance of SQL
> Server 2005 or earlier.
> The SQL Native Client OLE DB provider is an OLE DB version 2.0-compliant
> provider.
> </Excerpt>
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP

Faster ODBC connections wanted on W2K

Hi
I've been developing an app that use ODBC connections to connect to a database server (SqlServer, MySql or Oracle for the moment).
I noticed that SqlServer connection from a client was between 5 and 6 time faster with a server installed on W2K professional than on W2K Server.
These 2 servers have similar hardware and the same version of SqlServer installed.
The only ODBC parameters that changes on the client is the IP adress of the server.
Do you know what is the problem ?
Do you have some software solutions to get a faster SqlServer ?Need more information.
-Is the client running an a third box or one one or both of the sql-servers?
-Are the databases on either identical? same idnexes, etc?
-Is either sql-server set to use a different amt of ram? Check in the server properteis in em.
-Is either sql-server database set up on a different physical disk than the other? is it the same disk as the os or swapfile?
-Has either server been up and running for a while? If either server has already cached the requested data, it will be much faster. Try them both again after a fresh restart.
-Have you tried OLE-DB instead of ODBC? It tends to perform better.
-Are the two sql-servers on the same network or is one on another subnet or in some other location?

...|||Thank you for your answer.

You wrote :
-Is the client running an a third box or one one or both of the sql-servers?
The client is running on a third box.

-Are the databases on either identical? same idnexes, etc?
The databases are identicals (tables and indexes generated by the client itself)

-Is either sql-server set to use a different amt of ram? Check in the server properteis in em.
I think SqlServers installations are standards so they should use the same amount of memory but how can I check it ?

-Is either sql-server database set up on a different physical disk than the other? is it the same disk as the os or swapfile?
I don't understand your first question but It's the same HD than the OS (installed on C:\MSSQL7)

-Has either server been up and running for a while? If either server has already cached the requested data, it will be much faster. Try them both again after a fresh restart.
Even if the servers have just been started, the result is that it's slower on W2K Server.

-Have you tried OLE-DB instead of ODBC? It tends to perform better.
I have to use ODBC

-Are the two sql-servers on the same network or is one on another subnet or in some other location?
They are on the same network.

As you can see, there is no reason to have my app being slower with W2K server than with W2K pro but i'm not crazy, it's 5 or 6 time slower !
I'm sure that the problem is concerning W2K Server itself : a security option to disable, a service to stop ... I don't know ... I'm just a developer ... not a W2K expert. The problem occured on 2 different PC using W2K Server and I never encountered the problem on W2K pro.

On W2K Server, I check that background services was prefered to applications, I try to set the network card to use 'Full Duplex' but the problem is the same.

Thank you for your help.|||Hmmmm This is curious...

- Regarding the disk setup, my thought was that perhaps the data files were on a seperate physical disk from the OS/swapfile on one machine or the other, but that appears to not be the case.

- Regarding the memory settings, in Enterprise Manager, right click on the server and select properties, select the memory tab and check that the settings are identical for both systems.

- Have you checked to make sure that both ODBC connections are configured to use the same network library? You can do this by clicking on the 'client configuration' button during the DSN setup.

- I would run a profiler trace to watch the processes run and see if they are taking the same amount of time to process in SQL Server and if the problem might be with the network transmission. You can then capture the queries being used, paste them into Query Analyzer and get an execution plan on each sql-server to see if they're parsing the query the same way on each.

FAQ on SQL Server connect issues?

Seems like there are so many different variations and flavors of connect
issues, surely there is a FAQ or a KB that documents all (or most) of them?
There are KB articles that document specific areas and
common causes for certain errors. I can't think of anything
that documents all of these in one place as it would need to
cover a lot of territory - ODBC, OLE DB, JDBC, ADO, ADO.Net,
Linked Servers, Novell networks, Web applications, client
applications written in various languages, MDAC issues,
Server configurations, network issues, different SQL Server
versions, etc, etc.
There is a web cast on troubleshooting connectivity issues
if that's something along the lines of what you are looking
for:
Support WebCast: Microsoft SQL Server 2000: Troubleshooting
Connectivity
http://support.microsoft.com/?id=812817
-Sue
On Mon, 24 May 2004 22:06:31 -0500, "Earl"
<brikshoe<at>comcast<.>net> wrote:

>Seems like there are so many different variations and flavors of connect
>issues, surely there is a FAQ or a KB that documents all (or most) of them?
>
|||Earl,
'How to troubleshoot connectivity issues in SQL Server 2000'
http://support.microsoft.com/default.aspx?id=827422
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Earl comcast net>" <brikshoe<at.> wrote in message
news:ucX0DVgQEHA.2452@.TK2MSFTNGP11.phx.gbl...
> Seems like there are so many different variations and flavors of connect
> issues, surely there is a FAQ or a KB that documents all (or most) of
them?
>

FAQ on SQL Server connect issues?

Seems like there are so many different variations and flavors of connect
issues, surely there is a FAQ or a KB that documents all (or most) of them?There are KB articles that document specific areas and
common causes for certain errors. I can't think of anything
that documents all of these in one place as it would need to
cover a lot of territory - ODBC, OLE DB, JDBC, ADO, ADO.Net,
Linked Servers, Novell networks, Web applications, client
applications written in various languages, MDAC issues,
Server configurations, network issues, different SQL Server
versions, etc, etc.
There is a web cast on troubleshooting connectivity issues
if that's something along the lines of what you are looking
for:
Support WebCast: Microsoft SQL Server 2000: Troubleshooting
Connectivity
http://support.microsoft.com/?id=812817
-Sue
On Mon, 24 May 2004 22:06:31 -0500, "Earl"
<brikshoe<at>comcast<.>net> wrote:

>Seems like there are so many different variations and flavors of connect
>issues, surely there is a FAQ or a KB that documents all (or most) of them?
>|||Earl,
'How to troubleshoot connectivity issues in SQL Server 2000'
http://support.microsoft.com/default.aspx?id=827422
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Earl comcast net>" <brikshoe<at.> wrote in message
news:ucX0DVgQEHA.2452@.TK2MSFTNGP11.phx.gbl...
> Seems like there are so many different variations and flavors of connect
> issues, surely there is a FAQ or a KB that documents all (or most) of
them?
>

Failure to open SQL Server with given connect string

hello,

im working with VB 2005 and i have this error when triyng to connecting to the server database from my pocket pc application.

"Failure to open SQL Server with given connect string."

this is part of my code:

rdaOleDbConnectString = "Provider=sqloledb;Data Source=localhost;Persist Security Info=SSPI;User ID=id;Password=password;Initial Catalog=praia"

rdaUrl = "http://server/SqlMobile/sqlcesa30.dll"
localConnection = "Data Source=\My Documents\PraiaDB6.sdf"

If File.Exists("\My Documents\PraiaDB6.sdf") Then
File.Delete("\My Documents\PraiaDB6.sdf")
End If

Dim engine As New SqlCeEngine
engine.LocalConnectionString = localConnection
engine.CreateDatabase()
engine.Dispose()
MsgBox("DB Mobil empty created!")

'Init RDA Object
Dim rda As SqlCeRemoteDataAccess = Nothing

rda = New SqlCeRemoteDataAccess(rdaUrl, localConnection)
rda.InternetUrl = rdaUrl
rda.LocalConnectionString = localConnection
rda.Pull("Reserva", "select * from Reserva", rdaOleDbConnectString, RdaTrackOption.TrackingOn)
rda.Dispose()

the problem is in rda.Pull(...)
it can not connect to rdaOleDbConnectString

please help me
Thanks!

Jo?o
It is not likely that the datasource is "localhost", as this refers to the Pocket PC device, it should probably be the machine name or the ip address of the machine running SQL Server|||

Hi ErikEJ, thanks for the reply

i also tried the connectString with the machine name, like : Data Source=machinename; and i got the same error

also tried the ip adress of the server..

i dont understand whats supose the connect string look like...

anybody? thanks!

|||

Can you connect via Internet Explorer on the device to: http://server/SqlMobile/sqlcesa30.dll
If that is not possible, this may be the cause of the error.

|||

yes i can!

i read somewhere that when connecting via device (ppc) its need it to put ip adress instead server name.

i've found this link and im going to try now with this connecting string. ill post the result. thanks in advance for your help

http://www.connectionstrings.com/?carrier=sqlserver2005

Connect via an IP address

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Jo?o.

|||Same error...

:(
|||solution:

rdaOleDbConnectString = "Provider=SQLOLEDB.1;Data Source=machineName,1433;Persist Security Info=False;Initial Catalog=praiateste;User ID=teste;Password=password;"
|||hello,

i was olso working on RDA sync and with the same connection string but in C# and i'm having the same problem the error message i'm getting is:

Failure to open SQL Server with given connect string. [connect string = Provider=SQLOLEDB.1;Data Source=localhost; Persist Security Info= True; User ID=MobileUser;Password=password;Initial Catalog=MobileDemo; ]

i'd tryed out all the alternatives posted on the forum but i'm still getting the same error the intial connection string was:

@."Provider=SQLOLEDB.1;Data Source=localhost;Persist Security Info=True;User ID=MobileUser;Password=password;Initial Catalog=MobileDemo";

hope you can help me thanks|||rdaOleDbConnectString = "Provider=SQLOLEDB.1;Data Source=machineName,1433;Persist Security Info=False;Initial Catalog=praiateste;User ID=teste;Password=password;"

try using the actual server name and open the port 1433 at SQL server

Failure to open SQL Server with given connect string

hello,

im working with VB 2005 and i have this error when triyng to connecting to the server database from my pocket pc application.

"Failure to open SQL Server with given connect string."

this is part of my code:

rdaOleDbConnectString = "Provider=sqloledb;Data Source=localhost;Persist Security Info=SSPI;User ID=id;Password=password;Initial Catalog=praia"

rdaUrl = "http://server/SqlMobile/sqlcesa30.dll"
localConnection = "Data Source=\My Documents\PraiaDB6.sdf"

If File.Exists("\My Documents\PraiaDB6.sdf") Then
File.Delete("\My Documents\PraiaDB6.sdf")
End If

Dim engine As New SqlCeEngine
engine.LocalConnectionString = localConnection
engine.CreateDatabase()
engine.Dispose()
MsgBox("DB Mobil empty created!")

'Init RDA Object
Dim rda As SqlCeRemoteDataAccess = Nothing

rda = New SqlCeRemoteDataAccess(rdaUrl, localConnection)
rda.InternetUrl = rdaUrl
rda.LocalConnectionString = localConnection
rda.Pull("Reserva", "select * from Reserva", rdaOleDbConnectString, RdaTrackOption.TrackingOn)
rda.Dispose()

the problem is in rda.Pull(...)
it can not connect to rdaOleDbConnectString

please help me
Thanks!

Jo?o
It is not likely that the datasource is "localhost", as this refers to the Pocket PC device, it should probably be the machine name or the ip address of the machine running SQL Server|||

Hi ErikEJ, thanks for the reply

i also tried the connectString with the machine name, like : Data Source=machinename; and i got the same error

also tried the ip adress of the server..

i dont understand whats supose the connect string look like...

anybody? thanks!

|||

Can you connect via Internet Explorer on the device to: http://server/SqlMobile/sqlcesa30.dll
If that is not possible, this may be the cause of the error.

|||

yes i can!

i read somewhere that when connecting via device (ppc) its need it to put ip adress instead server name.

i've found this link and im going to try now with this connecting string. ill post the result. thanks in advance for your help

http://www.connectionstrings.com/?carrier=sqlserver2005

Connect via an IP address

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Jo?o.

|||Same error...

:(|||solution:

rdaOleDbConnectString = "Provider=SQLOLEDB.1;Data Source=machineName,1433;Persist Security Info=False;Initial Catalog=praiateste;User ID=teste;Password=password;"|||hello,

i was olso working on RDA sync and with the same connection string but in C# and i'm having the same problem the error message i'm getting is:

Failure to open SQL Server with given connect string. [connect string = Provider=SQLOLEDB.1;Data Source=localhost; Persist Security Info= True; User ID=MobileUser;Password=password;Initial Catalog=MobileDemo; ]

i'd tryed out all the alternatives posted on the forum but i'm still getting the same error the intial connection string was:

@."Provider=SQLOLEDB.1;Data Source=localhost;Persist Security Info=True;User ID=MobileUser;Password=password;Initial Catalog=MobileDemo";

hope you can help me thanks|||rdaOleDbConnectString = "Provider=SQLOLEDB.1;Data Source=machineName,1433;Persist Security Info=False;Initial Catalog=praiateste;User ID=teste;Password=password;"

try using the actual server name and open the port 1433 at 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 to connect error

I recently got a new computer that I use to connect to a SQL 2000 Server and
as soon as I started connecting to the server using QA and EM, the Air Force
base communications center called saying they were showing 1700 failure to
connect messages on ports, 1134,139, and 445. I connect just fine with no
errors and performance is just fine. I am assuming my new computer has some
protocol enabled that is sending broadcast messages to the server on these
ports that are not required and also on the bases hit list. Any Ideas?
OK,,,Found the culprit. I had Server State polling turned on. This is
located under Tools_Opotions of the Enterprise Manager. Seems my machine was
going out every second on port 1434 and the firewall was responding on port
445 saying no can do. I think the Slammer virus used 1434 so thats why it was
disabled. Also I had NetBios enabled in the TPC/IP properties.
"AkAlan" wrote:

> I recently got a new computer that I use to connect to a SQL 2000 Server and
> as soon as I started connecting to the server using QA and EM, the Air Force
> base communications center called saying they were showing 1700 failure to
> connect messages on ports, 1134,139, and 445. I connect just fine with no
> errors and performance is just fine. I am assuming my new computer has some
> protocol enabled that is sending broadcast messages to the server on these
> ports that are not required and also on the bases hit list. Any Ideas?

Failure to connect error

I recently got a new computer that I use to connect to a SQL 2000 Server and
as soon as I started connecting to the server using QA and EM, the Air Force
base communications center called saying they were showing 1700 failure to
connect messages on ports, 1134,139, and 445. I connect just fine with no
errors and performance is just fine. I am assuming my new computer has some
protocol enabled that is sending broadcast messages to the server on these
ports that are not required and also on the bases hit list. Any Ideas?OK,,,Found the culprit. I had Server State polling turned on. This is
located under Tools_Opotions of the Enterprise Manager. Seems my machine wa
s
going out every second on port 1434 and the firewall was responding on port
445 saying no can do. I think the Slammer virus used 1434 so thats why it wa
s
disabled. Also I had NetBios enabled in the TPC/IP properties.
"AkAlan" wrote:

> I recently got a new computer that I use to connect to a SQL 2000 Server a
nd
> as soon as I started connecting to the server using QA and EM, the Air For
ce
> base communications center called saying they were showing 1700 failure to
> connect messages on ports, 1134,139, and 445. I connect just fine with no
> errors and performance is just fine. I am assuming my new computer has som
e
> protocol enabled that is sending broadcast messages to the server on these
> ports that are not required and also on the bases hit list. Any Ideas?

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