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

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
ABC
1ax
2by
3c1
4nullnull
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1sql

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:53B523BC4BB04@.SQLMonster.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1

distinct row count in a table.

Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.|||Try this...

SELECT COUNT(*)
FROM
(SELECT DISTINCT * FROM ch1)ch1|||Or
SELECT COUNT(DISTINCT *) AS Distinct_Rows FROM ch1|||:shocked:
select sum(case when count(*)>1 then 1 else 1 end)
from ch1 group by a,b,c,d|||threads merged

ramshree, please do not post the same question into multiple forums|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1I think that you're "close, but no banana" on this... The code that you posted will actually count the non-distinct rows (how many rows have at least one duplicated row elsewhere).

-PatP

Distinct insert

I am trying to insert a record id primary at the same time that I am trying
to insert a Distinct Account number.
The insert statement is something kind of like this:
insert into Accounts (AccountsOID,Account)
select Distinct "Rec NO" + 100 as AccountsOID, "LIC TAG" as Account from
AutoExpt
In the AutoExpt table there are multiple occurrences of "LIC TAG" values all
of which need to be put into the Accounts table, however there must be only
one entry for the Lic tag in the accounts table. The Rec No +100 is just to
create a unique primary key for the AccountsOID.
Is there anyway I can get only the Distinct Lic Tag's, but at the same time
get a unique primary key for them.
Thanks,
On Wed, 25 Aug 2004 12:55:03 -0700, Robert wrote:

>I am trying to insert a record id primary at the same time that I am trying
>to insert a Distinct Account number.
>The insert statement is something kind of like this:
>insert into Accounts (AccountsOID,Account)
>select Distinct "Rec NO" + 100 as AccountsOID, "LIC TAG" as Account from
>AutoExpt
>In the AutoExpt table there are multiple occurrences of "LIC TAG" values all
>of which need to be put into the Accounts table, however there must be only
>one entry for the Lic tag in the accounts table. The Rec No +100 is just to
>create a unique primary key for the AccountsOID.
>Is there anyway I can get only the Distinct Lic Tag's, but at the same time
>get a unique primary key for them.
>Thanks,
Hi Robert,
Please provide DDL (CREATE TABLE statements, with constraints), sample
data (as INSERT statements), expected output and a description of the
business problem you're trying to solve.
http://www.aspfaq.com/etiquette.asp?id=5006
http://vyaskn.tripod.com/code.htm#inserts
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Unfortunately I do not have the create table SQL statements
What I am trying to accomplish is importing data from an excel file into a
SQL database. To make it easier to work with I simply straight imported the
excel file into a new table in the SQL database (AutoExpt). Accounts is one
of the tables that I need to import into. I do know that the OID for the
accounts table is not automatically generated, and must be generated manually.
In the AutoExpt table there is a listing of vechiles and what accounts
maintenance should be charged to. I can add the vechiles fine to the
Equipment table (Not listed here because it works just fine). However another
table EquipmentFeature is linked to the Equipment table. It contains things
such as account numbers, and the department that the vechile belongs to.
From what I can tell I can not add records to the Equipment feature table
such as the account number because they do not exist in the Accounts table.
So the solution is to first add all the distinct account numbers into the
accounts table. This also applies to the departments table as well.
I do have a backup of the database so I can fool aroudn with it all I want
without worry of data loss or damage, plus I am working on a dev server so no
actual users are accessing the database. I found that I can add duplicate
records in the accounts table and the departments table, however I have found
that I can't remove some duplicates in the departments table because it
claims that a record needs to exist in the General table first.
My theory is that if I add only the distinct account numbers and departments
I should be fine, and it should stop telling me that "A record must exist in
the accounts table first"
Unfortunately the company that makes the software is not offering much
support on importing the data. So I am left to figure it out myself.
"Hugo Kornelis" wrote:

> On Wed, 25 Aug 2004 12:55:03 -0700, Robert wrote:
>
> Hi Robert,
> Please provide DDL (CREATE TABLE statements, with constraints), sample
> data (as INSERT statements), expected output and a description of the
> business problem you're trying to solve.
> http://www.aspfaq.com/etiquette.asp?id=5006
> http://vyaskn.tripod.com/code.htm#inserts
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 26 Aug 2004 05:55:04 -0700, Robert wrote:

