August 20, 2011 at 3:38 am
Hi,
It was working on sql server 2000, not in SQL2K8.
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 + ' GB') as ram,
convert(varchar,convert(datetime,substring(d.system_uptime,7,2)+ '/'
+ substring(d.system_uptime,5,2)+ '/' + substring(d.system_uptime,0,5) + ' '
+ substring(d.system_uptime,9,2) + ':' + substring(d.system_uptime,11,2),103),100) as system_time,
convert(varchar(19),d.modified_on, 100) as modified, c.device_name as location,
c.port as vlan from ((desktop_software s left join desktop_info d on s.mac_address = d.mac_address)
left join desktop_hotfix h on (s.mac_address = h.mac_address))
left join cisco_master c on c.mac_address = d.mac_address Where s.software
like '@RISK%' order by d.mac,d.hostname )
Error
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.
please suggestion me, how to resolve this issuse?
August 20, 2011 at 4:04 am
Hi ananda
Just remove the last brace "order by d.mac,d.hostname )"
Greets
Flo
August 20, 2011 at 4:10 am
Hi thanks for reply.. It is working fine in SQL2K8 after remove the ORDER BY
thanks
ananda
August 20, 2011 at 4:13 am
Hi
Don't remove the whole ORDER BY. If it was needed on your SQL Server 2000, it will still be needed on your SQL Server 2008. Remove only the brace ")" at the end and keep the rest of the ORDER BY clause.
Greets
Flo
August 20, 2011 at 4:36 am
No..
If removed ")" as folllowing error comes
Msg 207, Level 16, State 1, Line 11
Invalid column name 'mac'.
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Thanks
August 20, 2011 at 4:44 am
Hi
Seems like SQL Server 2000 had a bug with column name referencing in ORDER BY clause. I suspect your table "desktop_info" has no column called "mac", does it?
If not, change your order by clause to this:
...
order by mac,d.hostname
Remove the leading alias "d." from column "mac".
If your table "desktop_info" has a column called "mac", and you want to order by this column, you need to add this column to your SELECT clause to be able to order by it.
Greets
Flo
August 20, 2011 at 5:03 am
Hi Thanks for reply...
Yes ...you are correct. there was bug in SQL 2000 also.
Developemnet team build this query and project. after that they are not tested properly for all reports at SQL 2000.
Before upgrade to SQL 2008, I tested with SQL 2008 database it thorowing error this query. I thought both ORDER BY and distict clause mention on same query not support with sql 2008.
By this way corrected myself and inform to devlopement team to change the query at application side. but he is not using any view and SP, directly using asp page.
Thanks
annada
August 20, 2011 at 5:09 am
Hi Florian Reischl, One more help require from your side.
There were 76 non-clustred Index created in sql 2000 as of now data pages and index pages are same MDF file. after upgrade to sql 2008. I want sperate all the index into sperate NDF file. please provide the script for how to move all the existing index another file?
Thanks
ananda
August 20, 2011 at 5:14 am
Hi
ananda.murugesan (8/20/2011)
By this way corrected myself and inform to devlopement team to change the query at application side. but he is not using any view and SP, directly using asp page.
Umm.. you mean those SQL statements are created and fired from a web page? If so, your developers should consider to read some literature about SQL injection and software layers.
Creating SQL statements by string concatenation can easily cause a backdoor for SQL injection, what means hackers can steal or destroy all your data.
From software architecture side, SQL statements in front end make software un-maintainable over a short lifetime. The only part of a software system, that (sometimes) should work with SQL statements is the Data Access Layer.
Greets
Flo
August 20, 2011 at 5:15 am
ananda.murugesan (8/20/2011)
Hi Florian Reischl, One more help require from your side.There were 76 non-clustred Index created in sql 2000 as of now data pages and index pages are same MDF file. after upgrade to sql 2008. I want sperate all the index into sperate NDF file. please provide the script for how to move all the existing index another file?
Thanks
ananda
Do me a favor and create a new thread for this, since this is a new topic. 😉
Greets
Flo
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply