thefrozencoder

Programming and Technology blog

Update to Narayana Vyas Kondreddi's generate insert script for SQL Server 2012

If you are like me you like re-using tools that you find extremely simple and easy to use. That is the case with Narayana Vyas Kondreddi’s sp_generate_inserts script for SQL Server 2000, 2005, (works with 2008 yet not documented). So when I installed SQL Server 2012 developer my first item was to re-create all of the system stored procs I have in my toolbox.

Unfortunately running the script from the above link will generate an error because the code references a system stored procedure that no longer exists in SQL Server 2012 master.dbo.sp_MS_upd_sysobj_category.  This stored procedure toggles the system mode feature where any object created while the mode is enabled is flagged as a system object.  Since the undocumented sproc does not exist the script will be created but it will not work properly.

However there is a small update that can be done to the original script to fix the issue.

You need to remove the following lines

--Turn system object marking on
EXEC master.dbo.sp_MS_upd_sysobj_category 1
GO

And replace the following lines

--Turn system object marking off
EXEC master.dbo.sp_MS_upd_sysobj_category 2
GO

With

EXEC sys.sp_MS_marksystemobject sp_generate_inserts
GO

The script should ruin without any errors and work as it did in the previous versions.

I have emailed the owner of the script so hopefully he will update the script for 2012.

Workaround for Installing KB Article KB971092 successfully

In some cases there is an issue with installing the following update “Security Update for Microsoft Visual Studio 2008 Service Pack 1 (KB971092)”.  The workaround to properly install the update you must set the security on the files vsvars32.bat and vcvarsall.bat to allow the local Users group write permission on those files.  I am not sure why this is an issue but from reading other posts and such on the issue most of the affected people do not have vc++ installed.

They can be found in the following folders:

x86

  • C:\Program Files\Microsoft Visual Studio 9.0\VC\vcvarsall.bat
  • C:\Program Files\Microsoft Visual Studio 9.0\Common7\Tools\vsvars32.bat

x64

  • C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\vcvarsall.bat
  • C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\Tools\vsvars32.bat

VS 2008 - Duplicate Toolbox Items

I opened up VS 2008 the other day and noticed that there were multiple instances of the same toolbox item listed (e.g. there were 4 Button controls listed).  I tried to reset the Toolbox but nothing seemed to solve the problem.

The fix was to close VS and delete the 4 *.tbd files located @ C:\Documents and Settings\USER_NAME\Local Settings\Application Data\Microsoft\VisualStudio\9.0\ (under XP / Win2k3) or C:\Users\USER_NAME\AppData\Local\Microsoft\VisualStudio\9.0\ (under Vista / Win2k8 / Win7)

Upon restarting VS seems to rebuild these files and fixed the issue.  Not sure why this happened and seems nobody else does either.

T4 Template lovin'

The following is a couple of T4 templates that I have been fooling around with in my spare time (sharing the time between jQuery, StringTemplate view engine for ASP.NET MVC and other endeavors).  For the most part these templates are nothing special, they generate both the T-SQL (stored procedures) and a simple but effective data access layer.  Like I said nothing special.

A good resource for starting with T4 templates is @ David Hayden site where he has a Screencast on how to use them.  Below describes some of the settings used to get my simple sample up and running.
Extract the _common.tt, DataClass.tt and T-SQL.tt files from the zip and add them to your VS project in a folder (like Generation).  In the _common.tt file there are some settings (variables) that you can use to modify how the code is generated.  You will need SQL 2005 client tools installed on your machine as it uses the SMO object library.  Once the code is compiled just cut and paste it into separate class files and run the T-SQL code on your db.

  • ConnectionString - This is your connection string to your database
  • TableName - This is the table you are going to run the code against
  • SchemaName - Use this for applying a schema to your T-SQL code if you use schemas (default is dbo)
  • ColumnsToOmit - Comma delimited list of columns to omit from the DAL code that is generated
  • NameSpace - Namespace of your application
  • ProcPrefix - Use to add a prefix to your stored procedures (ie. up_)
  • UseShortProperties - True | False to tell code generation to generate the C# short form for get/set properties
  • insertColumnsAsGetdate - Comma delimited list of column names that will automatically be assigned the GETDATE() T-SQL statement on an Insert.  (Also omits the fields from the insert statement parameters)
  • updateColumnsAsGetdate - Comma delimited list of column names that will automatically be assigned the GETDATE() T-SQL statement on an Update.  (Also omits the fields from the update statement parameters)

The code is supplied as is and if it's broken fix it, you're a programmer aren't you?  :P

T4 Templates.zip (4.98 kb)

VisualSVN with Active Directory Integration

So I decided to dump Visual Source Safe (VSS) and move to something that is more robust and is pretty easy to setup.  Poking around the internet it became pretty clear that VisualSVN would be my choice mostly because of its integration with Active Directory.  I never liked that fact that in VSS stored it’s user info and settings in a plain text file and anyone who had access to it with modify privileges could change anyone permissions.  This is not really a tutorial but a means to show interested developers and IT professionals just how easy it is to install VisualSVN and integrate it with your Active Directory model.

  1. Pre-Amble
  2. Pre-Setup
  3. Installation
  4. Post-Installation
  5. Active Directory Setup
  6. NTFS Setup
  7. Conclusion

Pre-Amble
The version of VisualSVN I ended up installing is v1.6.2; the server environment is Windows 2003 Standard (32bit).  Active Directory installed and fully configured.

Pre-Setup
If you are like me the first thing you will want to do is create a location for your Repositories.  Since my file server has a data drive (d:) it made sense to create in on that drive so I could do backups from it.  So I created the folder structure [D:SourceControlRepositories].

Installation
When you download the latest msi from the site it informs you that to setup VisualSVN you need to install TortoiseSVN as well.  This is actually not true as TortoiseSVN is just a visual GUI to access the SVN server.  You will need a SVN client like TortoiseSVN add/check in/out files unless you download one of the few VSS provider plug-ins for Visual Studio if development integration is your primary goal.

When you run the installer these are your only options to configure VisualSVN server:

 

Change the Repositories folder to the folder you created in the Pre-Setup section

Change the Authentication to Use Windows authentication

You may want to change the port number if you have some kind of internal numbering scheme for ports but remember VisualSVN uses a fully configured version of apache so you will not need to integrate it with another web server.

When you select the Next button the install will start and eventually complete.

Post-Installation
Once the setup is completed you will be asked to launch the MMC snap-in for configuring the VisualSVN server (note at this time there is no way to configure remote VisualSVN servers if you are looking to install VisuaSVN on a Windows 2008 Core install.  It is however something will be available in the future based on traffic on the VisualSVN Google groups).

Once the MMC launches you can configure the VisualSVN server further as well us it to create new repositories.  You should read the Recommended Repository Layout support topic on best practices on configuring a layout structure when creating Repositories.

When you click on the Server Url in the right hand window you will be taken to the web site of your VisualSVN server.  You will get the common “There is a problem with this website's security certificate.“ error in your browser.  This is due to the SSL certificate that is assigned to the apache web server on install, which is a self-signed certificate.

You will also be challenged with a ACL user name / password prompt.  This information is the same as your domain login since you selected to use Windows authentication.  By default the setup adds the Builtin/Users group from your domain for the ACL list.

Active Directory Setup
To change the ACL groups highlight the Repositories node in the left window -> right click -> properties.  There you will see a common security window you can add or remove groups from your domain.

How I configured my setup was to create a single group in my AD called Software Developers and add users to this group so that only users in this group can access the VisualSVN server.  Depending on your AD requirements you may implement it differently.  One of the nice things about this setup is the fact that the VisualSVN server runs as a service (by default under the LocalSystem account).

NTFS Setup
As I mentioned in the Active Directory Setup section the VisualSVN server runs as a Windows Service under a privileged account.  You will also need to set up your ACL for the SourceCode folder to allow the groups access to read and write permissions.

Conclusion
The entire install was pretty easy and intuitive and with the AD integration it is a breeze to configure and secure.  The only extra thing I did was to create an actual certificate request from the VisualSVN Server Properties window -> Certificates Tab and submit that request to my AD Certificate server.  I then imported the certificate once I authorized it.  What this does is for users that are AD authenticated you will not get the SSL certificate error in your browser or probably any other application that uses SSL to access the server.

As you can see here I have a valid certificate that is authorized within my AD network and thus no more certificate errors