Fetch text from HTML [URGENT]

Hello everybody,

I have data in my sql server table that is entered with a rich text editor so it contains html formatting. I need to show this data on the form with the formatting user has specified while entering the data. On the crystal report, I need to show the data not in the html format but simple text. I created a stored procedure to fetch the data and binded the report with the stored procedure. When data is printed on the report, it shows HTML tags with the data. Is there a way to fetch text in SQL so that report will only show text without HTML formatting?

Thanks.

It is stored a just a string of characters. It is retreived as just a string of characters. (Some of those characters are html coding characters.

You may wish to create a User Defined Function (UDF) that will strip out the html characters and can be used when necessary.

Here is an example of how you might create and use such a function. (It is incomplete -I'm sure you will be able to add the additional html coding...)

CREATE FUNCTION dbo.StripHTML
( @.StringIn varchar(500) )
RETURNS varchar(500)
AS
BEGIN
SET @.StringIn = replace( replace( @.StringIn, '

', '' ), '

', '' )
SET @.StringIn = replace( replace( @.StringIn, '<b>', '' ), '</b>', '' )
-- add addition html codes here
RETURN ( @.StringIn )
END
GO

SELECT dbo.StripHTML('

This is a <b>Test</b>

')

|||

I was looking for a quicker and easier way to do it (if there is something). As you said, I am writing my own UDF to parse HTML.

Thanks a lot.

|||

You can use the following code to replace all the HTML tags....

CREATE Function dbo.Html2Text(@.HtmlString As Varchar(8000))
Returns Varchar(8000)
as
Begin
Declare @.TagString As Varchar(8000)
Declare @.TagStart As int
Declare @.TagEnd As int

Select @.TagStart = CharIndex('<', @.HtmlString),
@.TagEnd = CharIndex('>', @.HtmlString)
While @.TagStart <> 0 And @.TagEnd <> 0 And @.TagEnd > @.TagStart
Begin
Select @.TagString = Substring(@.HtmlString, @.TagStart, @.TagEnd - @.TagStart + 1)
Select @.HtmlString = Replace(@.HtmlString, @.TagString, '')
Select @.TagStart = CharIndex('<', @.HtmlString),
@.TagEnd = CharIndex('>', @.HtmlString)
End

Select @.HtmlString = Replace(@.HtmlString, '&nbsp;', ' ')
Select @.HtmlString = Replace(@.HtmlString, '&amp;', '&')
Select @.HtmlString = Replace(@.HtmlString, '&quot;', '''')
Select @.HtmlString = Replace(@.HtmlString, '&#', '#')
Select @.HtmlString = Replace(@.HtmlString, '&lt;', '<')
Select @.HtmlString = Replace(@.HtmlString, '&gt;', '>')
Select @.HtmlString = Replace(@.HtmlString, '%20', ' ')
Select @.HtmlString = Replace(@.HtmlString, Char(10), '')
Select @.HtmlString = Replace(@.HtmlString, Char(13), '')
Select @.HtmlString = LTrim(RTrim(@.HtmlString))

Return @.HtmlString
End

Go

Select dbo.Html2Text('

Test&nbsp;Data

'); -- Result :Test Data

failure in starting the process for the user instance

High everybody,

I have a set of winforms (.NET 2.0 assemblies) ( 6 total ) that each tries to open a connection to the same Database file using the same connection string. In the connection string, UserInstance is set to true.

If each winform is started manually after opening the session on the computer, the connection to the database performs well for every winform.

If the same winforms are automatically started at the opening of the user session, the the following error is reported by each application when trying to open the connection :

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

The only way to overcome this problem is :

to stop the winforms, to stop the SQLExpress service, to kill the remaining sqlserv.exe running task, then restarting SQLExpress service, and finally manualy start the winforms.

Any help would be greatly appreciated.

Hi Henri,

When you say "a set of winforms" do you mean a single applications or six different applications?

Are all these forms being opened by the same user?

Is the database embedded in the application or is it located somewhere else? (If somewhere else, where?)

You can frequently find more detailed information about this error in the User Instance error log which is located in the user profile directories of the user who is starting the user instance: C:\Documents and Settings\<user name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. Check out this file and let us know what it says.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

I get a similar problem. I get this message anytime I try to open the database from within VB Express. I was following Lesson 08 by trying to Add a SQL Server Item Template to my project.

Here is what I found in my logs.

2006-04-08 17:11:44.62 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.

2006-04-08 17:11:44.07 spid4s Starting up database 'mssqlsystemresource'.
2006-04-08 17:11:44.32 spid4s Error: 15466, Severity: 16, State: 1.
2006-04-08 17:11:44.32 spid4s An error occurred during decryption.
2006-04-08 17:11:44.50 spid4s The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

|||

Does this only happen when connecting to the user instance? Can you connect to the main instance?

Thanks
Laurentiu

|||

Hi Mike,

Thank you for your help and sorry for the late answer, but I am still on holidays. I will try to get the User Instance log at the customer site.

my application consists of six different exe that are started at the same time when opening the user session ( on a windows 2000 server). Each of these exe makes a connection to the same database file using the same connection string. When the user session is opened, there are other softwares that are automatically started which makes the CPU usage about 100% for a few minutes.

However, when each exe is started manually one after the other, the connection to the User Instance is done without error.

regards,

Henri d'Orgeval

|||

Hi Mike,

here is the error log content :

2006-04-13 10:41:30.09 Server Microsoft SQL Server 2005 - 9.00.1314.06 (Intel X86)
Sep 2 2005 21:10:31
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

2006-04-13 10:41:30.09 Server (c) 2005 Microsoft Corporation.
2006-04-13 10:41:30.09 Server All rights reserved.
2006-04-13 10:41:30.09 Server Server process ID is 2468.
2006-04-13 10:41:30.09 Server Logging SQL Server messages in file 'C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log'.
2006-04-13 10:41:30.09 Server Registry startup parameters:
2006-04-13 10:41:30.09 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-04-13 10:41:30.09 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-04-13 10:41:30.09 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-04-13 10:41:30.09 Server Command Line Startup Parameters:
2006-04-13 10:41:30.09 Server -d C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\master.mdf
2006-04-13 10:41:30.09 Server -l C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\mastlog.ldf
2006-04-13 10:41:30.09 Server -e C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log
2006-04-13 10:41:30.09 Server -c
2006-04-13 10:41:30.09 Server -S SQLEXPRESS
2006-04-13 10:41:30.09 Server -s F2DAFDAF-4081-43
2006-04-13 10:41:30.09 Server -w 60
2006-04-13 10:41:30.09 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-04-13 10:41:30.09 Server Detected 2 CPUs. This is an informational message; no user action is required.
2006-04-13 10:41:30.46 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-04-13 10:41:30.79 Server Database mirroring has been enabled on this instance of SQL Server.
2006-04-13 10:41:30.81 spid5s Starting up database 'master'.
2006-04-13 10:41:30.90 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-04-13 10:41:31.04 spid5s SQL Trace ID 1 was started by login "sa".
2006-04-13 10:41:31.10 spid5s Starting up database 'mssqlsystemresource'.
2006-04-13 10:41:31.21 Server Server local connection provider is ready to accept connection on [ \\.\pipe\F2DAFDAF-4081-43\tsql\query ].
2006-04-13 10:41:31.21 spid7s Starting up database 'model'.
2006-04-13 10:41:31.21 spid5s Server name is 'IT-SHAPER1\F2DAFDAF-4081-43'. This is an informational message only. No user action is required.
2006-04-13 10:41:31.21 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2006-04-13 10:41:31.21 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-04-13 10:41:31.34 spid5s Starting up database 'msdb'.
2006-04-13 10:41:31.48 spid7s Clearing tempdb database.
2006-04-13 10:41:32.12 spid7s Starting up database 'tempdb'.
2006-04-13 10:41:32.18 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-04-13 10:41:32.18 spid10s The Service Broker protocol transport is disabled or not configured.
2006-04-13 10:41:32.18 spid10s The Database Mirroring protocol transport is disabled or not configured.
2006-04-13 10:41:32.34 spid10s Service Broker manager has started.
2006-04-13 10:41:32.54 spid51 Starting up database 'C:\PROGRAM FILES\W.A.I.S. TECHNOLOGY\DISTRIBUTION MANAGER\DISTRIBUTIONMANAGER.MDF'.

There are other error logs named errorN.log that contains the same content

Best regards,

Henri d'Orgeval

|||

This appears to be a successful errorlog. Can you post the errorlog that contains the errors you have extracted and posted in your earlier post?

Also, based on your description of the problem, have you considered starting the six instances of your program in succession, with a small delay between them. This might be a workaround until we determine the cause of this failure.

Thanks
Laurentiu

|||

Hi Henri,

I'm still not clear on when you're getting the error. You state that when each exe is started one after another the connection is done without error, when do you get the error?

As for the error log, could you confirm that you pulled that error log for the User Instance and not the main instance? The User Instance error log would be located at C:\Documents and Settings\<user>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS where <user> is the user that experienced the problem.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hi Henri,

Could you give a bit more information as requested by both Laurentiu and me? It's still not clear to use how your application is actually starting and where the error is occuring.

Have you tried the process that Laurentiu suggests and did it work for you?

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Hi Mike and Laurentiu,

I confirm that if each exe is started one after the other, waiting a little bit between each launch, everything is right. This is the only workaround I found that works every time.

If the 6 exes are started at the same time at the opening of the user session ( ie the 6 exes are started from the Startup folder of the User Session), then each exe reports the same error which is the one I mentioned at my first post.

To reproduce the problem the following is done :

1°) the station is rebooted,

2°) as soon as the Login/Password screen appears on the screen, the operator opens the session, which starts the 6 exe at the same time.

3°) then each exe immediately tries to open the connection by attaching the same database file and using the same connection string.

About the error log file I posted :

There are many error log files in the User Instance SQL folder, but the content of each of these error log files is similar to what I posted.

I agree with you, surprisingly it does not look like an error has occured !

I am currently rewriting the software in order to have only one exe that will start 6 different threads one after the other.

Best regards

Henri d'Orgeval

|||

I actually expect that only 5 exes are failing and the 6th succeeds. Can you please confirm this?

The process of connecting to the user instance works like this (this is a high level description):

1. if the instance exists, connect to it
2. otherwise start up the instance
3. connect to the instance

I expect all exe's try step 1 and find that there's no instance, then they all try to start it up, but only one will succeed and the others will fail. This is to be expected and can be resolved in several ways:

(a) start the exe's in succession. This was already proposed and verified to be a valid solution.
(b) start one exe first and after some delay start all the others.
(c) implement code in the exe to handle the connection failure and attempt to reconnect for several times with some delay in between.

Thanks
Laurentiu