Distributed insert on oracle DB does not work from within a transaction.

Dear All,

I have MS SQL 2000 on Windows 2000 box and Oracle 9i on RH Linux 9.

I wanna write a trigger on MS SQL which inserts a record in the Oracle table.

So I have configured a Linked server on MS SQL using Microsoft OLE DB provider MSDAORA and am able to insert into (Single insert st) oracle table successfully from the Query Analyzer.

However the insert does not work when I code it in the trigger. Basically insert on the Oracle DB does not work from a transaction.
I receive the foll error when the trigger executes -

Server: Msg 7391, Level 16, State 1, Procedure AIN_sql2ora, Line 14
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

Can anyone help me on this ?

Regards,
Azhar.set implicit transactions off

displaying related rows from a table

Dear All,
I want to write an SQL program that would display all identical fields from
a table, for eg if the table has 5 columns and two rows have same values for
all these five columns , the sql statement should be able to find all such
matching row
s in the table and display them to the user.
How would i go about doing this.
thank you
harshaselect col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/feat...cle.php/2235081
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying related rows from a table

Dear All,
I want to write an SQL program that would display all identical fields from a table, for eg if the table has 5 columns and two rows have same values for all these five columns , the sql statement should be able to find all such matching row
s in the table and display them to the user.
How would i go about doing this.
thank you
harsha
select col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/featu...le.php/2235081
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying related rows from a table

Dear All
I want to write an SQL program that would display all identical fields from a table, for eg if the table has 5 columns and two rows have same values for all these five columns , the sql statement should be able to find all such matching rows in the table and display them to the user
How would i go about doing this
thank yo
harshaharsha,
You can code a self-join on the table.
Ex. SELECT A.* FROM mytable A JOIN mytable B ON
a.col1 = b.col1 and a.col2 = b.col2 and b.col3 = b.col3
and ....
Might be other ways, but this should work for you.
Doug
>--Original Message--
>Dear All,
> I want to write an SQL program that would
display all identical fields from a table, for eg if the
table has 5 columns and two rows have same values for all
these five columns , the sql statement should be able to
find all such matching rows in the table and display them
to the user.
>How would i go about doing this.
>thank you
>harsha
>.
>|||select col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/features/mssql/article.php/2235081
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying related data from a table

Dear All,
I am using a SQL server database with around 20 columns,all
the columns have numeric values, I want to write an SQL statement
which does the following:

compare each row of the table with all other rows in the table and
return all the rows that have a difference of + or - 0.5 in each
column, for eg if row1 has values 12.2,13.6,11.4,15.7 corresponding
to column1,2,3,4 the sql statement should return all the rows from the
table with values of column 1-4 between

12.2- 0.5 to 12.2 + 0.5
13.6 -0.5 to 13.6+ 0.5
11.4 -0.5 to 11.4 +0.5
15.7 -0.5 to 15.7 +0.5

so effectively this statement would search for groups of rows that
have matching values(diffence of + or - 0.5)

Could anyone suggest how i go about doing this.

thank you in advance
harshaIt's not clear to me exactly what you want to see in the result. If you just
want to *join* like rows based on the criteria you've specified then you
could do so as follows (I've had to assume a primary key because you didn't
specify one).

CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, c1 NUMERIC(3,1) NOT
NULL, c2 NUMERIC(3,1) NOT NULL, c3 NUMERIC(3,1) NOT NULL, c4 NUMERIC(3,1)
NOT NULL)

/* Sample data */
INSERT INTO SomeTable VALUES (1, 12.2, 13.6, 11.4, 15.7)
INSERT INTO SomeTable VALUES (2, 12.0, 13.9, 10.9, 15.2)

SELECT S1.keycol, S1.c1, S1.c2, S1.c3, S1.c4,
S2.keycol, S2.c1, S2.c2, S2.c3, S2.c4
FROM SomeTable AS S1
JOIN SomeTable AS S2
ON S1.keycol < S2.keycol
AND ABS(S1.c1-S2.c1) <= 0.5
AND ABS(S1.c2-S2.c2) <= 0.5
AND ABS(S1.c3-S2.c3) <= 0.5
AND ABS(S1.c4-S2.c4) <= 0.5

Unfortunately this query will not optimize well because of the join on a
calculated expression.

--
David Portas
SQL Server MVP
--

Display Tabular Form from DBCC SHOWCONTIG Command

Dear all,
I want to display information resulted by DBCC SHOWCONTIG Command in
tabular form like below:
(Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
TABLE A
TABLE B
TABLE C
...
Is it possible?
Thanks
Robert LieUse the WITH TABLERESULTS option. Actually, if you look in Books Online (upd
ated version), you have
a sample for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23lOMj05VFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to display information resulted by DBCC SHOWCONTIG Command in tabul
ar form like below:
> (Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
> TABLE A
> TABLE B
> TABLE C
> ...
> Is it possible?
> Thanks
> Robert Lie

Display Tabular Form from DBCC SHOWCONTIG Command

Dear all,
I want to display information resulted by DBCC SHOWCONTIG Command in
tabular form like below:
(Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
TABLE A
TABLE B
TABLE C
...
Is it possible?
Thanks
Robert Lie
Use the WITH TABLERESULTS option. Actually, if you look in Books Online (updated version), you have
a sample for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23lOMj05VFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to display information resulted by DBCC SHOWCONTIG Command in tabular form like below:
> (Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
> TABLE A
> TABLE B
> TABLE C
> ...
> Is it possible?
> Thanks
> Robert Lie

Display Tabular Form from DBCC SHOWCONTIG Command

Dear all,
I want to display information resulted by DBCC SHOWCONTIG Command in
tabular form like below:
(Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
TABLE A
TABLE B
TABLE C
...
Is it possible?
Thanks
Robert LieUse the WITH TABLERESULTS option. Actually, if you look in Books Online (updated version), you have
a sample for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23lOMj05VFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to display information resulted by DBCC SHOWCONTIG Command in tabular form like below:
> (Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
> TABLE A
> TABLE B
> TABLE C
> ...
> Is it possible?
> Thanks
> Robert Lie

Display SQL 2005 KPI status graphic using visual studio 2005

Dear All,

Could anyone help send me a sample mdx code on how i could get the KPI status graphics from MS SQL 2005. I create a cube and add a few KPI's into the cube, on the management studio 2005 I am able to view the graphics e.g. smiley but when i use the mdx command I could only display the KPI status -1, 0 or 1 not the graphics.

The MDX functions does not include the KPI_status_graphics.

Thank you in advance.

Mike Siow siowm@.metierview.com

You can retrieve the KPI_STATUS_GRAPHIC and KPI_TREND_GRAPHIC for a KPI using the MDSCHEMA_KPIS Rowset:

http://msdn2.microsoft.com/en-us/library/ms126258.aspx

>>

MDSCHEMA_KPIS Rowset

Describes the key performance indicators (KPIs) within a database.

...

>>

For example, for the Adventure Works Internet Revenue KPI:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>MDSCHEMA_KPIS</RequestType>

<Restrictions>

<RestrictionList>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<KPI_NAME>Internet Revenue</KPI_NAME>

</RestrictionList>

</Restrictions>

<Properties>

<PropertyList>

<Catalog>Adventure Works DW</Catalog>

<Format>Tabular</Format>

</PropertyList>

</Properties>

</Discover>

--

<return xmlns="urn:schemas-microsoft-com:xml-analysis">

<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">

<xsd:element name="root">

<xsd:complexType>

<xsd:sequence minOccurs="0" maxOccurs="unbounded">

<xsd:element name="row" type="row" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:simpleType name="uuid">

<xsd:restriction base="xsd:string">

<xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />

</xsd:restriction>

</xsd:simpleType>

<xsd:complexType name="xmlDocument">

<xsd:sequence>

<xsd:any />

</xsd:sequence>

</xsd:complexType>

<xsd:complexType name="row">

<xsd:sequence>

<xsd:element sql:field="CATALOG_NAME" name="CATALOG_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="SCHEMA_NAME" name="SCHEMA_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="CUBE_NAME" name="CUBE_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="MEASUREGROUP_NAME" name="MEASUREGROUP_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_NAME" name="KPI_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_CAPTION" name="KPI_CAPTION" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_DESCRIPTION" name="KPI_DESCRIPTION" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_DISPLAY_FOLDER" name="KPI_DISPLAY_FOLDER" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_VALUE" name="KPI_VALUE" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_GOAL" name="KPI_GOAL" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_STATUS" name="KPI_STATUS" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_TREND" name="KPI_TREND" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_STATUS_GRAPHIC" name="KPI_STATUS_GRAPHIC" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_TREND_GRAPHIC" name="KPI_TREND_GRAPHIC" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_WEIGHT" name="KPI_WEIGHT" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_CURRENT_TIME_MEMBER" name="KPI_CURRENT_TIME_MEMBER" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_PARENT_KPI_NAME" name="KPI_PARENT_KPI_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="ANNOTATIONS" name="ANNOTATIONS" type="xsd:string" minOccurs="0" />

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

<row>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASUREGROUP_NAME>Internet Sales</MEASUREGROUP_NAME>

<KPI_NAME>Internet Revenue</KPI_NAME>

<KPI_CAPTION>Internet Revenue</KPI_CAPTION>

<KPI_DESCRIPTION>Revenue realized through direct sales via the internet.</KPI_DESCRIPTION>

<KPI_DISPLAY_FOLDER>Financial Perspective\Grow Revenue</KPI_DISPLAY_FOLDER>

<KPI_VALUE>[Measures].[Internet Sales Amount]</KPI_VALUE>

<KPI_GOAL>[Measures].[Internet Revenue Goal]</KPI_GOAL>

<KPI_STATUS>[Measures].[Internet Revenue Status]</KPI_STATUS>

<KPI_TREND>[Measures].[Internet Revenue Trend]</KPI_TREND>

<KPI_STATUS_GRAPHIC>Cylinder</KPI_STATUS_GRAPHIC>

<KPI_TREND_GRAPHIC>Standard Arrow</KPI_TREND_GRAPHIC>

<KPI_WEIGHT />

<KPI_PARENT_KPI_NAME />

<ANNOTATIONS />

</row>

</root>

</return>

|||

Hi Deepak,

Thank you.

Mike Siow

Display Report Filters in Excel Problem

Dear Anyone,

I have a report that displays the user's selected filters. I have placed this on the page footer. When I export this to excel, it wont display. Am I doing something wrong?

Thanks,
JosephHi Joseph,

I had this same problem before where I place the parameter selection display in the page footer. Apparently, if you place it in the page footer, it will be exported to excel.

What you can do is place it on the body along with the report table. This wil allow you to include your additional information in excel.

Thanks,
Joseph

display list problem

dear all
i have a dataSet in this way
ID NAME
1 VALUE1
2 VAlue2
3 VALUE3
4 VALUE4
5 VALUE5
i need to display this daya as the following
1- VALUE1 2- VALUE2 3- VALUE3
4- VALUE4 5- VALUE5
is there are anyway
thanksBolos,
A few options for you here,
You could use the multi-column feature. I think it is the Body's
Columns property.
Or, if we are talking about low volumes of data you pivot in-line. The
following is based on a code snippet in an article by Clinton Herring
on SQLServerCentral.com. You could use as is for one line or stick
inside a loop for a few more
SELECT @.tmp_values = @.tmp_values + convert(varchar(20), column_data) +
','
FROM DB.dbo.many_tbl
WHERE id = @.tmp_id
ORDER BY column_data
SELECT @.tmp_values
Chris
Bolos wageh wrote:
> dear all
> i have a dataSet in this way
> ID NAME
> 1 VALUE1
> 2 VAlue2
> 3 VALUE3
> 4 VALUE4
> 5 VALUE5
> i need to display this daya as the following
> 1- VALUE1 2- VALUE2 3- VALUE3
> 4- VALUE4 5- VALUE5
> is there are anyway
> thanks

Display first record occurance in textbox in RB

Dear Anyone,

I would like to display the first occurance of a record as a text box outside that of the report. I would like to do this in Report Builder. Is this possible?

Thanks,
Joseph

Try:

Use an expression like

= First(Fields!FieldName.Value, "DataSet1")

where field name = the mane of your record field; and DataSet1 is your dataset name.|||

It doesnt seem to work. I'm using this on a new textbox in RB. Am I doing it wrong?

|||Heres the error that gets displayed:

The Value expression for the textbox ‘textbox’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. (rsInvalidAggregateScope)|||If you just want the dataset scope, leave out the second parameter:

= First(Fields!FieldName.Value)

RB doesn't expose the RDL field names directly, so you'll have to guess that part. It's usually equal to or a predictable variant of the text in the column header.

Display first record occurance in textbox in RB

Dear Anyone,

I would like to display the first occurance of a record as a text box outside that of the report. I would like to do this in Report Builder. Is this possible?

Thanks,
Joseph

Try:

Use an expression like

= First(Fields!FieldName.Value, "DataSet1")

where field name = the mane of your record field; and DataSet1 is your dataset name.|||

It doesnt seem to work. I'm using this on a new textbox in RB. Am I doing it wrong?

|||Heres the error that gets displayed:

The Value expression for the textbox ‘textbox’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. (rsInvalidAggregateScope)|||If you just want the dataset scope, leave out the second parameter:

= First(Fields!FieldName.Value)

RB doesn't expose the RDL field names directly, so you'll have to guess that part. It's usually equal to or a predictable variant of the text in the column header.

Display Estimated Execution Plan

Dear all,
I obtain a warning when I press the 'Display Estimated Execution Plan'
for the following sp:
up_Rpt_Data_Contents_Bridge_Wholesaler '2005-01-01','COW'
Server: Msg 208, Level 16, State 1, Procedure
up_Rpt_Data_Contents_Bridge_Wholesaler, Line 42
Invalid object name '#PERIODS'.
Query Analyzer is parsing all the contents for that object? Just a temp
table.
How can reckon something if not exists yet?
Thanks in advance and best regards,This is called "delayed name resolution" or something like that... SQL wil
l
repeast the name resolution when it executes the SP. Then, it checks the
actual code and makes sure that you are indeed creating a table named
#PERIODS.
"Enric" wrote:

> Dear all,
> I obtain a warning when I press the 'Display Estimated Execution Plan'
> for the following sp:
> up_Rpt_Data_Contents_Bridge_Wholesaler '2005-01-01','COW'
> Server: Msg 208, Level 16, State 1, Procedure
> up_Rpt_Data_Contents_Bridge_Wholesaler, Line 42
> Invalid object name '#PERIODS'.
>
> Query Analyzer is parsing all the contents for that object? Just a temp
> table.
> How can reckon something if not exists yet?
>
> Thanks in advance and best regards,|||Thanks for your feedback
"Enric" wrote:

> Dear all,
> I obtain a warning when I press the 'Display Estimated Execution Plan'
> for the following sp:
> up_Rpt_Data_Contents_Bridge_Wholesaler '2005-01-01','COW'
> Server: Msg 208, Level 16, State 1, Procedure
> up_Rpt_Data_Contents_Bridge_Wholesaler, Line 42
> Invalid object name '#PERIODS'.
>
> Query Analyzer is parsing all the contents for that object? Just a temp
> table.
> How can reckon something if not exists yet?
>
> Thanks in advance and best regards,|||Since your procedure creates a temp table and with "Estimated" no code is ex
ecuted, SQL Server
cannot show the plan as the table isn't created. Select Show execution plan"
and instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:BC203C72-A62E-460C-95FC-B2D707AD60DD@.microsoft.com...
> Dear all,
> I obtain a warning when I press the 'Display Estimated Execution Plan'
> for the following sp:
> up_Rpt_Data_Contents_Bridge_Wholesaler '2005-01-01','COW'
> Server: Msg 208, Level 16, State 1, Procedure
> up_Rpt_Data_Contents_Bridge_Wholesaler, Line 42
> Invalid object name '#PERIODS'.
>
> Query Analyzer is parsing all the contents for that object? Just a temp
> table.
> How can reckon something if not exists yet?
>
> Thanks in advance and best regards,|||It has been very useful.
Best wishes,
"Tibor Karaszi" wrote:

> Since your procedure creates a temp table and with "Estimated" no code is
executed, SQL Server
> cannot show the plan as the table isn't created. Select Show execution pla
n" and instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:BC203C72-A62E-460C-95FC-B2D707AD60DD@.microsoft.com...
>
>