December 3, 2002 at 7:09 am
Here is the result of the View "MyView"
Col1 Col2
------- --------
x1 data1
x1 data2
x3 data3
x2 data4
x3 data5
Here is the following code
DECLARE @Str1 AS NVarchar(2000)
SET @Str1 = 'x1, x2'
SELECT * FROM MyView
WHERE (SELECT CHARINDEX(MyView.Col1, @Str1))<>0
Question: Why is that, that this code works on one database (lets say db1) and does not work on an other database(db2). Both databases have the same structure, have the same data.
If I use only "SELECT * FROM MyView" it works on both databases (I get the same result), but if I add the condition with "CHARINDEX" it stops working on databases db2 (it does not return any records, like the condition would not be true).
db2 is the same database as db1, with one difference: it is the restored backup of db1.
Both are on the same server using "MS SQL Server 2000", with 80 compatibility level on both.
How can I fix this problem?
🙂
December 3, 2002 at 7:30 am
Your syntax looks invalid and not sure why would work in one but not the other. Try this insted and see if works in both, I think the (SELECT CHARDINDEX...) is throwing it somehow.
SELECT * FROM MyView
WHERE CHARINDEX(MyView.Col1, @Str1) != 0 --I prefer != instead = <>
December 3, 2002 at 11:50 pm
Dear Antares686!
Your advice did not fix the problem. The symptom is the same with the new syntax, too.
But, I'll keep in mind your advice.
🙂
December 4, 2002 at 5:51 am
Can you post the DDL of the table from DB1 and DB2 for the underlying tables and the view. May be that something about the datatypes is causing the issue. For example if one is CHAR(2) and the other is CHAR(4) then in the other every items has 2 spaces after it so 'X1 ' would not be found in 'x1,x2' or some other kind of simple issue from the structure could be the issue here.
December 4, 2002 at 6:08 am
What is DDL?
"For example if one is CHAR(2) and the other is CHAR(4) then in the other every items has 2 spaces after it so 'X1 ' would not be found in 'x1,x2' or some other kind of simple issue from the structure could be the issue here."
I simplified a little bit, but yes, the data in col1 is variable-length, meaning that it can be "x111", "x12" etc., but this did not stop the syntax specified before to work correct on one database.
I suspect that Extended Properties are involved (probably some of them did not script or un-script well, and that's causing the problem), and I'm searching in this direction...
Meanwhile I expect somebody in the forum who had came across something like this, or who could tell me in wich direction to search.
Could be that some hidden database settings are different, because I could not find any difference in the "visible" database settings. I don't know...
At first sight everything seams to be identical, but the behaviour is different.
Edited by - botond.baika on 12/04/2002 06:30:47 AM
🙂
December 4, 2002 at 7:47 am
"db2 is the same database as db1, with one difference: it is the restored backup of db1
". If this is the case all settings should be the same. In a different post you spoke about scripting, how did you create db2? I did a test and the query returns 3 records (Col1 = x1 or x2). If I create a new db by restore or script the resultset is always consistent.
Regards,
Andy Jones
.
December 4, 2002 at 9:08 am
quote:
What is DDL?
DDL = data definition language.
Post the statement used to create the table and the view.
create table t1(
col1 char(1),
col2 varchar(255))
create view v1
as select col1, col2 from t1
I can't explain differences in DBs if one works and another doesn't but do ensure that your view definition includes the column names in it and not select *.
December 4, 2002 at 1:37 pm
It seems like something had to have changed and with the DLL you are getting down to comparing them for differences. Also, by posting the DDL of the tables and view we can set up tests similar to what you are doing.
December 5, 2002 at 1:09 am
The whole story is the following:
I am using MS Access XP .adp and MS SQL Server 2000 to realise my project. This means that if I modify an SQL object's properties from the ADP, there are extended properties added to that object, even if that is not necessary.
Posting the DDLs used for creating the to databases has no use, because:
The first database was created with ADP, EM (Entreprise Manager) and QA (Query Analizer) from scratch. The second was created by scripting the first and then "un-scripting" it (of course the extended properties were scripted, too). After the un-script, the second was extended using the same tools mentioned above.
So, as you can see there are many other factors that could cause "turbulences". PLUS: none of the notations are in English, so you could not understand a thing.
What I managed to get out from investigating the two databases is: there are 2 tables (among others) from which my view gets it's data, 2 tables that have different values for the extended property "MS_OrderBy" than their originals (the originals have nothing for this value, the copies have a valid order by clause)... probably they got it when editing with ADP.
Theoretically this shouldn't be a problem... but it would not be the first time when a "Theoretically this shouldn't be a problem" kind of thing IS the problem...
I am further more investigating 🙂
What I am sure of is that not a banal problem is causing this (for example: using "SELECT *" in the view instead of enumerating all the columns, or any other bad or inappropiate syntax). I checked all of this, and talked over with all my more experienced collegues. So, when I got here in this forum, I have already checked all the banal problems that could be envolved. What I am expecting, is something that I have not think of.
Best Regards,
Botond Baika
PS: Don't give up on me! 😉
🙂
December 12, 2002 at 12:47 am
Hi everybody!
First of all, my apologies, especially for Antares686, who wrote (and I did not pay enough atention):
"May be that something about the datatypes is causing the issue. For example if one is CHAR(2) and the other is CHAR(4) then in the other every items has 2 spaces after it so 'X1 ' would not be found in 'x1,x2' or some other kind of simple issue from the structure could be the issue here."
Because he was right. This was the issue. The data in db2 from col1 had spaces after the codes, till the length of the field was reached, meaning that: the field type was NVarchar(15), so if the code had 2 characters, for ex. "x1", than the rest was filled up with 13 spaces, which != "x1", so it would not be found.
BUT, the field containing "x1" plus 13 spaces still was "INNER JOIN"-ed corectly with a field "x1" (without 13 spaces) from an other table (the one with the descriptions of the codes).
I would appreciate if anyone of you would comment this weird "INNER JOIN" phenomenon.
Thanx everybody for your time and help!
Botond
PS: Sorry if I seemed too arrogant.
🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply