Our customer has an external SQL server on which they want to make a
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdkHi
see this link
http://support.microsoft.com/kb/839279
try this first and let us know
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181027684.578393.61140@.o5g2000hsb.googlegroups.com...
> Our customer has an external SQL server on which they want to make a
> lookup into a table/view.
> I have setup a Linked server and retrieve data from the linked server.
> Example:
> EXEC sp_addlinkedserver
> @.server = 'NPRSQL',
> @.srvproduct = 'SQLServer OLEDB Provider',
> @.provider = 'SQLOLEDB',
> @.datasrc = 'SQL01'
> Go
> SELECT *
> FROM NPRSQL.DTS.dbo.ItemView
> However I can "create" the view, get the data, but when I try to save
> the view I get the following error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> operation could
> not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a
> distributed transaction.[Microsoft][ODBC SQL Server Driver][SQ
L
> Server]
> [OLE/DB
> provider returned message: New transaction cannot enlist in the
> specified transaction
> coordinator.]
> How can it be that I can get the data but not save the view?
> I have tried to make a similarly scenario on the Northwind database,
> create a view on the linked server and the "call" this view via a view
> on my local server with the same result.
>
> /refdk
>|||I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk|||Hi
Can you post the view statement
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181109437.668792.166410@.q75g2000hsh.googlegroups.com...
>I had already done most of the stuff in the kb-issue. However, I have
> now completed every step but the error persists.
> /refdk
>|||SELECT dbo.Hornsh=F8j$Item.No_ AS [No], dbo.Hornsh=F8j
$Item.Description, dbo.Hornsh=F8j$Item.[Unit Price], SUM(dbo.[Hornsh
=F8j
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornsh=F8j$Item INNER JOIN
dbo.[Hornsh=F8j$Item Ledger Entry] ON dbo.Hornsh=F8j
$Item.No_ =3D dbo.[Hornsh=F8j$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornsh=F8j$Item.No_, dbo.Hornsh=F8j$Item.Description,
dbo.Hornsh=F8j$Item.[Unit Price]|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)
Showing posts with label retrieve. Show all posts
Showing posts with label retrieve. Show all posts
Monday, March 19, 2012
Can't save view - linked server
Our customer has an external SQL server on which they want to make a
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdkHi
see this link
http://support.microsoft.com/kb/839279
try this first and let us know
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181027684.578393.61140@.o5g2000hsb.googlegroups.com...
> Our customer has an external SQL server on which they want to make a
> lookup into a table/view.
> I have setup a Linked server and retrieve data from the linked server.
> Example:
> EXEC sp_addlinkedserver
> @.server = 'NPRSQL',
> @.srvproduct = 'SQLServer OLEDB Provider',
> @.provider = 'SQLOLEDB',
> @.datasrc = 'SQL01'
> Go
> SELECT *
> FROM NPRSQL.DTS.dbo.ItemView
> However I can "create" the view, get the data, but when I try to save
> the view I get the following error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> operation could
> not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a
> distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
> Server]
> [OLE/DB
> provider returned message: New transaction cannot enlist in the
> specified transaction
> coordinator.]
> How can it be that I can get the data but not save the view?
> I have tried to make a similarly scenario on the Northwind database,
> create a view on the linked server and the "call" this view via a view
> on my local server with the same result.
>
> /refdk
>|||I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk|||Hi
Can you post the view statement
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181109437.668792.166410@.q75g2000hsh.googlegroups.com...
>I had already done most of the stuff in the kb-issue. However, I have
> now completed every step but the error persists.
> /refdk
>|||SELECT dbo.Hornsh=F8j$Item.No_ AS [No], dbo.Hornsh=F8j
$Item.Description, dbo.Hornsh=F8j$Item.[Unit Price], SUM(dbo.[Hornsh=F8j
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornsh=F8j$Item INNER JOIN
dbo.[Hornsh=F8j$Item Ledger Entry] ON dbo.Hornsh=F8j
$Item.No_ =3D dbo.[Hornsh=F8j$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornsh=F8j$Item.No_, dbo.Hornsh=F8j$Item.Description,
dbo.Hornsh=F8j$Item.[Unit Price]|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdkHi
see this link
http://support.microsoft.com/kb/839279
try this first and let us know
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181027684.578393.61140@.o5g2000hsb.googlegroups.com...
> Our customer has an external SQL server on which they want to make a
> lookup into a table/view.
> I have setup a Linked server and retrieve data from the linked server.
> Example:
> EXEC sp_addlinkedserver
> @.server = 'NPRSQL',
> @.srvproduct = 'SQLServer OLEDB Provider',
> @.provider = 'SQLOLEDB',
> @.datasrc = 'SQL01'
> Go
> SELECT *
> FROM NPRSQL.DTS.dbo.ItemView
> However I can "create" the view, get the data, but when I try to save
> the view I get the following error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> operation could
> not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a
> distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
> Server]
> [OLE/DB
> provider returned message: New transaction cannot enlist in the
> specified transaction
> coordinator.]
> How can it be that I can get the data but not save the view?
> I have tried to make a similarly scenario on the Northwind database,
> create a view on the linked server and the "call" this view via a view
> on my local server with the same result.
>
> /refdk
>|||I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk|||Hi
Can you post the view statement
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181109437.668792.166410@.q75g2000hsh.googlegroups.com...
>I had already done most of the stuff in the kb-issue. However, I have
> now completed every step but the error persists.
> /refdk
>|||SELECT dbo.Hornsh=F8j$Item.No_ AS [No], dbo.Hornsh=F8j
$Item.Description, dbo.Hornsh=F8j$Item.[Unit Price], SUM(dbo.[Hornsh=F8j
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornsh=F8j$Item INNER JOIN
dbo.[Hornsh=F8j$Item Ledger Entry] ON dbo.Hornsh=F8j
$Item.No_ =3D dbo.[Hornsh=F8j$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornsh=F8j$Item.No_, dbo.Hornsh=F8j$Item.Description,
dbo.Hornsh=F8j$Item.[Unit Price]|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)
Can't save view - linked server
Our customer has an external SQL server on which they want to make a
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdk
I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk
|||SELECT dbo.Hornshj$Item.No_ AS [No], dbo.Hornshj
$Item.Description, dbo.Hornshj$Item.[Unit Price], SUM(dbo.[Hornshj
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornshj$Item INNER JOIN
dbo.[Hornshj$Item Ledger Entry] ON dbo.Hornshj
$Item.No_ = dbo.[Hornshj$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornshj$Item.No_, dbo.Hornshj$Item.Description,
dbo.Hornshj$Item.[Unit Price]
|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdk
I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk
|||SELECT dbo.Hornshj$Item.No_ AS [No], dbo.Hornshj
$Item.Description, dbo.Hornshj$Item.[Unit Price], SUM(dbo.[Hornshj
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornshj$Item INNER JOIN
dbo.[Hornshj$Item Ledger Entry] ON dbo.Hornshj
$Item.No_ = dbo.[Hornshj$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornshj$Item.No_, dbo.Hornshj$Item.Description,
dbo.Hornshj$Item.[Unit Price]
|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)
Sunday, March 11, 2012
Can't retrieve second resultset with jdbc sql server 2005 driver
Hi All,
My java application executes one stored procedure that returns two resultsets and then deal with data. The code actually is very simple
Statement stmt = con.createStatement(); con is database connection
ResultSet rs = stmt.getResultSet();
//Do something
rs.close();
rs = stmt.getResultSet();
//Do something
Then I got the error: The result set is closed.
The code worked well with jdbc sql server 2000 driver.
Thanks for your help.
Eric
Hello lxiao,
The syntax for getting the second result set is
if(stmt.getMoreResults())
stmt.getResultSet();
Keep in mind that the getMoreResults() methods takes multiple arguments which controls how visited result sets should persist. Those flags are:
CLOSE_CURRENT_RESULT
CLOSE_ALL_RESULTS
You can find more information about using multiple result sets objects at the following site http://msdn2.microsoft.com/en-us/library/ms378758.aspx
Regards,
Jaaved Mohammed
Can't retrieve second resultset with jdbc sql server 2005 driver
Hi All,
My java application executes one stored procedure that returns two resultsets and then deal with data. The code actually is very simple
Statement stmt = con.createStatement(); con is database connection
ResultSet rs = stmt.getResultSet();
//Do something
rs.close();
rs = stmt.getResultSet();
//Do something
Then I got the error: The result set is closed.
The code worked well with jdbc sql server 2000 driver.
Thanks for your help.
Eric
Hello lxiao,
The syntax for getting the second result set is
if(stmt.getMoreResults())
stmt.getResultSet();
Keep in mind that the getMoreResults() methods takes multiple arguments which controls how visited result sets should persist. Those flags are:
CLOSE_CURRENT_RESULT
CLOSE_ALL_RESULTS
You can find more information about using multiple result sets objects at the following site http://msdn2.microsoft.com/en-us/library/ms378758.aspx
Regards,
Jaaved Mohammed
can't retrieve data using app
Hi,
I have an application that can only sometimes retrieve data via the
application for a certain process. This option has been working correctly for
a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
against the tables in question a few weeks back and the problem disappeared.
However, the problem has now reoccured.
Does anyone have any ideas regarding the nature of the problem and how it
can be resolved?
Extremely difficult to give suggestions without more information even hands-on. Perhaps the
application has a time-out? Did you get an error message or incorrect information? Perhaps there is
a time-out and no error is presented back to the user?
Another thing you can do is to capture the SQL statement submitted by the application with Profiler
and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
problem is in the application and you need to talk to the app vendor/developer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> Hi,
> I have an application that can only sometimes retrieve data via the
> application for a certain process. This option has been working correctly for
> a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> against the tables in question a few weeks back and the problem disappeared.
> However, the problem has now reoccured.
> Does anyone have any ideas regarding the nature of the problem and how it
> can be resolved?
>
|||The error doesn't occur using sql analyzer.
I'm not sure that this would be a time out issue, as other reports
retrieving a lot more data are working correctly.
Please find the error below:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at expert.ExpertProc.PhysicalDataRead()
"Tibor Karaszi" wrote:
> Extremely difficult to give suggestions without more information even hands-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect information? Perhaps there is
> a time-out and no error is presented back to the user?
> Another thing you can do is to capture the SQL statement submitted by the application with Profiler
> and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
> problem is in the application and you need to talk to the app vendor/developer.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>
|||The error message clearly states:
"The timeout period elapsed prior to completion of the operation or the server is not responding."
You have to investigate why it takes a long time sometimes and sometimes not. It can be blocking
issues (use Current Activity, sp_who, sp_who2, sp_lock etc to investigate), or it might be different
execution plans (use Profiler to catch the execution plans so you can compare).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
|||The error message clearly indicates a timeout exception. The default
SqlCommand timeout is 30 seconds but this can be overridden by the
application. The default Query Analyzer query timeout is unlimited so,
assuming the query runs over 30 seconds, that explains why you don't get the
error in Query Analyzer.
A number of factors can contribute to a timeout. These include sub-optimal
query plan, blocking and server workload. If you've ruled out blocking and
an overtaxed server, I suggest you examine the plan of the problem query.
Perhaps additional indexes may help or existing indexes need to be rebuilt
using DBCC DBREINDEX. If you still have the problem after tuning, you'll
need to increase the application command timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
I have an application that can only sometimes retrieve data via the
application for a certain process. This option has been working correctly for
a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
against the tables in question a few weeks back and the problem disappeared.
However, the problem has now reoccured.
Does anyone have any ideas regarding the nature of the problem and how it
can be resolved?
Extremely difficult to give suggestions without more information even hands-on. Perhaps the
application has a time-out? Did you get an error message or incorrect information? Perhaps there is
a time-out and no error is presented back to the user?
Another thing you can do is to capture the SQL statement submitted by the application with Profiler
and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
problem is in the application and you need to talk to the app vendor/developer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> Hi,
> I have an application that can only sometimes retrieve data via the
> application for a certain process. This option has been working correctly for
> a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> against the tables in question a few weeks back and the problem disappeared.
> However, the problem has now reoccured.
> Does anyone have any ideas regarding the nature of the problem and how it
> can be resolved?
>
|||The error doesn't occur using sql analyzer.
I'm not sure that this would be a time out issue, as other reports
retrieving a lot more data are working correctly.
Please find the error below:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at expert.ExpertProc.PhysicalDataRead()
"Tibor Karaszi" wrote:
> Extremely difficult to give suggestions without more information even hands-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect information? Perhaps there is
> a time-out and no error is presented back to the user?
> Another thing you can do is to capture the SQL statement submitted by the application with Profiler
> and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
> problem is in the application and you need to talk to the app vendor/developer.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>
|||The error message clearly states:
"The timeout period elapsed prior to completion of the operation or the server is not responding."
You have to investigate why it takes a long time sometimes and sometimes not. It can be blocking
issues (use Current Activity, sp_who, sp_who2, sp_lock etc to investigate), or it might be different
execution plans (use Profiler to catch the execution plans so you can compare).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
|||The error message clearly indicates a timeout exception. The default
SqlCommand timeout is 30 seconds but this can be overridden by the
application. The default Query Analyzer query timeout is unlimited so,
assuming the query runs over 30 seconds, that explains why you don't get the
error in Query Analyzer.
A number of factors can contribute to a timeout. These include sub-optimal
query plan, blocking and server workload. If you've ruled out blocking and
an overtaxed server, I suggest you examine the plan of the problem query.
Perhaps additional indexes may help or existing indexes need to be rebuilt
using DBCC DBREINDEX. If you still have the problem after tuning, you'll
need to increase the application command timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
Thursday, March 8, 2012
can't retrieve data using app
Hi,
I have an application that can only sometimes retrieve data via the
application for a certain process. This option has been working correctly fo
r
a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
against the tables in question a few weeks back and the problem disappeared.
However, the problem has now reoccured.
Does anyone have any ideas regarding the nature of the problem and how it
can be resolved?Extremely difficult to give suggestions without more information even hands-
on. Perhaps the
application has a time-out? Did you get an error message or incorrect inform
ation? Perhaps there is
a time-out and no error is presented back to the user?
Another thing you can do is to capture the SQL statement submitted by the ap
plication with Profiler
and submit that same query using Query Analyzer. If the query work fine from
Query Analyzer the
problem is in the application and you need to talk to the app vendor/develop
er.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> Hi,
> I have an application that can only sometimes retrieve data via the
> application for a certain process. This option has been working correctly
for
> a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> against the tables in question a few weeks back and the problem disappeare
d.
> However, the problem has now reoccured.
> Does anyone have any ideas regarding the nature of the problem and how it
> can be resolved?
>|||The error doesn't occur using sql analyzer.
I'm not sure that this would be a time out issue, as other reports
retrieving a lot more data are working correctly.
Please find the error below:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding
.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at expert.ExpertProc.PhysicalDataRead()
"Tibor Karaszi" wrote:
> Extremely difficult to give suggestions without more information even hand
s-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect info
rmation? Perhaps there is
> a time-out and no error is presented back to the user?
> Another thing you can do is to capture the SQL statement submitted by the
application with Profiler
> and submit that same query using Query Analyzer. If the query work fine fr
om Query Analyzer the
> problem is in the application and you need to talk to the app vendor/devel
oper.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>|||The error message clearly states:
"The timeout period elapsed prior to completion of the operation or the ser
ver is not responding."
You have to investigate why it takes a long time sometimes and sometimes not
. It can be blocking
issues (use Current Activity, sp_who, sp_who2, sp_lock etc to investigate),
or it might be different
execution plans (use Profiler to catch the execution plans so you can compar
e).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not respondi
ng.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavi
or)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comm
andBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>|||The error message clearly indicates a timeout exception. The default
SqlCommand timeout is 30 seconds but this can be overridden by the
application. The default Query Analyzer query timeout is unlimited so,
assuming the query runs over 30 seconds, that explains why you don't get the
error in Query Analyzer.
A number of factors can contribute to a timeout. These include sub-optimal
query plan, blocking and server workload. If you've ruled out blocking and
an overtaxed server, I suggest you examine the plan of the problem query.
Perhaps additional indexes may help or existing indexes need to be rebuilt
using DBCC DBREINDEX. If you still have the problem after tuning, you'll
need to increase the application command timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comm
andBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>
I have an application that can only sometimes retrieve data via the
application for a certain process. This option has been working correctly fo
r
a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
against the tables in question a few weeks back and the problem disappeared.
However, the problem has now reoccured.
Does anyone have any ideas regarding the nature of the problem and how it
can be resolved?Extremely difficult to give suggestions without more information even hands-
on. Perhaps the
application has a time-out? Did you get an error message or incorrect inform
ation? Perhaps there is
a time-out and no error is presented back to the user?
Another thing you can do is to capture the SQL statement submitted by the ap
plication with Profiler
and submit that same query using Query Analyzer. If the query work fine from
Query Analyzer the
problem is in the application and you need to talk to the app vendor/develop
er.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> Hi,
> I have an application that can only sometimes retrieve data via the
> application for a certain process. This option has been working correctly
for
> a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> against the tables in question a few weeks back and the problem disappeare
d.
> However, the problem has now reoccured.
> Does anyone have any ideas regarding the nature of the problem and how it
> can be resolved?
>|||The error doesn't occur using sql analyzer.
I'm not sure that this would be a time out issue, as other reports
retrieving a lot more data are working correctly.
Please find the error below:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding
.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at expert.ExpertProc.PhysicalDataRead()
"Tibor Karaszi" wrote:
> Extremely difficult to give suggestions without more information even hand
s-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect info
rmation? Perhaps there is
> a time-out and no error is presented back to the user?
> Another thing you can do is to capture the SQL statement submitted by the
application with Profiler
> and submit that same query using Query Analyzer. If the query work fine fr
om Query Analyzer the
> problem is in the application and you need to talk to the app vendor/devel
oper.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>|||The error message clearly states:
"The timeout period elapsed prior to completion of the operation or the ser
ver is not responding."
You have to investigate why it takes a long time sometimes and sometimes not
. It can be blocking
issues (use Current Activity, sp_who, sp_who2, sp_lock etc to investigate),
or it might be different
execution plans (use Profiler to catch the execution plans so you can compar
e).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not respondi
ng.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavi
or)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comm
andBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>|||The error message clearly indicates a timeout exception. The default
SqlCommand timeout is 30 seconds but this can be overridden by the
application. The default Query Analyzer query timeout is unlimited so,
assuming the query runs over 30 seconds, that explains why you don't get the
error in Query Analyzer.
A number of factors can contribute to a timeout. These include sub-optimal
query plan, blocking and server workload. If you've ruled out blocking and
an overtaxed server, I suggest you examine the plan of the problem query.
Perhaps additional indexes may help or existing indexes need to be rebuilt
using DBCC DBREINDEX. If you still have the problem after tuning, you'll
need to increase the application command timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comm
andBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>
can't retrieve data using app
Hi,
I have an application that can only sometimes retrieve data via the
application for a certain process. This option has been working correctly for
a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
against the tables in question a few weeks back and the problem disappeared.
However, the problem has now reoccured.
Does anyone have any ideas regarding the nature of the problem and how it
can be resolved?Extremely difficult to give suggestions without more information even hands-on. Perhaps the
application has a time-out? Did you get an error message or incorrect information? Perhaps there is
a time-out and no error is presented back to the user?
Another thing you can do is to capture the SQL statement submitted by the application with Profiler
and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
problem is in the application and you need to talk to the app vendor/developer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> Hi,
> I have an application that can only sometimes retrieve data via the
> application for a certain process. This option has been working correctly for
> a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> against the tables in question a few weeks back and the problem disappeared.
> However, the problem has now reoccured.
> Does anyone have any ideas regarding the nature of the problem and how it
> can be resolved?
>|||The error doesn't occur using sql analyzer.
I'm not sure that this would be a time out issue, as other reports
retrieving a lot more data are working correctly.
Please find the error below:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at expert.ExpertProc.PhysicalDataRead()
"Tibor Karaszi" wrote:
> Extremely difficult to give suggestions without more information even hands-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect information? Perhaps there is
> a time-out and no error is presented back to the user?
> Another thing you can do is to capture the SQL statement submitted by the application with Profiler
> and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
> problem is in the application and you need to talk to the app vendor/developer.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> > Hi,
> >
> > I have an application that can only sometimes retrieve data via the
> > application for a certain process. This option has been working correctly for
> > a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> > against the tables in question a few weeks back and the problem disappeared.
> > However, the problem has now reoccured.
> >
> > Does anyone have any ideas regarding the nature of the problem and how it
> > can be resolved?
> >
>|||The error message clearly states:
"The timeout period elapsed prior to completion of the operation or the server is not responding."
You have to investigate why it takes a long time sometimes and sometimes not. It can be blocking
issues (use Current Activity, sp_who, sp_who2, sp_lock etc to investigate), or it might be different
execution plans (use Profiler to catch the execution plans so you can compare).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>> Extremely difficult to give suggestions without more information even hands-on. Perhaps the
>> application has a time-out? Did you get an error message or incorrect information? Perhaps there
>> is
>> a time-out and no error is presented back to the user?
>> Another thing you can do is to capture the SQL statement submitted by the application with
>> Profiler
>> and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
>> problem is in the application and you need to talk to the app vendor/developer.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "gracie" <gracie@.discussions.microsoft.com> wrote in message
>> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>> > Hi,
>> >
>> > I have an application that can only sometimes retrieve data via the
>> > application for a certain process. This option has been working correctly for
>> > a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
>> > against the tables in question a few weeks back and the problem disappeared.
>> > However, the problem has now reoccured.
>> >
>> > Does anyone have any ideas regarding the nature of the problem and how it
>> > can be resolved?
>> >
>>|||The error message clearly indicates a timeout exception. The default
SqlCommand timeout is 30 seconds but this can be overridden by the
application. The default Query Analyzer query timeout is unlimited so,
assuming the query runs over 30 seconds, that explains why you don't get the
error in Query Analyzer.
A number of factors can contribute to a timeout. These include sub-optimal
query plan, blocking and server workload. If you've ruled out blocking and
an overtaxed server, I suggest you examine the plan of the problem query.
Perhaps additional indexes may help or existing indexes need to be rebuilt
using DBCC DBREINDEX. If you still have the problem after tuning, you'll
need to increase the application command timeout.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>> Extremely difficult to give suggestions without more information even
>> hands-on. Perhaps the
>> application has a time-out? Did you get an error message or incorrect
>> information? Perhaps there is
>> a time-out and no error is presented back to the user?
>> Another thing you can do is to capture the SQL statement submitted by the
>> application with Profiler
>> and submit that same query using Query Analyzer. If the query work fine
>> from Query Analyzer the
>> problem is in the application and you need to talk to the app
>> vendor/developer.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "gracie" <gracie@.discussions.microsoft.com> wrote in message
>> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>> > Hi,
>> >
>> > I have an application that can only sometimes retrieve data via the
>> > application for a certain process. This option has been working
>> > correctly for
>> > a few years and no changes have been made to it. I ran a DBCC
>> > INDEXDEFRAG
>> > against the tables in question a few weeks back and the problem
>> > disappeared.
>> > However, the problem has now reoccured.
>> >
>> > Does anyone have any ideas regarding the nature of the problem and how
>> > it
>> > can be resolved?
>> >
>>
I have an application that can only sometimes retrieve data via the
application for a certain process. This option has been working correctly for
a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
against the tables in question a few weeks back and the problem disappeared.
However, the problem has now reoccured.
Does anyone have any ideas regarding the nature of the problem and how it
can be resolved?Extremely difficult to give suggestions without more information even hands-on. Perhaps the
application has a time-out? Did you get an error message or incorrect information? Perhaps there is
a time-out and no error is presented back to the user?
Another thing you can do is to capture the SQL statement submitted by the application with Profiler
and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
problem is in the application and you need to talk to the app vendor/developer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> Hi,
> I have an application that can only sometimes retrieve data via the
> application for a certain process. This option has been working correctly for
> a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> against the tables in question a few weeks back and the problem disappeared.
> However, the problem has now reoccured.
> Does anyone have any ideas regarding the nature of the problem and how it
> can be resolved?
>|||The error doesn't occur using sql analyzer.
I'm not sure that this would be a time out issue, as other reports
retrieving a lot more data are working correctly.
Please find the error below:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at expert.ExpertProc.PhysicalDataRead()
"Tibor Karaszi" wrote:
> Extremely difficult to give suggestions without more information even hands-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect information? Perhaps there is
> a time-out and no error is presented back to the user?
> Another thing you can do is to capture the SQL statement submitted by the application with Profiler
> and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
> problem is in the application and you need to talk to the app vendor/developer.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> > Hi,
> >
> > I have an application that can only sometimes retrieve data via the
> > application for a certain process. This option has been working correctly for
> > a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> > against the tables in question a few weeks back and the problem disappeared.
> > However, the problem has now reoccured.
> >
> > Does anyone have any ideas regarding the nature of the problem and how it
> > can be resolved?
> >
>|||The error message clearly states:
"The timeout period elapsed prior to completion of the operation or the server is not responding."
You have to investigate why it takes a long time sometimes and sometimes not. It can be blocking
issues (use Current Activity, sp_who, sp_who2, sp_lock etc to investigate), or it might be different
execution plans (use Profiler to catch the execution plans so you can compare).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>> Extremely difficult to give suggestions without more information even hands-on. Perhaps the
>> application has a time-out? Did you get an error message or incorrect information? Perhaps there
>> is
>> a time-out and no error is presented back to the user?
>> Another thing you can do is to capture the SQL statement submitted by the application with
>> Profiler
>> and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
>> problem is in the application and you need to talk to the app vendor/developer.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "gracie" <gracie@.discussions.microsoft.com> wrote in message
>> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>> > Hi,
>> >
>> > I have an application that can only sometimes retrieve data via the
>> > application for a certain process. This option has been working correctly for
>> > a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
>> > against the tables in question a few weeks back and the problem disappeared.
>> > However, the problem has now reoccured.
>> >
>> > Does anyone have any ideas regarding the nature of the problem and how it
>> > can be resolved?
>> >
>>|||The error message clearly indicates a timeout exception. The default
SqlCommand timeout is 30 seconds but this can be overridden by the
application. The default Query Analyzer query timeout is unlimited so,
assuming the query runs over 30 seconds, that explains why you don't get the
error in Query Analyzer.
A number of factors can contribute to a timeout. These include sub-optimal
query plan, blocking and server workload. If you've ruled out blocking and
an overtaxed server, I suggest you examine the plan of the problem query.
Perhaps additional indexes may help or existing indexes need to be rebuilt
using DBCC DBREINDEX. If you still have the problem after tuning, you'll
need to increase the application command timeout.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>> Extremely difficult to give suggestions without more information even
>> hands-on. Perhaps the
>> application has a time-out? Did you get an error message or incorrect
>> information? Perhaps there is
>> a time-out and no error is presented back to the user?
>> Another thing you can do is to capture the SQL statement submitted by the
>> application with Profiler
>> and submit that same query using Query Analyzer. If the query work fine
>> from Query Analyzer the
>> problem is in the application and you need to talk to the app
>> vendor/developer.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "gracie" <gracie@.discussions.microsoft.com> wrote in message
>> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>> > Hi,
>> >
>> > I have an application that can only sometimes retrieve data via the
>> > application for a certain process. This option has been working
>> > correctly for
>> > a few years and no changes have been made to it. I ran a DBCC
>> > INDEXDEFRAG
>> > against the tables in question a few weeks back and the problem
>> > disappeared.
>> > However, the problem has now reoccured.
>> >
>> > Does anyone have any ideas regarding the nature of the problem and how
>> > it
>> > can be resolved?
>> >
>>
cant retrieve connection string from web.config - ASP.NET 2.0
All,
I don't know what is the problem, i am not able to retrieve connection string from web.config in my code behind page
I used every solution provided by people in differents forums.
can anybody help me with this.
I usedWebConfigurationManager.OpenWebConfiguration("/TimeSheetApplication").ConnectionStrings("TimeSheetConnectionString").ConnectionString;
WebConfigurationManager.ConnectionStrings("TConnectionString").ConnectionString;
Here is a reference on MSDN
http://msdn2.microsoft.com/en-us/library/system.web.configuration.webconfigurationmanager(VS.80).aspx
or you can post your web.config with above code and let me look
|||My code
Web.Config -
<connectionStrings><addname="TConnectionString"connectionString="Data Source=Source name"
providerName="System.Data.SqlClient" /></connectionStrings>
Code Behind -
string query ="Select * from table"
string myConnectionString = ??SqlConnection myConnection =newSqlConnection(myConnectionString);
SqlDataAdapter ad =newSqlDataAdapter(query, myConnection);DataSet ds =newDataSet();
ad.Fill(ds,"table");GridView1.DataSource = ds;
GridView1.DataBind();
? - When i usedConnectionStringSettingsCollection connectionStrings =WebConfigurationManager.ConnectionStringsas ConnectionStringSettingsCollection;
connectionStrings["TConnectionString"].Name
it gives error
Cannot apply indexing with [] to an expression of type 'ConnectionStringSettingsCollection'
The type 'System.Configuration.ConnectionStringSettingsCollection' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.
|||
You can access this way
string connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["TConnectionString"].ToString();
|||Thanks Farooq...
However i used the same code earlier and i am getting the error
Cannot apply indexing with [] to an expression of type 'ConnectionStringSettingsCollection'
The type 'System.Configuration.ConnectionStringSettingsCollection' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
|||make sure you have this element in web.config:
<system.web>
<compilation>
<assemblies>
<add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
</assemblies>
</compilation>
</system.web>
Subscribe to:
Posts (Atom)