Query Access & MS SQL

Are syntax of queries same in MS Access and MS SQL 2005?
I am asking because I have lot of queries in Access database. Do I have to
change every query when I move to MS SQL 2005?
Thanks!!!Hi
No. Most will work the same, but certain Access specific implementations of
queries will not.
If a query does not work with SQL Server 2000, it won't in SQL Server 2005.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <john713@.hotmail.com> wrote in message
news:ddkllt$f2g$1@.ss405.t-com.hr...
> Are syntax of queries same in MS Access and MS SQL 2005?
> I am asking because I have lot of queries in Access database. Do I have to
> change every query when I move to MS SQL 2005?
> Thanks!!!
>|||Some basic queries might be portable between MS Access and SQL Server but if
you've used stuff like most of the VBA functions or some of the non-standard
SQL elements of Access then they'll need some work. If you are porting an
application to SQL Server then to get the most out of the platform you
should aim to convert your queries and other data-access code into SQL
stored procedures. That'll almost certainly involve a significant re-write
of your app. How far you need to go will depend partly on what you want to
gain from switching platforms.
Also, if your data model is of more than trivial complexity you should
certainly review it when upsizing. Many people use data models in Access
that are poorly normalized. These are OK in Access because Access often lets
you do non-relational stuff in the database to cope with problems like
missing keys or redundant data. In SQL Server you are more likely to have
serious problems with a weak data model. Of course, you may already be
totally confident that your data is strictly Third Normal Form, which will
give you a head start.
Hope this helps.
David Portas
SQL Server MVP
--|||Thanks David,
there are no VBA elements, because I am using VB 6.0 and Access as database,
but some queries are not in VB code, but in Access database, like cross tab
queries.|||John:
Cross-tab queries in Access have no equivalent in SQL Server. Also, as was
mentioned before by others in this thread, VB functions in Access won't work
in SQL.
You may want to check out the following: "Microsoft Access Developer's Guide
to SQL Server" by Mary Chipman and Andy Baron (ISBN 0-672-31944-6), availabl
e
at Amazon.com for under $10 used.
Going through an upgrade from MDB to ADP/SQL I learned to watch for the
"Now()" function in many Access queries, which were replaced with
"GETDATE()". You will need to see what functions are in use.
Good luck.
Toddsql

Quering AS2005 Cube to generate CSV or Excel in SISS 2005

can any one sugest Quering AS2005 Cube to generate CSV or Excel in SISS 2005

i have a mdx query that runs fine in SQLserver 2005 but when use OLEDN source in SSIS and connect to Flatfile source i am getting OLEDB exception is there any work arround i have SQL Server 2005 SP2 on Windows 2003

Any help is really appreciated

thanks in advance

The actual error message would be helpful.

Are you using the OLEDB provider for OLAP to try to connect to a flat file? That won't work.

|||

thank you

i use OLEDB source and under the connection i choose the AS 9.0

can please suggest the ways to quering a Cube using MDX in SISS

|||

You might take a look at this post:

http://sqljunkies.com/WebLog/hitachiconsulting/archive/2007/07/04/42805.aspx

It explains how to use the DataReader source to get the data from the cube.

sql