DBASchool – In-Person Class – Hope To See You There!
Instructor: Stephen Wynkoop (me!)
I’ll be teaching a 3-day class on the things that you really need to know about SQL Server. We’ll have hands-on exercises, lots of discussions and real-world lessons learned. Check out the site here and reserve your spot – we will not allow more than 15 students total so we can keep the class to a great size for discussions, demos and such. I can’t wait to work with everyone – register ASAP if you’ll be attending.
[Class Information] (check out the quotes on the site too from past attendees!)
On Using Access with SQL Server
Thomas wrote in with some additional information and experience working with SQL Server and Access, I wanted to pass it along here.
"In your recent editorial comment about Access and SQL Server, you quote Ian Posner as saying that Access retrieves key data from multiple tables back to the client and performs join operations there.
This is an oversimplification.
In many cases Access queries will pass all join logic back to the server, even with nested queries (one Access query referring to another) and large numbers of tables (I have a 20-table join to an SAP database that is passed back to the server and performs really well). The problem is that it doesn’t always do it, and sometimes a small change to a query results in it being processed locally (with, as Ian says, often disastrous effects on performance).
Figuring out when Access is going to decide to bring a join locally is a bit of a black art, and would benefit from more public discussion.
More complex queries involving outer joins, group by statements and multiple data sources are the things to look out for.
I strongly recommend a Windows registry setting (for Access 2007: HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice12.0Access Connectivity EngineEnginesODBCTraceSQLMode=1), which traces all the SQL that ODBC actually sends to the server to a file sqlout.txt (normally in My Documents). If a query runs slowly, you can check the trace to see how the join is being performed."
Newsletter Holiday Schedule
We’ll be taking a break over the Thanksgiving holiday – we’ll be back on Monday – have a great Thanksgiving everyone!
Featured White Paper(s)
SQL Server Fragmentation Explained
This technical whitepaper will help you understand SQL Server fragmentation and the performance benefits you can gain on your… (read more)