Database

6-Jun-10 23:02

I have two tables with the following structure.

Table 1

Name Field1

Table 2

ID Name Field1

1    n1     NULL
2    n2     NULL	
3    n3     NULL

Usually, Table1.Name and Table1.Field1 have a 1..1 relationship. however in very-rare circumstances they do tend to have 1..n

I need to update Table2.Field1 with Table1.Field1; In SQL Server we could write the following update statement to satisfy our business conditions.

UPDATE Table2 SET Table2.Field1 = T1.Field1<br />
	FROM Table1 t1, Table2 t2<br />
	WHERE t1.Name = t2.Name;<br />

In oracle, we were having the below statement.

UPDATE Table2 SET Field2 = (SELECT t1.FIELD1 FROM Table1 t1, Table2 t2<br />
	WHERE t1.Name = t2.Name);

This statement works for Table1 having 1..1 relationship between Table1.Name and Table1.Field1
For 1..n relationship the ORACLE version fails with the following error - ORA-01427: single-row subquery returns more than one row

The SQLServer sql works for both the cases 1..1 and 1..n; given the nature of the join.

Could someone guide me with appropriate sql for the above issue?
I am looking for a bulk update statement rather than using PL/SQL to go through each recrod in Table2.

Thanks for your help.
Kalyan

modified on Monday, June 7, 2010 10:55 AM

Mycroft Holmes6-Jun-10 23:33

6-Jun-10 23:33

Fairly obvious, your sub select statement is returning 2 rows for n2, this is illegal. try putting DISTINCT in there

UPDATE Table2 SET Field2 = (SELECT DISTINCT t1.FIELD1 FROM Table1 t1, Table2 t2
WHERE t1.Name = t2.Name);

Never underestimate the power of human stupidity
RAH

Kalyan_A6-Jun-10 23:41

6-Jun-10 23:41

Thanks for your reply.

I do know that multiple values is what is causing this issue.
Using DISTINCT would not work as the values being returned are distinct already. It would still return 2 rows for n2

This issue is due to ORACLE not supporting the same syntax as SQLSERVER. (as far as i know)

I am more comfortable with SQLServer than ORACLE and hence was checking if anyone else came across this issue.

Kalyan

Mycroft Holmes7-Jun-10 0:53

7-Jun-10 0:53

Sorry, looking at it from the wrong end.

What do you expect the table2 field2 (missing from design given) to contain b or c and what does it contian when you use SQL Server, I'm betting tt has c.

Does Oracle not support update with a join, try using INNER JOIN style instead of the tables in the where clause (horrible format)

No matter how you work it your data structue sucks. the relationship is screwed and I consider Oracle correct in spitting the dummy.

Never underestimate the power of human stupidity
RAH

Kalyan_A7-Jun-10 5:12

7-Jun-10 5:12

you are right - (for now) its not important as to which value gets updated. The SQLServer version of the script simply updates the t2.Field with the last found record. So for 'n2' t2.Field1 will always end-up with value 'c'
I was looking for an equivalent in ORACLE so that it will always update with the last row found.

The actual issue is much bigger and i was looking for a quick-fix without introducing a major change.
Looks like we need to address the issue with the current design before spending any further time on this issue,

Thanks for your inputs.

Cheers
Kalyan

David Skelly7-Jun-10 2:50

David Skelly

7-Jun-10 2:50

I'm confused. Given the example data you have given above, what value would you expect to see in Table2.Field1 for row id 2? It could be either b or c. Which one do you want and how would you distinguish between them?

If you don't care, then you could just say SELECT MAX(t1.FIELD1). That would give you one value.

As far as I can see with your current SQL Server statement you have no control over which value goes in there, so I'm guessing it's not important to you.

Kalyan_A7-Jun-10 5:11

7-Jun-10 5:11

RTF to TEXT

Sebastian T Xavier6-Jun-10 20:45

6-Jun-10 20:45

I have a RTF field in my SQL 2005 table, I need to convert it to Text and display it. After a quick research I got the following method... create function dbo.RTF2TXT(@in varchar(8000)) RETURNS varchar(8000) AS BEGIN

DECLARE @object int DECLARE @hr int DECLARE @out varchar(8000)

-- Create an object that points to the SQL Server EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT EXEC @hr = sp_OADestroy @object return @out

END GO

select dbo.RTF2TXT('{\rtf1\ansi\ansicpg1252\uc1 aaa}')

