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 Records

Hi All,
i want to Write the query which will give me the distinct top 1 records.
see i have my table and data in that table is in this way.
ReqNo Name Other
1 chirag
1 xyz
1 ABC
2 ZZZ
2 YYY
2 QQQ
3 NNN
i want the output as following
1 Chirag
2 ZZZ
3 NNN
the first records of each and every Reqno. i am trying this from a long time
but not able to get the same if you guys has any solution then please help m
e
out.Try this
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from MyTest group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hi
It should be
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from TableName
group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hello Chirag
You can do it as Madhivanan said if you have not created any views based on
this table. If you have any views based on this table then alterting the
table may affect the views. Views become invalid. In that case you can do
like this.
Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
Insert into #Temp Select * From TableName
Select * from #Temp where Slno in (Select min(Slno) from #Temp
group by ReqNo)
#Temp is a temporary table. So it will be deleted automatically.
Thank you
Baiju
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
> Hi
> It should be
> Alter table TableName add sno int identity
> go
> Select * from TableName where sno in (Select min(sno) from TableName
> group
> by ReqNo)
> go
> Alter table TableName drop column sno
>
> Madhivanan
>|||I would suggest you to create a running number and have it permantently.
creating a temp table, inserting data and doing a select on that, deleting
temp table..will be a workable solution..
but if your table has large number of records, this query takes its own
time..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Baiju" <baiju@.indus-systems.com> wrote in message
news:O#r6yd8HFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hello Chirag
> You can do it as Madhivanan said if you have not created any views based
on
> this table. If you have any views based on this table then alterting the
> table may affect the views. Views become invalid. In that case you can do
> like this.
> Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
> Insert into #Temp Select * From TableName
> Select * from #Temp where Slno in (Select min(Slno) from #Temp
> group by ReqNo)
> #Temp is a temporary table. So it will be deleted automatically.
> Thank you
> Baiju
>
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
>|||try
select ReqNo, Max(Name) from ... group by ReqNo
"Chirag" wrote:

> Hi All,
> i want to Write the query which will give me the distinct top 1 records.
> see i have my table and data in that table is in this way.
> ReqNo Name Other
> 1 chirag
> 1 xyz
> 1 ABC
> 2 ZZZ
> 2 YYY
> 2 QQQ
> 3 NNN
> i want the output as following
> 1 Chirag
> 2 ZZZ
> 3 NNN
>
> the first records of each and every Reqno. i am trying this from a long ti
me
> but not able to get the same if you guys has any solution then please help
me
> out.|||If those are the only columns then there's no such thing as the "first"
for each ReqNo. You haven't identified "first" in your table and tables
have no fixed concept of order. The best you can do is probably with an
aggregate function:
SELECT reqno, MIN(name)
FROM YourTable
GROUP BY reqno
David Portas
SQL Server MVP
--

Distinct issue

Hi

I wanna write a proc that returns Distinct CustomerID's in a table and returns result in output parameter

When i try this, i get error - incorrect syntax near distinct. Any ideas??

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
select
@.CustCount = distinct(CustomerID)
from
Orders
Where
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

hi,

what you want to return, count of distinct ids or distinct ids itself ?

regards,

satish

|||

hehe.. er ofcourse. I wanted to count the distinct ids, but i didnt count

i worked it out

cheers brother

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
SELECT
@.CustCount =Count(DISTINCT(CustomerID))
FROM
Orders
WHERE
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

|||

good you got it working :),

cheers ,

satish

Distinct in Common Table Expressions CTE

I have managed to write my first CTE SQL that handles recursion but I have a problem with distinct - can't get that to work!!

My CTE:

WITH StudentsHierarchy(SystemID1, GroupID, AccessType, AccessGroupID, StudentID, HierarchyLevel) AS(--Base CaseSELECT SystemID,GroupID,AccessType,AccessGroupID,StudentID,1 as HierarchyLevelFROM UserGroup aUNION ALL--Recursive stepSELECT u.SystemID,u.GroupID,u.AccessType,u.AccessGroupID,u.StudentID,uh.HierarchyLevel + 1 as HierarchyLevelFROM UserGroup uINNER JOIN StudentsHierarchy uh ONu.GroupID = uh.AccessGroupID)Select sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student swhere sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy shWHERE AccessType = 'S'

and I would like to have a distinct on the StudentID like:

Select DISTINCT sh.StudentID, sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student swhere sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy shWHERE AccessType = 'S'

How should I do?

is it possible to bump this?sql

Displaying the output

Hi,
I want to write a query such that part of the output is displayed and the
complete output is put into a temp table.
For example i have a table customer and i want the total customer details to
be put in a temp table and the top 5 to be displayed.
select * into #temp1 from customer
select top5 * from customer
I run this two queries for my result, but is there any way to get the result
in one query.
Thanks,
MaheshMahesh wrote:
> Hi,
> I want to write a query such that part of the output is displayed
> and the complete output is put into a temp table.
> For example i have a table customer and i want the total customer
> details to be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the
> result in one query.
> Thanks,
> Mahesh
No. You are talking about two different queries. You need to use an
ORDER BY with the TOP clause, unless you want somewhat random data
returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
My requirement is when i run a query it should go to temp table (e.g.
#temp1) and also display the output. This should happen on a single run of
query. Can you please help me in this.
Thanks,
Mahesh
"David Gugick" wrote:

> Mahesh wrote:
> No. You are talking about two different queries. You need to use an
> ORDER BY with the TOP clause, unless you want somewhat random data
> returned.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Mahesh
can you explain why you want to do that.
however it appears to be possible with functoon or procedure
--procedure
CREATE PROC DISPLAY
AS
select * into #temp1 from customer
select top5 * from customer ORDER BY CUSTOMER_ID
RETURN
GO
EXEC DISPLAY
GO
--function: you can't use temp table here but table variable
CREATE FUNCTION DISPLAYFUNCTION()
RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
AS
BEGIN
INSERT INTO @.TOPTABLE
SELECT * FROM EMPLOYEES
RETURN
END
GO
SELECT TOP 5 * FROM DISPLAYFUNCTION()
GO
Regards
R.D
--Knowledge gets doubled when shared
"Mahesh" wrote:

> Hi,
> I want to write a query such that part of the output is displayed and t
he
> complete output is put into a temp table.
> For example i have a table customer and i want the total customer details
to
> be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the resu
lt
> in one query.
> Thanks,
> Mahesh|||space between top and 5 is missing; TOP 5
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Mahesh
> can you explain why you want to do that.
> however it appears to be possible with functoon or procedure
> --procedure
> CREATE PROC DISPLAY
> AS
> select * into #temp1 from customer
> select top5 * from customer ORDER BY CUSTOMER_ID
> RETURN
> GO
> EXEC DISPLAY
> GO
>
> --function: you can't use temp table here but table variable
> CREATE FUNCTION DISPLAYFUNCTION()
> RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
> AS
> BEGIN
> INSERT INTO @.TOPTABLE
> SELECT * FROM EMPLOYEES
> RETURN
> END
> GO
> SELECT TOP 5 * FROM DISPLAYFUNCTION()
> GO
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Mahesh" wrote:
>|||RD, this is requires because i will be running multiple queries to analyze
the data, while i am running i would like to see the data and then when at
the end if it is stored in a temp table then i can run a simple select on
that temp table instead of running the whole query again. can you please hel
p
me on this.
Thanks,
Mahesh
"R.D" wrote:
> Mahesh
> can you explain why you want to do that.
> however it appears to be possible with functoon or procedure
> --procedure
> CREATE PROC DISPLAY
> AS
> select * into #temp1 from customer
> select top5 * from customer ORDER BY CUSTOMER_ID
> RETURN
> GO
> EXEC DISPLAY
> GO
>
> --function: you can't use temp table here but table variable
> CREATE FUNCTION DISPLAYFUNCTION()
> RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
> AS
> BEGIN
> INSERT INTO @.TOPTABLE
> SELECT * FROM EMPLOYEES
> RETURN
> END
> GO
> SELECT TOP 5 * FROM DISPLAYFUNCTION()
> GO
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Mahesh" wrote:
>|||David already answered. One query cannot both store the results in a temp ta
ble *and* display the
results. It is one of those, but not both. How about pushing all data into t
he temp table and then
SELECT TOP 5 from there?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mahesh" <Mahesh@.discussions.microsoft.com> wrote in message
news:E23898C7-8D15-492B-9CDE-0CFE24AC44CA@.microsoft.com...
> Hi David,
> My requirement is when i run a query it should go to temp table (e.g.
> #temp1) and also display the output. This should happen on a single run of
> query. Can you please help me in this.
> Thanks,
> Mahesh
> "David Gugick" wrote:
>|||Either you got to use sprocs or use two queries at a time with GO in betwee
n
--
Regards
R.D
--Knowledge gets doubled when shared
"Mahesh" wrote:
> RD, this is requires because i will be running multiple queries to analyze
> the data, while i am running i would like to see the data and then when at
> the end if it is stored in a temp table then i can run a simple select on
> that temp table instead of running the whole query again. can you please h
elp
> me on this.
> Thanks,
> Mahesh
> "R.D" wrote:
>|||Hi,
Try this. I did it with the northwind DB
select * into #temp1 from customers where customerid in (select top 5
customerid from customers)
Nils
"Mahesh" <Mahesh@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1E806702-EB01-459C-AE86-B9E873C8DA22@.microsoft.com...
> Hi,
> I want to write a query such that part of the output is displayed and
> the
> complete output is put into a temp table.
> For example i have a table customer and i want the total customer details
> to
> be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the
> result
> in one query.
> Thanks,
> Mahesh|||nils
> select * into #temp1 from customers where customerid in (select top 5
> customerid from customers)
this inserts only five rows while Mahesh wants all rows to temp and five
rows to be displayed.
--
Regards
R.D
--Knowledge gets doubled when shared
"Nils Wolf" wrote:

