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.
| Author |
Topic |
|
jbDevon
Starting Member
4 Posts |
 Posted - 2008-02-20 : 14:53:22
|
| Hello, I have one simple query joining two tables with left outer join on 3 fields and using MIN on two fields. These two tables have lot of data about 3 mil in total. I am trying to migrate db from MS Access to SQL 2005. I get about 689000 rows in SQL Server, vs 863000 rows in MS Access. SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1. [STATE]), T1.COUNT FROM T1 INNER JOIN T2 ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] AND T1.[STATE] = T2.[STATE] WHERE T1.[REASON] <> 'SOMETHING' AND T2.[REASON] <> 'SOMETHING' GROUP BY T1.[MON], T1.[ANUM], T2.[ANUM], T1.COUNT HAVING T1.[MON] <> '-' AND T1.[ANUM] <> '-' I have about 30 queries to migrate and I am sort of stuck. Does any one have any idea ? JB |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-20 : 15:04:45
|
| See SSMA (SQL Server Migration Assistant for access) in Microsoft website and the tool is free.Check it out. |
 |
|
|
jbDevon
Starting Member
4 Posts |
Posted - 2008-02-20 : 15:18:56
|
| Hello sodeep,I had installed it before but when I launch it, it gives blue screen (exception error) on my XP Professional. I have not been able to resolve that.Thanks for your directon.JB |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-20 : 15:23:36
|
| They you have to call microsoft: for product support |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-20 : 15:49:58
|
Do you have NULL values?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
jbDevon
Starting Member
4 Posts |
Posted - 2008-02-20 : 16:05:15
|
| Hi Peso,No I don't have null values. |
|
|
|
talleyrand
Starting Member
35 Posts |
Posted - 2008-02-20 : 17:34:08
|
"I have one simple query joining two tables with left outer join on 3 fields and using MIN on two fields"You say left outer join, but I see inner joins specified. Could Access be doing an old style join and in upsizing the query, it incorrectly changed the join from an outer to an inner? I thought the left outer joins were either specified with that or with FROM T1, T2 WHERE T1.MON *= T2.MON ishPerhaps export the results of each query into a table (I'd put it in SQL Server myself) and then figure out what the missing data looks like. Perhaps you'll be able to observe a pattern in the data. |
|
|
|
jbDevon
Starting Member
4 Posts |
Posted - 2008-02-21 : 13:35:28
|
| Hello talleyrand/Peso,I must admit two mistakes. One, that I have INNER JOIN only between two tables and not LEFT OUTER JOIN. Two, I do have some null values for ANUM, REASON and MON in both of these tables.I ran queries again with an attempt to exclude null using " Not (MON) IS NULL in Where clause and still getting the same # of rows.Regards,JB |
|
|
|
|
|
|
|
|
Why Same query results in two different # in SQL S