SSIS Crashes While Validating Data Flows

Jun 26, 2006

Hi all,

I'm stuck here when I try to open an existing package which contains several data flows, SSIS tries to validate each data flow and after a while a Visual Studio error message pops up and I can't do anything.

 The error message says : "Unable to cast COM object of type 'System._ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSObject90'. This operation failed because the QueryInterface call on Com component for the interface with IID '...GUID...' failed due to the following error : The application called an interface that was marshalled for a different thread. (Exception from HResult: RPC_E_WRONG_THREAD)"

Did anyone has seen this error message ?

Any help will be appreciated.

Sébastien

View 12 Replies


ADVERTISEMENT

SSIS Validating Data

Jul 4, 2006

Hi there,

I have a Problem with my SSIS and it is that when you open the package it says "Validating <Data Flow>" and it takes a loooooong time to finaly open ( by long I mean like 3 or 4 hours to just for open!!!). In 3 of the DF, I have conections to a Unisys server and in exactly in this DF is where it takes its time. This Unisys system its kind a slow but not that slow. I am running on a server that have 4 G and 8 processors, so it cant be the machine.



my question is, How can I make it validate faster ? I have tried to work Offline but when I run the package I need to be online.





Any Ideas? Am I doing anything wrong here??



thanks!

View 5 Replies View Related

Multiple Data Flows

May 3, 2007

Easy: read a SQL table with 500 fields, transform, write to flat file using SSIS.

But, I have hundreds of transformations to define using Lookups and Aggregates, Derived Column transformations. I wan to group the data flow transformations in usable (reasonable size) groups (packages, containers, subroutines, whatever you want to call it).

I cannot figure out a simple easy way of doing this most "simple" obvious thing.

Am I the only one on the planet who needs to do this?

Thx.

Newbie.

View 7 Replies View Related

Transaction With Many Data Flows

Feb 29, 2008

Good Afternoon,
I have a package with reads data from a text file and persist that data in a table "X" on sql server. I have one dataflow wich does this operation. In the sequence, i have this dataflow connected to the next dataflow, wich read the data from the table "X" and do some joins with another tables and persist that new data on a table "Y". The first problem, the table "Y" have a foreign key to table "X" and i have to do this operation with transaction, the package give problem, it's something like "Cannot enlist this connection on distributed transaction...", but i have many packages, wich I exec with transaction and runs sucessfully.
If i turn off the transaction, runs perfectly, when i put the transaction,it fails.

Sorry my english...

Thanks

View 5 Replies View Related

Tie Up A Set Of Data Flows In A Transaction

Jan 2, 2008

Hi,

I've 6 data flow tasks in my package. I need to put all of these dataflows into a transaction and rollback if any one of the task fails. I dont want to use MSDTC.

Can anyone help?

Thanks and Regards,
Subha

View 17 Replies View Related

Join Three Data Sets From Different Data Flows Into One Txt File

Mar 9, 2008

Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file.
Let's explain a little more:


I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.

Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.

What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?

Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows?
Thanks for your help!

View 7 Replies View Related

One Data Flow Task And Multiple Data Flows

Jul 26, 2007

I have a data flow task which has around 5 data flows (like the 2nd diagram shown here). These 5 simple flows with just a row count transformation in between. Now, I want to fail the entire task immediately even if one of the data flows failed. Right now if one flow fails the remaining flows fails after a long time, not immediately. How can I make it fails immediately.

The other I would like to do is Can I place these 5 data flows in a transaction, so that if one data flow fails, others data flows also roll backs? ( I assume its not possible)

Thanks

View 1 Replies View Related

Data Flows And Unique Columns.

Feb 9, 2007

Perhaps one too many 2000 DTS packages have permanently damaged my ability to think clearly - however, I've find myself very frustrated attempting to create a SSIS Data Flow which replaces a very simple 2000 DTS package.

Take data from table1 in database1, put it in table2 in database2. Table2 in Database2 has an additional column as part of the primary key - so I need to add an arbitrary unique value in each row as it's inserted. Previously, I did this in the transformation script through a variable I incremented.

