Showing posts with label example. Show all posts
Showing posts with label example. Show all posts

Thursday, March 29, 2012

Filter Html tags on Full text Search

I have a Text field in my database that has data along with the HTML tags.
I dont want to search these HTML tags on my FUll text Search.
Example Data
< Font color='red'> Blah</Font>
I want to ignore the font tages on my search.
How do i do it?
save the content in the text data type columns into columns of the image
data type, and use the document type column with a value of htm so that only
the content of these files will be indexed.
"Bruce" <Bruce@.discussions.microsoft.com> wrote in message
news:3BF3659B-E3EB-495F-8D17-75DDD996E323@.microsoft.com...
>I have a Text field in my database that has data along with the HTML tags.
> I dont want to search these HTML tags on my FUll text Search.
> Example Data
> < Font color='red'> Blah</Font>
> I want to ignore the font tages on my search.
> How do i do it?
>
|||Can you please be more elaborate.I am indexing almost 9 columns of Text data
type.How do I save all these columns into columns of the image data type?
How do I use document type columns?
"Hilary Cotter" wrote:

> save the content in the text data type columns into columns of the image
> data type, and use the document type column with a value of htm so that only
> the content of these files will be indexed.
> "Bruce" <Bruce@.discussions.microsoft.com> wrote in message
> news:3BF3659B-E3EB-495F-8D17-75DDD996E323@.microsoft.com...
>
>
|||for the document type column you must
1) ensure you have a column which is char(3) or char(4) and contains the
value htm or .htm
2) store your html content in an image data type column
3) use sp_fulltext_column to specify that the document type is specified in
the document type column you created above in 1)
here is an example
sp_fulltext_column 'MyTable','ImageColumn', 'add', 1033,
'DocumentTypeColumn'
where MyTable is the table you are full text indexing, ImageColumn is a
column of the image datatype, and DocumentTypeColumn is the char(3) or
char(4) column which tells what the native type of the document you are
storing is.
Now, if you also might want to convert your docs to pure text. Using
FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it.
To convert your columns from html to text or from the text datatype to image
you should spit them out to the file system, and then convert them and push
them back.
Let me know if you need code samples to do this.
"Bruce" <Bruce@.discussions.microsoft.com> wrote in message
news:604B02B0-2741-402F-AE62-318452B9BDF0@.microsoft.com...
> Can you please be more elaborate.I am indexing almost 9 columns of Text
data[vbcol=seagreen]
> type.How do I save all these columns into columns of the image data type?
> How do I use document type columns?
>
> "Hilary Cotter" wrote:
only[vbcol=seagreen]
tags.[vbcol=seagreen]
|||Hilary,
"Now, if you also might want to convert your docs to pure text. Using
FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it."
Is the use of FiltDump in the above scenario a violation of Microsoft's
licensing agreement?
Thanks,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIB1eqrtEHA.2596@.TK2MSFTNGP10.phx.gbl...
> for the document type column you must
> 1) ensure you have a column which is char(3) or char(4) and contains the
> value htm or .htm
> 2) store your html content in an image data type column
> 3) use sp_fulltext_column to specify that the document type is specified
in
> the document type column you created above in 1)
> here is an example
> sp_fulltext_column 'MyTable','ImageColumn', 'add', 1033,
> 'DocumentTypeColumn'
>
> where MyTable is the table you are full text indexing, ImageColumn is a
> column of the image datatype, and DocumentTypeColumn is the char(3) or
> char(4) column which tells what the native type of the document you are
> storing is.
> Now, if you also might want to convert your docs to pure text. Using
> FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it.
> To convert your columns from html to text or from the text datatype to
image
> you should spit them out to the file system, and then convert them and
push[vbcol=seagreen]
> them back.
> Let me know if you need code samples to do this.
>
> "Bruce" <Bruce@.discussions.microsoft.com> wrote in message
> news:604B02B0-2741-402F-AE62-318452B9BDF0@.microsoft.com...
> data
type?[vbcol=seagreen]
image[vbcol=seagreen]
that
> only
> tags.
>
|||That's in interesting question. I'll have to check into it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OEpwKoFuEHA.2624@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hilary,
> "Now, if you also might want to convert your docs to pure text. Using
> FiltDump -b myhtmldoc.htm > myhtmldoc.txt is one way of doing it."
> Is the use of FiltDump in the above scenario a violation of Microsoft's
> licensing agreement?
> Thanks,
> John
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIB1eqrtEHA.2596@.TK2MSFTNGP10.phx.gbl...
> in
> image
> push
Text[vbcol=seagreen]
> type?
> image
> that
HTML
>
|||Yes, as you had indicated in the past that any such use was not allowed by a
non-publicly available license policy for these files in another thread. If
you or Microsoft would make this licensing policy public, then there would
be less confusion on this issue.
Best Regards,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#SNgrgWuEHA.3320@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> That's in interesting question. I'll have to check into it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OEpwKoFuEHA.2624@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
specified[vbcol=seagreen]
a[vbcol=seagreen]
are
> Text
> HTML
>
|||Specifically what I said in the past was that I had been advised by
Microsoft that you could not use the word breakers for your own purposes, ie
to roll your own hit highlighting solution.
Filtdump may be another matter, as it is a diagnostic tool.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%2344qClXuEHA.2828@.TK2MSFTNGP12.phx.gbl...
> Yes, as you had indicated in the past that any such use was not allowed by
> a
> non-publicly available license policy for these files in another thread.
> If
> you or Microsoft would make this licensing policy public, then there would
> be less confusion on this issue.
> Best Regards,
> John
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#SNgrgWuEHA.3320@.TK2MSFTNGP15.phx.gbl...
> the
> specified
> a
> are
>
|||You also "generalized" response to include ALL .dll and .exe files in
addition to the wordbreaker dll files, if memory servers me correctly. I
also asked you (or Microsoft) at that time to make public (and now again)
the specific licensing policy from Microsoft that you are referring to. If
it is a secret or under NDA, then how can anyone judge whether or not he or
she is violating a non-public licensing agreement.
Best Regards,
John
PS: Feel free to contact me off-line.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eyNhvefuEHA.3456@.TK2MSFTNGP10.phx.gbl...
> Specifically what I said in the past was that I had been advised by
> Microsoft that you could not use the word breakers for your own purposes,
ie[vbcol=seagreen]
> to roll your own hit highlighting solution.
> Filtdump may be another matter, as it is a diagnostic tool.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%2344qClXuEHA.2828@.TK2MSFTNGP12.phx.gbl...
by[vbcol=seagreen]
would[vbcol=seagreen]
Microsoft's[vbcol=seagreen]
contains[vbcol=seagreen]
is[vbcol=seagreen]
Using[vbcol=seagreen]
of[vbcol=seagreen]
data[vbcol=seagreen]
[vbcol=seagreen]
the
>
|||Please review the pertinent posts:
http://groups.google.com/groups?hl=e...ver .fulltext
http://groups.google.com/groups?hl=e...ver .fulltext
I am not trying to hide anything and AFAIK the communication was not under
NDA. I do not have the communication I had with the Microsoft developer, nor
do I have the response I received from the link I posted as I posted above.
The link I posted is regarding distributing dlls and exes, as you correctly
point out. When I asked another question about tapping into services exposed
by another Microsoft product for my commercial use, I was directed to this
link by a PSS engineer who explained this was the forum to ask these
questions to.
I'll follow up on filtdump and post back here with the response I get. I
will ask that it can be made public.
Please stop mischaracterizing what I say, or check the original posts before
commenting on them.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:#dD0fKhuEHA.2632@.TK2MSFTNGP10.phx.gbl...
> You also "generalized" response to include ALL .dll and .exe files in
> addition to the wordbreaker dll files, if memory servers me correctly. I
> also asked you (or Microsoft) at that time to make public (and now again)
> the specific licensing policy from Microsoft that you are referring to. If
> it is a secret or under NDA, then how can anyone judge whether or not he
or[vbcol=seagreen]
> she is violating a non-public licensing agreement.
> Best Regards,
> John
> PS: Feel free to contact me off-line.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eyNhvefuEHA.3456@.TK2MSFTNGP10.phx.gbl...
purposes,[vbcol=seagreen]
> ie
allowed[vbcol=seagreen]
> by
thread.[vbcol=seagreen]
> would
Using[vbcol=seagreen]
> Microsoft's
> contains
ImageColumn[vbcol=seagreen]
> is
char(3)[vbcol=seagreen]
you[vbcol=seagreen]
> Using
datatype[vbcol=seagreen]
them[vbcol=seagreen]
> of
> data
of[vbcol=seagreen]
htm[vbcol=seagreen]
> the
Search.
>

filter for SQL commad ?

I dont know to write SQL command filter.
m_strQueryDelete.Format("DELETE FROM tab WHERE (Col1 = abc AND Col2
= abc AND ??)"
example: ODBC found 100 records.
I need to delete first 90 records and to leave last 10 records.
Thank you
Joseph
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Client-filte...ict259609.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=894697
Is there a unique field in the table, where you could write a query
like:
DELETE FROM TAB WHERE UNIQUE_FIELD IN
(SELECT TOP 90 UNIQUE_FIELD FROM TAB WHERE (COL1= 'ABC' AND COL2='ABC'
AND ??))
Good luck,
Tony Sebion
"Joseph" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_894697_c2b2ed0878532c267d72ee72a1e9aff5@.dbf orumz.com:

> I don't know to write SQL command filter.
> m_strQueryDelete.Format("DELETE FROM tab WHERE (Col1 = 'abc' AND Col2
> = 'abc' AND ??)"
> example: ODBC found 100 records.
> I need to delete first 90 records and to leave last 10 records.
> Thank you
> Joseph
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/Client-filte...ict259609.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=894697
sql

Filter date for ToDay, some problem with time.

I all.

In a talbe I've a datatime field. for example it contain '16-4-2007 10:45'.

I like to write a SQL that return all record with the date field equals '16-4-2007' (it's not important the time). how to?

thank you.

Try this query to retrive data

select

*from clientswhere clientAddressbetween'2007-04-12 00:00:00.000'and'2007-04-12 23:59:59.999'

The alternative query is

select

*from clientswhere clientAddress>='2007-04-12'and clientAddress<'2007-04-13'

The time format is yyyy-MM-dd and time. Since you are saving time in DB the query should be framed as above

Hope this will help you

|||

try this syntax

print

convert(varchar(20),convert(datetime,'16-04-2006 16:45',105),105)

how it works: convert string to date time with correct format for date string and next convert date time back to string with format you need.

See help for CONVERT in T-SQL help for more format info.

Tuesday, March 27, 2012

Filter a Report's Table using multiple Like conditions using Or

I would like to filter a report using multiple Like conditions

or - Change a filter in Reporting Services to OR rather than AND..

Example: (SameFieldName Like *1100) or (SameFieldName Like *1200).

When I try doing this on the Report's table properties - Filters tab - the And/Or automatically changes to "And" with using the "Like" Operator.

The only time "Or" appears is when I use the " = " Operator.

Or can someone show me how to use an expression to filter on multiple Like conditions.

Thanks!

Try the following filter:

Filter expression:
=(Fields!FName.Value like "*1100" OR Fields!FName.Value like "*1200")

Filter operator:
=

Filter value:
=True

-- Robert

|||You got it! It works!

Thank you

BBK

Monday, March 19, 2012

Filegroups

In oracle, to move objects from one tablespace to another
I can write some DDL and move the object. How do you
move, for example tables from one filegroup to another?
Only method I've come up with so far is rename the object,
recreate it in the filegroup I want it in and move any
associated data over. Is there an easier method?Yes, assuming the table has a clustered index, you can recreate this
index on the target filegroup with use of the DROP EXISTING keyword. For
example
SET QUOTED_IDENTIFIER ON
GO
CREATE CLUSTERED UNIQUE INDEX MyIndex ON MyTable(MyColumn) WITH
DROP_EXISTING ON "MyFilegroup"
Hope this helps,
Gert-Jan
"Cathy S." wrote:
> In oracle, to move objects from one tablespace to another
> I can write some DDL and move the object. How do you
> move, for example tables from one filegroup to another?
> Only method I've come up with so far is rename the object,
> recreate it in the filegroup I want it in and move any
> associated data over. Is there an easier method?|||Thanks...I figured it out.
>--Original Message--
>In oracle, to move objects from one tablespace to another
>I can write some DDL and move the object. How do you
>move, for example tables from one filegroup to another?
>Only method I've come up with so far is rename the
object,
>recreate it in the filegroup I want it in and move any
>associated data over. Is there an easier method?
>.
>|||Cathy
You can also do it from the design table window. Bring up
the table properties and change the filegroup in the drop
down option.
It pretty much does what Gert-Jan says, creates a
clustered index(although in a much more complicated way, I
captured it in profiler to see what it does, it does a
lot), even if the table does not have one (then drops it).
Regards
John

Friday, March 9, 2012

filegroup

What is the advantage of having two secondary files inside a filegroup.
For example, I can create a table and place it inside a filegroup which obviously will use the secondary files.
Not sure why some DBAs create just one but otherscreate more than one secondar files in a filegroup.
I have read the booksonline but can not figure out the advantages.
Thanks

suppose you have a file in filegroup on a hard disk and the hard disk will be full by your file now you can add another hard disk and add a file in filegroup and place it on new Hard disk

File Upload to SQL in 2.0 VB

Can anyone give me an example of how to Upload a file to SQL in asp.net 2.0 using VB?

Thanks

Randy

The way i do it is with a table of 3 columns:

ImgSize(int)
ImgContentType(nvarchar)
ImgFile(image)

Code:

'Pic is a File Upload Contol

Dim cnn As New SqlConnection(strConn)

Dim imgStream As Stream = Pic.PostedFile.InputStream
Dim imgLen As Integer = Pic.PostedFile.ContentLength
Dim imgContentType As String = Pic.PostedFile.ContentType

Dim imgBinaryData As Byte() = New Byte(imgLen) {}
Dim n As Integer = imgStream.Read(imgBinaryData, 0, imgLen)

You then build your command object and set your parameters with
the value you got above (imgStream, imgLen, imgContentType) and
then execute an non query.

Let me know if you need help reading as well !


|||@.amensi: ei that's the same problem as mine! So that's it, but if you don't me asking, can that also upload a MS word text file? if it doesn't can you please post the source code and where to put it? thanks!

Sunday, February 26, 2012

file Server and Ful text indexing

Dear All,
Is it possible for SQL Server to search on documents which are not inserted
into the database?
for example does SQL Server have the facility to scan the documents, keeping
the documents in the fileserver itself and doing full-text indexing on them
taking only the token words from it. (These token words can be inserted into
database as indexes).
A similar facility is provided bu ORACLE-Text.
We are new to this area. So any hints on this would be advantageous for us.
Very thanks in anticipation.
Also please give us the utilities/tools which are readily available which
can be integrated with SQL Server / .Net Applications for database and
Documents Search.
Regards
SathianSathian,
Yes, but not using SQL Server's "full text search" components, instead you
would use the Indexing Service and create a Linked Server to IS via the
MSIDXS OLE-DB provider. See SQL Server 2000 BOL title's "OLE DB Provider for
Microsoft Indexing Service" and sp_addlinkedserver from more info on this.
Note, while this is functional, it does not perform very well.
I believe that the ORACLE Text (an expensive add-on) feature you're
refereing to is called "Oracle Ultra Search". Correct? This is another "for
fee" spider based service of ORACLE Text, that has no direct feature
comparison in SQL Server 2000 or 2005 Full Text Search, I'm to say...
If you're interested in more details, feel free to email me directly.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sathian T" <sathian.t@.in.Bosch.com> wrote in message
news:d188gf$itc$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Is it possible for SQL Server to search on documents which are not
inserted
> into the database?
> for example does SQL Server have the facility to scan the documents,
keeping
> the documents in the fileserver itself and doing full-text indexing on
them
> taking only the token words from it. (These token words can be inserted
into
> database as indexes).
> A similar facility is provided bu ORACLE-Text.
> We are new to this area. So any hints on this would be advantageous for
us.
> Very thanks in anticipation.
> Also please give us the utilities/tools which are readily available which
> can be integrated with SQL Server / .Net Applications for database and
> Documents Search.
> Regards
> Sathian
>

Friday, February 24, 2012

File IO Exception thrown when trying to access a CLR Assembly in SQL Server

I have been encountering a problem using a CLR Assembly in SQL server. The Assembly is one provided by Microsoft for an example on using Exchange web services with SQL server.

http://www.microsoft.com/downloads/details.aspx?FamilyId=D6924897-7B62-46FD-874E-24FB0FBA2159&displaylang=en#Requirements

Essentially what this package is, is a set of c# classes that access the Exchange 2007 Web Services via a set of user defined functions and views in MS SQL Server 2005.

We have not modified the code except to configure for our host environment.

We are able to register the assembly using the setup.sql file included. But when we try to access any of the views or use the functions we get the following error:

Msg 10314, Level 16, State 11, Line 10

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65551. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly 'exchangeudfs, Version=0.0.0.0, Culture=neutral, PublicKeyToken=777b97dde00f3dbe' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

System.IO.FileLoadException:

at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)

