.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

how store the index number till the commit transaction?

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server

hi fiends,

i have sp that store data in three tables, t1, t2, t3

till the transaction is commited no record will be inserted.

if i store a value to the t1 table, i can fetch the idx number using scope_idenity() but not physcial record will inserted till commit tranaction.

How do i save this idx number so that i can keep it till it commits transaction. imust store this globally becuase this sp will called by a client application consequtivly four times. but it does not commit tranaction till it complete four iterations. therefore i need to save the idx generated from the first call to it?

i was thinking about creating a temp table and store the idx in there. but

1. does create seperate temp table for each call tothe sp, (think interms of multi user enviorement)

2. what sort columns that i should create in thsi temp tabel

3. is temp table a good option?


View Complete Post

More Related Resource Links

Pass index number and find the char in a string?


How do i find the charactors in teh first index and the last index number of a string? 


Getting index number of GridView Control


 I am trying access the row index on this GridView, but the e.CommandArgument gives me a value of an empty string "" , so the the error handler kicks in. What do you think I am missing.


protected void GridView2_RowCommand(object sender, GridViewCommandEventArgs e)
        if( e.CommandName == "Delete" )
            int index = 0;
                index = Convert.ToInt32(e.CommandArgument);
            catch (Exception p)
                Response.Redirect("RbErrorPage.aspx?pagefrom=GridView2_RowCommandgMethodLine4122" + p.Message);

What's the Realistic Maximum Number of Index per Table


What is a good real world maximum(so i am not looking for this answer:http://blog.sqlauthority.com/2009/06/29/sql-server-maximum-number-of-index-per-table/) for the number of indexes on a table (table has around 50 columns and at the moment 3 indexes)

. I would like to add 3 more indexes on fields that are not used for joining but are searched upon lots of the time. Is this a good idea?

Nested transactions, rollback causes error "Transaction count after EXECUTE indicates that a COMMIT



I have a stored proc that does something that requires a transaction, and so I've put in the begin tran, commit, and rollback on error logic in it. That works as expected. But what if I now want to use the proc in the context of a larger transaction, such as calling it multiple times or doing other things as part of the same transaction? This seems impossible to achieve without either raising an error (throwing an exception) or having SQL Server do so on my behalf, rather unhelpfully.

For instance, I wanted to test a proc and reckoned a simple way to do so would be to start a transaction, execute the procedure, and roll back the transaction if the procedure didn't abort it:

begin transaction 
exec someProc
if XACT_STATE() = 1 rollback

This works if someProc doesn't encounter any errors (so it begins and commits a transaction and the transaction count is 1 at entry and exit of the proc), but crashes and burns if the procedure rolls back the transaction, producing SQL error

Msg 266, Level 16, State 2, Procedure ExecuteImport, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

I don't understand why that is. To my mind, it is perfectly normal that the transaction count can go from any level at entry to ze

Append incrementing index number to uploaded filenames if already exist


Can anyone please advise on the following? Essentially, if the file already exists, I want to append an index number to the end of the filename, incrementing upwards from 1. The following almost works, but adds numbers on as it increments upwards:

                            Dim Index As Integer = 1
                            While File.Exists(strThumbPath + strThumbName + strThumbExt)
                                strThumbName += Index.ToString
                                Index += 1
                            End While



I want it to do the following:


But I can't help feeling there is a better way to code this anyway, hence the post!

vb.net 2005 : In which Numeric Datatype I can store the number 3271557120


In my application I need to calculate = 195 * 256 * 256 * 256.

In which type of Numeric dataType I can use to store this number, because I had already tried with int64,Long and Decimal...... I dont know why it is always giving me Exception like "Arithmetic operation resulted in an overflow" in all the datatypes......

Clustered index gives rise to heavy transaction logging


We recently started purging old data from a table in our production system.  Since then we have experienced excessive transaction logging, system has gone from about 1-3MB every 15 minutes to 5-800MB in the same time with the same number of inserts (data rarely seems to change in this table). 

Table has a clustered index on an ID integer column that automatically increments by 1 for each new record. 

The purging of data is based on a column containing the time of insert plus a status column, so there could be IDs with low values remaining.  The purging job runs once per day and initially removed about 40% of the data in the table.  Now it removes one day every day and we keep a year of data.  The deletions do not seem to create more transaction logging than anything else.

Here are the column datatypes, note the column "data" (ouch) is of type image.  Design of table is from a third party and cannot be changed.

 [ID] [int] IDENTITY(1,1) NOT NULL,
 [jobindex] [nvarchar](100) NULL,
 [sortindex] [nvarchar](100) NULL,
 [metadata] [nvarchar](1000) NULL,
 [data] [image] NULL,
 [readflag] [int] NULL,
 [intjobid] [nvarchar](50) NULL,
 [extjobid] [nvarchar](50) NULL,
 [status] [nvarchar](50) NULL,
 [index1] [nvar

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing



We have Sharepoint 2007 SP1 databases on SQL Server 2005 SP3 x64. Users are uploading and viewing the documents with NO issues but we are frequently getting the below error in share point logs:

08/01/2010 08:21:33.11  w3wp.exe (0x0DA4)                        0x2168 Windows SharePoint Services    Database                       6f8g Unexpected Unexpected query execution failure, error code 266. Additional error information from SQL Server is included below. "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." Query text (if available): "DECLARE @@S uniqueidentifier; DECLARE @@DocId uniqueidentifier; DECLARE @@Level tinyint; DECLARE @@DocUIVersion int; DECLARE @@DoclibRowId int; DECLARE @@iRet int;DECLARE @DN nvarchar(256); DECLARE @LN nvarchar(128); DECLARE @FU nvarchar(260); SET @@Level=2 SET @@DocUIVersion =1 SET @@DocId='9FA36FDF-00FF-4810-92CE-A5ED5671A0C9' SET @@S='6780CE0A-2C92-463D-B86D-D8ACBB72E065'; EXEC @@DoclibRowId = proc_GenerateNextId 'C145E0B3-9F7C-4E0F-9644-7DB0AEE53EDF','1E9F697F-4032

SQL Server Database Transaction

A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:

Store and Display Images from MS Access Database Using C#

There is only the difference of coding syntax to stream the binary data of image content type to upload it into MS Access Database using C#/VB in ASP.Net 2.0

Namespaces required:-
using System.Data.OleDb;

OleDb is used to connect the web site forms with MS Access Database using Microsoft.Jet.OLEDB.4.0

Convert English to Arabic number without changing any regional settings in .net

Well, most applications that I worked with was multilingual that supports English UI and Arabic UI.

And one of the major issue that we have faced is displaying Arabic numbers without the need of changing the regional settings of the PC.

So the code below will help you to display Arabic number without changing any regional settings.

dynamically adding sql server columns based on number of files in fileuploader


I want to create a new table to sql database at runtime.  The column names would be "Name", "Date", "Event".  I would also like to have one more column name "Image" but the problem is that since the amount of images will vary, I want the application to count the amount of files in the Multiple fileuploader and then add a image column to the table for the respected amount of files. 

For example if i am uploading 3 files in the multiple fileuploader, the names should be "Name", "Date", "Event", "Image1", "image2", Image3"

I also would like that table name to be name dynamically from the textbox Name when the user enters the information.


I have the following code to make a new table, but I dont know how to name the fields at runtime like previously asked please help!!!


Dim objConn As New SqlConnection("Server=<servername>;uid=<userid>;pwd=<password>;database=master")


Embeb a variable number of videos


I need to provide some alternate content for my non-Flash users.

I have a directory of video files that is scanned using the DirectoryInfo() function, and I want to use the results to embed all files ending in ".mov" in QuickTime players on a web form.

How would you approach this? Right now I am using the count on the array of videos to determine how many times to repeat the embed code.  But this seems inelegant.

Is there a way to have a single QuickTime player.ascx control that gets duplicated according to the number of videos available for display?

Article commenting by line number


I'm wondering what's the best way to approach an online article system that allows users to comment by clicking on the line they are commenting about? Essentially the same way Adobe Acrobat works with comments where you can click anywhere and the text is saved for that location.

I'm assuming a mix of jQuery would be the crux of the interface. The system would need to save comments so that when another user logs in, they will also see the other comments but they can´t edit them - only add their own.

Any suggestions would be great.

How to store more than 4k bytes of data in SQL CE 4?


I need to store more than 4k of bytes in my SQLCE 4 database. How can I do that with the ASP.NET Web Pages 1.0?

1: The Microsoft.Data provider fails when I try to populate a parameter with more than 4k bytes.
2: Referencing SqlServerCe assembly and using SqlCeConnection/SqlCeCommand/SqlCeParameter throws exception:  SQL Server Compact is not intended for ASP.NET development.

How can I somehow map the generic DbType, which does not have NText as an option, to the ntext column in my database? (SqlCeParameter supports DbSqlType, which has ntext, but see problem #2)

The number of members in the conceptual type ... does not match with the number of members on the ob



I've added a scalar property to an entity and now ASP.NET Dynamic Data scaffolding throws this error:

The number of members in the conceptual type 'TrainingModel.Users' does not match with the number of members on the object side type 'TrainingModel.Users'. Make sure the number of members are the same.

This happens at the following line in global.asax:
model.RegisterContext(typeof(TrainingModel.TrainingEntities), new ContextConfiguration() { ScaffoldAllTables = true });

Please help!

huge number of data from database ... so how to minimize load time


Hi ...

I have used the above method to configure my crystal report ...

Its working fine , But i have huge number of data in database ....

So , it take long time to load the report ...

So , i have decided to show only the last 50 records inserted into the database , not all the 500 records to be loaded every time.

Plz help me with this issue  ...


Also , Plz explain me abt index legend in crystal report.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend