How Can I Get The Actual Line Number From Inside Flatfile Sources For Each Record?

Jun 12, 2006

hi all,

maybe this is not a serious problem, but I tried for days to come toi a solution without success.

My Problem:

I have have several flatfile sources I need to import into a sql-server 2005 DB.

It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.

I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.

How can I achieve my goal? Please help me...

Thanks in advance .. Bernd

View 11 Replies

Reading Specific Line In Flatfile

Jun 14, 2007

Hi,

I have a flatfile source. I want to extract a specific row... let say row 2. how do i retrieve that row from the list of rows in my flat file?

thanks,

Cherriesh

View 1 Replies View Related

SQL 2012 :: Anyway To Examine Actual Code Inside DBREINDEX

Aug 25, 2014

Someone claimed that the query:

INSERT INTO [table1] SELECT * FROM [table1]

was a result of running DBCC DBREINDEX.I wasn't able to replicate this when I did a trace on my local machine, but SQL Monitor from Red-Gate shows that query happening for at least some of the tables that are being reindexed via a job. Is there anyway to examine the actual code inside DBREINDEX to see exactly what commands it may execute?

View 3 Replies View Related

Bulk Insert Truncate The Actual Line

Apr 29, 2008

Hi all,

I have a problem during the bulk insertion. SQL truncated each line of the file.

The format file is:
9.0
1
1 SQLCHAR 0 8000 "
" 1 if1 ""

Table:

CREATE TABLE #if1 (
[if1] [varchar] (8000) NULL
)

Query:
SET @sqlstr = 'BULK INSERT #if1 FROM ''' + @inputfilepath + '.if1'' WITH (FORMATFILE = ''' + @ifformat + '.fmt1'')'
EXEC(@SQLSTR)

Everything is fine. The only problem is the lines are truncated.

Any ideas?

View 1 Replies View Related

Any Limit On Number Of Characters For FLATFILE Connection ?

Jul 23, 2007

Any one knows for sure if there is any limit on the number of characters/letters that a FLATFILE connection manager can maximally have?

Is the following name (36 letters) valid ?

Code Snippet

<DTS:Property DTS:Name="ObjectName">Load Ready Output Connection Manager</DTS:Property>

View 2 Replies View Related

Need Actual Number Count?

Sep 12, 2014

I know I have had this before, but I can't remember what I did to fix it.I have a field that is varchar. I need to count the numbers up.

Example

Claim Count
37
6

When I do my query

Select Distinct count([Claim]) from Table

It comes up with 2.How do I have it recognize that I need the actual number count? I need it to be 43.

View 2 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

Under What Kind Of Circumstances,the LSN On The Page Is Higher Than The Actual LSN For This Log Record

Aug 16, 2007

I'm reading <inside sql server 2005torage engine> recently.

The author mentioned about " If the LSN on the page is equal to or higher than the actual LSN for this log record, SQL Server will skip the REDO operation" in chapter 5 and section 1.

As we all know,the transaction log will be written before the changes to the database are written.
So i think the the LSN on the page is equal to or lower than the actual LSN for this log record....

Any suggestions?

View 3 Replies View Related

SQL 2012 :: Difference Between Estimated And Actual Number Of Rows?

Oct 20, 2014

I have found execution plan with significant difference between actual and estimated number of rows (roughly actual/2=estimated) in non-clustered index seek.Statistics are updated.

View 9 Replies View Related

Package For Unknown Number Of Sources

Nov 21, 2007

Hi,

I need to write a SSIS package. The source are in a Windows folder where there are a variable number of text files. These text files may vary in number everyday, How can i write a package for variable number of sources.

Please respond.

Thanks,
Swapna

View 1 Replies View Related

How Can I Get Actual Operation Cost From Actual Execution Plan?

Jul 25, 2006

I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.

Thanks!

Henry

View 2 Replies View Related

Line Number In Transact SQL

Feb 16, 2004

Hi,

How do you generate line numbers in Transact SQL?

Example:

In Sybase, I could use this...
select number(*), customer_name from customer_table

number(*) customer_name
----------------------------------------------------
1 Customer Name 1
2 Customer Name 2
3 Customer Name 3

Thanks,
Geoff

View 2 Replies View Related

How Do We Locate Exactly The Line Number Of The Er

Sep 1, 2006

when we get something like this in QA:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.

I only have 3 lines inclusing only one insert. How do I know what value or keyword is wrong from the error message above.

Thanks

View 4 Replies View Related

Sum Up Number On The Time Line

Jul 22, 2007

Hello.
I have a query That look like that:

"SELECT COUNT(*),MONTH FROM TABLE GROUP BY MONTH"

The result is:
Jun 2
Feb 2
Mar 3
Apr 0
May 5

What I want to have is that every month will sum up the previous month and him self so in the end I will be able the create a graph wuth those numbers:

Jun 2
Feb 4
Mar 7
Apr 7
May 13

Any ideas on how to start?

View 3 Replies View Related

Finding A Line Number

Sep 7, 2006

I have a large query that I am trying to debug in query analyzer.However, the errors I get have no line number or reference to wherethey are failing. How can I find out what line in the query is causinga particular error message to appear?ThanksJohn IveySouth Pike School District

View 2 Replies View Related

Script Line Number

Jul 20, 2005

When you get a server error message referring to a line number in ascript, is there an easy way to jump to the correct line? It's noteasy to count down in a big script or when there is a lot of whitespace.In WordPerfect we had a linenum macro that allowed you to enter theline number and it would jump to the line using the same countingmethod as the interpreter that generated the error message. Is theresomething similar with TSQL, or another way?Kerry

View 2 Replies View Related

Sum Up Number On The Time Line

Jul 22, 2007

View 1 Replies View Related

Return The Line Number Of The Error

Feb 4, 2004

i have a huge stored proc which has about 8-9 cursors which move data from a few temp tables to the final tables...and also do lots of calculations in between..

anytime there is an error in the stored proc i have an error page which looks like this

**********************
Server Error in '/WebApplication3' Application.
--------------------------------------------------------------------------------

Error converting data type varchar to bigint. 139 pdiscnum 37 3429 4978 139 139 4979 50.93 189.93 4980 -189.93 0 4981 139 139 4982 -139 0 4986 23 23 4987 0 23 4988 0 23 5121 139 162 5122 -328.93 -166.93 3430 4983 89 89 4984 -89 0 5096 89 89 5097 -178 -89 5135 89 0 5144 139 139 3431 4989 89 89 4990 -89 0 5100 89 89 5101 -178 -89 3432 4991 139 139 4992 4.32 143.32 4993 -143.32 0 5102 139 139 5103 -278 -139 3433 4994 139 139 4995 50.93 189.93 4996 8.64 198.57 4997 -198.57 0 5003 0 0 5123 139 139 5124 -328.93 -189.93 3434 4998 59 59 4999 -59 0 5000 59 59 5001 -59 0 5002 0 0 5041 19 19 5042 0 19 5043 -2.39 16.61 5044 -16.61 0 5045 19 19 5046 -2.39 16.61 5047 -16.61 0 5056 0 0 5084 39 39 5085 -5.85 33.15 5086 -49.76 -16.61 5104 59 42.39 5105 .........

************************************************

a very lengthy page...the numbers are prbly due to the print stmts of the results of some calculations...
but my q is..is there any way to return the xact line number where the error occured in the stored proc instead of these infinite list of numbers...its taking pretty long time to go through the entire stored proc to isolate the error...

thanks

dinakar

View 4 Replies View Related

Use Trigger To Number Each Order Line

Jan 24, 2006

I am wanting to set up a trigger on our SQL server 2000 to create a sequential order line number in the OrderDetails table for OrderID.

I will be inserting data from an OrderLinesAdd table into the OrderLines table but want the LineNumber field to be incremented by 1 for each group of orders grouped by OrderID that I insert into the table eg.

OrderID LineNumber
1 - 1
1 - 2
1 - 3
2 - 1
2 - 2
3 - 1
3 - 2

I have just started using SQL so am not sure how I go about writing the code for the trigger ? It would seem like I will need to use the Max function in the code.

Thanking you in advance.

View 3 Replies View Related

Getting Line Number Sorted Out In Matrix

Dec 27, 2007

Hi there,

I have a matrix report that shows a certain columns and rows. I need a row counter to show me the amount of rows in that matrix but it doesn't count properly by using the expression: =Int(RowNumber("matrix1") -1)
I actually don't have any idea on what this expression should be to fix the row count...to illustrate what I get in the report:

______________________________________________________________________________
Chkp | Row | Serial No | Call of Date |Colour| Pillar | AV | Sport | USA | SRA | TAPE |
2019 | 1 | 7006892 | 2003/02/11 | 3644 | 78222 | 3902 | 9182 | | | |
| 3 | 7000123 | 2003/02/11 | 3299 | 17283 | | 9238 | 2793 | | |
| 5 | 7302031 | 2004/01/11 | 3902 | 28932 | | 3233 | 2332 | | |
| 7 | 7273211 | 2004/02/08 | 6727 | 39232 | 8228 | 3293 | | | |
| 8 | 7382728 | 2004/11/11 | 7822 | 32342 | 3902 | | | | |
| 13 | 7342934 | 2004/12/03 | 8323 | 23422 | 2031 | 9212 | 2934 | 2934 | 2982 |
| 18 | 7329491 | 2004/13/02 | 8291 | 92423 | 7922 | 0231 | 2342 | 2355 | 2223 |
| 22 | 7328438 | 2004/12/22 | 8399 | 92311 | | 0289 | 8392 | | 3982 |

As you can see above, the row count numbers are not lining up at all, it starts at 1, then jumps 2 every count then all of a sudden jumps 5 per line...a pattern that is interesting but difficult to understand. The unique value in this matrix is that serial number but even though i group it by the serial number, it still gives me the funny Row count values.

What would be the approach to solving this?

Regards
Mike

View 3 Replies View Related

Reporting Services :: Add Horizontal Line After Each Record?

Apr 21, 2015

I have 10 records seperated by ID. I m using List in SSRS to show data on report. I need to add horizontal line after first record, then after second record till 10 th record. How do we add this line?

View 4 Replies View Related

There Was An Error Parsing The Query. [ Token Line Number = 1,Token Line Offset = 43,Token In Error = C]

Jul 27, 2007

Hello all
Trying to delete some data from a SSCE (2005) DB produces the exception:
SqlCeException
There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C]
Here is the code I am using

string dsc = Application.StartupPath + "\FCDB07.sdf";

conn = new SqlCeConnection("DataSource = " + dsc);

conn.Open();

cmd = conn.CreateCommand();

cmd.CommandText = "DELETE FROM DataContainer WHERE FileName =" + dgContainers[0, SelRowIndex].Value.ToString();

cmd.ExecuteNonQuery(); //There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C ]

conn.Close();

Any Idea on What causes this?

TIA
Trophus

View 3 Replies View Related

Error Parsing Query: [ Token Line Number = 1,Token Line Offset = 83,Token In Error = 5 ]

Nov 23, 2007

Hey all-

I'm trying to insert some values into an SQL Compact database on a WM6 device but there is something apparently wrong with my SQL statement...

The program is going to allow users to schedule an SMS message to be sent at a certain date and time. I'm using a database to keep track of the scheduled SMS messages. The database has 3 rows: phone number, message, and the date/time to be sent.

Here is the relevent code:

private void scheduleMenu_Click(object sender, EventArgs e)

{

//connect to DB and do our scheduling magic

string message = messageBox.Text; //should rename messageBox...

string phoneNum = phoneNumBox.Text;

string dataBase = @"Program FilesSMS_Scheduler2SMSDatabase.sdf";

//SqlCeEngine eng = new SqlCeEngine(dataBase);

SqlCeConnection conn = new SqlCeConnection("Data Source=" + dataBase);

conn.Open();

//insert phone number, message text, and date/time into DB
string cmd = "INSERT INTO Scheduler(phoneNum, message, date) VALUES("+ phoneNum + ", "+ message + ", "+ dateTimePicker1.Value +")";

SqlCeCommand cmdPhone = new SqlCeCommand(cmd,conn);

cmdPhone.ExecuteNonQuery(); //error occures here...

messageBox.Text = "";

MessageBox.Show("Message Scheduled!");

}

I'm guessing it doesn't like how I am trying to get the data from the different text boxes and the DateTimePicker to go inside the SQL command. Does anyone have any ideas on how to fix my SQL command or how to get data from a textbox and DateTimePicker to be inserted into a database a different way?

View 3 Replies View Related

Preconfigure Port Number While Command Line Installing

Dec 1, 2006

Hi,

I want set a specific port number of SQL Server(express) during installation not by Configuration Manager after install.
Is it possible?

View 1 Replies View Related

Generating Sequenced Line Numbers For Each Invoice Number

May 8, 2008

Can you help me with SQL issue I��m stuck on?
I wish to take source data that looks like this:

Invoic_num

Line_num

6658

0

6658

2

6658

8

7721

2

7721

3

And rebuild the line numbers like this:

Invoic_num

Line_num

6658

1

6658

2

6658

3

7721

1

7721

2

This seems completely impossible to me. So I was thinking that maybe a second procedure using update could be run against the table after the initial build.

View 10 Replies View Related

Send Mail Task Error - Limit To Number Of Chars In To Line?

Mar 22, 2006

Hello everyone,
I'm having an issue with dynamically setting the Send Mail task's to field to decent sized recipient list(approx 15 email addys). The strange thing is that this same package works fine if I'm using a list of say 4 email addys. I have double checked everything I can think of....list is seperated by semicolons, each address looks good....and have ran script tasks to check my variables along the path of the package but so far have not found the reason. The error I get is below:

"The specified string is not in the form required for an e-mail address"

Also of note, these addresses are being populated from a table, that is then setting the To string of the send mail task via a seperate variable...that variable being populated from a foreach loop. Again, all of this works fine with a smaller recipient list. Any ideas?

The only thing I can think of right now is to run the send mail task within the foreach loop...but then I'll be sending a seperate email for each address and would like to avoid that if possible.

Thanks,
Adrian

View 6 Replies View Related

Get Record Number

Sep 22, 2006

I have a table containing 1000 records. I do have a primary key associated with the table. But I want to get the Record Number of each record. Is there a way to get it. That means for first record it should be 1 and so on and so forth.

View 5 Replies View Related

Want To Get The Order Number Of Each Record

Dec 22, 2006

I want to retrieve data from SQL server database table I also want to get the order number of each record also For example consider the following table I want to get one (1) associated with A, Two (2) associated with B Three (3) associated with E Name Mark A 10 B 9 E 10 How can I do this?
I want to retrieve data from SQL server database table

I want to get the order number of each record also

For example   consider the following table

I want to get one

Name          Mark
A                  10
B                   9
E                 10

(1) associated with A,

Two (2) associated with B
Three (3) associated with E

How can I do this?
Please help me

hopefully
Sujithukvl

View 2 Replies View Related

Query Using The Record Number

May 7, 2004

happy friday...
my table has 200,000 + records and I want to see the particular record which I think it is causing the problem.
How could i query 195,203rd record?
thank you, yanoroo

View 8 Replies View Related

Trigger & Record Number

Oct 16, 2007

Hello,

I have a table with a field that contains the record number. This field is an identity column that is needed to be compatible with old Clipper programs. I've to ensure continuity in the numbering. So I have to update those numbers each time one or several records are deleted. I was thinking of using a trigger but I'm quite novice in this domain and don't know how to do it. Does anyone have a solution for this problem ?

Thank you in advance.

Regards,

mathmax

View 20 Replies View Related

Get The Number Of Days It Has Been Since A Record Was Inserted

May 22, 2007

Hiwhen inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.eg: 3 daysif the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours. e.g. 22 hrsi dont want hours to be displayed if the days is >= 1.please can anyone guide me with this?thanks!

View 7 Replies View Related

Get Only One Record Out Of X Number Of Records Per User ID

Oct 29, 2013

I need to create a query that will select only one record out of x number of record per user id.

Sample Data
User ID | Date |Status
001 |10/1/2013 |01
001 |10/2/2013 |A1
002 |1/1/2013 |BB
003 |5/8/2013 |EE
003 |4/2/2013 |BB

Expected Result
User ID | Date |Status
001 | 10/2/2013 |A1
002 | 1/1/2013 |BB
003 | 5/8/2013 |EE

View 3 Replies View Related

Loop Through Record Set And Assign Number

Nov 7, 2013

I am new to SQL Server (coming from Oracle background) and have a large table I need to loop thru and assign a number 1 thru 5 on each record.

below is an example of how I would do it in Oracle.

declare
num number := 1;
cursor c_rec is SELECT rowid, t.* FROM temp t order by t.column;
begin
for d_rec in c_rec
loop
update temp set column = to_char(num)

[Code] ....

View 7 Replies View Related