Since my talk at ToorCon in October, a few questions have come in asking how to compile the xsproc sources I released earlier. Just to make things easier on everyone (mostly me), I’ve decided to post a walk-thru of how to set up a development environment for MS-SQL. This is not only useful for compiling extended stored procedures, but for any MS-SQL development you may do.
To compile the code and all of the available samples you will need to create a new project in Visual Studio, then specify a “Win32 Project” as your template type. Finally in the application settings, set the application type options to “DLL” and the check the “Empty Project” project option under the addition options section. Once the project is created you can import the available source files or create your own files for compilation.
To permit debugging of the xsproc some tweaks need to be made to the development environment. First you’ll want to have the DLL copied over to a more suitable directory. I accomplished this by editing the projects “Post-Build Events” command line option, located in Project/Properties/Configuration Properties/Build Events. I supplied the following command to be executed every time a new build was performed:
copy /y "C:\Users\user\Documents\Visual Studio 2010\Projects\xp_swaphash\Debug\xp_swaphash.dll" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
This copies the compile DLL into the MS-SQL ‘Binn’ directory, where most other extended stored procedure DLL files reside.
Next an instance of SQL needs to be set up so that when the “Start Debugging” option is selected, Visual Studio would has an environment to attach to. To do this, edit the “Command” option located in: Project/Properties/Configuration Properties/Debugging. Set the command line to (accounting for your specific path):
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe
For the command line arguments specify “-c”. Last, set the “Working Directory” option to the directory that the SQL Server binary will reside in.
Be sure to disable SQL Server service, so that your instance of SQL can run without issues.
Once SQL is running, you’ll want to add in the xsproc. This can be done either through the SQL Server Management Console GUI interface, or by executing query commands using the sp_addextendedproc stored procedure:
EXEC sp_addextendedproc xp_mynewxsproc, ‘C:\\myfile.dll’
More recent versions of Visual Studio compile against the “v100” platform toolset, with the option of compiling for the “v90” platform toolset if you have a previously installed version of Visual Studio. For legacy systems or systems that might not have been updated with the latest versions of the VC redistributable package you will need to locate at least Visual Studio 2005. Administrators often frown upon you updating their systems without their knowledge, just so your code will work there.