INSERT works in SQL server 2003 but NOT in SQL server 2000

I am new to Infopath 2003, SQL server 2000 and SQL server 2003. I am calling up a stored procedure with 2 variables from jscript in infopath 2003 to run a stored procedure in SQL server 2003 to copy a record (@.RecipetoCopy) and insert it with a new name(@.RecipeNew). It works fine with SQL 2003 but it will not work in SQL 2000. Below is my stored procedure for both.

Code for 2005 work fine:
__________________________________________________ ________
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[CopyInsert]@.RecipetoCopy varchar(10), @.RecipeNew varchar(10)

AS

INSERT INTO [Epmar].[dbo].[Formulas]
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

SELECT@.RecipeNew,Type1,RawMat1,Preset1,Message1
fromFormulas
whereFormulas.FormulaNumber = @.RecipetoCopy

SELECT * from Formulas
whereFormulas.FormulaNumber = @.RecipeNew
__________________________________________________ __________

Code for SQL 2000 does not work
__________________________________________________ __________
CREATE PROCEDURE CopyInsert @.RecipetoCopy varchar(10), @.RecipeNew varchar(10)

AS

INSERT Formulas
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

SELECT@.RecipeNew,Type1,RawMat1,Preset1,Message1
fromFormulas
whereFormulas.FormulaNumber = @.RecipetoCopy

SELECT * from Formulas
whereFormulas.FormulaNumber = @.RecipeNew
GO
__________________________________________________ _______

Quote:

Originally Posted by MMCI

I am new to Infopath 2003, SQL server 2000 and SQL server 2003. I am calling up a stored procedure with 2 variables from jscript in infopath 2003 to run a stored procedure in SQL server 2003 to copy a record (@.RecipetoCopy) and insert it with a new name(@.RecipeNew). It works fine with SQL 2003 but it will not work in SQL 2000. Below is my stored procedure for both.

Code for 2005 work fine:
__________________________________________________ ________
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[CopyInsert]@.RecipetoCopy varchar(10), @.RecipeNew varchar(10)

AS

INSERT INTO [Epmar].[dbo].[Formulas]
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

SELECT@.RecipeNew,Type1,RawMat1,Preset1,Message1
fromFormulas
whereFormulas.FormulaNumber = @.RecipetoCopy

SELECT * from Formulas
whereFormulas.FormulaNumber = @.RecipeNew
__________________________________________________ __________

Code for SQL 2000 does not work
__________________________________________________ __________
CREATE PROCEDURE CopyInsert @.RecipetoCopy varchar(10), @.RecipeNew varchar(10)

AS

INSERT Formulas
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

SELECT@.RecipeNew,Type1,RawMat1,Preset1,Message1
fromFormulas
whereFormulas.FormulaNumber = @.RecipetoCopy

SELECT * from Formulas
whereFormulas.FormulaNumber = @.RecipeNew
GO
__________________________________________________ _______

You've missed out the INTO on your INSERT statement in SQL 2000. It should be:

INSERT INTO Formulas
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])|||SQL server 2003 ?

From where you got that ?|||

Quote:

Originally Posted by

You've missed out the INTO on your INSERT statement in SQL 2000. It should be:

INSERT INTO Formulas
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

It still does not work with the INSERT INTO Formulas.|||SQL server 2005 not 2003

Insert Variables to a table.

Hi all. New to MS SQL, so this is probably dumb.
I've looked at all kinds of examples on the web and just can't seem to get
it.
I am trying to insert into a column (in an SQL2K) table a value from a
variable.
Here is an example of what I'm coding in Query Analyzer:
<< Code Start>>
declare @.RecNo as int
select count(Recno) from Help as RecNO
print @.Recno
Code Ends>>
UP TO HERE, this works fine and actually prints the number 1012 (which are
the correct number of records in the table).
OtherFile is just a name I'm using as an example.
<< Code Starts
insert into OtherFile (recno)
values (@.RecNo)
Code End>>
When I execute this, I get the message "Server: Msg 137, Level 15, State 2,
Line 2
Must declare the variable '@.RecNo'.
I thought I had declared it as I can print it, but I can't insert it into
anything.
What the heck am I doing wrong?
Thanks to all.Variables have a scope of one batch. Thus, the variable evaporated before
it got to you INSERT statement.
BTW, the first batch would have done the SELECT, but the PRINT should have
given you NULL, since the variable was never assigned. Therefore, run the
following in its entirety:
declare @.RecNo as int
select @.RecNo = count(Recno) from Help
print @.Recno
insert into OtherFile (recno)
values (@.RecNo)
go
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
news:ufwyI3NGIHA.4628@.TK2MSFTNGP02.phx.gbl...
Hi all. New to MS SQL, so this is probably dumb.
I've looked at all kinds of examples on the web and just can't seem to get
it.
I am trying to insert into a column (in an SQL2K) table a value from a
variable.
Here is an example of what I'm coding in Query Analyzer:
<< Code Start>>
declare @.RecNo as int
select count(Recno) from Help as RecNO
print @.Recno
Code Ends>>
UP TO HERE, this works fine and actually prints the number 1012 (which are
the correct number of records in the table).
OtherFile is just a name I'm using as an example.
<< Code Starts
insert into OtherFile (recno)
values (@.RecNo)
Code End>>
When I execute this, I get the message "Server: Msg 137, Level 15, State 2,
Line 2
Must declare the variable '@.RecNo'.
I thought I had declared it as I can print it, but I can't insert it into
anything.
What the heck am I doing wrong?
Thanks to all.|||Thanks Tom, that did it.
Also, I exited Query Analyzer, relaunched and ran the first part of my
script. It now returned 1013, so the insert worked.
I'm not sure why the Print displayed 1012 orignally. Maybe a ghost?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ODLfw6NGIHA.4296@.TK2MSFTNGP04.phx.gbl...
> Variables have a scope of one batch. Thus, the variable evaporated before
> it got to you INSERT statement.
> BTW, the first batch would have done the SELECT, but the PRINT should have
> given you NULL, since the variable was never assigned. Therefore, run the
> following in its entirety:
> declare @.RecNo as int
> select @.RecNo = count(Recno) from Help
> print @.Recno
> insert into OtherFile (recno)
> values (@.RecNo)
> go
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
> news:ufwyI3NGIHA.4628@.TK2MSFTNGP02.phx.gbl...
> Hi all. New to MS SQL, so this is probably dumb.
> I've looked at all kinds of examples on the web and just can't seem to get
> it.
> I am trying to insert into a column (in an SQL2K) table a value from a
> variable.
> Here is an example of what I'm coding in Query Analyzer:
> << Code Start>>
> declare @.RecNo as int
> select count(Recno) from Help as RecNO
> print @.Recno
> Code Ends>>
> UP TO HERE, this works fine and actually prints the number 1012 (which are
> the correct number of records in the table).
> OtherFile is just a name I'm using as an example.
> << Code Starts
> insert into OtherFile (recno)
> values (@.RecNo)
> Code End>>
> When I execute this, I get the message "Server: Msg 137, Level 15, State
> 2,
> Line 2
> Must declare the variable '@.RecNo'.
> I thought I had declared it as I can print it, but I can't insert it into
> anything.
> What the heck am I doing wrong?
> Thanks to all.
>
>|||There's difference in the output between a PRINT and a SELECT. Your
original script would have output one row with the correct number. If you
click on the Messages tab, that's where you see the output of any print
statements, assuming output in a grid. Oh, and I tried it and I got no
output in the Messages tab when the PRINT statement was printing a variable
that had not been assigned. I had thought it would have printed a NULL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
news:OM%23ChAOGIHA.3360@.TK2MSFTNGP04.phx.gbl...
Thanks Tom, that did it.
Also, I exited Query Analyzer, relaunched and ran the first part of my
script. It now returned 1013, so the insert worked.
I'm not sure why the Print displayed 1012 orignally. Maybe a ghost?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ODLfw6NGIHA.4296@.TK2MSFTNGP04.phx.gbl...
> Variables have a scope of one batch. Thus, the variable evaporated before
> it got to you INSERT statement.
> BTW, the first batch would have done the SELECT, but the PRINT should have
> given you NULL, since the variable was never assigned. Therefore, run the
> following in its entirety:
> declare @.RecNo as int
> select @.RecNo = count(Recno) from Help
> print @.Recno
> insert into OtherFile (recno)
> values (@.RecNo)
> go
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
> news:ufwyI3NGIHA.4628@.TK2MSFTNGP02.phx.gbl...
> Hi all. New to MS SQL, so this is probably dumb.
> I've looked at all kinds of examples on the web and just can't seem to get
> it.
> I am trying to insert into a column (in an SQL2K) table a value from a
> variable.
> Here is an example of what I'm coding in Query Analyzer:
> << Code Start>>
> declare @.RecNo as int
> select count(Recno) from Help as RecNO
> print @.Recno
> Code Ends>>
> UP TO HERE, this works fine and actually prints the number 1012 (which are
> the correct number of records in the table).
> OtherFile is just a name I'm using as an example.
> << Code Starts
> insert into OtherFile (recno)
> values (@.RecNo)
> Code End>>
> When I execute this, I get the message "Server: Msg 137, Level 15, State
> 2,
> Line 2
> Must declare the variable '@.RecNo'.
> I thought I had declared it as I can print it, but I can't insert it into
> anything.
> What the heck am I doing wrong?
> Thanks to all.
>
>

Insert Trigger Using Variables Help

Can somebody please help me with compiling my insert trigger below. I am fairly new to SQL server 2000 and I am having troubles with using variables in insert triggers. The trigger that I am creating will basically update another table based on a certain criteria that is not specified below. I am hoping to first get my trigger to work then apply the criteria on when to fire afterwards. I just need help with being able to store values in my declared variables for insert into another table. Thanks in advance for everyones help.

Use database_testing

IF EXISTS (SELECT name FROM sysobjects
WHERE type = 'TR' AND name = 'Trigger_Name')
DROP TRIGGER Trigger_Name
GO

CREATE TRIGGER Trigger_Name
ON [trigger_table] FOR INSERT
AS
Declare @.resource_id int = inserted.resource
@.type = varchar(100) = inserted.type
@.date_logged (datetime) = inserted.creation_date
@.created varchar(100) = inserted.username

Insert into table_A (resource_id, resource_type, date_created, created_by)
values (resource_id,type, date_logged, created)You didn't specified that exactly what you want to fulfill. I think you want to track users for insert row or update row.

CREATE TRIGGER Trigger_Name
ON [trigger_table] FOR INSERT
AS

SET NOCOUNT ON

Begin
Insert into table_A (resource_id, resource_type, date_created, created_by)
SELECT inserted.resource
inserted.type,
inserted.creation_date,
inserted.username
FROM inserted
End

SET NOCOUNT OFF|||rajeshpatel gave you probably the nicest sollution. If you want to hold on to your own script for some reason, I filtered some errors out of it. This is what it should look like:

CREATE TRIGGER Trigger_Name
ON [trigger_table] FOR INSERT
AS
BEGIN
Declare @.resource_id int
, @.type varchar(100)
, @.date_logged datetime
, @.created varchar(100)

select @.resource_id = inserted.resource
, @.type = inserted.type
, @.date_logged = inserted.creation_date
, @.created = inserted.username

Insert into table_A
(resource_id, resource_type, date_created, created_by)
values
(@.resource_id,@.type, @.date_logged, @.created)
...
END

Gr,
Yveau