SSWUG TV
With Stephen Wynkoop
Check out the new name for our video-based content, which will be one of many upcoming site revitalization efforts. In this episode, we have a two-part interview with SQL Azure MVP Ike Ellis and updates coming from the Microsoft Build conference.
Watch the Show
$$SWYNK$$
Featured White Paper(s)
How to Implement an Effective SharePoint Governance Plan
Written by AvePoint
Moving past the "what" and "why" of governance, an even… (read more)
Doing the Right Thing
I am so grateful when you take time to write in with your opinions or questions. I recently received a query regarding how to read external data stores from SQL Server efficiently. Specifically, the need was to return data from an Oracle data store from a SQL Server query.
Sadly, I’m not going to pick that one up today because I have not had the experience for that. If accessing Oracle as a remote server in a SQL Server query is your cup of tea, feel free to write in with any suggestions you may have.
I have written queries to read data from external sources such as MS Access (Jet), Excel, Paradox, CSV, and a couple other sources. There were requirements that caused me to learn those skills many years ago.
Today, I would probably ask the question, “why?” This is what multi-tier systems are all about. You would have to push me really hard before I would be convinced that the best use of my limited/cherished database processing resources are best used to go get remote data from a different data store.
There are two reasons I can think of to want the data from Oracle when running a SQL Server query.
- The Oracle data is being used to filter the SQL Server data
- The Oracle data is being combined in some fashion with the SQL Server data as a heterogeneous query
In either case, there is nothing requiring SQL Server to obtain the data from Oracle. SQL Server 2008 has the ability to use table value input parameter variables for stored procedures. Due to this capability, I would most likely get the Oracle data and inject it into a SQL Server stored procedure as an input parameter. At this point the Oracle data has been converted to native SQL Server data as a table memory variable, and may be used efficiently as a filter or heterogeneous query.
Ok…that’s a nice pat answer. What would you do if you didn’t’ have access to SQL Server 2008? Your company is still using SQL Server 2005, or even SQL Server 2000. Let’s not dive into that question; I just want to acknowledge that there are always exceptions to every solution.
A real world example I have is when I wrote custom Compression/Decompression functions using the SQL/CLR. I had a hard time writing the code through all the tears coming from my eyes, knowing this was a horrible waste of resources. But it was the right solution for the time for reasons to complicated to enumerate.
How about you? Care to share a war story or two regarding those times when the Right solution was not the best or even a good solution? Perhaps you’d like to share your experience working with external data stores from SQL Server? Send your comments to btaylor@sswug.org.
Cheers,
Ben