Problem with @@identity in SQL Server 6.5

In SQL Server 6.5, I have a table called employee
with two fields emp_id and emp_name where emp_id
is an identity column.

The below Stored Procedure is trying to select
the last inserted emp_id using @.@.identity.

CREATE PROCEDURE Employee AS

begin

SET IDENTITY_INSERT employee OFF

SET NOCOUNT ON

declare @.empid int

INSERT INTO employee(emp_name) VALUES
("Soundy")

SET @.empid = @.@.identity

return @.empid

end

But when compiling this procedure, SQL Server displays
an error saying "Incorrect syntax near @.empid".

Can i know where the problem is ?Q1 Can i know where the problem is ?

A1 Try 'Soundy' instead of "Soundy"? For example:

Use TempDB
Go

CREATE TABLE [employee] (
[emp_id] [int] IDENTITY (1, 1) NOT NULL ,
[emp_name] [nvarchar] (50) NULL)
Go

CREATE PROCEDURE ins_Employee
@.pEmpName nvarChar (50) AS
begin
SET IDENTITY_INSERT employee OFF
SET NOCOUNT ON
declare @.empid int
INSERT INTO employee(emp_name) VALUES
(@.pEmpName)
SET @.empid = @.@.identity
return @.empid
end

Go

DECLARE @.RC int
-- exec the Proc
EXEC @.RC = ins_Employee @.pEmpName = 'Soundy'
Select @.RC as '@.RC for ins_employee'
Go

SELECT emp_id, emp_name
FROM employee
Where
emp_id = (SELECT Max(emp_id)FROM employee)

problem with "Sysname" data type - SQL Server 2000

I have a database where several tables have fields of data
type "SYSNAME". Unfortunately SQL Server 2000 doesn't seem
to recognize this data type and this is causing "Field not
found" error messages in my application. Has anyone
encountered this problem before? How do I get around it?sysname = nvarchar(128) in 7 and 2000. It is equivalent to varchar(30) in
6.5
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"noclue6" <sobrienn@.hotmail.com> wrote in message
news:830501c350f2$3defa890$7d02280a@.phx.gbl...
> I have a database where several tables have fields of data
> type "SYSNAME". Unfortunately SQL Server 2000 doesn't seem
> to recognize this data type and this is causing "Field not
> found" error messages in my application. Has anyone
> encountered this problem before? How do I get around it?|||But its not possible to change the data type of a column
in the SYSUSERS table - i.e. a system table? Or is it?
>--Original Message--
>sysname = nvarchar(128) in 7 and 2000. It is equivalent
to varchar(30) in
>6.5
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"noclue6" <sobrienn@.hotmail.com> wrote in message
>news:830501c350f2$3defa890$7d02280a@.phx.gbl...
>> I have a database where several tables have fields of
data
>> type "SYSNAME". Unfortunately SQL Server 2000 doesn't
seem
>> to recognize this data type and this is causing "Field
not
>> found" error messages in my application. Has anyone
>> encountered this problem before? How do I get around it?
>
>.
>|||You can but you most certainly do not want to.
What problems exactly are you having with it?
What are you doing at the time.?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"noclue6" <sobrienn@.hotmail.com> wrote in message
news:0b1901c3510c$8a546530$a401280a@.phx.gbl...
> But its not possible to change the data type of a column
> in the SYSUSERS table - i.e. a system table? Or is it?
>
> >--Original Message--
> >sysname = nvarchar(128) in 7 and 2000. It is equivalent
> to varchar(30) in
> >6.5
> >
> >--
> >
> >--
> >Allan Mitchell (Microsoft SQL Server MVP)
> >MCSE,MCDBA
> >www.SQLDTS.com
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >"noclue6" <sobrienn@.hotmail.com> wrote in message
> >news:830501c350f2$3defa890$7d02280a@.phx.gbl...
> >> I have a database where several tables have fields of
> data
> >> type "SYSNAME". Unfortunately SQL Server 2000 doesn't
> seem
> >> to recognize this data type and this is causing "Field
> not
> >> found" error messages in my application. Has anyone
> >> encountered this problem before? How do I get around it?
> >
> >
> >.
> >|||the software is trying to access the NAME field of the
SYSUSERS table on logon but its just not finding it
because "Name" is of type SYSNAME.
>--Original Message--
>You can but you most certainly do not want to.
>What problems exactly are you having with it?
>What are you doing at the time.?
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"noclue6" <sobrienn@.hotmail.com> wrote in message
>news:0b1901c3510c$8a546530$a401280a@.phx.gbl...
>> But its not possible to change the data type of a column
>> in the SYSUSERS table - i.e. a system table? Or is it?
>>
>> >--Original Message--
>> >sysname = nvarchar(128) in 7 and 2000. It is
equivalent
>> to varchar(30) in
>> >6.5
>> >
>> >--
>> >
>> >--
>> >Allan Mitchell (Microsoft SQL Server MVP)
>> >MCSE,MCDBA
>> >www.SQLDTS.com
>> >I support PASS - the definitive, global community
>> >for SQL Server professionals - http://www.sqlpass.org
>> >
>> >
>> >
>> >"noclue6" <sobrienn@.hotmail.com> wrote in message
>> >news:830501c350f2$3defa890$7d02280a@.phx.gbl...
>> >> I have a database where several tables have fields of
>> data
>> >> type "SYSNAME". Unfortunately SQL Server 2000 doesn't
>> seem
>> >> to recognize this data type and this is
causing "Field
>> not
>> >> found" error messages in my application. Has anyone
>> >> encountered this problem before? How do I get around
it?
>> >
>> >
>> >.
>> >
>
>.
>

Problem with "select a, b, a+b from table" in SQL Server 2000

Using SQL Server 2000, I'm having a problem trying to derive a query field
from two other fields returned in the same query.
select
[User - Name] = user.name,
[User - ID] = user.id,
[Num Laptops] = (select sum(...) where id = user.id ),
[Num PCs] = (select sum(...) where id = user.id ),
[Total Computers] = [Num Laptops] + [Num PCs],
...
The erroneous line is the [Total Computers] line.
If I remove that, the query works fine and correctly returns the number of
laptops and pcs for each user.
How can I derive this field from the other fields returned in the query?
Any help most appreciated.
Mr Nice#laptops and #pc are calculated values that only exist at runtime. Thus,
they're not available for you to reference in the select to calc #total.
However, you can derive the whle select and the use the #laptops and #pc.
e.g.
select *,lp+pc
from (
select
[User - Name] = user.name,
[User - ID] = user.id,
[Num Laptops] = (select sum(...) where id = user.id ),
[Num PCs] = (select sum(...) where id = user.id )
from ...
where ..
) as derived
-oj
"Mr Nice" <no.spam@.thanks> wrote in message
news:420c6897$0$21947$cc9e4d1f@.news.dial.pipex.com...
> Using SQL Server 2000, I'm having a problem trying to derive a query field
> from two other fields returned in the same query.
>
> select
> [User - Name] = user.name,
> [User - ID] = user.id,
> [Num Laptops] = (select sum(...) where id = user.id ),
> [Num PCs] = (select sum(...) where id = user.id ),
> [Total Computers] = [Num Laptops] + [Num PCs],
> ...
> The erroneous line is the [Total Computers] line.
> If I remove that, the query works fine and correctly returns the number of
> laptops and pcs for each user.
> How can I derive this field from the other fields returned in the query?
> Any help most appreciated.
> Mr Nice
>|||Mr Nice wrote:
> select
> [User - Name] = user.name,
> [User - ID] = user.id,
> [Num Laptops] = (select sum(...) where id = user.id ),
> [Num PCs] = (select sum(...) where id = user.id ),
> [Total Computers] = [Num Laptops] + [Num PCs],
SELECT
[User - Name] = user.name,
[User - ID] = user.id,
[Num Laptops] = (select sum(...) where id = user.id ),
[Num PCs] = (select sum(...) where id = user.id ),
[Total Computers] = (select sum(...) where id = user.id ) + (select
sum(...) where id = user.id ),
However I'm sure that there is a way to do it without using as many
sub-queries. But that's my own personal pet peeve (I dislike subqueries
for some reason, that and in this case it loooks excessive).
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Please include DDL with your posts otherwise we can only guess what
your tables look like.
Given:
CREATE TABLE Users (user_id INTEGER NOT NULL PRIMARY KEY, user_name
VARCHAR(50) NOT NULL UNIQUE)
CREATE TABLE Assets (asset_number VARCHAR(20) NOT NULL PRIMARY KEY,
asset_type CHAR(2) NOT NULL CHECK (asset_type IN ('PC','LT')), user_id
INTEGER NOT NULL REFERENCES Users (user_id))
You could do this:
SELECT U.user_id, U.user_name,
COUNT(CASE WHEN asset_type = 'LT' THEN 1 END) AS num_laptops,
COUNT(CASE WHEN asset_type = 'PC' THEN 1 END) AS num_pcs
FROM Users AS U
LEFT JOIN Assets AS H
ON U.user_id = H.user_id
GROUP BY U.user_id, U.user_name
David Portas
SQL Server MVP
--