December 30, 2010 at 12:45 am
Hi,
This query is running fine with sql server 2000 version, but SQL 2008 data was retrive, please tell me what is the reason?
select distinct(d.MAC_address) as MAC, d.hostname, c.username,
substring(d.login_name,0,charindex('\',d.login_name)) as dom,
d.pc_make, d.pc_model, d.ip_address,
substring(d.login_name,charindex('\',d.login_name)+1,
len(d.login_name)) as login_id, d.os_name, d.service_pack, d.ram,
convert(varchar(19),d.system_uptime, 100) as system_time,
convert(varchar(19),d.modified_on, 100) as modified,
c.device_name as location, c.port as vlan
from (desktop_other_d o left join desktop_info d
on o.mac_address = d.mac_address ) left join cisco_master c
on d.mac_address = c.mac_address
where d.MAC_address+d.hostname not in
(select MAC_address+hostname from desktop_exclusion
where excl_type like 'USB') and o.type = 'STORAGE'
and o.status != '00000001' and d.MAC_address != ''
and (isnull(device_name, '') not like '%SEZStore%'
and isnull(device_name, '') not like '%DTAStore%')
order by d.mac_address, d.hostname
Thanks for you help
ananda
December 30, 2010 at 1:09 am
whats the error you getting?
----------
Ashish
December 30, 2010 at 2:10 am
it might be because of schema/definition discrepancy.i ran you script on my system it doesn't show any issue/error related to sql 2008
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 30, 2010 at 2:22 am
Maybe after the FROM (...) a table name is required
select
distinct(d.MAC_address) as MAC,
d.hostname, c.username,
substring(d.login_name,0,charindex('\',d.login_name)) as dom,
d.pc_make, d.pc_model, d.ip_address,
substring(d.login_name,charindex('\',d.login_name)+1, len(d.login_name)) as login_id,
d.os_name, d.service_pack, d.ram,
convert(varchar(19),d.system_uptime, 100) as system_time,
convert(varchar(19),d.modified_on, 100) as modified,
c.device_name as location,
c.port as vlan
from (
desktop_other_d o
left join desktop_info d on o.mac_address = d.mac_address
) GIVEANAMEHERE
left join cisco_master c on d.mac_address = c.mac_address
where
d.MAC_address+d.hostname not in (
select MAC_address+hostname from desktop_exclusion
where excl_type like 'USB') and o.type = 'STORAGE'
and o.status != '00000001' and d.MAC_address != ''
and (isnull(device_name, '') not like '%SEZStore%'
and isnull(device_name, '') not like '%DTAStore%'
)
order by d.mac_address, d.hostname
December 30, 2010 at 4:35 am
The FROM part of your select query is starting with table name "desktop_other_d"
I guess this is causing an error
from (
desktop_other_d o
left join desktop_info d on o.mac_address = d.mac_address
)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply