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
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)
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?
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'.
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.
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....)
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'
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.
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 %>">
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
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
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?
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):
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() + "' "; }
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
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.
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?!
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.
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.
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.
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.
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.
