manipulating ntext in stored procedures

Hi,

I have been trying to write a SP where, in the result set is brought back, two ntext columns are combined with a text string, However I get an error when I click on apply;

"Error 403: Invalid operator for data type. Operator equals add, type equals ntext"

I can only assume that this means you can't add (or even manipulate) ntext columns in a SP.

Short of changing the sp to bring back 3 columns and to do the manipulation in my c# code I can't think of a way round this.

Anyone with any ideas?

thanks in advance

rich

SP code ( I have highlighted the problem area):

CREATE PROCEDURE [dbo].[usp_RCTReportQuery]

@.PersonType varchar(255),
@.Status varchar(255),
@.Oustanding varchar(255)

AS

DECLARE @.PersonTypeID int,
@.StatusID int,
@.OustandingID int

SELECT @.PersonTypeID = PersonTypeID FROM TBL_LU_People_Type WHERE PersonType = @.PersonType
SELECT @.StatusID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Status
SELECT @.OustandingID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Oustanding

SELECT surname as [Potential Claimant], s.thevalue + '<BR /><BR />' + o.thevalue as [Status]
FROM tbl_people p
INNER JOIN tbl_Lu_people lup on lup.personid = p.personid and persontypeid = @.PersonTypeID
LEFT JOIN tbl_memo s on s.caseID = p.caseID and s.memotypeid = @.StatusID
LEFT JOIN tbl_memo o on o.caseID = p.caseID and o.memotypeid = @.OustandingID
GO

Richie:

If you are running SQL Server 2005 you might be able to leverage a function to work around this issue. Perhaps a function like this:

alter function dbo.appendText
( @.prm_textIn nvarchar(max),
@.prm_appendString varchar (8000)
)
returns nvarchar (max)
as

begin

return ( @.prm_textIn + @.prm_appendString )

end

This is coded as a scalar function; you might be better off setting it up as an inline function so that you can potentially leverage it in the future with the CROSS JOIN operator for other purposes. Below is an example:

declare @.what nvarchar (max) set @.what = ''
declare @.firstPart varchar (40) set @.firstPart = ' ** This is'
declare @.secondPart varchar (40) set @.secondPart = ' a test. **'

select @.what = @.what + ' #' + convert (nvarchar(5), iter)
from small_iterator (nolock)

select len (@.what) as [len],
substring (dbo.appendText (@.what, @.firstPart + @.secondPart), len(@.what) - 6, 30)
as [righthand piece]

-- Sample Output: --

-- len righthand piece
-- -- --
-- 218263 #32767 ** This is a test.

Dave

|||if you are on 2005, you could just use a nvarchar(MAX) column instead of ntext.

www.elsasoft.org|||

Richie:

You might be able to get your query to work in SQL 2005 doing something like this:

SELECT surname as [Potential Claimant], cast (s.thevalue as nvarchar (max)) + '<BR /><BR />' + o.thevalue as [Status]

|||

Please specify the version of SQL Server in your posts so it is easy to suggest the correct solution.

1. If you are using SQL Server 2005 then use varchar(max), nvarchar(max), and varbinary(max). The text, ntext, and image data types have been deprecated. The newer max data types will allow you to manipulate the values on the server-side just like regular varchar, nvarchar or varbinary values. Also, for backward compatibility reasons any expression that involves string concatenation will return only a string of maximum length 8000 bytes. In order to concatenate larger length strings you have to cast at least one of the values to varchar(max) or nvarchar(max). So in your SELECT list, you could do something like CAST(s.thevalue as varchar(max)) + '<BR/><BR>' + o.thevalue.

2. If you are not using SQL Server 2005 then there is no way to concatenate strings larger than 8000 bytes. You have to either dump the rows into a temporary table and use UPDATETEXT to manipulate each row individually or simply return the values as multiple columns and concatenate on the client-side

Lastly, you should actually leave the presentation to the client side and do these type of operations on the server. What if you want to later introduce a paragraph break or ident the line? What if the formatting is more complex? You should just return the strings as is to the client and then format it there.

|||

Cheers for the tip. Unfortunately I am still using Sql Server 2000 :(

I tried creating a function but you can't have variables of type ntext in functions.

Is there anyway I can do this in SQL Server 2000?

|||I am using sql server 2000 and try using table temp.

Whats wrong?

ROBUST PLAN. Somebody help?

---

No

se puede crear una fila de tabla de trabajo más larga que el máximo

admitido. Vuelva a enviar la consulta con la sugerencia ROBUST PLAN.

TRANSLATION

Quote:

Originally Posted by

A

row of table of work cannot be created longer than the admitted

maximum. Return to send the consultation with suggestion ROBUST PLAN.

Cant create line in table work longer,

CREATE TABLE temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

--Inicializar the NTEXT column
UPDATE temp SET Definition=''

CODE --

DECLARE

@.txtPval binary(16),

@.txtPidx INT,

@.curName SYSNAME,

@.curtext NVARCHAR(4000)
--

--
DECLARE C CURSOR

LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT OBJECT_NAME(id), text

FROM syscomments s

INNER JOIN #MYTEMP t

ON s.id = t.Proc_id

ORDER BY id, colid

OPEN c -> HERE TELL ME ERROR about robust plan.

FETCH NEXT FROM c INTO @.curname, @.curtext

--Start Loop

WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
--get pointer for the current procedure name colid
SELECT @.txtPval = TEXTPTR(Definition)
FROM temp
WHERE Proc_Name = @.curname

--find out where to append the @.temp table′s value

SELECT @.txtPidx = DATALENGTH(Definition)/2
FROM temp
WHERE Proc_Name = @.curName

--Apply the append of the current 8kb chunk
UPDATETEXT temp.definition @.txtPval @.txtPidx 0 @.curtext
FETCH NEXT FROM c INTO @.curName, @.curtext
END

-- check what was produced
SELECT Proc_name, Definition, DATALENGTH(Definition)/2
FROM temp

-- check our filter
SELECT Proc_Name, Definition
FROM temp
WHERE definition LIKE '%foobar%'

--Clean up

DROP TABLE temp
CLOSE c
DEALLOCATE c
Thanks for try help.

manipulating ntext in stored procedures

Hi,

I have been trying to write a SP where, in the result set is brought back, two ntext columns are combined with a text string, However I get an error when I click on apply;

"Error 403: Invalid operator for data type. Operator equals add, type equals ntext"

I can only assume that this means you can't add (or even manipulate) ntext columns in a SP.

Short of changing the sp to bring back 3 columns and to do the manipulation in my c# code I can't think of a way round this.

Anyone with any ideas?

thanks in advance

rich

SP code ( I have highlighted the problem area):

CREATE PROCEDURE [dbo].[usp_RCTReportQuery]

@.PersonType varchar(255),
@.Status varchar(255),
@.Oustanding varchar(255)

AS

DECLARE @.PersonTypeID int,
@.StatusID int,
@.OustandingID int

SELECT @.PersonTypeID = PersonTypeID FROM TBL_LU_People_Type WHERE PersonType = @.PersonType
SELECT @.StatusID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Status
SELECT @.OustandingID = MemoTypeID FROM TBL_LU_Memo WHERE MemoType = @.Oustanding

SELECT surname as [Potential Claimant], s.thevalue + '<BR /><BR />' + o.thevalue as [Status]
FROM tbl_people p
INNER JOIN tbl_Lu_people lup on lup.personid = p.personid and persontypeid = @.PersonTypeID
LEFT JOIN tbl_memo s on s.caseID = p.caseID and s.memotypeid = @.StatusID
LEFT JOIN tbl_memo o on o.caseID = p.caseID and o.memotypeid = @.OustandingID
GO

Richie:

If you are running SQL Server 2005 you might be able to leverage a function to work around this issue. Perhaps a function like this:

alter function dbo.appendText
( @.prm_textIn nvarchar(max),
@.prm_appendString varchar (8000)
)
returns nvarchar (max)
as

begin

return ( @.prm_textIn + @.prm_appendString )

end

This is coded as a scalar function; you might be better off setting it up as an inline function so that you can potentially leverage it in the future with the CROSS JOIN operator for other purposes. Below is an example:

declare @.what nvarchar (max) set @.what = ''
declare @.firstPart varchar (40) set @.firstPart = ' ** This is'
declare @.secondPart varchar (40) set @.secondPart = ' a test. **'

select @.what = @.what + ' #' + convert (nvarchar(5), iter)
from small_iterator (nolock)

select len (@.what) as [len],
substring (dbo.appendText (@.what, @.firstPart + @.secondPart), len(@.what) - 6, 30)
as [righthand piece]

-- Sample Output: --

-- len righthand piece
-- -- --
-- 218263 #32767 ** This is a test.

Dave

|||if you are on 2005, you could just use a nvarchar(MAX) column instead of ntext.

www.elsasoft.org|||

Richie:

You might be able to get your query to work in SQL 2005 doing something like this:

SELECT surname as [Potential Claimant], cast (s.thevalue as nvarchar (max)) + '<BR /><BR />' + o.thevalue as [Status]

|||

Please specify the version of SQL Server in your posts so it is easy to suggest the correct solution.

1. If you are using SQL Server 2005 then use varchar(max), nvarchar(max), and varbinary(max). The text, ntext, and image data types have been deprecated. The newer max data types will allow you to manipulate the values on the server-side just like regular varchar, nvarchar or varbinary values. Also, for backward compatibility reasons any expression that involves string concatenation will return only a string of maximum length 8000 bytes. In order to concatenate larger length strings you have to cast at least one of the values to varchar(max) or nvarchar(max). So in your SELECT list, you could do something like CAST(s.thevalue as varchar(max)) + '<BR/><BR>' + o.thevalue.

2. If you are not using SQL Server 2005 then there is no way to concatenate strings larger than 8000 bytes. You have to either dump the rows into a temporary table and use UPDATETEXT to manipulate each row individually or simply return the values as multiple columns and concatenate on the client-side

Lastly, you should actually leave the presentation to the client side and do these type of operations on the server. What if you want to later introduce a paragraph break or ident the line? What if the formatting is more complex? You should just return the strings as is to the client and then format it there.

|||

Cheers for the tip. Unfortunately I am still using Sql Server 2000 :(

I tried creating a function but you can't have variables of type ntext in functions.

Is there anyway I can do this in SQL Server 2000?

|||I am using sql server 2000 and try using table temp.

Whats wrong?

ROBUST PLAN. Somebody help?

---

No

se puede crear una fila de tabla de trabajo más larga que el máximo

admitido. Vuelva a enviar la consulta con la sugerencia ROBUST PLAN.

TRANSLATION

Quote:

Originally Posted by

A

row of table of work cannot be created longer than the admitted

maximum. Return to send the consultation with suggestion ROBUST PLAN.

Cant create line in table work longer,

CREATE TABLE temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

--Inicializar the NTEXT column
UPDATE temp SET Definition=''

CODE --

DECLARE

@.txtPval binary(16),

@.txtPidx INT,

@.curName SYSNAME,

@.curtext NVARCHAR(4000)
--

--
DECLARE C CURSOR

LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT OBJECT_NAME(id), text

FROM syscomments s

INNER JOIN #MYTEMP t

ON s.id = t.Proc_id

ORDER BY id, colid

OPEN c -> HERE TELL ME ERROR about robust plan.

FETCH NEXT FROM c INTO @.curname, @.curtext

--Start Loop

WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
--get pointer for the current procedure name colid
SELECT @.txtPval = TEXTPTR(Definition)
FROM temp
WHERE Proc_Name = @.curname

--find out where to append the @.temp table′s value

SELECT @.txtPidx = DATALENGTH(Definition)/2
FROM temp
WHERE Proc_Name = @.curName

--Apply the append of the current 8kb chunk
UPDATETEXT temp.definition @.txtPval @.txtPidx 0 @.curtext
FETCH NEXT FROM c INTO @.curName, @.curtext
END

-- check what was produced
SELECT Proc_name, Definition, DATALENGTH(Definition)/2
FROM temp

-- check our filter
SELECT Proc_Name, Definition
FROM temp
WHERE definition LIKE '%foobar%'

--Clean up

DROP TABLE temp
CLOSE c
DEALLOCATE c
Thanks for try help.

Managing the errors in stored procedures

How can I manage the errors into a stored procedure?
Example:
CREATE PROCEDURE MyProcedure
@.UserName SYSNAME = NULL
AS
BEGIN
DECLARE @.v INT
SET @.v = 0
EXEC @.v = sp_helpuser @.UserName
IF (@.@.ERROR <> 0)
BEGIN
RAISERROR('My Error Message...', 16, 1)
RETURN 1
END
IF (@.v <> 0)
BEGIN
RAISERROR('Return Value is 1', 16, 1)
RETURN 1
END
RETURN 0
END
I try this example, but in case than sp_helpuser exits with error, I did
not get my error messages in my client application.
What is the correct way to manage the errors?
Thank you for help!http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:314DE418-5F6C-4115-AB97-0B93CA56AC25@.microsoft.com...
> How can I manage the errors into a stored procedure?
> Example:
> CREATE PROCEDURE MyProcedure
> @.UserName SYSNAME = NULL
> AS
> BEGIN
> DECLARE @.v INT
> SET @.v = 0
> EXEC @.v = sp_helpuser @.UserName
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR('My Error Message...', 16, 1)
> RETURN 1
> END
> IF (@.v <> 0)
> BEGIN
> RAISERROR('Return Value is 1', 16, 1)
> RETURN 1
> END
> RETURN 0
> END
> I try this example, but in case than sp_helpuser exits with error, I
did
> not get my error messages in my client application.
> What is the correct way to manage the errors?
> Thank you for help!
>|||> What is the correct way to manage the errors?
I ran your proc at it seems sp_helpuser exits with return code 0 and @.@.ERROR
is also 0. However, the sp_helpuser error is raised so you should be able
to detect the error in your client app. I don't know what language/API
you're using but the VBScript example below detects the sp_helpuser error.
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = connection
cmd.CommandType = adCmdStoredProcedure
cmd.CommandText = "MyProcedure"
Set pReturnCode = cmd.CreateParameter("@.RETURN_CODE")
pReturnCode.Type = adInteger
pReturnCode.Direction = adParamReturnValue
cmd.Parameters.Append pReturnCode
Set pLoginName = cmd.CreateParameter("@.UserName")
pLoginName.Type = adVarWChar
pLoginName.Direction = adParamInput
pLoginName.Value = "Test"
pLoginName.Size = Len(pLoginName.Value)
cmd.Parameters.Append pLoginName
On Error Resume Next 'trap Execute error
cmd.Execute
On Error GoTo 0 're-enable default error handler
MsgBox cmd.Parameters(0).Name & "=" & cmd.Parameters(0).Value
For Each error in connection.Errors
MsgBox error.Description
Next
Hope this helps.
Dan Guzman
SQL Server MVP
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:314DE418-5F6C-4115-AB97-0B93CA56AC25@.microsoft.com...
> How can I manage the errors into a stored procedure?
> Example:
> CREATE PROCEDURE MyProcedure
> @.UserName SYSNAME = NULL
> AS
> BEGIN
> DECLARE @.v INT
> SET @.v = 0
> EXEC @.v = sp_helpuser @.UserName
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR('My Error Message...', 16, 1)
> RETURN 1
> END
> IF (@.v <> 0)
> BEGIN
> RAISERROR('Return Value is 1', 16, 1)
> RETURN 1
> END
> RETURN 0
> END
> I try this example, but in case than sp_helpuser exits with error, I did
> not get my error messages in my client application.
> What is the correct way to manage the errors?
> Thank you for help!
>|||For what it's worth, I structure all my SPs similarly, using the following
pattern:
As
-- Declare the error handler variables ...
Declare @.Err Integer
Declare @.Msg VarChar(100)
/ *************************
In Functional part of the stored Proc
consists of blocks like the following,
(one block for each executable statement that might fail),
in each block, 3 parts,
1. set the string value of the error msg,
2. execute the statement, and then
3. test @.@.error, and if error occurred
Goto ErrHandler
********************************/
Set @.Msg = 'Failed, Unable to Insert <blank> into <blank>... '
Insert Tablename(... )
Values(x,y,z, etc. )
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
Set @.Msg = 'Failed, Unable to Update Employee %d.'
Update EMployees Set
ColumnA = @.Val1,
ColumnB = @.Val2, ...
Where EmployeeID = @.EmployeeID
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
-- ****************************************
****
-- ****************************************
****
ErrHandler:
Begin
If @.@.TranCount > 0 RollBack Transaction
If CharIndex('%d', @.Msg) > 0
Raiserror(@.Msg, 16, 1, @.EmployeeID)
Else RaisError(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)
End
-- ****************************************
****
/********************************
If there is more than one statement in the
Stored Proc, I create a block for each one
and put them all inside a Begin Tran Commit
Error Handler detects @.@.TranCount to
determine whether or not to RollBack...
This pattern allows you to put common
error handler at the end of all your SPs,
It Therefore keeps the code in the body
of the SP "Cleaner", making it easier to
read, and to see the SP's business structure
business process which you are implementing
********************************/
"Mihaly" wrote:

> How can I manage the errors into a stored procedure?
> Example:
> CREATE PROCEDURE MyProcedure
> @.UserName SYSNAME = NULL
> AS
> BEGIN
> DECLARE @.v INT
> SET @.v = 0
> EXEC @.v = sp_helpuser @.UserName
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR('My Error Message...', 16, 1)
> RETURN 1
> END
> IF (@.v <> 0)
> BEGIN
> RAISERROR('Return Value is 1', 16, 1)
> RETURN 1
> END
> RETURN 0
> END
> I try this example, but in case than sp_helpuser exits with error, I di
d
> not get my error messages in my client application.
> What is the correct way to manage the errors?
> Thank you for help!
>|||Uh Forgot You have to
Return(0)
After the Business Logic, before the ErrHandler Label, or the ErrHandler
Code will always run
-- Declare the error handler variables ...
Declare @.Err Integer
Declare @.Msg VarChar(100)
/ *************************
In Functional part of the stored Proc
consists of blocks like the following,
(one block for each executable statement that might fail),
in each block, 3 parts,
1. set the string value of the error msg,
2. execute the statement, and then
3. test @.@.error, and if error occurred
Goto ErrHandler
********************************/
Begin Transaction
Set @.Msg = 'Failed, Unable to Insert <blank> into <blank>... '
Insert Tablename(... )
Values(x,y,z, etc. )
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
Set @.Msg = 'Failed, Unable to Update Employee %d.'
Update EMployees Set
ColumnA = @.Val1,
ColumnB = @.Val2, ...
Where EmployeeID = @.EmployeeID
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
Commit Transaction
Return(0)
-- ****************************************
****
-- ****************************************
****
ErrHandler:
Begin
If @.@.TranCount > 0 RollBack Transaction
If CharIndex('%d', @.Msg) > 0
Raiserror(@.Msg, 16, 1, @.EmployeeID)
Else RaisError(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)
End
-- ****************************************
****
"CBretana" wrote:
> For what it's worth, I structure all my SPs similarly, using the followin
g
> pattern:
> As
> -- Declare the error handler variables ...
> Declare @.Err Integer
> Declare @.Msg VarChar(100)
> / *************************
> In Functional part of the stored Proc
> consists of blocks like the following,
> (one block for each executable statement that might fail),
> in each block, 3 parts,
> 1. set the string value of the error msg,
> 2. execute the statement, and then
> 3. test @.@.error, and if error occurred
> Goto ErrHandler
> ********************************/
> Set @.Msg = 'Failed, Unable to Insert <blank> into <blank>... '
> Insert Tablename(... )
> Values(x,y,z, etc. )
> Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
> -- --
> Set @.Msg = 'Failed, Unable to Update Employee %d.'
> Update EMployees Set
> ColumnA = @.Val1,
> ColumnB = @.Val2, ...
> Where EmployeeID = @.EmployeeID
> Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
> -- --
>
> -- ****************************************
****
> -- ****************************************
****
> ErrHandler:
> Begin
> If @.@.TranCount > 0 RollBack Transaction
> If CharIndex('%d', @.Msg) > 0
> Raiserror(@.Msg, 16, 1, @.EmployeeID)
> Else RaisError(@.Msg, 16, 1)
> If @.Err = 0 Set @.Err = -1
> Return(@.Err)
> End
> -- ****************************************
****
> /********************************
> If there is more than one statement in the
> Stored Proc, I create a block for each one
> and put them all inside a Begin Tran Commit
> Error Handler detects @.@.TranCount to
> determine whether or not to RollBack...
> This pattern allows you to put common
> error handler at the end of all your SPs,
> It Therefore keeps the code in the body
> of the SP "Cleaner", making it easier to
> read, and to see the SP's business structure
> business process which you are implementing
> ********************************/
>
> "Mihaly" wrote:
>

Managing Stored Procedures / Functions for releases.

WE release our software once a week. In 1 months time we will have over 500 stored procedures in our DataBase.

What we do for releases is when a stored procedure is changed, we put the Drop and Create parts of that script in our SQL Update Script.

A problem comes up when Developer A changes My_StoredProc and then developer B changes the same stored procedure. Sometimes it works ok (the developer B will run the update script before changing his stored procedure. HOwever, it can happen where one Update script file has the same SP 5 times (5 drops 5 creates)... especially if over 300 SP's are getting updating in 1 release.

We will always catch these on our tests, however, it's the 2 hours to fix the Test DB after we run these tests...

What is the best way to manage these? We thought about putting our stored procedures into Team Foundation Server, but we don't know if that will work for us.

We have 8 developers in our team.

If anyone could help or give advice on this, it would be awesome.

Thanks.

You need some form of source control system. If you have Visual Source Safe then put the DROP/CREATE scrips in it and before any developer changes an SP they make sure it isn't checked out. They check it out, do some work, then check the new update script in.

If you are all using different databases the developer should run the script that is in Source Safe against their database before making changes to ensure they are working on the latest version.

|||

Thanks, That is currently what we're switching to.

I wanted to know if there's anything better we should be doing :D

now we need to write a file to throw them all together.

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.
>

Management Studio Sluggish

We have noticed that quite frequently, SQL Server Management Studio is somewhat sluggish with expanding lists for tables, opening stored procedures, opening files, etc.

Executing queries seems to be very quick though.

The machine that has SQL 2005 is 2.8 GHz with 512 MB of RAM.

Any idea why SQL Server Management Studio would seem somewhat sluggish?

That is really not very much memory for SQL Server (and the OS). I recommend adding additional memory and comparing the times -I think you will be pleased with the effect.|||Thanks. Got the order from the boss to bump it up to 2.5 GIG ram. I'll let you know how it performs after the upgrade.