What's the recommend method to do this now - since row level processing of variables seem to be a no-no?

View 5 Replies View Related

DTC Error With Concurrent Data Flows

Feb 6, 2006

I have a package that has several data flows that run concurrently after some initial tasks and an initial data flow. I want transactions on each of the data flows and have set the transaction option to Required on the data flows (not on the package itself). I am also using checkpoint restart on the package. A couple things are happening.

1) the first data flow is successful and that releases the several that are waiting. Some of these complete OK but inevitably one or two will fail. The failing data flows will be different from run to run, sometimes one and sometimes two will fail. The error says:

Error: 0xC0202009 at Provider_NF_Code, Delete Provider_NF_Code [130]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".

My hunch is that DTC is getting the transactions mixed up. I think it is committing one just after another data flow has already started work expecting the transaction to still be active. That would explain why the failing data flows are random. Plus, if I set the MaxConcurrentExecutables to 1 the entire package is successful. BUT, why have concurrent tasks if you can't run them concurrently.

2) when the package fails with the DTC problem I restart it with the checkpoint file. I was expecting the package to restart with the failed data flows. Instead, it restarts with the initial data flow (that all the other flows wait for in the package). This data flow has always been successful. It's as if the transactions I have put on the individual data flows are actually placed on a single virtual container that all of them are in, and when down stream data flows fail the entire data flow chain is rolled back and set to restart.

How can I get multiple concurrent data flows to run with transactions?

Why are successful data flows being restarted? Can I get just the failed tasks to restart?

-Gordy

View 5 Replies View Related

Sharing Data Flows Between Projects

Nov 29, 2007

We receive data files from different external customers, and these files have identical layouts.

I'm planning to set up a package for each customer. Each package will contain a flat file source -> OLEDB transformation dataflow, (followed by other customer-specific data flows).

What I'd like to do is just create this dataflow once, parameterising the flat file and table names. Is it possible to include this dataflow in each customer package so that if the flat file layout changes, I can just modify the connection managers in the one place, and then recompile each package to pick up the changes?

Any advice appreciated.

View 8 Replies View Related

Passing Constants In Data Flows

Jan 13, 2006

Hi

Does anyone know what would be the best technique to use for passing constants into data flows shapes?

For example if I had a lookup that required a static value to be passed into it as part of a concatenated key etc...

Cheers

Al

View 1 Replies View Related

Validating Source Schema Using SSIS

Sep 23, 2007

Greetings!!

I have a MsAccess db containing a table called Employees which i am transforming to a staging table in Sql server 2005. Everything is working fine. I am using Foreach File enumerator and uploading the files one by one.However I now plan to validate the schema of MsAccess before uploading it. For eg: My employee table in msaccess is as follows :




Code Snippet
Employees
empId int,
empName varchar(60),
empAge int




Since the files come from different vendor, while looping, i want to perform a check if the empid or empAge are not of type long or string etc. If they are of type smallint,i have no problem.

However if they are larger datatypes than the the ones kept in Sql server, then the file needs to be logged in the db with the reason and moved to the error folder. In short, if the datatypes in access tables are smaller than those in Sqlserver, allow it, otherwise reject it. THe schema of Sqlserver table is same as of that of Employees in msaccess.

I want to compare the schema of the incoming access tbl fields with my desired schema and all mdb's having data types that are higher or incompatible with the desired schema should be moved to the error.

How do I do it.

Thanks ,
Ron


View 1 Replies View Related

SSIS Packages Validating Before Execute

Feb 18, 2008

Hi

I have an SSIS package that wont run whne i try to run it in one go, but if i run it step by step it will succeed
as the reason its failing is in the 2nd step it references a table that doesn't exist (its created in the 1st step.

So at the start package is trying to validate, but it won't as the table is created in step 1.

So this is why it works if i execute this step by step, is there a way of turning off the pre run validation
on SSIS scripts ?

Thanks

View 1 Replies View Related

Integration Services :: Custom Data Flows

Nov 20, 2015

So I have to make a fairly dynamic Data flow. I will get the most of the configuration from a database table. I will look up the name of the procedure to run as a source (I can use expressions or a script component source for this), I will lookup columns names from a database table.I can use expressions (maybe) or a destination script component for the destination including the destination table name and column names, these will be looked up in a database table.What I am not sure is how I will do the mapping.  How can I make this dynamic?  The logic for mapping will be in the database as well.  Could I create a custom dataflow all in one script?  A source, destination and mappings all in one script?  Is there an example of this out there.my task ios to make the data flow completely dynamic.all config info would be kept in a SQL Server database.A complete custom script component dataflow task.

View 3 Replies View Related

Passing Data Between Multiple Data Flows

Nov 1, 2005

OK, it's the first of the month...that must mean it's time for another dumb question!

View 10 Replies View Related

Possible Validation Problem With Flat File Between Two Data Flows In A Package

Apr 17, 2007

I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:

Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:Temp.txt".

Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.

I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?

Thank you.

View 6 Replies View Related

Package Failure - Multiple Synchronous Data Flows - File Name Not Valid – /3GB /PAE

Apr 23, 2008

I have a package with 10 synchronous dataflows, which, combined, load about 300MB of flat file data to a database. This package would run successfully on 2 of our database servers, but would regularly fail on a third. The server on which it was failing is a 4 processor box with 16GB Ram with Windows Server 2003, SQL 2005, SSIS and SSRS installed - much more robust than one of the others that the package worked on. The SSIS error messages returned alternated between the following (with no apparent reason why one would show up rather than another, though the first was the most common):

"The file name "\Server1Folder1File1.txt" specified in the connection was not valid."

"The file name property is not valid. The file name is a device or contains invalid characters."

"An error occurred while initializing the flat file parser."

For the first error message, the error would report different connection managers and their associated file as invalid from run to run. All of the files across the 10 dataflows resided in the same network folder, and the package would read in and process a few of them before failing, so the problem was definitely not the connection string.

Searching the forums, etc. for these errors provided no useful information - given the real cause of the problem, these error messages are worse than unhelpful, they send you looking in the wrong direction. It was only when trying to track down another problem on the same server that I discovered the issue. When trying to copy database backups greater than 12GB over the network to this server, the operation would fail with an "Insufficient System Resources" message.

Some research led to the discovery that problem was caused by the /3GB switch in the boot.ini file of the server (don't let your Server team use that switch if you have 16GB of memory or more). Removing the switch and setting SQL to utilize AWE, fixed both the file copy problem AND the SSIS package failure problem. The SSIS package failed, not due to a bad connection string, but rather to insufficient server resources (read memory) to handle the simultaneous connections.

I hope this may help any others trying to track down this kind of SSIS package failure.

I will also provide here what I have gleaned about setting up Memory usage for SQL Server 2005 running on 32 bit Windows Server 2003 (with the caveat that I am no expert €“ corrections and additional information are welcome).

The following links got me started in my research (thanks to the folks who provided such useful information):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55191
http://articles.techrepublic.com.com/5100-10878_11-6091280.html
http://www.simple-talk.com/community/blogs/brian_donahue/archive/2007/09/30/37747.aspx
http://blogs.technet.com/askperf/archive/2007/03/23/memory-management-demystifying-3gb.aspx
http://www.modhul.com/2007/11/10/optimising-system-memory-for-sql-server-part-i/

Also, search BOL for:
Server Memory Options
Enabling Memory Support for Over 4 GB of Physical Memory
Enabling AWE Memory for SQL Server


Windows Server 2003 provides access to 4GB of virtual address space. By default, 2GB is assigned to the OS and 2GB to applications. This default can be change to 1GB for the OS and 3GB for applications by the use of the /3GB switch in the boot.ini file.

Physical memory over 4GB can be addressed by enabling Physical Addressing Extensions (PAE), which is done by setting the /PAE switch in the boot.ini file. This does not increase the systems virtual address space, rather it increases the size of the page table (which is maintained within the virtual address space), adding entries to reference the physical memory above 4GB.

It is important to note that these two switches are not interdependent (they do different things and you can turn each on or off regardless of the others status), though the combination of them has an impact on server performance and the maximum amount of physical memory which can be addressed.

The /3GB switch only impacts the allocation of the first 4GB of memory (virtual address space) between the OS and applications (default 50/50 % split, with switch on - 25% OS and 75% applications). The /PAE switch enables the system to reference/manage physical memory above 4GB, but does not alter the allocation percentages of the first 4GB of memory between the OS and applications. However, when PAE is enabled, the OS requires more memory within the first 4GB to manage the physical memory above 4GB (due to increased page table entries). With the /3GB switch, the OS has only 1GB of virtual address space, and only enough space to manage a total of 16GB of physical memory. If 32GB of physical memory is installed, 16GB of it will go to waste.

Address Windowing Extensions (AWE) is an API that allows an application to address more than the 2-3GB of memory that is available to applications within the virtual address space (first 4GB of memory). SQL Server can utilize AWE to take advantage of memory above the first 4GB that is made available via PAE, and can even reserve portions for its own use. I believe (though I can€™t remember where I got this bit) that SQL utilizes AWE memory only for the page cache (buffer pool €“ which seems to be a misnomer), and not for other operations.

To enable AWE, see the BOL references above.

The big question: what are the recommended settings for all of these? That all depends on what you have running on the server. You need to leave space for the OS, SQL Server and any other applications you have.

The hard and fast rules:
If you have more than 4GB of RAM, you must use the /PAE switch in order to take advantage of it.
If you have more than 16GB of RAM, you must NOT use the /3GB switch in order to take advantage of it.

Based on anecdotal evidence, I€™ve noticed the following generally recommended guidelines €“ assuming the server is dedicated to SQL.

Use of the /3GB switch seems to be a generally accepted practice if you have 8GB of RAM or less. For between 8 and 16GB, some say never use the /3GB switch, others say you can use it up to 12GB and still others up to 16GB. I interpret this to mean that it all depends on what types of loads are being placed on the server and that testing on individual servers will be required to determine whether or not to use the switch. Certainly that was my experience - the /3GB switch worked fine with 16GB RAM, until the server encountered a certain workload. For me, no more /3GB switch.

For setting SQL to use AWE, most seem to agree that it should be enabled if you have more than 4GB RAM. The setting of max server memory is more complicated. BOL seems to suggest (the €˜Server Memory Options€™ entry) a formula of Total Physical Memory minus 1-2GB for the operating system. Based on a desire to be a bit more conservative, I am now using the following formula:

max server memory = total physical memory

minus

4GB for the OS and application processes (since the AWE memory is utilized for page cache, not SQL processes)

minus

AWE memory required by other applications, including other instance of SQL Server


If anyone has additional insight, or a more refined equation, I could certainly benefit from it.

View 1 Replies View Related

Slowly Changing Dimension Task CRASHES SSIS

Apr 5, 2006

We have successfully built a SSIS ETL implementation for a data mart.  Most of our dimension loads are using the slowly changing dimensions task.  We successfully built every package and had 1 complete successful load.

We have made a modification to one of the tables that now has 16 mil records in it.  I open the dimension's package and it takes 30 sec - 1 minute to validate.  When I go to the Data Flow tab and attempt to open the Slowly Changing Dimension task, SSIS will freeze up.  I will get no response for hours.  I my testing so far SSIS has yet to come back.

My CPU usage is below 5%.  devenv.exe memory usage is at 113 Mb. My computer is using 400mb / 1Gb of memory.   The status bar at the bottom says "ready". I have let it sit for up to 2 hours with no results.  When I kill the process in the Windows Task Manager it ends instantly.

I have also tried deleting the task and adding a new Slowly Changing Dimension Task.  When I select the table from the drop down I get the same results.  I have noticed this with all my million + record tables that use the slowly changing dimension task but not the smaller tables.
I am also seeing the problem when I am in "Work Ofline" mode.

I am accessing a SQL 2005 server over a VPN connection.  I am connecting to the server using a Native OLE DB OLE DB for SQL Server connection.  My computer is running Windows 2000 SP4,  Intel Pentium M1.86 Ghz, 1 Gb of RAM.

Can a Slowly Changing Dimension Task be used on large tables?  I can understand the process taking a while to run but I do not understand why I can not edit the task.

 

View 3 Replies View Related

Validating Data In Sql Using C#

Nov 19, 2007

Hello All again,
I have another question, I would like to validate the data in a sql table before my code runs and inserts duplicate information. Can anyone help out with this via example?

View 2 Replies View Related

Validating Data Through Sql Server

Dec 21, 2007

hi guys, have a good day.

i have a table with a column named "cardRange", that column can only have integer values from 0 to 5.

i want the validation in the value that will be inserted in that column to be made in my sql server rather than in my programmed software.

Is there a way to validate (through a storedprocedure, or a trigger, or a column propertie) the values i want to add to my table??

In other wordsI just want my table to make sure that only a integer from 0 to 5 (or any other simple validation) can be added to the table.

Any help? thank you very much.

View 2 Replies View Related

Validating Data As Datetime Without Sql Errors

Jan 5, 2001

Has anyone figured out how to test if a string is a valid date without risking a sql conversion error?

I'd really like to avoid parsing the strings myself in an import procedure I am writing.

I can run an insert/select with the text being converted to datetime data, but if it hits text that won't convert correctly (such as '13-1-1999') it fails with a sql error. This could mess me up quite a bit.

Thanks in advance

View 2 Replies View Related

Filter Data From Multiple Tables Crashes App!

Jul 20, 2005

i didnt think my sql qeury was that complicated that it would crash my webapp. all im trying to do is filter data between two tables. heres my query<cfquery name="GetResults" datasource="#datasource#">SELECT *FROM Content, Content_SitesWHERE Content.ContentID <> Content_Sites.ContentIDORDER BY Content.ContentID DESC</cfquery>equals works, but when i try not equals, it all goes haywire. any ideas?TIA

View 3 Replies View Related

BIDS Crashes After Opening A Data Flow

Mar 11, 2008



I have a package with numerous data flows in it. (I know - this is not recommended as a best practice.) Anyway, one of the Data Flows just started giving me some trouble. When I opened that particular Data Flow, then BIDS would crash. I tried loading the project again, and opening that Data Flow task, and it just kept crashing. I rebooted - same deal.

The Data Flow was itself very simple. There was an OLE DB Source, and OLE DB Destination, a conditional split, a derived column, a data conversion and a Lookup. Pretty routine stuff. Out of the blue really, and long after I created it - it just starting crashing BIDS when I opened it.

My solution was to copy the Data Flow control flow component and paste it back into the same package. This worked perfectly fine, and didn't see any issues after. So, apparently something in the meta data became corrupt, and caused it to crash.

Anyway, just curious if others have seen this behavior, but also wanted to post this as a solution in case someone else has the same problem.

View 5 Replies View Related

Integration Services :: Validating Data Loaded From Flat File Into Table Points

Oct 16, 2015

In my SSIS package I have flat files as a source, I have to load numbers of flat files into SQL target table. I am using For each loop container for that. I am doing it correctly. My aim is to validate the source data from all angle before writing it into target sql table. I am using below points to validate the source data , if I found any bad data I am redirecting those data to error output.

To Checking

1. To check whether data type of column.
2. To check whether buisinesskey column null.

Is there any thing which I am missing to validate source data.

Screen shot for reference

View 10 Replies View Related

Visual Studio Crashes When Editing Data Flow Task

Nov 9, 2006

I used an evaluation version till last week.

Was working fine so I decided to buy a Standard License.

Installed during the weekend, I had many problems till I decided to reinstall completely Windows. I have installed SP1

Everything worked fine then, ONLY not one of my SSIS packages still works; I get following error when editing the data flow task

The thread 'Win32 Thread' (0x7e8) has exited with code 0 (0x0).

Unhandled exception at 0x54fc5e89 in devenv.exe: 0xC0000005: Access violation reading location 0x00000000.

Please help, this is a disaster otherwise





View 4 Replies View Related

SSAS Crashes - Mining Predictions For Large Data Sets

Sep 7, 2006

Hi all,

I am using SSAS 2005. The mining model works fine. But it crashes when I run the 'Mining Model Predictions' against large data sets.

I ran it against 5,000,000 records and it went fine.

But exactly same model failed for 5,100,000 records and beyound.

The message is 'Query Execution Failed' and then Visual Studio crashes.

Pl. let me know if anybody has the same experience or knows the solution.

Thanks,

Vikas

View 3 Replies View Related

Lookups And Their Error Flows

Nov 13, 2006

So I have three lookups in a row in my data flow. Basically they are doing data quality checks for me using a reference table.

I want to be able to take the error flows of the three lookups and merge them together (union all) so that I can insert the "errors" (or non matches) into a table.

Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.

What I would like to see is a lookup act more like a conditional statment where you have three outputs of a lookup table: match found, no match found, and error. Either that, or I'd like to be able to edit the names of the errorCode and errorColumn fields.

Am I missing something here, or do I need to just add an OLE destination for each lookup error flow when I only want one?  'Course the problem then is that I want to count the number of rows that are in "error" across all of the lookups.

View 4 Replies View Related

Is It Possible To Merge Control Flows?

May 15, 2008

I'm using a script task to split my control flow before performing different data flow tasks depending upon the result of the script.

The dataflow tasks populate a single dataset with data before entering a foreach loop where the data in the dataset is consumed.

The foreach loop is valid for both dataflow tasks but I can€™t get it to accept multiple inputs.

I could have 2 copies of the foreach loop, one for each branch, but this seems wasteful and also means I have to maintain the same chunk of code in 2 places.

Can I join control flows or is there a better way to achieve this?

Cheers.

View 6 Replies View Related

Validating 2 Table's Plz Help

Jul 30, 2004

I have one table called product 1 and the second table is called product 2 i have DirNo, ProdNo, UpdCode and BranchOffice as fields i whant to loop trought every record in table product 1 and check in table product 2 if the record is the same and exist i f it does go to next record, if not delete the record in product 1 and move to the next record and so on. could someone please help me. iam very new at this.
thanks

View 4 Replies View Related

Validating SP Inputs

Sep 23, 2007

This question is rather open ended. Basically, I'm wondering the different approaches people use when validating input into a stored procedure. The rest of my post just describes a rather simple approach I'm using, and some ideas I have, but I'm eager to know what others are doing. So, if you'd like to comment on this, feel free to do so without reading the following.





I often find myself calling a stored procedure, and needing to validate some of the inputs before proceeding with the rest of the tasks. Such as, making sure a matching record isn't already in the database before adding a new record. Ideally, this sort of validation will report back to the user interface immediately, so that the user can get real-time response to their form input, instead of the all-too-common approach of redirecting to an error page if something goes wrong in the database transaction.

It's also convenient, at times (though perhaps not efficient in all cases) to let your database perform business rules validation of inputs (let the user interface make sure data types are valid, and such), so that you duplicate less code in the event that the stored procedure is called in more than one context.

To address these issues, I've taken to making two stored procedures instead of one. Let the stored procedure be called AddRecord. I'd create that, as well as the procedure named AddRecord_Validate, which would take identical inputs as AddRecord, whenever possible. Nearly the first thing done in AddRecord would be to execute AddRecord_Validate. The user interface would also call AddRecord_Validate, and return any validation errors to the user interface.

This seems rather convenient to me, in many cases, but often it doesn't work as well as I prefer. It's not uncommon for me to end up performing the same queries in the _Validate SP as I do in the parent SP. For example, let's say I'm passing a parameter that I use to look up a record I plan to edit. In my validation, I query the database to verify that the record is found. But in the parent SP, I run the same query again in order to get the stored ID of the record I need to edit. This ends up duplicating queries, making the pair of procedures less efficient on the whole, as well as introducing the likelihood of bugs when code changes in one of the SPs, but not the other.

So I've been brainstorming other approaches. The first idea is to execute a single stored procedure, but when I want to run it in "validation" mode, I simply rollback the transaction. This would let me know if the stored procedure would have run with the specified input, but won't ultimately change anything. Unfortunately, I see some badness in this, such as the entire SP perhaps taking a lot longer to execute that simple validation would have, and side-effects such as incrementing Sequences, or locking the database, and basically just doing a whole lot more work during validation than needs to be done.

The next idea was to require that the user specify the "run mode" via a parameter, either 'validate' or 'run'. Then, all of the validation would be performed at the top of the stored procedure, and a simple IF statement would exit the SP before actually making any changes if it's run in 'validate' mode. Otherwise, it will continue, and do the real work. The only real downside I see to this is forcing the developer to deal with an extra parameter. And, perhaps, it's not really a "relational" approach.

So, what do the rest of you do?

View 5 Replies View Related

VALIDATING CONSTRAINTS

Apr 14, 2008



hello

i am using visual studio 2008 to create an inventory management system and i am having trouble checking for a constraint violation. i have a partnumber colum in the database that needs to be unique and i am using datasets / tableadapters to interact with the database. when a user adds a new part number to the database i need to make sure its not already there
i have tried to use
catch ce as constraintexception
debug.print ce.tostring
end try

but the program still crashes with

System.Data.ConstraintException was unhandled ( see full error below)


no matter where i put the try statement it says its unhandeled

this error triggers when when i leave the combo box (when its validated) i have even tried placing it here


Private Sub PartNumberComboBox_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles PartNumberComboBox.Validating

Try

Catch CE As ConstraintException

Debug.Print(CE.Message)

Catch EX As Exception

Debug.Print(EX.Message)

End Try



End Sub

if someone could point me in the right direction id greatley appriciate it

System.Data.ConstraintException was unhandled
Message="Column 'PartNumber' is constrained to be unique. Value '36LDVRRP' is already present."
Source="System.Data"
StackTrace:
at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action)
at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException)
at System.Data.DataTable.SetNewRecord(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean fireEvent)
at System.Data.DataRow.EndEdit()
at System.Data.DataRowView.EndEdit()
at System.Windows.Forms.CurrencyManager.EndCurrentEdit()
at System.Windows.Forms.CurrencyManager.ChangeRecordState(Int32 newPosition, Boolean validating, Boolean endCurrentEdit, Boolean firePositionChange, Boolean pullData)
at System.Windows.Forms.CurrencyManager.set_Position(Int32 value)
at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
at System.Windows.Forms.ComboBox.WmReflectCommand(Message& m)
at System.Windows.Forms.ComboBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at System.Windows.Forms.Control.SendMessage(Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Control.ReflectMessageInternal(IntPtr hWnd, Message& m)
at System.Windows.Forms.Control.WmCommand(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.GroupBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at System.Windows.Forms.NativeWindow.DefWndProc(Message& m)
at System.Windows.Forms.Control.DefWndProc(Message& m)
at System.Windows.Forms.Control.WmCommand(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ComboBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Inventory_System.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:


View 9 Replies View Related

Validating Imported Values

Jan 18, 2005

I am using the following SQl statement in a DTS vb file.
The data is coming from an Excel spreadsheet and being put into a SQl server table.

oCustomTask1.SourceSQLStatement = "select `Order No`,`Country`,`Desc`,`Amount` from `Sheet1$` WHERE `Order No` = 1 "


I want to validate the data being put into the table to ensure no duplicates records are entered UNLESS the record has changed in any way.
E.g. If record 1 had a column called "NumberOne" which changed to "Number1" change this information and move the original value to another table.

View 2 Replies View Related

Validating @ , And Length Of An Email

Feb 23, 2006

Im trying to perform an insert but with only valid emails that meet the following criteria:

Valid email : not Null, length > 6 with @ character

I know the is NOT NULL, but the other two im a bit confused on. :)

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved