Hi and Help!!
I'm using Bulk Insert to read a text file that is on a different server. I
get an access denied message.
- If I change the name to a non-existant file,
I get an error that it can't find that file, so it can see it.
- I can successfully read a copy that I stored on the SQL server server.
I pretty much have whatever rights I need, personally, but I'm guessing I
need to set something up elsewhere.
Any suggestions?
Thanks,
ArtTry checking the account that the server is running as, for example if
LocalSystem is being used, it may not have the permissions to access the
file. I always use a service account and grant permissions to a specific
folder on the server to pass to.
"Art" wrote:
> Hi and Help!!
> I'm using Bulk Insert to read a text file that is on a different server.
I
> get an access denied message.
> - If I change the name to a non-existant file,
> I get an error that it can't find that file, so it can see it.
> - I can successfully read a copy that I stored on the SQL server server.
> I pretty much have whatever rights I need, personally, but I'm guessing I
> need to set something up elsewhere.
> Any suggestions?
> Thanks,
> Art|||Josh,
Thanks for the suggestion -- I won't be able to try it until Tuesday.
Unfortunatly I still don't know much about how to use SQL Server. I've
learned enough SQL and enough about Stored Procedures, but I don't actually
know too much about the system itself. I guess that's something that I had
better change.
Any suggestions on a good book? I'm currently using 2000, but in a few
months will upgrade to 2005.
Thanks again,
Art
"Josh Crosby" wrote:
> Try checking the account that the server is running as, for example if
> LocalSystem is being used, it may not have the permissions to access the
> file. I always use a service account and grant permissions to a specific
> folder on the server to pass to.
> "Art" wrote:
>|||sure, great book on SQL 2005 written by Michael Otey i'm writing a review
for, here's the info.
SQL Server 2005: Developer's Guide
ISBN: 0-07-226099-8
--
Josh Crosby
"Art" wrote:
> Josh,
> Thanks for the suggestion -- I won't be able to try it until Tuesday.
> Unfortunatly I still don't know much about how to use SQL Server. I've
> learned enough SQL and enough about Stored Procedures, but I don't actuall
y
> know too much about the system itself. I guess that's something that I ha
d
> better change.
> Any suggestions on a good book? I'm currently using 2000, but in a few
> months will upgrade to 2005.
> Thanks again,
> Art
> "Josh Crosby" wrote:
>
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Thursday, March 29, 2012
Tuesday, March 20, 2012
access 2003 to SQL server migration
This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C5FBF1.DED8AFC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
We are migrating one of our clients databases from an access 2003 = database to their sql server 2000 database being that i have mostly = worked with oracle datbases I had come up with a list of things that we = were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and = not insert update or delete from any exisitng table and any new tables = without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes = QA have them promoted to DBO or can you only do that with stored = procedures
--=_NextPart_000_0008_01C5FBF1.DED8AFC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
We are migrating one of our clients = databases from an access 2003 database to their sql server 2000 database being that i = have mostly worked with oracle datbases I had come up with a list of things = that we were going to have to implement but i had some questions.
can you set it so certain roles can = only select from a table and not insert update or delete from any exisitng table = and any new tables without having to do it for each individual new = table
Can users create tables under thier = user and then once it passes QA have them promoted to DBO or can you only do that = with stored procedures
--=_NextPart_000_0008_01C5FBF1.DED8AFC0--This is a multi-part message in MIME format.
--=_NextPart_000_002A_01C5FC12.D9101D50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
1. You can make them a member of the datareader role and then they have =access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your =production server anyway.
-- Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" =<garytNOSPAM@.spatialmapping.com> wrote in message =news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 =database to their sql server 2000 database being that i have mostly =worked with oracle datbases I had come up with a list of things that we =were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and =not insert update or delete from any exisitng table and any new tables =without having to do it for each individual new table 2.. Can users create tables under thier user and then once it passes =QA have them promoted to DBO or can you only do that with stored =procedures
--=_NextPart_000_002A_01C5FC12.D9101D50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
1. You can make them a member of the =datareader role and then they have access to read all tables but nothing else.
2. Basically no. Development and QA =should not be done on your production server anyway.
-- Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" wrote in message news:ZA0mf.139758$y_1.98475=@.edtnps89...
We are migrating one of our clients =databases from an access 2003 database to their sql server 2000 database being =that i have mostly worked with oracle datbases I had come up with a list of =things that we were going to have to implement but i had some =questions.
can you set it so certain roles can =only select from a table and not insert update or delete from any exisitng table =and any new tables without having to do it for each individual new =table Can users create tables under thier =user and then once it passes QA have them promoted to DBO or can you only do =that with stored procedures
--=_NextPart_000_002A_01C5FC12.D9101D50--|||This is a multi-part message in MIME format.
--=_NextPart_000_0036_01C5FBFE.57EB4AB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
makes sense on #2 i jsut wanted to rule it out because i'm sure its =going to get asked of me.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message =news:%23JvZHzD$FHA.328@.TK2MSFTNGP14.phx.gbl...
1. You can make them a member of the datareader role and then they =have access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your =production server anyway.
-- Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" =<garytNOSPAM@.spatialmapping.com> wrote in message =news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 =database to their sql server 2000 database being that i have mostly =worked with oracle datbases I had come up with a list of things that we =were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table =and not insert update or delete from any exisitng table and any new =tables without having to do it for each individual new table 2.. Can users create tables under thier user and then once it =passes QA have them promoted to DBO or can you only do that with stored =procedures
--=_NextPart_000_0036_01C5FBFE.57EB4AB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
makes sense on #2 i jsut wanted to rule =it out because i'm sure its going to get asked of me.
"Andrew J. Kelly" wrote in message news:%23JvZHzD$FHA.3=28@.TK2MSFTNGP14.phx.gbl...
1. You can make them a member of the =datareader role and then they have access to read all tables but nothing =else.
2. Basically no. Development and QA =should not be done on your production server anyway.
-- Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" wrote in message news:ZA0mf.139758$y_1.98475=@.edtnps89...
We are migrating one of our clients =databases from an access 2003 database to their sql server 2000 database being =that i have mostly worked with oracle datbases I had come up with a list of =things that we were going to have to implement but i had some questions.
can you set it so certain roles =can only select from a table and not insert update or delete from any =exisitng table and any new tables without having to do it for each =individual new table Can users create tables under =thier user and then once it passes QA have them promoted to DBO or can you only =do that with stored =procedures
--=_NextPart_000_0036_01C5FBFE.57EB4AB0--
--=_NextPart_000_0008_01C5FBF1.DED8AFC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
We are migrating one of our clients databases from an access 2003 = database to their sql server 2000 database being that i have mostly = worked with oracle datbases I had come up with a list of things that we = were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and = not insert update or delete from any exisitng table and any new tables = without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes = QA have them promoted to DBO or can you only do that with stored = procedures
--=_NextPart_000_0008_01C5FBF1.DED8AFC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
We are migrating one of our clients = databases from an access 2003 database to their sql server 2000 database being that i = have mostly worked with oracle datbases I had come up with a list of things = that we were going to have to implement but i had some questions.
can you set it so certain roles can = only select from a table and not insert update or delete from any exisitng table = and any new tables without having to do it for each individual new = table
Can users create tables under thier = user and then once it passes QA have them promoted to DBO or can you only do that = with stored procedures
--=_NextPart_000_0008_01C5FBF1.DED8AFC0--This is a multi-part message in MIME format.
--=_NextPart_000_002A_01C5FC12.D9101D50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
1. You can make them a member of the datareader role and then they have =access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your =production server anyway.
-- Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" =<garytNOSPAM@.spatialmapping.com> wrote in message =news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 =database to their sql server 2000 database being that i have mostly =worked with oracle datbases I had come up with a list of things that we =were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and =not insert update or delete from any exisitng table and any new tables =without having to do it for each individual new table 2.. Can users create tables under thier user and then once it passes =QA have them promoted to DBO or can you only do that with stored =procedures
--=_NextPart_000_002A_01C5FC12.D9101D50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
1. You can make them a member of the =datareader role and then they have access to read all tables but nothing else.
2. Basically no. Development and QA =should not be done on your production server anyway.
-- Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)"
We are migrating one of our clients =databases from an access 2003 database to their sql server 2000 database being =that i have mostly worked with oracle datbases I had come up with a list of =things that we were going to have to implement but i had some =questions.
can you set it so certain roles can =only select from a table and not insert update or delete from any exisitng table =and any new tables without having to do it for each individual new =table Can users create tables under thier =user and then once it passes QA have them promoted to DBO or can you only do =that with stored procedures
--=_NextPart_000_002A_01C5FC12.D9101D50--|||This is a multi-part message in MIME format.
--=_NextPart_000_0036_01C5FBFE.57EB4AB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
makes sense on #2 i jsut wanted to rule it out because i'm sure its =going to get asked of me.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message =news:%23JvZHzD$FHA.328@.TK2MSFTNGP14.phx.gbl...
1. You can make them a member of the datareader role and then they =have access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your =production server anyway.
-- Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" =<garytNOSPAM@.spatialmapping.com> wrote in message =news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 =database to their sql server 2000 database being that i have mostly =worked with oracle datbases I had come up with a list of things that we =were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table =and not insert update or delete from any exisitng table and any new =tables without having to do it for each individual new table 2.. Can users create tables under thier user and then once it =passes QA have them promoted to DBO or can you only do that with stored =procedures
--=_NextPart_000_0036_01C5FBFE.57EB4AB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
makes sense on #2 i jsut wanted to rule =it out because i'm sure its going to get asked of me.
"Andrew J. Kelly"
1. You can make them a member of the =datareader role and then they have access to read all tables but nothing =else.
2. Basically no. Development and QA =should not be done on your production server anyway.
-- Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)"
We are migrating one of our clients =databases from an access 2003 database to their sql server 2000 database being =that i have mostly worked with oracle datbases I had come up with a list of =things that we were going to have to implement but i had some questions.
can you set it so certain roles =can only select from a table and not insert update or delete from any =exisitng table and any new tables without having to do it for each =individual new table Can users create tables under =thier user and then once it passes QA have them promoted to DBO or can you only =do that with stored =procedures
--=_NextPart_000_0036_01C5FBFE.57EB4AB0--
Sunday, March 11, 2012
Accent-sensitive
My SQL is instaled with CAse-Insensitive and Accent-Insensitive, but when I
search some thing with Full Text Search the result is diferent using accent
or not.
My server is a Windows 2000 server and MS SQL Server 2000.
Tia
Marcos Stabel
Unfortunately SQL FTS is not Accent Sensitive, ie a search on a word like cafe will not match with a search on a word like cafe (with an accent).
You have to trap for words which could be accented in your search phrase and then expand them into a search on the accented and unaccented versions of this work.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I Think you want to say the SQL FTS is not Accent Insensitive. If it was Accent Insensitive, Marco would have no problem !
|||Are you sure, Hilary, there is no way to change the SQL server'catalog sensitivity ?
|||Accent sensitive means that when I query on cafe, in a table like this
select * from TableName where col1='cafe' I will not get rows returned to
the accented version of cafe.
Accent insensitive means that I will.
With SQL FTS, the tokens found in your columns you are full text indexing
are stored as unicode, so a cafe is a different unicode sequence than the
accented version of cafe.
You can select how SQL Server treats accents, but not MSSearch, which is the
engine which does your searching in SQLFTS.
So to be totally correct - SQL FTS is accent sensitive or not accent
insensitive. Thanks for the correction.
"Syl20" <anonymous@.discussions.microsoft.com> wrote in message
news:88DDAA02-A424-4FE4-9E3A-F797ED2D543B@.microsoft.com...
> I Think you want to say the SQL FTS is not Accent Insensitive. If it was
Accent Insensitive, Marco would have no problem !
|||There is no way to change the accent handling capabilities of SQL Server
FTS, other than expanding your search phrase to search on the accented and
unaccented versions of the phrase.
"Syl20" <anonymous@.discussions.microsoft.com> wrote in message
news:F2EEC4B5-67E6-4AFB-B2D9-700490242FC5@.microsoft.com...
> Are you sure, Hilary, there is no way to change the SQL server'catalog
sensitivity ?
|||Yes, you are right.
This possibility will be available with Microsoft SQL-Server 2005 ("Yukon")
|||Syl20,
Yes, this feature will be in Microsoft SQL-Server 2005 ("Yukon"), for
example:
USE Adventureworks
GO
CREATE FULLTEXT CATALOG Employee_FTC WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT INDEX ON Employee(FirstName, LastName, Title, EmailAddress)
KEY INDEX PK_Employee_EmployeeID ON Employee_FTC
Regards,
John
"Syl20" <anonymous@.discussions.microsoft.com> wrote in message
news:A9DEF7CE-13B2-49E7-86ED-2A9B759B13D3@.microsoft.com...
> Yes, you are right.
> This possibility will be available with Microsoft SQL-Server 2005
("Yukon")
search some thing with Full Text Search the result is diferent using accent
or not.
My server is a Windows 2000 server and MS SQL Server 2000.
Tia
Marcos Stabel
Unfortunately SQL FTS is not Accent Sensitive, ie a search on a word like cafe will not match with a search on a word like cafe (with an accent).
You have to trap for words which could be accented in your search phrase and then expand them into a search on the accented and unaccented versions of this work.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I Think you want to say the SQL FTS is not Accent Insensitive. If it was Accent Insensitive, Marco would have no problem !
|||Are you sure, Hilary, there is no way to change the SQL server'catalog sensitivity ?
|||Accent sensitive means that when I query on cafe, in a table like this
select * from TableName where col1='cafe' I will not get rows returned to
the accented version of cafe.
Accent insensitive means that I will.
With SQL FTS, the tokens found in your columns you are full text indexing
are stored as unicode, so a cafe is a different unicode sequence than the
accented version of cafe.
You can select how SQL Server treats accents, but not MSSearch, which is the
engine which does your searching in SQLFTS.
So to be totally correct - SQL FTS is accent sensitive or not accent
insensitive. Thanks for the correction.
"Syl20" <anonymous@.discussions.microsoft.com> wrote in message
news:88DDAA02-A424-4FE4-9E3A-F797ED2D543B@.microsoft.com...
> I Think you want to say the SQL FTS is not Accent Insensitive. If it was
Accent Insensitive, Marco would have no problem !
|||There is no way to change the accent handling capabilities of SQL Server
FTS, other than expanding your search phrase to search on the accented and
unaccented versions of the phrase.
"Syl20" <anonymous@.discussions.microsoft.com> wrote in message
news:F2EEC4B5-67E6-4AFB-B2D9-700490242FC5@.microsoft.com...
> Are you sure, Hilary, there is no way to change the SQL server'catalog
sensitivity ?
|||Yes, you are right.
This possibility will be available with Microsoft SQL-Server 2005 ("Yukon")
|||Syl20,
Yes, this feature will be in Microsoft SQL-Server 2005 ("Yukon"), for
example:
USE Adventureworks
GO
CREATE FULLTEXT CATALOG Employee_FTC WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT INDEX ON Employee(FirstName, LastName, Title, EmailAddress)
KEY INDEX PK_Employee_EmployeeID ON Employee_FTC
Regards,
John
"Syl20" <anonymous@.discussions.microsoft.com> wrote in message
news:A9DEF7CE-13B2-49E7-86ED-2A9B759B13D3@.microsoft.com...
> Yes, you are right.
> This possibility will be available with Microsoft SQL-Server 2005
("Yukon")
accent sensitive
Hello
just a little question :
in full text search, in SQL Server 2000, how to enable accent-insentive?
for exemple : if I seize "tele", I want to have results with "tl", "tele",
"tle" and "tel"
thanks for your answer!
a+
syl
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
"lobrys" <REMOVE_MElobrys@.orange.com> wrote in message
news:uJKhJpmdFHA.2776@.TK2MSFTNGP10.phx.gbl...
> Hello
> just a little question :
> in full text search, in SQL Server 2000, how to enable accent-insentive?
> for exemple : if I seize "tele", I want to have results with "tl",
"tele",
> "tle" and "tel"
> thanks for your answer!
> a+
> syl
>
just a little question :
in full text search, in SQL Server 2000, how to enable accent-insentive?
for exemple : if I seize "tele", I want to have results with "tl", "tele",
"tle" and "tel"
thanks for your answer!
a+
syl
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
"lobrys" <REMOVE_MElobrys@.orange.com> wrote in message
news:uJKhJpmdFHA.2776@.TK2MSFTNGP10.phx.gbl...
> Hello
> just a little question :
> in full text search, in SQL Server 2000, how to enable accent-insentive?
> for exemple : if I seize "tele", I want to have results with "tl",
"tele",
> "tle" and "tel"
> thanks for your answer!
> a+
> syl
>
accent insensitive full text search
I am running SQL 2000 SP3a on Windows 2000.
Is it possible to obtain accent insensitive results from full-text searches
so that a search for caf will return all records for caf and cafe as if
these were the same?
In the four queries below queries 2a and 2b return the same results as each
other whereas queries 1a and 1b return different results from each other.
1a. select * from MyTable where CONTAINS(*,'cafe')
1b. select * from MyTable where CONTAINS(*,'caf')
2a. select * from MyTable where searchtext like '%cafe%'
2b. select * from Mytable where searchtext like '%caf%'
Thanks
Joseph
No,it is not possible.
This issue has already been discussed in detail several times, do a search
in this newsgroup.
Matthias
"Joseph Tervit" <joseph@.company-net.com> wrote in message
news:eaRfNYeMEHA.1340@.TK2MSFTNGP12.phx.gbl...
> I am running SQL 2000 SP3a on Windows 2000.
> Is it possible to obtain accent insensitive results from full-text
searches
> so that a search for caf will return all records for caf and cafe as if
> these were the same?
> In the four queries below queries 2a and 2b return the same results as
each
> other whereas queries 1a and 1b return different results from each other.
> 1a. select * from MyTable where CONTAINS(*,'cafe')
> 1b. select * from MyTable where CONTAINS(*,'caf')
> 2a. select * from MyTable where searchtext like '%cafe%'
> 2b. select * from Mytable where searchtext like '%caf%'
> Thanks
> Joseph
>
|||Joseph,
Matthias, yes this has been discussed many, many (too many) times in this
newsgroup, IMHO. However, with the new Microsoft newsgroup msg roll-off
policy, the past replies might not be searchable (except via Google groups,
of course! ;-). One easy solution is to simply install SharePoint Server
2001 or 2003 as either will upgrade the MSSearch service to the SPPS version
that does support accent insensitive search.
Regards,
John
"Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
news:409883a9$1@.epflnews.epfl.ch...[vbcol=seagreen]
> No,it is not possible.
> This issue has already been discussed in detail several times, do a search
> in this newsgroup.
> Matthias
>
> "Joseph Tervit" <joseph@.company-net.com> wrote in message
> news:eaRfNYeMEHA.1340@.TK2MSFTNGP12.phx.gbl...
> searches
if[vbcol=seagreen]
> each
other.
>
|||Hi,
I have two questions relating to your suggestion of installing sharepoint.
1. will this work for both sql fts and indexing server fts
(I currently have a file system catolog that I have linked into slq server, I do joined queries that search that catalog and and sql catalog for the same phrases)
2. if so, hwo do I rebuild the catalogs to be case insensitive?
thanks in advance
randyr
"John Kane" wrote:
> Joseph,
> Matthias, yes this has been discussed many, many (too many) times in this
> newsgroup, IMHO. However, with the new Microsoft newsgroup msg roll-off
> policy, the past replies might not be searchable (except via Google groups,
> of course! ;-). One easy solution is to simply install SharePoint Server
> 2001 or 2003 as either will upgrade the MSSearch service to the SPPS version
> that does support accent insensitive search.
> Regards,
> John
>
>
> "Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
> news:409883a9$1@.epflnews.epfl.ch...
> if
> other.
>
>
|||Randyr,
Hmm... a blast from the past... a past posting of mine...
1. will this work for both sql fts and indexing server fts
A. No. While the SQL FTS and Indexing Services use the same basic underlying
technology, they are implemented differently and SharePoint was developed
after both of these products were shipped.
2. if so, how do I rebuild the catalogs to be case insensitive?
A. You cannot rebuild a SQL Server 2000 FT Catalog to be "case insensitive"
or "accent insensitive" as I'm assuming that's what you meant to say...
.. However, this will be possible with SQL Server 2005 (Yukon) this will be
supported via T-SQL: "CREATE FULLTEXT CATALOG FT_Catalog_Name WITH
ACCENT_SENSITIVITY = OFF AS DEFAULT"
Either way, I'd highly recommend that you test this functionality this in a
test environment FIRST before implementing it on your production servers.
Regards,
John
"randyr" <randyr@.online.nospam> wrote in message
news:70BBE3FA-EA50-4D76-9E45-F52B3E5812CF@.microsoft.com...
> Hi,
> I have two questions relating to your suggestion of installing sharepoint.
> 1. will this work for both sql fts and indexing server fts
> (I currently have a file system catolog that I have linked into slq
server, I do joined queries that search that catalog and and sql catalog for
the same phrases)[vbcol=seagreen]
> 2. if so, hwo do I rebuild the catalogs to be case insensitive?
> thanks in advance
> --
> randyr
>
> "John Kane" wrote:
this[vbcol=seagreen]
groups,[vbcol=seagreen]
version[vbcol=seagreen]
search[vbcol=seagreen]
as[vbcol=seagreen]
as[vbcol=seagreen]
Is it possible to obtain accent insensitive results from full-text searches
so that a search for caf will return all records for caf and cafe as if
these were the same?
In the four queries below queries 2a and 2b return the same results as each
other whereas queries 1a and 1b return different results from each other.
1a. select * from MyTable where CONTAINS(*,'cafe')
1b. select * from MyTable where CONTAINS(*,'caf')
2a. select * from MyTable where searchtext like '%cafe%'
2b. select * from Mytable where searchtext like '%caf%'
Thanks
Joseph
No,it is not possible.
This issue has already been discussed in detail several times, do a search
in this newsgroup.
Matthias
"Joseph Tervit" <joseph@.company-net.com> wrote in message
news:eaRfNYeMEHA.1340@.TK2MSFTNGP12.phx.gbl...
> I am running SQL 2000 SP3a on Windows 2000.
> Is it possible to obtain accent insensitive results from full-text
searches
> so that a search for caf will return all records for caf and cafe as if
> these were the same?
> In the four queries below queries 2a and 2b return the same results as
each
> other whereas queries 1a and 1b return different results from each other.
> 1a. select * from MyTable where CONTAINS(*,'cafe')
> 1b. select * from MyTable where CONTAINS(*,'caf')
> 2a. select * from MyTable where searchtext like '%cafe%'
> 2b. select * from Mytable where searchtext like '%caf%'
> Thanks
> Joseph
>
|||Joseph,
Matthias, yes this has been discussed many, many (too many) times in this
newsgroup, IMHO. However, with the new Microsoft newsgroup msg roll-off
policy, the past replies might not be searchable (except via Google groups,
of course! ;-). One easy solution is to simply install SharePoint Server
2001 or 2003 as either will upgrade the MSSearch service to the SPPS version
that does support accent insensitive search.
Regards,
John
"Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
news:409883a9$1@.epflnews.epfl.ch...[vbcol=seagreen]
> No,it is not possible.
> This issue has already been discussed in detail several times, do a search
> in this newsgroup.
> Matthias
>
> "Joseph Tervit" <joseph@.company-net.com> wrote in message
> news:eaRfNYeMEHA.1340@.TK2MSFTNGP12.phx.gbl...
> searches
if[vbcol=seagreen]
> each
other.
>
|||Hi,
I have two questions relating to your suggestion of installing sharepoint.
1. will this work for both sql fts and indexing server fts
(I currently have a file system catolog that I have linked into slq server, I do joined queries that search that catalog and and sql catalog for the same phrases)
2. if so, hwo do I rebuild the catalogs to be case insensitive?
thanks in advance
randyr
"John Kane" wrote:
> Joseph,
> Matthias, yes this has been discussed many, many (too many) times in this
> newsgroup, IMHO. However, with the new Microsoft newsgroup msg roll-off
> policy, the past replies might not be searchable (except via Google groups,
> of course! ;-). One easy solution is to simply install SharePoint Server
> 2001 or 2003 as either will upgrade the MSSearch service to the SPPS version
> that does support accent insensitive search.
> Regards,
> John
>
>
> "Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
> news:409883a9$1@.epflnews.epfl.ch...
> if
> other.
>
>
|||Randyr,
Hmm... a blast from the past... a past posting of mine...
1. will this work for both sql fts and indexing server fts
A. No. While the SQL FTS and Indexing Services use the same basic underlying
technology, they are implemented differently and SharePoint was developed
after both of these products were shipped.
2. if so, how do I rebuild the catalogs to be case insensitive?
A. You cannot rebuild a SQL Server 2000 FT Catalog to be "case insensitive"
or "accent insensitive" as I'm assuming that's what you meant to say...
.. However, this will be possible with SQL Server 2005 (Yukon) this will be
supported via T-SQL: "CREATE FULLTEXT CATALOG FT_Catalog_Name WITH
ACCENT_SENSITIVITY = OFF AS DEFAULT"
Either way, I'd highly recommend that you test this functionality this in a
test environment FIRST before implementing it on your production servers.
Regards,
John
"randyr" <randyr@.online.nospam> wrote in message
news:70BBE3FA-EA50-4D76-9E45-F52B3E5812CF@.microsoft.com...
> Hi,
> I have two questions relating to your suggestion of installing sharepoint.
> 1. will this work for both sql fts and indexing server fts
> (I currently have a file system catolog that I have linked into slq
server, I do joined queries that search that catalog and and sql catalog for
the same phrases)[vbcol=seagreen]
> 2. if so, hwo do I rebuild the catalogs to be case insensitive?
> thanks in advance
> --
> randyr
>
> "John Kane" wrote:
this[vbcol=seagreen]
groups,[vbcol=seagreen]
version[vbcol=seagreen]
search[vbcol=seagreen]
as[vbcol=seagreen]
as[vbcol=seagreen]
Thursday, March 8, 2012
About XML upload to database
HI
Can anyone tell me how to upload a xml file in a table.I have uploaed text,csv,xel file but never xml.
just want to know how to upload, and when,why should we use xml file for data
thanks
sandipan
You can use an XML Source to read XML data and import it into a table.
Saturday, February 25, 2012
about text ,ntext,image error message
hello all:
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
I don't know why and how this happend .who can help me ,thx.
Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
I don't know why and how this happend .who can help me ,thx.
Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
about text ,ntext,image error message
hello all:
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
I don't know why and how this happend .who can help me ,thx.Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
I don't know why and how this happend .who can help me ,thx.Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
about text ,ntext,image error message
hello all:
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
I don't know why and how this happend .who can help me ,thx.Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
I don't know why and how this happend .who can help me ,thx.Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
Friday, February 24, 2012
about sorting
Hi all,
i use a full text for my db
however, when i write the SQL w/ order by and w/o order by
it's obviously time gap.
w/o using order by SQL, which return record within 5 seconds
w/ using order by SQL, which return record over 20 seconds
how can i elimiate the searching time by using order by SQL in fulltext
database ?
Thanks in advance!
Q,
Could you post the full output of SELECT @.@.version -- as this will provide
information about your environment. Could you also provide the total number
of rows in your FT-enabled table along with the expected number of rows to
be returned by your contains or freetext query?
Depending upon your answers, you may want to use CONTAINSTABLE or
FREETEXTTABLE and the Top_N_Rank parameter and restrict your results to
2000 rows or less and use an ORDER BY clause based upon RANK.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Q" <q@.q.com> wrote in message news:OZQlJmKmFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> i use a full text for my db
> however, when i write the SQL w/ order by and w/o order by
> it's obviously time gap.
> w/o using order by SQL, which return record within 5 seconds
> w/ using order by SQL, which return record over 20 seconds
> how can i elimiate the searching time by using order by SQL in fulltext
> database ?
> Thanks in advance!
>
|||"Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)"
actually, i have a 4 diff. full-text field, in one table which are (field_1,
field_2, field_3, field_4)
my query like this
select * from tblTest
where contains(field_1, '"my words"')
OR contains(field_2, '"my words"')
OR contains(field_3, '"my words"')
OR contains(field_4, '"my words"')
order by my_year desc, my_date desc
i MUST divided into 4 fields for searching, because sometimes, it only
involced field_1 for searching purpose!
"John Kane" <jt-kane@.comcast.net> glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...
> Q,
> Could you post the full output of SELECT @.@.version -- as this will provide
> information about your environment. Could you also provide the total
> number
> of rows in your FT-enabled table along with the expected number of rows to
> be returned by your contains or freetext query?
> Depending upon your answers, you may want to use CONTAINSTABLE or
> FREETEXTTABLE and the Top_N_Rank parameter and restrict your results to
> 2000 rows or less and use an ORDER BY clause based upon RANK.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Q" <q@.q.com> wrote in message
> news:OZQlJmKmFHA.1412@.TK2MSFTNGP09.phx.gbl...
>
|||Q,
First of all, thank you for providing the requested info!
You're using SQL Server 2000 SP3 on Win2K SP4 and therefore using the
infosoft.dll as the OS-supplied wordbreaker.
What is causing the slow performance is not the ORDER BY clause, but the
multiple CONTAINS clauses as each CONTAINS clause is a full "round trip",
i.e., each CONTAINS reads all of the entries in your FT Catalog, so the
below query is reading the FT Catalog four times! Unfortunately, this is "by
design" for SQL Server 2000. As a workaround, you may want to try using one
FREETEXT query and using the "*" (asterisk) for all FT-enabled columns, this
should perform better, but may give you more results than CONTAINS as
FREETEXT can search "across columns", and is less precise in its results
than CONTAINS.
See KB article 294809 (Q294809) "FIX: Full-Text Search Queries with CONTAINS
Clause Search Across Columns" at
http://support.microsoft.com/default...b;en-us;294809 for more info
as this behavior is also the default behavior for SQL Server 2000. You
should also consider using CONTAINSTABLE or FREETEXTTABLE with Top_N_Rank as
this parameter restricts the full "round trip" to just the Top N (some
specified number) by RANK.
FYI, SQL Server 2005 (codename Yukon) supports a new syntax of multiple
column syntax, for example:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
the above query is functionally equivalent to the following query:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
CONTAINS(FTSCol3, '"john" AND "kane"')
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Q" <q@.q.com> wrote in message news:#B73EXNmFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)"
> actually, i have a 4 diff. full-text field, in one table which are
(field_1,
> field_2, field_3, field_4)
> my query like this
> select * from tblTest
> where contains(field_1, '"my words"')
> OR contains(field_2, '"my words"')
> OR contains(field_3, '"my words"')
> OR contains(field_4, '"my words"')
> order by my_year desc, my_date desc
> i MUST divided into 4 fields for searching, because sometimes, it only
> involced field_1 for searching purpose!
>
> "John Kane" <jt-kane@.comcast.net>
glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...[vbcol=seagreen]
provide[vbcol=seagreen]
to
>
|||Thanks John Kane alot!
"John Kane" <jt-kane@.comcast.net> glsD:OFskTDQmFHA.2860@.TK2MSFTNGP15.phx.g bl...
> Q,
> First of all, thank you for providing the requested info!
> You're using SQL Server 2000 SP3 on Win2K SP4 and therefore using the
> infosoft.dll as the OS-supplied wordbreaker.
> What is causing the slow performance is not the ORDER BY clause, but the
> multiple CONTAINS clauses as each CONTAINS clause is a full "round trip",
> i.e., each CONTAINS reads all of the entries in your FT Catalog, so the
> below query is reading the FT Catalog four times! Unfortunately, this is
> "by
> design" for SQL Server 2000. As a workaround, you may want to try using
> one
> FREETEXT query and using the "*" (asterisk) for all FT-enabled columns,
> this
> should perform better, but may give you more results than CONTAINS as
> FREETEXT can search "across columns", and is less precise in its results
> than CONTAINS.
> See KB article 294809 (Q294809) "FIX: Full-Text Search Queries with
> CONTAINS
> Clause Search Across Columns" at
> http://support.microsoft.com/default...b;en-us;294809 for more
> info
> as this behavior is also the default behavior for SQL Server 2000. You
> should also consider using CONTAINSTABLE or FREETEXTTABLE with Top_N_Rank
> as
> this parameter restricts the full "round trip" to just the Top N (some
> specified number) by RANK.
> FYI, SQL Server 2005 (codename Yukon) supports a new syntax of multiple
> column syntax, for example:
> SELECT FTSPkey, FTSCol2, FTSCol3
> FROM FTS_Table
> WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
> the above query is functionally equivalent to the following query:
> SELECT FTSPkey, FTSCol2, FTSCol3
> FROM FTS_Table
> WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
> CONTAINS(FTSCol3, '"john" AND "kane"')
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Q" <q@.q.com> wrote in message
> news:#B73EXNmFHA.3316@.TK2MSFTNGP14.phx.gbl...
> (field_1,
> glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...
> provide
> to
>
i use a full text for my db
however, when i write the SQL w/ order by and w/o order by
it's obviously time gap.
w/o using order by SQL, which return record within 5 seconds
w/ using order by SQL, which return record over 20 seconds
how can i elimiate the searching time by using order by SQL in fulltext
database ?
Thanks in advance!
Q,
Could you post the full output of SELECT @.@.version -- as this will provide
information about your environment. Could you also provide the total number
of rows in your FT-enabled table along with the expected number of rows to
be returned by your contains or freetext query?
Depending upon your answers, you may want to use CONTAINSTABLE or
FREETEXTTABLE and the Top_N_Rank parameter and restrict your results to
2000 rows or less and use an ORDER BY clause based upon RANK.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Q" <q@.q.com> wrote in message news:OZQlJmKmFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> i use a full text for my db
> however, when i write the SQL w/ order by and w/o order by
> it's obviously time gap.
> w/o using order by SQL, which return record within 5 seconds
> w/ using order by SQL, which return record over 20 seconds
> how can i elimiate the searching time by using order by SQL in fulltext
> database ?
> Thanks in advance!
>
|||"Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)"
actually, i have a 4 diff. full-text field, in one table which are (field_1,
field_2, field_3, field_4)
my query like this
select * from tblTest
where contains(field_1, '"my words"')
OR contains(field_2, '"my words"')
OR contains(field_3, '"my words"')
OR contains(field_4, '"my words"')
order by my_year desc, my_date desc
i MUST divided into 4 fields for searching, because sometimes, it only
involced field_1 for searching purpose!
"John Kane" <jt-kane@.comcast.net> glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...
> Q,
> Could you post the full output of SELECT @.@.version -- as this will provide
> information about your environment. Could you also provide the total
> number
> of rows in your FT-enabled table along with the expected number of rows to
> be returned by your contains or freetext query?
> Depending upon your answers, you may want to use CONTAINSTABLE or
> FREETEXTTABLE and the Top_N_Rank parameter and restrict your results to
> 2000 rows or less and use an ORDER BY clause based upon RANK.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Q" <q@.q.com> wrote in message
> news:OZQlJmKmFHA.1412@.TK2MSFTNGP09.phx.gbl...
>
|||Q,
First of all, thank you for providing the requested info!
You're using SQL Server 2000 SP3 on Win2K SP4 and therefore using the
infosoft.dll as the OS-supplied wordbreaker.
What is causing the slow performance is not the ORDER BY clause, but the
multiple CONTAINS clauses as each CONTAINS clause is a full "round trip",
i.e., each CONTAINS reads all of the entries in your FT Catalog, so the
below query is reading the FT Catalog four times! Unfortunately, this is "by
design" for SQL Server 2000. As a workaround, you may want to try using one
FREETEXT query and using the "*" (asterisk) for all FT-enabled columns, this
should perform better, but may give you more results than CONTAINS as
FREETEXT can search "across columns", and is less precise in its results
than CONTAINS.
See KB article 294809 (Q294809) "FIX: Full-Text Search Queries with CONTAINS
Clause Search Across Columns" at
http://support.microsoft.com/default...b;en-us;294809 for more info
as this behavior is also the default behavior for SQL Server 2000. You
should also consider using CONTAINSTABLE or FREETEXTTABLE with Top_N_Rank as
this parameter restricts the full "round trip" to just the Top N (some
specified number) by RANK.
FYI, SQL Server 2005 (codename Yukon) supports a new syntax of multiple
column syntax, for example:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
the above query is functionally equivalent to the following query:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
CONTAINS(FTSCol3, '"john" AND "kane"')
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Q" <q@.q.com> wrote in message news:#B73EXNmFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)"
> actually, i have a 4 diff. full-text field, in one table which are
(field_1,
> field_2, field_3, field_4)
> my query like this
> select * from tblTest
> where contains(field_1, '"my words"')
> OR contains(field_2, '"my words"')
> OR contains(field_3, '"my words"')
> OR contains(field_4, '"my words"')
> order by my_year desc, my_date desc
> i MUST divided into 4 fields for searching, because sometimes, it only
> involced field_1 for searching purpose!
>
> "John Kane" <jt-kane@.comcast.net>
glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...[vbcol=seagreen]
provide[vbcol=seagreen]
to
>
|||Thanks John Kane alot!
"John Kane" <jt-kane@.comcast.net> glsD:OFskTDQmFHA.2860@.TK2MSFTNGP15.phx.g bl...
> Q,
> First of all, thank you for providing the requested info!
> You're using SQL Server 2000 SP3 on Win2K SP4 and therefore using the
> infosoft.dll as the OS-supplied wordbreaker.
> What is causing the slow performance is not the ORDER BY clause, but the
> multiple CONTAINS clauses as each CONTAINS clause is a full "round trip",
> i.e., each CONTAINS reads all of the entries in your FT Catalog, so the
> below query is reading the FT Catalog four times! Unfortunately, this is
> "by
> design" for SQL Server 2000. As a workaround, you may want to try using
> one
> FREETEXT query and using the "*" (asterisk) for all FT-enabled columns,
> this
> should perform better, but may give you more results than CONTAINS as
> FREETEXT can search "across columns", and is less precise in its results
> than CONTAINS.
> See KB article 294809 (Q294809) "FIX: Full-Text Search Queries with
> CONTAINS
> Clause Search Across Columns" at
> http://support.microsoft.com/default...b;en-us;294809 for more
> info
> as this behavior is also the default behavior for SQL Server 2000. You
> should also consider using CONTAINSTABLE or FREETEXTTABLE with Top_N_Rank
> as
> this parameter restricts the full "round trip" to just the Top N (some
> specified number) by RANK.
> FYI, SQL Server 2005 (codename Yukon) supports a new syntax of multiple
> column syntax, for example:
> SELECT FTSPkey, FTSCol2, FTSCol3
> FROM FTS_Table
> WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
> the above query is functionally equivalent to the following query:
> SELECT FTSPkey, FTSCol2, FTSCol3
> FROM FTS_Table
> WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
> CONTAINS(FTSCol3, '"john" AND "kane"')
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Q" <q@.q.com> wrote in message
> news:#B73EXNmFHA.3316@.TK2MSFTNGP14.phx.gbl...
> (field_1,
> glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...
> provide
> to
>
Sunday, February 19, 2012
about processXMLdata sample,how to insert a return in end of each row data of the result text fi
I am newbie, having a job to handle some XML files into sql server 2005. In the procXMLdata sample,,pls tell me how to insert a return in end of each row data of the result text file?
I am not familiar with this sample, but my guess is that you need to use Ragged Right Flat File format and choose {CR}{LF} as the row delimiter.
Give us more of the problem's context, if this is not what you are looking for.
Thanks.
|||hi,
Thanks for your reply.
The processXMLdata sample was put in the sample directory of ms sql server,which just converted a xml file into a txt file ,using two xml task. The first was to preform XPATH method,output a variable, and the second xml task was to do XSLT method ,input the variable above, output a txt.
I know XML little , i guess the problem may be on propery of the second XML task.
Monday, February 13, 2012
about GetODBCFieldInfo (MFC)
I use ODBC to operate SQL Server 2000. I want to know the data type for each
column in a table. But for image data, text , varchar... , all the return
value are negative.
In addition, how can I realize a table structure copy with ODBC ?
Any suggestion?
Thanks.
UHow about using SQLColumns
column in a table. But for image data, text , varchar... , all the return
value are negative.
In addition, how can I realize a table structure copy with ODBC ?
Any suggestion?
Thanks.
UHow about using SQLColumns
about GetODBCFieldInfo (MFC)
I use ODBC to operate SQL Server 2000. I want to know the data type for each
column in a table. But for image data, text , varchar... , all the return
value are negative.
In addition, how can I realize a table structure copy with ODBC ?
Any suggestion?
Thanks.UHow about using SQLColumns
column in a table. But for image data, text , varchar... , all the return
value are negative.
In addition, how can I realize a table structure copy with ODBC ?
Any suggestion?
Thanks.UHow about using SQLColumns
Sunday, February 12, 2012
About extended stored procedures
Hi,
I have such situation:
A function that make some custom translation of numbers (convert numbers to
text). I have this function in DLL. I want to use that function in my
selects like that:
SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
FROM SOME_TABLE
What are my options? Extended stored procedure? Something else? I know that
Extended stored procedures are function in external DLL that meet some
requirements, but can I use Extended stored procedure in such way?
TIA,
Miro.Hi Miro.
You can't call an xp_ directly in a set based call like that.
To do a set based call against an external library, you'd wrap the library
in a udf(), but calls to the xp_ will be serialized so performance might not
be too thrilling, depending on the number of rows in the select & the amount
of work / efficiency of the xp_.
If the custom logic isn't too complex, you might consider consider
converting to a t-sql udf() as this would give you the best performance, if
that's important to you..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||There are only extended stored procedures and you would need to use EXEC to
call them. This sort of syntax is currently not supported for extended
stored procedures. Even if you call extended stored procedures from
functions, you have many restrictions there.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||For now you should use a UDF. When YUKON comes out, you can make your UDF in
ainy dot net language... today your UDF must be written in T-SQL...
Another thing you might consider is writing a com object then using
sp_oacreate in a function to invoke it, but performance ( I suspect) would
not be as good as if you had written the UDF directly ONLY using T-SQL
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||On Tue, 13 Jan 2004 21:55:34 +1100
"Greg Linwood" <g_linwoodQhotmail.com> wrote:
> Hi Miro.
> You can't call an xp_ directly in a set based call like that.
> To do a set based call against an external library, you'd wrap the library
> in a udf(), but calls to the xp_ will be serialized so performance might not
> be too thrilling, depending on the number of rows in the select & the amount
> of work / efficiency of the xp_.
> If the custom logic isn't too complex, you might consider consider
> converting to a t-sql udf() as this would give you the best performance, if
> that's important to you..
The performance is not important in my case because that function will be used in SELECTs with small number of rows in result set (in most cases with only one row in result set). And the logic is complex enough to have that logic in two sources - one in SQL and one in my source. So I will made it with xp_.
Thanks again for your help,
Miro.|||On Tue, 13 Jan 2004 06:24:04 -0500
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote:
> For now you should use a UDF. When YUKON comes out, you can make your UDF in
> ainy dot net language...
:) Ooo god - no! The 'dot net' is the BIG mistake in world of programming for last 20 years ;).
> today your UDF must be written in T-SQL...
> Another thing you might consider is writing a com object then using
> sp_oacreate in a function to invoke it, but performance ( I suspect) would
> not be as good as if you had written the UDF directly ONLY using T-SQL
I prefer to do it in xp_ and make wrapper for it in UDF (see my other post in that thread). Logic inside is too complex to have two sources to maintain - one in T_SQL and one in my source (Delphi source actually).
10x,
Miro.
I have such situation:
A function that make some custom translation of numbers (convert numbers to
text). I have this function in DLL. I want to use that function in my
selects like that:
SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
FROM SOME_TABLE
What are my options? Extended stored procedure? Something else? I know that
Extended stored procedures are function in external DLL that meet some
requirements, but can I use Extended stored procedure in such way?
TIA,
Miro.Hi Miro.
You can't call an xp_ directly in a set based call like that.
To do a set based call against an external library, you'd wrap the library
in a udf(), but calls to the xp_ will be serialized so performance might not
be too thrilling, depending on the number of rows in the select & the amount
of work / efficiency of the xp_.
If the custom logic isn't too complex, you might consider consider
converting to a t-sql udf() as this would give you the best performance, if
that's important to you..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||There are only extended stored procedures and you would need to use EXEC to
call them. This sort of syntax is currently not supported for extended
stored procedures. Even if you call extended stored procedures from
functions, you have many restrictions there.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||For now you should use a UDF. When YUKON comes out, you can make your UDF in
ainy dot net language... today your UDF must be written in T-SQL...
Another thing you might consider is writing a com object then using
sp_oacreate in a function to invoke it, but performance ( I suspect) would
not be as good as if you had written the UDF directly ONLY using T-SQL
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||On Tue, 13 Jan 2004 21:55:34 +1100
"Greg Linwood" <g_linwoodQhotmail.com> wrote:
> Hi Miro.
> You can't call an xp_ directly in a set based call like that.
> To do a set based call against an external library, you'd wrap the library
> in a udf(), but calls to the xp_ will be serialized so performance might not
> be too thrilling, depending on the number of rows in the select & the amount
> of work / efficiency of the xp_.
> If the custom logic isn't too complex, you might consider consider
> converting to a t-sql udf() as this would give you the best performance, if
> that's important to you..
The performance is not important in my case because that function will be used in SELECTs with small number of rows in result set (in most cases with only one row in result set). And the logic is complex enough to have that logic in two sources - one in SQL and one in my source. So I will made it with xp_.
Thanks again for your help,
Miro.|||On Tue, 13 Jan 2004 06:24:04 -0500
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote:
> For now you should use a UDF. When YUKON comes out, you can make your UDF in
> ainy dot net language...
:) Ooo god - no! The 'dot net' is the BIG mistake in world of programming for last 20 years ;).
> today your UDF must be written in T-SQL...
> Another thing you might consider is writing a com object then using
> sp_oacreate in a function to invoke it, but performance ( I suspect) would
> not be as good as if you had written the UDF directly ONLY using T-SQL
I prefer to do it in xp_ and make wrapper for it in UDF (see my other post in that thread). Logic inside is too complex to have two sources to maintain - one in T_SQL and one in my source (Delphi source actually).
10x,
Miro.
Subscribe to:
Posts (Atom)