>Unfortunately I do not have the create table SQL statements
(snip)
Hi Robert,
Did you read the links I posted in my message? They contain instructions
on how you can generate the create table statements from the existing
tables (the ewb site shows how it's done with Query Analyzer; there's an
option to generate SQL in Enterprise Manager as well).
I read your description, but I can't figure out what you're trying to do
and what problems you are trying to tackle. I really need the table
structure and sample data to understand your problem.
(BTW, if therre are legal or other issues that prohibit you from posting
the table structure, you may also create a fake table that recreates the
problem).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I can't post the structure or data because it's against our policies. I can
provide you something that looks similair. I basically need to be able to
insert only the unique account numbers from one table into another table,
however at the same time inserting a unique primary key into the same
destination table.
For example I kinda have something like this:
Primary Key From Table 1, Account Number
1,A
2,A
3,B
4,C
5,B
I need to put that information in a seperate table that is similair to this:
Primary From table2, Account Number
-564564,K
564578,D
7246,S
54567,F
The second table can not have any duplicate account numbers and of course
the Primary key.
What I want to do is select from table1 something looking like this:
Primary Key From Table 1, Account Number
1,A
3,B
4,C
Leaving me only with unique primary keys and account numbers.
The end result for table2 should look something like this:
-564564,K
564578,D
7246,S
54567,F
1,A
3,B
4,C
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 05:55:04 -0700, Robert wrote:
> (snip)
> Hi Robert,
> Did you read the links I posted in my message? They contain instructions
> on how you can generate the create table statements from the existing
> tables (the ewb site shows how it's done with Query Analyzer; there's an
> option to generate SQL in Enterprise Manager as well).
> I read your description, but I can't figure out what you're trying to do
> and what problems you are trying to tackle. I really need the table
> structure and sample data to understand your problem.
> (BTW, if therre are legal or other issues that prohibit you from posting
> the table structure, you may also create a fake table that recreates the
> problem).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 26 Aug 2004 08:39:12 -0700, Robert wrote:

>I can't post the structure or data because it's against our policies. I can
>provide you something that looks similair. I basically need to be able to
>insert only the unique account numbers from one table into another table,
>however at the same time inserting a unique primary key into the same
>destination table.
>For example I kinda have something like this:
>Primary Key From Table 1, Account Number
>1,A
>2,A
>3,B
>4,C
>5,B
>I need to put that information in a seperate table that is similair to this:
>Primary From table2, Account Number
>-564564,K
>564578,D
>7246,S
>54567,F
>The second table can not have any duplicate account numbers and of course
>the Primary key.
>What I want to do is select from table1 something looking like this:
>Primary Key From Table 1, Account Number
>1,A
>3,B
>4,C
>Leaving me only with unique primary keys and account numbers.
>The end result for table2 should look something like this:
>-564564,K
>564578,D
>7246,S
>54567,F
>1,A
>3,B
>4,C
Hi Robert,
The following is untested as I only test solutions I provide in newsgroups
if I can copy and paste CREATE TABLE and INSERT statements. I like solving
problems and helping people, but I detest typing - I have to do plenty of
typing in my job :-)
But try if this gives you what you need:
INSERT INTO table2 (PrimKeyFromTable2, AccountNumber)
SELECT MIN(PrimKeyFromTable1), AccountNumber
FROM Table1
GROUP BY AccountNumber
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||That did the trick, I should have thought about grouping them.
Hugo... Your a genius!
Thanks,
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 08:39:12 -0700, Robert wrote:
>
> Hi Robert,
> The following is untested as I only test solutions I provide in newsgroups
> if I can copy and paste CREATE TABLE and INSERT statements. I like solving
> problems and helping people, but I detest typing - I have to do plenty of
> typing in my job :-)
> But try if this gives you what you need:
> INSERT INTO table2 (PrimKeyFromTable2, AccountNumber)
> SELECT MIN(PrimKeyFromTable1), AccountNumber
> FROM Table1
> GROUP BY AccountNumber
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Distinct insert

