Exclude Part Of The Code In A Transaction

Oct 25, 2007

Hello,

I have a TSQL (SQL SERVER 2005) code inside a Transaction,
I would like to exclude a piece of that code from the transaction (I do need to recovere it in the trasaction log).

For instance

BEGIN TRAN

SQL Statement 1
SQL Statement 2 -- out of the transaction
SQL Statement 3

COMMIT TRAN

I would like to exlude SQL Statement 2 (SQL statement1 and 3 must be inside the same transaction)

Is it possible? How to do it?

Thank

View 8 Replies


ADVERTISEMENT

Exclude Piece Of Code From Transaction

Mar 20, 2008



Hello,

I have created a procedure with several SQL statement, all are included in a transaction.
Some of the code are "insert" in tables that are in remote server.
for example:


procedure sp1 is on SERVER B.
From SERVERA by linked server we have:

exec serverB.db_B.dbo.sp1 (from serverA the procedure sp1 is called)


CREATE procedures sp1
AS
BEGIN TRANSACTION
insert into T1 (col1) values (1)
insert into serverA.db_A.dbo.T2 (col1) vales ('8vhrfvrf')
insert into T3 (col1) values (3)
COMMIT TRANSACTION
GO


I would like to make that these piece of code (
insert into serverA.db_A.dbo.T2 (col1) vales ('8vhrfvrf')
) is out of transaction.
My question is: Is it possible to exclude piece of code from a transction?


You ask me why this: because SQL server do not allow loopback transaction


Thank

View 7 Replies View Related

How Do I Exclude Carriage Returns As Part Of A IS NULL Exclusion Clause...

Jan 6, 2005

Hi

I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.

I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.

Does anyone know how I can elegantly get around this problem other than my best guess below:

Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.

Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]


Cheers - Andy

View 4 Replies View Related

Exclude All ID Which Doesn't Purchase Any Code

Aug 30, 2013

how can i exclude all id which doesn't purchase any code.

codeA,codeB,codeC,codeD,.....
ID 1 purchase codeA, this will not be pulled out.
ID 2 didnt purchase any code, this will be pulled out.

I tried select id,code from tableA where code not in ('codeA','codeB','codeC','codeD',...)

but it still pull out ID 1.

View 1 Replies View Related

Code To Exclude Records In A Crosstab

Jun 20, 2014

Using MS ACCESS I have:

TRANSFORM Avg(SAP_CALD.[CASE_DAYS]) AS AvgOfCASE_DAYS
SELECT SAP_CALD.[OPN_YEAR], SAP_CALD.[CLD_MONTH], Avg(SAP_CALD.[CASE_DAYS]) AS [Total Of CASE_DAYS]
FROM SAP_CALD
GROUP BY SAP_CALD.[OPN_YEAR], SAP_CALD.[CLD_MONTH]
PIVOT SAP_CALD.[Model];

which works fine. I want to look at the query SAP_CALD and disclude any record in the field 'department' that has a record of any {DPSG, System, Dealer, DealerIT, Inbound}..I can do this by piggybacking a another query that removes these but wondered if it would be easier to exclude them in the crosstab code.

View 1 Replies View Related

Exclude Records From A Table Where ID Column Is Same But Mail Code Column Is Multi-valued

Nov 12, 2012

I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)

ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC

So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.

View 3 Replies View Related

SQL 2012 :: Cannot Truncate Log File Which Is Part Of Transaction Replication

Feb 9, 2015

SQL 2012 Ent SP2

Database is in simple recovery mode, and published with transaction replication push subscription, just one subscriber but the database is huge. I don't want to overwrite the schema at the subscriber either.

I had to run an alter database command on a published database, it created so many logs that an extra drive had to be added along with an extra log file to accommodate all the logs.

The problem I have is I'd like to know clear the file of logs so I can drop the temporary log file, and give the drive back, but I cannot.

I have tried dbcc shrinkfile with the emptyfile option but it never clears, I have also tried it with notruncate and truncateonly options (mainly out of desperation).

I do not need to worry about point in time restore as a full backup is taken before and after the operation. After which the database will be put back into Full recovery mode.

I have looked at log_reuse_wait_desc and the file says 'Replication', so I am now thinking the file cannot empty because replication is keeping one of the VLFs active. I tried dropping and recreating the subscription hoping it might free something up and I could get somewhere, but it made no difference.

Do I have to remove replication completely to get round this? Surely not.

I have also tried putting the database back into full recovery mode, doing a full DB backup, and a transaction log backup, but its made no difference, which is also what makes me think a portion of the log is still active because of replication, and perhaps the transactions have not gone through to the subscriber, which raises another question, why not?

I have not tried restarting SQL server, as I'd like to know a way out of this without having to do that, plus I do not think it would make any difference anyway.

View 1 Replies View Related

Applying DBCC DBREINDEX On A Database That Is Part Of Transaction Replication

Mar 4, 2007

I am about to apply DBCC DBREINDEX to a large database that is part of transactional replication and synchronised every 3 minutes. What are the likely implication and what precautions I must take.





Thanks in advance

Regards

R Suresh

View 1 Replies View Related

Where Should I BEGIN/END Transaction? In Code Or In The SP?

Mar 30, 2006

I notice you can do a END and BEGIN trasaction in BOTH. But I hope BOTH are not needed. So where so I use them  ?
 
Or should I even use them for simple updates and inserts ?

View 3 Replies View Related

Error Code: 60000000b8 Server Could Not Continue Transaction

May 27, 2008

Hi,

does anybody know this error code? I get that error from a jdbc-connection ocasionally but can't find an explanation. The original message is in German. The number should be ok. The error occures when a transaction is aborted, the jdbc-connection is lost.

'Der Server konnte die Transaktion nicht fortsetzen. Beschreibung: 60000000b8'

Please help,
Many thanks

View 2 Replies View Related

Web Part Deserialization Error When Trying To Change Report Viewer Web Part Programmatically.

Oct 29, 2007



I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"

If someone has a solution, please respond at your earlist.

Thanks

Shankar

View 1 Replies View Related

Split A Decimal Number Into The Integer Part And The Fraction Part

Dec 7, 2007

I have a table with a column named measurement decimal(18,1).  If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return  2.5.  So if the value after the decimal point is 0, I only want the stored proc to return the integer portion.  Is there a sql function that I can use to determine what the fraction part of the decimal value is?  In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.

View 3 Replies View Related

SQL 2012 :: Function With 2nd Part Working On Results 1st Part

Jan 28, 2015

I have made the following Scalar-valued function:

CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)

[Code] ....

What it does is convert numbers to times

E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)

So far so good (function works perfectly)

My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.

This means that, e.g. with a time like 3.23.40 the last zero must be deleted.

My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,

is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))

There are 5 codes: 0 1 2 3 and 4

View 9 Replies View Related

SSIS Error: The Connection Does Not Support Enlisting In Distributed Transaction. Error Code: 0x8000FFFF

Feb 29, 2008



Hi all,

can anyone tell me if an oleDb connection (provider is Jet 4.0 to Access database) can be enlisted in a Distributed Transaction?

The goal is to copy data from SqlServer to Access within a transaction.

Pier

View 10 Replies View Related

Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

May 31, 2008

Hi All

I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.

If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.

I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.

set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN


It's got me stumped, so any ideas gratefully received.Thx

View 1 Replies View Related

SSIS, Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Feb 22, 2007

I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.

for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.

I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.

if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

and

Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.

Please help me it's very urgent.

View 3 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Feb 6, 2007

I am getting this error  :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!

View 1 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Dec 22, 2006

i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.

Message is like this:

Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

View 8 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction. (HELP)

Jan 8, 2008

Hi,

i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".

my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.



Thanks in advance.


View 2 Replies View Related

Exclude Rows

Feb 16, 2004

Table 1 is just a reference table. Users add values to table 2.

I need to select/exclude records from table1 where the id2 in table2 = 1.

How get the following results:


table 1
----------
id
----------
a
b
c
d
e
f
g


table 2
----------
id / id2
----------
b / 1
c / 1
d / 1
f / 1
c / 2
d / 2
a / 4
b / 4


need results
----------
id
----------
a
e
g


any suggestions?


thanks

View 1 Replies View Related

Exclude Condition

Jul 26, 2005

i have two tables: "Person" and "Year". "Person" can have many "Year"
(one to many relation). i want a query which returns all the records
from "Person" where "Year" is 2005 but exclude if there is any "Year"
with 2004. how can i write that query? any help will be appreciated.
i did try
<code>
SELECT * FROM Person JOIN Year ON Person.Id = Year.PersonID WHERE Year.Year = 2005 AND Year.Year <> 2004
</code>
but it doesn't seem to work. i want this query to return records from
Person where there is no any year with 2004 but only 2005. If a person
has both 2004 and 2005 exclude that person.

View 1 Replies View Related

MDX - Exclude Condition

Dec 15, 2005

I have to built a query to get the % for all the Region (Americas, Asia and Europe) from a cube.

But in these regions some countries are excluded and treated seperate.

Like Asia does not include India and Japan.

How do I get the ASIA query using an EXCLUDE condition.

Please help.

View 1 Replies View Related

Exclude If Returned Value Is 0

Jan 27, 2012

I am running a query that works just fine however, I would like it to exclude value that are equal to zero.

Basically my query looks at the commission that clients pay over a number of periods.

So it goes like this

T.Client_Code as Client
,SUM(CASE t.Transaction_Date WHEN DATEADD(day, DATEDIFF(day, 1, GETDATE()),0)THEN (ABS (t.transaction_commission) /((fx.Exchange_Bid + fx.Exchange_Ask)/2 )) ELSE 0 END)as Commission_Day

FROM TABLE T
JOINING FX TABLE

WHERE
fx.Currency = 'USD'

And T.Salesman_Name in ('X''Y'Z)

Group BY
T.Client_Code

It works perfectly fine however, we dont transact with our clients everyday so therefore this list will return all of our clients in the database and many will have generate zero commission. I want to keep the query along those lines I just need to insert something that says "ONLY SHOW WHEN Commission is not ZERO.

View 2 Replies View Related

Exclude Status Appear More Than Once In Same ID

Jul 16, 2013

How can i exclude those with more than one status? i tried using rows but it seems doesn't work.

ID status
1 new
1 new
1 close
2 new
2 close
3 close
3 close
4 new
4 close

the result should appear as:

ID status
2 new
2 close
4 new
4 close

View 4 Replies View Related

Exclude Columns

May 13, 2006

Hi

i wanna exclude some columns from my select statement. i do NOT wanna columns starting with 1,2,3,4,5 and I. How can i do it?

thanks

View 5 Replies View Related

Exclude From Query

Jan 9, 2008

Hi. hope someone can help me with this thing.

I have a list of numbers that I get from a query to my database. how could I make the query so that I can exclude a couple of numbers. For example the numbers 1234 and 8888. My list is in the range of 1 - 99999.

Thank you
Mr. Newbie

View 1 Replies View Related

Exclude Query

Jul 23, 2005

I have 16,000 rows in tblClient and 3000 rows in NewTable.SELECT tblClient.*FROM tblClient INNERJOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossierif I use = (equal) instead of <> (exclude), the query returns 3000 rowswhen I use <> it returns 160000 rows,if I try group by, the query bugswhat is my problem

View 4 Replies View Related

Exclude Row If A Particular Field Is Null

Jun 12, 2008

Is there any way of excluding an entire row if a particular field contains a null value? Even if other fields in the row aren't null...  This is in SQL Server 2005 

View 2 Replies View Related

How To Exclude Zero Values When Sorting?

Feb 9, 2005

I have a datagrid with a “sort� field I want to use to sort the rows in ascending order. However, I want values with a 0 or NULL value to be displayed last. I can’t figure out how to do a sort (preferably in the SQL) that returns the empty values last. Is this possible?

View 7 Replies View Related

How Do I Exclude Null Fields?

Apr 28, 2006

Is there a way to write a select statement that will pull only fields that are not null?

View 2 Replies View Related

Exclude Rows In SQL Statement

Oct 31, 2001

I have a table (tblAction) that contains customer account numbers (Account)
and actions taken on the account in a given day (AcctCode). So each account
can have multiple actions taken on it (one row for each action) in a day.

I have a request to present a result set that displays all action 52, 53,54.
But if a given account number has at least one action 28, then they want me to exclude all the rows for that account number from the result set. Can someone help with this?

View 1 Replies View Related

How To Exclude Null Data?

Apr 28, 2006

I'm new to MS SQL server. Is there a select statement that will include fields that are not null?

View 2 Replies View Related

How To Exclude Data From An Average?

Apr 8, 2008

I am selecting the following fields

AVG_Back
AVG_Yield

I want to select both fields, like this

Select AVG(AVG_Back), AVG(AVG_Yield)
FROM tblUser
WHERE Date Between '3/1/2008' AND '3/31/2008'

I want to limit the AVG_Back field to exclude all values of 0. So only average AVG_Back if the value > 0. What is the best way to accomplish this? I can't just put it in the where clause or the AVG_Yield will be excluded too.

View 5 Replies View Related







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