i'm struggling to write an sql select statement that will return records on this criteria:
any time a certain word (not part of a word) in a certain cell, i.e. if i'm looking for 'and' i dont want to get 'the sand was hot' but i do want to get 'cats and dogs'. i cant presume that there will always be a space on iether side of the word because of punctuation marks and times when the word is at the begining of the cell 'and then we...'
Hi,Just found out that this error can be solvedServer: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2A clause of the query contained only ignored words.triggered when executingSELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adidWHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULLWHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))can be solved by changing the language specific file with noise words ...but that's not really an option in our server configuration.Now I wondered is there a way to query via TSQL a list of the noisewords ...so I can exclude them before parsing the query??I also dedected I can not catch the error in my stored procedure with theinstruction IF @@error ... So is it true that I can only handle this errorin my client software that calls the stored procedure??Thanks a lot.--Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
Hope you can ignore your personal music tastes with this post!I have a table of Artists with a Full Text Index on a few columns on that table. My full text querying against this table works really well apart from it seems for one band - "Take That"! I'm guessing this is because these words consitute noise words? I parse user's search terms and add an "AND" between each word. So for example my query is essentially:select * from containstable(Artists, *, '"take AND that"') As I say, this works fine for Pink Floyd etc, but not for these guys! So either SQL Server has a preference on boy bands or I am thinking it is because these words are deemed to be noise. Anybody got any tips on how I could tackle this? I suppose it is quite possible that there might be another band with the same problem.I do have exclusive access to the SQL box, so perhaps I could edit the noise words file......Thanks
heywhen i try to search an "noise word" it drop me an ERRORwhat i have done : - stop full text service- remove the words i want ,from noise.enu , noise.eng , noise.dat (@@language = u.s...) the files is in system32 and in program files...config .- start full text service-rebuild and after that re populate the catalog and it still drop me an ERROR !!! how can i resolve this prob ??? tnx tnx .
Hello, I use the full-text search utility in SQL Server 2005 to find word in PDFs document. This is my 'Documents' table:
id (PK), data (VarBinary(max)), extension (nvarchar(4))
My full-text catalog on 'data' column works fine because when I search 'Microsoft', my document containing this word is returned as result.
SELECT * FROM Documents WHERE freetext([data], 'Microsoft'); 1 , 0x255044...., .pdf
But I need to know how many times 'Microsoft' word appears in this document. Do you have any idea how can I retrieve this information? Thanks in advance!
We are using fulltextsearch, with the FREETEXT command. This works very well for us, but we would like to highlight all forms of matched words in the text. This is used in a website, and now the highlighting of the found words is limited to the exact matches of the searched words. So it often happens that in a text there is no exact match of the searched words, but another form of that word.
Does anyone know if and how this problem can be fixed ?
Hello, I use the full-text search utility in SQL Server 2005 to find word in PDFs document. This is my 'Documents' table: id (PK), data (VarBinary(max)), extension (nvarchar(4)) My full-text catalog on 'data' column works fine because when I search 'Microsoft', my document containing this word is returned as result.
SELECT * FROM Documents WHERE freetext([data], 'Microsoft'); 1 | 0x255044.... | .pdf
But I need to know how many times 'Microsoft' word appears in this document. Do you have any idea how can I retrieve this information? Thanks in advance!
To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'transform noise words', 1 GO RECONFIGURE GO But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)
i am in the process of creating some new reports on a new Reporting Services site
reports developed in VisualStudio2005 pointing an an AnalysisServices Cube on a (development) SQL2005 database
when i Preview the reports in Visual Studio 2005 on my development box, the reports take about 12-15 seconds to run (about 4-5 seconds before the "Report is being generated" logo appears, then another 8-10 seconds before the report appears (report is a series (1-8) of charts))
when i run the same report (with the same report criteria) via the Report Manager (pointing at the same AnalysisServices Cube), it now takes about 45 seconds to run (about 15 -16 seconds before the "Report is being generated" logo appears, then another 30 seconds before the report appears)
(n.b. this is not due to initial Report Manager start-up delays, the timings are for the second and third run of the report)
can anyone out there point me in the direction of where to look ??
Hello,I need to do a seach in multiple columns for a certain word. With SQLI have to use one specific column right?I.E. select * from DB where Column1 like '%search%'That works.But what if I want to seach multiple columns in the table for thesearch word?You can't do this:select * from DB where Column1, Column2, Column3 like '%search%'Is there a way to do this?Thanks,Tmuld.
Hello, I perform queries on tables with 500,000 plus records, when I use the like clause or the = for record names with large numbers I get different counts with each run. As an example if I was searching retail stores and k-mart was one I get counts like the following 1st run: k-mart 2200 2nd run: k-mart 2240 3rd run: k-mart 2197 etc. etc. The records may be k-mart inc or kmart inc or k-mart etc. If anyone has a better understanding of how the items are searched for and knows a better way please respond. Thank you in advance.
i am using web parts with drag and drop feature , i did not use aspnetdb for user membership while it is published it gives eror as follows An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error: The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist. If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database. Sql Server Express must be installed on the machine. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts. Please give me ur valuable advice , thanks in advance
Hi! Mgmt just decided that the designer (me) was more valuable than the developer (now me). I hope there are some gurus out there who can help.
What would be the best way to duplicate certain parts of my RS? Suppose I have a table with three columns: ID, adName, and priority. When I serve the ads, I am randomizing them, but would like to serve some more frequently than others. I am hoping there is a single query I can use, that will seed the RS with multiple instances of high-priority records.
I am still using SQLv 6.5 and have a need to do monthly reports. It is my understanding that there is no inherent capability in SQL v6.5 for functions such as MONTH getdate() as exist in SQL 2000. Does anyone know of a script that will simulate this so I can extract information from a database such as date_last_update IN MONTH 02?
I'm very familiar with Oracle at a conceptual level and need tounderstand MSSQL as well. Can anyone point me to a document/web pagethat describes the different files used in MSSQL and a discussion of howthey relate? (ex. with Oracle, there is tempdb, redo, archive, db files,rollback, etc.)
I have 5 records in a table called "testing" as such:
LetterCdcontact_id x1 x2 x3 x4 x5
How can I split this dataset so that three records have LetterCd = 'y' and the other 2 records have LetterCd = 'x'? The number of records can vary, but the idea is the same: split the values in the LetterCd column evenly (50% = 'x', 50% = 'y'). LetterCd is varchar(25) and contact_id is int. I can't update the data, it would have to be a SELECT statement with some sort of CASE statement to get the remainder of the record count?
The issue is that I need to delete parts of this string using SQL Server.This string is a title of a document which should contain only the Document Category & Title. Instead, it is displaying extra parts such as Year, Date Created, etc. I would like to truncate/parse off the title. The database is Acme_system. The name of the table is dbo.documents, the column name for the title is "title" and there is also column is "childcat".
The title string reads:
2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714
I want to take off everything except for "DAILY NOTES AND TRACKING FORM-11-16-13 700"
I was wondering if this is possible I have to be able to just select the number before the '-' in acolumn which is formatted like 123-456 so I just need the the 123 part.
The issue is that I need to delete parts of this string using SQL Server.
This string is a title of a document which should contain only the Document Category & Title. Instead, it is displaying extra parts such as Year, Date Created, etc. I would like to truncate/parse off the title. The database is Acme_system. The name of the table is dbo.documents, the column name for the title is "title" and there is also column is "childcat".
The title string reads:
2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714
I want to take off everything except for "DAILY NOTES AND TRACKING FORM-11-16-13 700"
I was wondering if this is possible I have to be able to just select the number before the '-' in acolumn which is formatted like 123-456 so I just need the the 123 part.
I'm having troubles connecting the webparts on SharePoint 2007. I installed the RSWebParts package from SQL Server 2005 Reporting Services on my MOSS 2007 box. The installation went smoothly and I can add both Report Explorer and Report Viewer on a page. But when I try to connect RE to RV I get the following error:
An unexpected error has occurred.
Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.
So I have to go to the maintenance page and remove the web parts.
Have anyone managed to bypass this and make them work together?
I prepared a report including sale information of a company. There are different offices of the company in different locations. And in each offices there are more than 1 saleman. I have to send the report (but just the associated part of the report to the saleman, not the whole one) to each saleman and his/her manager. I prepared a subscriber data (name and mail addy) for each office. How can i send the associated part of the report to the salemen and his/her manager by using just the original report (I dont wanna seperate the report depending on the saleman and send each report one by one, cuz there are many offices and salemen)
This is the thread http://msdn2.microsoft.com/en-us/library/ms998292.aspx
Here it mentions the following Deployment Considerations When using Windows authentication to connect to SQL Server in production environments, consider the following:
Use a custom service account. Create a SQL Server login for a Windows group. Assign database permissions to a database role. Use a Custom Service Account
This is my problem we are trying to convert all our web pages to use Windows Authenication. DOMAINWEBGROUP (for example).
Tracey and Jack part of this group But sometimes there may be anothers added to this say John
The security comes from SQL right now i give data reader to the database.
Example 1. ASP.NET uses the service account DOMAINWEBGROUP 2. Assign WEBGROUP to database PERSONEL Table Employees Salary Web Page Lets say Tracey logs into the web page.....and i have two pages 1. Employess 2. Salary
As the WEBGROUP is data reader viola Tracey sees all salary information. Im trying to figure out that only Tracey can see Employees
But im not seeing the relationship when using WEBGROUP
Is there a way in SQL to say yes we have the DOMAINWEBGROUP but in sql also put in Tracey and know that Tracey is part of DOMAINWEBGROUP So that in my tables i can only give tracey part of this employee table (say the first name last name) and not salary information. (I know how to use the roles).
The part im confused about is the mapping WEBGROUP to tracey in sql as the web developers only pass in WEBGROUP
I want to add some information to a report that I do NOT want to be included in the printed output. Is there any way to designate a rectangle or list or table to NOT print?
I'm having problems with handling a very large amount of user records - about 100.000 - 150.000 records. Instead of selecting all of them at a time, how do I f.ex. select 1000 of them? (f.ex. get nr. 1 - nr 1000, then get nr. 1001 - nr. 2000) ???
I have a field that contains text strings that always follow this syntax "ABC:DE:comment follow". I want to return a new field with only whatever is between the two : (in this case DE but it could be more). It would be perfect if all the field follow that syntax, however, I have variations where users put spaces either before or after the :. Can the spaces be ignored?
I've tried variations on a SELECT statement like below but have been unable to find a way to count only those types per fileNo that have all partNo completed (and to count all types per fileNo with a partNo of 0 and a completed date as they have no parts):
SELECT [FileNo], COUNT(DISTINCT [Type]) AS CountOfAPs FROM APs WHERE (completed IS NOT NULL) GROUP BY [File]
Hi all, New to replication I need suggestions on how go about keeping 2 sql servers from different parts of the world to be in sync all times, some latency may be accepted.
Here is the senario, we have website hosted in NA and customers from all parts of the world log in to oursystem and does what ever they have to do which will update the sql server databases. as our company is expanding we want to have a same website hosted in eu, so when ever a person logs from eu he will be hitting the webservers in eu and anything he do will go to the SQL database servers that are in eu. Now the problem i have is when one site goes down say NA site goes down, we need to route all the traffic to site in EU that means sql servers in EU. This brings the challenge of both the servers need to be in sync all times.
I did some research on merge replication, this might not work because in this concept we have to make 1 server as the master and the others as slaves. Also i need to think in a way that if they want to expand more that means to asia, i will be having another server, these all need to be in Sync.
We are using SQL Server 2005 standard edition. I was reading that this can be achived easily with peer-to-peer replication,but is only available in Enterprise edition which is 4times the cost of standard edition.My company is not going to agree for this, so got to do the research and propose a solution