Have you tried something like:
insert into Accounts (AccountsOID,Account)
select min( "Rec NO" ) + 100 as AccountsOID, "LIC
TAG" as Account from AutoExpt group by "LIC TAG"
grahamdnle@.yahoo.com
>--Original Message--
>I am trying to insert a record id primary at the same
time that I am trying
>to insert a Distinct Account number.
>The insert statement is something kind of like this:
>insert into Accounts (AccountsOID,Account)
>select Distinct "Rec NO" + 100 as
AccountsOID, "LIC TAG" as Account from
>AutoExpt
>In the AutoExpt table there are multiple occurrences
of "LIC TAG" values all
>of which need to be put into the Accounts table, however
there must be only
>one entry for the Lic tag in the accounts table. The Rec
No +100 is just to
>create a unique primary key for the AccountsOID.
>Is there anyway I can get only the Distinct Lic Tag's,
but at the same time
>get a unique primary key for them.
>Thanks,
>.
>
Actualy That looks very much like what Hugo came up with. I just did'nt think
about grouping the "Lic Tag" field.
I've remembered this so I'll be sure to do it again if I have to do this
again.
"Dan" wrote:

> Have you tried something like:
> insert into Accounts (AccountsOID,Account)
> select min( "Rec NO" ) + 100 as AccountsOID, "LIC
> TAG" as Account from AutoExpt group by "LIC TAG"
> grahamdnle@.yahoo.com
> time that I am trying
> AccountsOID, "LIC TAG" as Account from
> of "LIC TAG" values all
> there must be only
> No +100 is just to
> but at the same time
>

Displaying error when using insert command

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:intranetnewConnectionString %>"

InsertCommand="INSERT INTO timeoffcalc(Typeoftime, amountoftime, employeeID) VALUES (@.TypeofTime, @.Amountoftime, @.emplID)"

I have the emplyID and Typeof time as a PK. When the user enters a duplicate value it just gives them the error page. Can i set a label or something to notify the user of the error instead of the error page?

UseTry- Catch block .. in the catch block put customized error message.

Hope it helps..

|||Problem is, I dont know how to do that when using a command object. All i have is the insertcommand function. Where would i put it?|||

try

whatever.insert

catch ex as exception

lblError.text=ex.message

end try

|||

Boy I feel dumb. I am using details view with the insert. I dont see where the command is fired in the code. I click insert in the details view and it does it. Where would i put that code. I know how to do a try catch, but i am confused to where the code is fired with the click, here is my code.

<asp:SqlDataSource

ID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:intranetnewConnectionString %>"

SelectCommand="SELECT emplID, lname + ' , ' + fname AS Name FROM employeemain ORDER BY lname">

</asp:SqlDataSource>

<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"BackColor="White"

BorderColor="#DEDFDE"BorderStyle="None"BorderWidth="1px"CellPadding="4"DataSourceID="SqlDataSource2"

DefaultMode="Insert"ForeColor="Black"GridLines="Horizontal"Height="50px"Width="125px"DataKeyNames="emplID">

<FooterStyleBackColor="#CCCC99"/>

<EditRowStyleBackColor="#CE5D5A"Font-Bold="True"ForeColor="White"/>

<RowStyleBackColor="#F7F7DE"/>

<PagerStyleBackColor="#F7F7DE"ForeColor="Black"HorizontalAlign="Right"/>

<Fields>

<asp:TemplateFieldHeaderText="Employee's Name"SortExpression="emplID">

<EditItemTemplate>

<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("emplID") %>'></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>

<asp:DropDownListID="DropDownList2"runat="server"DataSourceID="SqlDataSource1"

DataTextField="Name"DataValueField="emplID"SelectedValue='<%# Bind("emplID") %>'>

</asp:DropDownList>

</InsertItemTemplate>

<ItemTemplate>

<asp:LabelID="Label2"runat="server"Text='<%# Bind("emplID") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Type of Time"SortExpression="Typeoftime">

