.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Miss lines when converting data from TXT to sql

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
I have several txts files in a folder and I am trying to convert those into my sql database.  There is no error message during the conversion however, it misses converting some lines in my txt. It was showing that the total line number of the txt file was 8644 however after inserting into database, only 8631 shows up. I am so confused how can this happen? For example,   [Flat File Source [1]] Information: The total number of data rows processed for file "E:\My_Txts\20093347.txt" is 8742.  [Flat File Source [1]] Information: The processing of file "E:\My_Txts\20093347.txt" has ended.  and then it shows [DTS.Pipeline] Information: "component "OLE DB Destination" (9)" wrote 8730 rows. 

View Complete Post

More Related Resource Links

Error converting data type varchar to numeric.


I create a form to record data using ASP.NET and SQL database...
when i run the project show error "Error converting data type varchar to numeric"

what's the problem actually.i try to change in datatype in database..
but still showing error...


Conversion Failed when Converting the nvarchar value 'N' to data type int.

Hi.. I am facing a strange situation. when i executing the following query it is returning the error SELECT  s.row_id as shipment,    SUM(CAST(la5.attr_value AS INT)) AS Cases     FROM    shipment s WITH(NOLOCK)    INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id    INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id    INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND          s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local     INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3)  WHERE s.spare2 = 'RELEASED'       AND shift.shift_desc = 'c'   AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000')    AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000')    AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000') GROUP BY s.row_id  Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'N' to data type int. The column  type of attr_value of lot_attr table is a USER D

Error converting data type nvarchar to numeric.

i create a form using form view tools in asp.net..when i click insert command it shows error..this is code which i use for InsertCommand:<asp:SqlDataSource ID="SqlDataSourceFormView" runat="server"         ConnectionString="<%$ ConnectionStrings:AED_PanasonicConnectionString2 %>"         SelectCommand="SELECT * FROM [Debtor]"        InsertCommand="INSERT INTO Debtor (CompanyName, Desc2, RegisterNo, Address1, Address2, Address3, Address4, PostCode)VALUES (@CompanyName, @Desc2, @RegisterNo, @Address1, @Address2, @Address3, @Address4, @PostCode)">        </asp:SqlDataSource>Error:-Error converting data type nvarchar to numeric.where i have to make changes?

Arithmetic overflow error converting expression to data type smallint

Can anyone tell me what exactly this means? Arithmetic overflow error converting expression to data type smallint. 

converting datatable data to arraylist

hi friends pl tell me how  to convert datatable data to arraylistthanks in advance

Error converting data type nvarchar to datetime.

hi all, I simply explain my application.plz try to understand. In my applicationi want to generate one complaint id.one customer may give two or more complaints at a time.suppose,he give two or more pnr numbers and their status and prioirty may differs.for this complaint i need to generate one common complaint id.how it is possible..?with some constant rows. am getting error in Class File near CLUser_Logged_DTClass File using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Web.Configuration; /// <summary> /// Summary description for BalComplaints /// </summary> public class BalComplaints { public BalComplaints() { // // TODO: Add constructor logic here // } int SNo, Complaint_Type_ID, Caller_Type_ID, No_of_Articles, Article_ID, CLPriority_ID, Status_ID, Origin_ID, Destination_ID; public int SNo1 { get { return SNo; } set { SNo = value; } } public int Caller_Type_ID1 { get { return Caller_Type_ID; } set { Caller_Type_ID = value; } } public int Destination_ID1 { get { return Destination_ID; } set { Destination

Error converting data type varchar to numeric



I am trying to execute below query.



CAST('123,456,789.00' AS DECIMAL(11,2))

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'Cash' to


Line 64:         CMD = New SqlCommand(SQL, CON)
Line 65:         CMD.Parameters.Add("@payment", SqlDbType.VarChar, 50).Value = payment.Text.Trim
Line 66:         CMD.ExecuteNonQuery()
Line 67:
Line 68:

Hi All,

I'm having the above mentioned error message.

Please help.






SSIS converting Blank data to '30-DEC-99'

I am importing data from a flat file using SSIS 2005. Everything runs fine but I see blank values in source date columns getting converted to '30-DEC-99' in oracle.



Openquery - truncation problem (converting data from Oracle 10g to SQL 2005)


I am having problems with data truncating when inserting into a SQL 2005 db from Oracle 10g- using openquery and a linked server.   For instance, if I run something like:

insert dbo.CWDocumentStaging
select * from openquery(LK_Snomass,'select RSFORM.REPRESENTATION as REPRESENTATION

The results of the blob data are only 202 hexademical characters in length.  And then when I convert it to varchar, the results are cut-off.  For instance, on a particular test record that I converted, here is what I have in the SQL database (using):

select convert(varchar(max), DOCUMENT_TEXT), DOCUMENT_TEXT from dbo.CWDocumentStaging

I get the following:

for Document_Text, I get the following:


then for convert(varchar(max), DOCUMENT_TEXT):

this old man, he played one, he played knick-knack on my thumb with a knick-knack paddy whack give a

If I insert the data running the query against the linked server directly, the data does not truncate

Error converting data type nvarchar to numeric in Store Procedure execution



When I execute a Store Procedure via 'EXEC sp_XXX @p1, @p2' I'm getting this error 'Error converting data type nvarchar to numeric'.

After diggin into the TSQL code I have found that there's a field (type: nvarchar(200)) breaking the execution and resulting on the error.

If I query the table and the specified field with the same parameters as the Store Procedure the results are ok! If I execute the same code as the Store Procedure with the same parameters values the results are ok. The error only happens when executing the Store Procedure.

Any idea ?

Thanks in advance,

Gustavo Brian


If I cast the field to a smaller size value, as nvarchar(80), it works, but of course with data truncation.


"IN" operator and error converting data type varchar to numeric



I hope someone can help me figure this out please...

I have this query that returns some student data - specifically the student's names, and some classes they're enroled in. What I'm trying to do is return multiple rows using the LIKE operator.

However when I run it and plug in the parameters I get the errror - "Error converting data type varchar to numeric". This error doesn't occur when there is only ONE studentId. Only when there are multiple studentIds. I know it's to do with the string of studentIds being passed, but I'm not sure how to fix it?

Should I be using varchar as the datatype to pass the studentIds? Or do I need to cast this as something else?

Any ideas, much appreciated.



	@StudentIds varchar(8000),
	@ProfileId numeric,
	@Fname varchar(50) OUTPUT,
	@Lname varchar(50) OUTPUT,
	@ClassName varchar (100) OUTPUT,
	@ClassName2 varchar (100) OUTPUT,
	@ClassName3 varchar (100) OUTPUT,
	@ClassName4 varchar (100) OUTPUT,
	@Class numeric OUTPUT,
	@Class2 numeric OUTPUT,
	@Class3 numeric OUTPUT,
	@Class4 numeric OUTPUT


@Fname = Fname, @Lname = Lname, @ClassName = C.ClassName, @ClassName2 = C2.ClassName, @ClassName3 = C3.ClassName, @ClassName4 = C4.ClassName,

Conversion failed when converting the varchar value 'YTD' to data type int.


I have a report that I need to show the four quarters plus a Year To Date (YTD). I am getting a Conversion failed when converting the varchar value 'YTD' to data type int error and need some help. See first code & output. I used this same for a 12 month & YTD report and it works fine - see second code and output- any help is appreciated

First code

DECLARE @BD Datetime
DECLARE @ED Datetime
DECLARE @RptMonth Datetime

SET @ID = '1038'
SET @BD = '01/01/2010'
SET @ED = '12/01/2010'
SET @RptMonth = '07/01/2010'

Datasource_ID int, Actual_YTD float

Error converting data type varchar to numeric in SQL SERVER

Hi frendz..
Demo Example is...

alter FUNCTION [dbo].[Return_Example] 
@a numeric(18,4),
@b numeric(18,4)
RETURNS numeric(18,4)
@division numeric(18,4),
@reminder numeric(18,4),
@Num2 varchar(100),
@c numeric(18,4)

set @division=floor(@a/@b)
set @reminder=@a%@b
----Here output will be a concatenated
set @Num2= cast((@division +'.'+ @reminder) as numeric(10,3)) 
set @c=cast(@Num2 as numeric(18,3))


Execute :

exec Return_Example 50 ,5

Converting raw RGBA data back into an image.


I have been using a programme called Momento (which is a photobook programme - see www.momento.com.au if relevant).  With out boring you with the details I have lost all of my original data and am unable to recover it.

The programme did store image cache files in my application data and these appear to be related to the original picutres.  These files have names like 0dfc15fead0908ed2176d0d46e165614 and dont have file extensions.  Some are quite large - 3-4 GB which is consistent with the original pics.  There is also a file of thumb cache and these are DAT files.  Neither open with Momento.  I have been in touch with Momento support and they are unable to assist me other than to tell me the image cache files are raw RGBA data.

Obviously what I need to know is can I convert these files back to some sort of image and if so how and/or what programme can I use?  Is there a techincal service that could do it for me for a fee?

Thanks in advance


How to output data from master detail tables into text file but on different lines.



I have a requirement to output data from two tables, a master table and a detail table, to a text file such that the output looks like the following:

master record 1

detail record  1 1

detail record   1 2

master record 2

detail record  2 1

master record 3

detail record 3 1

detail record 3 2

detail record 3 3



Is this possible in one query?  I am sufficiently familiar with the bcp utility to do the output from any of the tables separately but have some difficulty with doing it for the two tables and have the result structured as outlined above.



i m facing the Problem for this getting an error like this


pls clear my error


CREATE PROCEDURE [dbo] . [UserDepositDetails_SPIns]

-- Add the parameters for the stored procedure here ( @regid int ,


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend