Basic Full Text search question

Hi

I am just starting to do a full text search on a sql 2000 database. My question is:

If I want to query a table Employee for 1. a particular word in a very large varchar field named "resume" 2 - a boolean field named "willtransfer" and 3. a field that holds the index of another table named "Cities" Cities just holds an index and cityname field of 15 characters.

do I create a fulltext index for all three fields or only the "resume" field since it is the only field that is being searched? What would a query for these 3 fields look like if I am searching for. I have seen no examples that combine multiple fields.

"speaks French" in resume, "true" for "willtransfer " field, and "Miami" in the cities table (there would need to be an inner join on the cityid field in both Employee and City table.

Thanks for any help on this. This is not the tables I am using, but this seemed like an easy example to explain.

smhaig

SELECT e.resume, e.willtransfer, c.cityname FROM Employee e
JOIN City c ON e.cityid = c.cityid
WHERE e.resume LIKE '%FRENCH%'
AND e.willtransfer = True
AND c.CityName = 'MIAMI'

I think maybe you might be looking for something like:

DECLARE @.mystring varchar(50)
SET @.mystring = 'french transfer=true Miami'

SELECT e.resume, e.willtransfer, c.cityname FROM Employee e
JOIN City c ON e.cityid = c.cityid
WHERE @.MyString LIKE '%FRENCH%'
AND @.MyString LIKE '%transfer=True%'
AND @.MyString@. = 'Miami'
AND e.resume LIKE '%FRENCH%'
AND e. willtransfer = 'True'
AND c.cityname = 'Miami'

I'm not sure if you want to concatenate all three fields or not.

Please see above

Adamus

|||

You only put a full text index on the 1 field you filter on the other columns using a standard where clause

i.e.

select employeeId

from employee

join city on employee.cityId = cities.cityId

where willtransfer = 1

and cityname = 'Miami'

and contains (employee.resume,'French')

This would match any resume that contains French you might want to include more complex search like "French NEAR speaks"

Basic data type question

What type of field do I create for OLE objects? (text,
ntext...) What field size?
thksJanice,
text,ntext and image sare the BLOB data types in SQL Server.ntext can
support unicode data and thats the only difference between ntext and text
while image datatype is used to store binary data .text and image support
upto 2GB and there isnt any need to specify the field size while ntext, in
terms of storage size, is two times the number of characters entered.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Janice Gilbert" <jgilburt@.yahoo.com> wrote in message
news:080501c33f26$d85d0db0$a001280a@.phx.gbl...
> What type of field do I create for OLE objects? (text,
> ntext...) What field size?
> thks

Barcode not printing

I have barcode printing formula in my report. When the report runs,it
does not print the barcode , just prints the barcode text with some additional characters in the begining and end of the text.Is there any file missing in cryatal to print that barcode ? If yes then whats the name of this file ?Are you using formula to print barcode inside report file?? :confused:
If you want to use 1-D barcode, why don't you use barcode font.sql

Bad data in source file kills package?

I'm reading data from sets of CSV source files with " text qualifiers. Most of the data is good, but I do run into some bad data that doesn't seem to get handled by redirecting rows. The type of data that is killing me is this:

"Business ID","Business Name","Business Phone"
"12345",""A" SPOT, THE ","555-555-5555"

Access sees this as an unparsable record and continues with the rest of the data. SSIS seems to choke on it. I've set the package to allow 1000 errors with no change.

I did set up an error file from my source file, with error output set to redirect row on error and on truncation. The error output seems only to show columns for "Flat File Source Error Output Column", "ErrorCode", and "ErrorColumn" too. (I'm sure there's a good reason, but I've found it really frustrating that it doesn't use the source file column mappings.)

What I'd like to do is redirect (or fix) the bad rows and continue with the rest of the load.

The Execution Results tab shows the following errors.

[Flat File Source [2082]] Error: The column delimiter for column "Business Phone" was not found.
[Flat File Source [2082]] Error: An error occurred while processing file "\\Share\Files\flat_file.csv" on data row 464.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (2082) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

Any help would be much appreciated!

two years past...I encounter the same problem.

Just output the source file to other format: tab as delimiter, execl..... to skip the problem.

|||Hi lund,

This is because you were using " as text qualifier and there was a data that had value just the same as the text qualifier. The solution is you should replace the " of your data value with another character like '. Or just use another text qualifier.

Best regards,

Hery|||

The other two posters are correct. If you have any control over the source file consider changing the delimeter (to tabs) and/or the text qualifier (to something that doesn't appear in your text.)

Failing that, I have had some success in the past in pre-processing files. Basically you can load the file line by line, treating each line as a single record, and do some processing on the line to clean it.

Then you output the clean lines back into a new file.

Processing could be simple (eg count the number of commas in a row, or verify that if you split a row you get a set number of columns, and they are the correct width.)

Preferred approach is to fix the source data to export to something other than comma separated, though. There is a good reason that .tab files were invented!

Dylan.

Backup/Restore Stored Procedure

Hi there

I just want to create Stored Procedure which will do following thing

i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)

ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)

