I'm not sure why this one fails,

I used the following SQL to extract data from our Firms emplyee database and
creates a text file that gets imported into our copy machine controllers.
Output lines look like this:
A|U|4111|MPLS, PBMS|01|0||
A|U|4222|RIC, PBMS|06|0||
A|U|2036|user, name|01|0|N|1234562|
The last number is the last 7 of the persons corporate VISA card. On SQL
2000 this SQL worked:
select 'A|U|' +
RTRIM(EMPLOYEE_CODE) + '|'
+ RTRIM(EMPLOYEE_NAME) + '|'
+ rtrim(offc) + '|0|' +
case position
when 'partner' then 'P'
else 'N'
end
+ '|' , _visano , '|'
from HBM_PERSNL
where INACTIVE = 'N'
and EMPLOYEE_NAME NOT like '%CMS%'
and EMPLOYEE_NAME NOT Like '%billing%'
and EMPLOYEE_NAME NOT Like '%temp%'
On SQL 2005 it doesn't: The sql runs but only delivers those with that
VISA Numeber, where it use to give you both those with and without cards.
Any Ideas?When you don't have a credit card number, are you storing a NULL instead?
Are you getting any errors? How are you creating the file?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jay Bukstein" <JayBukstein@.discussions.microsoft.com> wrote in message
news:697B4582-6D4F-47BD-B5DF-F1AD95422D0C@.microsoft.com...
I used the following SQL to extract data from our Firms emplyee database and
creates a text file that gets imported into our copy machine controllers.
Output lines look like this:
A|U|4111|MPLS, PBMS|01|0||
A|U|4222|RIC, PBMS|06|0||
A|U|2036|user, name|01|0|N|1234562|
The last number is the last 7 of the persons corporate VISA card. On SQL
2000 this SQL worked:
select 'A|U|' +
RTRIM(EMPLOYEE_CODE) + '|'
+ RTRIM(EMPLOYEE_NAME) + '|'
+ rtrim(offc) + '|0|' +
case position
when 'partner' then 'P'
else 'N'
end
+ '|' , _visano , '|'
from HBM_PERSNL
where INACTIVE = 'N'
and EMPLOYEE_NAME NOT like '%CMS%'
and EMPLOYEE_NAME NOT Like '%billing%'
and EMPLOYEE_NAME NOT Like '%temp%'
On SQL 2005 it doesn't: The sql runs but only delivers those with that
VISA Numeber, where it use to give you both those with and without cards.
Any Ideas?|||Hi Jay
a quick question, is _visano a column in the HBM_PERSNL table?
I noticed that at the end of the query you are using a comma instead of + to
concat the string, should the query be:
select 'A|U|' +
RTRIM(EMPLOYEE_CODE) + '|'
+ RTRIM(EMPLOYEE_NAME) + '|'
+ rtrim(offc) + '|0|' +
case position
when 'partner' then 'P'
else 'N'
end
+ '|' + _visano + '|'
from HBM_PERSNL
where INACTIVE = 'N'
and EMPLOYEE_NAME NOT like '%CMS%'
and EMPLOYEE_NAME NOT Like '%billing%'
and EMPLOYEE_NAME NOT Like '%temp%'
Lucas
"Jay Bukstein" wrote:

> I used the following SQL to extract data from our Firms emplyee database a
nd
> creates a text file that gets imported into our copy machine controllers.
> Output lines look like this:
> A|U|4111|MPLS, PBMS|01|0||
> A|U|4222|RIC, PBMS|06|0||
> A|U|2036|user, name|01|0|N|1234562|
> The last number is the last 7 of the persons corporate VISA card. On SQL
> 2000 this SQL worked:
> select 'A|U|' +
> RTRIM(EMPLOYEE_CODE) + '|'
> + RTRIM(EMPLOYEE_NAME) + '|'
> + rtrim(offc) + '|0|' +
> case position
> when 'partner' then 'P'
> else 'N'
> end
> + '|' , _visano , '|'
> from HBM_PERSNL
> where INACTIVE = 'N'
> and EMPLOYEE_NAME NOT like '%CMS%'
> and EMPLOYEE_NAME NOT Like '%billing%'
> and EMPLOYEE_NAME NOT Like '%temp%'
> On SQL 2005 it doesn't: The sql runs but only delivers those with that
> VISA Numeber, where it use to give you both those with and without cards.
> Any Ideas?
>|||Sounds like your server or session defaults on '05 yield null on
concatenation; i.e., select 'a' + NULL yields NULL whereas on 2000 select
'a' + NULL yields 'a'.
Suggest to modify the query to
....
'|' + ISNULL(_visano, '') + '|'
...
In fact, this is a good practice for any columns that allow null. That way
changes to server or session settings always yield the same results.
"Jay Bukstein" <JayBukstein@.discussions.microsoft.com> wrote in message
news:697B4582-6D4F-47BD-B5DF-F1AD95422D0C@.microsoft.com...
>I used the following SQL to extract data from our Firms emplyee database
>and
> creates a text file that gets imported into our copy machine controllers.
> Output lines look like this:
> A|U|4111|MPLS, PBMS|01|0||
> A|U|4222|RIC, PBMS|06|0||
> A|U|2036|user, name|01|0|N|1234562|
> The last number is the last 7 of the persons corporate VISA card. On SQL
> 2000 this SQL worked:
> select 'A|U|' +
> RTRIM(EMPLOYEE_CODE) + '|'
> + RTRIM(EMPLOYEE_NAME) + '|'
> + rtrim(offc) + '|0|' +
> case position
> when 'partner' then 'P'
> else 'N'
> end
> + '|' , _visano , '|'
> from HBM_PERSNL
> where INACTIVE = 'N'
> and EMPLOYEE_NAME NOT like '%CMS%'
> and EMPLOYEE_NAME NOT Like '%billing%'
> and EMPLOYEE_NAME NOT Like '%temp%'
> On SQL 2005 it doesn't: The sql runs but only delivers those with that
> VISA Numeber, where it use to give you both those with and without cards.
> Any Ideas?
>