Flat File Connection Manager does NOT fail

I use Flat File connection manager where I have defined the format of the file as "Ragged Right" (CR,LF Dos file) with no header rows. The columns are fixed width with Row Delimiter "{CR}{LF}".

The problem is when I process a file with incorrect format. The file execution does not throw an error. Instead it throws a warning " There is a partial row at the end of the file.".

How can I force the Flat file connection manager to FAIL if the format of the file is not matched exactly. I would think this would be the default behavior of Connection manager.

ie. If I have Connection manager setup for a dos file with 2 columns. Column 1 is 5 characters long, column 2 is 3 character long, and the end of line characters are CR, LF. However, if I send in file with 10 rows of 1 character and CR LF, the data flow works and processes these rows incorrectly. How can I force the task to fail if incoming file is not in defined format.

The "Ragged Right" format does not have any limitation on the size of the last column (delimited one), so if your file is missing some row delimiters the flat file parser will continue to look for them and probably swallow succeeding row(s).

You can restrict the size of your "ragged right" column by setting the OutputColumnWidth property of those columns on the Advanced page of the Flat File Connection Manager UI. That will make the flat file source fail if the truncation happens. If you would rather to redirect such rows, you can do that by setting "Redirect Row" for Truncation on the Error Output page of the Flat File Source UI, and then define your error flow.

HTH.|||Thanks. It worked.

FK constraint when FK row is there! Always takes 10 mins to fail!

I am developing a SQLServer/JSP/Java (Jrun4) web app that intermittently has a FK constraint that always hoses up the transaction for exactly 10 minutes.

The user can save a record from a form that inserts a few rows in the db. The second insert is dependent on the FK of the first insert. All the inserts are done in an entity bean that calls stored procs.

We've had this intermittant error where the inserts take exactly 10 minutes (every time!), then it fails with a FK constraint, yet out logging shows that it's using an existing foreign key. (the stored procs returns the FK)

We have a dev server and a test server, and this has yet to occur on the dev server! I feel like there must be something set up incorrectly.

Any ideas?

thanks in advance!

-DanFlagged -

Are any of the tables really large? Enforcing a FK constraint without indexes on large tables could cause it to take a really long time. 10 minutes seems extreme, but...

And do you know what the differences are between your dev and test server? Do they have the same constraints? Do they have the same data?|||And the same indexes and statistics?|||There isn't much data: under 100 rows, 10 column.

The problem only happens intermittently. In fact it hasn't happed in the last several hundered inserts (we've been testing it)

Originally posted by strader
Flagged -

Are any of the tables really large? Enforcing a FK constraint without indexes on large tables could cause it to take a really long time. 10 minutes seems extreme, but...

And do you know what the differences are between your dev and test server? Do they have the same constraints? Do they have the same data?|||Both servers are set up the same. The more I think about it- based on the infrequency of the problem it may just be a coincidence that it only happed on the one server.

I'm investigating a possible answer to the problem- I noticed an unhandled exception in the bean and I think it's possible the 1st insert worked, then some unhandled exception occured and rolled back the transaction, then the bean continuted to attempt to insert the child row- just a theory since this is so infrequent.

Originally posted by Paul Young
And the same indexes and statistics?|||Here's another possibility - if you're running simultaneous transactions from different threads or processes against that table, and if your java code or your interface (jdbc?) is hanging, causing that transaction to sit there indefinitely, it could be causing blocking or deadlocks on that small table. If it puts an exclusive lock on the table, and you're holding that transaction open, then all the other waiting processes will fail or timeout.

It might be happening on only one server vs. the other because you're stressing one more than the other.

If it happens again, try using SET LOCK_TIMEOUT to change it to a shorter time for that connection. The more I think about it, the more likely it seems this is what's happening, since it's timing out after exactly ten minutes each time.|||I wanted to try one more thing before I tried your suggestion- and it looks like I fixed the problem, but I'm not 100% sure why the transaction was failing. Here's what the code was doing:

When the user saves form data, the entity bean inserts rows into the database, and in the same transaction (I think) proceeds to update those rows. My guess is that once in a while sqlserver locks those rows so that the records cannot be re-read. My fix was to prevent the extraneous update from occuring. It was happening because I didn't know enough about Java beans when I wrote the bean code. (I only used the store method in the bean for UPDATES and I handled inserts manually, but the store is called automatically for all bean calls so I ended up inserting, then updating).

Our beans are container managed, so that their transactions begin when the bean is called and end when the bean returns. I thought that one could manipulate an uncommited row inserted within a transaction but I guess that's not ALWAYS the case because 1 out of 100 times it times out and fails.

I'd say the problem's solved since it's been a week since my fix was in and no more problems!

thanks for the help.

First few attempts to synchronize a merge subscribtion fail

Hello everybody!

I have a publisher running SQL 2005 and subscribers running SQL EXPRESS in a merge replication. The subscriptions are pull subscriptions using web synchronization. Every time I do a new release of the database schema on publisher and drop/recreate replication I seem to have a problem.

When a subscriber tries to synchronize first time after this (the datbase on subscriber is dropped/re-create prior to this syncronization) it always results in a error saying something about "unrecognized or invalid response" after sitting there for some time. Several subsequesnt tries give "another job for this subscriber is already running on publisher", and finaly after a few minutes it synchronizes correctly and from this point on everything works perfectly until the next release. This happens with every single subscriber.

As far as I understand, what is happening is that when the merge agent connects to the publisher from the subscriber via web replication the publisher kicks off a job to generate initial dynamic snapshot for this subscriber. This is taking long time. During this time the web request times out, which results in the first error I see. When I try to connect several mre times, the job is still running, which gets me the subsequesnt errors. And finally when creating of the initial dynamic snapshot is finished, I can run the synchronization successfully.

The question is: Is there anything I can do on the publisher in automated manner prior to the first synchroniztion that would help me top resolve this problem? My guess would be that if I run jobs

dyn_PUBLISHER-DATABASE-PUBLICATION-num__SUBSCRIBER_anothernum for each subscriber and wait until they've finished before starting sinchronization from the subscriber, it would solve my problem. How ever I'm not sure how to run these jobs in automateed manner (because their name may differ) and how can I make sure that all the jobs are finished.

There may be other way to achive what I want also, please let me know what you think.

EDIT:

Also using this method http://technet.microsoft.com/en-us/library/ms151260.aspx I guess underneath, it starts all the same jobs that I mentioned above, but then again, this is neither automatic nor there is an apparent way to tell when all theses job are finished. What I want to do is to run a script and be sure that once the script has finished all the snapshots are there and I can start synchronizing from the subscribers without fear of failure.

Anyone?

If anithing is not clear in my explanation above, I can clarify, just let me know what