I remembered that the Thesaurus that comes with SQL Server is essentially empty.Is there a Thesaurus that can be downloaded for US English so I won't have to sit there with Roget's doing data entry for a few weeks?
View 9 RepliesI have setup a custom Thesaurus for use with SQL Full Text Index (2008). For example I have the following terms :
<expansion>
<sub>400v</sub>
<sub>400volt</sub>
<sub>400 volt</sub>
<sub>415v</sub>
<sub>415volt</sub>
<sub>415 volt</sub>
<sub>3 phase</sub>
<sub>3phase</sub>
<sub>three phase</sub>
</expansion>
When I issue a search for "415 volt" I am getting matches on records with "3" in the data, not just "3 phase" - example query below :
select tblp.productid, tblp.productcode, tblp.description, KEY_TBL1.RANK
from tblProduct tblp
INNER JOIN FREETEXTTABLE(tblProduct, description, '"415 volt"') AS KEY_TBL1 ON tblP.ProductID = KEY_TBL1.[KEY]
ORDER BY KEY_TBL1.RANK DESC
It is returning items that don't have the word "phase" in at all and just have "3"! I have had to take all numbers out of the stop list as we sell a lot of technical items that will be searched for by size / voltage etc.
Has anyone created a Thesaurus file that is using it? Specifically, has anyone set up a First Name thesaurus file and is it possible to get a hold of it. Why reinvent the wheel if someone has already done this. I am creating a database that needs to return records for fuzzy matches on first names. I don't know what all forms of the names can be such as Robert, Bob, Bobby, Bert or Sue, Suzie, Susie, Susan, Suzanne, etc. and I am betting someone has created something already that can be a starting point at least.
What about the Thesaurus Word currently uses? Does anyone know what the physical file name and location is of the thesaurus file Word uses?
Any help or direction is appreciated.
Thanks
Paul
Hi! I am moving this from the Transact SQL forum as I am not getting a reply. Hope it's OK!
Hello: I have successfully enabled SQL Server 2005 Full Text (MSFTESQL) on my database, created the FT Catalog in Storage, and defined a FT Index for a table ( 1 table for testing).
I have also created an expansion and replacement entry in the Thesaurus (tsENU.xml) and removed the comments. I have restarted the server and the FTS Service, In addition, (per your comments to another user) I have used sqlcmd to confirm my default language of 1033.
I am unable to get any results from the Thesaurus file. I am able to run queries against the index using CONTAINS and FREETEXT. I get results - however I only get the same thing I would get from the CONTAINS portion of the query.
Also ..SQLSERVER2995MSFTEUser$.. has permission on the FTData folder.
// This query //
SELECT name, description FROM table
WHERE CONTAINS(description, ' FORMSOF(THESAURUS, blender) ')
// on this Thesaurus located in this C: folder MSSQL.1MSSQLFTData sENU.xml //
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<case caseflag="false"></case>
<expansion>
<sub>blender</sub>
<sub>chopper</sub>
<sub>grinder</sub>
<sub>food processor</sub>
</expansion>
</thesaurus>
</XML>
// returns this //
A search for blender returns all strings with the exact word blender in them - not even any plural forms. A search for blend or grind returns nothing. FREETEXT does a little better returning blender, blenders, blending. Still no grind,chop, or food process.
Appreciate your help, Thank you.
How can I download a file that is previously uploaded to database?
Of course its kind is Byte.
Hello people,
Do you know how can do for downloading a file stored in a database?. I'm using a table with a FILE field for storing the file.
I know i have to create a special aspx page for downloading, that receives parameters to locate the proper record in the table and then retrieve the file in memory to start downloading.
I have done this with file located at specific folders but not a database's field.
Another thing... the file may be big.
Dou you have any idea about retrieving from sql and sending the file back to the final user?
I really appreciate your support.
Larry.
have a URL that I get from a partner by calling another URL and feeding it some query parameters. That call returns to me a URL which is what I need to then go get the file I'm downloading. I have no problem getting that URL into a variable in my SSIS package. URL....there are several key-value pairs after that question mark which denote the login information, the report ID that it's picking up, etc.
If I manually take that URL and dump it into IE and hit enter, I get the standard "Open/Save/Cancel" prompt. If I open it, I see the XML that should be in my file in the browser. If I save it, I get the standard save dialoge box so that I can save the xml file.How do I do this in SSIS? I can create a file connection for where I want this XML file to be saved, but I don't see an option in the XML task to simply save the file from a URL.
Hello, I'm having users upload documents to my db and storing them as an Image datatype, I can do that without an issue. I'm also able to find that record and return it as a byte(). Now, what's the best practice/scheme to return it to the browser, even FF, and have it prompt the user with the Open or Download thing Dialogue we all know and love.
Thanks in advance.
I would like to design a content-management feature on a website, in particularbe able to upload a binary file and to store it into a MS Sql Server DB.be able to download it afterwards. The sizes of the files are very limited (less than 100Kb) so I do not need any special feature to handle the upload/download.Does anyone know how to achieve such feature?Thanks in advance,Joannes
View 5 Replies View RelatedHi EveryOne
I would like to download the SQL server databse to excel file using ASP.Net
I need to download the one table to excel file. If anyone knows please help me.
With Regards
Nibu Abraham
Hi, I have tried to implement file download option. I can download file which is stored in any folder. Code is... string filepath = Request.Params["file"].ToString(); string filename = Path.GetFileName(filepath); Response.Clear(); Response.ContentType = "image/gif"; Response.AddHeader("Content-Disposition", "attachment; filename=" + filename); Response.Flush(); Response.WriteFile(filepath);This code is working fine. But now I am facing a problem. Files (not the path) are stored in database table. User can download file from the database. How can I do this? The file may be a .txt, .doc, .xls, .jpg or .gif.
View 13 Replies View RelatedHi,
how do i upload and download images and files from database row?is there anyway i can upload images so that uploaded images ares saved in a listbox and at the same time in the IMAGES folderin the solution explorer as well so that i can later select an image fromlistbox and download it when needed? i m using c#,vwd2005 express,sql express.
thanks.
jack.
I'm having trouble importing a text file that was ftped down from an AS400 fixed length flat file. The text file appears to be formated correctly when I open it (1 record per line fixed format) but when I try to import it ASCII delimited it doesn't appear to be in that format and I'm not familiar at all with the AS/400 file formatting. This seems to only be a problem when the last field in the file is variable length. If the last field is a char 1 field it imports OK. Any idea on what I'm doing wrong with the import? I'm I going about this all wrong? Is there an easier way to get data into SQL Server from this AS400 flat file?
Any help would be greatly appreciated!!
Thanks,
Deidre
Hi,
I have written a stored procedure to upload a file to a table.
I am quering this table and want to download the result to a file.Can someone please help me to do this ?
Thanks,
Naguveeru
i want to download incremental file from a folder. already existing file information stored in database. we are using SQL server 2008R2 standard edition.
View 3 Replies View RelatedHi all,
I am extracting SQL server 2005 trial edition to my flash memory hard drive.
Hence suppose I should download one of the following three files:
"Self-extracting executables:
X86 Executable
X64 Executable
IA64 Executable "
But which one should I choose? based on what criteria ?
(This point seems clear to everyone but myself, even after I went through all the docs on the download site)
Thanks,
Jack (an absolute beginner)
I have a requirement where I need to pass some parameters using URL and that URL generate CSV file which I need to save to a shared drive.Here is sample URL....where practice, start and end are parameters.I need to automate the process and trigger ULR passing those parameters every month so that CVS file is saved automatically to a shared driver. How can I create SSIS package to that?
View 9 Replies View RelatedI am using Asp.net 2.0 with C# and sql server 2000. I need to download the file which is stored in sql server database table as image datatype. So I need to download from cs page. Pls reply,Arun.
View 2 Replies View RelatedI hope someone can help me here. Here is some background.
As I was attempting to download SQL Express, the download dialog box timed out. When I x-ed out of the download, Windows XP Professional 2002 threw up an error message indicating that the dialog box was non-responsive, and I elected to close it.
I downloaded SQL again successfully, and installed with no errors. I then attempted to create a new db through Visual Web Developer. Instead of creating a database, Visual Studio returned an error saying that I did not allow for new database objects, and that I should change my permission settings. I could not figure out how to do this, so I uninstalled SQL Express.
After re-starting, I got the following message: €œYour SQL Server Installation is either corrupt or has been tampered with (could not open SQLEVN70.RLL). Please uninstall then re-run setup to correct this option.€?
I uninstalled MS SQL again from the Add/Delete Programs on my Control Panel. On re-start, I received the same error again. On my Programs list, MS SQL is not present, there are several MS SQL files in the path C:Program FilesMicrosoft SQL Server.
So I located the Visual Studio uninstall tool and attempted to run it. I received the same error: €œYour SQL Server Installation is either corrupt or has been tampered with (could not open SQLEVN70.RLL). Please uninstall then re-run setup to correct this option.€?
Any ideas on how to uninstall the files in my program files so I can load a clean copy of SQL?
I have an SSIS package that inserts website URLs from a SQLServer table into a variable used by an HTTP Connection Manager, then downloads the data files from those URLs using a ForEach Loop and a Script Task. Works beautifully when a data file is found at the URL, but hangs if no data file is found. I've set the Timeout property on the HTTP Connection Manager to 30 seconds, but doesn't work. how to first check if a data file exists, of if the request returns nothing, or how to trap this situation in a try-catch?
Here is the VB code I'm using in the Script Task:
Public Sub Main()
Try
' Connect to website using HTTP connection manager
Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
' Create a new HTTP client connection
Dim connection As New HttpClientConnection(nativeObject)
[code].....
Get a filestream download link with only access to read and with folder navigation
I need a link with the path to get the file stream blob, that path could be used to download a document using any windows app like windows explorer, etc. the requirement is that path does not allow customer to navigate in filesstream share folders or see other files and only can read the file of the path,
Checking :
[file_stream].GetFileNamespacePath(2)
Allow you navigate in folders.
NON_TRANSACTED_ACCESS read_only, resolve the requirement to disable the save in file table, but allow you navigate and see other files.
Dear All,
I am not able to let thesaurus in SQL Server 2005 works.
My full indexed table has 4 records as follows:
tony
roby
mary
antony
The following is my tsENU.xml file:
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<expansion>
<sub>Tony</sub>
<sub>Roby</sub>
<sub>Mary</sub>
</expansion>
</thesaurus>
</XML>
After I execute the following query:
SELECT * FROM Person WHERE CONTAINS(*,'FORMSOF(Thesaurus,"Tony")')
the result is only the record containing 'Tony' but I need Tony, Roby and Mary to be treated as synonimus and for that reason the result should be the 3 records containing all the 3 names.
Which is my mistake?
thanks and regards
Buzz
Hello: Please let me know if another forum is a better place to post! I have successfully enabled SQL Server 2005 Full Text (MSFTESQL) on my database, created the FT Catalog in Storage, and defined a FT Index for a table ( 1 table for testing).
I have also created an expansion and replacement entry in the Thesaurus (tsENU.xml) and removed the comments. I have restarted the server and the FTS Service, In addition, (per your comments to another user) I have used sqlcmd to confirm my default language of 1033.
I am unable to get any results from the Thesaurus file. I am able to run queries against the index using CONTAINS and FREETEXT. I get results - however I only get the same thing I would get from the CONTAINS portion of the query.
Also ..SQLSERVER2995MSFTEUser$.. has permission on the FTData folder.
// This query //
SELECT name, description FROM table
WHERE CONTAINS(description, ' FORMSOF(THESAURUS, blender) ')
// on this Thesaurus located in this C: folder MSSQL.1MSSQLFTData sENU.xml //
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<case caseflag="false"></case>
<expansion>
<sub>blender</sub>
<sub>chopper</sub>
<sub>grinder</sub>
<sub>food processor</sub>
</expansion>
</thesaurus>
</XML>
// returns this //
A search for blender returns all strings with the exact word blender in them - not even any plural forms. A search for blend or grind returns nothing. FREETEXT does a little better returning blender, blenders, blending. Still no grind,chop, or food process.
Appreciate your help, Thank you.
I am trying to get the full-text search thesaurus to work in Sql
Server 2005 Express edition. I live in the USA so I assume the
tx.ENU.xml is the appropriate file to modify. I used Notepad to
modify the tx.ENU.xml file as follows:<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
<sub>littre</sub>
<sub>leydig</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub>Windows 2000</sub>
</replacement>
<expansion>
<sub>run</sub>
<sub>jog</sub>
</expansion>
</thesaurus>
</XML> I closed Management Studio and reopened to allow MSFTESQL service to restart. Then I ran these queries:SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS(SectionText, 'littre') *** returned 3 rows ***SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS(SectionText, 'leydig') *** returned 169 rows ***SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS(SectionText, ' FORMSOF(THESAURUS, littre) ') *** returned 6 rows ***
Thus the Thesaurus does not seem to be working since it should
have returned at least 169 rows. I rebooted my entire system to
make sure Sql Server is starting fresh. Any help in sorting this out will be greatly appreciated.
Does anyone know where I can get a Thesaurus (or list) of People's Names and their relationships?
For Example:
Bill = Billy = Billie = Will = Willie = William
Sue = Susie = Susan = Suzanne
I can build the needed xml file to become a Thesaurus for MSSQL Full Text Searching. But I can't find a list of related people names anywhere.
Any help would be great! Thanks!
Lyam
Hi,
I€™ve been trying to add keywords to the thesaurus used for SQL Server 2005 Full Text services but do not seems to get any improved results.
For example, I have a catalogue of article titles which contains titles such as €˜CRM€™ and then some others use the words €˜Customer Relationship Management€™, the ideal solution I would have thought was SQL Servers thesaurus support, I€™ve been though and added the values:
<expansion>
<sub>customer relationship management</sub>
<sub>crm</sub>
</expansion>
to the txGlobal.xml file in the SQL Servers FTData directory, I then amended by test query to use this clause:
FORMSOF(THESAURUS,"CRM")
The idea being that when I search for CRM it will bring back articles with Customer Relationship Management also. However I€™m just getting results for the same results as for the word 'CRM' and no expansion is taking place it would seem.
Would anyone be able to clarify what the best approach to making this work would be:
Do I need to restart the SQL Server FullText service after making any changes to the XML files?
(Done this, no sucess)
Do I need to restart the SQL Server service after making any changes to the XML files?
(Done this, no sucess)
Do you need to re-populate the full text catalogues after making a change the XML files?
(Done this, no sucess)
If this syntax correct when trying to utilise the thesaurus?
CONTAINSTABLE (Article_Metadata, *, €˜FORMSOF(THESAURUS,"CRM")€™ )
(It doesn;t give any errors, just no improved results over:
CONTAINSTABLE (Article_Metadata, *, €˜"CRM"€™ )
Any help would be appreciated.
Thanks
Ed
How to get the initial size of the database file using T-SQL.
sys.master_files, sys.database_files, sysfiles, sysaltfiles --> gives only the current size and not the initial size.
I would like to add to it the actual file size in mb or gb of each file to the results.
select sd.name,mf.name as logical_name,mf.physical_name,
case
when dm.mirroring_state is null then 'No'
else 'Yes'
end as Mirrored
from sys.sysdatabases sd JOIN
sys.master_files mf on sd.dbid = mf.database_id
join sys.database_mirroring dm on sd.dbid = dm.database_id
The sp_spaceused procedure does a nice job on it's own giving me what I want (only one db though), plus a bonus allocated space column. How can I combine this sp with my other query, or is there a better way to ad this information?
I have a date file with no delimiter like bellow
0080970393102312072981103378000004329392643958
0080970393102312072981103378000004329392643958
I just know 5 first number in a line is for example "ID of bank"
or 6th and 7th number in a line is for example "ID of employee"
How can I create a XML format file?
I have been trying to query SQL to generate relationship table for a set of files (CAD Files).They are stored in our Data management System I am able to get the following results from SQL.
DECLRE@Files TABLE( LEVEL VARCHAR(50), PARENT Varchar(50), CHILD Varchar(50))
INSERT@Files
SELECT'0' LEVEL, NULL PARENT, 'A' CHILD UNION ALL
SELECT'1' , 'A' , 'B' UNION ALL
SELECT'1' , 'A' , 'C' UNION ALL
SELECT'1' , 'A' , 'D' UNION ALL
SELECT'1' , 'A' , 'E' UNION ALL
SELECT'1' , 'A' , 'H' UNION ALL
[code]....
I have a store procedure who receive an xml file.
In this store procedure I try to put all record in temporary table for some treatement ..
But I have a problem with one field ...
When I execute the code below I receive this error message :
The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.
The problem comes from the field DeleteDate from the second row of the xml file
how I can get the correct conversion for this field ?
declare @tbl table(actorPersonId int, personType int, actorType int, person int
, firstName varchar(100), surname varchar(100), phone varchar(100), email varchar(100)
, publication int, logUsr varchar(20), active bit, delDate datetime )
[Code] .....
I have about 1200 sql files in one of my folders. Almost all of these files do data inserts and updates, so they should be run only once. As and when required I have manually ran around 150 of them already. Whenever I ran any of these scripts, I log that file name into a log table in my sql server including the execution time. Since running 1000+ more files takes a lot of time, I want to automate running of these files through a batch file. But I also want to filter the files that are already run.My file list looks like follows.
InsertToOrderTypes.sql
UpdateClientAddress.sql
DeleteDuplicateOrders.sql
InsertToEmailAddress.sql
ConsolidateBrokerData.sql
UpdateInventory.sql
EliminateInvalidOfficeLocations.sql
My log table in the database looks like this.
select * from sqlfileexecutionlog
FileNameRunTimeResult
---------------------
DeleteDuplicateOrders.sql03/12/2014 14:23:45:091Success
UpdateInventory.sql04/06/2014 08:44:17:176Success
Now I want to create a batch file to run the remaining files from my directory to my sql server. I also want to wrap each of these sql file executions in a transaction and log success/failure along with the runtime and filename into sqlfileexecutionlog table. As I add new sql files into this directory, I should be able to run the same batch file and execute only the sql files that have not bee run.
I created a dummy database (TomsTest) and then I try to restore my other database "DEV" to it.Normally, you have to use the Move for both the mdf and ldf files to tell it to restore to another database.But the message I am getting is: The file 'D:SQLDataTomsTest.mdf' cannot be overwritten. It is being used by database 'TomsTest'.
USE [master]
RESTORE DATABASE [DEV] FROM DISK = N'D:SQLBackupDEV_backup_2014_05_28.bak' WITH FILE = 1,
MOVE N'Dev' TO N'D:SQLDataTomsTest.mdf',
MOVE N'DEV_log' TO N'D:SQLDataTomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Of course it is in use, that's always the case. Even if I put it in single use mode, it still doesn't work.I tried it on another machine and had the same problem. Not sure why. If I call it the same name, it works fine but I don't want to overwrite the original database, I just need to get some data from the backup.