September 30, 2011 at 3:08 pm
Hello Friends, I've a question on Access to sql db conversion datatype change. In access i have datatype as Decimal places as "Auto" what should be the Datatype in SQL...
2. How can I convert all access queries into sql Stored proc in an automated way?---Please help
September 30, 2011 at 3:17 pm
sqlquest2575 (9/30/2011)
Hello Friends, I've a question on Access to sql db conversion datatype change. In access i have datatype as Decimal places as "Auto" what should be the Datatype in SQL...2. How can I convert all access queries into sql Stored proc in an automated way?---Please help
There is no hard and fast answer to either of these.
The datatype will depend on what you are actually storing in that field.
I doubt there is any way to automate converting an access query to a stored proc and have it work. There are just too many oddball syntax differences. #01/01/2011# instead of '1/1/2011'. There may be some sort of import tool but I would advise examining your stored procs for accuracy anyway.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 1, 2011 at 12:00 am
This may help
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26709
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 1, 2011 at 8:11 am
Thank you all for your reply but just to give you little more background. I used SSMA to migrate the Access DB to SQL but still having Datatype mapping issue as i mentioned in my earlier post that in access all Decimal places as "Auto" became int in sql but i am not sure what should be the correct datatype in sql
My second question was all my Access queries became Sql views ...Is there any way that they can be converted into Sql proc or functions? Please help
October 1, 2011 at 8:46 am
long time since I migrated Access to SQL...so not really sure what SSMA actually does in the "conversion"
for data types : http://msdn.microsoft.com/en-us/library/ms187752.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 1, 2011 at 9:30 am
Looks like you got your answer to the Data Type Issue.
You basically take you Access queries and convert to a SP. I don't know how good of a job id did in creating the view but you can alter the view so that it is structured right.
If you have Access Queries based on other Access Queries then you are going to have Sub Queries and InLine Views with CTE's, etc.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 3, 2011 at 5:48 pm
I've been working with SSMA to migrate my (fairly large) A2007 app from ACCDE/ACCDB (Front End/Back End) to ACCDE/SS2k5. I have to say, the most recent version is a joy. Please, if you haven't upgraded to version 5.0, do so immediately.
Here's my strategy (your mileage may vary....)
0. ALWAYS USE A COPY OF YOUR PRODUCTION DB....DO NOT RISK YOUR PRODUCTION DB!
1. Using vanilla data mapping, convert all your tables repeatedly, fixing issues with each iteration. Fix them on the Access side, and then reconvert them. You can repeatedly overwrite the SS database.
2. An error doesn't mean an error. SSMA converts every data type I used except memo fields, hyperlinks, and multi-valued fields. See the blog site for what to do there. Also, see the FMS website for advice (WWW.FMSINC.COM). Most errors from the conversion are data-name warnings. Using data names that are SQL reserved words or containing spaces generate warnings. Act accordingly. Change them if you can. If not, move on.
3. An autonumber field will be changed to an INT datatype and set up as an auto-incrementing IDENTITY column in SS. That's all you need to do. It works just like Access after that.
4. Use SSMA to "link" your new tables to your Access FE (I assume you plan to continue using Access as a front end....) Then, test them to be sure you can get to them, and your users can get to them from the Access front end. Simply open the table in Access, and it should pop right up just like it did before.
5. You don't have to convert all your queries to SS. In fact, you're better off NOT converting them right away until you've tested your new setup to be sure it's working as expected. Virtually all your queries will still work--though maybe slowly.
6. Once you've converted your tables, let SSMA take a shot at converting your queries. It will not try to convert most action queries (Insert, Update, Delete, etc.) and it generally will not convert UNION queries, though version 5 seems to take a shot at them. Expect lots of errors, and don't fret about them. The ones that convert easily become items you can check off your list!
7. You may NEVER want to convert all your action queries. They work pretty fast for the most part, and don't need to be converted unless you're abandoning Access as a front end. Concentrate on getting your non-action queries converted first because they don't have issues with #date# or single/double quotes.
8. Two items to remember: Passthrough queries may not be updateable as expected. Or even at all. Also, while Access allows you to update a query that references two or three tables, and even update more than one of them, SS is stricter, and will only let you update one table, and then only when SS can identify the exact row (using a primary or unique key....)
9. SSMA adds columns called "SSMA-Timestamp" to most tables it converts. This is annoying because now when you have a query that issues a "SELECT *" on more than one table, there will often be a duplicate data name you'll need to deal with. I changed most of my queries to name the columns rather than using SELECT *.
Again, I say, SSMA is a wonderful tool, but you need to read the SQL Server conversion literature that abounds on the internet. Check out
http://blogs.msdn.com/b/ssma/archive/2011/08/03/ssma-feature-overview.aspx
while you're at it.
Good Luck
Jim
October 6, 2011 at 6:05 pm
Thank you so much for your detailed explanation on this issue.. That was really helpful
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply