Reading and Writing Files in SQL Server using T-SQL

SQL Server has never been short of ways to read from and write to files and it is always better to use the standard techniques provided by SQL Server where possible. However, most of them are really designed for reading and writing tabular data and aren't always trouble-free when used with large strings or relatively unstructured data.

For reading tabular data from a file, whether character-delimited or binary, there is nothing that replaces the hoary old Bulk Copy Program (BCP), which underlies more esoteric methods such as Bulk Insert. It is possible to read text-based delimited files with ODBC, simple files can be read and written-to using xp_cmdshell, and you will find that OSQL is wonderful for writing results to file, but occasionally I've found I need to do more than this.

Thankfully, when armed with OLE Automation and the FileSystem Object (FSO), all sorts of things are possible. The FileSystem Object was introduced into Windows to provide a single common file-system COM interface for scripting languages. It provides a number of handy services that can be accessed from TSQL. In this article, I provide examples of stored procedures that use this interface to allow you to:

I am reading .dbf files using openrowset instructions, but it does not work if the files are bigger that 250MB. I been looking a lot for information and doing tests. At the end I tested with all my development enviroment mounted in a virtual machine and
I discovered something really rare. If the RAM assigned to de virtual machine is 1 GB, the openrowset query works fine, no matter how big is the .dbf file(I tested with 1.5 GB files and it works fine). So my question is if somebody knows if is necesary some
special configurations in SQL Server 2005 for to work in a server with 4 processors and 4 GB of RAM. The error message that appears when the server memory exceeds 1 GB is the next:

Periodically I get a request for information on how to read and write binary data to a database running on Windows Mobile. If you search the Internet you can typically find examples that are available on Windows Desktops or allow you to read and write to a local file system. The problem is that it can take a bit of work to get this code to work on Windows Mobile accessing a database.

Ultimately you might be asking, why would I want to store and image in a database? Well in an environment where you synchronizing data between a local mobile database and a consolidated (central) database this can be extremely useful. Imagine if an insurance adjuster went to an accident scene, took a picture of a damaged car, loaded it into his Windows Mobile database and then replicated that image up to the Insurance headquarters for approval. All of this could be done in a very short period of time when using images in the database. Another good example might be a doctor who was waiting for a patient chart to become available. If you could store the image in a database this chart could be sent down to the doctor's device once it became available.

For this article I am not going to get into how to synchronize the images to and from a remote and central database as this is typically fairly straightforward when using a data synchronization technologies like MobiLink

We learned to process XML files using the Document Object Model as implemented by the XmlDocument class. To go further and make XML friendlier, the .NET Framework provides many other classes for different purposes, allowing you to create and manage nodes from custom .NET classes.Besides the XmlDocument and the derived classes of XmlNode, the .NET Framework provides the XmlTextWriter class, which is derived from XmlWriter. The XmlTextWriter class works in a top-down approach to create, or deal with, the contents of an XML file. This class writes an XML node and moves down without referring

I want to create a new table to sql database at runtime. The column
names would be "Name", "Date", "Event". I would also like to have one
more column name "Image" but the problem is that since the amount of
images will vary, I want the application to count the amount of files
in the Multiple fileuploader and then add a image column to the table
for the respected amount of files.

For example if i am
uploading 3 files in the multiple fileuploader, the names should be
"Name", "Date", "Event", "Image1", "image2", Image3"

I also would like that table name to be name dynamically from the textbox Name when the user enters the information.

I
have the following code to make a new table, but I dont know how to
name the fields at runtime like previously asked please help!!!

One of my NUnit tests has to read in some values from config files. In my main application this process works perfectly well, however when I run the unit test, the code that reads in the values from the config files doesnt read anything in. Ive tried putting app.config in my unit test project (I even tried web.config) but nothing seems to work. Are there any special steps involved when reading from config files in an nunit test ?

But of course this is just a string, without any formatting information included. Writing to the document using a StreamWriter worked, but I received errors when opening the document.

I've also read that trying to perform actions on an Office document on a server without the Office products installed on it can result in mayhem. I'm working on a MVC application that will allow users to upload documents to the server, work on them, save their work and email it around. That doesn't specifically require the need to read/write programmatically, but for certain application functions I do need this. Any helpful suggestions are appreciated.

Hi!i have some files, and i need to upload these files into sql server 2008 by using filestream.lets take table name as custfiles, columns are fileid,filename,filepath,filesize.1.how to create table using filestream?2.how to insert file (record) into table from .net environment?3.how to retrive that file from .net environment?please provide related code....THANS FOR ANY HELP

Hi
I am using FtpWebRequest with option KeepAlive = true.
When I upload first file I create ftp request object:
FtpWebRequest result = (FtpWebRequest)FtpWebRequest.Create("ftp://10.10.6.104/lcsroot//inpt//Test//file1.xml");
I set KeepAlive = true and I do upload file.
When I want upload second file I have to create new FtpWebRequest object using new URI
FtpWebRequest result = (FtpWebRequest)FtpWebRequest.Create("ftp://10.10.6.104/lcsroot//inpt//Test//file2.xml");
I set KeepAlive = true and I do upload file.
The problem is that I have to create new FtpWebRequest so I create new connection to FTP server (I have checked using netstat -b command that my application uses another port number for second file).
How can I upload two files (or more) using only on FTP connection/session?
Regards

Hi,
i have nearly 1000 xml files in one folder of similar format and I have xsd for the same as well.
I would like to import all the files in to sql table by either looping through all the files or any other way.
Thanks

I have a SQL Server 2005 database that contains 3 files.ÃÂ Sometimes whenÃÂ I run rhe backup, it appears to only backup 2 of the three files.ÃÂ The only way I can get it to return to a full backup is to restore the database from a backup tape.

I find some information about to connect and get files using below code.
http://www.codeguru.com/csharp/csharp/cs_internet/desktopapplications/article.php/c13163
But i would need to call cd(/) directory using SSIS.
can anyone help how to resolve this issue.N.Srikanth Reddy