April 9, 2013 at 10:28 am
Hello,
We have an app that copies files to a new location via unc...\\Server\Share. This however is slow over vpn and despite being wide open still challenges for credentials. Another option would be storing the files (they are small usually around 10kb) in the SQL 2008R2 db. I had tried this previously and had trouble. It seemed that storing the file into the db was not a problem but writing it out the file was unrecognizeable. IE it was an excel file and would go in as such but come out and not open. So I am looking for advice and or pointers for how to approach this. As of yet I do not have any code to critique as I'm trying to establish a good understanding to build code on.
So any reading / direction would be appreciated
JB
No worries Google got me some good reading
Thx
April 10, 2013 at 2:05 pm
Hi There.
Check out Adam Haines reply,
1 - Use open rowset bulk to import data.
2 - Use bcp to export data.
You could use bcp to import, but use a format file.
Good luck
John Miner
Crafty DBA
www.craftydba.com
April 10, 2013 at 2:17 pm
To read and write files stored in varbinary you have to convert to and from a byte array.
Here is a snippet that I use to retrieve pdf's from a database and send it directly to the client's browser in .NET.
File.WriteAllBytes(path, dt.Rows[0]["MyPDF"] as byte[]);
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}.pdf", fileName));
Response.WriteFile(path);
//need to flush and close the response prior to deleting the physical file so the response.WriteFile doesn't break
if (Response.IsClientConnected)
{
Response.Flush();
Response.Close();
}
if (File.Exists(path))
{
File.Delete(path);
}
To upload you basically just have to do the opposite. Read the file to a stream and then output it into a byte array. I can probably find some code that does that if it helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2013 at 3:47 pm
Powershell I've used:
$server = "dkrSQL2012"
$database = "AdventureWorks2008"
$query = "SELECT TOP 10 Document, FileName FROM Production.Document WHERE Document IS NOT NULL"
$dirPath = "C:\Users\Steve\Documents\Docs\"
$connection=new-object System.Data.SqlClient.SQLConnection
$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database
$command=new-object system.Data.SqlClient.SqlCommand($query,$connection)
$command.CommandTimeout=120
$connection.Open()
$reader = $command.ExecuteReader()
while ($reader.Read())
{
$sqlBytes = $reader.GetSqlBytes(0)
$filepath = "$dirPath{0}" -f $reader.GetValue(1)
$buffer = new-object byte[] -ArgumentList $reader.GetBytes(0,0,$null,0,$sqlBytes.Length)
$reader.GetBytes(0,0,$buffer,0,$buffer.Length)
$fs = new-object System.IO.FileStream($filePath,[System.IO.FileMode]'Create',[System.IO.FileAccess]'Write')
$fs.Write($buffer, 0, $buffer.Length)
$fs.Close()
}
$reader.Close()
$connection.Close()
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply