Re: RTF to TEXT - Database Discussion Boards

J4amieC6-Jun-10 23:42

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.

Re: RTF to TEXT

Sebastian T Xavier6-Jun-10 23:51

Sebastian T Xavier

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

Re: RTF to TEXT

Mycroft Holmes7-Jun-10 0:56

Mycroft Holmes

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

Re: RTF to TEXT

Sebastian T Xavier7-Jun-10 1:22

Sebastian T Xavier

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

Re: RTF to TEXT

Mycroft Holmes7-Jun-10 1:30

Mycroft Holmes

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

Re: RTF to TEXT

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:

Re: RTF to TEXT

Sebastian T Xavier7-Jun-10 3:19

Sebastian T Xavier

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

Re: RTF to TEXT

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:

Re: RTF to TEXT

Sebastian T Xavier7-Jun-10 4:25

Sebastian T Xavier

7-Jun-10 4:25

Good information....
Thank you

Sebastian

Re: RTF to TEXT

Eddy Vluggen7-Jun-10 4:59

Eddy Vluggen

7-Jun-10 4:59

You're welcome Smile | :)

Has anyone run SQL Server 2000 Developer's Edition on Windows 2008?

Xiangyang Liu 刘向阳3-Jun-10 12:52

Xiangyang Liu 刘向阳

3-Jun-10 12:52

Has anyone run SQL Server 2000 Developer's Edition on Windows 2008? If yes, any problem?

My .NET Business Application Framework
My Younger Son & His "PET"

INLINE FUNCTION

It_tech3-Jun-10 4:18

It_tech

3-Jun-10 4:18

Hi all,

I have a problem in getting the one below to work. I think i have used the right syntax. But keeps coming with error on EXEC Lline...

CREATE FUNCTION Summary (@Month varchar(30),@Year varchar(30),@ShowType varchar(30))
RETURNS TABLE
AS
RETURN
With
EXECUTE C_WEB_sp_RevenueSummaryByShow 'April', '2010', '3'

Go

Thansk for your time

Re: INLINE FUNCTION

J4amieC3-Jun-10 4:39

J4amieC

3-Jun-10 4:39

It_tech wrote:
But keeps coming with error on EXEC Lline...

Do you think the error message might help us debug your problem?

Re: INLINE FUNCTION

It_tech3-Jun-10 5:51

It_tech

3-Jun-10 5:51

Many thanks for your help.

Here is the error message:

Msg 156, Level 15, State 1, Procedure Summary, Line 6
Incorrect syntax near the keyword 'EXECUTE'.

Re: INLINE FUNCTION

J4amieC3-Jun-10 6:06

J4amieC

3-Jun-10 6:06

The error message is somewhat descriptive. The main problem you're having is that you wont be able to return the result of a stored procedure call from a function. Its just not possible in SQLServer.

Re: INLINE FUNCTION

It_tech3-Jun-10 6:26

It_tech

3-Jun-10 6:26

Thanks Once again.Its Much appreciated.What is it about extended Stored Procedures?I hope we can execute them in table valued functions.

Re: INLINE FUNCTION

J4amieC3-Jun-10 6:34

J4amieC

3-Jun-10 6:34

As far as I know, you cant execute any SP's in a Function.

Re: INLINE FUNCTION

It_tech3-Jun-10 7:00

It_tech

3-Jun-10 7:00

Thanks.What is the way around then?

Re: INLINE FUNCTION

Mycroft Holmes3-Jun-10 14:20

Mycroft Holmes

3-Jun-10 14:20

Don't use a function!

Never underestimate the power of human stupidity
RAH

Re: INLINE FUNCTION

J4amieC3-Jun-10 21:30

J4amieC

3-Jun-10 21:30

Without knowing what you're trying to achieve, I cant answer that.

Re: INLINE FUNCTION

It_tech3-Jun-10 23:45

It_tech

3-Jun-10 23:45

Hi There,

I tried changing it to a Stored procedure but still have a problem

ALTER PROCEDURE [dbo].[sp_LiveRevenueGraph]
-- Add the parameters for the stored procedure here
@ShowType NVARCHAR(20),
@Statistic NVARCHAR(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @data TABLE ([Week] NVARCHAR(120), Sunday DECIMAL(12,5), Monday DECIMAL(12,5), Tuesday DECIMAL(12,5), Wednesday DECIMAL(12,5),
Thursday DECIMAL(12,5), Friday DECIMAL(12,5), Saturday DECIMAL(12,5), SubTotal DECIMAL(12,5))

-- Insert statements for procedure here
-- INSERT INTO @data EXECUTE C_WEB_sp_RevenueAchievedByShow 0, @ShowType;

END

Error : --INSERT EXEC statement cannot be nested

Please advice.

Thanks

Re: INLINE FUNCTION

J4amieC4-Jun-10 0:02

J4amieC

4-Jun-10 0:02

This is the answer you're looking for:

http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure[^]

Google is your friend for this sort of question. Learn to use it.

Re: INLINE FUNCTION

It_tech4-Jun-10 0:35

It_tech

4-Jun-10 0:35

Thanks very much for your help.

I have changed it to the following But seems to have problem with My server Name when using Open Query

Declare @ServName nvarchar(50)
set @ServName= (select @@SERVERNAME)
select @ServName
SELECT * INTO #data FROM OPENQUERY(@ServName, 'EXEc C_WEB_sp_RevenueAchievedByShow 0, @ShowType')

Error : Msg 102, Level 15, State 1, Procedure sp_LiveRevenueGraph, Line 22
Incorrect syntax near '@ServName

Re: INLINE FUNCTION

J4amieC4-Jun-10 0:57

J4amieC

4-Jun-10 0:57

There is no possible way for me to debug that. I have no access to your database, no knowledge of what you're doing. I think you need to spend some time debugging your problem yourself.

Re: INLINE FUNCTION

Eddy Vluggen4-Jun-10 1:48

Eddy Vluggen

4-Jun-10 1:48

Hi,

Just tried it, the code below works for me on the AdventureWorks database;

SQL

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * 
  INTO #MyTempTable 
  FROM OPENROWSET(
	'SQLNCLI', 
	'Server=(local);Trusted_Connection=yes;', 
	'EXEC AdventureWorks.dbo.uspGetEmployeeManagers 1'
  )

Hope this helps Smile | :)

I are Troll Suspicious | :suss:

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.