at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

at System.Reflection.Assembly.Load(String assemblyString)

We have 3 testing environments. 2 Windows 2003 servers called test and test2. Also a single Windows 2000 server called test3. Test and test2 have a full suite installed on them, web server, exchange 2007, sql server 2005, they are also domain controllers for their own domains.

We get the above error on test and test2 but it runs fine on test3. Test and test2 represent what our production environment is like. Test3 was just part of our troubleshooting process.

We have tried a lot to make this work. Here are some details on the things we have tried.

The database is set up to allow CLR Assemblies. This is part of the setup.sql.

The assembly's permission is set to external_access.

We have gone all the way to setting the file permissions on the dll to full control to the Everyone group.

We have tried different accounts to run the SQL Service. We've tried the system account, the local admin account and a seperate user account.

The database we are using is a fresh brand new database. Therefore it does not fit into the bug reported in this article:

http://support.microsoft.com/kb/918040

I am really at a loss to where to go from here. Any ideas on why this 'out of the box' solution is causing us so many headaches would be appriciated.

Thanks,

Tim

Which box did you compile your changes on? Do the full assembly identities match for the exchangeudfs assembly and all the dependents?

|||

I compiled the assembly on another seperate development machine. I'm not sure I know how to answer your second question.

|||

Fro whatever reason I recompiled it and it magically started working. Not sure why.