> Hi,
> Try this. I did it with the northwind DB
> select * into #temp1 from customers where customerid in (select top 5
> customerid from customers)
> Nils
>
> "Mahesh" <Mahesh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:1E806702-EB01-459C-AE86-B9E873C8DA22@.microsoft.com...
>

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
--

Displaying Auto Sequence Number

I want to write the sql query for displaying sequence number in the result..
.
E.g.
SeqNo Col1 Col2
==== === ===
1 aa aa2
2 bb bb2
3 cc cc2
The firlst column SeqNo is not the physical column of the table...It may be
a single function to retrieve the sequence number along with the result. I
know there may be a single function in SQL server to retrieve auto
sequence....
Any idea?
Thanks in advance
PeterOoSELECT ID, IDENTITY(INT,1,1) AS seq_number into #temp
FROM TableName
select * FROM #temp
DROP TABLE #temp
Hth
DishanF
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||OR
SELECT count(*) RecNum,
a.ID
FROM Agent a join
Agent b
on a.ID >= b.ID
group by a.ID
order by a.ID
DishanF
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Thanks...Can we do another way? This way needs to create a temp table and
I'm wondering if it can affects the performance...
If SQL server has a function like AutoNumber(), there will be nice...
Anyway thanks...And if you have any alternative idea, I'd be appreciated.
"DishanF" wrote:

> SELECT ID, IDENTITY(INT,1,1) AS seq_number into #temp
> FROM TableName
> select * FROM #temp
> DROP TABLE #temp
> Hth
> DishanF
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

Display textboxes depending on rendering format

I would like to display some textboxes depending on the rendering format. I
would like to write an expression like this in the Visibility.Hidden
=renderingFormat="Excel"
Is this possible?In case you are calling Reporting Services from your
Application then just pass an extra parameter from the UI
which has the Rendering Format value and then use the
value of this Textbox in the Report to determine Rendering
Format.
>--Original Message--
>I would like to display some textboxes depending on the
rendering format. I
>would like to write an expression like this in the
Visibility.Hidden
>=renderingFormat="Excel"
>Is this possible?
>.
>|||Eric:
I don't see a solution posted, but I have a similar need. Did you ever come
up with an answer to this?
Vince P
"Eric Quist" wrote:
> Thanks for your suggestion, but I have to support it even if the user uses
> the HtmlViewer and chooses to export the report from there.
>
> "Ravi" wrote:
> > In case you are calling Reporting Services from your
> > Application then just pass an extra parameter from the UI
> > which has the Rendering Format value and then use the
> > value of this Textbox in the Report to determine Rendering
> > Format.
> >
> > >--Original Message--
> > >I would like to display some textboxes depending on the
> > rendering format. I
> > >would like to write an expression like this in the
> > Visibility.Hidden
> > >=renderingFormat="Excel"
> > >
> > >Is this possible?
> > >.
> > >
> >|||I haven't found any good solution to this problem. I guess it must be added
to RS.
/Eric
"vmp_pdx" wrote:
> Eric:
> I don't see a solution posted, but I have a similar need. Did you ever come
> up with an answer to this?
> Vince P
> "Eric Quist" wrote:
> > Thanks for your suggestion, but I have to support it even if the user uses
> > the HtmlViewer and chooses to export the report from there.
> >
> >
> > "Ravi" wrote:
> >
> > > In case you are calling Reporting Services from your
> > > Application then just pass an extra parameter from the UI
> > > which has the Rendering Format value and then use the
> > > value of this Textbox in the Report to determine Rendering
> > > Format.
> > >
> > > >--Original Message--
> > > >I would like to display some textboxes depending on the
> > > rendering format. I
> > > >would like to write an expression like this in the
> > > Visibility.Hidden
> > > >=renderingFormat="Excel"
> > > >
> > > >Is this possible?
> > > >.
> > > >
> > >

Display Number of Records Processed

I've got a stored procedure that processes a TON of records...

What I would liek to do is to write a row to a "progress" table which shows how many rows have actually been processed.

I have a simple counter defined in the procedure:

SET @.COUNTER = 0

Each time the procedure loops through, it increments by 1:

SET @.COUNTER = @.COUNTER + 1

What I would like to do is write rows to a PROCESS table which would reads:

PROCESSED 1000 rows

PROCESSED 2000 rows

PROCESSED ...... rows

etc.

I have a slight idea how to pull this off, but not sure about the whole even number thing by 1000.

If anyone has any insight it would be greatly appreciated!!

Thanks in advance!

Do this:

IF @.COUNTER % 1000 = 0

INSERT .......

|||

Works like a charm!!!

Thanks Tom...

Display Number of Records Processed

I've got a stored procedure that processes a TON of records...

What I would liek to do is to write a row to a "progress" table which shows how many rows have actually been processed.

I have a simple counter defined in the procedure:

SET @.COUNTER = 0

Each time the procedure loops through, it increments by 1:

SET @.COUNTER = @.COUNTER + 1

What I would like to do is write rows to a PROCESS table which would reads:

PROCESSED 1000 rows

PROCESSED 2000 rows

PROCESSED ...... rows

etc.

I have a slight idea how to pull this off, but not sure about the whole even number thing by 1000.

If anyone has any insight it would be greatly appreciated!!

Thanks in advance!

Do this:

IF @.COUNTER % 1000 = 0

INSERT .......

|||

Works like a charm!!!

Thanks Tom...

Display image to ASP .NET from bynary data MSSQL 2000

Hi I have image in database sql server 2000 and want to display in ASP .net page. I make the memorystream from that data and write directly to page using Response.Write([bynary stream]) but U know it only display one image and other HTML is gone.

Normaly a HTML or ASP.net page will display image using local files like this <img src=..........> but how if I want to display it from memory stream that doesn't have physically in storage ? Or should I save that binary to storage first and then reference it ?

So anyone that have solution for this problem please help me how to this thing .........

Hi, Take a lok at the MSDN articles on images and bitmaps.

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

regards

smcoxon

|||

You don't understand. I'm not ask about image !

My question is how to display a binary image in memorystream to ASP.NET page via HTTP ?

I try to directly write by HttpResponse.Write( ... ) but U know it display the image only......

What I want is this image can be displayed in ASP.net page ......, I can't use <img src='.......'> ,

because it's on memory stream not in URL format or in local file in your hard drive.

|||

Hi,

smcoxon has given part of the answer but that is related to winforms. The picture box can accept the byte stream that you get from the binary datatype you stored the image in.

For aspx pages.

You have to first create a page which just serves the images.

so your image tag would be

<img src="http://pics.10026.com/?src=YourImageServingPage.aspx?imageID=1" ...

On YourImageServingPage.aspx

You would remove all mark up and then add this to your code behind

Response.Clear();
Response.ContentType="image/jpeg"; or any image type you are serving.

You would create a BitMap class load your bytes from database and write the image to the Response.Write stream.

You would find many articles on this Search Key: dynamic image generation

Happy programming,
Anton