I have a table with 1.5 million rows. Each field has a text qualifier of " ". Could you please tell me if and how I can write a script to remove the quotes from each field?
Thank you. ;)Using which database engine? The string operations aren't very standard between engines, so which engine makes a considerable difference.
-PatP|||Using MS SQL Server 2000|||Hi mary10k, try this
update tablename set filedname=replace(filedname,""" ""","")
Madhivanan
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
cleaning html tags.
does any one has any sql server function that passes some text and returns a string without html tags.
example:
nice day
should return nice day
or if other html tags strip them off.
thanks for your help.
-FrThis is not something that SQL Server is particularly good at.
You can probably accomplish this through instantiating the VBScript.RegExp libary and making use of a regular expression to strip out the HTML.
This article gives an example:Regular Expressions in T-SQL.
The pattern would probably be somthing like this: <[^>]*
Terri|||How about doing the same thing in c#?
Thanks
Fra
Thursday, March 22, 2012
cleaing up text type fields
Hello.
I have a text type field in an SQL2000 table I need to clean up. After
converting from MySQL I am finding that I have leading Tabs and when I
display the text field in an asp textbox I end up see the record
double spaced.
Any suggestion on how I can clean the field up?
Also, as I test an application I just migrated from apache/php/mysql
to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
pentium 4 with 512mb I am finding the the MS solution is like 3 times
slower?
Thanks for any help or info.> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
Trim it on the client before displaying it?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
We don't even have a fraction of the information required to analyze your
environment and point out potential reasons for any performance differences.|||Assuming that each record has two leading tabs, you could do something like:
DECLARE @.ptr binary(16)
SELECT @.ptr=textptr(textColumn) FROM yourTable
UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
That will delete the first two characters from each record. If you need to
do more complex evaluation, you can run a cursor over the table and use
CHARINDEX to determine where the replacement(s) should be made.
<jason@.cyberpine.com> wrote in message
news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> Hello.
> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
> Thanks for any help or info.|||On second thought, you will need a CURSOR - Running that code as-is will
only strip the first two characters from the last record selected.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> Assuming that each record has two leading tabs, you could do something
like:
> DECLARE @.ptr binary(16)
> SELECT @.ptr=textptr(textColumn) FROM yourTable
> UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> That will delete the first two characters from each record. If you need
to
> do more complex evaluation, you can run a cursor over the table and use
> CHARINDEX to determine where the replacement(s) should be made.
> <jason@.cyberpine.com> wrote in message
> news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > Hello.
> >
> > I have a text type field in an SQL2000 table I need to clean up. After
> > converting from MySQL I am finding that I have leading Tabs and when I
> > display the text field in an asp textbox I end up see the record
> > double spaced.
> >
> > Any suggestion on how I can clean the field up?
> >
> > Also, as I test an application I just migrated from apache/php/mysql
> > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > slower?
> >
> > Thanks for any help or info.
>|||You can see this example for using replace in this case:
http://www.aspfaq.com/2445
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> On second thought, you will need a CURSOR - Running that code as-is will
> only strip the first two characters from the last record selected.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > Assuming that each record has two leading tabs, you could do something
> like:
> >
> > DECLARE @.ptr binary(16)
> > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> >
> > That will delete the first two characters from each record. If you need
> to
> > do more complex evaluation, you can run a cursor over the table and use
> > CHARINDEX to determine where the replacement(s) should be made.
> >
> > <jason@.cyberpine.com> wrote in message
> > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > Hello.
> > >
> > > I have a text type field in an SQL2000 table I need to clean up. After
> > > converting from MySQL I am finding that I have leading Tabs and when I
> > > display the text field in an asp textbox I end up see the record
> > > double spaced.
> > >
> > > Any suggestion on how I can clean the field up?
> > >
> > > Also, as I test an application I just migrated from apache/php/mysql
> > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > > slower?
> > >
> > > Thanks for any help or info.
> >
> >
>|||Ahhh, nice... I didn't realize this slightly annoying caveat:
"Initially I used CHARINDEX, but that failed if the pattern was deeper than
8,000 characters into the value. PATINDEX never dies."
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uAGRfqUpDHA.2772@.TK2MSFTNGP12.phx.gbl...
> You can see this example for using replace in this case:
> http://www.aspfaq.com/2445
>
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> > On second thought, you will need a CURSOR - Running that code as-is will
> > only strip the first two characters from the last record selected.
> >
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > > Assuming that each record has two leading tabs, you could do something
> > like:
> > >
> > > DECLARE @.ptr binary(16)
> > > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> > >
> > > That will delete the first two characters from each record. If you
need
> > to
> > > do more complex evaluation, you can run a cursor over the table and
use
> > > CHARINDEX to determine where the replacement(s) should be made.
> > >
> > > <jason@.cyberpine.com> wrote in message
> > > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > > Hello.
> > > >
> > > > I have a text type field in an SQL2000 table I need to clean up.
After
> > > > converting from MySQL I am finding that I have leading Tabs and when
I
> > > > display the text field in an asp textbox I end up see the record
> > > > double spaced.
> > > >
> > > > Any suggestion on how I can clean the field up?
> > > >
> > > > Also, as I test an application I just migrated from apache/php/mysql
> > > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > > pentium 4 with 512mb I am finding the the MS solution is like 3
times
> > > > slower?
> > > >
> > > > Thanks for any help or info.
> > >
> > >
> >
> >
>|||It took a few practical uses before I cleaned that up. The code is a little
simpler for this specific case, because you don't have to find the index,
and you don't have to loop through... you just replace the first two
characters if they're what you think they are.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ePbcpuUpDHA.2528@.TK2MSFTNGP12.phx.gbl...
> Ahhh, nice... I didn't realize this slightly annoying caveat:
> "Initially I used CHARINDEX, but that failed if the pattern was deeper
than
> 8,000 characters into the value. PATINDEX never dies."sqlsql
I have a text type field in an SQL2000 table I need to clean up. After
converting from MySQL I am finding that I have leading Tabs and when I
display the text field in an asp textbox I end up see the record
double spaced.
Any suggestion on how I can clean the field up?
Also, as I test an application I just migrated from apache/php/mysql
to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
pentium 4 with 512mb I am finding the the MS solution is like 3 times
slower?
Thanks for any help or info.> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
Trim it on the client before displaying it?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
We don't even have a fraction of the information required to analyze your
environment and point out potential reasons for any performance differences.|||Assuming that each record has two leading tabs, you could do something like:
DECLARE @.ptr binary(16)
SELECT @.ptr=textptr(textColumn) FROM yourTable
UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
That will delete the first two characters from each record. If you need to
do more complex evaluation, you can run a cursor over the table and use
CHARINDEX to determine where the replacement(s) should be made.
<jason@.cyberpine.com> wrote in message
news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> Hello.
> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
> Thanks for any help or info.|||On second thought, you will need a CURSOR - Running that code as-is will
only strip the first two characters from the last record selected.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> Assuming that each record has two leading tabs, you could do something
like:
> DECLARE @.ptr binary(16)
> SELECT @.ptr=textptr(textColumn) FROM yourTable
> UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> That will delete the first two characters from each record. If you need
to
> do more complex evaluation, you can run a cursor over the table and use
> CHARINDEX to determine where the replacement(s) should be made.
> <jason@.cyberpine.com> wrote in message
> news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > Hello.
> >
> > I have a text type field in an SQL2000 table I need to clean up. After
> > converting from MySQL I am finding that I have leading Tabs and when I
> > display the text field in an asp textbox I end up see the record
> > double spaced.
> >
> > Any suggestion on how I can clean the field up?
> >
> > Also, as I test an application I just migrated from apache/php/mysql
> > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > slower?
> >
> > Thanks for any help or info.
>|||You can see this example for using replace in this case:
http://www.aspfaq.com/2445
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> On second thought, you will need a CURSOR - Running that code as-is will
> only strip the first two characters from the last record selected.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > Assuming that each record has two leading tabs, you could do something
> like:
> >
> > DECLARE @.ptr binary(16)
> > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> >
> > That will delete the first two characters from each record. If you need
> to
> > do more complex evaluation, you can run a cursor over the table and use
> > CHARINDEX to determine where the replacement(s) should be made.
> >
> > <jason@.cyberpine.com> wrote in message
> > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > Hello.
> > >
> > > I have a text type field in an SQL2000 table I need to clean up. After
> > > converting from MySQL I am finding that I have leading Tabs and when I
> > > display the text field in an asp textbox I end up see the record
> > > double spaced.
> > >
> > > Any suggestion on how I can clean the field up?
> > >
> > > Also, as I test an application I just migrated from apache/php/mysql
> > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > > slower?
> > >
> > > Thanks for any help or info.
> >
> >
>|||Ahhh, nice... I didn't realize this slightly annoying caveat:
"Initially I used CHARINDEX, but that failed if the pattern was deeper than
8,000 characters into the value. PATINDEX never dies."
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uAGRfqUpDHA.2772@.TK2MSFTNGP12.phx.gbl...
> You can see this example for using replace in this case:
> http://www.aspfaq.com/2445
>
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> > On second thought, you will need a CURSOR - Running that code as-is will
> > only strip the first two characters from the last record selected.
> >
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > > Assuming that each record has two leading tabs, you could do something
> > like:
> > >
> > > DECLARE @.ptr binary(16)
> > > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> > >
> > > That will delete the first two characters from each record. If you
need
> > to
> > > do more complex evaluation, you can run a cursor over the table and
use
> > > CHARINDEX to determine where the replacement(s) should be made.
> > >
> > > <jason@.cyberpine.com> wrote in message
> > > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > > Hello.
> > > >
> > > > I have a text type field in an SQL2000 table I need to clean up.
After
> > > > converting from MySQL I am finding that I have leading Tabs and when
I
> > > > display the text field in an asp textbox I end up see the record
> > > > double spaced.
> > > >
> > > > Any suggestion on how I can clean the field up?
> > > >
> > > > Also, as I test an application I just migrated from apache/php/mysql
> > > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > > pentium 4 with 512mb I am finding the the MS solution is like 3
times
> > > > slower?
> > > >
> > > > Thanks for any help or info.
> > >
> > >
> >
> >
>|||It took a few practical uses before I cleaned that up. The code is a little
simpler for this specific case, because you don't have to find the index,
and you don't have to loop through... you just replace the first two
characters if they're what you think they are.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ePbcpuUpDHA.2528@.TK2MSFTNGP12.phx.gbl...
> Ahhh, nice... I didn't realize this slightly annoying caveat:
> "Initially I used CHARINDEX, but that failed if the pattern was deeper
than
> 8,000 characters into the value. PATINDEX never dies."sqlsql
Tuesday, March 20, 2012
cidump tool documentation?
I'm trying to use the cidump tool of SQL Server 2005 Beta 2 to dump a words
list from a full text catalog and I can't find good documentation - there ar
e
few people online that claim that this is possible. I've look everywhere but
there's little to no info about this feature. Can the SQL documentation team
be so kind and provide us with something to work with here?Here is how I dump the catalog I created in C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\FTData\XIN
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>cidump -dump
"C:\Program Files\Microsoft SQL
Server"\MSSQL.1\MSSQL\FTData\XIN\MssearchCatalogDir -dir k
Note how I use the subdirectory called MSSearchCatalogDir.
For more documentation did you check this doc?
Use cidump.exe for:
- Dumping the content of the catalog or a specified index
cidump -dump <catalog_path> [options]
- Checking the integrity of the catalog or a specified index
cidump -check <catalog_path> [options]
- Computing statistics on the content of the catalog or a specified index
cidump -stats <catalog_path> [options]
=== Common options:
========================================
=================
-i <IndexId> - Apply the operation on this index.
In this option is not used, all the indexes are
processed.
-e <IndexId> - Exclude this index.
Use it multiple times for excluding more than one index.
-u <filepath> - Dump to a UNICODE file.
-not_read_only - Open CiStorage not read only
For when the index table streams are not in sync
=== Specific options to be used with -dump:
=================================
Select what to dump:
-x [<format>] - Dump the index.
<format> can be: k - keys only (default)
kw - keys and wids (document IDs)
kwo - dump keys, wids, and occurences
kwc - keys and WidCounts per key
sbr - the SortByRank index for each key
-dir - Dump the index directory.
-widset [<format>] - Dump the widset files.
<format> can be:
wid - iterate the wids in the widset (default)
wc - widcounts only
hdr - widset header only
To determine in which index a given wid is fresh use:
cidump -dump <path> -widset -w <wid>
Select the keys to dump:
-k <startKey> [<endKey>] - Dump only the the keys in an given range.
-kn <keyCount> - Stop after displaying a number of keys.
-w <wid> - Dump only the keys that contain the given wid.
-p <PropID> - Dump only keys with the given PropID.
Select the wids to dump:
-wr <startWid> [<endWid>] - Dump only the wids in a given range.
-wn <widCount> - Stop after displaying a number of wids per each key.
Select the format of the data:
-d - Display all numbers in decimal.
(default is hex for IDs and offsets decimal for counts and sizes).
-h - Display all numbers in hexadecimal.
-b - Display the internal representaion of the keys (bytes in hex).
=== Notes:
* IndexIds, wids and PropIds can be entered as hex numbers preceeded by
"0x".
* The keys can be entered as strings or as a sequence of bytes in hex,
between
quotes and parantheses: abc is the same as "(00 00 61 00 62 00 63)"
Run cidump -?key to get help on the input format for keys.
=== Samples:
Check the integrity of all the indexes:
cidump -check c:\catalog
Display global statistics for the index 0001002A:
cidump -stats c:\catalog -g -i 0x1002A
Dump from all the indexes the keys in the range aaa - bbb.
Use the format that also shows the widcounts (number of docs with that key):
cidump -dump c:\catalog -x kwc -k aaa bbb
Dump the keys that contain the the wid 9001:
cidump -dump c:\catalog -x -w 9001
Dump the first 10 wids from the sorted by rank index for the key "tokenone":
cidump -dump c:\catalog -x sbr -k tokenone -kn 1 -wn 10
Dump the directory
cidump -dump s:\encpath\encarta -dir kbo
===
Run cidump -' to display advanced options.
Advanced options:
=== Specific options for dump:
========================================
======
-x [<format>] - Dump the index
<format> can be also:
ks - statistics per key
kw+ - keys and wids and wid metadata (all but
occurrences)
kp - list of keys with position in index
kwp - list of keys and wids with position in index
kph - dump phrases that contain a given key.
Requires -kph and -pch options.
ph - dump all phrases in more than a given nr of docs
Requires -phc option.
-dir [<format>] - Dump the index directory
<format> can be:
kbo - keys and BitOffset in index (default)
kp - list of keys with position in the directory file
-phk <key> - Used only with "-x kph" dump format.
Dump phrases that contain the given <key>. Use also -phc.
-phc <minWidCount> - Used only with "-x ph" and "-x kph" dump formats.
Dump all the 2-3 word phrases that occur in at least <minOccCount>
documents in the indexed corpus.
-kwc <minWidCount> <maxWidCount> - Display only keys in a widcount range.
-alr - Display the allocated ranges for the master index.
-fbs - Force binary search when dumping widsets.
-rec <index> <type> <maxWid> <R/W> - Dump a standalone index.
Don't use the Index Table.
<index> - the index id (e.g.) 0x1001C
<type> - 0 - master index; 1 - shadow index.
<maxWid> - the maximum workid in the index.
<R/W> - 0 - complete index; 1 - incomplete index (write mode)
=== Specific options for check: ========================================
====
-k <start_key> [<end_key>] - Process only the area of the index
for the given key range.
=== Specific options for statistics: =======================================
Computing default statistics require a full scan of the index file.
Additional options:
-o - display default statistics and also occurence distribution.
-wc - display widcount distribution (iterates through keys only).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"cybergoo" <cybergoo@.discussions.microsoft.com> wrote in message
news:433D93A2-6162-446E-841C-DCEC48618ADF@.microsoft.com...
> I'm trying to use the cidump tool of SQL Server 2005 Beta 2 to dump a
words
> list from a full text catalog and I can't find good documentation - there
are
> few people online that claim that this is possible. I've look everywhere
but
> there's little to no info about this feature. Can the SQL documentation
team
> be so kind and provide us with something to work with here?sqlsql
list from a full text catalog and I can't find good documentation - there ar
e
few people online that claim that this is possible. I've look everywhere but
there's little to no info about this feature. Can the SQL documentation team
be so kind and provide us with something to work with here?Here is how I dump the catalog I created in C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\FTData\XIN
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>cidump -dump
"C:\Program Files\Microsoft SQL
Server"\MSSQL.1\MSSQL\FTData\XIN\MssearchCatalogDir -dir k
Note how I use the subdirectory called MSSearchCatalogDir.
For more documentation did you check this doc?
Use cidump.exe for:
- Dumping the content of the catalog or a specified index
cidump -dump <catalog_path> [options]
- Checking the integrity of the catalog or a specified index
cidump -check <catalog_path> [options]
- Computing statistics on the content of the catalog or a specified index
cidump -stats <catalog_path> [options]
=== Common options:
========================================
=================
-i <IndexId> - Apply the operation on this index.
In this option is not used, all the indexes are
processed.
-e <IndexId> - Exclude this index.
Use it multiple times for excluding more than one index.
-u <filepath> - Dump to a UNICODE file.
-not_read_only - Open CiStorage not read only
For when the index table streams are not in sync
=== Specific options to be used with -dump:
=================================
Select what to dump:
-x [<format>] - Dump the index.
<format> can be: k - keys only (default)
kw - keys and wids (document IDs)
kwo - dump keys, wids, and occurences
kwc - keys and WidCounts per key
sbr - the SortByRank index for each key
-dir - Dump the index directory.
-widset [<format>] - Dump the widset files.
<format> can be:
wid - iterate the wids in the widset (default)
wc - widcounts only
hdr - widset header only
To determine in which index a given wid is fresh use:
cidump -dump <path> -widset -w <wid>
Select the keys to dump:
-k <startKey> [<endKey>] - Dump only the the keys in an given range.
-kn <keyCount> - Stop after displaying a number of keys.
-w <wid> - Dump only the keys that contain the given wid.
-p <PropID> - Dump only keys with the given PropID.
Select the wids to dump:
-wr <startWid> [<endWid>] - Dump only the wids in a given range.
-wn <widCount> - Stop after displaying a number of wids per each key.
Select the format of the data:
-d - Display all numbers in decimal.
(default is hex for IDs and offsets decimal for counts and sizes).
-h - Display all numbers in hexadecimal.
-b - Display the internal representaion of the keys (bytes in hex).
=== Notes:
* IndexIds, wids and PropIds can be entered as hex numbers preceeded by
"0x".
* The keys can be entered as strings or as a sequence of bytes in hex,
between
quotes and parantheses: abc is the same as "(00 00 61 00 62 00 63)"
Run cidump -?key to get help on the input format for keys.
=== Samples:
Check the integrity of all the indexes:
cidump -check c:\catalog
Display global statistics for the index 0001002A:
cidump -stats c:\catalog -g -i 0x1002A
Dump from all the indexes the keys in the range aaa - bbb.
Use the format that also shows the widcounts (number of docs with that key):
cidump -dump c:\catalog -x kwc -k aaa bbb
Dump the keys that contain the the wid 9001:
cidump -dump c:\catalog -x -w 9001
Dump the first 10 wids from the sorted by rank index for the key "tokenone":
cidump -dump c:\catalog -x sbr -k tokenone -kn 1 -wn 10
Dump the directory
cidump -dump s:\encpath\encarta -dir kbo
===
Run cidump -' to display advanced options.
Advanced options:
=== Specific options for dump:
========================================
======
-x [<format>] - Dump the index
<format> can be also:
ks - statistics per key
kw+ - keys and wids and wid metadata (all but
occurrences)
kp - list of keys with position in index
kwp - list of keys and wids with position in index
kph - dump phrases that contain a given key.
Requires -kph and -pch options.
ph - dump all phrases in more than a given nr of docs
Requires -phc option.
-dir [<format>] - Dump the index directory
<format> can be:
kbo - keys and BitOffset in index (default)
kp - list of keys with position in the directory file
-phk <key> - Used only with "-x kph" dump format.
Dump phrases that contain the given <key>. Use also -phc.
-phc <minWidCount> - Used only with "-x ph" and "-x kph" dump formats.
Dump all the 2-3 word phrases that occur in at least <minOccCount>
documents in the indexed corpus.
-kwc <minWidCount> <maxWidCount> - Display only keys in a widcount range.
-alr - Display the allocated ranges for the master index.
-fbs - Force binary search when dumping widsets.
-rec <index> <type> <maxWid> <R/W> - Dump a standalone index.
Don't use the Index Table.
<index> - the index id (e.g.) 0x1001C
<type> - 0 - master index; 1 - shadow index.
<maxWid> - the maximum workid in the index.
<R/W> - 0 - complete index; 1 - incomplete index (write mode)
=== Specific options for check: ========================================
====
-k <start_key> [<end_key>] - Process only the area of the index
for the given key range.
=== Specific options for statistics: =======================================
Computing default statistics require a full scan of the index file.
Additional options:
-o - display default statistics and also occurence distribution.
-wc - display widcount distribution (iterates through keys only).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"cybergoo" <cybergoo@.discussions.microsoft.com> wrote in message
news:433D93A2-6162-446E-841C-DCEC48618ADF@.microsoft.com...
> I'm trying to use the cidump tool of SQL Server 2005 Beta 2 to dump a
words
> list from a full text catalog and I can't find good documentation - there
are
> few people online that claim that this is possible. I've look everywhere
but
> there's little to no info about this feature. Can the SQL documentation
team
> be so kind and provide us with something to work with here?sqlsql
cidump documentation?
I'm trying to use the new cidump tool of SQL Server 2005 Beta 2 to get a
words list from a full text catalog and I can't find good documentation.
I've looked everywhere. Have you see it? Can the SQL documentation team be
so kind and provide us with something here about this new feature?
Here is how I dump the catalog I created in C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\FTData\XIN
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>cidump -dump
"C:\Program Files\Microsoft SQL
Server"\MSSQL.1\MSSQL\FTData\XIN\MssearchCatalogDi r -dir k
Note how I use the subdirectory called MSSearchCatalogDir.
For more documentation did you check this doc?
Use cidump.exe for:
- Dumping the content of the catalog or a specified index
cidump -dump <catalog_path> [options]
- Checking the integrity of the catalog or a specified index
cidump -check <catalog_path> [options]
- Computing statistics on the content of the catalog or a specified index
cidump -stats <catalog_path> [options]
=== Common options:
================================================== =======
-i <IndexId> - Apply the operation on this index.
In this option is not used, all the indexes are
processed.
-e <IndexId> - Exclude this index.
Use it multiple times for excluding more than one index.
-u <filepath> - Dump to a UNICODE file.
-not_read_only - Open CiStorage not read only
For when the index table streams are not in sync
=== Specific options to be used with -dump:
=================================
Select what to dump:
-x [<format>] - Dump the index.
<format> can be: k - keys only (default)
kw - keys and wids (document IDs)
kwo - dump keys, wids, and occurences
kwc - keys and WidCounts per key
sbr - the SortByRank index for each key
-dir - Dump the index directory.
-widset [<format>] - Dump the widset files.
<format> can be:
wid - iterate the wids in the widset (default)
wc - widcounts only
hdr - widset header only
To determine in which index a given wid is fresh use:
cidump -dump <path> -widset -w <wid>
Select the keys to dump:
-k <startKey> [<endKey>] - Dump only the the keys in an given range.
-kn <keyCount> - Stop after displaying a number of keys.
-w <wid> - Dump only the keys that contain the given wid.
-p <PropID> - Dump only keys with the given PropID.
Select the wids to dump:
-wr <startWid> [<endWid>] - Dump only the wids in a given range.
-wn <widCount> - Stop after displaying a number of wids per each key.
Select the format of the data:
-d - Display all numbers in decimal.
(default is hex for IDs and offsets decimal for counts and sizes).
-h - Display all numbers in hexadecimal.
-b - Display the internal representaion of the keys (bytes in hex).
=== Notes:
* IndexIds, wids and PropIds can be entered as hex numbers preceeded by
"0x".
* The keys can be entered as strings or as a sequence of bytes in hex,
between
quotes and parantheses: abc is the same as "(00 00 61 00 62 00 63)"
Run cidump -?key to get help on the input format for keys.
=== Samples:
Check the integrity of all the indexes:
cidump -check c:\catalog
Display global statistics for the index 0001002A:
cidump -stats c:\catalog -g -i 0x1002A
Dump from all the indexes the keys in the range aaa - bbb.
Use the format that also shows the widcounts (number of docs with that key):
cidump -dump c:\catalog -x kwc -k aaa bbb
Dump the keys that contain the the wid 9001:
cidump -dump c:\catalog -x -w 9001
Dump the first 10 wids from the sorted by rank index for the key "tokenone":
cidump -dump c:\catalog -x sbr -k tokenone -kn 1 -wn 10
Dump the directory
cidump -dump s:\encpath\encarta -dir kbo
===
Run cidump -? to display advanced options.
Advanced options:
=== Specific options for dump:
==============================================
-x [<format>] - Dump the index
<format> can be also:
ks - statistics per key
kw+ - keys and wids and wid metadata (all but
occurrences)
kp - list of keys with position in index
kwp - list of keys and wids with position in index
kph - dump phrases that contain a given key.
Requires -kph and -pch options.
ph - dump all phrases in more than a given nr of docs
Requires -phc option.
-dir [<format>] - Dump the index directory
<format> can be:
kbo - keys and BitOffset in index (default)
kp - list of keys with position in the directory file
-phk <key> - Used only with "-x kph" dump format.
Dump phrases that contain the given <key>. Use also -phc.
-phc <minWidCount> - Used only with "-x ph" and "-x kph" dump formats.
Dump all the 2-3 word phrases that occur in at least <minOccCount>
documents in the indexed corpus.
-kwc <minWidCount> <maxWidCount> - Display only keys in a widcount range.
-alr - Display the allocated ranges for the master index.
-fbs - Force binary search when dumping widsets.
-rec <index> <type> <maxWid> <R/W> - Dump a standalone index.
Don't use the Index Table.
<index> - the index id (e.g.) 0x1001C
<type> - 0 - master index; 1 - shadow index.
<maxWid> - the maximum workid in the index.
<R/W> - 0 - complete index; 1 - incomplete index (write mode)
=== Specific options for check: ============================================
-k <start_key> [<end_key>] - Process only the area of the index
for the given key range.
=== Specific options for statistics: =======================================
Computing default statistics require a full scan of the index file.
Additional options:
-o - display default statistics and also occurence distribution.
-wc - display widcount distribution (iterates through keys only).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"cybergoo" <cybergoo@.newsgroup.nospam> wrote in message
news:%23448WlToFHA.1948@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use the new cidump tool of SQL Server 2005 Beta 2 to get a
> words list from a full text catalog and I can't find good documentation.
> I've looked everywhere. Have you see it? Can the SQL documentation team be
> so kind and provide us with something here about this new feature?
>
|||Cybergoo,
The SQL Server 2005 Beta2 as well as Bet3 Books Online (BOL) have yet to be
updated on this most useful SQL FTS utiltity. In the meantime, you should
use the cidump /? to get the syntax as well as example of use:
-- on my Win2003 server...
f:
cd:\MSSQL90\MSSQL.1\MSSQL\Binn
cidump /?
-- edited output:
Use cidump.exe for:
- Dumping the content of the catalog or a specified index
cidump -dump <catalog_path> [options]
- Checking the integrity of the catalog or a specified index
cidump -check <catalog_path> [options]
- Computing statistics on the content of the catalog or a specified index
cidump -stats <catalog_path> [options]
...
Display global statistics for the index 0001002A:
cidump -stats c:\catalog -g -i 0x1002A
cidump /?
Advanced options:
-x [<format>] - Dump the index
<format> can be also:
ks - statistics per key
kw+ - keys and wids and wid metadata (all but
occurrences)
kp - list of keys with position in index
kwp - list of keys and wids with position in index
kph - dump phrases that contain a given key.
Requires -kph and -pch options.
ph - dump all phrases in more than a given nr of docs
Requires -phc option.
-dir [<format>] - Dump the index directory
<format> can be:
kbo - keys and BitOffset in index (default)
kp - list of keys with position in the directory file
-phk <key> - Used only with "-x kph" dump format.
Dump phrases that contain the given <key>. Use also -phc.
-phc <minWidCount> - Used only with "-x ph" and "-x kph" dump formats.
Dump all the 2-3 word phrases that occur in at least <minOccCount>
documents in the indexed corpus.
-kwc <minWidCount> <maxWidCount> - Display only keys in a widcount range.
-alr - Display the allocated ranges for the master index.
-fbs - Force binary search when dumping widsets.
-rec <index> <type> <maxWid> <R/W> - Dump a standalone index.
Don't use the Index Table.
<index> - the index id (e.g.) 0x1001C
<type> - 0 - master index; 1 - shadow index.
<maxWid> - the maximum workid in the index.
<R/W> - 0 - complete index; 1 - incomplete index (write mode)
Enjoy!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"cybergoo" <cybergoo@.newsgroup.nospam> wrote in message
news:%23448WlToFHA.1948@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use the new cidump tool of SQL Server 2005 Beta 2 to get a
> words list from a full text catalog and I can't find good documentation.
> I've looked everywhere. Have you see it? Can the SQL documentation team be
> so kind and provide us with something here about this new feature?
>
|||Thanks, I'm aware of the help text of the /? switch. A better
documentation of the dump text is needed to parse it. For example, in
some cases there's a dot (.) at the beginning of an index entry line.
The help text is mute regarding what it means and it focuses on the
different switches, not the syntax of the dump.
|||You're welcome, Cybergoo,
Yep, it is... Actually the SQL Server 2005 (June CTP / IDW15 / Beta3)
version is *mute* on a lot of FTS-related topics, IMHO.
Hopefully, the next CTP version will be more *verbose*, as the FTS-related
topics leave a lot to be desired... Look for entries at my blog on this
topic in the near future!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<cybergoo@.gmail.com> wrote in message
news:1125101091.602442.196550@.g49g2000cwa.googlegr oups.com...
> Thanks, I'm aware of the help text of the /? switch. A better
> documentation of the dump text is needed to parse it. For example, in
> some cases there's a dot (.) at the beginning of an index entry line.
> The help text is mute regarding what it means and it focuses on the
> different switches, not the syntax of the dump.
>
words list from a full text catalog and I can't find good documentation.
I've looked everywhere. Have you see it? Can the SQL documentation team be
so kind and provide us with something here about this new feature?
Here is how I dump the catalog I created in C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\FTData\XIN
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>cidump -dump
"C:\Program Files\Microsoft SQL
Server"\MSSQL.1\MSSQL\FTData\XIN\MssearchCatalogDi r -dir k
Note how I use the subdirectory called MSSearchCatalogDir.
For more documentation did you check this doc?
Use cidump.exe for:
- Dumping the content of the catalog or a specified index
cidump -dump <catalog_path> [options]
- Checking the integrity of the catalog or a specified index
cidump -check <catalog_path> [options]
- Computing statistics on the content of the catalog or a specified index
cidump -stats <catalog_path> [options]
=== Common options:
================================================== =======
-i <IndexId> - Apply the operation on this index.
In this option is not used, all the indexes are
processed.
-e <IndexId> - Exclude this index.
Use it multiple times for excluding more than one index.
-u <filepath> - Dump to a UNICODE file.
-not_read_only - Open CiStorage not read only
For when the index table streams are not in sync
=== Specific options to be used with -dump:
=================================
Select what to dump:
-x [<format>] - Dump the index.
<format> can be: k - keys only (default)
kw - keys and wids (document IDs)
kwo - dump keys, wids, and occurences
kwc - keys and WidCounts per key
sbr - the SortByRank index for each key
-dir - Dump the index directory.
-widset [<format>] - Dump the widset files.
<format> can be:
wid - iterate the wids in the widset (default)
wc - widcounts only
hdr - widset header only
To determine in which index a given wid is fresh use:
cidump -dump <path> -widset -w <wid>
Select the keys to dump:
-k <startKey> [<endKey>] - Dump only the the keys in an given range.
-kn <keyCount> - Stop after displaying a number of keys.
-w <wid> - Dump only the keys that contain the given wid.
-p <PropID> - Dump only keys with the given PropID.
Select the wids to dump:
-wr <startWid> [<endWid>] - Dump only the wids in a given range.
-wn <widCount> - Stop after displaying a number of wids per each key.
Select the format of the data:
-d - Display all numbers in decimal.
(default is hex for IDs and offsets decimal for counts and sizes).
-h - Display all numbers in hexadecimal.
-b - Display the internal representaion of the keys (bytes in hex).
=== Notes:
* IndexIds, wids and PropIds can be entered as hex numbers preceeded by
"0x".
* The keys can be entered as strings or as a sequence of bytes in hex,
between
quotes and parantheses: abc is the same as "(00 00 61 00 62 00 63)"
Run cidump -?key to get help on the input format for keys.
=== Samples:
Check the integrity of all the indexes:
cidump -check c:\catalog
Display global statistics for the index 0001002A:
cidump -stats c:\catalog -g -i 0x1002A
Dump from all the indexes the keys in the range aaa - bbb.
Use the format that also shows the widcounts (number of docs with that key):
cidump -dump c:\catalog -x kwc -k aaa bbb
Dump the keys that contain the the wid 9001:
cidump -dump c:\catalog -x -w 9001
Dump the first 10 wids from the sorted by rank index for the key "tokenone":
cidump -dump c:\catalog -x sbr -k tokenone -kn 1 -wn 10
Dump the directory
cidump -dump s:\encpath\encarta -dir kbo
===
Run cidump -? to display advanced options.
Advanced options:
=== Specific options for dump:
==============================================
-x [<format>] - Dump the index
<format> can be also:
ks - statistics per key
kw+ - keys and wids and wid metadata (all but
occurrences)
kp - list of keys with position in index
kwp - list of keys and wids with position in index
kph - dump phrases that contain a given key.
Requires -kph and -pch options.
ph - dump all phrases in more than a given nr of docs
Requires -phc option.
-dir [<format>] - Dump the index directory
<format> can be:
kbo - keys and BitOffset in index (default)
kp - list of keys with position in the directory file
-phk <key> - Used only with "-x kph" dump format.
Dump phrases that contain the given <key>. Use also -phc.
-phc <minWidCount> - Used only with "-x ph" and "-x kph" dump formats.
Dump all the 2-3 word phrases that occur in at least <minOccCount>
documents in the indexed corpus.
-kwc <minWidCount> <maxWidCount> - Display only keys in a widcount range.
-alr - Display the allocated ranges for the master index.
-fbs - Force binary search when dumping widsets.
-rec <index> <type> <maxWid> <R/W> - Dump a standalone index.
Don't use the Index Table.
<index> - the index id (e.g.) 0x1001C
<type> - 0 - master index; 1 - shadow index.
<maxWid> - the maximum workid in the index.
<R/W> - 0 - complete index; 1 - incomplete index (write mode)
=== Specific options for check: ============================================
-k <start_key> [<end_key>] - Process only the area of the index
for the given key range.
=== Specific options for statistics: =======================================
Computing default statistics require a full scan of the index file.
Additional options:
-o - display default statistics and also occurence distribution.
-wc - display widcount distribution (iterates through keys only).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"cybergoo" <cybergoo@.newsgroup.nospam> wrote in message
news:%23448WlToFHA.1948@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use the new cidump tool of SQL Server 2005 Beta 2 to get a
> words list from a full text catalog and I can't find good documentation.
> I've looked everywhere. Have you see it? Can the SQL documentation team be
> so kind and provide us with something here about this new feature?
>
|||Cybergoo,
The SQL Server 2005 Beta2 as well as Bet3 Books Online (BOL) have yet to be
updated on this most useful SQL FTS utiltity. In the meantime, you should
use the cidump /? to get the syntax as well as example of use:
-- on my Win2003 server...
f:
cd:\MSSQL90\MSSQL.1\MSSQL\Binn
cidump /?
-- edited output:
Use cidump.exe for:
- Dumping the content of the catalog or a specified index
cidump -dump <catalog_path> [options]
- Checking the integrity of the catalog or a specified index
cidump -check <catalog_path> [options]
- Computing statistics on the content of the catalog or a specified index
cidump -stats <catalog_path> [options]
...
Display global statistics for the index 0001002A:
cidump -stats c:\catalog -g -i 0x1002A
cidump /?
Advanced options:
-x [<format>] - Dump the index
<format> can be also:
ks - statistics per key
kw+ - keys and wids and wid metadata (all but
occurrences)
kp - list of keys with position in index
kwp - list of keys and wids with position in index
kph - dump phrases that contain a given key.
Requires -kph and -pch options.
ph - dump all phrases in more than a given nr of docs
Requires -phc option.
-dir [<format>] - Dump the index directory
<format> can be:
kbo - keys and BitOffset in index (default)
kp - list of keys with position in the directory file
-phk <key> - Used only with "-x kph" dump format.
Dump phrases that contain the given <key>. Use also -phc.
-phc <minWidCount> - Used only with "-x ph" and "-x kph" dump formats.
Dump all the 2-3 word phrases that occur in at least <minOccCount>
documents in the indexed corpus.
-kwc <minWidCount> <maxWidCount> - Display only keys in a widcount range.
-alr - Display the allocated ranges for the master index.
-fbs - Force binary search when dumping widsets.
-rec <index> <type> <maxWid> <R/W> - Dump a standalone index.
Don't use the Index Table.
<index> - the index id (e.g.) 0x1001C
<type> - 0 - master index; 1 - shadow index.
<maxWid> - the maximum workid in the index.
<R/W> - 0 - complete index; 1 - incomplete index (write mode)
Enjoy!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"cybergoo" <cybergoo@.newsgroup.nospam> wrote in message
news:%23448WlToFHA.1948@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use the new cidump tool of SQL Server 2005 Beta 2 to get a
> words list from a full text catalog and I can't find good documentation.
> I've looked everywhere. Have you see it? Can the SQL documentation team be
> so kind and provide us with something here about this new feature?
>
|||Thanks, I'm aware of the help text of the /? switch. A better
documentation of the dump text is needed to parse it. For example, in
some cases there's a dot (.) at the beginning of an index entry line.
The help text is mute regarding what it means and it focuses on the
different switches, not the syntax of the dump.
|||You're welcome, Cybergoo,
Yep, it is... Actually the SQL Server 2005 (June CTP / IDW15 / Beta3)
version is *mute* on a lot of FTS-related topics, IMHO.
Hopefully, the next CTP version will be more *verbose*, as the FTS-related
topics leave a lot to be desired... Look for entries at my blog on this
topic in the near future!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<cybergoo@.gmail.com> wrote in message
news:1125101091.602442.196550@.g49g2000cwa.googlegr oups.com...
> Thanks, I'm aware of the help text of the /? switch. A better
> documentation of the dump text is needed to parse it. For example, in
> some cases there's a dot (.) at the beginning of an index entry line.
> The help text is mute regarding what it means and it focuses on the
> different switches, not the syntax of the dump.
>
Monday, March 19, 2012
Chr(13) not working as intended
When I use Chr(13) in my textbox expression, it does not produce a carriage return in the text. For example if I use "Test 1" & Chr(13) & "Test2", Test1 and Test2 come in the same line but if I use just Chr(10) in the place of Chr(13), it works as intended. In Crystal reports, Chr(13) does produce a carriage return and the text come in 2 different lines.
Thanks,
Shyam
It is a common issue here and you are right the difference with crystal is apparent.
You have to use a line feed to get an actual line return (10) as the carraige return (13) just feeds the symbol for systems to translate as they feel and the HTML report viewer will not action a (13) whereas the crystal viewer does.
Sunday, March 11, 2012
chinese full-text search
Hello,
Request your help on querying a Fulltext indexed table with Chinese Text.
I have sql server 2000, and a database with Chinese collation, a table is
full text indexed with Chinese(RPC) word breakers.
I am able to retrieve the Chinese text from the table and successfully
display the same on the browser but am not able to search.
What is happening is when I run the below query in query analyzer, it is
returning me a result set
SELECT * from DCNews_Live where contains (*,' "chinese text" ')
Where as when I run the same query from ASP (Active Server Pages) it is not
returning any values.
Can u explain where I am going wrong?
The ASP code that I use is
strSearch = trim(request.Form("txtSearch"))
strSql = "SELECT * from Table where contains(*,' """ & strSearch
& """ ')"
objRs.Open strSql,objCon
The Recordset is empty.
Can u please help out of this
Regards,
Prudhvi Raju M
Did you set your session code page for Chinese simplified or Traditional?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Prudhvi" <Prudhvi@.discussions.microsoft.com> wrote in message
news:0A2D4FD7-8E9F-4C6C-BC5E-D0CA84B6E284@.microsoft.com...
> Hello,
> Request your help on querying a Fulltext indexed table with Chinese Text.
> I have sql server 2000, and a database with Chinese collation, a table is
> full text indexed with Chinese(RPC) word breakers.
> I am able to retrieve the Chinese text from the table and successfully
> display the same on the browser but am not able to search.
>
>
> What is happening is when I run the below query in query analyzer, it is
> returning me a result set
>
> SELECT * from DCNews_Live where contains (*,' "chinese text" ')
>
> Where as when I run the same query from ASP (Active Server Pages) it is
> not
> returning any values.
>
> Can u explain where I am going wrong?
>
> The ASP code that I use is
>
> strSearch = trim(request.Form("txtSearch"))
>
> strSql = "SELECT * from Table where contains(*,' """ &
> strSearch
> & """ ')"
>
> objRs.Open strSql,objCon
>
> The Recordset is empty.
>
> Can u please help out of this
>
> Regards,
> Prudhvi Raju M
>
>
|||I've haven't set the session code page but included the meta tag
<META http-equiv=Content-Type content="text/html; charset=windows-1252">
"Hilary Cotter" wrote:
> Did you set your session code page for Chinese simplified or Traditional?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Prudhvi" <Prudhvi@.discussions.microsoft.com> wrote in message
> news:0A2D4FD7-8E9F-4C6C-BC5E-D0CA84B6E284@.microsoft.com...
>
>
|||You need to set the code page in your asp page.
Run profiler to see the command that hits SQL Server. I think what is
happening is that the chinese characters being entered on your web server
are not making to SQL Server as Chinese characters, rather their character
representations in the 1252 code page. Hence no hits
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Prudhvi Raju" <Prudhvi Raju@.discussions.microsoft.com> wrote in message
news:8B136C72-FD83-4BF8-8A59-C5B263294E46@.microsoft.com...[vbcol=seagreen]
> I've haven't set the session code page but included the meta tag
> <META http-equiv=Content-Type content="text/html; charset=windows-1252">
> "Hilary Cotter" wrote:
|||Thanks Hilary for the advice.
I've included BIG5 codepage and also changed the meta tag to
<META http-equiv=Content-Type content="text/html; charset=big5">
and am now able to get the result set for chinese search.
Thank You.
Regards,
Prudhvi
"Hilary Cotter" wrote:
> You need to set the code page in your asp page.
> Run profiler to see the command that hits SQL Server. I think what is
> happening is that the chinese characters being entered on your web server
> are not making to SQL Server as Chinese characters, rather their character
> representations in the 1252 code page. Hence no hits
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Prudhvi Raju" <Prudhvi Raju@.discussions.microsoft.com> wrote in message
> news:8B136C72-FD83-4BF8-8A59-C5B263294E46@.microsoft.com...
>
>
Request your help on querying a Fulltext indexed table with Chinese Text.
I have sql server 2000, and a database with Chinese collation, a table is
full text indexed with Chinese(RPC) word breakers.
I am able to retrieve the Chinese text from the table and successfully
display the same on the browser but am not able to search.
What is happening is when I run the below query in query analyzer, it is
returning me a result set
SELECT * from DCNews_Live where contains (*,' "chinese text" ')
Where as when I run the same query from ASP (Active Server Pages) it is not
returning any values.
Can u explain where I am going wrong?
The ASP code that I use is
strSearch = trim(request.Form("txtSearch"))
strSql = "SELECT * from Table where contains(*,' """ & strSearch
& """ ')"
objRs.Open strSql,objCon
The Recordset is empty.
Can u please help out of this
Regards,
Prudhvi Raju M
Did you set your session code page for Chinese simplified or Traditional?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Prudhvi" <Prudhvi@.discussions.microsoft.com> wrote in message
news:0A2D4FD7-8E9F-4C6C-BC5E-D0CA84B6E284@.microsoft.com...
> Hello,
> Request your help on querying a Fulltext indexed table with Chinese Text.
> I have sql server 2000, and a database with Chinese collation, a table is
> full text indexed with Chinese(RPC) word breakers.
> I am able to retrieve the Chinese text from the table and successfully
> display the same on the browser but am not able to search.
>
>
> What is happening is when I run the below query in query analyzer, it is
> returning me a result set
>
> SELECT * from DCNews_Live where contains (*,' "chinese text" ')
>
> Where as when I run the same query from ASP (Active Server Pages) it is
> not
> returning any values.
>
> Can u explain where I am going wrong?
>
> The ASP code that I use is
>
> strSearch = trim(request.Form("txtSearch"))
>
> strSql = "SELECT * from Table where contains(*,' """ &
> strSearch
> & """ ')"
>
> objRs.Open strSql,objCon
>
> The Recordset is empty.
>
> Can u please help out of this
>
> Regards,
> Prudhvi Raju M
>
>
|||I've haven't set the session code page but included the meta tag
<META http-equiv=Content-Type content="text/html; charset=windows-1252">
"Hilary Cotter" wrote:
> Did you set your session code page for Chinese simplified or Traditional?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Prudhvi" <Prudhvi@.discussions.microsoft.com> wrote in message
> news:0A2D4FD7-8E9F-4C6C-BC5E-D0CA84B6E284@.microsoft.com...
>
>
|||You need to set the code page in your asp page.
Run profiler to see the command that hits SQL Server. I think what is
happening is that the chinese characters being entered on your web server
are not making to SQL Server as Chinese characters, rather their character
representations in the 1252 code page. Hence no hits
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Prudhvi Raju" <Prudhvi Raju@.discussions.microsoft.com> wrote in message
news:8B136C72-FD83-4BF8-8A59-C5B263294E46@.microsoft.com...[vbcol=seagreen]
> I've haven't set the session code page but included the meta tag
> <META http-equiv=Content-Type content="text/html; charset=windows-1252">
> "Hilary Cotter" wrote:
|||Thanks Hilary for the advice.
I've included BIG5 codepage and also changed the meta tag to
<META http-equiv=Content-Type content="text/html; charset=big5">
and am now able to get the result set for chinese search.
Thank You.
Regards,
Prudhvi
"Hilary Cotter" wrote:
> You need to set the code page in your asp page.
> Run profiler to see the command that hits SQL Server. I think what is
> happening is that the chinese characters being entered on your web server
> are not making to SQL Server as Chinese characters, rather their character
> representations in the 1252 code page. Hence no hits
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Prudhvi Raju" <Prudhvi Raju@.discussions.microsoft.com> wrote in message
> news:8B136C72-FD83-4BF8-8A59-C5B263294E46@.microsoft.com...
>
>
Thursday, March 8, 2012
Chinese characters
Hello all,
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
is comeing out as Squares. However, I can paste the value into here (�) and
you can see it fine.
Can anyone plese help?
Thanks,
JonProblem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put in
> Middle Eastern language, such as Arabic and the text displays as it should.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
> is comeing out as Squares. However, I can paste the value into here (�) and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
is comeing out as Squares. However, I can paste the value into here (�) and
you can see it fine.
Can anyone plese help?
Thanks,
JonProblem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put in
> Middle Eastern language, such as Arabic and the text displays as it should.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
> is comeing out as Squares. However, I can paste the value into here (�) and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
Chinese characters
Hello all,
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the tex
t
is comeing out as Squares. However, I can paste the value into here (要) an
d
you can see it fine.
Can anyone plese help?
Thanks,
JonProblem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put
in
> Middle Eastern language, such as Arabic and the text displays as it should
.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the t
ext
> is comeing out as Squares. However, I can paste the value into here (要)
and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the tex
t
is comeing out as Squares. However, I can paste the value into here (要) an
d
you can see it fine.
Can anyone plese help?
Thanks,
JonProblem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put
in
> Middle Eastern language, such as Arabic and the text displays as it should
.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the t
ext
> is comeing out as Squares. However, I can paste the value into here (要)
and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
Chinese characters
Hello all,
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
is comeing out as Squares. However, I can paste the value into here (要) and
you can see it fine.
Can anyone plese help?
Thanks,
Jon
Problem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put in
> Middle Eastern language, such as Arabic and the text displays as it should.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
> is comeing out as Squares. However, I can paste the value into here (要) and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
I have a column in a database table that is set to an nvarchar. I can put in
Middle Eastern language, such as Arabic and the text displays as it should.
However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
is comeing out as Squares. However, I can paste the value into here (要) and
you can see it fine.
Can anyone plese help?
Thanks,
Jon
Problem solved!
By default, XP Pro does not install the East Asis languages, install these
and all is peachy!
Thanks
Jon
"Jon" wrote:
> Hello all,
> I have a column in a database table that is set to an nvarchar. I can put in
> Middle Eastern language, such as Arabic and the text displays as it should.
> However, if I enter any text from Asia Pacific (Chinese, Taiwan etc) the text
> is comeing out as Squares. However, I can paste the value into here (要) and
> you can see it fine.
> Can anyone plese help?
> Thanks,
> Jon
Wednesday, March 7, 2012
CHECKSUM() of binary data
Hello,
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
KaramCan anybody help me?|||CHECKSUM() by parts.
split ntext to blocks of nvarchar(4000) and use check sum.
Originally posted by karam_chand03
Hello,
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
Karam|||Thanks for the answer.
Can I use it for datatypes like image, sql_variant?
A simple code on how to break it up and get a hash value will be helpful.|||Hello,
I am still unable to figure out but how can I generate checksum of the whole column in one SQL query? Is it possible?
Karam|||you can generate checksum() to whole column
like
select checksum(*) from tablename
provided none of the columns are of text ntext image data types
Originally posted by karam_chand03
Hello,
I am still unable to figure out but how can I generate checksum of the whole column in one SQL query? Is it possible?
Karam|||Hello,
Thats the problem. I do have those unsupported columns and I need to generate hash value from it. In MySQL (where I come from) has a md5() function to generate hash value dfor every type of data it supports.
What I can think of is that I can convert every such data to varbinary and then employ checksum on it. But that is failing as if I convert some char values to varbinary and then running checksum on it, it is returning the same data.
Is it feasible?
Karam|||I don't think so.
convert text to varchar and ntext to nvarchar....
and check.
Originally posted by karam_chand03
What I can think of is that I can convert every such data to varbinary and then employ checksum on it. But that is failing as if I convert some char values to varbinary and then running checksum on it, it is returning the same data.
Is it feasible?
Karam
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
KaramCan anybody help me?|||CHECKSUM() by parts.
split ntext to blocks of nvarchar(4000) and use check sum.
Originally posted by karam_chand03
Hello,
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
Karam|||Thanks for the answer.
Can I use it for datatypes like image, sql_variant?
A simple code on how to break it up and get a hash value will be helpful.|||Hello,
I am still unable to figure out but how can I generate checksum of the whole column in one SQL query? Is it possible?
Karam|||you can generate checksum() to whole column
like
select checksum(*) from tablename
provided none of the columns are of text ntext image data types
Originally posted by karam_chand03
Hello,
I am still unable to figure out but how can I generate checksum of the whole column in one SQL query? Is it possible?
Karam|||Hello,
Thats the problem. I do have those unsupported columns and I need to generate hash value from it. In MySQL (where I come from) has a md5() function to generate hash value dfor every type of data it supports.
What I can think of is that I can convert every such data to varbinary and then employ checksum on it. But that is failing as if I convert some char values to varbinary and then running checksum on it, it is returning the same data.
Is it feasible?
Karam|||I don't think so.
convert text to varchar and ntext to nvarchar....
and check.
Originally posted by karam_chand03
What I can think of is that I can convert every such data to varbinary and then employ checksum on it. But that is failing as if I convert some char values to varbinary and then running checksum on it, it is returning the same data.
Is it feasible?
Karam
Tuesday, February 14, 2012
checkboxlist sql database
I have a form with text boxes and checkboxlists that a user will fill out and click submit. When the user clicks submit, it will update the sql database. In my sql database I have checkbox fields. My question is how can I use the selected items in a checkboxlist to update the sql database individual check boxes. Below is the code I have so far that works for a text box:
PartialClass windrockformInherits System.Web.UI.Page
ProtectedSub submitButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles submitButton.ClickDim dashdatasourceAsNew SqlDataSource
dashdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("WindrockIssuesConnectionString").ToStringdashdatasource.InsertCommandType = SqlDataSourceCommandType.Text
dashdatasource.InsertCommand ="INSERT INTO IssueLog (initiator) VALUES (@.initiator)"
dashdatasource.InsertParameters.Add("initiator", initiatorTextBox.Text)Dim rowsAffectedAsInteger = 0
Try
rowsAffected = dashdatasource.Insert()
Catch exAs ExceptionServer.Transfer("problem.aspx")
EndTry
If rowsAffected <> 1Then
Server.Transfer("problem.aspx")Else
Server.Transfer("confirm.aspx")EndIf
EndSubEndClass
I am new to asp.net and would appreciate the help.
Thanks,
I believe the key you are looking for isCheckBox11.Checked =False
|||
What are checkbox datatypes in SQL server? Maybe you mean they are of BIT datatype?
I think you will probably have to loop through your checkbox list and check each box to see if its checked, and set a parameter accordingly.
|||Hi nkair19 ,
When the user clicks submit, it will update the sql database. In my sql database I have checkbox fields. My question is how can I use the selected items in a checkboxlist to update the sql database individual check boxes
I think maybe you mean you want to update certain data fields in your sql server database based on selected values in your checkboxlist. If I've understood you wrong, please feel free to tell me, thanks.
I think you can use an arraylist as a bridge between your checkboxlist and database filed. Like this:
ArrayList list=new ArrayList();
for (int i=0; i<checkboxlist1.Items.Count; i++)
{
if (checkboxlist1.Items[i].Selected)
{
list.add("the value you want to assign to the database field when checkbox item checked");
}
else
list.add("the value you want to assign to the database field when checkbox item not checked");
}
After this,in your sqlcommand text, you can refer to the corresponding arraylist value directly.
Hope my suggestion helps
|||Did I forget to mention that I am using vb, not C#?
|||I have this so far:
Dim listAsNew ArrayList()3:
4:Dim iAsInteger = 0
5:While i < pacheckboxlist.Items.Count6:
7:If pacheckboxlist.Items(i).SelectedThen
8:
9: list.Add("win6310_pa--is this where I add the check box name that I have in the SQL database?")10:Else
11: list.Add("the value you want to assign to the database field when checkbox item not checked--I don't want anything to happen if nothing is checked because I have other checkboxlist groups to choose from")12:
13:
14:EndIf
15: System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
16:EndWhile
After this,in your sqlcommand text, you can refer to the corresponding arraylist value directly. --confused here.
Thanks.
Subscribe to:
Posts (Atom)