SharePoint, .Net, SQL, MS BI, Collaboration and Gadgets

There are conditions where we have our development environment on a SQL express (vs 2005) machine and we are trying to get the same code to run on a SQL Server 2005 machine. But, we cannot connect to the ASP.NET provider db even after registering a sql db in asp_net_regsql or something like that. By default, it uses whatever settings are in the machine.config file. So, that file usually has the settings that point to using a LocalSqlServer connection string, which is also defined in the machine.config as a user instance DB in the DataDirectory.

There are 2 ways to make it works:

1. Removing the LocalSqlServer connection string and then adding one to your point to your DB in web.config. The connection strings section of your web.config file should look something like this:

   <connectionStrings>
   <remove name=”LocalSqlServer”/>
   <add name=”LocalSqlServer”
   connectionString=”Data Source=Server\Instance; Integrated Security=SSPI; Initial Catalog=database;”
   providerName=”System.Data.SqlClient”/>
   </connectionStrings>

2. Provide your own <membership> section and specify the specific connection & provider type to use for membership.

   <configuration>
   <connectionStrings>
   <add name=”MySqlConnection” connectionString=”Data
   Source=MySqlServer;Initial Catalog=aspnetdb;Integrated
   Security=SSPI;” />
   </connectionStrings>
   <system.web>
   <membership defaultProvider=”SqlProvider” userIsOnlineTimeWindow=”15″>
   <providers>
   <clear />
   <add
   name=”SqlProvider”
   type=”System.Web.Security.SqlMembershipProvider”
   connectionStringName=”MySqlConnection”
   applicationName=”MyApplication”
   enablePasswordRetrieval=”false”
   enablePasswordReset=”true”
   requiresQuestionAndAnswer=”true”
   requiresUniqueEmail=”true”
   passwordFormat=”Hashed” />
   </providers>
   </membership>
   </system.web>
   </configuration>

* Thanks to Shawn Cicoria and George McKee for the solution.

Do you ever get Access denied error when using MS SQL xp_cmdshell command?

E.g.  I have an special AD account access to \\server2\backups folder.  In my Store Procedure, I have to copy db.bak from local folder to share folder.

EXEC xp_cmdshell ‘copy c:\SQLbcks\db.bak \\server2\backups\db.bak, NO_OUTPUT’;

I get access denied error because xp_cmdshell use permission associate with SQL server service account (local system account). I have to change the SQL service account to use the AD account in order xp_cmdshell able to use the correct permission.  This seems a bit weired.  Do we have other solutions?

I had come across this task few days ago. Hence, put it here for future reference. The requirement is simple. System need to scan a specific folder and import all files into database. Ideally, these are the steps how I do it:

1. Create a import format file using BCP for BULK INSERT usage
2. Use master..xp_cmdshell to get all the file names in to table variable
3. Loop through the table and import data one by one using OPENROWSET and BULK

Create Sample Payment Table

Run Create_Payment_Table.sql to create a “Payment” Table. The columns must match with columns in CSV file.

Creating an XML Format file

Run

EXEC master..xp_cmdshell ‘bcp yourdbname.dbo.Payment format nul -T -t, -c -x -f C:\Payment.xml’

to create an XML format file for BULK INSERT usage.

Alter Sample Payment Table to add more columns

Run Alter_Payment_Table.sql to add extra columns ID, Filename, BatchID, DateCreated, DateModified for tracking purpose.

How to scan all CSV files in a specific folder?

Let say we expect all CSV files will be drop in c:\inbox folder. So, we will use master..xp_cmdshell with DIR dos command to get all the file names.

Store Proc “usp_import”

Loop through inbox folder and call “usp_import_common” to start import.

First declare a @Files table variable and insert result into @Files

Declare @Files Table ([File] varchar(250) null)

Insert all files’ name to @Files

INSERT @Files ([File])
EXEC master..xp_cmdshell ‘DIR C:\inbox\*.csv /b’

Open cursor to import CSV files one by one via store proc “usp_import_common”.

DECLARE @FILENAME VARCHAR(250)
DECLARE file_cursor CURSOR FOR
SELECT [File] FROM @Files
WHERE [File] NOT IN (‘NULL’,‘File Not Found’)

OPEN file_cursor

FETCH NEXT FROM file_cursor
INTO @FILENAME

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FullFileName = @FilePath + @FILENAME
–Start Import
EXEC usp_import_common @FullFileName
FETCH NEXT FROM file_cursor
INTO @FILENAME
END
CLOSE file_cursor
DEALLOCATE file_cursor

Store Proc “usp_import_common”

Import data using BULK command with “Payment.xml” as format file

– Set Payment’s table XML format file
SET @FormatFile =‘C:\Payment.xml’

– Get data from file
SET @SQL1 = ” +
‘INSERT Payment’ +
‘(’ +
‘ [FileName],’ +
‘ [InvoiceNo],’ +
‘ [Date],’ +
‘ [Status],’ +
‘ [CheckNumber],’ +
‘ [VoucherNo],’ +
‘ [PaymentAmount],’ +
‘ [BatchID]‘ +
‘) ‘
SET @SQL2 = ” +
‘SELECT’ +
‘ ”’ + @SourceFile + ”’,’ +
‘ TMP.[InvoiceNo],’ +
‘ TMP.[Date],’ +
‘ TMP.[Status], ‘ +
‘ TMP.[CheckNumber],’ +
‘ TMP.[VoucherNo],’ +
‘ TMP.[PaymentAmount],’ +
‘ ”’ + CAST (@BatchID AS VARCHAR(50)) + ”’ ‘ +
‘FROM OPENROWSET(’ +
‘ BULK ”’ + @SourceFile + ”’, ‘ +
‘ FORMATFILE = ”’ + @FormatFile + ”’, ‘ +
‘ FIRSTROW = 2 ) TMP’
EXEC (@SQL1 + @SQL2)

After import, we will delete the CSV:

DECLARE @DirCMD VARCHAR(1000)
SELECT @DirCMD = ‘DEL “‘ + @SourceFile + ‘” /F’
EXEC master..xp_cmdshell @DirCMD

Finally copy testdata.csv file in to c:\inbox and run EXEC usp_import. Then, you are able to import data successfully.

You may download all the sql, xml and csv file here.