Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts

Thursday, March 22, 2012

converting a databases transaction log into a human readable file

SQL SERVER 2000
Hello All,
Is there a method for converting a databases transaction log into a human
readable file (SQL) for the purposes of conducting an audit or debugging a
particular set of circumstances?
Thanks in advance
Russell J Morgan
Hi
You need to buy 3rd party tools to do this
www.logpi.com and http://www.lumigent.com
Regards
Mike
"Russell J Morgan" wrote:

> SQL SERVER 2000
> Hello All,
> Is there a method for converting a databases transaction log into a human
> readable file (SQL) for the purposes of conducting an audit or debugging a
> particular set of circumstances?
> Thanks in advance
> Russell J Morgan
>
>

converting a databases transaction log into a human readable file

SQL SERVER 2000
Hello All,
Is there a method for converting a databases transaction log into a human
readable file (SQL) for the purposes of conducting an audit or debugging a
particular set of circumstances?
Thanks in advance
Russell J MorganHi
You need to buy 3rd party tools to do this
www.logpi.com and http://www.lumigent.com
Regards
Mike
"Russell J Morgan" wrote:

> SQL SERVER 2000
> Hello All,
> Is there a method for converting a databases transaction log into a human
> readable file (SQL) for the purposes of conducting an audit or debugging a
> particular set of circumstances?
> Thanks in advance
> Russell J Morgan
>
>

converting a databases transaction log into a human readable file

SQL SERVER 2000
Hello All,
Is there a method for converting a databases transaction log into a human
readable file (SQL) for the purposes of conducting an audit or debugging a
particular set of circumstances?
Thanks in advance
Russell J MorganHi
You need to buy 3rd party tools to do this
www.logpi.com and http://www.lumigent.com
Regards
Mike
"Russell J Morgan" wrote:
> SQL SERVER 2000
> Hello All,
> Is there a method for converting a databases transaction log into a human
> readable file (SQL) for the purposes of conducting an audit or debugging a
> particular set of circumstances?
> Thanks in advance
> Russell J Morgan
>
>sqlsql

Saturday, February 25, 2012

Convert returned value to Integer

I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.

----------------
DECLARE @.TransactionID int
<-- INSERT statement (after inserting the record, select the identity ID) --->
Select @.TransactionID = @.@.Identity
RETURN
----------------

.
.
.
Dim transactionID As Integer
connection.Open()
Dim reader As SqlDataReader
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim p3 As New SqlParameter("@.TransactionID", SqlDbType.Int)
p3.Direction = ParameterDirection.Output
transactionID = Convert.ToInt16(p3.Value)
connection.Close()
.
.
.

I wanna retrieve the transactionID of the newly inserted record so that I can use it in my next step of inserting a new record to another table which has reference to the transactionID as a foreign key. However, I encountered error and suspect that it is due to the conversion of the output to Integer as it worked when I tested using dummy Integers.

I tried many different ways of conversion but couldn't resolve the error. Could anyone help?

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.


|||

ndinakar:

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.


hmm.. pardon me as this is my first time with asp.net

In the article:

myCommand.Parameters.Add(New SqlParameter("@.ProductId",SqlDbType.Int))

myCommand.Parameters.Direction = ParameterDirection.Output

The line in red doesn't work. I guess I'm using asp.net 2.0, that's why... so I changed to something like

command.Parameters("@.ProductID").Direction = ParameterDirection.Output

Yupz.. I'm retrieving through output parameters, I tried ExecuteReader but still couldn't work.

Actually I have these two tables for a shopping cart project - OrderTransaction and OrderDetails

OrderTransaction stores general information of TransactionID, Status, ShippingAddress etc of an order. OrderDetails stores details of a particular transaction which has foreign keys references to the Ids of OrderTransaction, Customers and Products table.
Hence, the reason for retrieving the TransactionID is so that I can create records for the items in the shopping cart in the OrderDetails for the particular transaction.

Code:

Dim transactionID As Integer
Dim connection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
Dim command As SqlCommand = New SqlCommand("AddOrders", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@.CustomerID", Context.Session("myID"))
command.Parameters.AddWithValue("@.DateCreated", DateTime.Now.ToString())
command.Parameters.AddWithValue("@.Verified", 0)
command.Parameters.AddWithValue("@.Cancelled", 0)
command.Parameters.AddWithValue("@.CustomerName", nameTB.Text)
command.Parameters.AddWithValue("@.ShippingAddress", shippingaddressTB.Text)
command.Parameters.AddWithValue("@.CustomerEmail", emailTB.Text)
command.Parameters.Add("@.TransactionID", SqlDbType.SmallInt)
command.Parameters("@.TransactionID").Direction = ParameterDirection.Output
connection.Open()
command.ExecuteNonQuery()
transactionID = Convert.ToInt16(command.Parameters("@.TransactionID").Value)
connection.Close()

Stored Procedure:

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50)
)

AS

DECLARE @.TransactionID int

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated), @.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)

Select @.TransactionID = @.@.Identity

RETURN

The error is
"Procedure or function AddOrders has too many arguments specified."
Line 161: command.ExecuteNonQuery()

I think that's because I did something wrong in the lines ingreen.Appreciate if anyone helps. Thanks in advanced.
|||(1) The error is exactly what it says. You are trying to add @.TransactionId as a parameter when you havent declared it as a parameter in your stored proc. modify your stored proc as follows:
(2) SCOPE_IDENTITY() is more efficient than @.@.IDENTITY. Check out books on line for more explanation.
(3) Since the value you are returning is through OUTPUT parameters, use ExecuteNonQuery.

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50),
@.TransactionId INT OUTPUT
)

AS

BEGIN
SET NOCOUNT ON

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated),@.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)
SELECT @.TransactionID= SCOPE_IDENTITY()

SET NOCOUNT OFF
END

Sunday, February 12, 2012

Convert DB2 "FOR UPDATE" in SQL Server

Hello,
In DB2, we can do FOR UPDATE selection, in order to lock the records until the transaction is completed. e.g.
select field1, field2, field3
from table
FOR UPDATE;
How can I perform the equivalent feature in SQL Server?
Thanks!This is an exceptionally bad way to do things. It allows the "lunchtime lock" where a user has data locked for ages, preventing others from getting any real work done. The best way to simulate this feature in SQL Server is to retrieve the data, then turn off or unplug the network cable from the server.

-PatP|||Pat...Pat...Pat...

You've worked on big iron before....|||You've worked on big iron before....I know, I'll admit it... but I keep trying to prevent those bad habits from spreading!

-PatP|||I sincerely need helps for this question. And I am not fooling around here.