link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/default...b;en-us;280106
http://support.microsoft.com/default...b;en-us;814398
Regards
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
sql

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
--
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:
> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
http://support.microsoft.com/default.aspx?scid=kb;en-us;814398
Regards
"Mangesh Deshpande" wrote:
> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error mess
age
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any error
s.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error me
ssage
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/defaul...kb;en-us;280106
http://support.microsoft.com/defaul...kb;en-us;814398
Regards
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any error
s.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error me
ssage
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'

link to mysql

I tired to create a link 'MYSQL' in a SQL Server using the following code :

EXEC sp_addlinkedserver
@.server = mysql,
@.provider = 'MSDASQL',
@.provstr = 'DRIVER={MySQL ODBC 3.51 Driver};SERVER=MySQLServer;UID=root;PWD=;'

NB: I have a DB by the name of : MYSQL

There is no errors during creation ...

The link creation is OK but I am not able to see tables from SQL Server

Any Idea ?Linked server login mappings can be added using sp_addlinkedsrvlogin and refer to books online for more information.|||No it ok for the link creation but we have the following error :

Msg 7314, Niveau 16, tat 1, Ligne 1

when execution :

select * from mysql...host

any idea ?|||When setting up the linked server you need to map your local login to appropirate remote/destination server's login.|||The mapping is OK ? really, I don't inerstant the pb ?
Did you try to to this with a MYSQL DB ?|||You shoudl use OPENQUERY method to access the data.

However, the openquery() method ALWAYS worked while the suggested method of SERVER.CATALOG.OWNER.TABLE will have some issues.

Ensure you've MyODBC installed properly on SQL server.
I don't have any issues in using MySQL as LS.|||Could you please give us an example (with MySQL Server)

Could you tell us what is :

SERVER under MySQL
CATALOG under MySQL
OWNER under MySQL

Could you give us a query example with a table from the default Mysql DB.

Indeed, I successed to have a resultat when using OPENQUERY :

But it doesn't work every time (it depends of the row nb in the table I think).

So it is OK for :

SELECT * FROM OPENQUERY (mysql,'SELECT * FROM db where 1=2')

But not for :

SELECT * FROM OPENQUERY (mysql,'SELECT * FROM db)

Error : Msg 7347

Help !!!!!!!

Link Server to SQL2000 from SQL2005 64bit

Hello All,
I setup a link to my SQL2000, everything seem fine except when I try to
query the SQL 2000 database. I get the following error:
OLE DB provider "SQLNCLI" for linked server "ces012" returned message
"Unspecified error".
OLE DB provider "SQLNCLI" for linked server "ces012" returned message "The
stored procedure required to complete this operation could not be found on
the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
"SQLNCLI" for linked server "ces012". The provider supports the interface,
but returns a failure code when it is used.
I search on the web and it tells me to run the instcat.sql on the SQL2000
server.
Do you see any problem after I run this script? Are there anyway I can back
out?
Thanks.
It should be no problem, I would take a backup of the Master database first.
You can run the script if you have SP3 or SP4 applied on SQL 2000. You can
restore your master if you want to back out.
You can refer to this article: http://support.microsoft.com/kb/906954
Ayad Shammout
"David" <David@.discussions.microsoft.com> wrote in message
news:DDC6A798-9099-47FB-A2FC-EEF7ABF1D1B4@.microsoft.com...
> Hello All,
> I setup a link to my SQL2000, everything seem fine except when I try to
> query the SQL 2000 database. I get the following error:
> OLE DB provider "SQLNCLI" for linked server "ces012" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "ces012" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "ces012". The provider supports the interface,
> but returns a failure code when it is used.
> I search on the web and it tells me to run the instcat.sql on the SQL2000
> server.
> Do you see any problem after I run this script? Are there anyway I can
> back
> out?
> Thanks.
>
sql

Link Server to SQL2000 from SQL2005 64bit

Hello All,
I setup a link to my SQL2000, everything seem fine except when I try to
query the SQL 2000 database. I get the following error:
OLE DB provider "SQLNCLI" for linked server "ces012" returned message
"Unspecified error".
OLE DB provider "SQLNCLI" for linked server "ces012" returned message "The
stored procedure required to complete this operation could not be found on
the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
"SQLNCLI" for linked server "ces012". The provider supports the interface,
but returns a failure code when it is used.
I search on the web and it tells me to run the instcat.sql on the SQL2000
server.
Do you see any problem after I run this script? Are there anyway I can back
out?
Thanks.It should be no problem, I would take a backup of the Master database first.
You can run the script if you have SP3 or SP4 applied on SQL 2000. You can
restore your master if you want to back out.
You can refer to this article: http://support.microsoft.com/kb/906954
Ayad Shammout
"David" <David@.discussions.microsoft.com> wrote in message
news:DDC6A798-9099-47FB-A2FC-EEF7ABF1D1B4@.microsoft.com...
> Hello All,
> I setup a link to my SQL2000, everything seem fine except when I try to
> query the SQL 2000 database. I get the following error:
> OLE DB provider "SQLNCLI" for linked server "ces012" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "ces012" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "ces012". The provider supports the interface,
> but returns a failure code when it is used.
> I search on the web and it tells me to run the instcat.sql on the SQL2000
> server.
> Do you see any problem after I run this script? Are there anyway I can
> back
> out?
> Thanks.
>

Link server problrm connecting to Oracle

I get the following error after trying to do update to an Oracle 9i table
from SQL Server 2000. :
The operation could not be performed because the OLE DB provider 'MSDAORA'
was unable to begin a distributed transaction.
Any ideas to solve this?
Check the following article:
INF: Troubleshooting Linked Server Error 7391
http://support.microsoft.com/?id=306212
-Sue
On Wed, 23 Mar 2005 12:44:06 GMT, "Mori"
<obibiman.nospam@.yahoo.com> wrote:

>I get the following error after trying to do update to an Oracle 9i table
>from SQL Server 2000. :
>The operation could not be performed because the OLE DB provider 'MSDAORA'
>was unable to begin a distributed transaction.
>Any ideas to solve this?
>

Link server problrm connecting to Oracle

I get the following error after trying to do update to an Oracle 9i table
from SQL Server 2000. :
The operation could not be performed because the OLE DB provider 'MSDAORA'
was unable to begin a distributed transaction.
Any ideas to solve this?Check the following article:
INF: Troubleshooting Linked Server Error 7391
http://support.microsoft.com/?id=306212
-Sue
On Wed, 23 Mar 2005 12:44:06 GMT, "Mori"
<obibiman.nospam@.yahoo.com> wrote:

>I get the following error after trying to do update to an Oracle 9i table
>from SQL Server 2000. :
>The operation could not be performed because the OLE DB provider 'MSDAORA'
>was unable to begin a distributed transaction.
>Any ideas to solve this?
>sql

link from sql2005 (64-bit) to sql 7 (32-bit).

Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
I get the following error:
OLE DB provider "SQLNCLI" for linked server "sql7" returned message
"Unspecified error".
OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
stored procedure required to complete this operation could not be found on
the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
"SQLNCLI" for linked server "sql7". The provider supports the interface, but
returns a failure code when it is used.
How can I get these servers linked?
Regards,I just ran into a similar problem, but from 64 bit SQL 2005 trying to link
to 32 bit SQL 2000. What I did on the 2000 server was run the instcat.sql
from the latest service pack installation I did on the 2000 server. In my
case it was SP3a. There are instructions to change the last insert statement
to the table to reflect the version of SQL Server (select @.@.version) that
you are running. In my case it was .977. That fixed my problems.
Jackie
"José" <josedc@.starmedia.com> wrote in message
news:OE2iT1GlGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "sql7". The provider supports the interface,
> but
> returns a failure code when it is used.
> How can I get these servers linked?
> Regards,
>sql

Line 1: Incorrect Syntax Near.......(is this a bug?)

Hi,

Uses: Sql server 2000, asp.net 1.1;

I've the following Stored Procedure:

<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@.FromDate DATETIME,
@.ToDate DATETIME,
@.PortalID INT = 1,
@.UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @.FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @.ToDate, 102)
AND PABX_PortalUsers.uID = @.UserID
AND PABX_PortalUsers.PortalID = @.PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>

However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:

Line 1: Incorrect Syntax Near ......

When I check the sql profiler the stored procedure is been executed in this way:

exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.

Could anyone help me out on this?

Thanks & Regards,

Try and execute the procedure like this

execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||

Hi,

Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:

<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader

Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)

With oCmd
.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = Todate
.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int)).Value = UserID
.Parameters.Add(New SqlParameter("@.PortalID", SqlDbType.Int)).Value = _PortalID
EndWith

If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf

oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf

EndFunction
</code>

However one thing really kicks me. I checked that those stored procedures which didnt give errors (another) and successfully executed without any problems showed in the profiler in this pattern (for example):

exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1

but the the one which gives error showed in the profiler as:

exec sp_executesql N'PABX_sp_MyPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 1

Look at the 2 different executions of SPs. I think ,at some point, sql server doesn't recognise this as a SP and doesn't executes in some other way instead of the usuall way or asp.net sends in the wrong way to execute....yet the T-sqls are correct. Is this a bug?

|||

Hi,

I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:

"SqlCommand.CommandType = CommandType.StoredProcedure"

Line 1: Incorrect Syntax Near.......

Hi,

Uses: Sql server 2000, asp.net 1.1;

I've the following Stored Procedure:

<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@.FromDate DATETIME,
@.ToDate DATETIME,
@.PortalID INT = 1,
@.UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @.FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @.ToDate, 102)
AND PABX_PortalUsers.uID = @.UserID
AND PABX_PortalUsers.PortalID = @.PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>

However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:

Line 1: Incorrect Syntax Near ......

When I check the sql profiler the stored procedure is been executed in this way:

exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.

Could anyone help me out on this?

Thanks & Regards,

Try and execute the procedure like this

execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||

Hi,

Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:

<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader

Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)

With oCmd
.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = Todate
.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int)).Value = UserID
.Parameters.Add(New SqlParameter("@.PortalID", SqlDbType.Int)).Value = _PortalID
EndWith

If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf

oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf

EndFunction
</code>

However one thing really kicks me. I checked that those stored procedures which didnt give errors (another) and successfully executed without any problems showed in the profiler in this pattern (for example):

exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1

but the the one which gives error showed in the profiler as:

exec sp_executesql N'PABX_sp_MyPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 1

Look at the 2 different executions of SPs. I think ,at some point, sql server doesn't recognise this as a SP and doesn't executes in some other way instead of the usuall way or asp.net sends in the wrong way to execute....yet the T-sqls are correct. Is this a bug?

|||

Hi,

I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:

"SqlCommand.CommandType = CommandType.StoredProcedure"

Limits on rows in a table

Hi

We received the following in an email from a third-party supplier (who
naturally has a solution for the problem as described). It sounds like
gibberish to me, but does anyone have any comments?

<quote>
SQL in its current incarnation hits a performance brick wall when a
table contains more than about 75 million rows. This is not a
configuration limit as the table could be grown a lot larger but the
performance issue generates problems for ??; primarily during
search and retrieval of archived objects; although if the database
engine is being heavily hit for retrieval the archiving process can
slow down as well.
</quote
Chloe Crowder
British Librarychloe.crowder@.bl.uk wrote:
> Hi
> We received the following in an email from a third-party supplier (who
> naturally has a solution for the problem as described). It sounds like
> gibberish to me, but does anyone have any comments?
> <quote>
> SQL in its current incarnation hits a performance brick wall when a
> table contains more than about 75 million rows. This is not a
> configuration limit as the table could be grown a lot larger but the
> performance issue generates problems for ??; primarily during
> search and retrieval of archived objects; although if the database
> engine is being heavily hit for retrieval the archiving process can
> slow down as well.
> </quote
I've never heard something like this before. Maybe it's a problem
limited to a specific application. Maybe the app uses the DB in bad
ways (wrong indexing...)?

robert|||chloe.crowder@.bl.uk wrote:
> Hi
> We received the following in an email from a third-party supplier (who
> naturally has a solution for the problem as described). It sounds like
> gibberish to me, but does anyone have any comments?
> <quote>
> SQL in its current incarnation hits a performance brick wall when a
> table contains more than about 75 million rows. This is not a
> configuration limit as the table could be grown a lot larger but the
> performance issue generates problems for ??; primarily during
> search and retrieval of archived objects; although if the database
> engine is being heavily hit for retrieval the archiving process can
> slow down as well.
> </quote>
> Chloe Crowder
> British Library

"SQL in its current incarnation..."

SQL is a language and it has no such limitations. Did they mean to
refer to Microsoft SQL Server? Either way, their claim is plain
nonsense.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||(chloe.crowder@.bl.uk) writes:
> We received the following in an email from a third-party supplier (who
> naturally has a solution for the problem as described). It sounds like
> gibberish to me, but does anyone have any comments?
><quote>
> SQL in its current incarnation hits a performance brick wall when a
> table contains more than about 75 million rows. This is not a
> configuration limit as the table could be grown a lot larger but the
> performance issue generates problems for ??; primarily during
> search and retrieval of archived objects; although if the database
> engine is being heavily hit for retrieval the archiving process can
> slow down as well.
></quote
SQL [Server] in its current incarnation? Nah, rather the current
incarnation of the application from the supplier hits a brick wall, and
the supplier needs to clean up its act.

OK, a 75-million is no game for kids, and it requires more careful coding
and design than a 750000 row table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland is correct; I personally am running an app that generates
40,000,000 rows of data a day, and it performs well (and it's not a
super server, either). I also suck data out of app by a vendor that
has less than 100,000 rows per day, and it takes hours using their
method.

Not saying you should point the finger at your vendor, but if the
performance of their app is mission-critical, and they value your
business, you need to have a conversation with them about SQL tuning.
Could be something you're doing, or it could be something they're
doing. But it's probably not the engine (unless you're running SQL
Server on a bare-bones server).

Stu|||chloe.crowder@.bl.uk wrote:
> Hi
> We received the following in an email from a third-party supplier (who
> naturally has a solution for the problem as described). It sounds like
> gibberish to me, but does anyone have any comments?
> <quote>
> SQL in its current incarnation hits a performance brick wall when a
> table contains more than about 75 million rows. This is not a
> configuration limit as the table could be grown a lot larger but the
> performance issue generates problems for ??; primarily during
> search and retrieval of archived objects; although if the database
> engine is being heavily hit for retrieval the archiving process can
> slow down as well.
> </quote
Translation 1:

We charge little money for our services, and we need to have profits so
we contract amateurs to do the job and when there are problems we
complain against something/someone not related to us. If you want a
solution pay us more money.

Translation 2:

We didn't think about a table with 75 million rows, we don't want to
look like stupids and lose you as a customer.

etc, etc...

One thing is sure, your supplier is not honest, don't trust them!

> Chloe Crowder
> British Library|||Thanks everyone - I was 99.9% sure it was rubbish and the problem was
with their app, but I thought I should check.

Chloe