Last insert id

Hi

I am trying to import several master detail records from files to ms sql
server.
I have orders file and order_items file that has several rows for each
order.
If I insert programmatically these records how can find out which order ID
was the last inserted, so that I can attach the subsesquent row items to a
proper order.

I am quite new to ms sql server. I have used mysql a lot and there I could
use mysql_insert_id to find out the last autoincremented filed number.
I am looking for a similar method for ms sql server 2000.

TIA
George"George Hill" <ghill@.NOSPAM.com> wrote in message
news:LUf8b.5984$ZB4.5409@.reader1.news.jippii.net.. .
> Hi
> I am trying to import several master detail records from files to ms sql
> server.
> I have orders file and order_items file that has several rows for each
> order.
> If I insert programmatically these records how can find out which order ID
> was the last inserted, so that I can attach the subsesquent row items to a
> proper order.
> I am quite new to ms sql server. I have used mysql a lot and there I could
> use mysql_insert_id to find out the last autoincremented filed number.
> I am looking for a similar method for ms sql server 2000.
> TIA
> George

Assuming that you're using an IDENTITY column to generate the IDs, then the
scope_identity() function will give the last ID inserted. There are also
ident_current() and @.@.identity - see Books Online for an explanation - but
scope_identity() is probably the one you want.

Simon

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. SundarNo, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:

> Hi
> I want to know what is the last stored procedure executed in the server an
d
> by which user. Is it possible to find. (I think there is some log for this
)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. Sundar
No, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:

> Hi
> I want to know what is the last stored procedure executed in the server and
> by which user. Is it possible to find. (I think there is some log for this)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last date of access on a DB

Hi!
I've a many test servers with sql server 2000. On these server i've a lot of
DB for test, development and staging. I want to delete some of these DB, but
i'm not sure what of them are really being accessed. Is there any option
active script or whatever to know when was the last time a BD was accessed o
n
the server?
Any help will be appreciated.
FRivasFrivas wrote:
> Hi!
> I've a many test servers with sql server 2000. On these server i've a
> lot of DB for test, development and staging. I want to delete some of
> these DB, but i'm not sure what of them are really being accessed. Is
> there any option active script or whatever to know when was the last
> time a BD was accessed on the server?
> Any help will be appreciated.
> FRivas
You could try running a Profiler trace and capturing the Connect or
Disconnect events. The database ID is captured. Of course, this is only
the database that was originally connected to and may not be the
database where all the work took place.
Another option is to capture RPC:Completed and SQL:BatchCompleted
events. YOu don't have to bind the TextData to the trace if you want to
save space but you need to create a server-side trace to do this. This
trace captures all executed SQL on the server and you can easily see the
database id. If you were only concerned about specific databases you
could filter them out (once again I think you need to use a T-SQL
server-side trace for this type of filtering of multiple databases).
David Gugick
Imceda Software
www.imceda.com|||Turn on dbo use only or single user mode..and wait to see who complains?
Jeff
"Frivas" <Frivas@.discussions.microsoft.com> wrote in message
news:228FA9DF-7284-48A6-B4E1-FB9F3B50F1DD@.microsoft.com...
> Hi!
> I've a many test servers with sql server 2000. On these server i've a lot
of
> DB for test, development and staging. I want to delete some of these DB,
but
> i'm not sure what of them are really being accessed. Is there any option
> active script or whatever to know when was the last time a BD was accessed
on
> the server?
> Any help will be appreciated.
> FRivas
>

Last Checkpoint

Hi:
I am trying to find when last checkpoint was done by sql server. I don't
see this in sql server log. Is there any way to find out when last
checkpoint was done or frequency of checkpoints?
Thanks
I don't know about the last one but you can certainly use perfmon to view
when checkpoints occur.
Andrew J. Kelly SQL MVP
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:84109BB5-9167-49D9-87EF-8DC30FB08105@.microsoft.com...
> Hi:
> I am trying to find when last checkpoint was done by sql server. I don't
> see this in sql server log. Is there any way to find out when last
> checkpoint was done or frequency of checkpoints?
> Thanks

Last Checkpoint

Hi:
I am trying to find when last checkpoint was done by sql server. I don't
see this in sql server log. Is there any way to find out when last
checkpoint was done or frequency of checkpoints?
ThanksI don't know about the last one but you can certainly use perfmon to view
when checkpoints occur.
Andrew J. Kelly SQL MVP
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:84109BB5-9167-49D9-87EF-8DC30FB08105@.microsoft.com...
> Hi:
> I am trying to find when last checkpoint was done by sql server. I don't
> see this in sql server log. Is there any way to find out when last
> checkpoint was done or frequency of checkpoints?
> Thanks

Large TempDB

Hi
I have many reports running from a SQL Server DB, from Crystal Enterprise
reporting system. However I have noticed some performance degradation, and
I noticed that my TempDB had ballooned in size. Is there a way to reset
this? I tried Shrink, but this did not reduce the size of the DB?
Kind Regards
Ricky
(SQL2K-SP3A/WIN2K-Client/WIN2K-Server/Crystal Enterprise 9.0)http://www.aspfaq.com/show.asp?id=2446
David Portas
SQL Server MVP
--
"rikesh patel" <trikky@.rukp28.freeserve.co.uk> wrote in message
news:ubgD8RnxFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have many reports running from a SQL Server DB, from Crystal Enterprise
> reporting system. However I have noticed some performance degradation,
> and
> I noticed that my TempDB had ballooned in size. Is there a way to reset
> this? I tried Shrink, but this did not reduce the size of the DB?
> Kind Regards
> Ricky
> (SQL2K-SP3A/WIN2K-Client/WIN2K-Server/Crystal Enterprise 9.0)
>

Large number of reports

Hi

I have a large number of reports that are very similar in structure (only some query and report heading changes).

Creation of these is simple by creating a template or modifying the project file and saving as a new project. But whenever there is some change to be made (format or query change), it has to be made individually in all of the reports, which makes it very time consuming and error prone.

Can anyone give some suggestions as to how to automate this process, or some other way to improve maintainability in this situation.

Thanks

Shomik

Unfortunately RS does not support CSS which would be ideal. One solution is to make certain formatting options data driven. Seeing as most properties are expression based, you could have a styles table and have the report query the styles at runtime and set colours and fonts based on fields from a dataset. Any change would effect all supporting reports.|||

I have made the formatting options and even the static messages shown, database driven wherever possible. But still cannot do it everywhere, because of which even a position change of a textbox has to be made in all of the reports manually.

I tried using a template, but that is ok for creating the new report. I again have the same problem when it comes to maintenance.

Can anyone suggest any changes to the way I am doing this or another approach for tackling this issue ?

Thanks,

Shomik

Large log file

Hi:
I have a 10 GB log file that has remained exactly the same size even when
database was only 2GB. Now database is 9GB. I noticed that log file was not
alloed to grow in Enterprise Manager and that our recovery mode was full.
My Questions are
My SQL server recovery is set to full. What should I backup before I
statrt. I plan on backing the full directory before I start.
And if I switch the recovery mode to simple would it automatically delete or
shrink the log file. Our Server utilization is at constant 100% since SP4
(SQL 2000). Before it was 50% to 70% at most.
I am newbie to SQL but find it fascinating. I am also not fmiliar with Query
Analyzer.
Thanks
JM
JBM,
1. Backup the transaction log.
2. Perform a DBCC SHRINKFILE on the transaction log file
If you set the recovery mode to SIMPLE and clear the log you'll loose the
ability to recover those transaction since the last transaction log backup
in case of a failure. With the first option you'll still have that ability.
HTH
Jerry
"JBM" <JBM@.nowhere.com> wrote in message
news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
> Hi:
> I have a 10 GB log file that has remained exactly the same size even when
> database was only 2GB. Now database is 9GB. I noticed that log file was
> not alloed to grow in Enterprise Manager and that our recovery mode was
> full.
> My Questions are
> My SQL server recovery is set to full. What should I backup before I
> statrt. I plan on backing the full directory before I start.
> And if I switch the recovery mode to simple would it automatically delete
> or shrink the log file. Our Server utilization is at constant 100% since
> SP4 (SQL 2000). Before it was 50% to 70% at most.
> I am newbie to SQL but find it fascinating. I am also not fmiliar with
> Query Analyzer.
>
> Thanks
> JM
>
|||Hi,
If your database is not production then change the recovery model to simple
using below script:-
ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
Now use the below command to see if the log file is free
DBCC SQLPERF(LOGSPACE)
If the log is free then issue the below command to shrink the physical file.
DBCC SHRINKFILE('Logical_ldf_file_name',Truncateonly')
Since the LDF file is huge instead of giving Truncateonly option you can
shrink the file in small sizes say 1000 MB on each execution.
In this case replace Truncateonly with 1000.
Thanks
Hari
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
> JBM,
> 1. Backup the transaction log.
> 2. Perform a DBCC SHRINKFILE on the transaction log file
> If you set the recovery mode to SIMPLE and clear the log you'll loose the
> ability to recover those transaction since the last transaction log backup
> in case of a failure. With the first option you'll still have that
> ability.
> HTH
> Jerry
> "JBM" <JBM@.nowhere.com> wrote in message
> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>
|||Hi Jerry:
Many thanks for responding.
I am a newbie. Could you please tell me the exeact syntax.
DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
I would also like to know why i should not trucate the file and get rid of
dead transaction. Should I clear log once a week, once a day. What is an
ideal schedule. Does shrink get rid of dead (committed) transactions.
Thanks
JBM
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
> JBM,
> 1. Backup the transaction log.
> 2. Perform a DBCC SHRINKFILE on the transaction log file
> If you set the recovery mode to SIMPLE and clear the log you'll loose the
> ability to recover those transaction since the last transaction log backup
> in case of a failure. With the first option you'll still have that
> ability.
> HTH
> Jerry
> "JBM" <JBM@.nowhere.com> wrote in message
> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>
|||Harry:
My database is production. We run our business on it. What other
alternatives I have to regularly delete (truncate) dead (committed)
transactions.
Thanks
JBM
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OwirhyexFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> If your database is not production then change the recovery model to
> simple using below script:-
> ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
> Now use the below command to see if the log file is free
> DBCC SQLPERF(LOGSPACE)
> If the log is free then issue the below command to shrink the physical
> file.
> DBCC SHRINKFILE('Logical_ldf_file_name',Truncateonly')
> Since the LDF file is huge instead of giving Truncateonly option you can
> shrink the file in small sizes say 1000 MB on each execution.
> In this case replace Truncateonly with 1000.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
|||> I am a newbie. Could you please tell me the exeact syntax.
> DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
The command is documented in Books Online.

> I would also like to know why i should not trucate the file and get rid of dead transaction.
> Should I clear log once a week, once a day. What is an ideal schedule. Does shrink get rid of dead
> (committed) transactions.
The question you should ask yourself is whether you want to do both database backups and transaction
log backup or only database backups. If only database backups, set the database to simple recovery
model and SQL Server will empty the log for you. If full recovery model, the log is emptied each
time you do a transaction log backup. So, start by reading about backup and restore and recovery
models in Books Online and it will all be clear for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JBM" <JBM@.nowhere.com> wrote in message news:%23DLChvkxFHA.664@.tk2msftngp13.phx.gbl...
> Hi Jerry:
> Many thanks for responding.
> I am a newbie. Could you please tell me the exeact syntax.
> DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
> I would also like to know why i should not trucate the file and get rid of dead transaction.
> Should I clear log once a week, once a day. What is an ideal schedule. Does shrink get rid of dead
> (committed) transactions.
> Thanks
> JBM
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
>

Large log file

Hi:
I have a 10 GB log file that has remained exactly the same size even when
database was only 2GB. Now database is 9GB. I noticed that log file was not
alloed to grow in Enterprise Manager and that our recovery mode was full.
My Questions are
My SQL server recovery is set to full. What should I backup before I
statrt. I plan on backing the full directory before I start.
And if I switch the recovery mode to simple would it automatically delete or
shrink the log file. Our Server utilization is at constant 100% since SP4
(SQL 2000). Before it was 50% to 70% at most.
I am newbie to SQL but find it fascinating. I am also not fmiliar with Query
Analyzer.
Thanks
JMJBM,
1. Backup the transaction log.
2. Perform a DBCC SHRINKFILE on the transaction log file
If you set the recovery mode to SIMPLE and clear the log you'll loose the
ability to recover those transaction since the last transaction log backup
in case of a failure. With the first option you'll still have that ability.
HTH
Jerry
"JBM" <JBM@.nowhere.com> wrote in message
news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
> Hi:
> I have a 10 GB log file that has remained exactly the same size even when
> database was only 2GB. Now database is 9GB. I noticed that log file was
> not alloed to grow in Enterprise Manager and that our recovery mode was
> full.
> My Questions are
> My SQL server recovery is set to full. What should I backup before I
> statrt. I plan on backing the full directory before I start.
> And if I switch the recovery mode to simple would it automatically delete
> or shrink the log file. Our Server utilization is at constant 100% since
> SP4 (SQL 2000). Before it was 50% to 70% at most.
> I am newbie to SQL but find it fascinating. I am also not fmiliar with
> Query Analyzer.
>
> Thanks
> JM
>|||Hi,
If your database is not production then change the recovery model to simple
using below script:-
ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
Now use the below command to see if the log file is free
DBCC SQLPERF(LOGSPACE)
If the log is free then issue the below command to shrink the physical file.
DBCC SHRINKFILE('Logical_ldf_file_name',Trunc
ateonly')
Since the LDF file is huge instead of giving Truncateonly option you can
shrink the file in small sizes say 1000 MB on each execution.
In this case replace Truncateonly with 1000.
Thanks
Hari
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
> JBM,
> 1. Backup the transaction log.
> 2. Perform a DBCC SHRINKFILE on the transaction log file
> If you set the recovery mode to SIMPLE and clear the log you'll loose the
> ability to recover those transaction since the last transaction log backup
> in case of a failure. With the first option you'll still have that
> ability.
> HTH
> Jerry
> "JBM" <JBM@.nowhere.com> wrote in message
> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>|||Hi Jerry:
Many thanks for responding.
I am a newbie. Could you please tell me the exeact syntax.
DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
I would also like to know why i should not trucate the file and get rid of
dead transaction. Should I clear log once a week, once a day. What is an
ideal schedule. Does shrink get rid of dead (committed) transactions.
Thanks
JBM
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
> JBM,
> 1. Backup the transaction log.
> 2. Perform a DBCC SHRINKFILE on the transaction log file
> If you set the recovery mode to SIMPLE and clear the log you'll loose the
> ability to recover those transaction since the last transaction log backup
> in case of a failure. With the first option you'll still have that
> ability.
> HTH
> Jerry
> "JBM" <JBM@.nowhere.com> wrote in message
> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>|||Harry:
My database is production. We run our business on it. What other
alternatives I have to regularly delete (truncate) dead (committed)
transactions.
Thanks
JBM
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OwirhyexFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> If your database is not production then change the recovery model to
> simple using below script:-
> ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
> Now use the below command to see if the log file is free
> DBCC SQLPERF(LOGSPACE)
> If the log is free then issue the below command to shrink the physical
> file.
> DBCC SHRINKFILE('Logical_ldf_file_name',Trunc
ateonly')
> Since the LDF file is huge instead of giving Truncateonly option you can
> shrink the file in small sizes say 1000 MB on each execution.
> In this case replace Truncateonly with 1000.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
>|||> I am a newbie. Could you please tell me the exeact syntax.
> DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
The command is documented in Books Online.

> I would also like to know why i should not trucate the file and get rid of
dead transaction.
> Should I clear log once a week, once a day. What is an ideal schedule. Doe
s shrink get rid of dead
> (committed) transactions.
The question you should ask yourself is whether you want to do both database
backups and transaction
log backup or only database backups. If only database backups, set the datab
ase to simple recovery
model and SQL Server will empty the log for you. If full recovery model, the
log is emptied each
time you do a transaction log backup. So, start by reading about backup and
restore and recovery
models in Books Online and it will all be clear for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JBM" <JBM@.nowhere.com> wrote in message news:%23DLChvkxFHA.664@.tk2msftngp13.phx.gbl...[vbco
l=seagreen]
> Hi Jerry:
> Many thanks for responding.
> I am a newbie. Could you please tell me the exeact syntax.
> DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
> I would also like to know why i should not trucate the file and get rid of
dead transaction.
> Should I clear log once a week, once a day. What is an ideal schedule. Doe
s shrink get rid of dead
> (committed) transactions.
> Thanks
> JBM
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
>[/vbcol]