<EditItemTemplate>

<asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("Typeoftime") %>'></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>

<asp:DropDownListID="DropDownList1"runat="server"SelectedValue='<%# Bind("Typeoftime") %>'>

<asp:ListItem>Vacation</asp:ListItem>

<asp:ListItem>Personal</asp:ListItem>

<asp:ListItem>Sick</asp:ListItem>

<asp:ListItem></asp:ListItem>

</asp:DropDownList>

</InsertItemTemplate>

<ItemTemplate>

<asp:LabelID="Label1"runat="server"Text='<%# Bind("Typeoftime") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundFieldDataField="amountoftime"HeaderText="Amount per Month"SortExpression="amountoftime"/>

<asp:CommandFieldShowInsertButton="True"/>

</Fields>

<HeaderStyleBackColor="#6B696B"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:intranetnewConnectionString %>"

InsertCommand="INSERT INTO timeoffcalc(Typeoftime, amountoftime, employeeID) VALUES (@.TypeofTime, @.Amountoftime, @.emplID)"

SelectCommand="SELECT timeoffcalc.Typeoftime, timeoffcalc.amountoftime, employeemain.emplID FROM employeemain INNER JOIN timeoffcalc ON employeemain.emplID = timeoffcalc.employeeID">

<InsertParameters>

<asp:ParameterName="TypeofTime"/>

<asp:ParameterName="Amountoftime"/>

<asp:ParameterName="emplID"/>

</InsertParameters>

</asp:SqlDataSource>

<asp:LabelID="Label3"runat="server"Font-Italic="True"Font-Size="Large"ForeColor="Red"

Text="It has been Added"Visible="False"></asp:Label><br/>

<asp:ButtonID="Button1"runat="server"Text="Add Another"Visible="False"/><br/>

</asp:Content>

|||

Ah...

Well try catching the sqldatasource2_inserted event, I believe you can catch the errors there, and then set the e parameter to something to say you handled the error, but I've never actually done it.

display string having length greater than 255

I have a created a table and entered data into the table as follows:

CREATE TABLE t ( id INT , txtcol varchar(1000) )

INSERT INTO t ( id , txtcol ) VALUES ( 1 , 'ATXR_SOURCE_ID,CDDL_AG_PRICE,CDDL_ALLOW,CDDL_ALTDP_EXCD_ID,CDDL_CAP_IND,CDDL_CHG_AMT,CDDL_COINS_AMT,CDDL_CONSIDER_CHG,CDDL_COPAY_AMT,CDDL_DED_AC_NO,CDDL_DED_AMT,CDDL_DIS_PA_LIAB,CDDL_DISALL_AMT,CDDL_DISALL_EXCD,CDDL_DISC_AMT,CDDL_DP_PRICE,CDDL_FROM_DT,CDDL_PAID_AMT,CDDL_PF_PRICE,CDDL_PR_PYMT_AMT,CDDL_PRICE_IND,CDDL_REF_IND,CDDL_RISK_WH_AMT,CDDL_SB_PYMT_AMT,CDDL_SURF,CDDL_TOOTH_BEG,CDDL_TOOTH_END,CDDL_TOOTH_NO,CDDL_TOT_PA_LIAB,CDDL_UNITS,CDDL_UNITS_ALLOW,CGCG_ID,CGCG_RULE,DPCG_DP_ID_ALT,DPDP_ID,DPTC_CD,PDVC_LOBD_PTR,PSDC_ID,UTUT_CD' )

Now if i select data using the query below the txtcol field displays only 255 characters :

SELECT * FROM t

Y is this happening?

This is happening because of your client application (QA, SSMS or something else) has a setting not to display field values grater than 255 bytes. Inspect application options, then.

Display Report Title in Report

Hello,
I am trying to display the report title with from and to dates. I currently have start and end dates as parameters and when I insert into my report in the 'Design' view the dates do not appear when refreshed. Please provide how to steps. Help!!!!
JSjust drag and drop ur date parameters in page header and preview report, it will prompt you to enter parameter values...