Difficulties Getting @@identity To Work In Transaction

Jan 3, 2006

I am having some problems wit a form where i need to insert a record into a table and then get the id of the record i just inserted and use it to insert a record in another table for a many to many relationship. The code is below (I cut out as much as i could to make it more readable).

The erro message i get is this:

Error saving file ATLPIXOFC.txt Reason: System.Data.SqlClient.SqlException: Prepared statement '(@FileName varchar(13),@ i' expects parameter @FileID, which was not supplied. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microvit_Document_Product_Document_Add_v2.insertDocumentToDB() in e:inetpubwwwrootMicrovitDocumentProduct_Document_Add_v2.aspx.vb:line 127 at Microvit_Document_Product_Document_Add_v2.btnInsert_Click(Object sender, EventArgs e) in e:inetpubwwwrootMicrovitDocumentProduct_Document_Add_v2.aspx.vb:line 37

 

Protected Sub insertDocumentToDB()

   Dim myConnString As String = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

   Dim myConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(myConnString)

   Dim myCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand

   Dim myTransaction As System.Data.IDbTransaction = Nothing

   myCommand.Connection = myConnection

   myCommand.Parameters.Add(New SqlParameter("@FileName", SqlDbType.VarChar))

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

   myCommand.Parameters.Add(New SqlParameter("@FileID", SqlDbType.Int, ParameterDirection.Output))

 

myCommand.Parameters("@FileName").Value = fuDocument.FileName

myCommand.Parameters("@ProductID").Value = ddlProduct.SelectedValue

 

 

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute 1st Command

'****************************************************************************

myCommand.CommandText = "INSERT INTO [Files] ([FileName) VALUES (@FileName); SELECT @FileID = @@Identity;"

myCommand.ExecuteNonQuery()

'****************************************************************************

' Execute 2nd Command

'****************************************************************************

'myCommand.Parameters("@FileID").Value = ddlProduct.SelectedValue

myCommand.CommandText = "INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@ProductID, @FileID)"

myCommand.ExecuteNonQuery()

 

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

End Try

End Sub

View 3 Replies


ADVERTISEMENT

@@ Identity Does Not Work ?

Oct 17, 2006

By standard i try my questions in SQL Mangment.

set nocount on
insert into [order]
(orderdate,deliverydate,status,Totalprice,customerID)
values(2006-02-23,2006-02-23,'N',10000,10)
(insert into temp (orderid)values(@@identity))

Major Error 0x80040E14, Minor Error 25569
> set nocount on
insert into [order]
(orderdate,deliverydate,status,Totalprice,customerID)
values(2006-02-23,2006-02-23,'N',10000,10)
(insert into temp (orderid)values(@@identity))
Invalid set option. [ Set option = nocount ]


Without Nocount:

insert into [order]
(orderdate,deliverydate,status,Totalprice,customerID)
values(2006-02-23,2006-02-23,'N',10000,10)
(insert into temp (orderid)values(@@identity))

Major Error 0x80040E14, Minor Error 25501
> insert into [order]
(orderdate,deliverydate,status,Totalprice,customerID)
values(2006-02-23,2006-02-23,'N',10000,10)
(insert into temp (orderid)values(@@identity))
There was an error parsing the query. [ Token line number = 4,Token line offset = 1,Token in error = ( ]

How do i get @@identity to work. I understand that @@identity only can be used in the sam insert session.

View 4 Replies View Related

Identity Doesn't Work

Nov 11, 1999

I'm working with mssql 6.5

I have an primary key column with Identity property.
And at the moment server doesn't insert the proper value to this column.

Error message is following

Msg 2601, Level 14, State 3
Attempt to insert duplicate key row in object 'Spot' with unique index 'XPKSpot'
Command has been aborted.

The datatype of this column is int, and number of rows ~17000.
If I execute select @@identity it returns null.

View 4 Replies View Related

Minimal Permission For Identity Insert On To Work

Jun 12, 2014

We are using sqlserver2005 at our liveserver. Due to some third party attacks which caused loss of data, we changed the sql user permission to only read,write and execute. Now, some of the sps in the db contain code to insert into identity column with line

SET IDENTITY_INSERT [tblName] ON
insert stmts...
SET IDENTITY_INSERT [tblName] OFF

This throwing error as

Cannot find the object "tblName" because it does not exist or you do not have permissions.

Which minimal permission can be given to get the above code work with identity insert on/off? We have removed the dbo permission due to external attacks.

View 2 Replies View Related

Enable Identity Insert Doesn't Work

Jan 4, 2008

Hi,

I'm trying to transfer data between two 2005 databases using the import/export method. I set the Enable Identity insert to true - edit mappings option - on all the tables but I still get the ID set back to 1. I want to add some live data to an altered database structure, and I'm assuming the import/export tool is the way to do this.

I'm using sql server management studio, SP1

Thanks, this is becoming really frustrating.

Peter Cat

View 6 Replies View Related

New To SQL - Having Difficulties Setting Up

May 24, 2008

Hello again
sorry to bother again but I am hvaing difficulty setting up the SQL server 2005. I have downloaded and installed the following

.Net Framework 2.0 Serv Pack 1
MS SQL server 2005
MS SQL server Native Client
MS SQL server Setup support
MS SQL server VSS Writer
AdventureWorksDB.msi as a sample database i believe

When I want to launch the program I point to Programs, that dispplays MS SQL Server 2005 which displays Configurations Tools which when pointed displays
1) SQL Server Configuration manager (has the icon with little hammer and folder whatever it is,
2) SQL Server Error and Usage reporting
3) SQL server surface area configuration
None of them is letting me to open a screen to see any tables and write a query.

What am i missing?

thanks

ted

View 4 Replies View Related

Difficulties With Join

May 27, 2008

Hello,
I'm trying to create a report that displays different sized customers in each office.
The tables look something like this:

client(id,size,representative)
representative(id,office)
office(id,name)

I would like the report to look something like this:

_______| all | <10 | 10-100 |
office1|
-----------------------------
office2|
------------------------------

I can get list of the offices and amount of all the clients like this:

SELECT office.name, COUNT(client.id) AS all
FROM office INNER JOIN
representative ON representative.office = office.id INNER JOIN
client ON client.representative = representative.id
group by office.name

But I just can't get my head around how to get rest of the information I want. If anyone has any suggestions how to proceed from here, I would be grateful.

View 2 Replies View Related

TSQL - @@Identity - Transaction

Aug 4, 2001

Is it possible to do the following?
Begin Transaction
Insert into some table
select @@identity as someid
insert into relatedtables(forgeignkey) values(someid)
Commit

Or does SQL not know the value of @@IDentity until the actual transaction commits, thus not allowing the select @@identity as someid?
How does one circumvent this issue

View 1 Replies View Related

ASP Data Access Difficulties

Aug 6, 2007



I'm using ASP pages to access a Microsoft SQL 2005 SP1 database server for information that populates dropdown menus. I ran into an interesting problem. We can query all existing data without a problem (all expected rows return and correctly populate the dropdowns).

I manually add a new row to the database table for a dropdown. I can manually query the database from the SQL2005 management tool, and the new records are included in the results.

I load the ASP page, and the newly added records never appear in the dropdown... only the old, existing data.

What could cause this? The queries from ASP are the same as the queries I make directly from the SQL server.

View 10 Replies View Related

Transaction Commit Doesn't Work

Sep 27, 2007

Could somebody also help me on the issue: I am using OLE DB to setup database with SQL CE 3.1, what i need is to flush the buffer to database file before program exit, I am using DBPROPVAL_SSCE_TCM_FLUSH and Transaction commit, but they don't work for me, My code is here:

1. the code to do transaction commit


// Access Transaction Interface

hr = pIDBCreateCommand->QueryInterface(IID_ITransactionLocal, (void **) &pTransLocal);

ULONG lTransLevel;

// Start the transaction

hr = pTransLocal->StartTransaction( ISOLATIONLEVEL_READCOMMITTED, 0, NULL, &lTransLevel );

// Execute the command with paramters.

hr = pICommandText->Execute(NULL, IID_NULL, &params, &cRowsAffected, NULL);

if( FAILED( hr ) )

{

goto Exit;

}

// commit

hr = pTransLocal->Commit( FALSE, XACTTC_SYNC, 0 );

pTransLocal->Release();



2. the code to set up property:


void LogDatabase::SetSessionProperty( IDBCreateSession *pISession )

{

DBPROPSET dbpropset[1]; // Property Set used to initialize provider

DBPROP sessionProps[1];

VariantInit(&sessionProps[0].vValue);

ISessionProperties *pISessionProperties = NULL;

//************** We Initial a session properties here *************

// Initialize a session object.

HRESULT hr = pISession->CreateSession(NULL, IID_ISessionProperties,

(IUnknown**) &pISessionProperties);

// Initialize the property to change commit mode.

sessionProps[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;

sessionProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;

sessionProps[0].vValue.vt = VT_I4;

sessionProps[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;



// Initialize the session property set.

dbpropset[0].guidPropertySet = DBPROPSET_SSCE_SESSION;

dbpropset[0].rgProperties = sessionProps;

dbpropset[0].cProperties = sizeof(sessionProps)/sizeof(sessionProps[0]);

// Set the session property.

hr = pISessionProperties->SetProperties(sizeof(dbpropset)/sizeof(dbpropset[0]),

dbpropset);

VariantClear(&sessionProps[0].vValue);

}

View 1 Replies View Related

Truncate Transaction Log Script Won't Work On Db Name With '-'

Feb 25, 2008

Hello all, I have a very simple script which I use to truncate and reclaim space on all the transaction logs in a SQL Server 2005 database. However, I have some Sharepoint db names I can't change that have dashes ('-') in the names, e.g., SharePoint_AdminContent_dc27334f-fb2d-4453-9764-5d8b730fb9e1. The script won't back up those databases because it has a problem with the dashes in the names. Does anyone have any thoughts on how I could modify the script to get it to work? Here is the script:

ALTER PROCEDURE [dbo].[SP_GlobalTruncate_transaction_logs]

AS

Set quoted_identifier off

DECLARE @dataname varchar(300)

DECLARE @dataname_header varchar(75)

DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases

WHERE name not in ('master', 'pubs', 'tempdb', 'model', 'northwind')

OPEN datanames_cursor

FETCH NEXT FROM datanames_cursor INTO @dataname

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status = -2)

BEGIN

FETCH NEXT FROM datanames_cursor INTO @dataname

CONTINUE

END

SELECT @dataname_header = 'Database ' + RTRIM(UPPER(@dataname))

PRINT ' '

PRINT @dataname_header

EXEC('BACKUP LOG ' + @dataname + ' WITH TRUNCATE_ONLY')

EXEC('DBCC SHRINKDATABASE (' + @dataname + ',TRUNCATEONLY)')

FETCH NEXT FROM datanames_cursor INTO @dataname

END

DEALLOCATE datanames_cursor

PRINT ''

PRINT ' '

PRINT 'Free space removed and transaction log truncated for each user database'
GO

And here is the error I get:
Database SHAREPOINT_ADMINCONTENT_DC27334F-FB2D-4453-9764-5D8B730FB9E1

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.

Msg 319, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.


Thanks so much for the help.

View 4 Replies View Related

SQL 7 Identity Alternative And Transaction Concurrency

Apr 3, 2001

One of the kernel components of our application(s) is the concept of using surrogate keys (aka system assigned keys [SAKs]) for significant tables. We chose early on not to use IDENTITY due to limitations of cross-DBMS compatibility and @@identity retrieval issues relating to trigger value resets.

We are now in the process of reengineering our own "System Counter" scheme. Our current scheme runs in the user's transaction space (not using an identity column, but a separate "counters" table) -- we're running into performance problems in high concurrency situations as the counters table is holding locks for the duration of a user's transaction (e.g. if the insert trigger on one row's insert inserts rows into a log table which must fetch counters first, the counter rows are locked until the original insert is committed).

Our best performing solution currently is using an extended SP which opens its own connection, gets the counter then closes the connection (running quick this way, running in its own transaction space, it doesn't cause concurrency blocks from the longer running outer user transaction). It appears to work very well (with "pooled connections" its taking appx. 2ms to run) and is easy to use (e.g. a "black box solution"). However we're still considering using IDENTITY to avoid the extra DB connections that are taken up from the middle-tier solution.

Does anyone have experiences working with this topic, that you could share?

View 1 Replies View Related

Regarding SSIS Implementation Difficulties In Migration

May 2, 2008



We would like to Share the BatchJobs functionality before going to the difficulties what we are facing in SSIS package Design and it's implementation.

BatchFile Process Steps:

1.Flat File is coming from the upstream server(DB2 main Frame Server) to the linux box in which the jobs are scheduled using crons.

2.The Flat file is validated whether having header and trailer information.The flat file name itself (eg. APPLWIC00077.dat ) will have it's sequence number ie 00077.This number will be checked against Flat file Header information Which also gives the sequence number information .If both number matches,The Loading will be initiated.Same way checking happens with Trailer.If anything goes wrong,mail will be sent and Loading will not happen.

3.The Job will process the coming flat file and Loading it into SQL Server Staging Table.

4.If loading is successful,then Some Sql server Procedure are called to validate the Staging Table Data,Filters,then move the Data from Staging table to Target table.

5.All Process or Steps are tracked,If anything goes wrong,Mail will be sent to Concerned Person.

Utilities Involved:

1. SQL Loader to Load the FlatFile Data into the SQL Server 2005 Database.

2. SQLPLUS to execute the PL/SQL procedures.

3. SENDMAIL or MAILX to send mails to externals.

Additional Information:

The SQL Loader Control File Splits the flatfile data by Position wise in order to load it into the particular column of the Staging Table.No column delimiters.Data are taken position wise (like column1 -> Sequence No , 1-3 -> column2 , 4-8 -> column2 )and checked against a condition By which the particular Staging Table is selected.





Difficulties We face in migration in regard to SSIS Package Design and it's Implementation:

1. We could Receive the flat file using FTP Task and we don't know how to open and validate the flat file data Like What we mentioned in STEP 2.

2. We could load the sample flat file which is properly delimited like {CR}{LF} ,{CR},{LF},semi -colon,comma,colon,tab,vertical bar .

If flatfile is messed up and data need to be processed by position wise (Scenario mentioned in Additional Information) ,We don't know how to implement this in SSIS package with the Limited delimiter option given.

FYI : Flat File columns are not fixed and ragged type.

3. We need logging information like howmany rows inserted, discarded,something in detail..How can we log these information using SSIS.This will help to send detailed mail to Application handler.It should be automated using SSIS.

4. How can we execute Procedures Using available SSIS tasks ( Executing many Procedures which is residing in Sql server 2005 using Single sql file as input to SSIS package)



Thanks in Advance!
Your input is priceless.

Regards,
Prabhu.M

View 4 Replies View Related

Problem Retrieving @@Identity When Inside A Transaction.

Aug 22, 2005

I'm using transactions with my SqlConnection (  sqlConnection.BeginTransaction() ).

I do an Insert into my User table, and then subsequently use the
generated identity ( via @@identity ) to make an insert into another
table. The foreign key relationship is dependant on the generated
identity. For some reason with transactions, I can't retrieve the
insert identity (it always returns as 1).  However, I need the
inserts to be under a transaction so that one cannot succeed while the
other fails. Does anyone know a way around this problem?

So heres a simplefied explanation of what I'm trying to do:

Begin Transaction
Insert into User Table
Retrieve Inserted Identity (userID) via @@Identity
Insert into UserContact table, using userID as the foreign key.
Commit Transaction.

Because of the transaction, userID is 1, therefore an insert cannot be
made into the UserContact table because of the foreign key constraint.
I need this to be a transaction in case one or the other fails. Any ideas??

View 5 Replies View Related

Difficulties With SQL Server 6.5 SP5a Cluster Wizard

Mar 26, 1999

Has anyone gotten clustering to work via the Cluster Wizard in SP5a?

I've got two ProLiant 5500's with the Compaq Clustering S/100 option. I've done a fresh install of NT Enterprise 4.0 SP3, then installed clustering with the Compaq S/100 drivers... everything fails over nicely, can make shares off the virtual server, etc. I then install SQL Server 6.5 EE, then apply SP5a -- so far, so good.

Now I try to run the clustering wizard. The wiz prompts me for my SA password. I enter it, and it gives me a message box that it can't start or log onto the database... but when I look in the event log it appears to have logged on successfully with a Non-trusted connection.

Thanks for the help...

View 1 Replies View Related

Transaction Log Size Too Big, DBCC Shrinkfile Doesn&#39;t Work

Oct 30, 2000

What is the best way to control Transaction log sizes?? The logs keep growing and when I manually truncate
them and use the dbcc shrinkfile command, it doesn't want to shrink it to the specified size. In some cases,
our data file is smaller than the log file. It'll have a Gig of space allocation but only contain 40 megs of data.
Any suggestions on how I can shrink the log file??

View 3 Replies View Related

Transact SQL :: Multiple Update Top On Commit Transaction Doesn't Work

Jul 10, 2015

I have this sql stored procedure in SQL Server 2012:

ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes]
@newBatches as dbo.CreateBatchList READONLY
, @productId int
, @cLevelRatio int
, @nLevelRatio int
AS
set nocount on;

[Code] ....

View 4 Replies View Related

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

Jan 7, 2007

Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...

SQL Server Express edition

Visual Studios 2005

Jet 4.0 newest upgrade

.Net Framework 2.0 (or should I use 3.0)

VS2005 Security upgrade

Anything else I need for just creating a database for my VS2005 Visual Basic project?

I was trying to use MS Access as my backend db but would like to try SQL Express



Thank you, Mark





View 7 Replies View Related

Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

May 31, 2008

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

View 1 Replies View Related

SSIS, Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Feb 22, 2007

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.

View 3 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Feb 6, 2007

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?!

View 1 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Dec 22, 2006

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.

View 8 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction. (HELP)

Jan 8, 2008

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.


View 2 Replies View Related

Last GASP On Insert Row In Table With Identity Field, And Get New Identity Back ?

Jul 9, 2006

While I have learned a lot from this thread I am still basically confused about the issues involved.

.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.

To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.

1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.

2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!

3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?



TIA,



Barkingdog

View 10 Replies View Related

Insert Row In Table With Identity Field, And Get New Identity Back

Jun 30, 2006

I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).

What is the most direct way to do this in SSIS?



TIA,



barkingdog



P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

View 12 Replies View Related

T-SQL (SS2K8) :: How To Update Identity Column With Identity Value

Jan 25, 2015

I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.

Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?

View 6 Replies View Related

Identity...I Need To Get The Last (or Highest Number In Identity Column)...

Sep 19, 2005

Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint

View 2 Replies View Related

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

Jun 19, 2008

Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View 6 Replies View Related

How To Use Identity On Non-identity Column (with Concurrence)

Aug 1, 2014

I'm working with a third-party database (SQL Server 2005) and the problem here is the following:

- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).

- The PK of T is an Integer.

Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.

Considering that I can't change the PK, what is the best approach to solve this problem?

To sum up:

* I need to have processes in parallel inserting on T

* I can't change anything on T

* The PK is NOT an Identity

View 4 Replies View Related

SQL Server Admin 2014 :: Restore Lost Transaction From Transaction Log File

Jun 10, 2015

I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.

View 8 Replies View Related

I Need Away To Show The Pending Transaction From Transaction Replication In A User Friendly Format.

Jul 11, 2007



I want to list out the pending transaction for transaction replication by publication.



Help needed.

View 1 Replies View Related

TRANSACTIONS In SSIS (error: The ROLLBACK TRANSACTION Request Has No Corresponding BEGIN TRANSACTION.

Nov 14, 2006

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.

Anyone know of the reason?

View 1 Replies View Related

Analysis :: Find Amount Distribution Across Different Transaction Types Under Spend Transaction

Jul 27, 2015

I created a Calculated measure in cube something like this : ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount]). To get only spend transactions. Now, I want to slice this measure with same hierarchy to find the amount distribution across different transaction types under spend transaction. But this query behaving like the measure doesn't have relation with measure.

you can think this as below query:
WITH
MEMBER SPEND AS ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount])
SELECT NON EMPTY {SPEND} ON 0
,NON EMPTY ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent]) ON 1
FROM [CUBE]

View 6 Replies View Related







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