But Here I am getting only NULL as result... What could be the issue, please suggest

Thanks

Mycroft Holmes6-Jun-10 23:29

6-Jun-10 23:29

Well if the line wrap has not deceived me then the function does nothing because it is commented out.

You seem to have 4 EXEC strung together in a single line that is commented out!

Do you have any idea what you are doing, sp_OACreate is an OLE Automation procedure. I can't use it because of security permissions so I would not be surprised if that will also kill this.

Never underestimate the power of human stupidity
RAH

Sebastian T Xavier6-Jun-10 23:40

6-Jun-10 23:40

Thanks for your response.
I don't think, the line wrap will create issues. I have removed those comments & tried it.
Actually I was trying to convert the Rich text type string to plain text while
fetching records.
Can you suggest any better approach to convert a string from rich text to plain text in sql server.

Thanks once again

Sebastian

Sebastian T Xavier6-Jun-10 23:43

6-Jun-10 23:43

Hello, One more thing

can't we use following code to allow execution of sp_OACreate

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Thanks

Sebastian

J4amieC6-Jun-10 23:42

J4amieC

6-Jun-10 23:42

sp_OACreate is the devil.

This is NOT something that a database should be doing - load the RTF data to your business layer and convert it there.

Sebastian T Xavier6-Jun-10 23:51

6-Jun-10 23:51

Thanks for your reply.

This method also I have tried.
What I have intended to do is to create a class library , add the following code;

public String ConvertRtftoText(RichTextBox rtb)
        {
            String strText = "";
            try
            {
                strText = rtb.Text;
            }
            catch (Exception Ex)
            {
                // do nothing
            }
            return strText;
        }

& call this as a CLR stored procedure.
But the problem which I faced is , when I tried to execute the below code

CREATE ASSEMBLY getTextfromRTF
from '\\ustech39\Shared\BackendRTFHandler.dll'
WITH PERMISSION_SET = UNSAFE

I got another error

Warning: The Microsoft .Net frameworks assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 10301, Level 16, State 1, Line 1
Assembly 'BackendRTFHandler' references assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

modified on Monday, June 7, 2010 9:19 AM

Mycroft Holmes7-Jun-10 0:56

7-Jun-10 0:56

So do as Jamie suggested, move the conversion to your business layer where it belongs and get it out of the database.

Why do you want to inflict another layer of abstraction between you nd your data, inplementing the CLR for this is just wrong.

Never underestimate the power of human stupidity
RAH

Sebastian T Xavier7-Jun-10 1:22

7-Jun-10 1:22

Hi Mycroft Holmes
My intension was to avoid looping through the records in front end.
thanks for the suggestion

Sebastian

Mycroft Holmes7-Jun-10 1:30

7-Jun-10 1:30

IMHO you have your priorities completely wrong, to save looping the records you are willing to saddle your self with OLE or the CLR, a cursor would be better than that and I hate cursors.

You could convert the content to varchar and then check each character for a-z and punctuation. Nasty but should be doable.

Never underestimate the power of human stupidity
RAH

Eddy Vluggen7-Jun-10 1:59

Eddy Vluggen

7-Jun-10 1:59

Sebastian T Xavier wrote:
My intension was to avoid looping through the records in front end.

Why not do the conversion on the fly, just before the data is required in the BL? Does the data "have" to be re-stored in the database as plain text?

If the answer is yes, consider writing a specialized application to do the conversion once. Another alternative would be a CLR-UDF in Sql Server; you'd load the RTF text into a RichTextBox and return it's plain-text contents.

I are Troll Suspicious | :suss:

Sebastian T Xavier7-Jun-10 3:19

7-Jun-10 3:19

Hello Eddy,

please see an earlier post in this chain..

Click

Will this same issue happen to CLR-UDF?
please advise

Thanks for your thoughts

Sebastian

Eddy Vluggen7-Jun-10 3:48

Eddy Vluggen

7-Jun-10 3:48

Sebastian T Xavier wrote:
Will this same issue happen to CLR-UDF?

Aw, yes; the WinForms assembly isn't considered "safe" in Sql Server. Installing the CLR-UDF (when using WinForms components) is described here[^], along with some other viable solutions.

I are Troll Suspicious | :suss:

Sebastian T Xavier7-Jun-10 4:25

7-Jun-10 4:25

Good information....
Thank you

Sebastian