Detecting Commits

Aug 10, 1998

Is there a TSQL function or value that is assigned to some sort of internal variable when part of a distributed transcation commits? If so, how do I access it?
Marc

View 1 Replies


ADVERTISEMENT

MSSQL7 Commits

Feb 23, 2001

We've experienced a problem with one of our MSSQL7 servers where transactions are not being committed to the database. We've experienced this in Query Analyzer -- where we run several (simple) insert or update statements. Although the script runs successfully, the rows do not appear in the tables. The implicit transaction setting is off for the server.
We have also experienced this problem with other tools that run against this server via ODBC.
I thought the default setting for MSSQL7 is to auto-commit transactions and I've seen nothing in the error log that indicates that the updates were rolled back. I have compared our server, database and client configuration options to other servers that do not have this problem and have no idea where to look next.
Has anyone encountered this problem? Or does anyone have some ideas about where I could look for more information?
Thanks!

View 2 Replies View Related

Trigger Commits

Mar 12, 2003

Hi,

I have a Insert,Update trigger on a table called TBL_TEST . I am doing inserts in 4 tables inside the trigger. But if any one of these insert fails I want to keep the changes made in any previous table but rollback the insert / update made on TBL_TEST.

When I encounter an error and issues rollback its rolling back all the inserts. Even though I am using exclusive begin tran - Commit Tran for every insert statement.

Whats going wrong here?

Thanks in advance.

jfkuser

View 2 Replies View Related

Do Cursors Lose Their Place If Deletes And Multiple Commits Are Occuring On The Underlying Table?

Oct 11, 2007

will my fetches on a cursor continue seamlessly even if deletes and multiple commits are occuring on the underlying source table? I want to make sure the cursor will not lose it's place as I believe some older dbms's did.

View 11 Replies View Related

Detecting IIS

Nov 17, 2005

Good Morning,

View 3 Replies View Related

Detecting Database Changes

Jan 10, 2005

Is there a way in detecting changes in the database?

thanks..

View 14 Replies View Related

Detecting LOB Datatypes.

Oct 15, 2007



Does anyone have a good way to detect LOB datatypes for a table or index? I am doing index rebuilds with the ONLINE option and need to work around indexes with LOB datatypes in them...


Any suggestion?

View 3 Replies View Related

Detecting Service Pack 2

Sep 27, 2007

Hello all,

Is there a preferred method for detecting SQL Server 2005 SP2? I do installation/deployment and during my install script I would prefer to skip the SP2 install if the instance is already upgraded.

I had tried to check HKLMSoftwareMicrosoftMicrosoft SQL ServerInstance NamesSQL to first find out what MSSQL.[n] our instance took.

After that I was attempting to check HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL.[n]SetupSP but there have been cases where the key SP did not exist.

I decided I could just install it under the condition that no key existed at all, assuming since the 'SP' key did not exist, the instance was not upgraded. In tests however, after a fresh install of SP2, the key was not there.

I guess the question is further defined as: does installing SP2 place a flag that it exists anywhere else? Is it supposed to write to HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL.[n]SetupSP?

Thanks,

-Mike

View 4 Replies View Related

Detecting (local) Sql-server

Jul 23, 2005

Can someone please point me to some code (preferably C#, but C++ or C or VBwill work) that will detect if the (local) instance of SQL Server isrunning on a machine or not? Many thanks!-- Rob

View 2 Replies View Related

DDL Triggers And Detecting Sp_rename

Jan 2, 2008

I am trying to use DDL triggers to detect all changes to a table.

Sometimes a user directly or (more likely) through the SQL Server Management Studio GUI renames a column via sp_rename.

I can't seem to figure out how to detect such renames using a DDL trigger. Is there any way or is this a limitation of the DDL trigger system?

View 9 Replies View Related

Detecting Of Record Does Not Work In If Query

Nov 23, 2007

Edit: Newer mind. I tested this query more after writing this, and now it seems to work!I hope it continues to work. In following query, else is never executed.CREATE PROCEDURE Put_into_basket(    @Product_code varchar(20))ASBEGIN    SET NOCOUNT ON;IF NOT EXISTS(SELECT * FROM dbo.t_shopping_basket WHERE Product_code=@Product_code)     BEGIN        INSERT dbo.t_shopping_basket (Product_code, Name,Price)        SELECT Product_code, Name,Price        FROM dbo.t_product        WHERE Product_code= @Product_code    ENDELSE    --this part is never executed    BEGIN            UPDATE dbo.t_shopping_basket        SET Quantity=Quantity+1        WHERE Product_code=@Product_code    ENDENDGO  The query should test if there is a record or row with Product_code=@Product_code. If there is not, that is the first part, one such row is inserted. Quantity has a default value of 1. Insertion works, one row is inserted. At least sort of. If there is already record, That's later part, Quantity is increased by 1. That too works, if ran separately.But when I test query, it never runs the quantity+1 part. 

View 3 Replies View Related

Detecting A Change In A Particular Field In A Database

Nov 16, 2005

Is it possible, using VB code, to retain a user on a particular page
until the status of a certain field in a database is changed?
The scenario I am working on is that a person makes a reservation and
has to wait until that reservation has been accepted or rejected by the
administration.

Dim dsResv As DataSet
        dsResv = objResv.DALgetResvStatus(resvId)

        While (dsResv.Tables(0).Rows(0).Item("resvStatus") = "pending")

            dsResv = objResv.DALgetResvStatus(resvId)

            If
dsResv.Tables(0).Rows(0).Item("resvStatus") = "denied" Then
                failure.Visible = True
                LinkButton1.Visible = True
           
ElseIf dsResv.Tables(0).Rows(0).Item("resvStatus") = "reserved" Then
                success.Visible = True
                LinkButton1.Visible = True
            End If

        End While

This is how I tried to do it, but it doesnt seem to work.
Any suggestions?

View 2 Replies View Related

Detecting Carriage Returns In A Column

Jun 12, 2006

Can anyone provide me with some SQL that will identify rows from a table where a varchar column named "Notes" contain Carriage Returns?

I know that with report writer SQR I can translate CR's to white space but I do not know of any Sybase function that will allow me to do the same, any ideas on this well would be appreciated.

View 3 Replies View Related

Detecting Joined Records That Number Only 1

Oct 1, 2013

I have 2 tables:

Table 1 Customers with column CustomerId
Table 2 Purchases with columns PurchaseId and CustomerId

I want to select all customers who have made just 1 purchase, what would be the sql for that? I know I can join the tables to get the customers with purchases, but I don't know how to limit it to those with just 1 purchase (and exclude all the other customers).

View 9 Replies View Related

Ssis - Detecting Hung Packages

Apr 17, 2008

Folks,
I have a SSIS package that works fine 90 % of the time. Every once in a while it gets hung and when I notice that the package has been running for several hours I check the job activity monitor and notice that the package is in executing state for several hours..my question is ...Is there a way I can check for this incidence through a c# app ? Does the package name show up in the process task if so I can poll to see if the process corresponding to the package has been executing for > 30 mins then I can kill it.


Any suggestions would be great.

Thanks

View 6 Replies View Related

Lookup Not Detecting New Column On Table

Aug 13, 2007

I have added a new column to a table, but it is not showing up in the column mapping tab of the lookup editor.

It does show up in the preview.

Without deleting and recreating the component (with all the ensuing broken metadata fixes 'downstream' that this always entails), how can I get it to recognise the change?

In future I will do all LUs as a sql statement so at least I can control columns. This again defeats the purpose of the drag and drop environment..... If it were a harry potter character, it would be a dementor

View 5 Replies View Related

Checksum Transformation Is Not Detecting My Change

Jul 20, 2007

I am using the Konesans Checksum transformation ( http://www.sqlis.com/21.aspx ) to detect changes in my big (many columns, type 2 SCD) dimensional table.

But I am running into collossions

The checksum transformation, sometimes misses a small change in the record, for instance when a certain flag is set or unset. Is there a more robust checksum generator? Of any other suggestions on to solve this?

thx

View 12 Replies View Related

Request For Info - Detecting DB Changes And Batching For E-mail

Feb 7, 2006

I have a .NET app recently ported from 1.1 to 2.0 with a pending feature request.  I'd first like to state that I'm not asking anyone for the programmatic answer, unless of course, you'd like to provide it.  I'm simply asking the best way to accomplish this task with the tools I have available to me (VS 2005, SQL 2000 and 2005).  I do not consider myself a professional .NET or T-SQL programmer but I do have a good understanding of the technologies so that I can find what I need to know once I know I'm going about something the right way.
The application I wrote, from scratch, manages News, Featured Connections, and FAQs for a web site.  It was designed in a multi-layer approach where there are:
items  -> assigned to categories -> assigned to users
So if user Fred is granted rights to category A, when he authenticates to the app he can Add/Change/Delete any item in category A.  Simple enough.  But also what Fred can do is see all the other items other users have created and *schedule* any relevant item related to his web page to appear within his own items even tho he has no rights to change that item in the admin interface.
The feature request is a notification service, so that Fred gets an e-mail when someone has added a new item or edited an existing one.  I don't want Fred to get an e-mail *every* time an item is created or edited, that would be big bother and would generate lots of e-mails.  What  I'd like to do is send Fred an e-mail sometime in the middle of the night that informs him of the items added or changed since his last login, so that at his choosing he can go into the admin interface and schedule one or more of those items for his page.
Should I just write a new console app that does this, compile it and schedule the .EXE to run at midnight? Or is there a better way to leverage the new features in SQL 2005 (extended strored procs, triggers) and .NET to do this a better way?  Currently, the DB is in SQL 2000 but I have no problems moving it to SQL 2005 if that helps me in some way. Thanks in advance for your comments.
...Bill
 

View 1 Replies View Related

Detecting SQL Server Service Pack Version?

Aug 5, 1999

How do you find out which service pack is installed on SQL Server?
We are installing sp5a but we get an error saying 'cfgchar.exe could not execute' and at the end it says ' can not install successfully'.
How can I understand If I really installed sp ?

View 2 Replies View Related

Detecting Or Monitoring For Long-running Queries.

Jul 20, 2005

I can't seem to find a step-by-step guide to how to detect orcontinuously monitor for long-running queries that is suitablefor a comparative SQL Server novice. I know that it is possibleto monitor for such with other database products - can anyoneenlighten me as to how this is done with SQL Server? Ideally,I'd like to snapshot running queries (preferably with querytext) at a particular instant via a script, although any helpto show what queries are running at an instant will be muchappreciated. Any ideas?__________________________________________________ ___________Are you Catholic ?http://www.CatholicEmail.com100s of FREE email addresses --->http://www.UltimateEmail.comSend an Online Greeting Card http://www.UltimateEcards.com

View 1 Replies View Related

Detecting Installed IIS - Installing SQL 2005 Standard

Dec 5, 2005

I've seen lots of posts but few if any solutions to this apparently common problem.

View 4 Replies View Related

Installing SQL Server 2005, Detecting Installed IIS

Jun 24, 2006

When trying to install SQL 2005, the installation halts at "Detecting Installed IIS". From a previous forum discussion I tried disabling Norton Anti-Virus and Firewall, but the install still does not get past this point.

Please help.

View 9 Replies View Related

Detecting && Deleting Duplicates In Batch Vs Proc

Aug 22, 2006

I know how to detect & delete dups/or >dups in test with a select clause, this works fine in a small table, but if the table has a million rows say, it sounds like a proc would be faster:  my question is:  How do I display those rows in a proc for detecting what the problem is.  The print stmt. doesn't seem to work and I wondered if I had to go through the process of building an output stream.  The proc creates okay but I'm stuck after that part.

thx

Kat  -- very rough code below

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc dupcount
@count int
as
set nocount on
select categoryID,
 CategoryName,
 Count(*) As Dups
 from Categories
group by Categoryid, CategoryName
having count(*) >1

set @count = @@Rowcount
print convert(varchar(30),CategoryID) + ' ' + convert(varchar(30),@count) + CategoryName

/*set @count = @@RowCount
IF @count > 1
print convert CategoryID, CategoryName, convert(varchar(30),@count)*/

 

View 18 Replies View Related

Transact SQL :: Tables And Database Not Detecting Auto While Coding

Aug 27, 2015

When I type Select statemet like below, Tables names are not populate auto. I need to type full lenght instead of selection in list.

Ex: I have few tables and database in my schema and need to select while coding.

select * from (Table Name) When i type first char in the table space it will not populate list of tables in my sql studio. 

View 12 Replies View Related

Transact SQL :: Detecting Modified Rows Before Writing To A Table?

Apr 17, 2015

I've a table with more columns and 1 identifier. I need to write this table when a modified row is detecting respect to the columns not to the identifier.

So I've created a temporary table to put the potential rows to write on the real table, but I want to detect the modified rows. I've thought to use the checksum function, but I don't know how to use it and if it could be useful in this scenario.

Moreover, in the temporary table I've collected daily the rows to write: the first day a row could have a value respect to his columns, the next day a different value and the next one the same value respect to the first day.

View 26 Replies View Related

When Install SQL 2005 Get Detecting Installed IIS Message And Stuck

Apr 13, 2006

I am installing SQL2005 Develop Edition, it goes to "Detecting Installed IIS" screen and stop there. No error messages, but not moving forward, kinda stuck. I am using XP Professional Service Pack 2, I tried uninstall IIS, got the same result. If anyone knows how to solve this problem, please advice, thanks a lot!

View 7 Replies View Related

SSIS Not Detecting Oracle Stored Procedure Failure

May 29, 2006

Hi,

I figured out a way to execute an Oracle Stored Procedure from an Execute SQL Task by using

Declare
Begin
SomeStoredProc(?,?,?);
End;

with an OLE DB connection using the Oracle Provider for OLE DB.

The parameters are getting passed in and the procedure executes but if for some reason it fails SSIS is painting the task green and keeps processing. I'm guessing that's because the outer Declare/End statement completed sucessfully.

I couldn't get it to work as a function with a return value. :(

Is there another way to execute an Oracle stored procedure that I missed?

Can you call an Oracle stored procedure from a Script Task and then fail it on parameter value?

Thanks

John Colaizzi

View 4 Replies View Related

SQL 2012 :: Detecting Open Tran In SSMS That Is Visible Indicator?

Mar 23, 2015

I am looking for a plugin or way of detecting an open tran in SSMS that is visible indicator,

I know of DBCC OPENTRAN
Select @@trancount

SSMS know you have an open tran when you attempt to close your SQL query window. Just looking for that to show or alert before that.

The context behind this is you given a script and its run on SQL and for some reason the tran is not committed. or rolled back.

View 0 Replies View Related

Reliably Detecting The Current Directory For An Executing SSIS Package?

Oct 30, 2007

I'm attempting to find a reliable way to determine the file system folder in which the current package is located. (Please note that for this purpose I have no need to support SQL Server deployment, so the fact that there is no current folder in that context is not in scope for this post. )

Anyway, I've added the following code to a Script task in one of my packages:




Code BlockMsgBox(Environment.CurrentDirectory)




When I run the package from DTEXEC I get this the current working folder from which I executed the DTEXEC command line. When I run the package from DTEXECUI I get "C:Projects2005TFSRootSSISCurrentBranchProject", which is the actual folder where the package is located. When I run the package from within Visual Studio (right click, execute package) I get "C:Projects2005TFSRootDocumentationSSIS" which is what is throwing me off. I can't tell where this path is coming from; it is a valid path on my machine, but why it's showing up as the current folder is the mystery. There's no code there and never has been, although I do check in and check out documentation stored in this folder.


I've looked around online but have had no luck in finding any information aboutreliably determining the location of an executing SSIS package. The best I have found is this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2202954&SiteID=1) which doesn't address the issue I'm seeing.

In short, I'm looking for a consistently reliable mechanism that will allow an SSIS package to be aware of its location at run time, regardless of the mechanism (DTEXEC, DTEXECUI, BIDS, etc.) through which the package is executed. My hopes aren't all that high, but I figured if I was going to get an answer anywhere it would be here.

Has anyone else had success with this?


Thanks in advance!

View 3 Replies View Related

Integration Services :: Detecting Empty Flat File With Script Task

Jun 17, 2015

I am attempting to get this script provided by Microsoft to work to no avail. Specifically, when I set the variable FFNonDataRows to 1 (in order to accommodate for the header row), the variable is not being set to False as expected. I don't know enough about C# to understand why this script isn't working. How to get this script to work in this manner?

[URL] ....

View 6 Replies View Related

Rookie Question? Peer-to-Peer Replication Not Detecting Conflict

Mar 8, 2007

Hi

Sorry for asking what may be a rookie question... We have configured transactional peer-to-peer replication in a testing environment with two servers, each publishing and subscribing to each other. If we write a piece of code that updates the same row on both servers with different values, we are not seeing data conflict issues. The data updated on server 1 propagates to server 2, but at the same time data updated on server 2 is updated on server 1. This leaves the records on each server out of data.

I expected my test case to produce errors, but instead I got inconsistent data. How do you turn on data consistency checking?

Thanks in advance

View 5 Replies View Related

Detecting Feature Pack Components On A SQL Server Server

Feb 28, 2007

We have a product that uses, and will bootstrap, several of the feature pack components when it is installed (xmo, MSOLAP9, etc). However, if our product is installed on a machine that has SQL Server 2005 already installed, all of the feature pack components will have been installed as part of the server install, so we don't want to bootstrap them in that case. Is there a good way to detect this case? Clearly, simply checking that the feature pack MSI hasn't been installed isn't good enough, since SQL Server has its own MSIs that are different from the FP MSIs.

View 2 Replies View Related







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