I tried creating one simple bar chart report in SSRS, with background fill with pattern "UpwardDiagonal", the stripes on the column when viewed insider report viewer control is quite prominently visible, whereas when exporting the same report to PDF the lines are very thin and dense and hence makes it very difficult to differentiate between one column with pattern inside and one without. I have even tried various other pattern like "LightUpwardDiagonal" and "WideUpwardDiagonal" but all have same result when exported to PDF.why?
I don't know whether this is the right place to ask this question. I didn't get the answer from sql forums may be someone here can help. I need to use freetext on two columnssomething likefreetext(column1+column2,@Search) this will give error "Incorrect syntax near '+'."Any One know the correct syntax. Thanks
I have a vb page, which is a simple front end so I can edit text from anywhere.
The person who is going to use it knows no html, and so I am trying to get freetextbox to work.
I am getting the following error: Server Error in '/' Application. --------------------------------------------------------------------------------
ExecuteNonQuery: CommandText property has not been initialized 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.
Exception Details: System.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized
Source Error:
Line 81: Line 82: mySelectCmd.Connection.Open() Line 83: mySelectCmd.ExecuteNonQuery() Line 84: MyConnString.Close() Line 85:
The pages code is below:
<%@ Page Language="VB" Debug="True" validateRequest="False"%> <%@ Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <script runat="server"> Dim MyConnString As SqlConnection Dim mySelectCmd As SqlCommand Dim mySelectQuery As String dim myExecuteQuery As String
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then ReadMyData() end if end sub
Public Sub ReadMyData() MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
Dim dt As DataTable Dim dr As DataRow dt = New DataTable
mySelectQuery = "select * from content_text" mySelectCmd = New SqlCommand( mySelectQuery, MyConnString) MyConnString.Open() Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader() Try While myReader.Read()
End While Finally myReader.Close() MyConnString.Close() End Try
Pubs.DataSource = DT Pubs.Databind()
End Sub
Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs) Pubs.EditItemIndex = -1 ReadMyData() End Sub
Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs) Pubs.EditItemIndex = CInt(e.Item.ItemIndex) ReadMyData() End Sub
Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
myExecuteQuery= "Update content_text Set title=@title, text=@text, section_no=@section_no where id=@id" mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
dim e_id as integer dim e_title, e_text, e_section_no as string
Hello all. I'm new to this forum and of course I found it because I have a problem I can't find the answer to.
I have full-text indexing on a couple of tables in my database. Periodically, maybe once every couple of weeks, I get an error on a webpage that runs a stored procedure searching these tables. The error is as follows:
"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Products' because it is not full-text indexed."
I'm running Classic ASP, and the stored procedures run thousands of times a day. As soon as I get the error email from the website containing the url that created the error, I immediately click on it, but by that time, the error has stopped and everything is running properly.
The timing of the error does not coorespond to any other processes. My transactionals run on the hour and the index is re-built at 12:45am, yet I just received the error again at 12:45pm (no, I checked the clocks and they are right).
I do not have replication running and I don't know where to look next.
I tried the following select: select * from patientprofile where contains (BillingNotes, '572576-02')
And got the errormessage: Server: Msg 7601, Level 16, State 2, Line 1 Cannot use a CONTAINS or FREETEXT predicate on table 'patientprofile' because it is not full-text indexed.
It shows BillingNotes as a text column
Is there a differnet way I can search this note field for records that contain that text value (572576-02)
Is there a way I could achieve something like this. freetext(FirstName+LastName,@Name) FirstName and LastName are two columns in my database table. What I am trying here is allowing the user to type their first name , last name or both. and write a search which can find there record for all the three cases. Please Advice.
I'm attempting to query the MS Index Server. I allow the users to supply the search text and when doing that it is best to pass the text into the query via a parameter to protect against SQL Injection attacks. However, for some reason I cannot get the FREETEXT predicate to work with a parameter. The FREETEXT predicate works fine when the query is concatenated together. I'd like an example of the FREETEXT predicate working with MS Index Server (Windows 2003). The following line works just fine: cmd.CommandText = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, '" + searchText + "') order by rank desc " I'd like the following lines to work:cmd.Parameters.AddWithValue("@SearchText", searchText) cmd.CommandText = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, @SearchText) order by rank desc "
So now I'm wondering if the MS Index Server doesn't support the use of a parameter in this way or if I somehow managed to get the syntax wrong. Thoughts?
Hi, I realised that I am not able to do a FREETEXT search on multiple table, example:SELECT * FROM [tStaffDir], [tStaffDir_ClientExp], [tStaffDir_CoreSpecs], [tStaffDir_GlobalExp], [tStaffDir_Lang], [tStaffDir_PrevEmp], [tStaffDir_TerEdu] WHERE FREETEXT(*, @Name) ORDER BY [Name] Can I use FREETEXTTABLE instead? How do I go about doing it?
I installed SQL server 7.0 beta 3 and I have been experimenting with the full text search capabilities. The catalogs appear to build correctly, but when I issue a query against the full text database, isql/w always returns with `contains` is not a recognized function name. I am confident of my query syntax, since I can execute an identical query against a database without full text search enabled and I get an error stating that the target database does not have full text searching enabled. Has anybody else encountered this? I am hoping that I am missing something obvious/simple.
Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns. Is there a way to do this? My two colums are:title and description
I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.
select
b.rank,
a.ProductID,
a.ProductName,
a.Sequence,
a.ProductImage,
a.ItemID,
a.ItemName,
a.ManufacturerItemCode,
a.ItemImage,
a.ItemSourceID,
a.PackageID,
a.BrandID,
a.BrandName,
a.ManufacturerID,
a.ManufacturerName,
a.ProductCategoryID,
a.CategoryID,
a.CategoryName,
d.CustomerGroupName,
isnull(h.PackageDescription,a.ItemPKG) as PKG,
case g.StockStatus
when 1 then 'Yes'
when 0 then 'No'
else ''
end as StockStatus,
isnull(g.StandardUnitPrice,a.ListPrice) as Price,
isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource
from
dbo.vw_mcProductItem a
inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @SearchWord) as b ON a.ItemID = b.[KEY]
inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID
inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID
inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID
inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID
left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID
left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID
left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId
left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID
where
d.CustomerGroupID = @CustomerGroupID
and f.UserID = @UserID
and FREETEXT(BrandName,ManufacturerName,CategoryName, @SearchWord)
Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
WHERE FREETEXT(*, 'foo')
GROUP BY Username
ORDER BY [Count] DESC
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct
ON OptionID = ct.[KEY]
GROUP BY Username
ORDER BY [Count] DESC
They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.
But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.
If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).
So... I am worrying about this unnecessarily? Should I just use the simpler first version?
Hi everyone. I'm developing a web search engine using asp and SQL Server 2000. I need to return records that matches with a string entered by users. In example, suppose my database to have this structure and it's filled like this:
ID NAME KEYWORDS --- ------- ----------- AA025 NAME1 attached, atic, common, business, hotels AA026 NAME2 headache, medicin, aspirins, heat, health AA027 NAME3 at, services, music, electronics
suppose that user enters 'at'. By now, i'm using this pattern '%<input_text>%'. So in this example, pattern would be '%at%'. As i remarked in the fields above, the three fields matches, and that's not what i'm looking for. I want that the result of that query be just the last field, with 'AA027' ID. Thanks in advance guys. Cheers.
hi can any one help me to get the pattern of 70-441 exam. i need information total number of question in the exam.. and will there be any simulation as 70-431 exam.
Say I have an object that can be created by some users and not others. And, I have a method something like - function createObject(userId as Integer, objectName as String). The method calls a stored procedure using the two input arguments as parameters.Now, what I am wondering is this; I am obviously going to check in my SP to ensure that the user has the rights to create the new object before inserting the new record, and there will be an output parameter to send the new record ID back to the method, but do Ia) have a separate output parameter to report on the success of the creation (e.g. @Err Int OUTPUT)b) use the existing output (used to send the new record ID back) and send an error code (e.g. if newRecordId = -9999)c) somehow try to cause a failure of the SP that can be caught in a try/catch blockThanks in advance,Martin
I've been trying to find and filter out data that can cause FOR XML to fail with the error:FOR XML could not serialize the data for node ?? because it contains a character (0x0006) which is not allowed in XML.I thought it would be simple, just identify the rows that don't match the XML spec URL...
However, the following doesn't work. select fieldname from tablename where fieldname like N'%[^'+nchar(0x9)+nchar(0xA)+nchar(0xD)+nchar(0x20)+N'-'+nchar(0xD7FF)+N']%'
Not only does this fail to find the offending rows, when I checked by inverted it by taking out the "^" it still returns no rows.When I reduced 0xD7FF down to 0xFF it returns data, but I cannot add the other range back in without all rows not matching.I've experimented with values and found 0x02E9 was the highest I could go without all values vanishing. Even then, numbers lower than that caused a large variation in the number of rows returned.My Field is NVarchar(100) in SQL_Latin1_General_CP1_CI_AS.
We have a collection of charts, some of which we use the gray scale for the palette (users only have B&W printers). We need to use the same patterns in other charts for consistency. The only option I see in Edit Chart Value-->Appearance-->Series Style-->Fill is Gradient. Is there any way to pick a fill pattern?
The pattern is the diagonal lines slanting down from left to right. It is the third column in the gray scale, so I have tried to add a dummy column as the first column, so that my column would have this pattern. However, the chart allows for space width wise for the first column, so there is a blank space on the chart. I have the Data Output checked to No.
SELECT summaryFROM summariesWHERE CONTAINS(summary, <any of the values in phrasesToSearchFor>)
I've tried every weird permutation of the query I can think of, but can't find a way to make it work. Can anybody help?
Bonus Question:
I've got a function dbo.getPhraseToSearchFor(userId) that returns a phrase depending on the value of userId. I want to get a result table of (userId, summary) where for every user, I use dbo.getPhraseToSearchFor(userId) to figure out what to search the full-text catalog for. The docs say you can use a scalar function anywhere a scalar value is expected, but apparently that doesn't apply in this case -- you can't do CONTAINS(summary, dbo.getPhraseToSearchFor(23))
I need a simple tSQL code that will produce an array sequence values that has gap with 2 or more consecutive values, which can vary - so 2 or 3 or 4 between sequence of number. For example, I want it to return a series with a gap or space of 2- So it should return 1, 2, 5, 6, 9, 10.. or a gap of 3 - 1,2 ,6,7,11,12.. etc I know a single gap is quite simple using a recursive cte, but I haven't been able to find how to get this kind of result.
hi i have a table with a column which contains a large paragraph of data. i need to search for a pattern within this text and update it.
for example the column contains 4096 char. within this text, i search for a pattern"qwerty" and convert it to "asdfgh". any ideas how this can be done? i'm using sql server 2000 i have to write something like UPDATE [DB].[dbo].[Table] SET [Column1] = '.............', WHERE .......... any help will be appreciated.
DelimitedSplit8k and PatternSplitLoop seem to have potential, but I'm just plain stuck on some things:
1. DelimitedSplit8k: the delimiter split the folder paths, but the pattern can be within the strings that result. 2. PatternSplitLoop: I would have to cross apply 16 times and have an awful WHERE clause to determine which of the four strings matched first.
Unless I'm missing something. Short example is below.
WITH testctes (string, pattern) AS ( SELECT 'oh_look_at_this.thing.hishers_stuffmine.craftyours_protein', 'his first' UNION ALL SELECT 'i.am.a._thing.hershis_thingsmine.refrigeratoryours_potato', 'hers first' UNION ALL SELECT 'path_like.things_minehers.some_elsehis_garbageyours_sneakers', 'mine first' UNION ALL SELECT 'more_stuff.yoursminehershis_falafel', 'yours first' ) SELECT string, pattern, ca.item, ca.itemnumber FROM testctes CROSS APPLY [dbo].[PatternSplitLoop] (string, '%his%') ca
I've decalred this stored procedure with a simple where and like statement. The problem is I don't see any result. Here is my code
create procedure sp_Select_ProfileNames @NameSearch varchar(50) as select ProfileFirstName +' '+ ProfileLastName as ProfileName from Profiles where ProfileLastName like '%@NameSearch%';
When i change this line: where ProfileLastName like '%@NameSearch%'; to where ProfileLastName like '%Bil%';
I see names starting with Bil, but when i enter Bil as param, I don't get anything.
I am trying to do a select statement with SQL 2000 for all recordscontaining any number of lowercase letters. I have tried the following2 statements and they both seem to match both lowercase and uppercaseletters.SELECT * FROM customers WHERE name LIKE '%[a-z]%'SELECT * FROM customers WHERE name LIKE'%[abcdefghijklmnopqrstuvwxyz]%'Can anybody help?
I need to store a list of parameters in a database. Each parameter has a name, description, comment and a value. Easy so far.
However the values are of different types. Each individual parameter has a value which may be of type int, decimal, string, boolean, custom type etc.
Which table design pattern is most appropriate? We have a heated in-house discussion and I need supporting arguments.
Options explored so far:
1) (De-)serializing the value to a string-type. 2) Adding a column for each type, using only one column at a time. 3) Adding extra value-tables, one table for each type. The disadvantages for each option are obvious and the basis for our discussion.
Your help in this matter will be appreciated. Regards, Tonn
I want to check for multiple patterns in a particular column. For one pattern I can write e.g. SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.
My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery e.g. (SELECT '%'+name+'%' FROM <TableName>)
I need a table value function for extracting strings matching a particular pattern from a long string.
e.g. I have a table called cs_Posts, it has a column called FormattedBody, this value can be something like:
Code Snippet<p>this is the 1st photo <a href="http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPGhttp://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG">http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG</A< A>>" border="0" alt="" /></a></p> <p>this is the 2nd photo<a href="http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPGhttp://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG">http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG</A< A>>" border="0" alt="" /></a></p>
When this long string is input, the table value function should 2 rows: http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG
I have a table called MessageBoard. It has a column called Messages. A user can type text including any html tags through a text area ans when he saves it by clicking a button, the content typed by the user is saved in the MessageBoard Table (in the Messages) column. So once saved, the html tags are kept intact. If I have to find and replace certain html tags, what kind of SQL Query I have to write? For example I want to find all the <pre> </pre> tags and replace it with <p> </p> tags. How do I do this?
