Many times i write stoted procedures with transaction blocks.
I have delete a row after begin transaction and in continue i
read from table the select statement get back the deleted row:
begin tran
delete mytable
where id = @myid
and seqid = 3
select sum(balance)
from mytable
where id = @myid
............
...............
commit tran
.... OR
rollback tran
the sum(balance) function has calculate the balance of row 3
I use SQL 7.0
No I did not write this below, this is from a vendor, I used profiler and I believe their SP is causing a blocking problem on their vendor supplied DB. It thought at the least always have a begin end or a begin trans commit trans. ANy quick opinions greatly appreciated
create procedure write_planned_service_rec @p1 varchar(20),@p2 varchar(20),@p3 varchar(20),@p4 varchar(20),@p5 varchar(20), @p6 varchar(20),@p7 varchar(20),@p8 varchar(20),@p9 varchar(20), @p10 varchar(20),@p11 varchar(20),@p12 varchar(20),@p13 varchar(20),@p14 varchar(20), @p15 varchar(20),@p16 varchar(20),@p17 varchar(20),@p18 varchar(20),@p19 varchar(20), @p20 varchar(20) AS IF @p20 = 'P' update patient set date_insurance_updated = getdate() where patient_id = @p1 and practice_id = @p13
I have an overnight process that takes transactions from an external system & applies updates to a single db table. Other processes may be active on the db but none touch the tables I'm using. I cannot guarantee the volume of source transactions (may vary from 100s to 100,000s).
My question is should I protect the update within a begin+commit/rollback or should I have a recovery procedure to run in the event of failure (that would delete any rows added to my db table)? (My preference is to do the latter - so I'm really looking for any reasons why I shouldn't take this approach).
-- DICHIARAZIONE VARIABILI... DECLARE @dett_idturno as INT DECLARE @totale_giorni as INT DECLARE @tipo_albo_A as VARCHAR(9) DECLARE @tipo_albo_B as VARCHAR(9) DECLARE @data_odierna as DATETIME DECLARE @tot_avvocati as INT DECLARE @avv_giorno as INT DECLARE @avv_giornoA as INT DECLARE @avv_giornoB as INT DECLARE @conta_giorni as INT DECLARE @incremento_giorni as INT DECLARE @incr_dif_giorno as INT DECLARE @nuovo_idturno as INT DECLARE @idalboturno as INT DECLARE @old_turni as INT DECLARE @tot_giorniA as INT DECLARE @tot_giorniB as INT DECLARE @conta_giorni_incremento as INT DECLARE @conta_avvocati as INT DECLARE @avv as INT DECLARE @altri_turni AS INT DECLARE @neg_giorni_incremento as int -------------------------------------------------------------------------------------------------------
-- SET VARIABILI PER EVITARE PROBLEMI CON NULL o 0 SET @totale_giorni = 0 SET @tot_avvocati = 0 SET @avv_giorno = 0 SET @conta_giorni = 0 SET @incremento_giorni = 0 SET @incr_dif_giorno = 0 SET @idalboturno = 0 SET @old_turni = 0 --------------------------------------------------------------------------------------------------------
SET @nuovo_idturno = @idturno
BEGIN TRAN
-- conta i giorni SELECT @totale_giorni = (DATEDIFF(dd, @data_inizio, @data_fine))+1
-- select da vista avvocati per tipo difensori selezionato.. (controllare anche data_fine_iscrizione?)
create table #Tmp ( [ID_anagrafica] varchar(50) )
IF @tipo_albo = 'DIFO' BEGIN SET @tipo_albo_A = 'DIFM' SET @tipo_albo_B = 'CPT'
-- select per contare..
SELECT @tot_avvocati = COUNT(*) FROM VALBO_ISCRIZIONE_DIF_ORDINARI
SELECT @tot_avvocati As tot_avvocati
-- creo tabella temporanea.. INSERT INTO #Tmp SELECT [ID_anagrafica] FROM VALBO_ISCRIZIONE_DIF_ORDINARI ORDER BY ID_ANAGRAFICA
END
IF @tipo_albo = 'DIFM' BEGIN SET @tipo_albo_A = 'DIFO' SET @tipo_albo_B = 'CPT'
-- select per contare.. SELECT @tot_avvocati = COUNT(*) FROM VALBO_ISCRIZIONE_DIF_MINORI
SELECT @tot_avvocati as tot_avvocati
-- creo tabella temporanea.. INSERT INTO #Tmp SELECT [ID_anagrafica] FROM VALBO_ISCRIZIONE_DIF_MINORI ORDER BY ID_ANAGRAFICA END
IF @tipo_albo = 'CPT' BEGIN SET @tipo_albo_A = 'DIFM' SET @tipo_albo_B = 'DIFO'
-- select per contare.. SELECT @tot_avvocati = COUNT(*) FROM VALBO_ISCRIZIONE_DIF_CPT
SELECT @tot_avvocati as tot_avvocati
INSERT INTO #Tmp SELECT [ID_anagrafica] FROM VALBO_ISCRIZIONE_DIF_CPT ORDER BY ID_ANAGRAFICA END
DECLARE @idanagrafica varchar(50)
-- GIORNI PER AVVOCATO -- tot_giorni * n_dif_minimo / tot_avvocati = tot_giorni_avvocato (con intero successivo) SET @incremento_giorni = ((@totale_giorni * @n_dif)/@tot_avvocati) -- controllo se c'è resto.. IF ((@totale_giorni * @n_dif)%@tot_avvocati) <> 0 BEGIN SET @incremento_giorni = @incremento_giorni + 1 END
-- ogni avvocato deve essere difensore per almeno 2 giorni di seguito.. -- quindi se l'incremento è minore di 2 deve essere uguale a 2 IF @incremento_giorni < '2' BEGIN SET @incremento_giorni = '2' END
-- AVVOCATI AL GIORNO -- numero variabile.. prendere in considerazione il primo intero e l'intero successivo.. SET @avv_giorno = (@incremento_giorni * @tot_avvocati)/@totale_giorni SET @avv_giornoB = @avv_giorno
-- controllo il resto della divisione.. se <> 0 @avv_giornoB = @avv_giorno + 1.. -- altrimenti i due valori sono uguali..
IF ((@incremento_giorni * @tot_avvocati)%@totale_giorni) <> 0 BEGIN SET @avv_giornoA = @avv_giorno + 1 END ELSE BEGIN set @avv_giornoA = @avv_giorno END
-- conteggi giorni totali difensori... -- giorni con N difensori SET @tot_giorniB = ((@avv_giornoB * @incremento_giorni)/@totale_giorni) -- giorni con M difensori SET @tot_giorniA = @totale_giorni - @tot_giorniB
declare @totale as int declare @conta_inseriti as int set @conta_inseriti = 0 set @totale = (@avv_giornoA * @tot_giorniA) + (@avv_giornoB * @tot_giorniB)
-- ciclo per totale dei giorni SET @conta_giorni = 1 SET @conta_giorni_incremento = 0 SET @conta_avvocati = 0 WHILE @conta_giorni <= @totale_giorni BEGIN
-- ogni giorno @avv deve essere ZERO SET @avv = 0
IF @conta_giorni <= @tot_giorniA BEGIN SET @avv_giorno = @avv_giornoA END ELSE BEGIN SET @avv_giorno = @avv_giornoB END
-- ciclo per ogni giorno per totale di avvocati/giorno SET @incr_dif_giorno = 0
WHILE (@incr_dif_giorno < @avv_giorno) AND EXISTS(SELECT TOP 1 ID_anagrafica FROM #Tmp) BEGIN
SET @conta_avvocati = @conta_avvocati + 1
SET @data_odierna = DATEADD(dd, (@conta_giorni-1), @data_inizio)
SET @neg_giorni_incremento = -1 * @conta_giorni_incremento
SET @old_turni = 0 SET @altri_turni = 0
SELECT TOP 1 @idanagrafica = ID_anagrafica from #Tmp DELETE #Tmp WHERE ID_anagrafica = @idanagrafica
-- query che controlla i turni già assegnati per altre liste...
SELECT @old_turni = COUNT(*) FROM Albo_Turno_Dettaglio CROSS JOIN ALBO_TURNO WHERE Albo_Turno_Dettaglio.idalbo = @idanagrafica AND ( ALBO_TURNO.Tipo = @tipo_albo_A --- probabile problema con trigger OR ALBO_TURNO.Tipo = @tipo_albo_B ) AND ( Albo_Turno_Dettaglio.Data BETWEEN DATEADD(dd, (@neg_giorni_incremento + 1), @data_odierna) AND DATEADD(dd, (@incremento_giorni-@conta_giorni_incremento), @data_odierna) )
SELECT @old_turni AS old_turni
-- (nel caso in cui il ciclo ricominci..) controllare che questo avvocato non abbia -- già un set di giorni in questo turno...
-- passato il primo controllo.. deve passare anche questo..
SELECT @altri_turni = COUNT(*) FROM Albo_Turno_Dettaglio WHERE Albo_Turno_Dettaglio.idalbo = @idanagrafica AND Albo_Turno_Dettaglio.idturno = @nuovo_idturno
SELECT @altri_turni AS altri_turni
IF @old_turni = NULL BEGIN SET @old_turni = 0 END
IF @ALTRI_TURNI = NULL BEGIN SET @ALTRI_TURNI = 0 END
IF (@old_turni = 0 AND (@altri_turni = 0 OR @altri_turni < (@incremento_giorni))) BEGIN -- se non ci sono turni sovraposti assegna il turno all'avvocato x N giorni (incremento_giorni) -- seleziono il dettaglio con idturno max per aumentare di uno... SET @idalboturno = 1 SET @dett_idturno = 1 SELECT @dett_idturno = MAX(idalboturno) FROM Albo_Turno_Dettaglio
if (@dett_idturno) = null begin set @dett_idturno = 0 end
SET @idalboturno = @dett_idturno + 1
BEGIN TRAN
INSERT Albo_Turno_Dettaglio ( idalboturno, idalbo, idturno, data ) VALUES ( @idalboturno, @idanagrafica, -- da cursore @nuovo_idturno, @data_odierna -- problemi inserimento data?? )
COMMIT -- valorizza il numero degli avvocati del giorno + 1 SET @incr_dif_giorno = @incr_dif_giorno + 1 END ELSE BEGIN -- non incrementare la variabile... SET @incr_dif_giorno = @incr_dif_giorno END
END -- END WHILE AVVOCATI PER GIORNI
SET @conta_giorni_incremento = (@conta_giorni_incremento + 1) IF @conta_giorni_incremento < @incremento_giorni BEGIN --DEVO RIPOPOLARE LA TABELLA PERCHE' OGNI AVVOCATO DEVE AVERE X GIORNI.. -- cancello tutti i record e poi inserisco di nuovo.. DELETE FROM #Tmp IF @tipo_albo = 'DIFO' BEGIN
INSERT INTO #Tmp SELECT [ID_anagrafica] FROM VALBO_ISCRIZIONE_DIF_ORDINARI ORDER BY ID_ANAGRAFICA END
IF @tipo_albo = 'DIFM' BEGIN
INSERT INTO #Tmp SELECT [ID_anagrafica] FROM VALBO_ISCRIZIONE_DIF_MINORI ORDER BY ID_ANAGRAFICA END
IF @tipo_albo = 'CPT' BEGIN
INSERT INTO #Tmp SELECT [ID_anagrafica] FROM VALBO_ISCRIZIONE_DIF_CPT ORDER BY ID_ANAGRAFICA END
END ELSE BEGIN set @conta_giorni_incremento = 0 END
SET @conta_giorni = @conta_giorni + 1 END
DROP TABLE #TMP
COMMIT TRAN
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
First of all, this is my first time using SQL SERVER 2005 express, before that i'm using POSTGRESQL database.
I would like to know how what's the equivalent command for "BEGIN","ROLLBACK","COMMIT", these are the POSTGRESQL COMMAND use to start transaction, rollback transaction and commit transaction.
Example when i use this kind of command is . I need to insert data into 3 table. before insert into table1, i issue "begin", start to insert data into table1, if table1 no error, then i proceed to table 2 and table3. if table2 and table3 no error. then issue "commit" to commit the changes. but if any error happen between table1 and table 2 or table 2 and table3, i will issue "rollback" to roll any changes that i make to table1, table2 and table3.
Maybe some one can teach me how to achieve using SQL SERVER 2005 EXPRESS.
I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."
I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.
Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint] AS DECLARE @TableName VARCHAR (50)
I have a series of questions about SSIS and transactions. The answers to these questions are probably so obvious that I can't see them, so please feel free to just point out what it is that I'm missing. My transaction-processing experience is very low-level, so I'm probably just not seeing how it's done at the high level of SSIS.
The first question is one that I may know the answer to, so please confirm:
Consider a package with TransactionOption set to Supported. It contains a single Execute SQL Task with TransactionOption set to Required. Is it true that if that Execute SQL Task succeeds, that the transaction commits, and that if the task fails, the transaction rolls back?
Consider another package with TransactionOption set to Supported. It contains a Sequence Container with TransactionOption set to Required. That container contains our same Execute SQL Task, but that is joined to a script task by a "success" precedence constraint. The script task simply returns Dts.Results.Failure. Is it the case that the transaction will roll back? That is, is it truly a simple failure result that would initiate the rollback?
If a DataFlow Task is the one that is set to Required, does that mean that every transactional operation within that task will commit in a single transaction? For instance, if I'm inserting five rows for each input record from a flat file, and if my flat file has 1000 records in it, will I see a single transaction with 5,000 rows? Thanks for your patience!
Hope this is the right forum. I'm using Transaction=required on a page which inserts on multiple tables, 2 of which have a foreign key relationship. All works fine as log as I don't input erroneous data. However, I have a range check in the code, and if the range is exceeded, an exception is thrown and the transaction fails using ContextUtil.SetAbort(). I then correct the data and try to save and get a Foreign key contraint error. I have debugged and the primary key table seems to be carrying out the insert ok (I'm retreiving the key at that point, and can see it). But when I use the key in the child table it fails and cites the foreign key relationship. I suspect that having the same data for the primary key table 2nd time around means it doesn't think it has to commit???? Grateful for any help. I'm using Sqlserver 2005 by the way.
I have a update trigger. In this trigger I need to insert few records in 3 tables. If error comes in any of these inserts then previous inserts to get committed. This trigger was written in Sybase and it was possible to create transaction and commit the transactions.
I have to modify the table structure where the table have a lot of data already. The log is getting full due to uncommitted transactions, there is a lot of data being updated in large bulks, not all of the transactions are committed, the update task cannot be completed. However, there is no more spare disk space for it to commit the transaction. Anyone can help?
If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce
How do I make use of begin transaction and commit transaction in SSIS.
As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:
Hresult:0x80004005
descriptionyntax error or access violation.
its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.
I am executing a stored procedure something like this
Create Procedure TEST @test1 @test2 AS BeGIN TRANSACTION ONE SELECT... UPDATE.. .... ....
.... TRUNCATE etc and lot of Select,update and delete statements like this ..... COMMIT TRANSACTION ONE
The Block of code which I have b/w BEGIN TRANSACTION AND COMMIT TRANSACTION ..Will it be rolled back if it encounters any errors in the SELECT,UPPDATE,DELETE ..statements which I have with the transaction one. IF not How do I roll back if it encounters any erros b/w the BEGIN TRANSACTION and END TRANSACTION.
I had thought that if any statement failed within a BEING TRANS .. COMMIT TRANS block, then all the statements would be rolled back. But I am seeing different behavior (SQL Server 2000 8.00.2039)
For instance, run these statements to set up a test: --DROP TABLE testTable1 --DROP TABLE testTable2 CREATE TABLE testTable1 (f1 varchar(1)) CREATE TABLE testTable2 (f1 varchar(1)) CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1) insert into testTable1(f1) values ('a')
So table testTable1 has a unique index on it..
Now try to run these statements:
--DELETE FROM testTable2 BEGIN TRANSACTION insert into testTable1(f1) values ('a') insert into testTable2(f1) values ('a') COMMIT TRANSACTION
SELECT * FROM testTable2
..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?
I want to rollback my t-sql if it encounters an error. I wrote this code:
begin tran mytrans; insert into table1 values (1, 'test'); insert into table1 values (1, 'jsaureouwrolsjflseorwurw'); -- it will encounter error here since max value to be inputted is 10 commit tran mytrans;
I forced my insert to have an error by putting a value that exceeds the data size. However, I didn't do any rollback. Anything i missed out?
I'm receiving the below error when trying to implement Execute SQL Task.
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'
I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.
Hi there, I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following: <code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction? Any help is greatly appreciated
Hi have have two linked SQL Servers and I am trying to get things workingsmootly/quickly.Should I be using 'BEGIN TRANSACTION' or 'BEGIN DISTRIBUTED TRANSACTION' ?Basicly, these SPs update a local table and a remote table in the sametransaction. I cant have one table updated and not the other. Please dontsay replicate the tables either as at this time, this is is not an option.I have for example a number of stored procedures that are based around thefollowing:where ACSMSM is a remote (linked) SQL Server.procedure [psm].ams_Update_VFE@strResult varchar(8) = 'Failure' output,@strErrorDesc varchar(512) = 'SP Not Executed' output,@strVFEID varchar(16),@strDescription varchar(64),@strVFEVirtualRoot varchar(255),@strVFEPhysicalRoot varchar(255),@strAuditPath varchar(255),@strDefaultBranding varchar(16),@strIPAddress varchar(23)asdeclare @strStep varchar(32)declare @trancount intSet XACT_ABORT ONset @trancount = @@trancountset @strStep = 'Start of Stored Proc'if (@trancount = 0)BEGIN TRANSACTION mytranelsesave tran mytran/* start insert sp code here */set @strStep = 'Write VFE to MSM'updateACSMSM.msmprim.msm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID;set @strStep = 'Write VFE to PSM'updateACSPSM.psmprim.psm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID/* end insert sp code here */if (@@error <> 0)beginrollback tran mytranset @strResult = 'Failure'set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' + @@Errorreturn -1969endelsebeginset @strResult = 'Success'set @strErrorDesc = ''end-- commit tran if we started itif (@trancount = 0)commit tranreturn 0
Hi All,Can any one help by giving me the details/difference in using the Transaction Isolation Levels (read uncommitted, read committed, repeatable read, or serializable)in asp.net. I just want to know in which case we can use these things in begining a transaction, and will it improve the performance. thanks in advance Boo
I am basically a newbie to networking. My manager gave me backoffice and said "here, install this".
I have NT and the service pack installed............but now I want to install SQL.........where do I begin? We are installing NT, and SQL etc on drive C: of the server, and the database (we use Goldmine for database software/contact management), is installed on drive D: anyone point me in the right direction???? web pages, books, newsgroups...........any help is greatly appreciated.
Thanx In Advance. Mike Ciotti mciotti@usinfotel.com
Server: Msg 156, Level 15, State 1, Procedure fn_GetSpouseFrSecondPerson, Line 49 Incorrect syntax near the keyword 'BEGIN'.
from this code :
if (@flags | @LN_MATCH) BEGIN set @nPos = charindex(@sOwnerslast, @sSecondperson) set @sSpouse = left(@sSecondperson, @nPos - 1) END
(whole listing is below for the curious)
the problem is not my boolean test; if i just replace it with TRUE, the same results.
Only by commenting out the BEGIN ... END block eliminates the error.
Is there some esoteric rule about which commands or how much or how little can be in a code block?
I'm stumped...
Thanks,
Joe
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTERFUNCTION fn_GetSpouseFrSecondPerson( @sOwnersName varchar(50), @sSecondPerson varchar(40), @sToken varchar(10), @TrustFlag int, -- implicit cast from datetime does not work (how are nulls cast?) @CompFlag int ) RETURNS varchar(30) AS BEGIN
-- if SecondPerson is empty, return empty if @sSecondPerson = '' RETURN '' if @sSecondPerson is NULL RETURN ''
DECLARE @sOwnersLast varchar(50) if @TrustFlag is not null SET @TrustFlag = 1 else SET @TrustFlag = 0 --convert null to 0
DECLARE @sSpouse varchar(40), @nLen int, @nPos int DECLARE @nOwnerSex int, @nSpouseSex int
DECLARE @flags int -- store flags SET @flags = 0 -- initialize DECLARE @TRUST int, @COMP int, @LN_MATCH int, @FOR int, @FN_DIFF int, @HW int -- flags bitmasks SET @TRUST= 1 SET @COMP= 2 SET @FOR= 4 SET @LN_MATCH= 8 SET @FN_DIFF= 16 SET @HW= 32
-- get last name of ownersname without suffix (JR, etc) SET @sOwnersLast = dbo.fn_GetLastNameNoSuffix(@sOwnersName)
-- first check for spouse with same last name as ownersname
if @TrustFlag = 1 set @flags = @flags + @TRUST
if@CompFlag = 1set @flags = @flags + @COMP
if charindex('FOR', @sSecondPerson) > 0 set @flags = @flags + @FOR
if charindex(@sOwnersLast,@sSecondPerson) > 0 set @flags = @flags + @LN_MATCH
if (@flags | @LN_MATCH) BEGIN set @nPos = charindex(@sOwnerslast, @sSecondperson) set @sSpouse = left(@sSecondperson, @nPos - 1) END
If you are set up for AutoCommit why would you or should you set a explicit transaction? I have noticed that in some called stored procudures from a "container" stored procedure. (Hope I got that right) that in the called stored procedure a Begin tran is used. Can anyone help with the why and what fors? It seems to me that you want to let SQL Server handle this becuase of the danger of leaving out a Commit or Rollback? But thats me. I may be very wrong? Thanks.
I need to copy two large tables from one database into another, via the internet. I haven't worked out exactly how yet, but the first issue which has occurred to me is that by the time the first table has been exported (via a SELECT clause?) into a suitable file, the second table (to which it is related) will be out of sync. So, how do I ensure that I end up with a snapshot of the two tables, perfectly in sync with each other? I know that BEGIN/END TRANSACTION makes sure that UPDATES to tables remain in sync, but will it work just for SELECT statements?
hello, I have a big problem with a script.. some instructions seems to be not executed. I don't understand. If I execute line perline it's ok - but the entire block no. Explain me and find the solution:
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ACS_ACL' AND COLUMN_NAME = 'ZoneUId' ) BEGIN
ALTER TABLE dbo.ACS_ACL ADD ZoneUId T_UID;
UPDATE dbo.ACS_ACL SET ZoneUId = '1' WHERE ZoneUId IS NULL;
print 'end of script'
END
result:
Msg 207, Level 16, State 1, Line 9 Invalid column name 'ZoneUId'.
the error is on line: UPDATE dbo.ACS_ACL SET ZoneUId = '000000001' WHERE ZoneUId IS NULL;
I have writen a small program in a Query window that runs fine if I hilight and run small chuncks. (I have listed the statements with out the clauses so it is more easly viewed here.)
The problem is if I simply execute the Query window I get massive errors that don’t make sence. I am guessing I need some GO and BEGIN/END statements? But I don’t know where I should use them.
I would really appreciate a few pointers so I can just run the Query window.
drop table SourceFile drop table ReferenceFile
SELECT TOP INTO SourceFile FROM
SELECT TOP INTO ReferenceFile FROM
-- test data INSERT ReferenceFile (AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip) values () INSERT SourceFile (AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip) values (22) -- test data
drop table MATCHTEST SELECT TOP 1 AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip INTO MATCHTEST FROM AddressCleaned_npidata_20050523_20071112
DELETE MATCHTEST
ALTER TABLE MATCHTEST ADD REF_ML VARCHAR(2)
INSERT MATCHTEST () SELECT FROM ReferenceFile r inner join SourceFile s on s.New_Last_Name = r.New_Last_Name
/*Subject: How best to use BETWEEN Begin and End Dates to find out if anemployeewas/is member of any group for a certain date range?You can copy/paste this whole post in SQL Query Analyzer or ManagementStudio andrun it once you've made sure there is no harmful code.I am working on an existing database where there is code that is usingBETWEEN logic and three different OR conditions to search for a user thathas worked between begin and end date parameters that you search for.For me the three WHERE conditions with the Begin and End dates are a littleconfusing so I would like to know if there's a better/simpler way to writethis.1- I have groups table with GroupID, Name2- I have employees table with EmployeeID, LastName, FirstName3- I have employeegroups table where the EmployeeID has the GroupID he/shewas/is a member of and from what Begin to what End dates.The employee can never be a member of two groups in any date interval.The employee always was/is a member of a group from a certain to a certaindate and then the next group he/she is a member of a group begins 1 dateafter the previous group membership's end date. Therefore If I worked from2006-01-01 to 2006-01-31 and then I changed group, well in this databasethenext group dates would begin at 2006-02-01 till an Open Ended default dateof2009-12-31.I can also be a member of a group for 1 day: 2006-05-05 to 2006-05-05Please continue to read below at the bottom.*/USE tempdbGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'EmployeeGroups' AND xtype= 'U')BEGINTRUNCATE TABLE EmployeeGroupsDROP TABLE EmployeeGroupsENDGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'Groups' AND xtype = 'U')BEGINTRUNCATE TABLE GroupsDROP TABLE GroupsENDGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'Employees' AND xtype ='U')BEGINTRUNCATE TABLE EmployeesDROP TABLE EmployeesENDGOCREATE TABLE dbo.Groups(GroupID int NOT NULL,Name varchar(50) NOT NULLCONSTRAINT PK_Groups PRIMARY KEY NONCLUSTERED(GroupID))GOCREATE TABLE dbo.Employees(EmployeeID int NOT NULL,LastName varchar(50) NOT NULL,FirstName varchar(50) NOT NULLCONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED(EmployeeID))GOCREATE TABLE dbo.EmployeeGroups(EmployeeID int NOT NULL,GroupID int NOT NULL,BeginDate datetime NOT NULL,EndDate datetime NOT NULL,CONSTRAINT PK_EmployeeGroups PRIMARY KEY NONCLUSTERED(EmployeeID,GroupID),CONSTRAINT FK_EmployeeGroups_Employees FOREIGN KEY(EmployeeID) REFERENCES Employees(EmployeeID),CONSTRAINT FK_EmployeeGroups_Groups FOREIGN KEY(GroupID) REFERENCES Groups(GroupID))GOINSERT Groups (GroupID, Name)SELECT 1, 'Group1' UNION ALLSELECT 2, 'Group2' UNION ALLSELECT 3, 'Group3' UNION ALLSELECT 4, 'Group4'GOINSERT Employees (EmployeeID, LastName, FirstName)SELECT 1, 'Davolio', 'Nancy' UNION ALLSELECT 2, 'Fuller', 'Andrew' UNION ALLSELECT 3, 'Leverling', 'Janet' UNION ALLSELECT 4, 'Peacock', 'Margaret' UNION ALLSELECT 5, 'Buchanan', 'Steven'GOINSERT EmployeeGroups (EmployeeID, GroupID, BeginDate, EndDate)SELECT 1, 3, '1990-01-01', '2004-10-15' UNION ALLSELECT 1, 4, '2004-10-16', '2004-10-16' UNION ALLSELECT 1, 1, '2004-10-17', '2099-12-31' UNION ALLSELECT 3, 2, '1999-11-15', '2002-02-22' UNION ALLSELECT 3, 4, '2002-02-23', '2099-12-31' UNION ALLSELECT 4, 3, '2006-05-17', '2099-12-31'GO--SELECT * FROM Groups--SELECT * FROM Employees--SELECT * FROM EmployeeGroupsDECLARE @EmployeeID INTEGERDECLARE @BeginDate DATETIMEDECLARE @EndDate DATETIMEPRINT 'First example of querying...'SET @EmployeeID = 1SET @BeginDate = 'Sep 18 2005 12:00:00:000AM'SET @EndDate = 'Sep 24 2006 12:00:00:000AM'-- This is the code logic being used in the database I am looking at.SELECT *FROM EmployeeGroupsWHERE EmployeeGroups.EmployeeID = @EmployeeIDAND ((EmployeeGroups.BeginDate <= @BeginDate AND EmployeeGroups.EndDate
