Hi All
I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).
I have been using the @.@.Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an after insert Trigger has been added to the table being updated.
Now the @.@.Identity is returning the identity value of the trigger that was called instead of the original table insert.
Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).
Thank in advance.
Eamon.Look at SCOPE_IDENTITY and/or IDENT_CURRENT
Regards,
hmscott|||Thank you very much hmscott. SCOPE_IDENTITY works a treat!!!
Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
cannot view existing database in management studio !!!
Hi
? have sharepoint 2007 server and everthing is ok
it is working
? can view database name in sharepoint central administration
but ? cannot view same database in sql management studio
what is the problem
how can i view it
Thanks
Are you connecting to the correct server instance? It should be <computername>\SharePoint. If you are connecting to another instance, this might explain your problem.|||no no
? m sure it is correct instance computername\sharepoint
|||SQL Server 2005 is security sensitive which means that you will only able to see those database which you have access to.Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
Tuesday, March 20, 2012
/L*v C:\temp\logfile
Hi!
I found that using /L*v with the setup procedure creates a verbose log
file. Is there any way to add a verbose log file to an existing database?
Stefan
hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfqz6rois9ddfw@.news.individual.de
> Hi!
> I found that using /L*v with the setup procedure creates a verbose
> log file. Is there any way to add a verbose log file to an existing
> database?
> Stefan
/L*v is a setup parameter, which enables the setup logging features...
I do not understand your requirements regarding "add a verbose log file to
an existing database"...
all SQL Server databases do have a transaction log file at least, and the
logging mode is dependent to the recovery setting, as described in
http://msdn.microsoft.com/library/de...kprst_6rqr.asp
...
can you please elaborate your requirements?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 11:05:25 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:
> hi Stefan,
> "Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
> news:opsfqz6rois9ddfw@.news.individual.de
> /L*v is a setup parameter, which enables the setup logging features...
> I do not understand your requirements regarding "add a verbose log file
> to an existing database"...
> all SQL Server databases do have a transaction log file at least, and the
> logging mode is dependent to the recovery setting, as described in
> http://msdn.microsoft.com/library/de...kprst_6rqr.asp
> ..
> can you please elaborate your requirements?
I am having troubles when connecting to MSDE 1.0 after installing XP SP2:
The inital connection takes an eternity (10 seconds on lightning fast
machines, up to 90 seconds on my older working machine). In this time,
sqlsvr.exe causes 100% processor load and a lot of I/O stress. I'd simply
like to find out what is going on.
After the initial connection lag, operation continues normally.
We connect through Delphi 5 using ADO.
any pointers appreciated (upgrading to D7 is not an option; it's not my
decision)
Stefan
|||hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfq3c3uks9ddfw@.news.individual.de
> I am having troubles when connecting to MSDE 1.0 after installing XP
> SP2: The inital connection takes an eternity (10 seconds on lightning
> fast machines, up to 90 seconds on my older working machine). In this
> time, sqlsvr.exe causes 100% processor load and a lot of I/O stress.
> I'd simply like to find out what is going on.
> After the initial connection lag, operation continues normally.
> We connect through Delphi 5 using ADO.
> any pointers appreciated (upgrading to D7 is not an option; it's not
> my decision)
> Stefan
XP sp2 causes a lot of connection issues, see
http://www.michna.com/kb/WxSP2.htm for instance...
for related SQL Server issues please have a look at
http://support.microsoft.com/default.aspx?kbid=841249
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 13:04:27 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:
> XP sp2 causes a lot of connection issues, see
> http://www.michna.com/kb/WxSP2.htm for instance...
> for related SQL Server issues please have a look at
> http://support.microsoft.com/default.aspx?kbid=841249
Thanks for the pointers, Andrea. We've gone through the second one
already; I'll double check if we missed something there.
Do you think that an upgrade to MSDE 2000 would cure some issues?
Stefan, off reading
|||hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfq89i1ws9ddfw@.news.individual.de
> ..
> Thanks for the pointers, Andrea. We've gone through the second one
> already; I'll double check if we missed something there.
> Do you think that an upgrade to MSDE 2000 would cure some issues?
> Stefan, off reading
actually not, but MSDE 2000 can be worth upgrading becouse several
improvements in the database engine...
I only installed a Virtual Machine of WinXP sp 2 but had (fortunately) no
related issue to connectivity..
opened the TCP port required for network connections and enlisted a range of
subnet IP addresses... all is ok..
... and I hope to be that lucky when upgrading the real physical machine
=;-D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 15:39:23 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:
> hi Stefan,
> "Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
> news:opsfq89i1ws9ddfw@.news.individual.de
> actually not, but MSDE 2000 can be worth upgrading becouse several
> improvements in the database engine...
Yes; as long as we can easily handle upgrading issues, like the password
in all our connection strings.
> I only installed a Virtual Machine of WinXP sp 2 but had (fortunately) no
> related issue to connectivity..
> opened the TCP port required for network connections and enlisted a
> range of subnet IP addresses... all is ok..
> ... and I hope to be that lucky when upgrading the real physical machine
> =;-D
I'd rather set up a complete testing machine beforehand
Good luck and thanks,
Stefan
I found that using /L*v with the setup procedure creates a verbose log
file. Is there any way to add a verbose log file to an existing database?
Stefan
hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfqz6rois9ddfw@.news.individual.de
> Hi!
> I found that using /L*v with the setup procedure creates a verbose
> log file. Is there any way to add a verbose log file to an existing
> database?
> Stefan
/L*v is a setup parameter, which enables the setup logging features...
I do not understand your requirements regarding "add a verbose log file to
an existing database"...
all SQL Server databases do have a transaction log file at least, and the
logging mode is dependent to the recovery setting, as described in
http://msdn.microsoft.com/library/de...kprst_6rqr.asp
...
can you please elaborate your requirements?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 11:05:25 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:
> hi Stefan,
> "Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
> news:opsfqz6rois9ddfw@.news.individual.de
> /L*v is a setup parameter, which enables the setup logging features...
> I do not understand your requirements regarding "add a verbose log file
> to an existing database"...
> all SQL Server databases do have a transaction log file at least, and the
> logging mode is dependent to the recovery setting, as described in
> http://msdn.microsoft.com/library/de...kprst_6rqr.asp
> ..
> can you please elaborate your requirements?
I am having troubles when connecting to MSDE 1.0 after installing XP SP2:
The inital connection takes an eternity (10 seconds on lightning fast
machines, up to 90 seconds on my older working machine). In this time,
sqlsvr.exe causes 100% processor load and a lot of I/O stress. I'd simply
like to find out what is going on.
After the initial connection lag, operation continues normally.
We connect through Delphi 5 using ADO.
any pointers appreciated (upgrading to D7 is not an option; it's not my
decision)
Stefan
|||hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfq3c3uks9ddfw@.news.individual.de
> I am having troubles when connecting to MSDE 1.0 after installing XP
> SP2: The inital connection takes an eternity (10 seconds on lightning
> fast machines, up to 90 seconds on my older working machine). In this
> time, sqlsvr.exe causes 100% processor load and a lot of I/O stress.
> I'd simply like to find out what is going on.
> After the initial connection lag, operation continues normally.
> We connect through Delphi 5 using ADO.
> any pointers appreciated (upgrading to D7 is not an option; it's not
> my decision)
> Stefan
XP sp2 causes a lot of connection issues, see
http://www.michna.com/kb/WxSP2.htm for instance...
for related SQL Server issues please have a look at
http://support.microsoft.com/default.aspx?kbid=841249
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 13:04:27 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:
> XP sp2 causes a lot of connection issues, see
> http://www.michna.com/kb/WxSP2.htm for instance...
> for related SQL Server issues please have a look at
> http://support.microsoft.com/default.aspx?kbid=841249
Thanks for the pointers, Andrea. We've gone through the second one
already; I'll double check if we missed something there.
Do you think that an upgrade to MSDE 2000 would cure some issues?
Stefan, off reading
|||hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfq89i1ws9ddfw@.news.individual.de
> ..
> Thanks for the pointers, Andrea. We've gone through the second one
> already; I'll double check if we missed something there.
> Do you think that an upgrade to MSDE 2000 would cure some issues?
> Stefan, off reading
actually not, but MSDE 2000 can be worth upgrading becouse several
improvements in the database engine...
I only installed a Virtual Machine of WinXP sp 2 but had (fortunately) no
related issue to connectivity..
opened the TCP port required for network connections and enlisted a range of
subnet IP addresses... all is ok..
... and I hope to be that lucky when upgrading the real physical machine
=;-D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 15:39:23 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:
> hi Stefan,
> "Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
> news:opsfq89i1ws9ddfw@.news.individual.de
> actually not, but MSDE 2000 can be worth upgrading becouse several
> improvements in the database engine...
Yes; as long as we can easily handle upgrading issues, like the password
in all our connection strings.
> I only installed a Virtual Machine of WinXP sp 2 but had (fortunately) no
> related issue to connectivity..
> opened the TCP port required for network connections and enlisted a
> range of subnet IP addresses... all is ok..
> ... and I hope to be that lucky when upgrading the real physical machine
> =;-D
I'd rather set up a complete testing machine beforehand
Good luck and thanks,
Stefan
Monday, March 19, 2012
.SQL file
I have install.sql, that i want to run...so I can add to my existing asp.net 2.0 project. How do can I add the table to my database using that file (contents below)? Database resides in SQL Server 2000.
create table[dbo].[CustomProfile](
[UserID] [uniqueidentifier]not null,
[FirstName] [nvarchar](25)not null,[LastName] [nvarchar](25)not null,
[Address1] [nvarchar](75)not null,[Address2] [nvarchar](75)null,
[City] [nvarchar](50)not null,[State] [nvarchar](2)not null,
[Zip] [nvarchar](10)not null,[Phone] [nvarchar](12)null,
[ProfileVersion] [int]not null,[LastUpdatedDate] [datetime]not null
)on[PRIMARY]
alter table[dbo].[CustomProfile]add
constraint[PK_CustomProfile_UserProfile]primary key clustered
(
[UserID]
)with(IGNORE_DUP_KEY =OFF)on[PRIMARY]
Easiest way is to run it in the Query Analyzer. Open up SQL management tools, connect to the sevrver/db of your choice. Then you start the Query Analyzer (or new Query if it's 2005) from a menu. There you can paste the code and just click the green arrow to execute it. Voila!
Thanks for the suggestion. However, I got the following error listed below. Any idea?
Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near '('.
|||I removed 'with(IGNORE_DUP_KEY =OFF)on[PRIMARY]'....and it worked. Is this not an SQL option?
Thanks again for your help.
|||
johram:
Easiest way is to run it in the Query Analyzer. Open up SQL management tools, connect to the sevrver/db of your choice. Then you start the Query Analyzer (or new Query if it's 2005) from a menu. There you can paste the code and just click the green arrow to execute it. Voila!
In a perfect world, perhaps...
The OP is saying he needs to create the table on SQL 2000. The script wil fail at the end ".. WITH (IGNORE_DUP_KEY).. " part. This is 2005 syntax. I believe the script was generated from Management Studio connected to 2000 box?
Subscribe to:
Posts (Atom)