I've run a process that extracts data from a SQL Server 2005 DB and outputs the data into a pipe delimited .txt file. After the file has been created I'm trying to insert the data into tables. The insert is failing because of some type of rowterminator character that is appearing at the end of each row. Has this happened to anyone else? How do I get rid of that 'rowterminator' character? By the way, in textpad the character looks like the page return character, something like a backwards P. In notepad it appears as a 0.
Update - the row terminator is coming across as an ANSI character. How can this be passed as a bulk insert parameter??
I don't understand why the row terminator isn't working? Please give insights in to following error message.
The data file looks like this -- data.txt
01/31/07þ005002892Aþ891007967Bþ066106þJACKS DRAW UNIT 5 FT UNþ04þ01þAG01þ11/30/06þ570.96þ710.27þ1.244þ4241.04þ71.37þ530.13þEþ14094528
BULK INSERT WEXPRO_RMS_DATA.dbo.RMS_DATA
FROM 'C:MikeMAIN_DATABASESRMS_DATA.txt'
WITH
(
CHECK_CONSTRAINTS,
DATAFILETYPE = 'char',
FIELDTERMINATOR = 'þ',
ROWTERMINATOR = ''
)
GO
The Error I'm getting is as follows --
Msg 4866, Level 16, State 1, Procedure sp_InsertData, Line 5
The bulk load failed. The column is too long in the data file for row 1, column 17. Verify that the field terminator and row terminator are specified correctly.
Greetings I'm trying to use the BULK INSERT command in SQL Server 2005 to import a file with a column delimiter of ASCII 01 and a row delimiter of ASCII 02. Here's the command I am using:
I don't understand why the row terminator isn't working? Please give insights in to following error message.
The data file looks like this -- data.txt
01/31/07þ005002892Aþ891007967Bþ066106þJACKS DRAW UNIT 5 FT UNþ04þ01þAG01þ11/30/06þ570.96þ710.27þ1.244þ4241.04þ71.37þ530.13þEþ14094528
BULK INSERT WEXPRO_RMS_DATA.dbo.RMS_DATA
FROM 'C:MikeMAIN_DATABASESRMS_DATA.txt'
WITH
(
CHECK_CONSTRAINTS,
DATAFILETYPE = 'char',
FIELDTERMINATOR = 'þ',
ROWTERMINATOR = ''
)
GO
The Error I'm getting is as follows --
Msg 4866, Level 16, State 1, Procedure sp_InsertData, Line 5
The bulk load failed. The column is too long in the data file for row 1, column 17. Verify that the field terminator and row terminator are specified correctly.
Using the DTS wizard in SQL 2000 Enterprise Manager, a DTS Package can be saved as a Visual Basic file. If a row terminates with a {CR}{LF}, the appropriate .ConnectionProperties("Row Delimiter") = vbCrLf is included on or around the third line of the package connection information for the text file.
I have two different files that I cannot import using the saved VB file. One is a .txt file with a carriage return {CR} as a row terminator. This imports fine using DTS but not from VB. The row delimiter is omitted when the package is saved. I have tried adding the connection properties using vbCr, {CR}, and CHR(13) as the row delimiters, but none of these will import the file from VB.
The other file, and the solution for this file can be used for the previous file, is a .dat file exported from SAP (I do not have access to pull the data directly from the Oracle servers into SQL). If the file is opened in a text editor, it contains no row terminator. DTS allows me to specify where the row ends and imports the file, but, once again, this property is omitted when the package is saved as a Visual Basic file. Unable to find a list of possible .ConnectionProperties, I have tried "Row Length", "Row Width", and every other possibility I could think of, but the file will not import. The records are 429 characters in length.
Hi there, I'm trying to import a cobol file (.dat) which has a line feed as the row delimiter. Using the TransactSQL Bulk Insert with a row terminator of '' is not working for me. Does anyone know the equivilant row terminator of a LF? (Using the Import Export wizard I supply a {LF} and it likes that fine). I would like to use the Bulk Insert Statement for more control of the data. Any help is greatly appreciated.
Hello all,I have a multiple text files with an odd row terminator. If you were toexamine it in VB it would be like a "CrCrLf" instead of just "CrLf". InHEX it is DDA instead of just DA. When I am trying to import into mytable using BULK INSERT I use "" as the row terminator but that isputting the the previous character into the column and then it signalsa carriage return when I attempt to query the data.Any suggestions on what I should use as the row terminator? Is itpossible to tell BULK INSERT to use something like "CHAR(10)"?"" does NOT work.Thanks in advance.
I have a file that has fixed row size of 148 and fixed column size, but the file has no end of line character. I know it is wierd but a client has made the file and refuses to change the format. So I am stuck with reading it the way it is. In Enterprise Manager, I used the Import/Export wizard and I specified fixed length and it let me specify 148 as the lenght of each line. Then it recognized the file and I was able to read it in. I saved the DTS package and I can run it over and over again using dtsrun. However I want to do the same thing using Bulk Insert. How do you specify fixed row length for Bulk insert and how do you give it individual column lengths?
I have a CSV file that I am trying to bulk load into a temp table. The data in the file is all jumbled together, as in, there does not appear to be a row terminator. However, I do see a bunch of little rectangular boxes that I assume are the row terminators.
When I run the bulk insert, the data is treated as one string. For example... If I have 10 columns in the table, the 10 columns will be populated, but the remainder of the data is dumped into the last column.
Here are the row terminators I have used so far that haven't worked.
I have a file that has fixed row size of 148 and fixed column size, but the file has no end of line character. I know it is wierd but a client has made the file and refuses to change the format. So I am stuck with reading it the way it is. In Enterprise Manager, I used the Import/Export wizard and I specified fixed length and it let me specify 148 as the lenght of each line. Then it recognized the file and I was able to read it in. I saved the DTS package and I can run it over and over again using dtsrun. However I want to do the same thing using Bulk Insert. How do you specify fixed row length for Bulk insert and how do you give it individual column lengths?
Hi, I have a data file which consists of data as below, 4 PPU_FFA7485E0D|| T_GLR_DET_11||
While iam inserting into table using bulk insert, this pipe(||) is also getting inserted into the table, here is my query iam using to insert the data using bulk insert.
BULK INSERT TABLE_NAME FROM FILE_PATH WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''')
Hi All, I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.
UPDATE #2: When it said "Do you want to install Microsoft SQL Server" I said "yes" and that caused it to work. I exited and re-ran and now the print runs w/o the "install SQL Server" (If the prompt had said "Do you want to install the print dialog" we wouldn't be having this discussion...)
UPDATE: After posting this i discovered that the same thing occurs when attempting to print the report direct from IE6: First a dialog pops up "Do you want to install this software?" Name: Microsoft SQL Server. When I click "Don't Install" I get the dialog "unable to load client print control." Since this happens direct from IE6 I suspect it's browser settings. I'll resume tomorrow and post a followup.
My WinForm C# app integrates Reporting Services by calling them from WebBrowser controls. The problem is attempts to print cause a dialog: "unable to load client print control."
I've read prior posts that say "enable Active-X in your browser" - I don't know how to do that from a WebBrowser control.
Any ideas how to support Reporting Services "Print" from within a WebBrowser control?
My development environment includes Visual Web Developer, SQL Server 2005 Express and SQL Server Management Studio Express. I have a .sql control string that creates a database. The control string is in my App_Data folder within my web site. When I use SQL Server Managment Studio Express to run the .sql control string and create the tables etc. it does it in my SQLEXPRESS folder and not in the web. How do I run the .sql file so it will create the database in my App_Data folder within the web site? Thanks, Kyleq
I wanna know how to retrieve & insert an image from/to a Sql Server database. using image control or any other control I’ve done that with windows form picture box. As that was explained in MS tutorials Code…. -------------------------------------------Dim ms As New MemoryStreamPictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)Dim arrImage() As Byte = ms.GetBuffer ms.Close() Dim strSQL As String = _ "INSERT INTO Emp (EmpName,EmpSalary,Picture)" & _ "VALUES (@EmpName,@EmpSalary,@Picture)" Dim cmd As New SqlCommand(strSQL, ConnEmp) With cmd .Parameters.Add(New SqlParameter("@Picture", _ SqlDbType.Image)).Value = arrImage .Parameters.Add(New SqlParameter("@EmpName", _ SqlDbType.NVarChar)).Value = txtEmpName.Text .Parameters.Add(New SqlParameter("@EmpSalary", _ SqlDbType.Decimal)).Value = txtEmpSalary.Text End With cmd.ExecuteNonQuery() ------------------------------------------- But with a web form’s image control I DO NOT know how to do it I real appreciate your help Thank you
Hi,i have a question, i have a VS2005 and a SQL server 2000 enterprise in my office that i used for web developement,iam new to this, i made a data driven site using some automated controls available in the toolbox, but i need to made some manual process, like i need to make a LABEL show the SUM of specific number fields from my data base, they say i have to make a OLEDB connection using VB, but i don't know how,can you tell me how?Thanks
hi friends, I created sqldatasource control. In select command i written the query like this "select * form emp" and bounded in grid.How can I change the query for searching the details according the date wise when i click the search button.
Hello experts i have a SqlDataSource Control on my web form. Here is the source for the control <asp:SqlDataSource ID="sqlSearchDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:DbefomsConnectionString %>" SelectCommand="SELECT [icon], [file_name], [path] FROM [tblfile] WHERE ([file_name] = @file_name)"> <SelectParameters> <asp:ControlParameter ControlID="txtSearch" DefaultValue="0" Name="file_name" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource> This control displays icon, file_name, path in GridView Control The problem is How can i store the above field values in an asp.net variable. Thanks Regards Ali
Hi! I'm building a web application with ASP.NET, and using MS SQL 2000 for my database server. How should I do to guarantee the integrity of the data in spite of the concurrent access? Meaning... how can I make sure that more than 1 user can update 1 table at the same time, while no error will occur? Do I need to add some codes at my aspx file? Or do I need to do something to my database? Or do I not have to worry about it? Thank you.
If i use the TimeTracker app from the starter kit suite and I enter my SQL connection in the web.config file it works fine,
If i build a simple app with just a sqladapter, sqlconnection and dataset control then enter in the page_load procedure
sqlDataAdater1.fill(dataset11) datagrid1.databind() It keeps giving my errors see below: I created a connection with the sqlconnection control and used the same userid and password I used in the time tracker stater kit's web config file which works - I have no idea what is going on
*** is it better to create the sqlconnection control first then create the sqladapter or vise versa. Server Error in '/WebApplication2' Application.
Login failed for user 'IssueTrackerUser'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'IssueTrackerUser'.Source Error:
Line 55: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Line 56: 'Put user code to initialize the page here Line 57: SqlDataAdapter1.Fill(DataSet11) Line 58: DataGrid1.DataBind() Line 59: Source File: c:inetpubwwwrootWebApplication2WebForm1.aspx.vb Line: 57 Stack Trace:
Do you have to use the sqldatasource in conjuction with another control like gridview or dropdown list? I'd like to check to see if a record exists in one table before inserting data into another.
Hello.I'm new to ASP.NET and trying to write data to a Microsft SQL Server. I have created a SqlDataSource control, which is 'connected' to my SQL server.Now my question is how i can put data in the database throw the control, and read data from it. I've read the site, but it's still unclear for me..Greetings!
I'm writing a page to do some reporting off of one of our databases, and I'm using 3 control parameters for my sql query that feeds my datagrid. 2 of the ControlParameters are from dropdownlists, and one is from a RadioButtonList. The ControlParameters that reference the dropdownlists are both working well, but the one for the RadioButtonList is not. The error I am getting is: Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '@Booga'. (I changed my ControlParameter name from DateStr to Booga to try to avoid any conflicts with reserved words.)Any ideas? Here is a snippet of my code: <form id="form1" runat="server"> <table width="100%"> <tr> <td style="text-align: center"> <strong>DataCenter</strong></td> <td style="text-align: center"> <strong>Time Scope</strong></td> <td style="text-align: center"> <strong>Call Status</strong></td> </tr> <tr> <td style="height: 47px; text-align: center"> <asp:DropDownList ID="DropDownList1" DataSourceID="SqlDataSource2" AutoPostBack="true" DataTextField="LocationName" runat="server" /> </td> <td style="height: 47px; text-align: center"> <asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="true" Font-Size="Smaller"> <asp:ListItem Selected="True" Value=" 1 = 1 ">All</asp:ListItem> <asp:ListItem Value="((CAST(CallLog.RecvdDate AS smalldatetime) + 1) >= (CAST(GETDATE() AS smalldatetime)))">Last Day</asp:ListItem> <asp:ListItem Value="((CAST(CallLog.RecvdDate AS smalldatetime) + 7) >= (CAST(GETDATE() AS smalldatetime)))">Last Week</asp:ListItem> <asp:ListItem Value="((CAST(CallLog.RecvdDate AS smalldatetime) + 30) >= (CAST(GETDATE() AS smalldatetime)))">Last 30 Days</asp:ListItem> <asp:ListItem Value="((CAST(CallLog.RecvdDate AS smalldatetime) + 120) >= (CAST(GETDATE() AS smalldatetime)))">Last 120 Days</asp:ListItem> </asp:RadioButtonList></td> <td style="height: 47px; text-align: center"> <asp:DropDownList ID="DropDownList2" AutoPostBack="true" runat="server"> <asp:ListItem Selected="True" Value="Open">Open</asp:ListItem> <asp:ListItem Value="Closed">Closed</asp:ListItem> </asp:DropDownList></td> </tr> </table> <br /> <p style="text-align: center"><strong>Displaying All Open Tickets</strong><br /></p> <asp:SqlDataSource ID="SqlDataSource2" runat="server" SelectCommand="SELECT DISTINCT [locationname] FROM [profile]" ConnectionString="<%$ ConnectionStrings:Heat %>" /> <table width="100%"> <tr width="100%"> <td valign="top" width="100%"> <asp:GridView ID="GridView1" AllowSorting="True" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="CallID" AutoGenerateColumns="False" Font-Size="Smaller" Width="100%" > <Columns> <asp:CommandField /> <asp:BoundField DataField="CallID" HeaderText="Call ID" ReadOnly="True" SortExpression="CallID" /> <asp:BoundField DataField="CustID" HeaderText="Customer ID" ReadOnly="True" SortExpression="CustID" /> <asp:BoundField DataField="CallType" HeaderText="Call Type" ReadOnly="True" SortExpression="CallType" /> <asp:BoundField DataField="Priority" HeaderText="Priority" ReadOnly="True" SortExpression="Priority" /> <asp:BoundField DataField="Cause" HeaderText="Cause" ReadOnly="True" SortExpression="Cause" /> <asp:BoundField DataField="CallDesc" HeaderText="Call Description" ReadOnly="True" SortExpression="CallDesc" > <ItemStyle Width=40% /> </asp:BoundField> <asp:BoundField DataField="RecvdBy" HeaderText="Received By" ReadOnly="True" SortExpression="RecvdBy" /> <asp:BoundField DataField="RecvdDate" HeaderText="Call Date" ReadOnly="True" SortExpression="RecvdDate" > <ItemStyle Wrap="False" /> </asp:BoundField> <asp:BoundField DataField="RecvdTime" HeaderText="Call Time" ReadOnly="True" SortExpression="RecvdTime" > <ItemStyle Wrap="False" /> </asp:BoundField> </Columns> </asp:GridView>
Can someone please tell me what I am doing wrong... I have a simple webform that has contains a GridView and a DetailsView. Once the GridView is populated the user simply selects a record from the list. The DetailsView is them populated with all of the data from the selected record. The DetailsView is bound to a SQL DataSource/CustomerData. I have made sure that the DV control has the Edit/Update Command listed. The user can click the Edit button and successfully edit the fields but when the user clicks the "Update" button I am getting the following message. Updating is not supported by data source 'CustomerData' unless UpdateCommand is specified Why isnt the control handling the Update. Many Thanks!!!! T
I am trying to use the FileUpload control to save a filename to a SQL database. I'm using the OnUpdating event for my SQLDataSource to add the filename to the UpdateParameters. The OnUpdating event is firing but the FileName doesn't get added to the database. Any pointers as to why that might be would be very helpful. Here's the code. Thanks much. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="File Upload Testing" %> <script runat="server"> Sub Page_Load() Response.Write("UpdateParameters(0)=" & dsLabel.UpdateParameters(0).Name & "<br />") Response.Write("UpdateParameters(1)=" & dsLabel.UpdateParameters(1).Name & "<br />") End Sub
Sub dsLabel_OnUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Dim FileUploadControl As FileUpload = DetailsView1.FindControl("FileUpload1") Dim strFileName As String = FileUploadControl.FileName dsLabel.UpdateParameters(0).DefaultValue = strFileName dsLabel.UpdateParameters(1).DefaultValue = Request.QueryString("rgstnID") Response.Write("dsLabel.UpdateParameters(0).DefaultValue = " & dsLabel.UpdateParameters(0).DefaultValue & "<br />") Response.Write("dsLabel.UpdateParameters(1).DefaultValue = " & dsLabel.UpdateParameters(1).DefaultValue & "<br />") End Sub </script> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <h1>File Upload</h1> This page should allow me to use the FileUpload Control to save a filename to an MS SQL Database.<br /> <br /> <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="dsLabel" Height="50px" Width="125px"> <Fields> <asp:TemplateField HeaderText="File" SortExpression="labelName"> <EditItemTemplate> <asp:FileUpload ID="FileUpload1" runat="server" /> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("labelName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:CommandField ButtonType="Button" ShowEditButton="True" /> </Fields> </asp:DetailsView> <br /> <br /> <asp:SqlDataSource ID="dsLabel" runat="server" ConnectionString="Data Source=FILESERVER1;Initial Catalog=IM;Integrated Security=True" OnUpdating="dsLabel_OnUpdating" ProviderName="System.Data.SqlClient" SelectCommand="SELECT labelName FROM tblProductRegistration WHERE (registrationID = @registrationID)" UpdateCommand="UPDATE tblProductRegistration SET labelName = @LabelName WHERE (registrationID = @registrationID)"> <UpdateParameters> <asp:Parameter ConvertEmptyStringToNull="True" Name="LabelName" /> <asp:QueryStringParameter Type="Int64" Name="registrationID" QueryStringField="rgstnID" /> </UpdateParameters> <SelectParameters> <asp:QueryStringParameter Name="registrationID" QueryStringField="rgstnID" /> </SelectParameters> </asp:SqlDataSource> </asp:Content>
Does SQL 6.5 integrate with visual source safe to provide revision control of your stored procedures and queries? If not, is there a product for controlling revision control on a SQL 6.5 server?
Is anybody aware of how I can audit redundant Stored Procedures [SP] out of a large application. I only have the SQL-Server (no Sourcesafe/CVS). I need to know when an SP was last accessed. There are over seven hundered SPs.
Is there any access control available for the DTS package? I want to create an a/c that can create/modify but cannot delete DTS. Is there any way I can do that?
I have trigger,that trigger having Procedure(This procedure is ment for sending mails),if we insert data we have to send mails about 2000 mails,this is goal,mail sending is OK but at that my application getting slow
hi all, i wonder if could accomplish this problem in sql..
say i wanna total sum up some qty in table 1, and the sum up qty appear in table 2.. means everytime qty in table 1 change, it will sum up automatically total qty in tbl1 :-
table 1 id qty 1 5 2 5 2 5 1 5
table 2 id SumQty 1 10 2 10
note: table 2 will sum up automatically, isit possible to do this?