Sunday, February 19, 2012

File IO Exception thrown when trying to access a CLR Assembly in SQL Server

I have been encountering a problem using a CLR Assembly in SQL server. The Assembly is one provided by Microsoft for an example on using Exchange web services with SQL server.

http://www.microsoft.com/downloads/details.aspx?FamilyId=D6924897-7B62-46FD-874E-24FB0FBA2159&displaylang=en#Requirements

Essentially what this package is, is a set of c# classes that access the Exchange 2007 Web Services via a set of user defined functions and views in MS SQL Server 2005.

We have not modified the code except to configure for our host environment.

We are able to register the assembly using the setup.sql file included. But when we try to access any of the views or use the functions we get the following error:

Msg 10314, Level 16, State 11, Line 10

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65551. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly 'exchangeudfs, Version=0.0.0.0, Culture=neutral, PublicKeyToken=777b97dde00f3dbe' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

System.IO.FileLoadException:

at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)

at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

at System.Reflection.Assembly.Load(String assemblyString)

We have 3 testing environments. 2 Windows 2003 servers called test and test2. Also a single Windows 2000 server called test3. Test and test2 have a full suite installed on them, web server, exchange 2007, sql server 2005, they are also domain controllers for their own domains.

We get the above error on test and test2 but it runs fine on test3. Test and test2 represent what our production environment is like. Test3 was just part of our troubleshooting process.

We have tried a lot to make this work. Here are some details on the things we have tried.

The database is set up to allow CLR Assemblies. This is part of the setup.sql.

The assembly's permission is set to external_access.

We have gone all the way to setting the file permissions on the dll to full control to the Everyone group.

We have tried different accounts to run the SQL Service. We've tried the system account, the local admin account and a seperate user account.

The database we are using is a fresh brand new database. Therefore it does not fit into the bug reported in this article:

http://support.microsoft.com/kb/918040

I am really at a loss to where to go from here. Any ideas on why this 'out of the box' solution is causing us so many headaches would be appriciated.

Thanks,

Tim

Which box did you compile your changes on? Do the full assembly identities match for the exchangeudfs assembly and all the dependents?

|||

I compiled the assembly on another seperate development machine. I'm not sure I know how to answer your second question.

|||

Fro whatever reason I recompiled it and it magically started working. Not sure why.