Hi, I've recently moved a site to MSSQL server from Access and I've hit a few problems as it implements SQL differently. I've had a lot of duplicate data on the site which I have partly remedied by using SELECT DISTINCT - though I don't know how to use this in conjunction with SELECT * to cure other parts of my site that return duplicate data. Does anyone know how to do this? Thanks!
-
-
First let mi say that MSSQL Server supports most(if not all) of the sql u write in Ms Access; it however builds on this to provide proprietary utility aggregate functions using T-SQL....
So depending on ur calling application(fro nt end)...use 'select distinct' in ur SQL cmd to get distinct record temporarily or u just go 2 d database to do dat - select distinct into a temporary table, delete d table n insert d distinct records into a new table dat bears d name of the old(deleted) table. -
ok thanks, i'll look into that.
Like above because of syntax differences, this no longer works and the page does not display:
"Select * from Events where Format(EventDat e,'yyyymmdd') >= " & FormatDate(Now( ),"YYYYMMDD") & " order by EventDate asc"
I'm pretty sure it has something to do with Format(), but I'm a little unsure. Any ideas?
Thanks!Comment
-
u cannot use an expression directly on the column name, so try:
"Select * from Events where EventDate >= " & FormatDate(Now( ),"YYYYMMDD") & " order by EventDate asc"Comment
-
hmm still not workingComment
-
use convert function of sql serverComment
Comment