Order By Adding Rows?

Hi All,
Having trouble with the following SQL statement. If there is multiple
ORDER by's it adds rows to the result. The SQL statement below adds
rows to the result, if I remove block_line_no
from the orer by it returns the correct data.
SELECT *
FROM nc_block_data
WHERE (PROGRAM_ID = 2607) OR
(PROGRAM_ID = 2608) OR
(PROGRAM_ID = 2609) OR
(PROGRAM_ID = 2610) OR
(PROGRAM_ID = 2612) OR
(PROGRAM_ID = 2613) OR
(PROGRAM_ID = 2614) OR
(PROGRAM_ID = 2615) OR
(PROGRAM_ID = 2616) OR
(PROGRAM_ID = 2617) OR
(PROGRAM_ID = 2618) OR
(PROGRAM_ID = 2619) OR
(PROGRAM_ID = 2620) OR
(PROGRAM_ID = 2621)
ORDER BY program_id, block_line_noSkip wrote:
> Hi All,
> Having trouble with the following SQL statement. If there is multiple
> ORDER by's it adds rows to the result. The SQL statement below adds
> rows to the result, if I remove block_line_no
> from the orer by it returns the correct data.
>
> SELECT *
> FROM nc_block_data
> WHERE (PROGRAM_ID = 2607) OR
> (PROGRAM_ID = 2608) OR
> (PROGRAM_ID = 2609) OR
> (PROGRAM_ID = 2610) OR
> (PROGRAM_ID = 2612) OR
> (PROGRAM_ID = 2613) OR
> (PROGRAM_ID = 2614) OR
> (PROGRAM_ID = 2615) OR
> (PROGRAM_ID = 2616) OR
> (PROGRAM_ID = 2617) OR
> (PROGRAM_ID = 2618) OR
> (PROGRAM_ID = 2619) OR
> (PROGRAM_ID = 2620) OR
> (PROGRAM_ID = 2621)
> ORDER BY program_id, block_line_no
Can you post some code to reproduce that problem - CREATE TABLE
followed by INSERTs of one or two sample rows. Also tell us what
version, edition and service pack of SQL Server you are using. If your
server is patched and you can't reproduce the problem then maybe you
are looking at some corrupt data or index somewhere.
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/ms130214(en-US,SQL.90).aspx
--|||Here is the table structure. I created a test table but was not able to
reproduce the problem. Could it be something with the table structure?
USE [iFrame]
GO
/****** Object: Table [dbo].[Nc_Block_Data] Script Date: 04/11/2006
14:32:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Nc_Block_Data](
[PROGRAM_ID] [int] NOT NULL,
[BLOCK_LINE_NO] [int] NOT NULL,
[PROG_LINE_NO] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NC_CODE] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOLE_NAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NCBLOCK_STATUS] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NCBLOCK_STATUS_DT] [datetime] NULL,
[ERROR_INF] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF|||Not sure why but it looks like it was bad data, just in one section of
the DB. Does this make sense? If I grab a different group of data all
works fine.|||If you can be certain that the data is not corrupt, try reindexing the
suspicious block_line_no column.
ML
http://milambda.blogspot.com/sql

Oracle Query

Good day
I am having trouble with getting an Oracle query right for SQL
reporting service.
I wrote a front end to execute the query and it works fine. Now the
next step is to allow users to get a report on any number of clients.
The way I do this for normal SQL Server, is to build SQL with "AND"s
and "OR"s in code, and then send it to reporting services as a
parameter and append it.
This is the text
="SELECT * FROM TABLE WHERE "+ Parameters!Id.Value
And the value of my parameter is:
ID = 'ALE' OR ID = 'ALO'
I keep getting errors like "SQL command not properly ended".
Have anybody done something similar? Am I missing something?
Any help will be appreciated.
Kind regards,
KarlKarl,
You need to use a (:) colon instead of the @. sign.
For example,
Select *
From tblYours
Where ID=:ID
Hope this helps,
rwiethron
"karlbasson@.gmail.com" wrote:
> Good day
> I am having trouble with getting an Oracle query right for SQL
> reporting service.
> I wrote a front end to execute the query and it works fine. Now the
> next step is to allow users to get a report on any number of clients.
> The way I do this for normal SQL Server, is to build SQL with "AND"s
> and "OR"s in code, and then send it to reporting services as a
> parameter and append it.
> This is the text
> ="SELECT * FROM TABLE WHERE "+ Parameters!Id.Value
> And the value of my parameter is:
> ID = 'ALE' OR ID = 'ALO'
> I keep getting errors like "SQL command not properly ended".
> Have anybody done something similar? Am I missing something?
> Any help will be appreciated.
> Kind regards,
> Karl
>

oracle problems

i'm having trouble accessing oracle data (gee... what a shock)
i get the very *helpful* error message: "MinimumCapacity must be
non-negative". no idea what it means. i'm running a sql statement which
calls a stored proc. it runs fine, gets the data fine (i can't use the
wizard because of the above error), but the fields are not retrieved into
the schema for my report layout.
if i set the dataset up in the data page, it works. but before retrieving
the data, the error message above displays and also adds "the list of fields
could not be retrieved". i really don't want to (or should have to) create
all the fields by hand.
the sql is:
{ call pkgMyStuff.sp_MyStoredProc('01/01/1900', '2/26/2004', '1') }
anyone have any ideas here? i know it's not an ms database, but oledb should
be the *universal* data access... so this should work fine.
please help
dushan bilbijaPlease try the managed Oracle provider instead of the OleDB provider when
working with Oracle stored procedures. Just edit your datasource and select
"Oracle" from the data source type dropdown rather than "OleDB". You will
also need to set the correct connection string for the managed provider.
In addition, please follow the guidelines in the following article on MSDN
(scroll down to the
section where it talks about "Oracle REF CURSORs") on how to design the
Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
Note: You need to make sure that the stored procedure has only one OUTPUT
parameter which is a REF cursor and NO other out parameters.
Here is a basic example of a stored procedure:
CREATE OR REPLACE package test_package as
TYPE T_CURSOR IS REF CURSOR;
procedure get_customers(
customer_name in VARCHAR2,
o_customer_cursor out T_CURSOR);
end test_package;
/
CREATE OR REPLACE package body test_package as
procedure get_customers (
customer_name in VARCHAR2,
o_customer_cursor out T_CURSOR)
IS
begin
open o_customer_cursor for select * from customers where name =customer_name;
end;
end test_package;
/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dushan Bilbija" <dbilbija@.msn.com> wrote in message
news:%236Xj9%23iXEHA.3284@.TK2MSFTNGP12.phx.gbl...
> i'm having trouble accessing oracle data (gee... what a shock)
> i get the very *helpful* error message: "MinimumCapacity must be
> non-negative". no idea what it means. i'm running a sql statement which
> calls a stored proc. it runs fine, gets the data fine (i can't use the
> wizard because of the above error), but the fields are not retrieved into
> the schema for my report layout.
> if i set the dataset up in the data page, it works. but before retrieving
> the data, the error message above displays and also adds "the list of
fields
> could not be retrieved". i really don't want to (or should have to) create
> all the fields by hand.
> the sql is:
> { call pkgMyStuff.sp_MyStoredProc('01/01/1900', '2/26/2004', '1') }
> anyone have any ideas here? i know it's not an ms database, but oledb
should
> be the *universal* data access... so this should work fine.
> please help
> dushan bilbija
>|||This seems to have been b/c the database was used with a .mdw file that I did not have. When I created a new database and imported the table into that, the query worked fine.
"David Conorozzo" wrote:
> I am getting this on an Access DB using OLEDB. My query is:
> Dim oComm As New OleDb.OleDbCommand("SELECT * FROM " & _formattedtablename & " WHERE 1=-1", _Connection)
> Dim oReader As OleDb.OleDbDataReader = oComm.ExecuteReader(CommandBehavior.SchemaOnly)
> It doesn't really matter what I do in the query. On this particular DB I always get the exception.
> "Robert Bruckner [MSFT]" wrote:
> > Please try the managed Oracle provider instead of the OleDB provider when
> > working with Oracle stored procedures. Just edit your datasource and select
> > "Oracle" from the data source type dropdown rather than "OleDB". You will
> > also need to set the correct connection string for the managed provider.
> >
> > In addition, please follow the guidelines in the following article on MSDN
> > (scroll down to the
> > section where it talks about "Oracle REF CURSORs") on how to design the
> > Oracle stored procedure:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> >
> > Note: You need to make sure that the stored procedure has only one OUTPUT
> > parameter which is a REF cursor and NO other out parameters.
> >
> > Here is a basic example of a stored procedure:
> >
> > CREATE OR REPLACE package test_package as
> > TYPE T_CURSOR IS REF CURSOR;
> > procedure get_customers(
> > customer_name in VARCHAR2,
> > o_customer_cursor out T_CURSOR);
> > end test_package;
> > /
> >
> > CREATE OR REPLACE package body test_package as
> > procedure get_customers (
> > customer_name in VARCHAR2,
> > o_customer_cursor out T_CURSOR)
> > IS
> > begin
> > open o_customer_cursor for select * from customers where name => > customer_name;
> > end;
> > end test_package;
> > /
> >
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >
> >
> > "Dushan Bilbija" <dbilbija@.msn.com> wrote in message
> > news:%236Xj9%23iXEHA.3284@.TK2MSFTNGP12.phx.gbl...
> > > i'm having trouble accessing oracle data (gee... what a shock)
> > >
> > > i get the very *helpful* error message: "MinimumCapacity must be
> > > non-negative". no idea what it means. i'm running a sql statement which
> > > calls a stored proc. it runs fine, gets the data fine (i can't use the
> > > wizard because of the above error), but the fields are not retrieved into
> > > the schema for my report layout.
> > >
> > > if i set the dataset up in the data page, it works. but before retrieving
> > > the data, the error message above displays and also adds "the list of
> > fields
> > > could not be retrieved". i really don't want to (or should have to) create
> > > all the fields by hand.
> > >
> > > the sql is:
> > >
> > > { call pkgMyStuff.sp_MyStoredProc('01/01/1900', '2/26/2004', '1') }
> > >
> > > anyone have any ideas here? i know it's not an ms database, but oledb
> > should
> > > be the *universal* data access... so this should work fine.
> > >
> > > please help
> > >
> > > dushan bilbija
> > >
> > >
> >
> >
> >

Oracle linked server trouble

Okay, here's the situation. I have a query that runs against a linked server
to an Oracle database (OLE DB connection, Oracle 8i client installed). It w
as fine running daily as a scheduled job until Thursday. On Thursday, some s
ecurity patches were applie
d by Windows Update on the server that hosts Oracle. The equivalent query (u
sing Oracle PL/SQL) still runs fine. A query like SELECT * FROM LNKNAME..USE
RNAME.TABLE is successful with no where clause. It is likewise successful u
sing OPENQUERY to submit th
e SQL to Oracle directly with whatever conditions I want. Virtual tables wor
k as long as there is no where clause. However, a query using qualified 4 pa
rt table names with a where clause or functions in the select list returns a
n error.
The only changes made were security patches applied to the server hosting Or
acle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the la
st query, using OPENQUERY, is a better solution than the first query because
all the tables are in the same Oracle database, but I will need to write qu
eries across this datasourc
e and others in the near future, making that less appealing. Plus, this simp
ly SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
) AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [A
LVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [N
LVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
)
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows retur
ned 0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I ge
t the same error using a query against this datasource even if I simplify it
to a single table with only text functions used, etc. It does not appear to
be related to any numeric
data, or to the number of operands (as Oracle suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS
RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.Hello Mck,
We would appreciate your patience while we are looking into this issue and
will post as soon as we have updates for you.
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Oracle linked server trouble
>thread-index: AcQ+hcDvT9r0qjysQwCeo1J8y3xEtw==
>X-WN-Post: microsoft.public.sqlserver.connect
>From: examnotes <mck@.newsgroups.nospam>
>Subject: Oracle linked server trouble
>Date: Thu, 20 May 2004 09:16:06 -0700
>Lines: 85
>Message-ID: <78572429-DA2C-4E17-B576-344CFCE73B5B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:41814
>NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Okay, here's the situation. I have a query that runs against a linked
server to an Oracle database (OLE DB connection, Oracle 8i client
installed). It was fine running daily as a scheduled job until Thursday. On
Thursday, some security patches were applied by Windows Update on the
server that hosts Oracle. The equivalent query (using Oracle PL/SQL) still
runs fine. A query like SELECT * FROM LNKNAME..USERNAME.TABLE is successful
with no where clause. It is likewise successful using OPENQUERY to submit
the SQL to Oracle directly with whatever conditions I want. Virtual tables
work as long as there is no where clause. However, a query using qualified
4 part table names with a where clause or functions in the select list
returns an error.
The only changes made were security patches applied to the server hosting
Oracle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the
last query, using OPENQUERY, is a better solution than the first query
because all the tables are in the same Oracle database, but I will need to
write queries across this datasource and others in the near future, making
that less appealing. Plus, this simply SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
)
AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [A
LVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [N
LVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
)
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows retur
ned
0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I
get the same error using a query against this datasource even if I simplify
it to a single table with only text functions used, etc. It does not appear
to be related to any numeric data, or to the number of operands (as Oracle
suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS
RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.

>|||Hello Mck,
Looking at the issue, seems like you will need a detailed troubleshooting
including running MDAC trace etc which will give us
the metadata query that runs behind the scene and we can see where it is
failing..
However would be done quickly and effectively with direct assistance from
a Microsoft Support Professional through Microsoft Product Support
Services.
If you would like us to create an incident for you and have Microsoft
Customer Service Representative contact you directly, please send email to
(remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: <ID 22824565>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Engineer
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Oracle linked server trouble
>thread-index: AcQ+hcDvT9r0qjysQwCeo1J8y3xEtw==
>X-WN-Post: microsoft.public.sqlserver.connect
>From: examnotes <mck@.newsgroups.nospam>
>Subject: Oracle linked server trouble
>Date: Thu, 20 May 2004 09:16:06 -0700
>Lines: 85
>Message-ID: <78572429-DA2C-4E17-B576-344CFCE73B5B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:41814
>NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Okay, here's the situation. I have a query that runs against a linked
server to an Oracle database (OLE DB connection, Oracle 8i client
installed). It was fine running daily as a scheduled job until Thursday. On
Thursday, some security patches were applied by Windows Update on the
server that hosts Oracle. The equivalent query (using Oracle PL/SQL) still
runs fine. A query like SELECT * FROM LNKNAME..USERNAME.TABLE is successful
with no where clause. It is likewise successful using OPENQUERY to submit
the SQL to Oracle directly with whatever conditions I want. Virtual tables
work as long as there is no where clause. However, a query using qualified
4 part table names with a where clause or functions in the select list
returns an error.
The only changes made were security patches applied to the server hosting
Oracle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the
last query, using OPENQUERY, is a better solution than the first query
because all the tables are in the same Oracle database, but I will need to
write queries across this datasource and others in the near future, making
that less appealing. Plus, this simply SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
)
AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [A
LVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [N
LVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONEN
TID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)
)
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows retur
ned
0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I
get the same error using a query against this datasource even if I simplify
it to a single table with only text functions used, etc. It does not appear
to be related to any numeric data, or to the number of operands (as Oracle
suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS
RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.

>|||Mck-
Curious if you ever got this resolved and how? I'm having pretty much the same issue. Ple
ase reply to sreid@.solutran.com, as I don't check this forum regularly.
Thanks!

Oracle linked server trouble

Okay, here's the situation. I have a query that runs against a linked server to an Oracle database (OLE DB connection, Oracle 8i client installed). It was fine running daily as a scheduled job until Thursday. On Thursday, some security patches were applie
d by Windows Update on the server that hosts Oracle. The equivalent query (using Oracle PL/SQL) still runs fine. A query like SELECT * FROM LNKNAME..USERNAME.TABLE is successful with no where clause. It is likewise successful using OPENQUERY to submit th
e SQL to Oracle directly with whatever conditions I want. Virtual tables work as long as there is no where clause. However, a query using qualified 4 part table names with a where clause or functions in the select list returns an error.
The only changes made were security patches applied to the server hosting Oracle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the last query, using OPENQUERY, is a better solution than the first query because all the tables are in the same Oracle database, but I will need to write queries across this datasourc
e and others in the near future, making that less appealing. Plus, this simply SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1)) AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [ALVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [NLVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1))
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I get the same error using a query against this datasource even if I simplify it to a single table with only text functions used, etc. It does not appear to be related to any numeric
data, or to the number of operands (as Oracle suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.
Hello Mck,
We would appreciate your patience while we are looking into this issue and
will post as soon as we have updates for you.
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Oracle linked server trouble
>thread-index: AcQ+hcDvT9r0qjysQwCeo1J8y3xEtw==
>X-WN-Post: microsoft.public.sqlserver.connect
>From: =?Utf-8?B?TWNL?= <mck@.newsgroups.nospam>
>Subject: Oracle linked server trouble
>Date: Thu, 20 May 2004 09:16:06 -0700
>Lines: 85
>Message-ID: <78572429-DA2C-4E17-B576-344CFCE73B5B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:41814
>NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Okay, here's the situation. I have a query that runs against a linked
server to an Oracle database (OLE DB connection, Oracle 8i client
installed). It was fine running daily as a scheduled job until Thursday. On
Thursday, some security patches were applied by Windows Update on the
server that hosts Oracle. The equivalent query (using Oracle PL/SQL) still
runs fine. A query like SELECT * FROM LNKNAME..USERNAME.TABLE is successful
with no where clause. It is likewise successful using OPENQUERY to submit
the SQL to Oracle directly with whatever conditions I want. Virtual tables
work as long as there is no where clause. However, a query using qualified
4 part table names with a where clause or functions in the select list
returns an error.
The only changes made were security patches applied to the server hosting
Oracle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the
last query, using OPENQUERY, is a better solution than the first query
because all the tables are in the same Oracle database, but I will need to
write queries across this datasource and others in the near future, making
that less appealing. Plus, this simply SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1))
AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [ALVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [NLVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1))
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned
0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I
get the same error using a query against this datasource even if I simplify
it to a single table with only text functions used, etc. It does not appear
to be related to any numeric data, or to the number of operands (as Oracle
suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS
RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.

>
|||Hello Mck,
Looking at the issue, seems like you will need a detailed troubleshooting
including running MDAC trace etc which will give us
the metadata query that runs behind the scene and we can see where it is
failing..
However would be done quickly and effectively with direct assistance from
a Microsoft Support Professional through Microsoft Product Support
Services.
If you would like us to create an incident for you and have Microsoft
Customer Service Representative contact you directly, please send email to
(remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: <ID 22824565>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Engineer
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Oracle linked server trouble
>thread-index: AcQ+hcDvT9r0qjysQwCeo1J8y3xEtw==
>X-WN-Post: microsoft.public.sqlserver.connect
>From: =?Utf-8?B?TWNL?= <mck@.newsgroups.nospam>
>Subject: Oracle linked server trouble
>Date: Thu, 20 May 2004 09:16:06 -0700
>Lines: 85
>Message-ID: <78572429-DA2C-4E17-B576-344CFCE73B5B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:41814
>NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Okay, here's the situation. I have a query that runs against a linked
server to an Oracle database (OLE DB connection, Oracle 8i client
installed). It was fine running daily as a scheduled job until Thursday. On
Thursday, some security patches were applied by Windows Update on the
server that hosts Oracle. The equivalent query (using Oracle PL/SQL) still
runs fine. A query like SELECT * FROM LNKNAME..USERNAME.TABLE is successful
with no where clause. It is likewise successful using OPENQUERY to submit
the SQL to Oracle directly with whatever conditions I want. Virtual tables
work as long as there is no where clause. However, a query using qualified
4 part table names with a where clause or functions in the select list
returns an error.
The only changes made were security patches applied to the server hosting
Oracle. Those patches are as follows:
KB837009
Q832894
KB837001
KB828741
KB835732
Q832483
Below is the query that fails, followed by two that succeed. Granted, the
last query, using OPENQUERY, is a better solution than the first query
because all the tables are in the same Oracle database, but I will need to
write queries across this datasource and others in the near future, making
that less appealing. Plus, this simply SHOULD work!
SELECT
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1))
AS RDate,
COUNT(DISTINCT [NODE].[NAME]) AS NCount
FROM [BMCEDS]..[EDA_USER].[NAMESPACE] [NS]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [ALVL]
ON [NS].[NAMESPACEID] = [ALVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [APPL]
ON [ALVL].[NAMESPACECOMPONENTID] = [APPL].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACELEVELCOMPONENT] [NLVL]
ON [NS].[NAMESPACEID] = [NLVL].[NAMESPACEID]
INNER JOIN [BMCEDS]..[EDA_USER].[NAMESPACECOMPONENT] [NODE]
ON [NLVL].[NAMESPACECOMPONENTID] = [NODE].[NAMESPACECOMPONENTID]
INNER JOIN [BMCEDS]..[EDA_USER].[MEASUREMENTSUMMARY] [MS]
ON [NS].[NAMESPACEID] = [MS].[NAMESPACEID]
WHERE
([NLVL].[COMPONENTLEVEL] = 1) AND
([ALVL].[COMPONENTLEVEL] = 2) AND
([APPL].[NAME] IN ('CPU', 'NT_CPU')) AND
([MS].[SUMMARYLEVELID] = 1) AND
([MS].[LOCALTIMESTAMP] >= DATEADD(dd, -30, GETDATE()))
GROUP BY
CONVERT(datetime, ROUND(CONVERT(float, [MS].[LOCALTIMESTAMP]), 0, 1))
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned
0x80040e57].
For the record, this numeric overflow error seems to be pretty generic. I
get the same error using a query against this datasource even if I simplify
it to a single table with only text functions used, etc. It does not appear
to be related to any numeric data, or to the number of operands (as Oracle
suggests).
SELECT CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1)) AS
RDate,
COUNT(DISTINCT LEFT(NS.FULLNAME, CHARINDEX('/', NS.FULLNAME, 2))) AS Ct
FROM
(SELECT * FROM BMCEDS..EDA_USER.NAMESPACE) AS NS
INNER JOIN (SELECT * FROM BMCEDS..EDA_USER.MEASUREMENTSUMMARY) AS MS
ON (NS.NAMESPACEID = MS.NAMESPACEID)
WHERE
(NS.FULLNAME LIKE '/%/%CPU/%') AND
(NS.LEVELCOUNT = 4) AND
(MS.LOCALTIMESTAMP >= DATEADD(mm, -1, GETDATE()))
GROUP BY CONVERT(datetime, ROUND(CONVERT(float, MS.LOCALTIMESTAMP), 0, 1))
Successful.
SELECT *
FROM
OPENQUERY(BMCEDS,
'SELECT
TRUNC(MS.LOCALTIMESTAMP),
COUNT(DISTINCT NN.NAME)
FROM
MEASUREMENTSUMMARY MS,
NAMESPACELEVELCOMPONENT NL,
NAMESPACECOMPONENT NN,
NAMESPACELEVELCOMPONENT AL,
NAMESPACECOMPONENT AN
WHERE
(MS.LOCALTIMESTAMP >= ADD_MONTHS(TRUNC(SYSDATE), -1)) AND
(MS.SUMMARYLEVELID = 1) AND
(NL.NAMESPACEID = MS.NAMESPACEID) AND
(NL.COMPONENTLEVEL = 1) AND
(NN.NAMESPACECOMPONENTID = NL.NAMESPACECOMPONENTID) AND
(AL.NAMESPACEID = MS.NAMESPACEID) AND
(AL.COMPONENTLEVEL = 2) AND
(AN.NAMESPACECOMPONENTID = AL.NAMESPACECOMPONENTID) AND
(AN.NAME IN (''CPU'', ''NT_CPU''))
GROUP BY
TRUNC(MS.LOCALTIMESTAMP)')
Successful.

>
|||Mck-
Curious if you ever got this resolved and how? I'm having pretty much
the same issue. Please reply to sreid@.solutran.com, as I don't check
this forum regularly.
Thanks!
sreid55
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message233123.html

ORACLE BEFORE INSERT EQUIVALENT IN SQL SERVER

I am having trouble creating an INSTEAD OF trigger in SQL Server to
replicate a BEFORE UPDATE trigger from ORACLE.
Here is a sample of the ORACLE BEFORE UPDATE trigger:
CREATE TRIGGER myTRIGGER ON MYTABLE
begin
:new.DT := SYSDATE;
if :new.NM is NULL then
:new.NM := USER;
end if;
end myTRIGGER;

It seems as though I have to jump through hoops in SQL Server AND I
cannot come up with correct results.

Here is a snippet from SQL SERVER (this is what I figured I needed to
do after reading various articles,questions):
CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS

SELECT * INTO #MYTABLE FROM INSERTED

UPDATE #MYTABLE SET DT = GETDATE()
UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

UPDATE THETABLE
SET
DT = (SELECT DT FROM #MYTABLE),
NM = (SELECT NM FROM #MYTABLE)
WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.You don't need to use temp tables and subqueries here. The example
below will do the job, assuming that ID is the primary key and is never
changed. Note that you'll also need to include other columns in the
UPDATE statement in order for those to be updated.

CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS
UPDATE THETABLE
SET
DT = GETDATE(),
NM = COALESCE(inserted.NM, USER)
FROM inserted
WHERE THETABLE.ID = inserted.ID
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"DTB" <macdtb@.mac.com> wrote in message
news:4af8df45.0308170552.27c24433@.posting.google.c om...
> I am having trouble creating an INSTEAD OF trigger in SQL Server to
> replicate a BEFORE UPDATE trigger from ORACLE.
> Here is a sample of the ORACLE BEFORE UPDATE trigger:
> CREATE TRIGGER myTRIGGER ON MYTABLE
> begin
> :new.DT := SYSDATE;
> if :new.NM is NULL then
> :new.NM := USER;
> end if;
> end myTRIGGER;
> It seems as though I have to jump through hoops in SQL Server AND I
> cannot come up with correct results.
> Here is a snippet from SQL SERVER (this is what I figured I needed to
> do after reading various articles,questions):
> CREATE TRIGGER myTRIGGER on THETABLE
> INSTEAD OF UPDATE
> AS
> SELECT * INTO #MYTABLE FROM INSERTED
> UPDATE #MYTABLE SET DT = GETDATE()
> UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> UPDATE THETABLE
> SET
> DT = (SELECT DT FROM #MYTABLE),
> NM = (SELECT NM FROM #MYTABLE)
> WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> Can anyone please shed some light on this? Thanks in advance.|||Thank you Dan! This works the way I want it to.

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<ZfN%a.27352$vo2.5843@.newsread1.news.atl.earthlink. net>...
> You don't need to use temp tables and subqueries here. The example
> below will do the job, assuming that ID is the primary key and is never
> changed. Note that you'll also need to include other columns in the
> UPDATE statement in order for those to be updated.
> CREATE TRIGGER myTRIGGER on THETABLE
> INSTEAD OF UPDATE
> AS
> UPDATE THETABLE
> SET
> DT = GETDATE(),
> NM = COALESCE(inserted.NM, USER)
> FROM inserted
> WHERE THETABLE.ID = inserted.ID
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --------
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index...epartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --------
> "DTB" <macdtb@.mac.com> wrote in message
> news:4af8df45.0308170552.27c24433@.posting.google.c om...
> > I am having trouble creating an INSTEAD OF trigger in SQL Server to
> > replicate a BEFORE UPDATE trigger from ORACLE.
> > Here is a sample of the ORACLE BEFORE UPDATE trigger:
> > CREATE TRIGGER myTRIGGER ON MYTABLE
> > begin
> > :new.DT := SYSDATE;
> > if :new.NM is NULL then
> > :new.NM := USER;
> > end if;
> > end myTRIGGER;
> > It seems as though I have to jump through hoops in SQL Server AND I
> > cannot come up with correct results.
> > Here is a snippet from SQL SERVER (this is what I figured I needed to
> > do after reading various articles,questions):
> > CREATE TRIGGER myTRIGGER on THETABLE
> > INSTEAD OF UPDATE
> > AS
> > SELECT * INTO #MYTABLE FROM INSERTED
> > UPDATE #MYTABLE SET DT = GETDATE()
> > UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> > UPDATE THETABLE
> > SET
> > DT = (SELECT DT FROM #MYTABLE),
> > NM = (SELECT NM FROM #MYTABLE)
> > WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> > Can anyone please shed some light on this? Thanks in advance.|||I'm tring to import the data from text file into Table and table have
the trigger. but it takes too long time

here is code

CREATE TRIGGER TR_TFACP200

ON TFACP200

FOR INSERT

AS

UPDATE TFACP200

SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)

WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1

AND DOCUMENT_DATE IS NULL

can any give me the solution, i thing this is happening boz. UPDATE
statement is fire on all records when we insert a single record.

I think if i use BEFORE INSERT trigger like ORACLE then it solve the
problem, but how i use the BEFORE INSERT trigger in SQL server

I'm using SQL Server 7.0, and INSTEAD OF option not available.

pls help me.

Milind

I am having trouble creating an INSTEAD OF trigger in SQL Server version
7.0 to replicate a BEFORE INSERT trigger from ORACLE.

Originally posted by Dtb

> I am having trouble creating an INSTEAD OF trigger in SQL Server to

> replicate a BEFORE UPDATE trigger from ORACLE.

> Here is a sample of the ORACLE BEFORE UPDATE trigger:

> CREATE TRIGGER myTRIGGER ON MYTABLE

> begin

> :new.DT := SYSDATE;

> if :new.NM is NULL then

> :new.NM := USER;

> end if;

> end myTRIGGER;

> It seems as though I have to jump through hoops in SQL Server AND I

> cannot come up with correct results.

> Here is a snippet from SQL SERVER (this is what I figured I needed to

> do after reading various articles,questions):

> CREATE TRIGGER myTRIGGER on THETABLE

> INSTEAD OF UPDATE

> AS

> SELECT * INTO #MYTABLE FROM INSERTED

> UPDATE #MYTABLE SET DT = GETDATE()

> UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

> UPDATE THETABLE

> SET

> DT = (SELECT DT FROM #MYTABLE),

> NM = (SELECT NM FROM #MYTABLE)

> WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.

--
Posted via http://dbforums.com|||Milind,

you are correct that the current problem is, that your trigger acts on
all rows of the table instead of just the inserted.

In the trigger context, all inserted rows are available in the virtual
table "inserted". (Lookup "triggers, inserted tables" in BOL for more
information).

Assuming "id" is the primary key of your table, you could use:

CREATE TRIGGER TR_TFACP200
ON TFACP200
FOR INSERT
AS

UPDATE TFACP200
SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)
WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1
AND DOCUMENT_DATE IS NULL
AND EXISTS (
SELECT 1
FROM inserted
WHERE inserted.id = TFACP200.id
)

Hope this helps,
Gert-Jan

Milind wrote:
> I'm tring to import the data from text file into Table and table have
> the trigger. but it takes too long time
> here is code
> CREATE TRIGGER TR_TFACP200
> ON TFACP200
> FOR INSERT
> AS
> UPDATE TFACP200
> SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
> RIGHT(DDATE,4)
> WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
> RIGHT(DDATE,4)) = 1
> AND DOCUMENT_DATE IS NULL
> can any give me the solution, i thing this is happening boz. UPDATE
> statement is fire on all records when we insert a single record.
> I think if i use BEFORE INSERT trigger like ORACLE then it solve the
> problem, but how i use the BEFORE INSERT trigger in SQL server
> I'm using SQL Server 7.0, and INSTEAD OF option not available.
> pls help me.
> Milind
> I am having trouble creating an INSTEAD OF trigger in SQL Server version
> 7.0 to replicate a BEFORE INSERT trigger from ORACLE.
> Originally posted by Dtb
> > I am having trouble creating an INSTEAD OF trigger in SQL Server to
> > replicate a BEFORE UPDATE trigger from ORACLE.
> > Here is a sample of the ORACLE BEFORE UPDATE trigger:
> > CREATE TRIGGER myTRIGGER ON MYTABLE
> > begin
> > :new.DT := SYSDATE;
> > if :new.NM is NULL then
> > :new.NM := USER;
> > end if;
> > end myTRIGGER;
> > It seems as though I have to jump through hoops in SQL Server AND I
> > cannot come up with correct results.
> > Here is a snippet from SQL SERVER (this is what I figured I needed to
> > do after reading various articles,questions):
> > CREATE TRIGGER myTRIGGER on THETABLE
> > INSTEAD OF UPDATE
> > AS
> > SELECT * INTO #MYTABLE FROM INSERTED
> > UPDATE #MYTABLE SET DT = GETDATE()
> > UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> > UPDATE THETABLE
> > SET
> > DT = (SELECT DT FROM #MYTABLE),
> > NM = (SELECT NM FROM #MYTABLE)
> > WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> Can anyone please shed some light on this? Thanks in advance.
> --
> Posted via http://dbforums.com