I dare to state that I found a very disappointing bug when using the LIKE operator on NTEXT columns. I actually need to check for equality, but since that doesn't work, I was using LIKE and I noticed I was getting lots of duplicates in my table.
I am using SQL Server 2005 and the issue is very easy to reproduce.
In a table T(string NTEXT) we have 1 row containing 5000 times the character a, like aaaaaaaaa....a - 5000 times. The following query does not return any matches:
SELECT * FROM T WHERE string LIKE 'aaa....a' -- 5000 times
SELECT * FROM T WHERE string LIKE 'aaa...a%' -- 5000 times
doesn't return any values either.
Now comes the fun part: if the literal used with LIKE in the above query is 'aaa...a%' where a occurs 3999 times the query returns the row. If a occurs 4000 times, the query returns no results. It looks like a magic number to me ;-) Does the literal get truncated to a NVARCHAR (whose maximum length is 4000)?
Is there any fix for this problem? I'm thinking of writing a stored procedure to work around the issue...
Another thing that I observed is that there seem to be problems with the comparison when the strings contain special characters (tabs, new lines, etc).
Any ideas would be appreciated, since I really need the comparison in order to avoid having duplicates in my table. I can't keep all the strings in memory and do the check in the application...
Is this a known issue? When I try to filter my report using Like operator on one of the Report parameters/dataset field it returns nothing. If I use = it works? Any ideas how to use the "Like" operator in reporting?
I have a disconnected utility dimension with the following data and a waterfall->subwaterfall hierarchy. I have ignore if same as parent set on sub water fall. The unary operator is assigned to the relevant level
Opening ~ Opening ~ Price ~ Price ~ Total Composition ~ Composition L1 + Total Composition ~ Composition L2 + Total Composition ~ Composition L3 + Total Composition ~ Composition L4 + Total Composition ~ Composition L5 + Closing ~ Closing ~
I'm using MDX to set values to the members
SCOPE(Measures.Waterfall,[Dim Waterfall].[Water Fall].&[Price]); THIS=Measures.LowestPrice; END SCOPE; SCOPE(Measures.Waterfall,[Dim Waterfall].[Sub Water Fall].&[Composition L1]); THIS=Measures.[Composition L1]; END SCOPE;
[Code] ....
But when i run a simple select query
SELECT {[Measures].[Waterfall]} ON COLUMNS, [Dim Waterfall].[Water Fall].[Water Fall] ON ROWS FROM [GPA]
I only get a value for price. Total Composition is NULL. If I change my query to look at the subwaterfall level then I see values for composition L1->L5, so I know there are values there I was expecting the unary operator to aggregate my composition measures into Total Composition.
I should point out that every measure that's being assigned to my utility dim is calculated with in excess of a dozen or so steps and intermediate calcs, is this causing some kind of solve order issue?
I know that as a workaround i could probably do something like
SCOPE(Measures.Waterfall,[Dim Waterfall].[Water Fall].&[Total Composition]); THIS=SUM([Dim Waterfall].[Hierarchy].[Water Fall].&[Total Composition].Children,Measures.Waterfall); END SCOPE;
But that defeats the purpose of the unary operators
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Just a quick question that I really couldn't find using google or anything because it was too bland. How do you return the whole database while still using the LIKE operator. I have a textbox that you can use to search the database but I want all the data to be displayed underneath the box so you can "browse" the data if you don't know the name of what you are looking for. What would I put as the default value? I used just defaultvalue="%" and that worked in 1 area, but not another. Is this the right way? Thanks, Chris
I have a query and for the life of me I cant figure it out. I have a web form with a number of textboxes on it e.g. Title, subtitle, isbn. I also have a search button. This search page allows a user to enter any combination of fields in the form and then when they click the search button, a datagrid is diaplyed showing the results (gets the results from a SQL DB). I have got the code working if the user selects say one item, but I dont know how to allow it so that the user can enter any combination of items in the form and the search button will search for it based on what the user says. I think im gettng confused with my logical operators in the sql statement. If any one can take a look I would be greatful.public void BindData(){String @BookID = TxtBookID.Text;String @Title = TxtTitle.Text;String @Subtitle = TxtSubtitle.Text;String @ISBN = TxtISBN.Text;String @AuthorFName = TxtForename.Text;String @AuthorLName = TxtSurname.Text; SqlDataAdapter ad = new SqlDataAdapter("SELECT Book_ID_Internal, Title, Subtitle, ISBN, Edition, Publication_Date, Author.First_Name, Author.Last_Name FROM Book, Author WHERE Book.Author_ID = Author.Author_ID AND Book.Book_ID_Internal = '" + @BookID + "' AND Book.Title = '" + @Title + "' AND Book.Subtitle = '" + @Subtitle + "' AND Book.ISBN = '" + @ISBN + "' AND Author.First_Name = '" + @AuthorFName + "' AND Author.Last_Name = '" + @AuthorLName + "'", conn);DataSet ds = new DataSet();ad.Fill(ds, "Book");GridView1.DataSource = ds;GridView1.DataBind();} protected void BtnSearch_Click(object sender, ImageClickEventArgs e){BindData();}
I have a select statement that consists on something like this :
SELECT * FROM Products WHERE Ref LIKE '%" & Search & "%'
well this works ....but the problem is that i have some Ref in this format "MC-909" and if the user makes a search with the word "MC909" ...it dosent return any value ...
where nt.Project_type='A' and nt.status='Done' and nt.project_id like '06%'
I also need to add an additional condition : like '07%'. How can I do this, i. e I need the projects where the type = A , status = Done and project_id starting with 06 and 07.
Hi , This is my first message on this forum. Well, I want to write a query which finds the length of the employee names whose name starts with J, A or S?.
This is what I did:
SELECT LENGTH(ENAME) FROM EMP WHERE ENAME LIKE 'A%' OR LIKE 'J%' OR LIKE 'S%'
This query does not work. Can anyone tell me what is wrong with it?
I have a column named tags which contains tagnames seperated by commas now i have to compare this column with a parameter named tagname which contains a single tagname how to do this.Please help me
here is my procedure
ALTER PROCEDURE [dbo].[ProcSoftwaresGetSoftwaresbyTags] ( @FileTag Varchar(64) ) AS BEGIN
SELECT Category.CategoryName, Softwares.SoftwareFileID, Softwares.CategoryID, Softwares.FileName, Softwares.FileImage,Softwares.FilePath, Softwares.FilePublisher, Softwares.Adminrating, Softwares.FileDownloadCount,substring(Softwares.FileDescription,1,200)as FileDescription,convert(Varchar(12),Softwares.FilePostedDate,109)as FilePostedDate,Softwares.FileSize, SystemRequirements.OperatingSystem FROM Category INNER JOIN Softwares ON Category.CategoryID = Softwares.CategoryID INNER JOIN SystemRequirements ON Softwares.SoftwareFileID = SystemRequirements.SoftwareRequirementID where substring(Softwares.FileTag,0,3) LIKE substring(@FileTag,0,3) and FileStatus=1 END
I have two tables athat are structurally identical. I wish to obtain rows that are in the first table that are not in the second one (e.g. the EXCEPT operator). EXCEPT is not supported directly in TSQL...
I'm having problems using the between operator in a query statement.
Select * from <mytable> where date between @date1 and @date2
The date values with a hour specified, aren't returned. What is the approach you would recommend here?
Thx
EDIT: by playing with this problem I've figured out I can append the hour to the date like this: <date> between @fromDate + '00:00:00' and @toDate + '23:59:59'
this seem to work, but I'm not sure if this is correct
Hi, I'm trying to build a form that will allow users to choose their own parameters for the Select statement in the SqlDataSource. These results would then be displayed back to a GridView control. The only problem I am having is figuring out how to allow them to choose the operator (=, <, >, <>, etc) from a dropdown list. Does anyone have any suggestions on how to do this with a SqlDataSource control? It is probably something simple and any help would be much appreciated. Thanks
please do help. Have been wrestling with this for about 3 hours and read a buncha forums + searched the tutorial lists.. AARRGH!
Anyhow,
I have to paginate a datalist (and I really can't use a datagrid because of the layout, blame the bluddy graphic designer)
I want to return the top 8 rows, the next 8 top rows and so on.
This is the sql string I have: 'retrieve pagination in order to construct the rest of the sql string Dim startrec As Integer If pageno = 1 Then startrec = 0 Else startrec = (pageno - 1) * pagesize End If ' this builds the sql string, getting ONLY the records we need for this. Page size is a constant expressed on the base page ' startrec is the record where I want to start in the db. strsql = "select top " & pagesize & " * " & strsqlbasic & " and itemID>" & startrec & " order by itemnotes1 asc" noresults.text = strsql & " <br> " & searchwhat & searchfor
strsqlbasic is constructed elsewhere and is just the 'from X where y = var
Of course, this returns all records where the value of itemID is greater than the value of startrec. How would I change this so it returns the next 4 rows starting from the row number given by the value of startrec?
strsql = " select * from dic where english='" & SearchBox.Text Or TextBox4.Text & "'"The machine said Input string was not in a correct format. How can i select database with or operator in asp.net .Is it possible to select database in sql from more textbox ?
Consider the following SQL query: SELECT ENAME,SAL FROM EMP,ASG,PAY,PROJ WHERE EMP.ENO=ASG.ENO AND PAY.TITLE=EMP.TITLE AND ASG.PNO=PROJ.PNO AND ASG.DUR=48 AND BUDGET>200000 Give the possible operator trees:right-deep,left-deep and bush Tank you very much!
Hoping someone can shed some light on this one, using MSSQL 2000,
I have a query like this;
Code:
Select T.TransactionRecordClass, T.ClassDetail from [remoteserver].otherdb.dbo.Data T where T.TransactionRecordClass like '10000002%'
which will return around 10000 records in under 1 second. My issue is that I need to make this part of a function, but as soon as I add parameters/variables, it goes from returning in under 1 second, to 11 minutes!!!
Code:
DECLARE @TRC_ID nvarchar (9) SET @TRC_ID = rtrim('10000002') + '%'
Select T.TransactionRecordClass, T.ClassDetail from [remoteserver].otherdb.dbo.Data T where T.TransactionRecordClass like @TRC_ID
There is a non-clustered index on the 2 fields being returned , and about 77 million records in the table. I have attempted using Index hints, but found they are not allowed on remote queries..... So... any clues????
I need to have a select statement to take all the products with the LABEL_ID containing 1 AND 2. I can't use the IN operator as it will return me results with LABEL_ID 1 OR 2. Is there any solution to this? Sorry I'm relatively new to mysql and not familiar with the select statements.
I'm currently working on something like a search engine. Hope to have some good advice here :)
Hey guys, I was just thinking, is it possible to send alerts via the Managment/SQL Server Agent/Operators/Alerts to an SMS system?
Is it even technically posibble?
Actually I am trying to develop an alert system that reads the database table periodcally, the data being read is a datetime, once it reaches the time, I need to send an sms to the person.
I have to pass a string list separated by Commas ( For example, 'Item1','Item2') from a C# code to run a Stored procedure which has a Select query which has an IN Operator in it.
While passing though the data are there in the database, Stored procedure doesn't recognise the passed string list as a list instead it recognise as a whole string, so query doesn't return the expected resultset.
Please help me on this how to pass a stringlist to a stored procedure from C#?
SET @FROM = 'FROM Payment PY (NOLOCK) JOIN (SELECT DISTINCT PY.AccountPaymentId, PY.' + @SORT + ', ROW_NUMBER() OVER(ORDER BY PY.' + @SORT +') AS RowNum FROM Payment PY (NOLOCK)) AS SQ '
SET @ON = 'ON (SQ.AccountPaymentID = PY.AccountPaymentID) JOIN Distribution DS (NOLOCK) ON (DS.DistributionId = PY.DistributionId) JOIN Account AC (NOLOCK) ON (AC.AccountId = DS.AccountId) JOIN PartyAddress PA (NOLOCK) ON (PA.PartyAddressId = PY.PartyAddressId) JOIN Party PT (NOLOCK) ON (PT.PartyId = PA.PartyId) JOIN Payee PE (NOLOCK) ON (PE.PayeeId = PY.PayeeId) JOIN clm CM (NOLOCK) ON (CM.clm_no = PE.clm_no) '
SET @WHERE = 'WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize AND ((@PayeeName IS NULL) OR (PT.[Name] LIKE '''%''' + @PayeeName + '''%''')) AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId)) AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId)) AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber)) AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled)) AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued)) AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid)) AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus)) AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus)) AND ((@PayeeId IS NULL) OR (PE.PayeeId = @PayeeId)) ORDER BY PY.' + @SORT
SET @SQL = @SELECT + @FROM + @ON --+ @WHERE
SELECT @SQL
When I try to execute it, I get the following error:
Msg 402, Level 16, State 1, Line 71 The data types varchar and varchar are incompatible in the modulo operator.
It appears to not like this line:
SET @WHERE = 'WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize
Could anyone shed some light on why it's puking on this? And is you have any possible suggestions to get around this?
