I had finally had it. I was no longer accepting the lack of tool support while editing the .SqlDataProvider scripts for DotNetNuke modules. I needed to figure out a way to make Visual Studio treat those files as if they ended with .sql instead of .SqlDataProvider so that it would give me syntax highlighting for my SQL scripts. Finally, I found an article about doing the same sort of thing for Torque files. I don't have any idea what Torque is, but what I found there seemed to do the trick.
NOTE: The below tip involves editing the registry. No warranty is expressed or implied. YMMV.
What I learned from the above linked article is that the file associations are controlled in the registry. By simply adding a .sqldataprovider key to the correct location and giving it the same value as the .sql key, everything magically works as expected.
The key to create is HKLM:\SOFTWARE\Microsoft\VisualStudio\[version]\Languages\File Extensions\.sqldataprovider where [version] is 7.1 for Visual Studio .NET 2003, 8.0 for Visual Studio 2005, etc. Those are the two versions I updated, so those are the two I'm sure of. You should be able to find other versions without too much trouble. If you're into PowerShell (I'm getting there), here are the four separate commands to run in order to add this functionality to those two versions of Visual Studio.
For Visual Studio .NET 2003:
New-Item -Path 'HKLM:\SOFTWARE\Microsoft\VisualStudio\7.1\Languages\File Extensions\.sqldataprovider'
set-itemproperty -Path 'HKLM:\SOFTWARE\Microsoft\VisualStudio\7.1\Languages\File Extensions\.sqldataprovider' -name "(default)" -value "{A5C4C661-62BB-11D1-9CFD-0000F81E818C}"
For Visual Studio 2005:
New-Item -Path 'HKLM:\SOFTWARE\Microsoft\VisualStudio\8.0\Languages\File Extensions\.sqldataprovider'
set-itemproperty -Path 'HKLM:\SOFTWARE\Microsoft\VisualStudio\8.0\Languages\File Extensions\.sqldataprovider' -name "(default)" -value "{FA6E5E79-C8EE-4D37-B79A-5067F8BD5630}"
**UPDATE** For Visual Studio 2008:
New-Item -Path 'HKLM:\SOFTWARE\Microsoft\VisualStudio\9.0\Languages\File Extensions\.sqldataprovider'
set-itemproperty -Path 'HKLM:\SOFTWARE\Microsoft\VisualStudio\9.0\Languages\File Extensions\.sqldataprovider' -name "(default)" -value "{FA6E5E79-C8EE-4D37-B79A-5067F8BD5630}"
**UPDATE** I just upgraded to Vista x64, and the registry path changed a bit (as found in a comment to a similar blog post). I think the change is because of x64, rather than Vista, though if you're on some other x64 OS, you might want to double-check before making this change. That said:
For Visual Studio 2008 on Vista x64:
New-Item -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\9.0\Languages\File Extensions\.sqldataprovider'
set-itemproperty -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\9.0\Languages\File Extensions\.sqldataprovider' -name "(default)" -value "{FA6E5E79-C8EE-4D37-B79A-5067F8BD5630}"
**UPDATE** It looks like this is no longer still required for Visual Studio 2010. In the Options dialog for Visual Studio, you can go to the File Extensions node under Text Editor, and from there map SqlDataProvider to the Transact-SQL Editor directly within the IDE (instead of directly monkeying around with the registry) Despite "Transact-SQL Editor" showing up as an option within the "All Extensions" page of "Text Editor" options, I've had no luck getting that to cause any sort of syntax highlighting. However, after rummaging through the registry, I did find the GUID for the VS 2010 SQL editor (which wasn't in the same place as the previous versions, but in HKEY_USERS\.DEFAULT\Software\Microsoft\VisualStudio\10.0_Config\Languages\File Extensions\.SQL). This worked for me on Visual Studio 2010 on Windows 7 x64:
New-Item -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\10.0\Languages\File Extensions\.sqldataprovider'
set-itemproperty -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\10.0\Languages\File Extensions\.sqldataprovider' -name "(default)" -value "{c4d96929-a9b0-42cc-b3e0-adac0435d7f2}"
Hope it helps!