Incorrect Data Is Inserted Into The SQL Table From OLEDB Command
I have an OLEDB command in a package that inserts the data into two tables.
When I run the package, the data is getting inserted as divided by 100 of original data for derived columns.
For example: Say col1 is my input column from flat file with value 1000. I am dividing it by 100 in Derived column and then inserting into the table.
So the value 100 should be inserted into the table. But it is not so, the value 1 is getting inserted.
Two Tables involved here have referential integrity constraints between them. SQL Script for the operation I am doing looks like the one just below
INSERT INTO PrimaryKeyTable(ID,
FirstName)
VALUES
(?,?)
If @@rowcount=1
BEGIN
DECLARE @MaxID as int
SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable --AutoIncremented column
INSERT INTO [ForeignKeyTable]
( [MaxID]
,[Cost]
,[MarkDownDollars]
,[VersionCode]
)
VALUES(@MaxID,?,?,'act')
END
But this script did not work in OLEDB commandL
So I wrote the below script for which I am facing the problem mentioned
INSERT INTO PrimaryKeyTable(ID,
FirstName)
VALUES
(?,?)
If @@rowcount=1
BEGIN
DECLARE @Cost AS money
SET @Cost=?
DECLARE @MarkDownDollars AS money
SET @MarkDownDollars=?
DECLARE @MaxID as int
SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable
INSERT INTO [ForeignKeyTable]
( [MaxID]
,[Cost]
,[MarkDownDollars]
,[VersionCode]
)
VALUES(@MaxID,@Cost,@MarkDownDollars,'act')
END
View Complete Forum Thread with Replies
Related Forum Messages:
Staging Data In ##Table In A OLEDB Command Task
Hi All, In one of my interfaces,i am trying to Stage data in a ##table from a Flat file,so that i can iterate through the data in for each loop afterwards. I have written a stored Proc to do this.But what i am observing is in the ##Table only the last Row will be persisted. The SP looks like this Code Snippet CREATE PROCEDURE USP_SVInsS093Data_V001 @DetailRECTYPE nvarchar(5), @StoreNumber int, @Product nvarchar(20), @Planogramref nvarchar(20), @DisplayGroup nvarchar(5), @ModSequence int, @Shelfnumber int, @Productposition int, @StartModnumber nvarchar(20) AS SET NOCOUNT ON BEGIN if object_id('tempdb..##S093_TempTable') is not null drop table ##S093_TempTable CREATE TABLE ##S093_TempTable( [DetailRECTYPE] [nvarchar](5) NULL, [StoreNumber] [int] NULL, [Product] [nvarchar](20) NULL, [Planogramref] [nvarchar](20) NULL, [DisplayGroup] [nvarchar](5) NULL, [ModSequence] [int] NULL, [Shelfnumber] [int] NULL, [Productposition] [int] NULL, [StartModnumber] [nvarchar](20) NULL ) INSERT INTO ##S093_TempTable ([DetailRECTYPE] ,[StoreNumber] ,[Product] ,[Planogramref] ,[DisplayGroup] ,[ModSequence] ,[Shelfnumber] ,[Productposition] ,[StartModnumber]) VALUES ( @DetailRECTYPE , @StoreNumber , @Product , @Planogramref , @DisplayGroup , @ModSequence, @Shelfnumber , @Productposition , @StartModnumber ) If i have a select query after the Data Flow task only the last row is returned. Now how to make the ##Table keep all the rows. Any help will appreciated. Thanks in Advance... Cheers Srikanth Katte
View Replies !
Issue With The Use Of OLEDB Command Task To Update Db2 Target Table
I am having a problem on updating data in DB2 target table. I followed BJ Custard's (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1058272&SiteID=1&mode=1)(thanks amillion!) post and configured OLEDB destination to insert data. But I have to also update or delete data from the target table based on flag from source. I tried using OLEDB command which uses the OLEDB connection created by following the steps posted in above link. Trail 1real requirement): When I used the SQL query: delete from table where Col1=? and Col2=? I am unable to map to the parameters. When I click refresh button after writing the query, I get "There is a data source column with no name. Each data source column must have a name." message. Added to before message, there are no parameters to map to. Trail 2: When I hard code the parameters : delete from table1 where Col1='abc' and Col2='xyz' no parameters will come up, so no mapping. So when I execute the mapping I get the following error: Error: 0xC0202009 at Load .....................................................: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00. Lookup on above error codes show those are related more to target Db2 database. I am sure some one might have used the OLEDB command task, not only just insert task. Any help is really appreciated.
View Replies !
SQL Transaction W/ Oledb Destination Vs. Oledb Command
I have been doing some tests with transactions using the method described at: http://blogs.conchango.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx My test package is set up as follows: For loop Begin Transaction in a SQL task Sequence Container Data flow task that bring in data from a table, adds a derived column and insert into a different table A script task that returns a failure to produce an error in the sequence container If sequence container is a success the execute SQL commit and if fails, rollback transaction The data flow task and script task are done in parallel, no constraints between them. The problem is that if I use an OLE DB destination to do the insert in the data flow task, the data gets inserted despite the rollback task being shown as executing. But if I use a OLE DB command to do the insert, the rollback works just fine. I looked at the package Jamie provided in his blog and he is using a OLE DB destination so I'm unsure about what I am doing wrong. Any ideas?
View Replies !
INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table
Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO
View Replies !
Duplicate Records Are Being Inserted With One Insert Command.
This is like the bug from hell. It is kind of hard to explain, soplease bear with me.Background Info: SQL Server 7.0, on an NT box, Active Server pageswith Javascript, using ADO objects.I'm inserting simple records into a table. But one insert command isplacing 2 or 3 records into the table. The 'extra' records, have thesame data as the previous insert incident, (except for the timestamp).Here is an example. Follow the values of the 'Search String' field:I inserted one record at a time, in the following order (And only oneinsert per item):airplanejetdogcatmousetigerAfter this, I should have had 6 records in the table. But, I endedup with 11!Here is what was recorded in the database:Vid DateTime Type ProductName SearchString NumResultscgcgGeorgeWeb3 Fri Sep 26 09:48:26 PDT 2003 i null airplane 112cgcgGeorgeWeb3 Fri Sep 26 09:49:37 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null cat 75cgcgGeorgeWeb3 Fri Sep 26 09:52:53 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null tiger 14cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64Look at the timestamps, and notice which ones are the same.I did one insert for 'dog' , but notice how 2 'jet' records wereinsertedat the same time. Then, when I inserted the 'cat' record, another'dog' record was inserted. I waited awhile, and inserted mouse, andonly the mouse was inserted. But soon after, I inserted 'tiger', and 2more mouse records were inserted.If I wait awhile between inserts, then no extra records are inserted.( Notice 'airplane', and the first 'mouse' entries. ) But if I insertrecords right after one another, then the second record insertion alsoinserts a record with data from the 1st insertion.Here is the complete function, in Javascript (The main code ofinterestmay start at the Query = "INSERT ... statement):----------------------------------------------------------------------//Write SearchTrack Record ------------------------------------Search.prototype.writeSearchTrackRec = function(){Response.Write ("<br>Calling function writeSearchTrack "); // fordebugvar Query;var vid;var type = "i"; // Type is imagevar Q = "', '";var datetime = "GETDATE()";//Get the Vid// First - try to get from the outVid var of Cookieinctry{vid = outVid;}catch(e){vid = Request.Cookies("CGIVid"); // Gets cookie id valuevid = ""+vid;if (vid == 'undefined' || vid == ""){vid = "ImageSearchNoVid";}}try{Query = "INSERT SearchTrack (Vid, Type, SearchString, DateTime,NumResults) ";Query += "VALUES ('"+vid+Q+type+Q+this.searchString+"',"+datetime+","+this.numResults+ ")";this.cmd.CommandText = Query;this.cmd.Execute();}catch(e){writeGenericErrLog("Insert SearchTrack failed", "Vid: "+vid+"- SearchString:: "+this.searchString+" - NumResults: "+this.numResults, e.description);}}//end-----------------------------------------------------------------I also wrote a non-object oriented function, and created the commandobject inside the function. But I had the same results.I know that the function is not getting called multiple timesbecause I print out a message each time it is called.This really stumps me. I'll really appreciate any help you canoffer.Thanks,George
View Replies !
Oledb Command
I am transfering large data. I use oledb command to insert and update as i need to make some modifications to incoming data.I do my modifications in the procedure. But the command does not insert as the data is huge at one shot. if i try to send small data it works fine Its shows warning(yellow color) How can i achive inserting huge data effeciently please help.
View Replies !
OLEDB Command In SSIS
I AM working on DW building and i m using SSIS.I haev problem with data transformation OLEDB command.I have written a query to clean data in OLEDB command box ,but it takes whole lot of time because at a time it slects 6000 rows from the source and put in to destination but i have 300000 rows to process.How can i increase the size.
View Replies !
Need ID After OLEDB Command (insert)
Hi, I'm stuck on the following thing: After a slowly changing dimension task I replaced the OLE DB Destination task by an OLE DB Command and created the insert manually. This because I need to work further on the dataset. So I do a union all between the output of the two OLE DB Commands (insert and update). Untill here no problem. But than, because I need an ID further on, I do a lookup in the table in which I just inserted and updated my data for the right ID's. When I run this project I get the error message "row yielded no match during lookup". I don't understand this beacause I just inserted the data and I've checked, it's there. I could resolve this by splitting up in two control flows (reselect all the needed data wÃth the ID-field in my selection) but I would prefer to solve it in another way Greets, Tom
View Replies !
Error In Oledb Command
We are having package which transfers data from AS/400 to SQL server 2005, the source component is a data reader which connects to the AS/400 and pulls the data and some transformation are being made and then finally Oledb command is used which uses a Stored Procedure to Insert/Update the records. The Packages transfers nearly 2,00,000 records, but after transferring some records we get the following error: Error: 0xC0202009 at dftJDE_CUSTOMER_ORDER_ITEM, olc_JDE_CUSTOMER_ORDER_ITEM_InsertUpdate [199]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.". Error: 0xC0202009 at dftJDE_CUSTOMER_ORDER_ITEM, olc_JDE_CUSTOMER_ORDER_ITEM_InsertUpdate [199]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.". This same error is getting repeated many times. Anyone have come across this problem, any help appreciated. Thanks in advance.
View Replies !
DBSTATUS_UNAVAILABLE With OLEDB Command
Hello, I have an issue with the OLEDB Command in a package of mine where it used to work and now it doesn't. I had 4 different packages where I was bringing in a flat file, massaging the data, and calling 1 of 4 Stored procedures using the OLEDB Command. Everything was working great, then we decided to use the SSIS EBCDIC conversion in the flat file connection rather than converting to ASCII outside of SSIS. This wasn't a problem for 3 of the 4. The one I have an issue with seems to be somehow corrupt, I keep getting the error "Invalid character value for cast specification", as well as "DBSTATUS_UNAVAILABLE", for a particular column. When I re-map that column in the OLEDB Command task, re-run the package, it then tells me a different column has the exact same error. So in going through and re-mapping all of the columns, it goes back to the 1st column I had an error with and gives me the exact same error... ARRRGGGGHHHH!!!! It seems like something is corrupt in SSIS or something. Does anyone have any thoughts, other than re-writing the package from scratch? I know there's no issue with the data, I imported the same file into a test table without problems.
View Replies !
SQL Command Returning Some Incorrect Records
I am fairly sure that I am just overlooking something, but the following command is returning some incorrect fields. SqlDataSource1.SelectCommand = "SELECT ITNBR, (SELECT ITDSC FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS ITDSC, SUM(MOHTQ) AS Balance, (SELECT VNDNR FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS VENDOR FROM AMFLIBT.ITEMBL WHERE VNDNR = @DDLVNDNR GROUP BY ITNBR, VENDOR" SqlDataSource1.SelectParameters.Add("ddlvndnr", ddl1.SelectedValue) I have more code to show, if this looks correct. Just let me know. Thanks in advance.
View Replies !
Writing Data To OLEDb Dest With The Table Name Set Via A Variable. (ValidateExternalMetadata Issue)
I am using a foreach loop, with the data from an ado recordset, which contains the table name that I wish to write data to an OLEDB data dest. The table names are retrieved from an execute sql task in the an object var. Within the foreach loop, for each table name, I then use a datareader to an ado.net source to pull data from that table, via an expression construct into a variable - i.e. "select * from " + @[User::table_name]. This works fine for the first table, in which mappings are setup using the SSIS design environment. The data is retrieved. I then use a variable and set the data access mode for the oledb destination to "Table name or view name variable". This also saves data fine for the first table in the loop in the oledb dest. When the next table name is retrieved from the ado provider in the foreach loop, the datareader fails, as it still thinks the metadata mappings are from the first table, which was used for the mapping in the design environment. I.E. FIN_CLASS is a column from the first table in the loop. Error: 0xC0202005 at Data Flow Task, DataReader Source [7181]: Column "FIN_CLASS" cannot be found at the datasource. I have set the following properties, that I thought (in my feeble mind), are supposed to avoid that behavior. For the datareader, I set ValidateExternalMetadata to false, and for the data flow task (container for the datareader), I set DelayValidation to true. These settings, according to the doc, are supposed to evaluate metadata for the datareader source at runtime (not design time), so that the column metadata is dynamic, and so that the subsequent oledb destination can use the "data access mode" for the oledb destination of "Table name or view name variable". If I cannot get this to work, I have 2 options: Use OPENQUERY via dynamic t-sql statements, OR create 30 separate flows in SSIS - one for each table - not looking forward to that one.
View Replies !
Parameters In A OleDb Command Transformation
Hi there, In order to prevent lookup errors in a lookup transformation, I've decided to go for an OleDb Command Transformation. This transformation should check the lookup and, if it turns out to be null, ir returns a dummy value. Otherwise, it would return the lookup value. This should be done by doing something like this: select coalesce( (select ID_Table2 from ID_Table2 where FK_Table1 = ?), 0) suposing Table2 has an atribute called "FK_Table1" that should match a column in the data flow. Now, such command result in this message: "An OLE DB record is available. Source "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax eror, permission violation, or other nonspecific error". But, it I remove the coalesce and type the following command: select ID_Table2 from ID_Table2 where FK_Table1 = ? It presents me no errors and allows me to continue. Did i did anything wrong or is this something that is not possible to be done? I know i have the option to use a script task to do this operation, but that would turn the maintenance process a little more difficult. Otherwise, i know i could also re-direct the error from the lookup transformation and handle it. Though, my package has about 10 lookups and that would turn my package a lot more complex than Thanks in advance Best Regards André Santana
View Replies !
Oledb Command Transformation Output
Hi, I have two tables, Table A on Server 1 (3 ROWS) ID Name Address ID1 A B ID2 X Y ID3 M N There is another table on a different server which looks like Table B on Server 2 PKColumn ID Details 1 ID1 Desc1 2 ID1 Desc2 3 ID1 Desc 3 4 ID2 Desc 5 ID2 Description As you can see the ID is the common column for these two tables, I want to get the Query the above 2 tables and the output should be dumped into a new table on Server2. I am using the following SSIS Package OledbDataSource-------> OledbCommand(Select * from TableB where ID =?) From here, how can insert the rows returned from the oledb command into another table. Since, for each row of TableA it will return some output rows...How can I insert all these into the New Table. Please help on configuring the output of the oledb command. Thanks,
View Replies !
Incorrect Syntax Near [SQL UPDATE COMMAND] &> Cmd.ExecuteNonQuery()
Please let me know what is wrong with my code below. I keep getting the "Incorrect syntax near 'UpdateInfoByAccountAndFullName'." error when I execute cmd.executenonquery. I highlighted the part that errors out. Thanks a lot. --------------------------------------------------------------------------------------------------------------------------- public bool Update( string newaccount, string newfullname, string rep, string zip, string comment, string oldaccount, string oldfullname ) { SqlConnection cn = new SqlConnection(_connectionstring); SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn); cmd.Parameters.AddWithValue("@newaccount", newaccount); cmd.Parameters.AddWithValue("@newfullname", newfullname); cmd.Parameters.AddWithValue("@rep", rep); cmd.Parameters.AddWithValue("@zip", zip); cmd.Parameters.AddWithValue("@comments", comment); cmd.Parameters.AddWithValue("@oldaccount", oldaccount); cmd.Parameters.AddWithValue("@oldfullname", oldfullname); using (cn) { cn.Open(); return cmd.ExecuteNonQuery() > 1; } }
View Replies !
SSIS OLEDB Source Data Access Mode (Table Name Or View Name Variable)
Thanks for any one can give me a help. I am try to transfer some tables data from one database server into another database server. I create a package in SSIS, and I use a variable to pass each table name. In Data flow, I use a OLEDB Source, but I cannot set the Data access mode to Table name or view name variable. Ever time, I will get this following error info "=================================== Error at Data Flow Task [OLE DB Source [31]]: A destination table name has not been provided. (Microsoft Visual Studio) =================================== Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ Program Location: at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData() at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata() at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)". Some one can tell me what is the reason, or give me some examples. Thanks in advance.
View Replies !
How To Use The OLEDB Command To Call A Oracle Function?
HI, I want to use the OLEDB command to call a oracle function, but i havnt found any materials about how to do that, my oracle function is as below: CREATE OR REPLACE function GET_ZONEID_FROM_SYFZ(ycz varchar2,xc varchar2,strat_id varchar2) return varchar2 IS zone_id_result varchar2(10) ; begin PKG_DM_DQ.GET_ZONEID_FROM_SYFZ(ycz,xc,strat_id,zone_id_result); return zone_id_result; end; In OLEDB command transformation component, i fill the sql command with "select GET_ZONEID_FROM_SYFZ(?,?,?) from dual", but i dont have it worked. The error message is :provider can not derive parameter information and setparameterinfo has not been called. Who have any idea about how to make it work? Thanks ~~
View Replies !
OLEDB Command Usage When Update/delete On Db2
We have SS2K5 source and DB2 Target. I downloaded and installed MS OLE DB provider for DB2. It is configured correctly. The data insert works fine. But I am having problem with update/delete on DB2 when passing string parameters. The OLEDB command works fine when I hard code the values: delete from TableName where Col1='abc' and Col2='xyz' But when I use parameters the package executes successfully, but the data is not delete on DB2. Any one having similar experience or solution for this is really appreciated. Thanks,
View Replies !
OLEDB Command Parameter Type Is Different In Different Situations
Hi All, I am using OLEDB Command transformation in a data flow to update the table. Find the columns for the table EMP as follows. EmpID - int EmpName - varchar(40) EmpSal - float Status - varchar(20) I am using the following command to update the table. Update EMP set status = 'Disabled' where EmpID = ? and EmpSal = ? when I use the above condition the type of the second parameter is taking as "double precision float" as the incoming input column type is "double - precision float". This is fine. But when I use the following condition the type of the param is taking as different one. Update EMP set status = 'Disabled' where EmpID = ? and ( EmpSal + ? ) = 0 It is taking as "four byte signed integer" even though the incoming input column type is "double precision float" thus I am loosing the precision and getting error when the limit exeeds. If I use 0.0 then it is taking as "Numeric" type. If I use convert function like (EmpSal + ? ) = Convert(float, 0) then it is taking as "double precision float". Update EMP set status = 'Disabled' where EmpID = ? and ( EmpSal + ? ) = Convert(float, 0) My question is how it behaves in the above situation. Can any body clarify please? Venkat.
View Replies !
Problem With Update Statement In OLEDB Command
HI, In my mapping i have used one OLEDB command which updates a table. It updates 70 columns of that table. The problem is that it is taking long time to execute that(almost 15-20 minutes) It is updating almost 3k-5k rows. I tried to put the update statement in SP and called that in OLEDB command. Still it is taking same time. Please advice how to solve this problem. Thanks in Advance.
View Replies !
Maxmimum Number Of Parameters For OleDB Command
I have a stored proc with 28 commmands but only 24 are loaded. If I move the parameters around the ones that didn't show are visible and the ones after the 24th parameter don't show up. Is there some sort of limit on parameters for OLEDB commands that execute stored procs in the exec procname @p = ? construct?
View Replies !
Handling Updates In SSIS With OLEDB Command Transformation
Hello My package flow is like This OLEDB SOURCE --> LOOK UP TRANSFORMATION | lookup output / error output(for new Inserts) (updated records) / / DERIVED COLUMN DERIVED COLUMN TRANSFORMATION1 TRANSFORMATION1 | | v v OLEDB COMMAND TRANSFORMATION OLEDB DESTINATION (inserting new records to (Updating records in destinationTable) destination) in this senario new records r insrted properly but though package runs without error records not get updated in Destination. In OLEDB COMMAND my query is like below, UPDATE TARGET_SCD_1 SET CURRENTSTATUS = ?, CURRENTSTATUSEFFECTIVEDATE=?, PROPOSALEFFECTIVEDATE=?, UNDERWRITINGEFFECTIVEDATE=?, TECHLAPSEEFFECTIVEDATE=?, WITHDRAWNEFFECTIVEDATE=?, DCSEFFECTIVEDATE=?, PREPOSIONEFFECTIVEDATE=?, INFOURCEEFFECTIVEDATE=?, LAPSEEFFECTIVEDATE=?, SURRENDEREFFECTIVEDATE=?, FLCEFFECTIVEDATE=?, CANCELLEDEFFECTIVEDATE=?, DCIEFFECTIVEDATE=?, REC_UPT_DT=? WHERE O__NUM = ? In advanced editor of OLE DB I hv created additional 16 paramater columns though i assign datatype as numeric to tht columns when i press refresh automatically it changes to DT_STR. My destination table columns r numeric . I though due to this datatype mismatch the error came So i change the datatype of dest to varchar to make compatible with OLEDB Comand Transformation. THN also no Use NO UPDATES package is running without error but records not get updated. if change the flow like below OLEDB SOURCE --> LOOK UP TRANSFORMATION | lookup output / error output(for new Inserts) (updated records) / / DERIVED COLUMN DERIVED COLUMN TRANSFORMATION1 TRANSFORMATION1 | | v v OLEDB COMMANDTRANSFORMATION / (Updating records in / destinationTable) / / / UNION ALL TRANSFORMATION | v OLEDB DESTINATION In This Case The updated record get inserted in the target as wel as the old remains as it is means m getting one additional record. kindly help me to figure out the bug M frusted with this issue please.............
View Replies !
OLEDB Command Transform Error Code: 0x80004005
I'm receiving an Error Code: 0x80004005 ... a "non-specific error" when trying to use a fairly simple insert in the oleDB command transform. SQL command works fine if I specify the parameters in the values clause of the INSERT statement. If however, I try to use the parameters in setting a variable, I get the 0x80004005 error. Is this a known issue/limitation, or am I just reallly doing something wrong?
View Replies !
Error Output In Oledb Command Advance Editor
I have a data flow and inside the data flow , i have a ole db source and ole db command task to execute an insert transaction ( SP).. i would like to save the error output if the insert didn;t happend into a error log table.. but when I darg an error output line ( red) to another ole db command to insert an error log , i can only see two columns( error code and error column) are available in OLEDB command advance editor related to errors.. this doesn;t tell you much information about the error.how can i grap the error reson(desc) as the error output and store into a erro log table? so that i can see what the problem is?
View Replies !
OLEDB Command Gives An Error For The Parameters Used In The Second Insert Statement
Hi, First script mentioned here does not work while the second script works even though they are doing the same thing and first one is good way. OLEDB Command gives an error for the parameters used in the second insert statement of the first script. Why is it so? --First Script INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable --AutoIncremented column INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,?,?,'act') END -- Second Script INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @Cost AS money SET @Cost=? DECLARE @MarkDownDollars AS money SET @MarkDownDollars=? DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,@Cost,@MarkDownDollars,'act') END
View Replies !
Output Param Of Stored Proc - Oledb Command
Hello This is my first post so please be gentle!! I have a stored proc that returns a value: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON SET NOCOUNT ON GO -- ============================================= ALTER PROCEDURE [dbo].[spPK] -- Add the parameters for the stored procedure here @varNSC varchar(4), @varNC varchar(2), @varIIN varchar(7), @varIMCDMC varchar(8), @varOut as int output AS Declare @varPK int set @varPK = 0 BEGIN --This checks Method 1 --NSC = @varNSC --NC = @varNC --IIN = @varIIN begin if exists (select Item_id From Item Where NSC = @varNSC and NC = @varNC and IIN = @varIIN) set @varPK = (select Item_id From Item Where NSC = @varNSC and NC = @varNC and IIN = @varIIN) set @varOut = @varPK if @varPK <> 0 Return end [There are some more methods here] Return END How do I get at the output value? I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.
View Replies !
Exception Error - Incorrect Syntax Near '('. Inline UPDATE Command
Hi, Here's the code I've used to try and update a new user's IP Address to a Table called Customer who's key field in the UserId: Getting the Exception Error "Incorrect Syntax near'('. " Any ideas? protected void ContinueButton_Click(object sender, EventArgs e) { //Get the ip address and put it into the customer table - (the instance of this user now exists) MembershipUser _membershipUser = Membership.GetUser(); //This gets the active user if there is someone logged in... Guid UserId = (Guid)_membershipUser.ProviderUserKey; //This gets the userId for the currently logged in user string IPAddress = Request.UserHostAddress.ToString();//This gets the IPAddress of the currently logged in user string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); using (System.Data.SqlClient.SqlConnection con =new System.Data.SqlClient.SqlConnection(cs)) { con.Open(); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.Connection = con; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "UPDATE Customer SET(IP_Address = @IP_Address) WHERE (UserId = @UserId)"; cmd.Parameters.Add("@UserId", System.Data.SqlDbType.UniqueIdentifier).Value = UserId; cmd.Parameters.Add("@IP_Address", System.Data.SqlDbType.Char, 15).Value = IPAddress; cmd.ExecuteNonQuery(); con.Close(); } Thanks.
View Replies !
OLEDB Command Stage Error In SLOWLY CHANGING DIMENSION
I have created a sample dataflow to parse the employee details (empid,empname,empaddr) from a flat file to oracle 9i database table named employee(columns : empid,empname,empaddress - All are varchar2(15)) using SLOWLY CHANGING DIMENSION transformation for insert/update on the table. EMPID as Businees key EMPNAME and EMPADDR as changing attributes. Connection string is using Microsoft oledb provider for oracle. I am getting the following oledb command error. ----------------------------------------------------------------------------------- TITLE: Microsoft Visual Studio ------------------------------ Error at Data Flow Task [OLE DB Command 1 [2007]]: An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.". Error at Data Flow Task [OLE DB Command 1 [2007]]: Unable to retrieve destination column descriptions from the parameters of the SQL command. Warning at {CF5DCB64-279E-45A4-A9A8-FF2FBB130980} [Insert Destination [1972]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used. ------------------------------ ADDITIONAL INFORMATION: Errors were encountered while generating the wizard results: Error at Data Flow Task [OLE DB Command [1996]]: An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.". Error at Data Flow Task [OLE DB Command [1996]]: Unable to retrieve destination column descriptions from the parameters of the SQL command. Error at Data Flow Task [OLE DB Command 1 [2007]]: An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.". Error at Data Flow Task [OLE DB Command 1 [2007]]: Unable to retrieve destination column descriptions from the parameters of the SQL command. For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=ScdWizardGenerationErrors&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
View Replies !
OLEDB Command Giving Error For Decalre And Set Statements At The Top Of The SQL Script
Hi All, I have an OLEDB command in my package that has to execute some SQL script. But when I declare and set a variable at the top of all code, The OLEDB gives an error in column mappings tab. My DQL script is as shown below DECLARE @Cost AS money SET @Cost=? --Some update statements a table OLEDB Command works if write the declare and set statements after update statements. Like below. But I don€™t need it. --Some update statements a table DECLARE @Cost AS money SET @Cost=? I also observer that,Oledb Command gives error for the code given below. Just paste the following Script in OLEDB command, it gives error in column mapping tab DECLARE @Cost AS money SET @Cost=? Any Idea on this behaviour? Thanks in advance..
View Replies !
Calling Scalar Valued Function From SSIS OleDB Command Transformation
Hi There, I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say.. 'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column. I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this. Thanks a lot!!
View Replies !
SQL Command Needed To Edit Table Data
I have an existing SQL 7 server named HHARBR. HHARBR has a database namedSPR with a table named "reportname" in it, the table has the name HHARBRembedded the table data.I migrated the HHARBR database SPR to a second server called HHARBR2. When Ilook into the table in HHARBR2 I still see the name HHARBR in the data.What command can I run on HHARBR2 to change the table entries of HHARBR tothe new server name of HHARBR2?I prefer something I can run in the Query AnalyzerSource server: HHARBRSource Database: SPRSource Datbase Table: reportnameDestination Server: HHARBR2Destination Databse: SPRDestination Table: reportnameSummary:Change wrong server name in destination server to reflect the destinationsserver name
View Replies !
Data Flow OLE DB Command And Table Locking
Hi all, I'm just testing an SSIS package and am having issues with dealing with locked records. my situation is as follows: my source table is oracle, my destination table is in SQL server. my data flow is a very simple update with a lookup transformation and then two OLEDB commands for update and insert. On each of the OLEDB commands I have set the "command timeout" to 5 seconds (just for testing purposes). also each OLE DB command has a failure path that outputs to a flat file. I'm expecting that if the destination table/records is/are locked then after 5 seconds the record will be output to the flat file. so to test this I begin a transaction on the destination table and don't commit it. then I start the SSIS job. it doesn't appear to even get to the OLE DB commands. it appears to stop at the beginning of the data flow task. the output window shows this: "Information: 0x40043007 at Import from Phoenix, DTS.Pipeline: Pre-Execute phase is beginning." but it just hangs there indefinately. the progress tab tells me that it get's from the validating stage and past the prepare for execute stage but hangs on pre-execute - 0 percent. I've put the command timeout = 5 on everything that I can find. I've mucked arround with all the possible "validateExternalMetadata" properties even though I only guessed that it may be the cause. is there anything that I'm missing? where should I look next? (yes it does work perfectly when there is no transaction locking the target table) Cheers, Andrew
View Replies !
Copying Table Data From SQL Server 2005 To SQL Server 2000 - Very Slow When Using OLEDB Source And Destination Sources?
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out. On SQL Server 200 instances the job ran in minutes in the old 2000 package. Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be? Any inputs will be much appreciated. Thanks, MShah
View Replies !
Is Having A Trigger That Inserts A Row In Table 'A', When A Row In Same Table Is Inserted By ADo.Net Code?
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
View Replies !
Trigger- Dump 'inserted' Table To Temp Table
I want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare). Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?
View Replies !
Monitoring Inserted Data And Comparing Against Selected Data
I made ahuge load script in SQL Server 2000 as i load data from manytables(select some of each one collumns) into one single table and iwant to test the loaded data against the selected data to make surethat the loaded data is the same the selected datais there a code or tool to make this test or monitoring ?? pleaseurgent ....
View Replies !
Inserted Table ?
Trying to get my head around this table: I thought it contained all data used in an insert; meaning if i enter a row in a table the inserted table should then contain all data entered? Ive created multiple tables linked via primary key Learner_ID this key is unique to any/all students and can never be duplicated hence i thought i would use it to link the tables. I have tables Fab / Main / Elec / Learner_info / WS_WR. For Fab / Main / Elec the only data i need form Learner_info is the Learner_ID field, i simply used: INSERT INTO FAB(Leaner_ID) SELECT Learner_ID FROM Inserted as a trigger to insert the ID into these tables. Everything works perfectly fine. So i assumed that i would be able to use something very similar to insert say: Learner_ID, FNAME, LNAME, ONAME and C_NUM from the learner_info table into the WR_WS table. Apparently i was wrong I can insert the learner_ID perfectly but if i then try to add another insert trigger eg: insert fname i get an sql error: Cannot insert the value NULL into the column 'LEARNER_ID', table 'swdt_im.WS_WR'; column does not allow nulls. INSERT fails. Now from reading the error it suggests to me that because the second trigger is running my learner_id value has become a NULL. Obviously a primary key wont accept this value so the statement fails. What im struggling to understand, im sure i will solve the problem eventually but i would apreciate help, and this is most important to me rather than a solution is this: If i enter data into a row on a table does that data go into the inserted table in the same row? or does the data go into inserted as a single entry row, meaning when i tab along from learner_ID into FNAME does Learner_ID become row 1 (in inserted) and FNAME the current row? or do they go into the appropriate columns? I was under the assumption that i could enter: LEARNER_ID / FNAME / LNAME / ONAME / C_NUM into one table and have a trigger insert those values into multiple other tables. Dont suppose someone can help me out im alright querying a SQL db but ive never had to develop anything other than a single table one myself before Thanks in advance
View Replies !
Use Variable As &&"IN&&" List For OleDB Command
Group, First let me say that I'm new to SSIS, so be gentle with me. I need to extract a limited set of data from a very large view in Oracle (I know, yuk!). The view contains millions of rows, but I only need the child matches of 343 unique keys in a one to many relationship. In pure SQL the query would look something like this. Select proj, tn, rn, total FROM Oracle.view WHERE proj in (select distinct proj from MSSQL.dbo.projlist) As you can see, this is an impossible query on many levels. My first thought was to get the runtime list into a variable and use that variable as a parameter in the Oracle OleDb Source. Alas, the Oracle source will not allow me to add a parameter. My second thought was to use a script component and build a SQL_Command string into a variable with all of my keys included. Then use the read_write variable as the SQL Command from variable in the Oracle Source. My attempts to construct such a variable expression have failed. Any ideas would be appreciated. RH
View Replies !
How To Find An Inserted Value In A Table
Hello, I have 2 tables, and use objectdatasource and stored procedures, with sql server. Let say in the first table I have IDCustomer as a datakey, and other records, and in the second I have the same IDCustomer and CustomerName. I have an INSERT stored procedure that will create a new record in the first table (so generate a new IDCustomer value), and I would like to insert immediately this new value in the second table. How can I know the value of this new IDCustomer ? What is the best way to handle that ? Once the insert in the first table is done should read it the table and extract (with an executescalar) the value and then insert it in the second table ? This solution should work but I am not sure this is the best one. Thanks for your help.
View Replies !
How To Retrieve A Value From The Inserted Table
I'm writing a trigger for my DotNetNuke portal that requires me to read the value of a just inserted record. I believe I'm doing this right, still I'm unable to retrieve the desired value from the Inserted table. What am I doing wrong? Here is my code: CREATE TRIGGER tr_addEditorRole ON [dbo].[UserPortals] AFTER INSERT AS Declare @Portal int set @Portal = (select PortalId FROM inserted) Declare @TabId Int set @Tabid = (select TabID from Tabs where Tabs.PortalID = @Portal and Tabs.TabName = 'MyTab') Declare @ModuleId int set @ModuleId = (SELECT ModuleId FROM Modules WHERE Modules.TabID = @TabId and Modules.ModuleTitle = 'MyModule') update Modules set AuthorizedEditRoles = '-1;' where ModuleId = @ModuleId
View Replies !
Inserted/deleted Table.
Hi, I am currently working on a MS SQL server 2000. I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted --- are not accessible. anyone knows why? And is there any other way to do this? Thanks
View Replies !
Inserted Table When Inserting
Hi, Now thanks to you good folks on here, I have recently found out that when inserting data into table, there is a system table which can be queried with triggers - specifically called "Inserted". What I am wondering is what are the limitations of what I can do with the data in this table? I know I can query it from within the trigger, but can I update data specifically in this table before it is inserted? (ie IF field1 FROM inserted = 'blah' UPDATE inserted SET field2 = 'something') If so is there anything that I need to look out for? Concerns? Etc? Thanks in advance for your help Cheers
View Replies !
Using Inserted Table In Trigger
hi, i am writing a trigger to log inserts,updates and deletes in a table and would like to also enter the user details ie who did the transaction. is the spid in the inserted table? if not how do i get this information? TIA
View Replies !
Getting Recenlty Inserted Value From A Table
Hi all, I am inserting one row from sp which is at one database into table on other database.I want to retrieve one columns value which will be recently inserted . I was trying it using output parameter and @@Identity in sp. But hardly any success.I guess we can not get @@Identity values from other server. Can anybody suggest better way to do it Thanks Kedar
View Replies !
Inserted And Deleted Table
hi for after trigger the records stored in followig table inserted and deleted table. but i want to know where this tables physically stored ...i mean in which database master or some other database? and 2nd thing tigger fired for each row or for only insert,delete,update statement? thanx
View Replies !
Getting Last Identity Value Inserted In A Table
Suppose we have a table with an identity field. Someone adds a record and wants to retrieve the value of the identity field from the just added record. I have seen code like this: BEGIN TRAN .... INSERT data select newlyAddedID from table END TRANS In other words, they think that surrounding the INSERT and SELECT in a transaction insures they will get the correct value (even when other apps are updating this table). Is this approach sound? TIA, barkingdog
View Replies !
How WCF Can Get New Inserted SQL Data ?
dear all, I have a WCF service which is host in a console application for the time beeing. This service provide methods for retriving history data when request. So far so good. My WCF service need also to know when a particular table (ALARMS tabel ) gets updated with DELETE, INSERT, OR UPDATE. This because my client application (WinForm) need to refresh a datagrid binding to that ALARMS table. In other words my WCF servcie would send a callback event to my client when it as been notice for a change.. But my problem is how my WCF service can be notify from an update in my SQL table ? I have tried SQLDependency class, but I give up was not working properly...and hard to know the xact context you are runing on. Was thinking also of having a timer whcih pool every 1s by calling my tabel store procedure and verifiy is somerow ha changed.... What to do, how to do, what is the best way and thred safe method Thnaks for help and advise regards serge
View Replies !
|
TRACKER