I mean to say, the file should be act as the full backup of database, in plain text.

The same need to be done in the reverse order,this SP will do following things

i)Open the file created by first SP, create new database, with same name as backup in SP one

ii) create the tables, by reading the Schema script.

iii) Insert all the data in corrsponding tables.

For EX:

--creating backup

sp_CreateDatbaseBackup('DatabaseName')

--Restoring

sp_RestoreDatabaseBackup('FileName')

I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)

I want an editable full Backup and Restore.

Please help me in creating these SPs or any other idea, solution ?

OR

any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.

Regards,

Thanks.

Gurpreet S. Gill

Hi,

I had done something familiar to what you are looking for but i only backed up data. I used bcp to copy data to a file and read data from the file. I am not sure as to how u can back up the schema. If you need more details on the bcp code i can email it to you. Please give me ur email address.

Sapna

|||Sorry, actually the file created by bcp is not editable.|||

Hi Sapna

Please send me the SP or the related to it, hope it will help me. my mail ID are

gsgill76@.yahoo.com , gsgill76@.gmail.com

Thanks,

Regards.

Gurpreet S. Gill

|||I am able to solve the problem i.e the script of all the tables other like constaints, Indexs etc.

this is done with the help of Scptxfr.exe(i copied it from V2000), its working very fine in V2005 too.

now, regarding the other problem, scripting the data into, i use BCP & loop through all the table & merge all the files generated into one.
now the problem are
1) i am not able to distinguish between the data for table, i mean the data to which that table belongs to.
2)how can i restore that file?

Regards,
Thanks.
Gurpreet S. Gill|||Can you create individual files for each table using bcp or do you have to create one file. Also you can name the file name same as the table name. This will help you distinguish the tables. While reading the data back u need to specify the file name in the bcp command to open the particular file.

For my application: We used the c sharp code to zip all the individual (tables) files to one file and then encrypt it.

Backup/Restore Stored Procedure

Hi there

I just want to create Stored Procedure which will do following thing

i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)

ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)

I mean to say, the file should be act as the full backup of database, in plain text.

The same need to be done in the reverse order,this SP will do following things

i)Open the file created by first SP, create new database, with same name as backup in SP one

ii) create the tables, by reading the Schema script.

iii) Insert all the data in corrsponding tables.

For EX:

--creating backup

sp_CreateDatbaseBackup('DatabaseName')

--Restoring

sp_RestoreDatabaseBackup('FileName')

I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)

I want an editable full Backup and Restore.

Please help me in creating these SPs or any other idea, solution ?

OR

any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.

Regards,

Thanks.

Gurpreet S. Gill

Hi,

I had done something familiar to what you are looking for but i only backed up data. I used bcp to copy data to a file and read data from the file. I am not sure as to how u can back up the schema. If you need more details on the bcp code i can email it to you. Please give me ur email address.

Sapna

|||Sorry, actually the file created by bcp is not editable.|||

Hi Sapna

Please send me the SP or the related to it, hope it will help me. my mail ID are

gsgill76@.yahoo.com , gsgill76@.gmail.com

Thanks,

Regards.

Gurpreet S. Gill

|||I am able to solve the problem i.e the script of all the tables other like constaints, Indexs etc.

this is done with the help of Scptxfr.exe(i copied it from V2000), its working very fine in V2005 too.

now, regarding the other problem, scripting the data into, i use BCP & loop through all the table & merge all the files generated into one.
now the problem are
1) i am not able to distinguish between the data for table, i mean the data to which that table belongs to.
2)how can i restore that file?

Regards,
Thanks.
Gurpreet S. Gill|||Can you create individual files for each table using bcp or do you have to create one file. Also you can name the file name same as the table name. This will help you distinguish the tables. While reading the data back u need to specify the file name in the bcp command to open the particular file.

For my application: We used the c sharp code to zip all the individual (tables) files to one file and then encrypt it.

Backup/Restore History Question

This is a multi-part message in MIME format.
--=_NextPart_000_0009_01C4E35E.2D639B30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi all,
Occasionally, when I perform a RESTORE on a database,
The msdb tables are not updated properly.
The RESTORE functions normally, however when I run a query against the
BackupSet, BackupMediaFamily and RestoreHistory tables, there appear to = be orphaned records in the RestoreHistory table. (eg. The = RestoreHistory has a record of the restore as well as a BackupSetID = value, however these values cannot be found in the BackupSet and = BackupMediaFamily tables.
This is a recurring problem, however it does not happen all of the time.
Do you have any ideas?
TIA
Rick Sawtell
MCT, MCSD, MCDBA
--=_NextPart_000_0009_01C4E35E.2D639B30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi all,

Occasionally, when I perform a RESTORE = on a database,
The msdb tables are not updated = properly.

The RESTORE functions normally, however = when I run a query against the
BackupSet, BackupMediaFamily and = RestoreHistory tables, there appear to be orphaned records in the RestoreHistory = table. (eg. The RestoreHistory has a record of the restore as well as a = BackupSetID value, however these values cannot be found in the BackupSet and BackupMediaFamily tables.


This is a recurring problem, however it = does not happen all of the time.

Do you have any ideas?

TIA


Rick Sawtell
MCT, MCSD, MCDBA


--=_NextPart_000_0009_01C4E35E.2D639B30--This is a multi-part message in MIME format.
--=_NextPart_000_0048_01C4E370.FDF3EB80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Too many drinks I think Rick<g>. Seriously though I have not noticed =this before. Is there any chance there is a job that tries to clean out =the history without using sp_deletebackuphistory?
-- Andrew J. Kelly SQL MVP

"Rick Sawtell" <quickening@.msn.com> wrote in message =news:e7q53A54EHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi all,
Occasionally, when I perform a RESTORE on a database,
The msdb tables are not updated properly.
The RESTORE functions normally, however when I run a query against the
BackupSet, BackupMediaFamily and RestoreHistory tables, there appear =to be orphaned records in the RestoreHistory table. (eg. The =RestoreHistory has a record of the restore as well as a BackupSetID =value, however these values cannot be found in the BackupSet and =BackupMediaFamily tables.
This is a recurring problem, however it does not happen all of the =time.
Do you have any ideas?
TIA
Rick Sawtell
MCT, MCSD, MCDBA
--=_NextPart_000_0048_01C4E370.FDF3EB80
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Too many drinks I think Rick. =Seriously though I have not noticed this before. Is there any chance there is a job =that tries to clean out the history without using sp_deletebackuphistory?
-- Andrew J. Kelly SQL MVP
"Rick Sawtell" wrote in =message news:e7q53A54EHA.1260=@.TK2MSFTNGP12.phx.gbl...
Hi all,

Occasionally, when I perform a =RESTORE on a database,
The msdb tables are not updated =properly.

The RESTORE functions normally, =however when I run a query against the
BackupSet, BackupMediaFamily and =RestoreHistory tables, there appear to be orphaned records in the RestoreHistory =table. (eg. The RestoreHistory has a record of the restore as well as a =BackupSetID value, however these values cannot be found in the BackupSet and BackupMediaFamily tables.


This is a recurring problem, however =it does not happen all of the time.

Do you have any ideas?

TIA


Rick Sawtell
MCT, MCSD, MCDBA

---

--=_NextPart_000_0048_01C4E370.FDF3EB80--|||This is a multi-part message in MIME format.
--=_NextPart_000_0034_01C4E36D.A1FD50D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
There is that possibility,
I'm still researching it however. This only happens occasionally, so =it is a bit baffling.
I've got 50 or so servers in the hosted environment and only a few of =them are experiencing this problem.
All the servers are the same H/W and S/W, service packs etc.
Win 2k Server, SP4, SQL 2k SP3a etc.
I'll keep you posted, meanwhile if anyone else can think of =anything...
I've got jobs that run after a restore takes place which ensures that =everything in these logs match up (just in case we run into problems =like this. ;-))
Rick
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message =news:uT3Lwr54EHA.4008@.TK2MSFTNGP15.phx.gbl...
Too many drinks I think Rick<g>. Seriously though I have not noticed =this before. Is there any chance there is a job that tries to clean out =the history without using sp_deletebackuphistory?
-- Andrew J. Kelly SQL MVP

"Rick Sawtell" <quickening@.msn.com> wrote in message =news:e7q53A54EHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi all,
Occasionally, when I perform a RESTORE on a database,
The msdb tables are not updated properly.
The RESTORE functions normally, however when I run a query against =the
BackupSet, BackupMediaFamily and RestoreHistory tables, there appear =to be orphaned records in the RestoreHistory table. (eg. The =RestoreHistory has a record of the restore as well as a BackupSetID =value, however these values cannot be found in the BackupSet and =BackupMediaFamily tables.
This is a recurring problem, however it does not happen all of the =time.
Do you have any ideas?
TIA
Rick Sawtell
MCT, MCSD, MCDBA
--=_NextPart_000_0034_01C4E36D.A1FD50D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

There is that possibility, =
I'm still researching it =however. This only happens occasionally, so it is a bit baffling.
I've got 50 or so servers in the hosted =environment and only a few of them are experiencing this problem.
All the servers are the same H/W and =S/W, service packs etc.
Win 2k Server, SP4, SQL 2k =SP3a etc.
I'll keep you posted, meanwhile =if anyone else can think of anything...
I've got jobs that run after a restore =takes place which ensures that everything in these logs match up (just in case we =run into problems like this. ;-))
Rick
"Andrew J. Kelly" wrote in message news:uT3Lwr54EHA.4008=@.TK2MSFTNGP15.phx.gbl...
Too many drinks I think Rick. =Seriously though I have not noticed this before. Is there any chance there =is a job that tries to clean out the history without using sp_deletebackuphistory?
-- Andrew J. Kelly SQL MVP
"Rick Sawtell" wrote =in message news:e7q53A54EHA.1260=@.TK2MSFTNGP12.phx.gbl...
Hi all,

Occasionally, when I perform a =RESTORE on a database,
The msdb tables are not updated =properly.

The RESTORE functions normally, =however when I run a query against the
BackupSet, BackupMediaFamily and =RestoreHistory tables, there appear to be orphaned records in the RestoreHistory table. (eg. The RestoreHistory has a record of the restore as =well as a BackupSetID value, however these values cannot be found in the =BackupSet and BackupMediaFamily tables.


This is a recurring problem, =however it does not happen all of the time.

Do you have any ideas?

TIA


Rick Sawtell
MCT, MCSD, MCDBA

---

--=_NextPart_000_0034_01C4E36D.A1FD50D0--