stange problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

stange problem

Author

Topic

elementarladung
Starting Member

4 Posts

Posted - 2010-03-26 : 06:44:10

Hello,

i have to update my sql database for a new company application version, but i have no update script for it. The only thing i have is an example database of the new version. In the new database some column types, indices and stored procedures have changed.
For me, the best way to migrate the database is to copy all data from the old database to the new database (import/export function from management studio). The datacopy is working fine and the new version of the application seems to work. But at the end i found a strage problem and i don't know how to find the error.

I've made a query:

SELECT * from ARTIKEL where ARTIKEL like 'HZ%1'

This query is working fine in the old database. This query is working fine with the sample database, too.
If i copy the data from the old database to the new database then the query is not working anymore (Select * from artikel is working).

I don't know why.
I've started some tasks:
- rebuild inices
- rebuild and create statistics
but no change. The query isn't working anymore.

Any Idea?

I'm using SQL 2005 Std Edition

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-26 : 07:05:26

Any error messages?
Or just no result?
Try this:
SELECT * from ARTIKEL where ARTIKEL like '%HZ%1%'
to see if there are matching records in your table.

No, you're never too old to Yak'n'Roll if you're too young to die.

elementarladung
Starting Member

4 Posts

Posted - 2010-03-26 : 07:11:54

Hi

I've got no error message. Only no result.

Your query
SELECT * from ARTIKEL where ARTIKEL like '%HZ%1%'
is working.

Query
SELECT * from ARTIKEL where ARTIKEL like 'HZ%1%'
is working, too.

But it's very necessary that ist working with
SELECT * from ARTIKEL where ARTIKEL like 'HZ%1'

In the old and sample database it's working fine.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-26 : 08:19:32

Maybe there are spaces or anything else after that '1'?
Try this to examine what's going on:
SELECT * from ARTIKEL where ARTIKEL like '<' + 'HZ%1' + '>' -- so you can see if there are trailing spaces or so.
Ad switch the result window from grid to text so maybe you can see any whitespace...like CRLF.

No, you're never too old to Yak'n'Roll if you're too young to die.

elementarladung
Starting Member

4 Posts

Posted - 2010-03-26 : 08:37:26

Hi

may be i've found the problem (with your help).
I've created a stored procedure (with clr in vb .net) that will copy
all tabledata to the new table with the following command

INSERT INTO db2.dbo.tabelle (@SpaltenNamen) SELECT @SpaltenNamen FROM db1.dbo.tabelle

The artikel field in the db2 is char(22), in the new db it is nvarchar(22). I think it's an conversion error.

I should trim the data. i hope i will find a way ^^

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-26 : 08:44:43

char(22) --> will always add trailing space to have always a length of 22
nvarchar(22) --> will not have trailing spaces and it is using only the needed length.

No, you're never too old to Yak'n'Roll if you're too young to die.

elementarladung
Starting Member

4 Posts

Posted - 2010-03-26 : 09:38:30

Hi,
i've changed the copy script that every values are trimmed ad the end. Everything is working fine, now.

Thx for helping

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-26 : 09:49:14

you're welcome