davie455
asked on
MS Access to SQL Server Express Migration ASP SQL queries language
Hi,
For a few years now I have been running an asp applicaiton with a MS Access Database. Now is the time to move over to MS SQL Server 2005 Express.
Originally I used paramaterized queries in Access but a year ago i though it would aide my inevitable DB migration more painless by coding the SQL in asp instead, leaving just the table structures and data intact in Access. I now realise I may have been better off leaving alone.
My problem is how do I easliy migrate the data and queries to the new dbms (sql server express). The MS management studio seems to have translated the tables (although my Access database used uk date format #31/12/2006# and clearly this has changed although Im not sure how) but the sql which queires the MS access db needs to be 'translated' (including some complex and lenghthy sql) to work with the new dbms.
I would be really grateful for some advice on this experts.
rgds
R
For a few years now I have been running an asp applicaiton with a MS Access Database. Now is the time to move over to MS SQL Server 2005 Express.
Originally I used paramaterized queries in Access but a year ago i though it would aide my inevitable DB migration more painless by coding the SQL in asp instead, leaving just the table structures and data intact in Access. I now realise I may have been better off leaving alone.
My problem is how do I easliy migrate the data and queries to the new dbms (sql server express). The MS management studio seems to have translated the tables (although my Access database used uk date format #31/12/2006# and clearly this has changed although Im not sure how) but the sql which queires the MS access db needs to be 'translated' (including some complex and lenghthy sql) to work with the new dbms.
I would be really grateful for some advice on this experts.
rgds
R
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys, apologies for delay in posting points. I have followed your advice. Most of my queries are pretty simple but there are one or two extremeley lenghthy and complicated statements. I spent all of yesterday translating the biggest and guess what....used to execute in 3 seconds in access and now about 5 in SQL server...this is after a removed access from the equation. So, I am now splitting the query up into something leaner (I always new it was a badly written one anyway and I hope the extra commands in T-SQL will give me more flexibility).
Thanks again.
Thanks again.
ASKER
i think my best option is to first get the data into SQL 2005 but continue to use access as the front end by linking to the data and leave the SQL transactions as they are (access compatible). once I have this up and running then go about rewriting the queries.
Just two points of clarification if I may, acperkins you said: >>As pointed out you will have to change the way you are querying datetime columns. If you were using #dd/mm/yyyy# you should get in the habit now of using an unambiguous date format such as 'yyyy-mm-dd'
does this mean I dont need to worry about the format the data is stored in, I just need to consider what format i query that data in?
Also, some of my more complex queries I have relied 100% on the access query builder to get running. is there some kind of query builder for SQL 2005 syntax...if that makes sense?
Really appreciate the help.