Get List Of Files From a Windows Directory to SQL Server

In SQL Server, we read data from single text file; excel file…etc. However we can extend this to read all the files in a particular directory. This post demonstrates a part of this scenario. Here we will discuss how to get the list of files from a particular directory, then can be extended to load them into SQL Server, which is not the scope of this post.

So in order to use xp_cmdshell, we need to enable it as it is disabled by default.

Here is the way to enable:

1 2 3 4 5 6 7 8 9 10 11 12

--allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO --Update the currently configured value for advanced options. RECONFIGURE GO --Enable XP_CMDSHELL EXEC sp_configure 'xp_cmdshell', 1 GO --Update the currently configured value for this feature. RECONFIGURE GO

Refer Image-1 for the list of files in the directory. Now create a table and load the file list into it:

(Image-1)

1 2 3 4 5 6 7 8 9 10 11 12 13

--Create the table to store file list CREATE TABLE myFileList (FileNumber INT IDENTITY,FileName VARCHAR(256)) --Insert file list from directory to SQL Server DECLARE @Path varchar(256) = 'dir C:\Import\' DECLARE @Command varchar(1024) =  @Path + ' /A-D  /B' INSERT INTO myFileList EXEC MASTER.dbo.xp_cmdshell @Command --Check the list SELECT * FROM myFileList GO --Clean up DROP TABLE myFileList GO

Here is the result in the table.

How to execute generated script(.sql file) with schema and data in SQL Server 2008

SQL Server offers 2 command prompt features that can se used for executing large queries - osql (will be removed in future), and sqlcmd

osql is located in the Tools\Binn subfolder. To execute a SQL script:

1. Start the Command Prompt
2. Navigate to the folder where the osql utility is located
3. Run the command in the following format:
   osql –H <workstation name> -S <server_name[\instance_name]> -U <user login ID> -P <login password> –i <full path to script>

To execute the large.sql file located in the D:\test, against the Central database on the SQL Server instance Dell\SQL2012, as an sa with the 'sqladmin' password, run the following command:

osql -H Dell -S Dell\SQL2012 -i D:\test\large.sql -U sa -P sqladmin

The sqlcmd command line utility is also located in the SQL Server’s Tools\Binn sub-directory. To execute a SQL script:

1. Start the Command Prompt
2. Navigate to the folder where the sqlcmd utility is located
3. Run a command in the following format:
   sqlcmd –S <server name> -d <database name> -i <full path to script> -U <user login ID> –P <login password>

To execute the same as above, run the following command:

sqlcmd -S Dell\SQL2012 -d Central -i D:\test\large.sql -U sa –P sqladmin

Start the sqlcmd Utility Run Transact-SQL Script Files Using sqlcmd

Difference between .NET Framework 4.5 and .NET Framework 4.0

.NET Framework 4.0 support fallowing features:-

Web.config File Refactoring
Extensible Output Caching
Auto-Start Web Applications
Permanently Redirecting a Page
Shrinking Session State
Expanding the Range of Allowable URLs
Extensible Request Validation
Object Caching and Object Caching Extensibility
Extensible HTML, URL, and HTTP Header Encoding
Performance Monitoring for Individual Applications in a Single Worker Process
Multi-Targeting .... etc

.NET Framework 4.5 support fallowing improvement features:-

Asynchronously Reading and Writing HTTP Requests and Responses
Improvements to HttpRequest handling
Asynchronously flushing a response
Support for await and Task-Based Asynchronous Modules and Handlers

 What is new in .NET Framework 4.5 

.NET Framework 4.5 Support for Windows Runtime
.NET Framework 4.5 Support for Metro Style Applications
.NET Framework 4.5 Support for Async Programming
 Garbage Collector Improvements
.NET Framework 4.5 is Faster ASP.NET Startup
.NET Framework 4.5 Support Better Data Access
.NET Framework 4.5 Support WebSockets                         
 Workflow Support - BCL Support

connect to an MDF database file asp.net

con.ConnectionString = @"Data Source=.\SQLEXPRESS;
                          AttachDbFilename=c:\folder\SampleDatabase.mdf;
                          Integrated Security=True;
                          Connect Timeout=30;
                          User Instance=True";

Get table name and its SCHEMA NAME from database sql server

SELECT Distinct TABLE_NAME,SCHEMA_NAME() FROM information_schema.TABLES

C# / SQL Get all Stored Procedures and their Code

SELECT    sysobjects.name, syscomments.text
FROM    sysobjects
JOIN    syscomments
    ON    sysobjects.id = syscomments.id
WHERE    xtype='p'