I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
Need help reading a binary file see below for details...
I have uploaded a csv file into a sql table. Now i want to extract the data and insert the data in the csv file into another sql table. What commands can i use in sql to extract/ read the data ?
Hi have done the following but get the errors at the end, any ideas Created a stored procedureALTER PROCEDURE ulfile@upload varchar(200)ASINSERT INTO results(@upload)VALUES (@Upload)RETURN after sitting for two hours I realised I had a comma after (200) then the following in the .cs file using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class Default3 : System.Web.UI.Page{//PostedFile Property of Type HTTPPostedFileint intDocLen = txtFileContents.PostedFile.ContentLength;byte[] Docbuffer = new byte[intDoclen];protected void Page_Load(object sender, EventArgs e){ Stream objStream;//InputStream://Gets a Stream object which points to an uploaded Document; //to prepare for reading the contents of the file.objStream = txtFileContents.PostedFile.InputStream;objStream.Read(Docbuffer, 0, intDocLen);//results is the connection objectcmdUploadDoc = new SqlCommand("ulfile", results);cmdUploadDoc.CommandType = CommandType.StoredProcedure;//Add the Params which in the Stored Proc cmdUploadDoc.Parameters.Add("@upload ", SqlDbType.VarChar, 200);//Set Params ValuescmdUploadDoc.Parameters[0].Value = txtTitle.Text;//Set the "@Doc" Param to be Docbuffer byet ArraycmdUploadDoc.Parameters[1].Value = Docbuffer;cmdUploadDoc.Parameters[2].Value = strDocType; }private void btnSubmit_Click(object sender, System.EventArgs e){string strDocExt;//strDocType to store Document type which will be Stored in the Databasestring strDocType;//Will be used to determine Document lengthint intDocLen;//Stream object used for reading the contents of the Uploading DocumnetStream objStream;SqlConnection results;SqlCommand cmdUploadDoc;if(IsValid){if(txtFileContents.PostedFile != null){//Determine File TypestrDocExt = CString.Right(txtFileContents.PostedFile.FileName,4).ToLower();switch(strDocExt){case ".doc":strDocType = "doc";break;case ".ppt":strDocType = "ppt";break;case ".htm":strDocType = "htm";break;case ".html":strDocType = "htm";break;case ".jpg":strDocType = "jpg";break;case ".gif":strDocType = "gif";break;case ".im":strDocType = "im";break;default:strDocType = "txt";break;}//Grab the Content of the Uploaded DocumentintDocLen = txtFileContents.PostedFile.ContentLength;//buffer to hold Document Contentsbyte[] Docbuffer = new byte[intDocLen];//InputStream://Gets a Stream object which points to an uploaded Document; //to prepare for reading the contents of the file.objStream = txtFileContents.PostedFile.InputStream;//Store the Content of the Documnet in a buffer//This buffer will be stored in the DatabaseobjStream.Read(Docbuffer ,0,intDocLen);//Add Uploaded Documnet to Database as Binary//You have to change the connection stringBooksConn = new SqlConnection("Server=66.179.84.110;UID=******;PWD=******;Database=******");//Setting the SqlCommandcmdUploadDoc = new SqlCommand("ulfile",results);cmdUploadDoc.CommandType = CommandType.StoredProcedure;cmdUploadDoc.Parameters.Add("@upload ",SqlDbType.VarChar,200);cmdUploadDoc.Parameters[0].Value = txtTitle.Text;cmdUploadDoc.Parameters[1].Value = Docbuffer ;cmdUploadDoc.Parameters[2].Value = strDocType;Results.Open();cmdUploadDoc.ExecuteNonQuery();Results.Close();}//End of if(txtFileContents.PostedFile != null)}//End Of if(IsValid)}//End of Method btnSubmit_Click} And the form code <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head id="Head1" runat="server"><title>Untitled Page</title></head><body><form id="frmUpload" method="post" enctype="multipart/form-data" runat="server"><span>Title</span><asp:textbox id="txtTitle" runat="server" EnableViewState="False"></asp:textbox><asp:requiredfieldvalidator id="valrTitle" runat="server" ErrorMessage="* Required" ControlToValidate="txtTitle">* Required</asp:requiredfieldvalidator><span>Document to Upload</span><input id="txtFileContents" type="file" runat="server" name="txtFileContents"></></><asp:button id="btnSubmit" Text="Submit" Runat="server" OnClick="btnSubmit_Click"></asp:button> </form></body></html> however when I try and view in browser I get the following Line 21: Line 22: Line 23: Stream objStream; Line 24: //InputStream: Line 25: //Gets a Stream object which points to an uploaded Document; I have trawlled the net, MSDN and anywhere else I can find to see what it is and nothing. eventually changed Stream.objstream to System.IO.Stream objStream; And now getting the following error Line 29: Line 30: //results is the connection object Line 31: cmdUploadDoc = new SqlCommand("ulfile",results); Line 32: cmdUploadDoc.CommandType = CommandType.StoredProcedure; Line 33: //Add the Params which in the Stored Proc Can anyone help?
I'm trying to easily change a value in 80 ssis configuration files from "localhost" to "myservername". I downloaded WinGrep to do this, but it's balking on some of the files, saying that they are binary files.
Does anyone know how to make the *.dtsconfig files NOT binary in SSIS?
Any suggestions GREATLY appreciated.
I won't go into the reason I have my servername in 80 places...
hello! I am curious to know how I can find the length of a binary in Sql Server. The length doesn't work on binaries. ie select length(binary) from Binarytable
I want to have an SSIS package that processes a file in the normal insert, update style. But at the end I want to store the file as a binary field to another table for archive purposes. I am having trouble finding a good way to do this. Any samples, ideas, or articles would be appreciated.
hello dear friends I am trying to save a binary file to database with the following code: public static void memorystreamToDb() { MemoryStream mst = new MemoryStream(); UnicodeEncoding u = new UnicodeEncoding(); string Textn = "Test"; byte[] b = u.GetBytes(Textn); mst.Write(b ,0,Textn.Length ); BinaryReader reader = new BinaryReader(mst); byte[] file = reader.ReadBytes((int)mst.Length); using (SqlConnection connection = new SqlConnection("Some Connection String")) { SqlCommand command = new SqlCommand("INSERT INTO temp (examplefile) Values(@File)", connection); command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file; connection.Open(); command.ExecuteNonQuery(); } reader.Close(); mst.Close(); } or with the other method from a real file (not memory stream) public static void Addfile(string path) { CommonMethods_class k = new CommonMethods_class(); byte[] file = GetFile(path); using (SqlConnection connection = new SqlConnection(k.Get_connection_string())) { SqlCommand command = new SqlCommand("INSERT INTO temp (examplefile) Values(@File)", connection); command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file; connection.Open(); command.ExecuteNonQuery(); } } and after running each of them seams that the file have been saved; but I can not retrive the files. I have tried some solutions from msdn but inside the created file is empty. the point that i really look for it is to just working with memory not in the disk before saving. and then retriving each field that I want. looking forward your points thank you in advance
Hello all, I am exporting data from AS/400 into an xml file and then importing the data from the xml file into an SQL database. I am getting the error: "The statement has been terminated. String or binary data would be truncated" when there are characters like "," or "&" in the xml. How can i solve this problem? Almost all my records have characters like that because they include comments and i can't disallow the users from entering these characters (especially ","). Thanks,Mike.
I have been trying to store binary file in a folder from the SQL Server.
Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected. The folder remains empty after running this query.
I am using FileUpload control in ASP.net 2.0 to upload files and store them into SQL server database as an image. I am fine with MS office files, image files and etc. We have Product Center (Proe) engineering software to configure parts and the files generated through this software have PLT extension when I store these files, the file type is Plian/text. I am using Fileupload.PostedFile.Content to get the file type. How can I store PLT, TIF files in SQL server? Please help.
I need to store several different types of documents (text, MS Word, PDF, etc.) in SQL Server 2005. What is the best way to store file attachments that have different MIME types?
Currently I'm using a table with a varbinary(max) column which works for binary files but not for text files. Do I need to have multiple columns in the table for different file types? Thanks in advance for your help.
CREATE TABLE dbo.[CM_Attachment] ( [Id] int IDENTITY (1, 1) NOT NULL , [Change_Request_Id] int NOT NULL , [Attachment] varbinary(max) not null, [Created_Date] datetime NOT NULL DEFAULT GetDate() , [Created_By] varchar(30) NOT NULL , [Modified_Date] datetime NOT NULL , [Modified_By] varchar(30) NOT NULL ) GO
INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By) SELECT 6, BulkColumn , '', GetDate(), '' FROM Openrowset( Bulk 'C:TestingTest Doc #1.txt', SINGLE_CLOB) -- Does not work -- FROM Openrowset( Bulk 'C:TestingTest Doc #2.xls', Single_Blob) -- Works -- FROM Openrowset( Bulk 'C:TestingTest Doc #3.jpg', Single_Blob) -- Works as ChangeRequestAttachment
I have a design oriented question for a system I am developing. Because of various business concerns and issues we have been moving towards as desing that brings files into the SQL 2005 system as binary columns in a database. These files will then be processed at a later time using SSIS into relation model tables.
Normally I would just have the process be files are placed on a FTP location (or other drive path) and a location is stored in the database versus the storing of them as binary rows in the database. Then later the SSIS package runs using the path information for the conneciton manager.
Based on the proposed binary design I have two questions.
1. Can anyone speak to the advantages, disadvantages, issues they have had, etc to the binary storage method?
2. Can anyone make a suggestion on how they would handle the pulling of the file out of SQL when the file is ready to process? Do you stream it to a file and rebuild it on the physical disk, to then just import it with a connection manager for the flat file structure.... or can you stream it directly into a conneciton manager that reads it like a flat file and parses the file without ever going to disk? Any information on suggested implementations would be helpful.
Is there any real purpose to the File Connection Manager? Not the Flat File Connection Manager, just the plain old File Connection Manager.
I have one in my SSIS package because I thought it might make writing to a file easier. But from what I can tell, it doesn't really do much. I have to pull the file name from the connection manager and basically open and manage the file myself in my scripts.
I went this route because I have two scripts that write to the file in turn... one writing out parent records and the other inserting the child records after each parent.
The script looks like this:
Code Block Dim outFile As String outFile = Dts.Connections("TestFile.LDIF").AcquireConnection(Dts.Transaction).ToString()
Dim sw As New StreamWriter(outFile, True) sw.WriteLine() sw.WriteLine("dn: " & Dts.Variables("GroupID").Value.ToString()) sw.WriteLine("changeType: add")
sw.Close()
I have to close the connection every time because the other script uses the file. It seems that this is quite inefficient. Am I using the File Connection Manager incorrectly? What's a more appropriate way to do this?
I could open the file in a different script and store the StreamWriter object in a global variable, I suppose...
Hey everyone I've got this question that has me stuck for the last few days but its an important part of my website.....What I am trying to do is basicly have a user be able to upload a file, have that uploaded file plus some other info automaticly display on other parts of my site, and have a different user eventually be able to download that file....I have thought about allowing the file upload as a BLOB but still cannot find a proper way to execute this using VB, plus I have heard that this way of doing it is not reccommeneded cause databases were not designed to store large files like this, lots of articles recommened having the file upload to a Folder on your server then get the binary data for the file that can be placed in a database to refrence that particular file.....Well this also proves to be a lot harder then said here is what I got so far (written in C#) protected void UploadBtn_Click(object sender, EventArgs e) { if (FileUpLoad1.HasFile) {
FileUpLoad1.SaveAs(@"C:Documents and SettingsAdamMy DocumentsVisual Studio 2005ProjectsWebsiteFiles" + FileUpLoad1.FileName); Label1.Text = "File Uploaded: " + FileUpLoad1.FileName; } else { Label1.Text = "No File Uploaded."; } } and here is the asp part of the code that goes with it<asp:FileUpLoad id="FileUpLoad1" runat="server" />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label> Now from what I know is I need to get the binary of the file which I have read you can do with the Page.Request.Files statement but again not sure how I would impliment this. Does anyone have any suggestions on which way I should take when dealing with this should I try and just use the BLOB method or use the binary refrence method? and if so how would I impliment this, heck even some good tutorials on the subject would be great... Thanks.....Adam
I have a table with 10 rows with a varbinary column
I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)
I have a well-structured but also very large binary data-set that is generated by a C++ application every five minutes. The data needs to be accessed by SQL applications. Since data is generated every five minutes, performance is key, both for write and read. The data set is about 500MB.If data is written to the file system, the write performance doesn't involve SQL server. For reading it, I have a CLR to read the portions of the data that I need based on offset and length. That works and is very fast. The problem is that data is stored in the file system, so it is not self-contained within the database.
A second option that I haven't explored yet, is to write the data into a table as VARBINARY(MAX). I would read the data using SUBSTRING with appropriate offset and length. Performance of SQL write/read of binary data of this size, and whether there is a third option I haven't thought off. I'm using SQL Server 2014.
I'm using a bit-wise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.you also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?
Hi All, I am not sure whether this is the right place to post this question. But I am unable to figure out what is the best solution to retrieve and display an image in a html file(stored in varbinary(max) column). I have a list of images in the file and I am supposed to display them. Can anybody please let me know what is the best way to do this? Thanks a lot!!
Hi, How can I read a file on the server and insert it into my SQL Server database. The user is not uploading the file, but it already exists on the Server. Thanks
I don't have direct access to the SQL server. I have to write/read through a class I recieved from the IT department. The class also had an example in it but I can't get it to work correctly.
I get an error: Object reference not set to an instance of an object. Line 35
Dim clsWDD As New WebDevDB.WebDevDB Dim intLeng As Int32 Dim intLock As Int32
Dim strAppl As String = "MyApplication" Dim strData As String = "This is my text data!"
I am using bcp to write from a query or table into a text file from a stored procedure. No problem. However, what do I do if I want to write to a text file from another stored procedure which returns a record set? Any help gratefully received. thanks.
I need to write data returned by a stored proc to an XML file. The resultant file will then be consumed by an external application. The file has to adhere to a specific schema. Is there a way to do this through SSIS? Is .NET CLR a better option?
I need to write data returned by a stored proc to an XML file. The resultant file will then be consumed by an external application. The file has to adhere to a specific schema. Is there a way to do this through .NET CLR in SQL Serer 2005? Is SSIS a better option? Thanks.
