Programatically Create a SQL Table with a Variable in The Name

I ran into a situation where I needed to compare an Excel table to an existing SQL table, then update the SQL table with only the missing information.  My goal was to create a temporary table, then compare and update.  The problem for me arises when more than 1 user wants to update their information at once; I am worried that using a static name for a temp table might create issues.  Here is how to create and delete a table with a variable in the name.

declare @makeTable nvarchar(max)

declare @delTable nvarchar(max)

declare @tablename varchar(200)

declare @variable varchar(20)

set @variable= 'userName'

set @tablename = 'Temp_ProjectRisksExcel_' + @variable

-- Create Tabele

set @makeTable = 'create table ' + @tablename +

      '(   

      [ExecutionPlanCode] [varchar](20) NULL,

      [RiskIdentifierCode] [varchar](40) NULL,

      [RiskTitle] [varchar](250) NULL,

      [RiskDescription] [varchar](1500) NULL,

      [Occurrence] [varchar](50) NULL,

      [Impact] [varchar](50) NULL,

      [RiskConsequence] [varchar](1500) NULL,

      [Strategy] [varchar](50) NULL,

      [RiskMitigation] [varchar](1500) NULL,

      [Comments] [varchar](1500) NULL,

      [RiskRetired] [varchar](50) NULL,

      [RiskRetiredComments] [varchar](1500) NULL,

      [RiskRetiredDate] [date] NULL

      )'

     

exec(@makeTable)

-- Delete Table

set @delTable = 'DROP TABLE ' + @tablename

exec(@delTable)

Http to Https Redirect in IIS7

To keep with the subject of error pages, I thought it would be good to touch on how to redirect end users and force them to use port 443 instead of port 80.  There is a very simple way to accomplish this in IIS7, using the same Error Pages Feature that we used in the last posting on creating a SharePoint Custom Login Error (401) Page.
1) Go to IIS and select your web site.

2) If you have not already done so, edit the site bindings to add the port 443
3) Open the Error Pages Feature under the IIS area.  In the right column, click Add.

4) Add a Status Code of 403.4 and select Respond with a 302 redirect.  Put in YOUR https address!

5) Open the SSL Settings in the IIS area for your site.

6) Click the Require SSL check box, and click Apply in the upper right Actions column.

SharePoint Custom Login Error (401) Page

When dealing with lots of people logging into your SharePoint site, you will spend a lot of time answering phone calls from people with login errors. A nice and easy way to preemptively help deal with login failures is to use a custom error page.

1) Go to your IIS and look up where the custom errors are located for the site.

2) Look at the path location for the location of the error files.

3) Go to the file location,, you will want to edit the 401.htm file.

4) The last step is to modify the web.config for your SharePoint site.  You will need to add (modify) the system.webserver.
Download code here

To add custom error pages in different languages, just add the pages to the appropriate local language folder.  You can find the local language folder information here...  http://msdn.microsoft.com/en-us/library/bb266177.aspx

Excel Import Over 255 Characters

Another Excel gotcha!!!  I have a SQL column called myText and it is a varchar(2500), but when I tried to import the appropriate cell, I kept getting null values returned.  If my cell was 255 characters or less, I would be fine.  The first hint was in the sample preview for cell formating.  Anytime I saw hash marks in the sample box, my text would not import.

Here is how I fixed it!  Added a Custom "text" Type, and all the hash marks disappeared!

Cannot Build Data View Web Part Remotely

I was trying to build a Data View Table using SharePoint Designer 2007 remotely.  I could create my Data Source and see all of my columns.  However, when I clicked to view the Data Source Details, I received an error: 

"The server returned a non-specific error when trying to get data from the data source. Check the format and content of your query and try again. If the problem persists, contact the server administrator."

After doing a little research, all I needed to do was modify the SqlDataSource SafeControlAssembly in the web.config file.

SafeControl Assembly="System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="System.Web.UI.WebControls" TypeName="SqlDataSource" Safe="true" AllowRemoteDesigner="true" 

I made the change, and now I can build my Data View Web Part from home, over the web, without a VPN!