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
ADVERTISEMENT
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
View Related
Jun 26, 2006
I'm building a site, and while stress testing it I received a few exceptions when the SQL Server was under relatively high load. Originally I was opening the connection when required in a particular Sub as follows (and then closing it when I was finished with it):
If Not MyConnection.State = ConnectionState.Open Then MyConnection.Open()
The probelm however was that from time to time the connection state was Opening instead of Closed or Open. So I am considering using the following piece of code instead:
If MyConnection.State = ConnectionState.Connecting Then Do Until MyConnection.State = ConnectionState.Open
Loop ElseIf MyConnection.State = ConnectionState.Broken Or MyConnection.State = ConnectionState.Closed Then MyConnection.Open() End If
I'm a little worried about the Do...Loop in there, but I don't see how it should be a problem. Any thoughts?
View 2 Replies
View Related
Feb 7, 2007
select sub1.*, case sub1.mdiff when sub1.mdiff<12 then 1 else 0 end as flag
error msg by query analyser
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '<'.
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'as'.
Thanks
Jeff
View 2 Replies
View Related
Jul 20, 2005
I have this piece of script and I'm trying to understand what the'SET t.Z_ASSOC_ROW = (t.rowid - m.MinRowID) + 1'and the 'INNER JOIN' line is doingUPDATE tSET t.Z_ASSOC_ROW = (t.rowid - m.MinRowID) + 1FROM tassoc tINNER JOIN (SELECT Booking,MIN(rowid) AS 'MinRowID'FROM tassocGROUP BY Booking) mON m.Booking = t.Bookingtable 'tassoc'rowid booking z_assoc_row1 38 02 40 03 41 04 42 05 43 06 44 07 53 08 53 09 102 010 103 011 103 0
View 1 Replies
View Related
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
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
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
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
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
Apr 2, 2008
Junk
OK, I have all of my SS2k sproc scripts saved as
owner.sprocname.prc
Because EM scripted them out that way
Now that we are using MS, it doesn't recognize the script, it doesn't highlight the code, and doesn't connect to a server
Can a change this somewhere?
I've got all of my scripts in pvcs as *.prc and I'd rather not have to change it
Any ideas?
View 5 Replies
View Related
May 20, 2008
Hi All,
I have a column called TIER in my database which is a long string. There are piece of these strings that are separated by spaces like this:
'A3A00 A2B00 B1A00 C2C06 C3A06 C5A00 D2C00 G6B00 M2B00 M3B00 P7A00 T2A00 G4C00 G3C00 T5A06'
How do I select the last 2 characters of the piece of string when the first 2 chacters are M2?
I have the following:
This is retruning me all the rows that have 'M2'.
declare @code varchar (2)set @code = 'M2'select tier from companywhere tier like '%'+@code+'___ %'
Any help is appreciated.
Thank you in advance.
View 3 Replies
View Related
Jul 23, 2007
I have the following sqlDataSource. I wan't to display one piece of data, not a whole row, from it. I can find lots of information on putting it into a datagrid, but nothing on puttin one piece in a textbox. I cannot use a formview as we are embedding html in response.write and it breaks in a formview. Thanks.
<asp:SqlDataSource runat="server"
ID="myEmpInfo"
SelectCommand="Select di.EmpInfo,di.eth,gc.t_level From tblEmp di
,tblMisc gc
Where di.empnum = @empnumand di.empnum = gc.empnum" DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings : myConnectionString %>">
<SelectParameters>
<asp:QueryStringParameter Name="empnum" QueryStringField="empnum"/>
</SelectParameters>
</asp:SqlDataSource>
View 1 Replies
View Related
May 30, 2008
Hi All,
I have a column in my table like so:
'D4B00 L2A00 L3A00 L6C00 P1C00 L2A28 P4B00 '
How do I check in SQL if any pieces have the first 3 character the same. In the above case, L2A is present twice. I need to do this because I need display disctinct items, therefore L2A needs to be displayed only once.
Any help is appreciated.
Thanks
View 3 Replies
View Related
Jun 1, 2007
Below is the pice of code that I use to produce results that look like this
[4] [5] [1]
what I would like to do is is make it look like this:
[4] [5] [1]
Basically anything that >3 = Green
= "[" & CStr(Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))) &
"] [" & CStr(Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))) &
"] [" & CStr(Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing)))& "]"
Thanks,
Rick
View 3 Replies
View Related
Jul 2, 2007
I work with .NET 2005.
thank you ...
View 6 Replies
View Related
Jan 17, 2006
First I want to say thanks to the folks how have helped me over the past week or two.
I'm missing a crucial piece of insight. How do I read a single piece of data from a SqlDataSource which only hold one row of data into a declared variable?
string zUserName = SqlDataSource.(get the value in the row that is labeled UserName)
It really doesn't matter to me whether it is an SqlDataSource or some other data container that hold the data I've selected. My goal is to be able to read some data from some predefined datasource into a declared variable.
If I am approaching this problem in the wrong way any advice or article links you could give me would be grealy appreciate.
Thanks, Bill
View 1 Replies
View Related
Dec 17, 2014
I have a database table that was designed by someone else, and I'm trying to see if I can normalize the pattern. Here's the dummy table:
CREATE TABLE [dbo].[BadTox](
[PatientID] [int] NULL,
[Cycle] [tinyint] NULL,
[ALOPECIA] [tinyint] NULL,
[Causality1] [tinyint] NULL,
[Code] ....
All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:
CREATE TABLE Symptom (
PatientID INT NOT NULL,
Cycle TINYINT NOT NULL,
SymptomName VARCHAR(20) NOT NULL, -- from the source column *name*
Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase
PRIMARY KEY (PatientID, Cycle, SymptomName));
I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...
I can retrieve all the column names that are in uppercase using this:
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'SCRIDB');
SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox');
SELECT name AS column_name
, column_id AS col_order
FROM sys.all_columns
WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS
AND object_id = @object_id;
but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):
ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply]
AS
SELECT PatientID
, Toxicity
, MAX(Grade) AS MaxGrade
[code]....
The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.
So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?
View 5 Replies
View Related
Apr 20, 2015
I have a string column in a DB where it's values contain the following midway through the string ([DOCUMENTGUID] is a uniqueidentifier that is different for each row):
<a href="../downloadDoc.aspx?dg=[DOCUMENTGUID]" target="_blank">
I would like to replace this part of the string with a different piece of text.
I know that I should be using PATINDEX and REPLACE functions but I don't know how to utilise.
View 4 Replies
View Related
Jun 22, 2006
The TSQL below all works except the bolded part at the end. I'm want to grab only the most recently logged piece of equipment not the most recent and all past ones as well which is what I've got doing minus the bolded part below. But I don't know how to say get this Equipment ID etc and only the most recently logged one to find its present location. The bolded part below is just there to show what I want it to do I know you can use an aggregate in a where clause. So in the first table listed tblRdrLog there is a column Time that I want to do this on so a.Time. I don't want to display a.Time just reference.
String dbsql = " SELECT a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";
#region Advanced Search Criteria
// Check whether advanced search submitted
if (adv)
{
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}
#endregion
dbsql += " ) " +
" AND a.Time = max(a.Time) " +
"";
View 11 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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