@@identity

I am running an insert sp that looks like this
*****************************
@.x int,
@.x1 int,
@.x2 int,
@.NewID int
AS
BEGIN
INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
SET @.New ID = (Select @.@.IDENTITY)
INSERT INTO TABLE2 (@.newID,@.x)
END
***********************
I am getting a conversion error, "Error converting data type varchar to
int.". I cannot figure out the converstion process. I have tried a ton of
different options but nothing works.
I want to run both INSERTs in the same sp.
Any ideas...>SET @.New ID = (Select @.@.IDENTITY)
Try: @.NewID = @.@.IDENTITY
If you are on SQL Server 2000, look at Scope_Identity() in BOL.
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
M.Smith wrote:
> I am running an insert sp that looks like this
> *****************************
> @.x int,
> @.x1 int,
> @.x2 int,
> @.NewID int
> AS
> BEGIN
> INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
> SET @.New ID = (Select @.@.IDENTITY)
> INSERT INTO TABLE2 (@.newID,@.x)
> END
> ***********************
> I am getting a conversion error, "Error converting data type varchar to
> int.". I cannot figure out the converstion process. I have tried a ton of
> different options but nothing works.
> I want to run both INSERTs in the same sp.
> Any ideas...
>|||worked with SCOPE, thanks
"Lars Broberg" <lars.b@.elbe-data.nothing.se> wrote in message
news:ugIkZPFPFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Try: @.NewID = @.@.IDENTITY
> If you are on SQL Server 2000, look at Scope_Identity() in BOL.
> --
> Lars Broberg
> Elbe-Data AB
> http://www.elbe-data.se
> Remove "nothing." when replying to private e-mail!
>
> M.Smith wrote:|||Hi
Unless you want to keep @.NEWID for later use then you may want to use
SCOPE_IDENTITY() directly in the insert statement.
John
"M.Smith" <martys@.bennyhinn.org> wrote in message
news:uxQhOmFPFHA.3372@.TK2MSFTNGP10.phx.gbl...
> worked with SCOPE, thanks
> "Lars Broberg" <lars.b@.elbe-data.nothing.se> wrote in message
> news:ugIkZPFPFHA.2788@.TK2MSFTNGP09.phx.gbl...
>|||Don't forget to specify the fileds in your INSERT statement.
INTO into TheTable (Field1,Field2) values (@.Value1,@.Value2)
JN.
"M.Smith" <martys@.bennyhinn.org> a crit dans le message de news:
%23Z$GiMFPFHA.1088@.TK2MSFTNGP14.phx.gbl...
>I am running an insert sp that looks like this
> *****************************
> @.x int,
> @.x1 int,
> @.x2 int,
> @.NewID int
> AS
> BEGIN
> INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
> SET @.New ID = (Select @.@.IDENTITY)
> INSERT INTO TABLE2 (@.newID,@.x)
> END
> ***********************
> I am getting a conversion error, "Error converting data type varchar to
> int.". I cannot figure out the converstion process. I have tried a ton of
> different options but nothing works.
> I want to run both INSERTs in the same sp.
> Any ideas...
>

@@Error help

Im not really understanding how @.@.Error works. If I run this code:
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
--
TIA,
ChrisRrun this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/|||I dont get it... I need to make it fail twice in order to be used?
--
TIA,
ChrisR
"SQL" wrote:
> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
--
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||See [url]http://msdn2.microsoft.com/en-us/library/ms190193.aspx[/url]
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
---
Posted via http://www.codecomments.com
---sql

@@Error help

Im not really understanding how @.@.Error works. If I run this code:
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
TIA,
ChrisRrun this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/|||I dont get it... I need to make it fail twice in order to be used?
--
TIA,
ChrisR
"SQL" wrote:

> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||See [url]http://msdn2.microsoft.com/en-us/library/ms190193.aspx[/url
]
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
---
Posted via http://www.codecomments.com
---

@@ identity insert

Is it possible to create a trigger that inserts the @.@.identity (primary key) from Table1 into a field in Table2?
If so, how? Thank you.
-D-yes it's possible. Create a trigger on Table1 for insert and execute an insert-statement using @.@.identity. Are you stuck?

HLEP..Sql Server UPDATE INNER JOIN QUERY ..

Im using an ADP to connect to a SQL Sqever DB.
In access it was really easy to say

Inner join on table1 and table2 and update columnA from table1 with
columnC from table2 where table1.key = table2.key and table2 columnB =
1 and table2 columnD = 4

I have tried all manner of beasts to get this thing to work..

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))

This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0' part which is really important

Any Help would be great...This way looks like it should work also but I get an error 'ADO Error:
HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
can only show one table in an update qurey?

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0))
WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)|||SAME ADO ERROR WHEN I USE THIS ???

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
FROM GIS_EVENTS_TEMP G INNER JOIN
HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
WHERE (H.UNITID = 1 AND H.LISTORDER = 0)|||(meyvn77@.yahoo.com) writes:
> Im using an ADP to connect to a SQL Sqever DB.
> In access it was really easy to say
> Inner join on table1 and table2 and update columnA from table1 with
> columnC from table2 where table1.key = table2.key and table2 columnB =
> 1 and table2 columnD = 4
>
> I have tried all manner of beasts to get this thing to work..
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
> AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))
> This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0' part which is really important
> Any Help would be great...

Unfortunately, it's not very easy to help if we don't know what
tables you have. The standard recommendation for this type of
problem is to post:

o CREATE TABLE statements of the tables nvolved. (Preferrably
cut down to the columns relevant to the problem.(
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of the business problem.

The two first points makes it easy to copy and paste into Query Analyzer,
so a tested solution can be developed. The third point makes it possible
to verify that the solution is correct. And the fourth point gives some
extra information which helps to understand the general problem.

> This way looks like it should work also but I get an error 'ADO Error:
> HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
> can only show one table in an update qurey?
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0))
> WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)

No, but because you are referring to HARMFULEVENT outside the subquery.

> AME ADO ERROR WHEN I USE THIS ???
>
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
> FROM GIS_EVENTS_TEMP G INNER JOIN
> HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
> WHERE (H.UNITID = 1 AND H.LISTORDER = 0)

Here you are mixing use of aliases and table name. Once you have
introduced an alias, you can not refer to the full table name in
the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I don't think that you can write something like this:
SELECT HARMFULEVENT FROM HARMFULEVENT

.fmt

Hi,
How is it possible to create a .fmt file with the following informations if
they are required?
server1
database1
table1
Thanksbcp pubs..authors2 in
c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
Read Using Format File article in the bol"farshad"
<farshad@.discussions.microsoft.com> wrote in message
news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> Hi,
> How is it possible to create a .fmt file with the following informations
> if
> they are required?
> server1
> database1
> table1
> Thanks|||Hi,
Does this mean I have to have a .dat file? How do I create it?
P.S. I run the bcp to insert using bulk insert.
Thanks
"Uri Dimant" wrote:
> bcp pubs..authors2 in
> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
> Read Using Format File article in the bol"farshad"
> <farshad@.discussions.microsoft.com> wrote in message
> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> > Hi,
> > How is it possible to create a .fmt file with the following informations
> > if
> > they are required?
> > server1
> > database1
> > table1
> >
> > Thanks
>
>|||Hi
On SQL 2000 BCP has a format option that will create a format for you, see
Books online for more.
John
"farshad" <farshad@.discussions.microsoft.com> wrote in message
news:31F50883-0BF3-49B9-BB17-A45EB192F489@.microsoft.com...
> Hi,
> Does this mean I have to have a .dat file? How do I create it?
> P.S. I run the bcp to insert using bulk insert.
> Thanks
> "Uri Dimant" wrote:
>> bcp pubs..authors2 in
>> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
>> Read Using Format File article in the bol"farshad"
>> <farshad@.discussions.microsoft.com> wrote in message
>> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
>> > Hi,
>> > How is it possible to create a .fmt file with the following
>> > informations
>> > if
>> > they are required?
>> > server1
>> > database1
>> > table1
>> >
>> > Thanks
>>|||I would have if I could find it in books online.
Thanks
"John Bell" wrote:
> Hi
> On SQL 2000 BCP has a format option that will create a format for you, see
> Books online for more.
> John
> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> news:31F50883-0BF3-49B9-BB17-A45EB192F489@.microsoft.com...
> > Hi,
> > Does this mean I have to have a .dat file? How do I create it?
> > P.S. I run the bcp to insert using bulk insert.
> > Thanks
> >
> > "Uri Dimant" wrote:
> >
> >> bcp pubs..authors2 in
> >> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
> >> Read Using Format File article in the bol"farshad"
> >> <farshad@.discussions.microsoft.com> wrote in message
> >> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> >> > Hi,
> >> > How is it possible to create a .fmt file with the following
> >> > informations
> >> > if
> >> > they are required?
> >> > server1
> >> > database1
> >> > table1
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Hi
Try the online copy:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp
John
farshad wrote:
> I would have if I could find it in books online.
> Thanks
> "John Bell" wrote:
> > Hi
> >
> > On SQL 2000 BCP has a format option that will create a format for you, see
> > Books online for more.
> >
> > John
> > "farshad" <farshad@.discussions.microsoft.com> wrote in message
> > news:31F50883-0BF3-49B9-BB17-A45EB192F489@.microsoft.com...
> > > Hi,
> > > Does this mean I have to have a .dat file? How do I create it?
> > > P.S. I run the bcp to insert using bulk insert.
> > > Thanks
> > >
> > > "Uri Dimant" wrote:
> > >
> > >> bcp pubs..authors2 in
> > >> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
> > >> Read Using Format File article in the bol"farshad"
> > >> <farshad@.discussions.microsoft.com> wrote in message
> > >> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> > >> > Hi,
> > >> > How is it possible to create a .fmt file with the following
> > >> > informations
> > >> > if
> > >> > they are required?
> > >> > server1
> > >> > database1
> > >> > table1
> > >> >
> > >> > Thanks
> > >>
> > >>
> > >>
> >
> >
> >|||farshad wrote:
> I would have if I could find it in books online.
> Thanks
>
What is it you can't find? Try to look for bcp in Books On Line. That
will give you a number of "hits" - and also about bcp.fmt.
Regards
Steen

**to trace what has happened**

Hi
In SQL2000, how can I find what has happened in my DB in particular date?
for example I insert a ne row in table1 and I update 2 rows in table2. now
how can find what did I perfome?
I don't want to monitor the changes simentanously, I want to refer to them
afre some times.
should I refer to .log file of my db?(if ye ,how?) or should I do
something else?
any help would be thanked.Hi,
SQL Server will not log the events by default. In this case probably you can
write triggers to audit the Delete/Insert and Update events.
Thanks
Hari
SQL Server MVP
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tf2mdvein9ig5y@.system109.parskhazar.net...
> Hi
> In SQL2000, how can I find what has happened in my DB in particular date?
> for example I insert a ne row in table1 and I update 2 rows in table2. now
> how can find what did I perfome?
> I don't want to monitor the changes simentanously, I want to refer to them
> afre some times.
> should I refer to .log file of my db?(if ye ,how?) or should I do
> something else?
> any help would be thanked.|||you could enable c2 auditing as many gov types are forced to do.
mike.menard
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tf2mdvein9ig5y@.system109.parskhazar.net...
> Hi
> In SQL2000, how can I find what has happened in my DB in particular date?
> for example I insert a ne row in table1 and I update 2 rows in table2. now
> how can find what did I perfome?
> I don't want to monitor the changes simentanously, I want to refer to them
> afre some times.
> should I refer to .log file of my db?(if ye ,how?) or should I do
> something else?
> any help would be thanked.|||I think it does it, cause when we set the database in full mode it somehow
save the changes so it can use it when it wants to restore the db based on
the last changes.
is it right?
in other words, if I want to know what has happened during a period of
time should I save the changes as a log file, or is there any place to
have it systematically?
thanks
On Mon, 18 Sep 2006 15:17:22 +0330, Hari Prasad
<hari_prasad_k@.hotmail.com> wrote:

> Hi,
> SQL Server will not log the events by default. In this case probably you
> can
> write triggers to audit the Delete/Insert and Update events.
> Thanks
> Hari
> SQL Server MVP
> "M" <rez1824@.yahoo.co.uk> wrote in message
> news:op.tf2mdvein9ig5y@.system109.parskhazar.net...
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||Have a look at these:
http://sqlserver2000.databases.aspf...erver-data.html
Andrew J. Kelly SQL MVP
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tf33aps8n9ig5y@.system109.parskhazar.net...
>I think it does it, cause when we set the database in full mode it somehow
>save the changes so it can use it when it wants to restore the db based on
>the last changes.
> is it right?
> in other words, if I want to know what has happened during a period of
> time should I save the changes as a log file, or is there any place to
> have it systematically?
> thanks
> On Mon, 18 Sep 2006 15:17:22 +0330, Hari Prasad
> <hari_prasad_k@.hotmail.com> wrote:
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

**sum**

Hi
I've follwoing data in table1 in SQL server2000,and I want to get the
result as below:
table1 result
Id Qty
-- --
1 500 1 500
2 502 2 1002
3 400 3 1402
4 140 4 1542
how is it possible in select statement?
Any help would be thankful.Here you go
CREATE TABLE #Table1(id int PRIMARY KEY, Quantity int NOT NULL)
INSERT INTO #Table1 VALUES(1,500)
INSERT INTO #Table1 VALUES(2,502)
INSERT INTO #Table1 VALUES(3,400)
INSERT INTO #Table1 VALUES(4,140)
SELECT id,
(SELECT SUM(Quantity) FROM #Table1 WHERE id <= O.id) As RunningTotal
FROM #Table1 O
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<R> wrote in message news:opsyn8fezjmw7tkz@.system109.parskhazar.net...
> Hi
> I've follwoing data in table1 in SQL server2000,and I want to get the
> result as below:
> table1 result
> Id Qty
> -- --
> 1 500 1 500
> 2 502 2 1002
> 3 400 3 1402
> 4 140 4 1542
>
> how is it possible in select statement?
> Any help would be thankful.|||I missed the ORDER BY
SELECT id,
(SELECT SUM(Quantity) FROM #Table1 WHERE id <= O.id) As RunningTotal
FROM #Table1 O
ORDER By ID
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ecqyxfV0FHA.3892@.TK2MSFTNGP12.phx.gbl...
> Here you go
>
> CREATE TABLE #Table1(id int PRIMARY KEY, Quantity int NOT NULL)
> INSERT INTO #Table1 VALUES(1,500)
> INSERT INTO #Table1 VALUES(2,502)
> INSERT INTO #Table1 VALUES(3,400)
> INSERT INTO #Table1 VALUES(4,140)
> SELECT id,
> (SELECT SUM(Quantity) FROM #Table1 WHERE id <= O.id) As RunningTotal
> FROM #Table1 O
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> <R> wrote in message news:opsyn8fezjmw7tkz@.system109.parskhazar.net...
>