I set the transactionoption = required at package level. This package has a couple of dataflow taks and both the tasks are set transactionoption = supported. If one of them fails, both the tasks should be rolled back. When I run the package, it simply hangs at the first dataflow task with no error message. I had to stop debug to stop the execution of the package.
My system has DTC service enabled with Network DTC access enabled and the sql server is running on my local machine, which is windows xp box.
Any idea why this happens and the solution or workaround for this?
I have a package that has three control flow items - an Execute SQL task, a Data Flow task and another Execute SQL task - linked in sequence by On Success constraints and contained by a Sequence container. I want any of the three contained tasks that fails to cause the Seuqence container to be rolled back and write a checkpoint.
I've configured the package to use Checkpoints, set the TransactionOption to Required and the FailPackageOnFailure to True ion the Sequence container and set the TransactionOption to Supported and the FailParentOnFailure to True on each of the three contained tasks.
When I attempt to run the package in debug mode, the first Execute SQL task executes fine but validation of the Data Flow task never finishes. If I switch the TransactionOption of the Sequence container to Supported instead of Required, the package executes fine.
I have had problems in the past where MSDTC was not running but that causes an error and I've checked that it's running and it is.
I was trying to maintain transactions between my tasks, so I set the TransactionOption property as Required to the Parent Container (For Each Loop Container). But I am getting the following error while executing my SSIS package.
"Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running."
I checked in my Windows Services and did not find MSDTC. Pls let me know if you have any solution for this.
I want to use transactions to protect my tasks. I have ten tasks that need to be completed in a single package. If any of the tasks fail - I need the entire process to rollback.
I have 5 execute sql tasks to truncate groups of tables in my destination database.
After each of the single truncate tasks - I use data flow tasks to copy the data from the source to the destination db.
Both dbs are on the same server.
I am basically copying the entire db - with the exception of a few tables.
package.transactionOption = required , and all tasks transactionOptions are set to supported.
Several questions. It seems to hang on the first data flow task - the output window stops, etc.
If I set package.transactionOption = supported - The package will execute. If I create an error on the thrid truncate task - all previous tasks will complete - the package will fail on step 3 and the truncation for this step is not commited.
I am partially there. Can anyone help point me in the right direction? I need the entire process to commit if all tasks are successful or rollback if any fail.
I can't find information on how you are suppose to handle the TransactionOption setting inside of a custom task.
I have a custom task that I have developed, and it basically calls a COM+ object that writes data to a database. When I have the task inside of a container that has the transactionoption set to required, and my custom task is set to supported, if one of the X items fail to execute in my custom task I am telling my task to fail the parent, which I thought would rollback everything. But it does not.
Is there someplace that I need to write rollback code in custom SSIS tasks? If there is I can't find any mention of it anywhere. Any examples out there on how to build custom SSIS tasks that support the TransactionOption parameter?
I have a package which consists of 3 Execute SQL Tasks -
1) Drop old database & Restore a new one 2) Run DDL 3) Run DML
My task was to put all of these in a transaction. I started using the "TransactionOption" property for this package to be "Required" and each child tasks also were set to the same transaction option property of "Required". However, after running the package, it errored out saying Task 1 cannot be in a transaction (which is logical) so, I removed the transaction required property from the first task and kept it on for the remaining 2 tasks. In addition, I have also set up a failover strategy, where in if these tasks were to fail, the package should restore the previous working copy of the backup. To test this scenario, I deliberately created an error in the DML task, so logically only this task should rollback, instead it rolls back the entire transaction and to my horror the failover step is not executed as well.
Is there something which I am not doing correctly?
I have a Package and a DataFlow Task. The Package has TransactionOption=Required. The DataFlow Task has an OLE DB Source and an OLE DB Destination. The DataFlow Task has TransactionOption=Supported. The package executes on a Workstation and DataSources for the OLE DB Source and the OLE DB Destination are on a Server.
After the package had been launched an error message showed:
[OLE DB Destination [43]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DWH_Destination" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. [DTS.Pipeline] Error: component "OLE DB Destination" (43) failed the pre-execute phase and returned error code 0xC020801C.
[Connection manager "DWH_Destination"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.". [Connection manager "DWH_Destination"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D024.
If I set TransactionOption=NotSupported in the DataFlow Task then the package executes successful.
I need to create either a sql query or a procedure and i am not sure how to go about it.Simply i have two fields in a table. 1 for products values and 1 for how many of that product was purchased.What i am wondering is how to calculate this in a sql query.can someone help pleaseIlan
I am deleting a database but I am not seeing any entry in server logs or in event viewer. and same thing is happens when I am deatching a database.Can any body tell me the way through which I can know when a particualr database was deleted or deatched.
I am trying to connect MS SQL 2000 with C language using ODBC drivers in win 2k environment
i have a UTF 8 string stored in a nvarchar field of the MS SQL table and i am trying to retrieve this field from C and store it in a txt file everythin is working perfectly fine the probs is that the UTF 8 string is not getting being retrieved correctly
Hello All, I have 2 tables Trans & Slmast In Trans Tran_type,Tran_amt,Slcode ---------------------------- VES,10000.00,AB01 DBN,20000.00,AB01 CRN,30000.00,AB01 BRT,40000.00,AB01 DBN,10000.00,AB02 CRN,50000.00,AB02 BRT,20000.00,AB03 CRN,30000.00,AB03
In Slmast Slcode,Sldesc -------------- AB01,ABC Ltd AB02,CAB Ltd AB03,BAC Ltd
All transaction related datas will be in Trans for which Slmast is the master. I want to sum those records based on slcode,tran_type for slcode which does not have tran_type with 'VES'
Select (Loccode+Glcode+Slcode) as LGS,Refno,Refdt Into #VST from Trans_Transnarr Where Trtype='VES' and (Refdt>=Convert(Datetime,@SDt,103) and Refdt<=Convert(Datetime,@EDt,103)) and (Cancflg Is Null or Cancflg='R') and Loccode=@Loccode and Glcode=@Glcode
Select Sl.Sldesc,Tr.Loccode,Tr.Glcode,Tr.Slcode, X.Refno,Convert(Varchar,X.Refdt,103) as Refdt, Sum(Case When Tr.Trtype='VES' Then Tr.Tramt Else 0 End) as VES, Sum(Case When Tr.Trtype='DBN' Then Tr.Tramt Else 0 End) as DBN, Sum(Case When (Tr.Trtype='CPM' or Tr.Trtype='BPM') Then Tr.Tramt Else 0 End) as Refund, Sum(Case When (Tr.Trtype='JNL' and Tr.Drcrflag='D') Then Tr.Tramt Else 0 End) as JNLD, Sum(Case When Tr.Trtype='SPS' Then Tr.Tramt Else 0 End) as SPS, Sum(Case When (Tr.Trtype='CRT' or Tr.Trtype='BRT') Then Tr.Tramt Else 0 End) as Receipt, Sum(Case When Tr.Trtype='ADV' Then Tr.Tramt Else 0 End) as ADV, Sum(Case When Tr.Trtype='CRN' Then Tr.Tramt Else 0 End) as CRN, Sum(Case When (Tr.Trtype='JNL' and Tr.Drcrflag='C') Then Tr.Tramt Else 0 End) as JNLC, Sum(Case When Tr.Trtype='SRT' Then Tr.Tramt Else 0 End) as SRT Into #VOS from Trans_Journal Tr,Slmast Sl,#VST X Where (Sl.Loccode=@Loccode and Tr.Glcode=Sl.Glcode and Tr.Slcode=Sl.Slcode) and (Tr.Loccode+Tr.Glcode+Tr.Slcode)=X.LGS and (Tr.Cancflg Is Null or Tr.Cancflg='R') Group By Tr.Loccode,Tr.Glcode,Tr.Slcode,Sl.Sldesc,X.Refno,X.Refdt
ALTER TABLE #VOS ADD OPDR Float,OPCR Float
UPDATE #VOS SET #VOS.OPDR = S.DRAMT,#VOS.OPCR = S.CRAMT FROM #VOS, (SELECT GLCODE,SLCODE, (CASE WHEN DRCRFLAG = 'D' THEN OPBAL ELSE 0 END) AS DRAMT,(CASE WHEN DRCRFLAG = 'C' THEN OPBAL ELSE 0 END) AS CRAMT FROM OBDATA WHERE LOCCODE=@Loccode and GLCODE =@Glcode) S WHERE #VOS.GLCODE = S.GLCODE AND #VOS.SLCODE = S.SLCODE
Always wondered, now I have the time to ask - we currently backup ALLdatabases and these seem to all grow in size, the MSDB BAK files are now35mb each and growing!!! Can we remove these from our Enterprise Manager?- Master- Model- MSDBCheers, @sh
Hi allI am creating a web services application in Visual Studio Dot netwhich links to a MS SQL database.I need to create a form which lets the user pick his field ofchoice(doesnt matter what it is..) using drop down boxes,radio buttonsetc and query the database according to what he has picked andretrieve only those columns and rows.The thing is, I do not want to hardcode the queries usingOleDbDataAdapter or SqlDataAdapter, but want the query to be generatedon the fly, once the user picks his fields from the form.I'd greatly appreciate any help.Thanks,m
I am writting a very simple ASP.NET code, I am simlply trying to establish a connection with the database in SQL Server 2000 and than closing the connection but still I am getting an exception,the name of the database is mydatabase, SQL Server 2000 is running in the windows authenticationfiltered=SSPI. The name of SQL Server 2000 running onfiltered=xyz, I checked the name from SQL Server Service Manager.Also I would like to state here that I also tryed to establish a connection using C# and the connection was successfully establish which means that there is no problem with SQL Server also I sucessfully established the connection with MS Access and my ASP.NET application which proves that there is no problem with IIS also.Also please check that the connection string which I am providing is correct or not.The exception which I am getting is as follows: /////////////////////////////////////////////////////////////////////////////// An exception occured while establishing connection Login failed for user 'xyzASPNET'. //////////////////////////////////////////////////////////////////////////// Please also note that I have a user with the name xyz/Administrator in the logins of Sql Server 2000. Also note one more thing that the name of my PC is xyz, I think that there is some problem with the connection string which i am providing so please especially check the connection string that either I have missed some thing or some thing has gone wrong,also please tell that are there any security restructions for ASP.NET to access SQL Server 2000 or some thing like that which I have to remove.The code is as follows: ////////////////////////////////////////////////////////////////////////////////////// <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %>
The same above code is also present in the attachement file data.aspx , please guide me where I am making mistake and please tell me that how can I correct this mistake so that the code executes correctly.Also please tell that what things should be included in the connection string and also please check the connection string of this code.
Need your help !! I have a database say for e.g Test_db . I have data in the form of Image and text in the database . The image data or symbols corresponds to the right text data . This database is being replicated to other servers . The servers are SQL Server 7.0 with SP 3 .
But when this database is replicated , the subscriber does not get the data in the right form . The image and text fields do not correspond properly as it is on the publisher . But sometimes it works .
This is a strange problem and i could not make out what could be the exact problem . Does any body have insight of this problem pl . Any kind of help will be appreciated .
How much RAM is necessary for concurrent SQL connections? My server has 2 Xeon processors, 1GB RAM, a 10GB database size on a TCP/IP box. CPU utilization can run 80-90% and pages/sec can sometimes jump up to 40, especially is the users are running reports from the SQL database. I've been told by some people that I have enough RAM to support the 130 SQL user connections. Others I've been told that I need at least 40MB RAM per user (which means that I'm about 4GB short of RAM). Based on previous entries, I obviously need more, but how much more is the question.
Hi, I have a doubt..... I have a text field that is 255 bytes in lenght and I want the contents of this full field to be split.....on various gouds which may be ',' or and or 'And' etc
This field stores all the skills...
So i have created a search module ....but this search is taking awfully long as u know text field searches are tedious...
Can u please suggest a way I could parse/split the text field to enable faster search....
I am trying to create a script that will return information from 4 different tables in a database using different where clauses.
This is working correctly, however the primary table the query uses has multiple entries for different people and i only want to return the last row per person. I have got this to work using a MAX(t.column) statement, but when used in conjunction with all the where clauses things go wrong.
This is what i have
Code:
CREATE VIEW Firereport1 AS
select max(t.punch_id) as punch, e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
FROM TM_PUNCH t, EMPLOYEE e, COMM_CLOCK c, DEPT2 d
WHERE t.PUNCH_EMPID = e.EMPLOYEE_ID and e.EMPLOYEE_ONCLOCK <> 'False' and e.EMPLOYEE_LASTTIMELOC = c.CLOCK_ID and e.EMPLOYEE_LASTDEPT2 = d.DEPT2_ID
group by e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
GO
This returns multiple entries because there are multiple unique entries for t.punch_time.
What i was thinking is i need a where statement on t.time_punch, d.dept2_description, c.clock_name that says something along the lines of where return t.punch_time where t.punch_id=punch (**substitute correct column names**)
Hi folks; I have two SQL machines; SERVER1 (Publisher) and SERVER2(Pull Subscriber). Every thing seems to be working but: I've just imported 17,000 records from another SQL database into a published table of the production server SERVER1 using IMPORT-EXPORT wizard. Now the subscriber doesn't get updated with these records. When i run the merge-agent; the agent just stops with success without pulling the data.
When i set the agent-profile to ROWCOUNT-VALIDATION; it fails at this table.
Publisher db at SERVER1 has recovery-model =FULL Subscriber db at SERVER2 has recvery-model=BULK-LOGGED
I have two related tables in my SQL database that I wish to join as follows:------------------------------------tblCustomersID (pk)Nameetc.tblCustomerManagersID (pk)CustomerID (fk)Manager (this *is* an fk but for the purposes of demonstration isnot)StartDate (indicates the date upon which the manager took / is takingcontrol of the company)------------------------------------Example entries are:tblCustomers1 Microsoft2 Symantec3 BorlandtblCustomerManagers1 1 Barry 01/01/032 1 Peter 01/07/033 2 Norman 01/02/034 3 Terry 01/01/035 3 Peter 01/07/05------------------------------------What I want to do is extract, in one query, a list of all customers andtheir *current* associated manager, so the result set today would be:Microsoft Peter 01/07/03Symantec Norman 01/02/03Borland Terry 01/01/03Currently I have:SELECT [Name], [Manager], [StartDate]FROM tblCustomersINNER JOIN tblCustomerManagers ON tblCustomerManagers.[CustomerID] =tblCustomers.[ID]WHERE [StartDate] <= GETDATE()ORDER BY [Name], [StartDate] DESCbut this obviously returns multiple entries for customers having managersprior to today eg:....Microsoft Peter 01/07/03Microsoft Barry 01/01/03....I know this is a simple question but I cannot think of a way of doing itwithout making the query extremely complicated.Any help is appreciated,Thanks,df
