Hi,
I still have problem with my script that perform a sequence of
many
data files with a BULK INSERT in a loop and rarely (about 10% of
times) fails with "cannot obtain a lock" (see the original post
below).
Since this happens also on a dedicate server without any other
activity
(both sql server and the O.S. are idle apart for the BULK INSERT
loop)
I start thinking the cause could be in the ODBC connection and cursor
that are
constantly re-used during the whole loop (the odbc connection is
in autocommit) and so
A friend of mine suggested me that I could try to enforce a checkpoint
could a ' cursor.execute("CHECKPOINT") ' statement issued before
any BULK INSERT
solve my issue?
TIA!
bye,
PiErre
on 2007-02-16 PiErre wrote:
> Subject: suggestion to avoid error "cannot obtain a lock"
> Hi,
> I have a script (actually the same python script
> that runs locally on several ms sql 2000sp4
> server - connecting via mxodbc)
> that every night tries to bulk insert about 20 files
> of various size (from less than 10 MB
> to more than 300 MB - about 900 MB in total )
> and sometimes (about 5% of the times)
> the importation fails (randomly on
> one or more of the servers) with the error like
> 'S1000', 1204, '[Microsoft][ODBC SQL Server Driver][SQL Server]
> The SQL Server cannot obtain a LOCK resource at this time...
>
> There is no other activity on the db server at the error time and
> so I cannot detect what is the cause of the resource lock.
> The db is normally configured with recovery-model set to simple
> and autoshrink enabled (not my decision
> and I must provide clear evidence to have
> that "standard" changed), but even after
> disable it the errors still appear.
>
> What can I do to detect the actual lock cause?
> More important: what can I do to avoid
> such errors? I heard something
> about to set a checkpoint after every bulk insert
> but I don't know how to do that...
>
> Thanks in advance for your help!
> bye,
> PiErreTry specifying TABLOCK on the BULK INSERT statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PiErre" <siggy2@.supereva.it> wrote in message
news:1178184835.364450.30740@.y80g2000hsf.googlegroups.com...
> Hi,
> I still have problem with my script that perform a sequence of
> many
> data files with a BULK INSERT in a loop and rarely (about 10% of
> times) fails with "cannot obtain a lock" (see the original post
> below).
> Since this happens also on a dedicate server without any other
> activity
> (both sql server and the O.S. are idle apart for the BULK INSERT
> loop)
> I start thinking the cause could be in the ODBC connection and cursor
> that are
> constantly re-used during the whole loop (the odbc connection is
> in autocommit) and so
> A friend of mine suggested me that I could try to enforce a checkpoint
> could a ' cursor.execute("CHECKPOINT") ' statement issued before
> any BULK INSERT
> solve my issue?
> TIA!
> bye,
> PiErre
> on 2007-02-16 PiErre wrote:
>> Subject: suggestion to avoid error "cannot obtain a lock"
>> Hi,
>> I have a script (actually the same python script
>> that runs locally on several ms sql 2000sp4
>> server - connecting via mxodbc)
>> that every night tries to bulk insert about 20 files
>> of various size (from less than 10 MB
>> to more than 300 MB - about 900 MB in total )
>> and sometimes (about 5% of the times)
>> the importation fails (randomly on
>> one or more of the servers) with the error like
>> 'S1000', 1204, '[Microsoft][ODBC SQL Server Driver][SQL Server]
>> The SQL Server cannot obtain a LOCK resource at this time...
>> There is no other activity on the db server at the error time and
>> so I cannot detect what is the cause of the resource lock.
>> The db is normally configured with recovery-model set to simple
>> and autoshrink enabled (not my decision
>> and I must provide clear evidence to have
>> that "standard" changed), but even after
>> disable it the errors still appear.
>> What can I do to detect the actual lock cause?
>> More important: what can I do to avoid
>> such errors? I heard something
>> about to set a checkpoint after every bulk insert
>> but I don't know how to do that...
>> Thanks in advance for your help!
>> bye,
>> PiErre
>|||On 3 Mag, 12:08, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> Try specifying TABLOCK on the BULK INSERT statement.
>
There are good chance that your hint was EXACTLY what I was looking
for...
Thanks a lot!
bye,
PiErre
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Manually Insert a Primary Key Value
I have a colleague who mysteriously lost his record in our Employee table.
The "employee ID" field serves as the primary key on the table.
How do I manually insert his record, including the old primary key value,
back into the table? That is, how do I bypass the primary-key constraint?
Thanks in advance,
Mark HolahanWhat is the definition of the table?
AMB
"Mark Holahan" wrote:
> I have a colleague who mysteriously lost his record in our Employee table.
> The "employee ID" field serves as the primary key on the table.
> How do I manually insert his record, including the old primary key value,
> back into the table? That is, how do I bypass the primary-key constraint?
> Thanks in advance,
> Mark Holahan
>
>|||Is this an identity field? if so use:
SET IDENTITY_INSERT ON
--execute insert statement here
SET IDENTITY_INSERT OFF|||You can't "bypass" a primary key constraint unless you drop it. I
assume you are actually referring to the IDENTITY property on this
column. The IDENTITY property is quite distinct from a PRIMARY KEY
constraint. If you want to insert an explicit IDENTITY value then use
the SET IDENTITY_INSERT table_name ON option.
Why does it matter to you if the row gets inserted with a different
IDENTITY value to the one it originally had? It shouldn't have been
possible for the accidental delete to cause "orphan" rows in a
referencing table - That's assuming you have correctly declared foreign
key constraints against the employee ID column. If you don't have
foreign keys then that's something you really ought to fix.
David Portas
SQL Server MVP
--|||AMB,
The table definition follows:
CREATE TABLE [dbo].[Employee] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchId] [int] NULL ,
[SalesRepId] [int] NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Deactivated] [datetime] NULL ,
[ResetPW] [bit] NOT NULL ,
[Tries] [tinyint] NULL ,
[LastLoginDtm] [datetime] NULL ,
[PendingInfoUpdate] [bit] NOT NULL ,
[IsSalesRep] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
[PendingInfoUpdate],
CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
> What is the definition of the table?
>
> AMB
> "Mark Holahan" wrote:
>|||Read David's post.
AMB
"Mark Holahan" wrote:
> AMB,
> The table definition follows:
> CREATE TABLE [dbo].[Employee] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BranchId] [int] NULL ,
> [SalesRepId] [int] NULL ,
> [Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Deactivated] [datetime] NULL ,
> [ResetPW] [bit] NOT NULL ,
> [Tries] [tinyint] NULL ,
> [LastLoginDtm] [datetime] NULL ,
> [PendingInfoUpdate] [bit] NOT NULL ,
> [IsSalesRep] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
> CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] ADD
> CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
> CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
> [PendingInfoUpdate],
> CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
>
>|||Distinction noted.
CIO of company claims RI puts unneeded burden on SQL Server. Therefore we
handle RI on the front end. I don't necessarily agree, especially when I
read in BOL that, "The query optimizer also uses constraint definitions to
build high-performance query execution plans." But I've never done the
homework to disprove his theory. So I abide.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107450710.189817.206210@.g14g2000cwa.googlegroups.com...
> You can't "bypass" a primary key constraint unless you drop it. I
> assume you are actually referring to the IDENTITY property on this
> column. The IDENTITY property is quite distinct from a PRIMARY KEY
> constraint. If you want to insert an explicit IDENTITY value then use
> the SET IDENTITY_INSERT table_name ON option.
> Why does it matter to you if the row gets inserted with a different
> IDENTITY value to the one it originally had? It shouldn't have been
> possible for the accidental delete to cause "orphan" rows in a
> referencing table - That's assuming you have correctly declared foreign
> key constraints against the employee ID column. If you don't have
> foreign keys then that's something you really ought to fix.
> --
> David Portas
> SQL Server MVP
> --
>|||The CIO is wrong. If he wants to design databases he should take a course
first ;-)
Obviously handling RI on the front end isn't working otherwise you wouldn't
have this problem. No surprises there.
David Portas
SQL Server MVP
--
The "employee ID" field serves as the primary key on the table.
How do I manually insert his record, including the old primary key value,
back into the table? That is, how do I bypass the primary-key constraint?
Thanks in advance,
Mark HolahanWhat is the definition of the table?
AMB
"Mark Holahan" wrote:
> I have a colleague who mysteriously lost his record in our Employee table.
> The "employee ID" field serves as the primary key on the table.
> How do I manually insert his record, including the old primary key value,
> back into the table? That is, how do I bypass the primary-key constraint?
> Thanks in advance,
> Mark Holahan
>
>|||Is this an identity field? if so use:
SET IDENTITY_INSERT ON
--execute insert statement here
SET IDENTITY_INSERT OFF|||You can't "bypass" a primary key constraint unless you drop it. I
assume you are actually referring to the IDENTITY property on this
column. The IDENTITY property is quite distinct from a PRIMARY KEY
constraint. If you want to insert an explicit IDENTITY value then use
the SET IDENTITY_INSERT table_name ON option.
Why does it matter to you if the row gets inserted with a different
IDENTITY value to the one it originally had? It shouldn't have been
possible for the accidental delete to cause "orphan" rows in a
referencing table - That's assuming you have correctly declared foreign
key constraints against the employee ID column. If you don't have
foreign keys then that's something you really ought to fix.
David Portas
SQL Server MVP
--|||AMB,
The table definition follows:
CREATE TABLE [dbo].[Employee] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchId] [int] NULL ,
[SalesRepId] [int] NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Deactivated] [datetime] NULL ,
[ResetPW] [bit] NOT NULL ,
[Tries] [tinyint] NULL ,
[LastLoginDtm] [datetime] NULL ,
[PendingInfoUpdate] [bit] NOT NULL ,
[IsSalesRep] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
[PendingInfoUpdate],
CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
> What is the definition of the table?
>
> AMB
> "Mark Holahan" wrote:
>|||Read David's post.
AMB
"Mark Holahan" wrote:
> AMB,
> The table definition follows:
> CREATE TABLE [dbo].[Employee] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BranchId] [int] NULL ,
> [SalesRepId] [int] NULL ,
> [Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Deactivated] [datetime] NULL ,
> [ResetPW] [bit] NOT NULL ,
> [Tries] [tinyint] NULL ,
> [LastLoginDtm] [datetime] NULL ,
> [PendingInfoUpdate] [bit] NOT NULL ,
> [IsSalesRep] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
> CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] ADD
> CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
> CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
> [PendingInfoUpdate],
> CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
>
>|||Distinction noted.
CIO of company claims RI puts unneeded burden on SQL Server. Therefore we
handle RI on the front end. I don't necessarily agree, especially when I
read in BOL that, "The query optimizer also uses constraint definitions to
build high-performance query execution plans." But I've never done the
homework to disprove his theory. So I abide.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107450710.189817.206210@.g14g2000cwa.googlegroups.com...
> You can't "bypass" a primary key constraint unless you drop it. I
> assume you are actually referring to the IDENTITY property on this
> column. The IDENTITY property is quite distinct from a PRIMARY KEY
> constraint. If you want to insert an explicit IDENTITY value then use
> the SET IDENTITY_INSERT table_name ON option.
> Why does it matter to you if the row gets inserted with a different
> IDENTITY value to the one it originally had? It shouldn't have been
> possible for the accidental delete to cause "orphan" rows in a
> referencing table - That's assuming you have correctly declared foreign
> key constraints against the employee ID column. If you don't have
> foreign keys then that's something you really ought to fix.
> --
> David Portas
> SQL Server MVP
> --
>|||The CIO is wrong. If he wants to design databases he should take a course
first ;-)
Obviously handling RI on the front end isn't working otherwise you wouldn't
have this problem. No surprises there.
David Portas
SQL Server MVP
--
Wednesday, March 7, 2012
Managing ntext, text with a long text data
Hi,
I have a problem to insert(update) a long text (more than 64K) into
SQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.
MSDN says: "When the ntext, text, and image data values get larger,
however, they must be handled on a block-by-block basis. Both
Transact-
SQL and the database APIs contain functions that allow applications to
I have a problem to insert(update) a long text (more than 64K) into
SQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.
MSDN says: "When the ntext, text, and image data values get larger,
however, they must be handled on a block-by-block basis. Both
Transact-
SQL and the database APIs contain functions that allow applications to
work with ntext, text, and image data block by block." Could somebody
give me an example how to do this, please.
Thank youThere are examples under UPDATETEXT and WRITETEXT in Books Online - do
these cover what you're trying to do? The MSSQL Resource Kit also has a
whole chapter on working with BLOBs, including a number of examples
using TSQL and ADO:
http://www.microsoft.com/technet/pr...art3/c1161.mspx
Simon|||igorsl (igorsl@.yahoo-dot-com.no-spam.invalid) writes:
> I have a problem to insert(update) a long text (more than 64K) into
> SQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.
> MSDN says: "When the ntext, text, and image data values get larger,
> however, they must be handled on a block-by-block basis. Both
> Transact-
> SQL and the database APIs contain functions that allow applications to
> work with ntext, text, and image data block by block." Could somebody
> give me an example how to do this, please.
I believe this limitation is in the client API rather than in T-SQL
itself. (Altough inserting a 1MB value through a plain INSERT is not
that performant.) Which API are you using?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Managing Insert with Identity
Hi,
I am new at stored procedures.
I am running SQL Server 2005 standard on XP Pro sp2.
I have a database that has about 100 tables. For example I have a table
called 'People' with a primary key called 'PeopleID', which is an int and
also of column type 'Identity'. This 'People' table also has a
'PeopleAttributesID' column, also of int nature, and relates to a table name
d
'PeopleAttributes' of which the primary key and Identity column is name
'PeopleAttributesID'. PeopleAttributes table also has a column named
'PeopleInterfaceListID' of int that also relates to a table called
PeopleInterfaceList that has a primary key, int, identity, called
PeopleInterfaceListID.
You get the idea. :-)
My question is how to create a stored procedure for inserting new people in
the 'People' table. My issues is dealing with the identity columns of the
'People' table and it's related tables.
How do I add a row into a table that has an identity primary key and has
foreign keys into tables that also have identity primary keys. Can you give
me an example of how this should be done in your opinion?
Thank you very, very much :)
Antoine Dubuc
MSN Messenger : banquo_ws@.hotmail.com
514-761-1832Antoine wrote:
> Hi,
> I am new at stored procedures.
> I am running SQL Server 2005 standard on XP Pro sp2.
> I have a database that has about 100 tables. For example I have a
> table called 'People' with a primary key called 'PeopleID', which is
> an int and also of column type 'Identity'. This 'People' table also
> has a 'PeopleAttributesID' column, also of int nature, and relates to
> a table named 'PeopleAttributes' of which the primary key and
> Identity column is name 'PeopleAttributesID'. PeopleAttributes table
> also has a column named 'PeopleInterfaceListID' of int that also
> relates to a table called PeopleInterfaceList that has a primary key,
> int, identity, called PeopleInterfaceListID.
> You get the idea. :-)
> My question is how to create a stored procedure for inserting new
> people in the 'People' table. My issues is dealing with the identity
> columns of the 'People' table and it's related tables.
> How do I add a row into a table that has an identity primary key and
> has foreign keys into tables that also have identity primary keys.
> Can you give me an example of how this should be done in your opinion?
> Thank you very, very much :)
> Antoine Dubuc
> MSN Messenger : banquo_ws@.hotmail.com
> 514-761-1832
Youinsert by leaving off the identity column. Once the insert is complete,
you can get the new value using the SCOPE_IDENTITY() function. if you need
to insert FK values, you need to pass them into the stored procedure and use
them in the insert. You'll need to get those values from your application
first.
David Gugick
Quest Software|||Hi David,
When I try to execute this, I get the cannot leave null for this column erro
r.
DI have to manually insert it using something like:
DECLARE PeopleID INT
PeopleID = SCOPE_IDENTITY
...
Exactly how do you do this?
thank you,
Antoine
"David Gugick" wrote:
> Antoine wrote:
> Youinsert by leaving off the identity column. Once the insert is complete,
> you can get the new value using the SCOPE_IDENTITY() function. if you need
> to insert FK values, you need to pass them into the stored procedure and u
se
> them in the insert. You'll need to get those values from your application
> first.
> --
> David Gugick
> Quest Software
>
>|||> When I try to execute this, I get the cannot leave null for this column
> error.
> DI have to manually insert it using something like:
> DECLARE PeopleID INT
> PeopleID = SCOPE_IDENTITY
Did you mean:
DECLARE @.PeopleID INT
... INSERT statement here
SET @.PeopleID = SCOPE_IDENTITY()
?|||>> have a database that has about 100 tables. For example I have a table ca
lled 'People' with a primary key called 'PeopleID', which is an int and also
of column type 'Identity'. This 'People' table also has a 'PeopleAttribute
sID' column, also of int na
ture, and relates to a table named 'PeopleAttributes' of which the primary k
ey and Identity column is named 'PeopleAttributesID'. PeopleAttributes table
also has a column named 'PeopleInterfaceListID' of int that also relates to
a table called PeopleInter
faceList that has a primary key, int, identity, called PeopleInterfaceListID
. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
However, these data element names and the improper use of IDENTITY as a
key imply that you have never written an RDBMS before. IDENTITY is not
a key by definition and you are mixing data and metadata. It looks
like the typhical newbie disaster with IDENTITY columns used as pointer
chains to mimic a 1970's IDMS database. Please get help instead of
newsgroup kludges.|||In order for a column with the IDENTITY 'property' to become a key then you
need only make it such using a CONSTRAINT, either PRIMARY KEY where no
natural key exists or UNIQUE if you are making it an artificial or surrogate
key.
Get a grip and be more constructive, the poster said his level of experience
was beginner so be polite and don't rant your rude rubbish.
There are many objects in the world that do not have a natural key, my
favourite is the message board example.
It's quite concievable that the author wants to create an auto generated
number and use that as a key, afterall, its becoming very dodgy to say the
least in the US using social security numbers and capturing that info on a
website, well - would you honestly give over your national insurance number
(uk equiv) to a company - nope.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132258326.365651.286720@.g14g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> However, these data element names and the improper use of IDENTITY as a
> key imply that you have never written an RDBMS before. IDENTITY is not
> a key by definition and you are mixing data and metadata. It looks
> like the typhical newbie disaster with IDENTITY columns used as pointer
> chains to mimic a 1970's IDMS database. Please get help instead of
> newsgroup kludges.
>
I am new at stored procedures.
I am running SQL Server 2005 standard on XP Pro sp2.
I have a database that has about 100 tables. For example I have a table
called 'People' with a primary key called 'PeopleID', which is an int and
also of column type 'Identity'. This 'People' table also has a
'PeopleAttributesID' column, also of int nature, and relates to a table name
d
'PeopleAttributes' of which the primary key and Identity column is name
'PeopleAttributesID'. PeopleAttributes table also has a column named
'PeopleInterfaceListID' of int that also relates to a table called
PeopleInterfaceList that has a primary key, int, identity, called
PeopleInterfaceListID.
You get the idea. :-)
My question is how to create a stored procedure for inserting new people in
the 'People' table. My issues is dealing with the identity columns of the
'People' table and it's related tables.
How do I add a row into a table that has an identity primary key and has
foreign keys into tables that also have identity primary keys. Can you give
me an example of how this should be done in your opinion?
Thank you very, very much :)
Antoine Dubuc
MSN Messenger : banquo_ws@.hotmail.com
514-761-1832Antoine wrote:
> Hi,
> I am new at stored procedures.
> I am running SQL Server 2005 standard on XP Pro sp2.
> I have a database that has about 100 tables. For example I have a
> table called 'People' with a primary key called 'PeopleID', which is
> an int and also of column type 'Identity'. This 'People' table also
> has a 'PeopleAttributesID' column, also of int nature, and relates to
> a table named 'PeopleAttributes' of which the primary key and
> Identity column is name 'PeopleAttributesID'. PeopleAttributes table
> also has a column named 'PeopleInterfaceListID' of int that also
> relates to a table called PeopleInterfaceList that has a primary key,
> int, identity, called PeopleInterfaceListID.
> You get the idea. :-)
> My question is how to create a stored procedure for inserting new
> people in the 'People' table. My issues is dealing with the identity
> columns of the 'People' table and it's related tables.
> How do I add a row into a table that has an identity primary key and
> has foreign keys into tables that also have identity primary keys.
> Can you give me an example of how this should be done in your opinion?
> Thank you very, very much :)
> Antoine Dubuc
> MSN Messenger : banquo_ws@.hotmail.com
> 514-761-1832
Youinsert by leaving off the identity column. Once the insert is complete,
you can get the new value using the SCOPE_IDENTITY() function. if you need
to insert FK values, you need to pass them into the stored procedure and use
them in the insert. You'll need to get those values from your application
first.
David Gugick
Quest Software|||Hi David,
When I try to execute this, I get the cannot leave null for this column erro
r.
DI have to manually insert it using something like:
DECLARE PeopleID INT
PeopleID = SCOPE_IDENTITY
...
Exactly how do you do this?
thank you,
Antoine
"David Gugick" wrote:
> Antoine wrote:
> Youinsert by leaving off the identity column. Once the insert is complete,
> you can get the new value using the SCOPE_IDENTITY() function. if you need
> to insert FK values, you need to pass them into the stored procedure and u
se
> them in the insert. You'll need to get those values from your application
> first.
> --
> David Gugick
> Quest Software
>
>|||> When I try to execute this, I get the cannot leave null for this column
> error.
> DI have to manually insert it using something like:
> DECLARE PeopleID INT
> PeopleID = SCOPE_IDENTITY
Did you mean:
DECLARE @.PeopleID INT
... INSERT statement here
SET @.PeopleID = SCOPE_IDENTITY()
?|||>> have a database that has about 100 tables. For example I have a table ca
lled 'People' with a primary key called 'PeopleID', which is an int and also
of column type 'Identity'. This 'People' table also has a 'PeopleAttribute
sID' column, also of int na
ture, and relates to a table named 'PeopleAttributes' of which the primary k
ey and Identity column is named 'PeopleAttributesID'. PeopleAttributes table
also has a column named 'PeopleInterfaceListID' of int that also relates to
a table called PeopleInter
faceList that has a primary key, int, identity, called PeopleInterfaceListID
. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
However, these data element names and the improper use of IDENTITY as a
key imply that you have never written an RDBMS before. IDENTITY is not
a key by definition and you are mixing data and metadata. It looks
like the typhical newbie disaster with IDENTITY columns used as pointer
chains to mimic a 1970's IDMS database. Please get help instead of
newsgroup kludges.|||In order for a column with the IDENTITY 'property' to become a key then you
need only make it such using a CONSTRAINT, either PRIMARY KEY where no
natural key exists or UNIQUE if you are making it an artificial or surrogate
key.
Get a grip and be more constructive, the poster said his level of experience
was beginner so be polite and don't rant your rude rubbish.
There are many objects in the world that do not have a natural key, my
favourite is the message board example.
It's quite concievable that the author wants to create an auto generated
number and use that as a key, afterall, its becoming very dodgy to say the
least in the US using social security numbers and capturing that info on a
website, well - would you honestly give over your national insurance number
(uk equiv) to a company - nope.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132258326.365651.286720@.g14g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> However, these data element names and the improper use of IDENTITY as a
> key imply that you have never written an RDBMS before. IDENTITY is not
> a key by definition and you are mixing data and metadata. It looks
> like the typhical newbie disaster with IDENTITY columns used as pointer
> chains to mimic a 1970's IDMS database. Please get help instead of
> newsgroup kludges.
>
Saturday, February 25, 2012
Managing database growth
Hi
I've a DB in SQL Server 2000 which allows the
database to grow by a percentage (defualt=10%).
Now,anfter any action(for example insert ing a row)
does it calculate the DB size and allocate the
accurrate size of space by defined percentage?
does it take long time and has overhead?
is it a good way to define database grow by
percentage?
Thanks for any help.
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/> Now,anfter any action(for example insert ing a row)
Yes
No
Yes
Otherwise, when the database grow and somebody forget to give more space, it
will happen some ugly error.
I think if the database it some, you should let the database grow by fix
size, otherwise grow by percentage|||SQL Server doesn't do the grow after an insert. It happens whenever SQL Serv
er need space for an
insert, update etc and the file is full. The user is blocked by the grow ope
ration during the grow.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opr2g3ovl8hqligo@.msnews.microsoft.com...
My personal preference is for a fixed growth unit.
Say, blocks of 1024MB for data and 512MB for log.
For some databases, excessive numbers of small growth will give you
fragmentation on the physical files.
I have seen some databases with the primary file in 300+ fragments. This
cannot be good.
Much better is a scheme something like this:
Initial size: 30GB Data / 3GB Log
Data growth: 3072MB
Log growth: 1024MB
This will minimize the number of 'expands' that need to run.
Planning is key here.
What you want to avoid is the 'ever-larger' growth syndrome.
I.e. Initial Size 30GB Data / 3GB Log.
Data growth: 10%.
Log growth 10%.
This percentage grows ever larger each time the DB expands.
Again, this is just personal preference.
James Hokes
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr2g3ovl8hqligo@.msnews.microsoft.com...
I've a DB in SQL Server 2000 which allows the
database to grow by a percentage (defualt=10%).
Now,anfter any action(for example insert ing a row)
does it calculate the DB size and allocate the
accurrate size of space by defined percentage?
does it take long time and has overhead?
is it a good way to define database grow by
percentage?
Thanks for any help.
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/> Now,anfter any action(for example insert ing a row)
quote:
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
Yes
quote:
> does it take long time and has overhead?
No
quote:
> is it a good way to define database grow by
> percentage?
Yes
Otherwise, when the database grow and somebody forget to give more space, it
will happen some ugly error.
I think if the database it some, you should let the database grow by fix
size, otherwise grow by percentage|||SQL Server doesn't do the grow after an insert. It happens whenever SQL Serv
er need space for an
insert, update etc and the file is full. The user is blocked by the grow ope
ration during the grow.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opr2g3ovl8hqligo@.msnews.microsoft.com...
quote:|||RM,
[c
olor=darkred]
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/[/color]
My personal preference is for a fixed growth unit.
Say, blocks of 1024MB for data and 512MB for log.
For some databases, excessive numbers of small growth will give you
fragmentation on the physical files.
I have seen some databases with the primary file in 300+ fragments. This
cannot be good.
Much better is a scheme something like this:
Initial size: 30GB Data / 3GB Log
Data growth: 3072MB
Log growth: 1024MB
This will minimize the number of 'expands' that need to run.
Planning is key here.
What you want to avoid is the 'ever-larger' growth syndrome.
I.e. Initial Size 30GB Data / 3GB Log.
Data growth: 10%.
Log growth 10%.
This percentage grows ever larger each time the DB expands.
Again, this is just personal preference.
James Hokes
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr2g3ovl8hqligo@.msnews.microsoft.com...
quote:
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
Managing database growth
Hi
I've a DB in SQL Server 2000 which allows the
database to grow by a percentage (defualt=10%).
Now,anfter any action(for example insert ing a row)
does it calculate the DB size and allocate the
accurrate size of space by defined percentage?
does it take long time and has overhead?
is it a good way to define database grow by
percentage?
Thanks for any help.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
Yes
> does it take long time and has overhead?
No
> is it a good way to define database grow by
> percentage?
Yes
Otherwise, when the database grow and somebody forget to give more space, it
will happen some ugly error.
I think if the database it some, you should let the database grow by fix
size, otherwise grow by percentage|||SQL Server doesn't do the grow after an insert. It happens whenever SQL Server need space for an
insert, update etc and the file is full. The user is blocked by the grow operation during the grow.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opr2g3ovl8hqligo@.msnews.microsoft.com...
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||RM,
My personal preference is for a fixed growth unit.
Say, blocks of 1024MB for data and 512MB for log.
For some databases, excessive numbers of small growth will give you
fragmentation on the physical files.
I have seen some databases with the primary file in 300+ fragments. This
cannot be good.
Much better is a scheme something like this:
Initial size: 30GB Data / 3GB Log
Data growth: 3072MB
Log growth: 1024MB
This will minimize the number of 'expands' that need to run.
Planning is key here.
What you want to avoid is the 'ever-larger' growth syndrome.
I.e. Initial Size 30GB Data / 3GB Log.
Data growth: 10%.
Log growth 10%.
This percentage grows ever larger each time the DB expands.
Again, this is just personal preference.
James Hokes
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr2g3ovl8hqligo@.msnews.microsoft.com...
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
I've a DB in SQL Server 2000 which allows the
database to grow by a percentage (defualt=10%).
Now,anfter any action(for example insert ing a row)
does it calculate the DB size and allocate the
accurrate size of space by defined percentage?
does it take long time and has overhead?
is it a good way to define database grow by
percentage?
Thanks for any help.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
Yes
> does it take long time and has overhead?
No
> is it a good way to define database grow by
> percentage?
Yes
Otherwise, when the database grow and somebody forget to give more space, it
will happen some ugly error.
I think if the database it some, you should let the database grow by fix
size, otherwise grow by percentage|||SQL Server doesn't do the grow after an insert. It happens whenever SQL Server need space for an
insert, update etc and the file is full. The user is blocked by the grow operation during the grow.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opr2g3ovl8hqligo@.msnews.microsoft.com...
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||RM,
My personal preference is for a fixed growth unit.
Say, blocks of 1024MB for data and 512MB for log.
For some databases, excessive numbers of small growth will give you
fragmentation on the physical files.
I have seen some databases with the primary file in 300+ fragments. This
cannot be good.
Much better is a scheme something like this:
Initial size: 30GB Data / 3GB Log
Data growth: 3072MB
Log growth: 1024MB
This will minimize the number of 'expands' that need to run.
Planning is key here.
What you want to avoid is the 'ever-larger' growth syndrome.
I.e. Initial Size 30GB Data / 3GB Log.
Data growth: 10%.
Log growth 10%.
This percentage grows ever larger each time the DB expands.
Again, this is just personal preference.
James Hokes
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr2g3ovl8hqligo@.msnews.microsoft.com...
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
Subscribe to:
Posts (Atom)