Hi There, Can I store flash files in the database. I have a table with one of the columns as varbinary datatype. Previously, I was saving images (as filebytes) into this column. But now I need to implement something to store the flash file. So, is it possible to store flash files in the database and retrieve them and display them in an asp.net page? Thanks a lot for your suggestion!!
1. How do I store audio and video files in sql server 2000. 2. Is it possible to store and retrieve audio and video files using t-sql 3. Which is the most efficient way to store and retrieve audio and video files in sql server
As part of a web site I allow users to store documents in SQL Server 2000 using an Image column on a table. After the file is saved the user can open the document through the browser. This works great for most file extensions such as ".XLS" or ".DOC" but fails when attempting to open ".MSG" files (saved outlook messages). XLS documents will open in Excel and DOC documents open in Word, but MSG documents open in Notepad and display what appears to be binary data. I'm trying to figure out if this is a SQL Server issue or some sort of ASP/ASP.NET webserver issue.
If I attempt to open an MSG file stored directly on the web server, the file opens correctly using the "Microsoft Outlook View Control", but when attempting to open the same file saved in SQL Server, it attempts to open in notepad.
I need help(Tutorials or online links) regarding storing and retrieval of files in Sql server (BLOB) using ASP.net and C#. Secondly,Is it possible to search file in BLOB using SQL server Full text search service.
I have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.
So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?
Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?
Which is better, to store the files onto the server's folders or to a database? I tried storing to MSSQL 2000 but the varbinary does not allow me to set "MAX" for the data type.
Hi,I was wondering what anybodies feelings on storing images and files in SQL server express 2005?I am guessing it is a No No...If this is the case what are the best practices for achieving this?Would something like a table which held the "real name of the file", "a unique identifier" do the trick...hence in code (c#) i could create a GUID for the unique identifier maybe??? and then upload the pictue or file to a common directory and name it with the unique identifier rather than its real name.This means that i could potentially hold a different file with the same name i.e. no conflictsMy app is a sort of HTML editor in asp.net (all html is stored in the db) so if i do upload a picture using a GUID or uniquee identifier then if somebody saves teh image from a webpage then it would be called uniqueIdentifier.jpg which is probably what i don't want.So the only other idea i had was to create a directroy within my site which I would name with the unique identifier and upload the pics and files with thier real names into this directory. Hence each document would have its own directory so there would be no conflicts with filenamesAny advice or comments really appreciatedThanks in advanceIan
I have a table that I'm inserting a file into and using the Image data type to store the binary object. Now the code below works fine for files around 1.5 MB, but anything larger and it's like the code won't even execute and I get a Page Not found error. I'm in the process of running some traces to find out what's going on in the backend, but I'm assuming there's something amiss with my code. The Image data type should handle files that size with no problem but for some reason it isn't. Does anyone see anything wrong? Thanks Dim iLength As Integer = CType(File1.PostedFile.InputStream.Length, Integer) If iLength = 0 Then Exit Sub 'not a valid file Dim sContentType As String = File1.PostedFile.ContentType Dim sFileName As String, i As Integer Dim bytContent As Byte() ReDim bytContent(iLength) 'byte array, set to file size
'strip the path off the filename i = InStrRev(File1.PostedFile.FileName.Trim, "") If i = 0 Then sFileName = File1.PostedFile.FileName.Trim Else sFileName = Right(File1.PostedFile.FileName.Trim, Len(File1.PostedFile.FileName.Trim) - i) End If conn = New SqlConnection(eco) conn.Open() cmd = New SqlCommand("INSERT INTO ECO_Attachments (ECOID, FromType, DocName,OldRev,NewRev,NtLogin,DisplayName, FileName, FileSize, FileData, ContentType) VALUES (@ECOID, @FromType,@DocName,@OldRev,@NewRev,@NtLogin,@DisplayName, @FileName, @FileSize, @FileData, @ContentType) ") cmd.Connection = conn Try File1.PostedFile.InputStream.Read(bytContent, 0, iLength) With cmd .Parameters.Add("@ECOID", SqlDbType.Int) .Parameters.Add("@FromType", SqlDbType.NVarChar, 50) .Parameters.Add("@DocName", SqlDbType.NVarChar, 250) .Parameters.Add("@OldRev", SqlDbType.NVarChar, 50) .Parameters.Add("@NewRev", SqlDbType.NVarChar, 50) .Parameters.Add("@NTLogin", SqlDbType.NVarChar, 100) .Parameters.Add("@DisplayName", SqlDbType.NVarChar, 200) .Parameters.Add("@FileName", SqlDbType.NVarChar, 255) .Parameters.Add("@FileSize", SqlDbType.Real) .Parameters.Add("@FileData", SqlDbType.Image) .Parameters.Add("@ContentType", SqlDbType.NVarChar, 50) .Parameters("@ECOID").Value = ECOID .Parameters("@FromType").Value = From .Parameters("@DocName").Value = DocName .Parameters("@OldRev").Value = OldRev .Parameters("@NewRev").Value = NewRev .Parameters("@NTLogin").Value = NTLogon .Parameters("@DisplayName").Value = DisplayName .Parameters("@FileName").Value = sFileName .Parameters("@FileSize").Value = iLength .Parameters("@FileData").Value = bytContent .Parameters("@ContentType").Value = sContentType .ExecuteNonQuery() '.ExecuteScalar() End With Catch ex As Exception Response.Write(ex) 'Handle your database error here conn.Close() End Try
Hi,I am tasked with creating a site in two languages: English and Farsi (Persian), which is a right-to-left language.I am having problems with the Faris version when it comes to stroing and retrieving data. I can use Farsi fonts and type data into tables, but when I try to retrieve them they are displayed as ?????. I check the table and it says there were problems retriving data and asks to run the query again. When I click on "Execute SQL", the content of the fields that were in Farsi change to ?????.I am new to MS-SQL and would appreciate it if you could tell me how to set up non-English database.TIA.
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph? Let me know if you need a better explanation. Thanks in advance!
Hi, can we keep a file like word file or html file in the sql server database? if yes, then can we search any thing in these stored file? regards mihir
Is there any way to store a binary or image file into a database record / save a binary or image record back into a file using Transact SQL? If yes, would the "image" data type be most suitable?
(As a background, I am working on a process that deals with incomign emails, and I am using SQL Mail for that purpose. The above is needed to deal with attachments that may come with emails, and which are saved in a physical file on the HDD).
What are the negatives, challenges and issues going to be if we choose to store 300-1000 GB worth of image, xml and PDF files (avg size 1 mb) in SQL server 2005 db.
What is the best way to store 100 of thousands of .jiff files (<5 MB each) to be used (creating and retreiving) in .net c# new application using sqlserver db.
We will also need to migrate existing files working with old VB application to .net c# application
So the next question will be to: what is the best way to migrate these files into new db/system
Hey I currently have a foreach loop container working which scans a folder, loops through the files in this folder and then moves them to a new folder.
At the same time I also do an SQL insert into a table logging the details of the transfer.
What I would like to do next is to store the actual PDF in binary in my DB (varbinary format). How would I go about this ?
Hi - I'm new to SSRS/SQL and have a situation I can't figure out. I've tried a number of things, but I'm not sure the best way to return a calculated column based on the structure of my tables.
I have a primary Detail table that tracks product testing results. The Detail records need to be calculated against a "Year" table that contains a set of values by year. This table contains a single record for each year. There isn't a direct key that joins these two tables.
The Detail table looks like: Record ID: 9999 PO Number: 12345 Date: 12/1/2007 Test Result: 1.52
The Year table looks like: Record ID: 1 Start Date: 10/6/2007 End Date: 6/14/2008 LowVal1: 1.0 HighVal1: 1.49 Incentive1: .05 LowVal2: 1.50 HighVal2: 1.59 Incentive2: .06 and so on...
The Detail records need to find the correct Year record based on the Detail date, find the correct value within the LowVal - HighVal range based on the Test Result value, and then multiply the Test Result by the correct Incentive value and return that value.
I was able to find the correct LowVal-HighVal range using an Expression in the report column. Now that I need multiple years, I can't figure out the best way to configure the query.
Any suggestions would be appreciated. Thanks in advance!
how can i stored audio file in Sql Server, so that i can loop my web page to fill the table with a link to download the audio. What will be the best way to achieve that.
Hi folks,Is it possible to store Binary Files in MS SQL 2000 ??? Say I have a100K PDF or a 150K word document. Is it possible to store this in afield in MS SQL and pull it out somehow? We're using ColdFusion on theserver on Apache.Thanks,Ringo
I am new to VC# and SQL Express. I am currently trying to find a way to store user selected files into SQL Express. I am using a column with the varbinary(MAX) data type. My current thought is to give the user a open file dialog box, and let them select the file they want to upload. On closing this dialog box, I convert the file selected into a file stream and insert into SQL Express.
Is this method a good one? If anyone else can give me pointers or hints to better methods, please do!
I'm deploying a desktop application with Sql CE 3.5. I have a collection of files that I would like to be save as a binary format in SQL CE. These files range from 1KB to 5MB. I know I cannot use varbinary(max) , instead I am limited to varbinary(8000), but this obvisously comes short of 5MB. There are articles stating to use the image datatype to get around this. Is this just bad practice, should I keep the files on the file system or take advantage of this work around? Do you have any suggestions?
Hello,I'm looking for a tutorial or some sample code to save a file in sql server with c#. So far I have not been able to find a anything I could use as most tutorials are in VB. I'm using the varbinary max type to store the file. The part I need help with is selecting the file and converting it using the Stream object, not the SQL insert statements. Thanks,Arnold
I did some quick Googling and didn't find the article of my dreams here. Any experience with storing files in the db in 2005? Any opinions?
Here's the deal. We have our new 'Enterprise' software being designed and written in St. Petersburg (not FL) and I'm reviewing the design for dealing with letters and forms that are generated as hard copy from our business (by the thousands per month) and sent to clients for review, signature etc. Then they are returned by fax or snail mail.
The current design has all of these being stored in image data type columns in the database--storing saved files of the actual outgoing stuff and saved files of the stuff that comes in (scanned copies or the files from our fax server).
This screams 'BAD IDEA' based on our experience doing this in SQL Server 2000. We get things like 23 page faxes from Dr's offices, large packets of FMLA forms returned, etc. In 2000 they tended to get rather large when stored in the DB and caused us all kinds of headaches when Microsoft changed how it recognized and displayed certain types of files, e.g. tiffs.
Further, the outgoing stuff is largely boilerplate with added fields from the db--name, address, a few dates. My proposed design for outbound notifications and forms was to keep only references to the template used, the date created, and an id to link it to the personal info of the schmoe to whom it was sent--essentially, store which document template and then just enough data to map to the variable content that is pulled from the db. Then you can recreate the file that was printed and mailed at any time in the future, but you don't actually save the .doc (or Word .xml) anywhere, not in the file system, not in the db.
The current developers feel this is way too much work to design--and it is certainly more effort than just generating .docs/.xml files via a merge with Word and then stuffing those into the database. They also argue that having them in the db makes them 'more secure' and more 'accessible' to users around the globe.
Does anyone know if the image storage is any better in 2005 than 2000? The .docs & .tifs we used to store just about tripled in size when we put them in the db in 2000. Plus there were the defrag issues and generally it was an unsatisfying experience.
Does anyone know what the advantages/disadvantages of generating Word .xml files might be? Can they be stored as xml data type in SQL Server? Perhaps this is a better option?
If anyone has the time to digest this, please send opinions.
i need to store a bmp or zipped file in a field of a ms sql db. i read the file using vb6 o.net, and in my mind i think sto store it in binary mode. the files could be more of 12 mega. whitch kind of field a could use?
I'm building a system when one can upload a document to the website.I will be storing the document on the hard-drive for quick/easy access,but I was also thinking of storing it in an existing database since mostof the sites information is all stored there.As well there would be only one place to worry about backing up. And ifthe file on the hard-drive was ever missing or became corrupted, I couldrestore it form tha database. Is this feasable? Has anyone ever done this?--* Don VaillancourtDirector of Software Development**WEB IMPACT INC.*phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.com/ This email message is intended only for the addressee(s)and contains information that may be confidential and/orcopyright. If you are not the intended recipient pleasenotify the sender by reply email and immediately deletethis email. Use, disclosure or reproduction of this emailby anyone other than the intended recipient(s) is strictlyprohibited. No representation is made that this email orany attachments are free of viruses. Virus scanning isrecommended and is the responsibility of the recipient./
Hello -- I'm building an app that will allow users to create their own photo galleries. At this point, I'm planning on storing all photos as byte arrays in SQL server image fields. Besides the organizational benefit, is there a space benefit to doing this? That is, if I have 1MB of .jpg's, will those same images take up less than 1MB of file space within the database? One of the reasons I ask is that most hosting plans out there seem to offer more "normal" disk space than is allocated for the database, so I'm trying to make a best plan to accommodate what will probably end up being the biggest disk space consumer in my app (the photos, that is). Any other recommendations re: this scenario (hosting, best practices) are appreciated. TIA, Eric
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
Hello and thanks for taking a moment. I am trying to retrieve and display an image that is stored in SQL Server 2000. My aspx code is as follows:<HTML> <HEAD> <title>photoitem</title> <meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1"> <meta name="CODE_LANGUAGE" Content="C#"> <meta name="vs_defaultClientScript" content="JavaScript"> <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5"> </HEAD> <body MS_POSITIONING="GridLayout"> <form id="Form1" method="post" runat="server"> <asp:DataGrid id="DataGrid3" HorizontalAlign='Left' runat="server" AutoGenerateColumns="true" Visible="True"> <Columns> <asp:TemplateColumn HeaderText="Image"> <ItemTemplate> <asp:Image Width="150" Height="125" ImageUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem, "InventoryItemPhoto")) %>' Runat=server /> </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid> </form> </body></HTML>-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------My code behind file is below VS 2003 does not like my datareader. It says the following: 'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'Items'If there are any suggestions as to what I am doing wrong I would appreciate the input. - Jasonusing System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;using System.Data.SqlClient;using System.Text;namespace ActionLinkAdHoc{ /// <summary> /// Summary description for photoitem. /// </summary> public class photoitem : System.Web.UI.Page { string connStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; protected System.Web.UI.WebControls.DataGrid DataGrid3; private void Page_Load(object sender, System.EventArgs e) { // Get the querystring ID string item =Request.QueryString["ID"]; int ID=Convert.ToInt32(item); SqlConnection dbConn5 = new SqlConnection(connStr); SqlCommand sqlCom5 =new SqlCommand("sp4TWRetrieveItemPhotos"); sqlCom5.Connection = dbConn5; sqlCom5.CommandType = CommandType.StoredProcedure; sqlCom5.Parameters.Add("@ID", SqlDbType.Int); sqlCom5.Parameters["@ID"].Value =ID; dbConn5.Open(); SqlDataReader myDataReader; myDataReader = sqlCom5.ExecuteReader(CommandBehavior.CloseConnection); DataGrid3.DataSource = sqlCom5.ExecuteReader(); DataGrid3.DataBind(); while(myDataReader.Read()) { Response.ContentType = myDataReader.Items("JPEG"); Response.BinaryWrite(myDataReader.Items("InventoryItemPhoto")); } dbConn5.Close(); } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } #endregion }}