Hallo. I need some help. Is it possible to run SQL statement outside a transaction when it is aleready started? Ok let's look at example, if I cas say so:
begin tran...{do something somehow in environment when @@trancount = 0}...commit tran
I tryed do like this
begin tran...exec('select @@trancount')...commit tran
but result was "1".
Sory about my english and thanks for help.
Hello all,
I am currently having a rather pestering issue with my full/transaction log backups. It seems that after running either, my logs do not truncate and the file continues to stay @ the current size and eventually fill to the brim. This issue only began after mirroring was setup. Is there any differences in log file maintainence when dealing with Mirrored databases ? I made my last attempt to run backups and waited over the weekend for some hope that a checkpoint would occur and my file would be shrunk once again for normal use.
Any insight on this is greatly apprecieated.
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
Hi There
I have a large database in full recovery model (9.0.3152).
I have s stored procedure, that generates a HUGE transaction log, i have optimized the stored proc as much as i can, i have also broken it up into smaller transactions that i commit more frequently and do transaction log backups more frequently.
However i cannot make a significant dent into the transaction log size.
Is there anyway / syntax to ensure that a transaction or statement is not logged to the transaction log ?
I will never want to have this transaction or commands present if i do a restore.
Is this possible ?
Thanx
Hi!
I have a problem when I use Handling transaction. I basically combine
the handling Transaction with Handling Exception. However, I use the
Handling Transaction under "Using" Statement. This is the code that I
use:
Try
con = New SqlConnection(....)
con.open()
trans - con.beginTransaction
For Each row in Books.rows
Dim cell As TableCell = row.Cells(0)
Dim checkBox As HtmlInputCheckBox = cell.Controls(1)
Dim memberID As String = String.Empty
Dim sql as String = "SELECT * From Item WHERE ItemID =" + checkBox.Value
If checkBox IsNot Nothing And checkBox.Checked Then
Using cmd As New SqlCommand(sql, con, trans)
memberID = cmd.ExecuteScalar()
sqldatasource1.DeleteParameters("ItemID").DefaultValue = checkBox.Value
sqldatasource1.Delete()
sqldatasource2.DeleteParameters("memberID").DefaultValue = memberID
sqldatasource2.Delete()
End Using
End IF
Next
Trans.Commit()Catch ex As Exception
If trans IsNot Nothing Then
trans.Rollback()
End If
Return
End Try
I have a problem that when the error occurs, the transaction won't
rollback. I know this because if the error occurs in
sqldatasource2.deleteparameters, the sqldatasource1.deleteparameters
didn't rollback. How do I know it doesn't rollback? I open my database
table, and it shows that the record has been deleted by
sqldatasource1.deleteparameters.
I am using VWD and VB.net
many many thanks in advanced for your help !!
for example i have a table
and i update a column
using this query:
begin tran
update table1
set column1='something where column2='otherthing'
i dont give the "commit tran" statement
but when i give a select statement on table1
select column1 from table1
where column2='otherthing'
i find the change has been effected eventhough i have not given the commit tran command
can somebody explain me pls
Hello.
Your help is very much apperciated.
I am using SQL SERVER 2005, and framework 1.1.
I would like to create transaction, that:
inserts a record to one table1 -> selects the new record's PK(identity) ->
Inserts a record to table2, with FK (equals to the PK of table1).
Example:
Table1 (orders):
order ID (PK, identity)
Order Date
Table2(items ordered)
Item ID (PK)
OrderID (FK)
1. Insert a record to table1
2. Get the order ID (PK), of the new record, from table1.
3. Insert record into table2, using the order ID .
I don't know how to include SELECT in transactions.
Can it be done?
Thanks
Roy
I'm having a problem relating the SYSPROCESS to SYSCACHEOBJECTS table to get the last sql statement the transaction has executed. It seems that the SYSCACHEOBJECTS doesn't have a unique value so that we can easily identify where these records belong. However, if you will run the Microsoft Enterprise Manager and go to Management --> Current Activity --> Process Info and double click on the each process, you will view the last the sql statment the transaction executed. I don't know how microsoft have done this? Or, they have another table where they really store the last transaction sql statement executed.
Thanks in advance... (sorry for my poor english)
George
Im working on Partition purge process, where I need to specify following statement:
SET @cmd = 'ALTER PARTITION FUNCTION ' + @function_name + '() MERGE RANGE (@range)'
EXEC (@cmd);
SET @cmd1 = 'ALTER DATABASE '+ db_name()+ ' REMOVE FILE ' + @partition_file
EXEC (cmd1);
I want to put this statement in Begin Tran /Commit statement but getting error that it is not allowed. "ALTER DATABASE statement not allowed within multi-statement transaction"..what are my options to rollback in case there is a failure.
Hello:
I am implimenting the creation of sequence numbers .I use an insert proc on a table that generates the numbers using an identity field:
procedure usp_createidentity
begin transaction
insert into [tblOrderNumber] with default values
rollback ' done so no records in this table
select @OrderNumber = scope_identity()
I call this from another proc that inserts values into my order table:
procedure usp_Insert @OrderNumber int
as
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
EXEC usp_GetNewOrderNumber @OrderNumber = @OrderNumber output
INSERT INTO [dbo].[tblOrder] ([OrderNumber]) values (@orderNumber) ' inserts value from other stored proc
COMMIT TRANSACTION
END TRY
BEGIN CATCH
if (XACT_STATE() = -1)
ROLLBACK TRANSACTION
else
if (XACT_STATE() = 1)
COMMIT TRANSACTION
END CATCH
Here is the problem. When I run usp_Insert I get the following: Error 266 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
This refers to the usp_GetNewOrderNumber that is called inside the other proc as shown above.
The problem does not happen if I put each statement in usp_Insert in its own try/catch. transaction statements.
Maybe it has something to do with the rollback call in the usp_getneworder.
What do I need to do to get rid of this. problem and still run these within one try/catch trans statement set.
Thanks
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
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.
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.
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.
Dear All,
This is my first time posting in this forum, so forgive me if my post is out of place. If so, let me know.
I am trying to get started setting up a development server on Windows 2000. These are the componants I have installed so far:
- IIS 5
- MSDE Database Server
- .NET Framework 1.1
- MDAC (Microsoft Data Access Componants)
- ASP.NET Web Matrix
I have also downloaded a quickstart example, TimeTracker, to learn from.
Here is my problem: When I try to install TimeTracker, it does not detect a database connection. Also, when I try to connect to a database in Web Matrix, it says I need to have client tools installed. What are these and how to I get them?
Is there anyone that can give me a hand with this? Is it a matter of running the MSDE service? If so, how do you do this?
My apologies for not being more knowledgable, but thanks for helping me get started.
Sincerely,
Chris
I have just started programming using sql server. how do i get stated?
the basics are needed thanks for ur advice
I just installed SQL server 2005 with SP2 on Vista business.
When I open Management Studio, and try to Register a new server, I get this message:
Cannot connect to MyServer.
Additional information:
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could nor open a connection to SQL Server) (Microsoft SQL Server, Error: 1231)
When I open SQL Server 2005 Surface Area configuration and click on Add New Administrator, there are no Available privileges. Not sure if this is relevant.
Don't know what to do, any help will be appreciated.
I have just installed SQL Server at home. Had a bit of experience at work, but at home I have to get started from scratch.
Enterprise manager says 'Connection could not be established'. In this error it talkes about 'registration properties'. I am (by default ?) a member of SQL Server group, but I have no sub items, like databases. And how do I get to where I can edit stored procedures ?
As you can see I'm quite a novice, but with a little nudge I can would a lot out myself. But not all. If you can help me now, I'll be back soon.
Thank you so much.
Regards
Robert
i have roughly 10-15 access databases that i am trying to run through sql server express, i installed this thing thinking it would would "just work minus a little configuration", what a misconception.
so do tell, what do i need to do. i have sql server express running, it is configured to allow remote connections. i know the path's of these access databases. how do i attach them. i saw something about the command line interface but im not interested in that because of the number of databases i will need to manage, not to mention trying to add an additional one 6 months down the road and i will be lost all over again. i thought about downloading SQL Server Management Studio thinking it would be a "gui side" to the CLI version. while it was downloading, it said i need XX program, i go to that page, it says i need XX and XX first, it is turning into a pyramid scheme to install a billion programs.
so point blank, i want to share some access databases to my local lan, i have sql server express installed and running. what do i need to finish this out. the simplest and smallest footprint solution is needed.
all this because the program im using cant do remote databases. ugh.
Hi,
I am trying to make a small vb.net program in visual studio 2005 edition. I would like to have a SQL database with it.
I have the Microsoft Visual Studio and SQL Server 2005 installed on my PC. I am running XP but will be moving it all to a new pc running Vista next week.
How to I get the SQL database started ?
I am not using the express edition.
This is a repost of my post on the VB Express board.
I would like your opinions.
I am a network guy. I learned basic years ago. I can do some html edits as needed to change sites.
Today I would like to start the long process of learning VB with .net applications. I thought I could start with Office developer xp to learn VB. I have that package.
I also would like to be able to build SQL quaries and integrations or automate and manipulate data exports and imports later down the road.
Example: Export various order and customer information from a shopping cart and import it in to Quickbooks or MS Accounting.
First off I need to be able to build web sites and I know I want to grow into VB and SQL.
What should I do?
Learn HTML from web monkey?
Start with Office xp developer Tutorials?
Start with VB Express?
Buy a book?
Can someone tell me where to start and when to move to the next language?
What is the best resource to get to it, without the bla bla bla and a commnd/syntax refference?
How do I mix Html, CSS and .Net (VB)...in the learning process?
I would like to get the fast track for the long haul.
Thanks so much.
I am looking to learn about reporting with SQL Server for my company
Currently we have an applicaiton running on SQL Server 2000 and have SQL Server 2000 Reporting Services installed. However, I have VB Express and Web Development Express 2005.
How do I get started here? What versions "work" with what? Do I need to either upgrade the DB to SQL 2005 or find VB.net 2003, or can I use what I have to get started?
hello,
i own about a dozen interactive forums (see example at http://ConcealedCarryForum.com for one of them) that i host on a windows 2000 server dedicated server out of my home on a commercial cable backbone. the forums are ASP using Access DBs. we have outgrown the capabilities of Access and are getting complaints from DB lag during peak usage, and i must upgrade my DB to ensure the QOS end users get from my forums. i have no intention of switching over to php/MySQL/linux formats.
i tried unsuccessfully to get MySQL to play nicely in a windows environment, and recently learned that MSSQL offers free versions, the compact and the express. trouble is, i know absolutely nothing about DB management. Access is idiot proof so i have used it as long as i could. now i find myself in a scramble to learn how to create, employ, and use a more powerful DB.
for my intended usage (running about a dozen DBs on a dedicated server), will compact or express be the appropriate version to start learning? for my intended usage, is there any benefit to downloading/installing/learning the additional tools that are also available? if so, which tools? where do i learn how to create new DBs, and what tools do i need to do this? most tech papers ive seen on MSSQL assume a prior working knowledge of MSSQL DB management and im coming into this completely ignorant but willing to dedicate myself to learning.
answers, advice, etc. very much appreciated.
Hello,
I'm studying some articles about DMX and i have a question. DMX statements is only for prediction?
Can i create a model and run a classification/clustering/decision tree algorithm through DMX instead of prediction?Because all the examples i found are talking about prediction.
Are there any other sources i can study, so i can have a better understanding of what DMX does?
Thank you in advance!
I am using this program for the first time but as it has no direct interface on its own, I would like to ask if anyone could help me get a database up and running.Nothing fancy but simple and functional for my staff to use for basic csv searches and so forth. skywalkerza@highveldmail.co.za
Hi folks... this is part complaint, part request for help. I have an Access db that I'd rather were an sql db. Partly to learn sql and partly because all of the asp.net examples use sql and don't seem to work with access.
I installed VS2005 which includes sqlexpress, but there's no front end to it.. there's no way to "talk to" this sql2005. Can't seem to create tables, etc.
So, I installed SQL 2005 Development server... still no front end. Just some config tools that I presume I may care about some day but don't mean anything to me just yet.
I'm on XP Pro so can't install SQL 2005 Enterprise, but I discover a CTP called Management Studio Express. Well, we all know how robust CTPs are, nevertheless I install it and boy is it friendly.
At this point I feel as though I've missed The Big Thing that had I not missed I would be a lot further along.
Maybe the best way to express my question is to ask for what the heck I should have done in order to be learning sql at this point instead of having run out of time again.
Thanks for any insight.
Best,
Eric
PS... hmm, seems like this is mostly complaint... sorry.
Hi all
I have a job for run DTS packages,
My Job got failed due some reason,I corrected the problem a started Job again.Here problem is I for got to note the start time.Now I want find out from how long it is running or it what time it started.
Thanks in advance
Hi -
I need to start from the beginning with SQL Server 7 2005 Express. I want to get financial data and perform analysis in conjunction with Excel. I have downloaded the file from Microsoft and would like a resource for getting going. What is the best way to go?
Your help appreciated.
Doug
While I have been programming .Net for four years and T-SQL for about 12, I am a newbie to the whole SQL CLR thing. I have VS.NET 2003 on my machine, and I installed the client tools for SQL 2005 which also installs VS 2005 for SQL. Yet when I open VS 2005, it only has Data Analysis projects, but no Database projects. If I go into VS 2003 and try to import some of the assemblies I have found by googling 'SQL CLR' - VS 2003 won't let me add them.
Exactly what do I need to install so that I can get started? Do I need an instance of SQL server running on my machine? (I currently do not have a server running, only the client tools are installed)
TIA,
--Yonah
OK, so, from what people tell me I should be using @@Idnetity. What Im trying to do is insert data into a table, than revrieve the id from the new row in that table, than use that id in another sql insert statment later down the road. Currnetly the way im doing it is with a sql insert, than executte the scalar, than execute a reader, which is causeing me much grief.
This is my sql statment here:
Dim sqlInsert As New SqlCommand("INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@LName, @FName, @FullName)", sqlConn)
From what I understand, using @@Identity, I can retrive data from my insert statment without using a sperate select statment. Can you guys point me in the right direction, ive looked alot in the forms and such, most I found is either assuming you know what todo, or is security related.
Hello all. I have some questions that are probably pretty stupid but Ill ask anyway :)
It is important that I start down a path of understanding mssql 2000. I wanted some advice from experts as to what the best way to start would be. Is there a great book out there? Can I run a version locally that I can play with? Also, when using the database, are the commands the same for mssql and mysql? I ask because I searched for tutorials and a lot more mysql tutorials popped up.
Obviously I dont expect to become an expert in a few weeks but if I could start down the path I would be happy. Maybe learn how to do simple maintenance and management functions.
Any advice would be greatly appreciated.