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 |
|
ramdas
Posting Yak Master
181 Posts |
 Posted - 2003-01-12 : 12:14:32
|
| Hi,1. I have couple of access queries which need to be coverted to SQL Server. What is the optimal strategy for converting those queries: 2. Can bring over access queries to SQL Server through DTS?3. I am having issues with JOIN statements in Access, is there a way to map them out so that they can be written in SQL Server.ByeRamdas NarayananSQL Server DBA |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-12 : 14:52:04
|
| 1. Move the data to SQL Server tables and write them in T-SQL. Use the Access SQL as a GUIDELINE, do not just copy and paste the Access SQL into SQL Server.2. I don't think you can, nor would you want to. See above.3. Move them to SQL Server and your JOIN problems will go away.One of the problems with Access JOINs is that Access needs multiple JOIN clauses to be nested within parentheses; SQL Server does not, and nesting them can adversely affect performance in SQL Server. That is why I strongly urge you NOT to copy and paste the Access code. It probably WONT'T work anyway, and it will probably perform worse than writing the code in T-SQL from the beginning.Additionally, there are features in T-SQL that are vastly superior to what MS Access can do. If you have a number of Access queries that only serve as the basis for OTHER Access queries, chances are they can be written as one query in SQL Server, or be put into a stored procedure that can accomplish all of the same steps.Do not just copy an Access database to SQL Server, you'd be wasting your time and the power of SQL Server by doing so. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-01-13 : 17:32:41
|
| To expand on item 2, copying with DTS, I tried this once in the past to see what would happen. At first it looked okay, but upon further inspection what I found was that the system had done was import the results of the query into SQL Server as a new table and not a view, so you completely lose any dynamic aspect to it. So don't be fooled! Follow Rob's advice on rewriting.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
|
|
|
|
|
Access Queries-.SQL Server