How to transfer a table between databases in SQL Server 2005 express

Hi,

In SQL Server 2005 Express I currently have 2 databases which I’m working on, my problem is I want to transfer a table from one of the databases to the other but don't know how to go about achieving this? I want the table structure and all the info held within the table transferred across. Any ideas are welcome.

hi,

using SSMSE, generate the DDL script of the table you like to "copy"...

execute the resulting script in the target database..

then insert all the rows in the original table like

INSERT INTO dbo.Table SELECT col1, col2, coln FROM originalDatabase.dbo.Table;

regards

How to track down this error

Hi,
We recently got an exception in the SOA-solution I'm working on and none of
the developers have seen this error before, and we are not sure where to
begin. We are running against a busy sql server 2005 and as I understand it
logging cannot be turned on. The exception message is as follows.
System.Data.SqlClient.SqlException: The current transaction cannot be
committed and cannot support operations that write to the log file. Roll
back the transaction.
at System.Data.SqlClient.SqlConnection.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()
The exception occures running ExecuteNonQuery against one of the stored
procedures in the database (which I'm not allowed to reproduce here). We
cannot identify anything wrong with the procedure.
Do anyone have any idea what a likely cause (or unlikely, yet possible) for
this error? Or ideas on how to track it down.
Thanks,
Morten WennevikOn Sep 26, 11:36 am, "Morten Wennevik" <MortenWenne...@.hotmail.com>
wrote:
> Hi,
> We recently got an exception in the SOA-solution I'm working on and none of
> the developers have seen this error before, and we are not sure where to
> begin. We are running against a busy sql server 2005 and as I understand it
> logging cannot be turned on. The exception message is as follows.
> System.Data.SqlClient.SqlException: The current transaction cannot be
> committed and cannot support operations that write to the log file. Roll
> back the transaction.
> at System.Data.SqlClient.SqlConnection.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()
> The exception occures running ExecuteNonQuery against one of the stored
> procedures in the database (which I'm not allowed to reproduce here). We
> cannot identify anything wrong with the procedure.
> Do anyone have any idea what a likely cause (or unlikely, yet possible) for
> this error? Or ideas on how to track it down.
> Thanks,
> Morten Wennevik
Morten,
The error is easy to reproduce:
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
-- at this time transaction is not committable
COMMIT
END CATCH
/*
Msg 3930, Level 16, State 1, Line 7
The current transaction cannot be committed and cannot support
operations that write to the log file. Roll back the transaction.
*/
if that is your case, you should change your code to:
BEGIN TRAN
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
-- at this time transaction is not committable
IF XACT_STATE() = -1
ROLLBACK
ELSE
COMMIT
END CATCH|||On Sep 26, 6:36 pm, "Morten Wennevik" <MortenWenne...@.hotmail.com>
wrote:
> Hi,
> We recently got an exception in the SOA-solution I'm working on and none =of
> the developers have seen this error before, and we are not sure where to
> begin. We are running against a busy sql server 2005 and as I understand= it
> logging cannot be turned on. The exception message is as follows.
> System.Data.SqlClient.SqlException: The current transaction cannot be
> committed and cannot support operations that write to the log file. Roll
> back the transaction.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateOb=je=ADct
> 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(DbAsyncRes=ult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> The exception occures running ExecuteNonQuery against one of the stored
> procedures in the database (which I'm not allowed to reproduce here). We
> cannot identify anything wrong with the procedure.
> Do anyone have any idea what a likely cause (or unlikely, yet possible) f=or
> this error? Or ideas on how to track it down.
> Thanks,
> Morten Wennevik
Check if the transaction did not violate any constraint and that you
did not try to insert wrong data type to one of the columns.|||Hi Alex,
Thanks for your suggestion. What we believe turned out to be the error was
a convert to int where the input parameter was '2007-09-27' instead of
'20070927'. When we got logging turned on, this generated an exception,
which was caught, and the catch block did indeed do RaiseError with an error
severity above 11, but somehow this didn't pass control back to .Net. When
the query much later tried to commit all the jobs it could not, since an
exception had occured. The actual Commit and Rollback is handled at a
higher level than inside the catch block.
Morten
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:1190825499.191973.201250@.g4g2000hsf.googlegroups.com...
> On Sep 26, 11:36 am, "Morten Wennevik" <MortenWenne...@.hotmail.com>
> wrote:
>> Hi,
>> We recently got an exception in the SOA-solution I'm working on and none
>> of
>> the developers have seen this error before, and we are not sure where to
>> begin. We are running against a busy sql server 2005 and as I understand
>> it
>> logging cannot be turned on. The exception message is as follows.
>> System.Data.SqlClient.SqlException: The current transaction cannot be
>> committed and cannot support operations that write to the log file. Roll
>> back the transaction.
>> at System.Data.SqlClient.SqlConnection.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()
>> The exception occures running ExecuteNonQuery against one of the stored
>> procedures in the database (which I'm not allowed to reproduce here). We
>> cannot identify anything wrong with the procedure.
>> Do anyone have any idea what a likely cause (or unlikely, yet possible)
>> for
>> this error? Or ideas on how to track it down.
>> Thanks,
>> Morten Wennevik
> Morten,
> The error is easy to reproduce:
> SET XACT_ABORT ON
> BEGIN TRAN
> BEGIN TRY
> SELECT 1/0
> END TRY
> BEGIN CATCH
> -- at this time transaction is not committable
> COMMIT
> END CATCH
> /*
> Msg 3930, Level 16, State 1, Line 7
> The current transaction cannot be committed and cannot support
> operations that write to the log file. Roll back the transaction.
> */
> if that is your case, you should change your code to:
> BEGIN TRAN
> BEGIN TRY
> SELECT 1/0
> END TRY
> BEGIN CATCH
> -- at this time transaction is not committable
> IF XACT_STATE() = -1
> ROLLBACK
> ELSE
> COMMIT
> END CATCH
>|||Hi Adi,
Thanks for your suggestion. What we believe turned out to be the error was
a convert to int where the input parameter was '2007-09-27' instead of
'20070927'. When we got logging turned on, this generated an exception,
which was caught, and the catch block did indeed do RaiseError with an error
severity above 11, but somehow this didn't pass control back to .Net. When
the query much later tried to commit all the jobs it could not, since an
exception had occured. The actual Commit and Rollback is handled at a
higher level than inside the catch block.
Morten
"Adi" <adicohn@.hotmail.com> wrote in message
news:1190825538.689369.104420@.d55g2000hsg.googlegroups.com...
On Sep 26, 6:36 pm, "Morten Wennevik" <MortenWenne...@.hotmail.com>
wrote:
> Hi,
> We recently got an exception in the SOA-solution I'm working on and none
> of
> the developers have seen this error before, and we are not sure where to
> begin. We are running against a busy sql server 2005 and as I understand
> it
> logging cannot be turned on. The exception message is as follows.
> System.Data.SqlClient.SqlException: The current transaction cannot be
> committed and cannot support operations that write to the log file. Roll
> back the transaction.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObje­ct
> 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()
> The exception occures running ExecuteNonQuery against one of the stored
> procedures in the database (which I'm not allowed to reproduce here). We
> cannot identify anything wrong with the procedure.
> Do anyone have any idea what a likely cause (or unlikely, yet possible)
> for
> this error? Or ideas on how to track it down.
> Thanks,
> Morten Wennevik
Check if the transaction did not violate any constraint and that you
did not try to insert wrong data type to one of the columns.

How to tell if AWE is actually working?

How can I verify that AWE is actually working? The server is SQL 2000
EE on W2KAS with the /pae switch.

I've allocated 5000Mb (5Gb) for SQL but Performance monitor says the
working set for the sqlservr process is only 91Mb (it started out at
around 87Mb a couple of days ago and seems to be creeping up).
"committed bytes" for the server is about 5.6 Gb out of ~6Gb so there's
a whole pile of memory going somewhere.

The error log says "Address Windowing Extensions enabled" (sp_config
indicates a "1" for this as well). The "max server memory" item says
5000 Mb for its "running" value so within the SQL Server things appear
to be correct.

The server does seem to be performing better but it's hard to decide if
that's because of AWE or just the fact that it was rebooted (it hadn't
been in a while).

- MartinCheck your AWE counters under Buffer Manager object in the Performance
monitor. You should see the memory maps & unmaps going on, if it is set up
right and is being utilized.

--
Anith|||Anith Sen wrote:
> Check your AWE counters under Buffer Manager object in the
Performance
> monitor. You should see the memory maps & unmaps going on, if it is
set up
> right and is being utilized.
> --
> Anith

Thanks, I tried that but the SQL perf counters don't show up in
Performance Monitor. There is an error message in the log: "Performance
monitor shared memory setup failed: -1".

I recall that this is a known problem that can be worked around by
stopping perfmon and bouncing the server but unfortunately this is a
Production server so that's not a trivial matter).

Is there another way to verify AWE is working?

- Martin|||To anyone still interested in this thread:

I found a post from the esteemed Kalen Delaney in
microsoft.public.sqlserver.server that referred to an excerpt from
"Inside SQL Server 2000's Memory Management Facilities" by Ken
Henderson of Microsoft...

"Note None of the tools you typically use to inspect application
memory use (Task Manager, Perfmon/Sysmon, etc.) show the amount of AWE
memory used by individual processes. There's no indication of the
amount of AWE memory used by each process, nor is this memory included
in the working set size reported for a given process."

This is exactly what I was concerned about (the working set size for
SQL Server wasn't getting bigger than 92Mb even though I've allocated
5Gb to it).

It seems this "under-reporting" of memory usage is just a quirk of AWE.
I'll just have to believe the SQL Server error log when it reports that
AWE is enabled (until I get the freaking SQL perfmon counters fixed so
that I can actually inspect the AWE-related counters!).

- Martinsql

how to sync mobile database and ms-access database?

Hi,
i am working with windows mobile Cf 2.0 and desktop. i got problem when i were trying to synd mobile database and ms-access database.
my requirement is online i want to transfar data records from mobile to desktop vice versa.
but how? i dont knwo any body would like to guide me.
i am ready to accepting yours valuable views.
waiting
bye
Rajat.maybe you should have a look at the ADS Wizard http://www.microsoft.com/downloads/details.aspx?FamilyId=B967347A-5DD0-445C-8A9F-AEA3DB9EC4BC&displaylang=en|||Hi,
i make more clear, what actualy i want to do.
i want to access mobile databse (.sdf) from desktop application which is build in .net.
or
i want to access desktop access databse (.mdb) from mobile application which is build in .net.
mobile may be pocket or smart phone.
thanks for all efforts
bye
Rajat|||

Access device database from desktop:

1. Copy SDF file from device to desktop using, say, RAPI.

2. Access this copy from desktop application.

3. Copy changed database back to device.

Access Jet database (which you call “Access”) from device:

1. Create Web Service to expose Jet database remotely.

2. Access this web service from device.

|||Hi thanks for concering,
i have tryed copying approch from device to desktop but i thing it is not systematic work.
i send u a link there say it can possible which we are discusing but i can not able to understand.
please take a look the link;

Pocket PC with SQL CE - The Code Project - .NET Compact Framework

may be it will help u to make that which i want and i am tring reqularly.
good luck you and myself also.
bye|||Ilya, do you have any tips on how to do this? I have an existing program that worked this way using cdb files and DESKTOPTODEVICE/DEVICETODESKTOP APIs. With our latest batch of iPAQs that come preloaded with Windows Mobile 5, I am unable to use our existing system and must rewrite our program. I'd like to keep the webservice as is and only change the local desktop calls to the mobile device.

Are there any links that you can provide (or sample code) for Steps 1, 2 & 3 when using sdf files and I assume SqlServerCe ?

Thank you,
David
|||hi,
i have made a dll which makes connection between M to D/ D to M.
but i dont know how to send it.
give me right way to send the file to you by the mail.
bye
keep connecting.|||you can zip & email it to

rocndav at hotmail dot com

Thank you,
David
|||Well I was able to get mine to work by using RAPI commands to copy the sdf file to my desktop and then use CE31 runtime to be able to read the sdf file. I was able to communicate with my existing web service.

These links really helps me out, hopefully this saves the next person some time:

Rapi sample:
http://msdn2.microsoft.com/en-us/library/ms837846.aspx

SQLCE 3.1:
http://blogs.msdn.com/mgaur/archive/2007/01/16/sql-server-compact-edition-3-1-launched.aspx

how to sync mobile database and ms-access database?

Hi,
i am working with windows mobile Cf 2.0 and desktop. i got problem when i were trying to synd mobile database and ms-access database.
my requirement is online i want to transfar data records from mobile to desktop vice versa.
but how? i dont knwo any body would like to guide me.
i am ready to accepting yours valuable views.
waiting
bye
Rajat.maybe you should have a look at the ADS Wizard http://www.microsoft.com/downloads/details.aspx?FamilyId=B967347A-5DD0-445C-8A9F-AEA3DB9EC4BC&displaylang=en|||Hi,
i make more clear, what actualy i want to do.
i want to access mobile databse (.sdf) from desktop application which is build in .net.
or
i want to access desktop access databse (.mdb) from mobile application which is build in .net.
mobile may be pocket or smart phone.
thanks for all efforts
bye
Rajat|||

Access device database from desktop:

1. Copy SDF file from device to desktop using, say, RAPI.

2. Access this copy from desktop application.

3. Copy changed database back to device.

Access Jet database (which you call “Access”) from device:

1. Create Web Service to expose Jet database remotely.

2. Access this web service from device.

|||Hi thanks for concering,
i have tryed copying approch from device to desktop but i thing it is not systematic work.
i send u a link there say it can possible which we are discusing but i can not able to understand.
please take a look the link;

Pocket PC with SQL CE - The Code Project - .NET Compact Framework

may be it will help u to make that which i want and i am tring reqularly.
good luck you and myself also.
bye|||Ilya, do you have any tips on how to do this? I have an existing program that worked this way using cdb files and DESKTOPTODEVICE/DEVICETODESKTOP APIs. With our latest batch of iPAQs that come preloaded with Windows Mobile 5, I am unable to use our existing system and must rewrite our program. I'd like to keep the webservice as is and only change the local desktop calls to the mobile device.

Are there any links that you can provide (or sample code) for Steps 1, 2 & 3 when using sdf files and I assume SqlServerCe ?

Thank you,
David
|||hi,
i have made a dll which makes connection between M to D/ D to M.
but i dont know how to send it.
give me right way to send the file to you by the mail.
bye
keep connecting.|||you can zip & email it to

rocndav at hotmail dot com

Thank you,
David
|||Well I was able to get mine to work by using RAPI commands to copy the sdf file to my desktop and then use CE31 runtime to be able to read the sdf file. I was able to communicate with my existing web service.

These links really helps me out, hopefully this saves the next person some time:

Rapi sample:
http://msdn2.microsoft.com/en-us/library/ms837846.aspx

SQLCE 3.1:
http://blogs.msdn.com/mgaur/archive/2007/01/16/sql-server-compact-edition-3-1-launched.aspx

how to suppress zeroes after decimal and whole no. should remain same

hi ,
but it is not working for whole number like ex:11120
it is fetching it as 11120. (with point at the end)
how to do that.
thanks,
hari.
>--Original Message--
>see following example:
>drop table test
>create table test(c1 decimal (15,5))
>insert into test values (3.567000)
>insert into test values (232233.567000)
>insert into test values (3.567)
>query:
>select c1,reverse(substring(reverse(cast(c1 as varchar
(25))) ,
>patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
>len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as
>varchar(25)))) - 1)
>)) 'no_zeros'
>from test
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>hi,
> i have a sql table field price and datatype is
>decimal 13(20,6).
> when i insert values to this field, values are being
>inserted correctly. i.e. 13.45 inserted as 13.45 and
>145.653 inserted as 145.653 only.
>But while fetching only the values are coming as
>13.450000,
>145.653000, because the datatype is decimal 13(20,6)
>with
>6 decimals. but i want 13.45, 145.653 as in the table.
> How to suppress the unwanted zeroes at the end of those
>numbers.
>any help.
thanks,
hari.

>.
>
check my other reply.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||You can try the following expression:
LEFT( c, LEN( c ) - PATINDEX( '%[1-9]%', REVERSE( c ) ) + 1 )
: where c is your column
Anith
|||thanks Steve,
it's working fine for removing zeroes and formating it
with commas. i changed it with combining ur previous and
current answers.
rgds,
hari.

>--Original Message--
>Here is another option:
>select
> parsename(c1,2)
>+ coalesce('.'+reverse(0+nullif(reverse(parsename
(c1,1)),0)),'')
>from test
>SK
>hari wrote:
>.
>

How to store GPS Coordinates in database, Which Data Type?

I am working on a program in VB 2005 in which i want to store and retrieve GPS coordinates. I am not sure which data type is the best to use to enter Latitude & Longitude numbers and maintain their proper integrity.

Like LAT ( N38 28.025' ) and LONG (W105 52.098' )

The numbers will be entered by the user and that format can be maintained, but how to re-enter & or insert them into the database using the same format is my real question.

I hope I have explained this right. The numbers in BOLD are what I need to maintain.

Thanks for any help in advance.

Steve

Hi Steve,

are you using SQL Server 2005 ? YOu could create your own data type for that ! I am not quite sure if since now anybody did already one for that reason but it would be worth a try searching in google for CLR +data type +sql server 2005 (and then the appropiate buzz words you probably know for your problem, like special names for the coordinates etc.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You could look at a SQL CLR User defined data type.The other option is to store as integers (or numerics) and then have a function that converts from the raw numeric value to the format above.

With a UDT you implement a parse method that allows you to interpret text into a value that you can store.

Be careful with UDTs though as changing them once in place is very difficult, as it is in use and so you can't just upgrade the base assembly.

How to store duration in database?

Hi all,

I want to find working duration between two datetimes in c#.
i'm using following code...

DateTime starttime = Convert.ToDateTime(Session["StartTime"]);
DateTime endtime = DateTime.Now;

TimeSpan duration = endtime - starttime;
DateTime period = new DateTime(duration.Ticks);

i want to store this duration in database through stored procedure, i've give datetime datatype to duration but it is giving error in conversion of TimeSpan to DateTime..

Please help... Thanks

You don't need to store this information in your database. You can retrieve this value by using DateDiff between two dates in SELECT statement, for example in seconds.

Datediff(second,startdate, enddate) as duration

Here is a link to how to get the duration and format it afterwards.

http://forums.asp.net/t/1185449.aspx