Insert Trigger with If Statement

I want to check if a field has a 0 in it, if it does it should run some
code, if not do nothing
If field1 = 0 then
some code
end if
Please advise of the syntax for this
TIA PaulTry something on these lines:
If Exists (Select 1 from <Table> where field = 0)
Begin
-- the activity you wanted to do
End
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Paul Goldney" <paulg@.wizardit.co.uk> wrote in message
news:ca97dk$pm3$1$8302bc10@.news.demon.co.uk...
> I want to check if a field has a 0 in it, if it does it should run some
> code, if not do nothing
> If field1 = 0 then
> some code
> end if
> Please advise of the syntax for this
> TIA Paul
>|||Remember that more than one row may be inserted at once. Maybe one row = 0
and another row is <> 0. Probably you just need a WHERE clause on whatever
statement(s) are in "some code" instead of an IF statement:
?
..
FROM Inserted WHERE col = 0
You can test for col = 0 in an IF statement using EXISTS but crucially this
tests for the presence of *any* row where col = 0, which may or may not be
what you want in your trigger.
IF EXISTS
(SELECT *
FROM Inserted
WHERE col = 0)
David Portas
SQL Server MVP
--

Insert Trigger with If Statement

I want to check if a field has a 0 in it, if it does it should run some
code, if not do nothing
If field1 = 0 then
some code
end if
Please advise of the syntax for this
TIA Paul
Try something on these lines:
If Exists (Select 1 from <Table> where field = 0)
Begin
-- the activity you wanted to do
End
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Paul Goldney" <paulg@.wizardit.co.uk> wrote in message
news:ca97dk$pm3$1$8302bc10@.news.demon.co.uk...
> I want to check if a field has a 0 in it, if it does it should run some
> code, if not do nothing
> If field1 = 0 then
> some code
> end if
> Please advise of the syntax for this
> TIA Paul
>
|||Remember that more than one row may be inserted at once. Maybe one row = 0
and another row is <> 0. Probably you just need a WHERE clause on whatever
statement(s) are in "some code" instead of an IF statement:
?
...
FROM Inserted WHERE col = 0
You can test for col = 0 in an IF statement using EXISTS but crucially this
tests for the presence of *any* row where col = 0, which may or may not be
what you want in your trigger.
IF EXISTS
(SELECT *
FROM Inserted
WHERE col = 0)
David Portas
SQL Server MVP

Insert Trigger with If Statement

I want to check if a field has a 0 in it, if it does it should run some
code, if not do nothing
If field1 = 0 then
some code
end if
Please advise of the syntax for this
TIA PaulTry something on these lines:
If Exists (Select 1 from <Table> where field = 0)
Begin
-- the activity you wanted to do
End
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Paul Goldney" <paulg@.wizardit.co.uk> wrote in message
news:ca97dk$pm3$1$8302bc10@.news.demon.co.uk...
> I want to check if a field has a 0 in it, if it does it should run some
> code, if not do nothing
> If field1 = 0 then
> some code
> end if
> Please advise of the syntax for this
> TIA Paul
>|||Remember that more than one row may be inserted at once. Maybe one row = 0
and another row is <> 0. Probably you just need a WHERE clause on whatever
statement(s) are in "some code" instead of an IF statement:
?
...
FROM Inserted WHERE col = 0
You can test for col = 0 in an IF statement using EXISTS but crucially this
tests for the presence of *any* row where col = 0, which may or may not be
what you want in your trigger.
IF EXISTS
(SELECT *
FROM Inserted
WHERE col = 0)
--
David Portas
SQL Server MVP
--sql

Insert trigger not working...

can someone shed some light on this? i want this to work on update and
insert, but it only works on update. When i run a simple insert
statement, i get this
<root>The specified statement did not generate any data</root>
Here is the trigger:
CREATE TRIGGER trg_UpdateQty ON [fastpic].[FP_INVTRANS]
FOR UPDATE, INSERT
--This trigger is used to export an xml file with system_qty, part_name
AS
DECLARE @.trans_date datetime,
@.part_name varchar(50),
@.Q varchar (255),
@.file varchar (255)
--IF NOT UPDATE(system_qty)
--RETURN
SELECT @.trans_date =trans_date FROM Inserted
SELECT @.part_name = part_name From Inserted
SELECT @.Q = 'select system_qty, part_name FROM [fastpic].[FP_INVTRANS2]
as InvTrans WHERE part_name ='''+@.part_name + ''' and trans_date ='''
+ Convert(varchar(20), @.trans_date, 120) + ''' for xml auto, elements'
Exec sp_makewebtask @.outputfile ='c:\updatePart.xml',
@.query = @.Q,
@.templatefile = 'c:\scripts\template1.tpl'On 8 May 2006 15:26:56 -0700, lytung@.gmail.com wrote:

>can someone shed some light on this? i want this to work on update and
>insert, but it only works on update. When i run a simple insert
>statement, i get this
> <root>The specified statement did not generate any data</root>
Hi lytung,
Not a definitive answer, but I have some comments below:

>Here is the trigger:
> CREATE TRIGGER trg_UpdateQty ON [fastpic].[FP_INVTRANS]
>FOR UPDATE, INSERT
(snip)

>SELECT @.trans_date =trans_date FROM Inserted
>SELECT @.part_name = part_name From Inserted
This can give unexpected results for multi-row updates or multi-row
inserts. Since the trigger is fired only once per statement execution,
the inserted pseudo-table will hold several rows. You might well get
@.trans_date from one row and @.part_name from another. Plus, you probably
want to execute the trigger's logic for all affected rows.

>SELECT @.Q = 'select system_qty, part_name FROM [fastpic].[FP_INVTRANS2]
>as InvTrans WHERE part_name ='''+@.part_name + ''' and trans_date ='''
>+ Convert(varchar(20), @.trans_date, 120) + ''' for xml auto, elements'
Use style 126 for the converstion of @.trans_date. Style 120 is not
guaranteed to be unambiguous in all locale settings.

>Exec sp_makewebtask @.outputfile ='c:\updatePart.xml',
>@.query = @.Q,
>@.templatefile = 'c:\scripts\template1.tpl'
Add a PRINT @.Q statement before, after or instead of this statement for
debugging purposes. Or insert @.Q into a table, if you are testing from a
front-end that doesn't expose the output of PRINT. Check if the SQL that
is generated matches your expecations.
Hugo Kornelis, SQL Server MVP

insert stored procedure does not commit to the database

Hi,
I have a problem where an insert stored procedure does not commit to
the database from a vb.net program. I can run the stored procedure
fine through the IDE, but when I use the following vb code the message
box shows the next ID number but when I check the database no new row
has been added. Any ideas?
Phil
*******STORED PROCEDURE
CREATE PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer
AS
SET IMPLICIT_TRANSACTIONS OFF
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SELECT SCOPE_IDENTITY() AS ID
*VB CODE
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposT ill.mdf;Integrated
Security=True;User Instance=True"
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MYSP_InsertEposTransaction"
' Create a SqlParameter for each parameter in the stored
procedure.
Dim transDateParam As New SqlParameter("@.TransactionDate",
Now())
Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
Dim transactionTypeParam As New
SqlParameter("@.TransactionTypeID", 1)
Dim userIDParam As New SqlParameter("@.UserID", 1)
Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
cmd.Parameters.Add(transDateParam)
cmd.Parameters.Add(customerIDParam)
cmd.Parameters.Add(transactionTypeParam)
cmd.Parameters.Add(userIDParam)
cmd.Parameters.Add(paymentTypeParam)
Dim previousConnectionState As ConnectionState = conn.State
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
MsgBox(cmd.ExecuteScalar)
Finally
If previousConnectionState = ConnectionState.Closed Then
conn.Close()
End If
End Try
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegr oups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
>
Is there an existing transaction running in the connection? Perhaps a
System.Transactions.TransactionScope? Or some other bit of code running a
BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
>0 there is a transaction.
David
|||On 19 Mar, 21:52, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:

> Is there an existing transaction running in the connection? Perhaps a
> System.Transactions.TransactionScope? Or some other bit of code running a
> BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
> David
Hi David,
Thanks for the reply, I dont think so, I'm running this bit of code as
the first thing my application does in the Form Load Code. I changed
the last line of the SP to
SELECT @.@.trancount AS ID , and the message box returned a blank or
empty string, is this how I was supposed to check?
|||Phil,
With "SET IMPLICIT_TRANSACTIONS OFF" you need to somewhere explicitly commit
the transaction. Your VB code looks like you are closing you connection
without ever issuing a commit.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegr oups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposT ill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>
|||General Issues:
1) SET NOCOUNT ON should be first line of almost any sproc.
2) Use an OUTPUT variable for a single row, single value return from a
sproc.
Specific Question Issues:
3) Get rid of SET IMPLICIT_TRANSACTIONS OFF
4) Use BEGIN TRAN..DML..Check for Error..COMMIT/ROLLBACK TRAN methodology in
your sproc.
5) if the above doesn't work, it could be something funky with the MSGBOX
workings. Set a variable equal to the sproc return and then display that
variable in the msgbox.
TheSQLGuru
President
Indicium Resources, Inc.
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegr oups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposT ill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>
|||Ok, here is my SProc, and I have changed my VB code to display the
output parameter in the message box, but still no joy, it give me the
next ID number in the table, but when I check the table still nothing
there.
ALTER PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SET @.ID = SCOPE_IDENTITY()
COMMIT TRAN
|||On 20 Mar, 08:50, "Steen Schlter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> phil...@.googlemail.com wrote:
>
>
> I know it's a very stupid question, but are you sure that you look for
> the new record in the correct table?
> What if you extend your code to do a SELECT for the record with the
> actual @.ID? If that gives you a record, it should be in the database.
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator- Hide quoted text -
> - Show quoted text -
Hi Steen,
thanks for replying, yes I did consider that, and will try it when I
get home tonight, however I'm sure that I'm on the right database
because I always get the ID no 10, every time I run it, and in the
table that I check the last ID number was 9.
I'm starting to think maybe its some sort of permissions problem, but
I dont really know much about security in SQL 2005. The fact the the
procedure works fine when I try it in the IDE is the bit that is
really confusing me.
|||
> Maybe I'm misunderstanding what you are saying, but if you get the same
> ID every time it looks like it's not inserting anything? If the insert
> is succesfully, the @.ID should be incremented with the IDENTITY
> increment value. If the ID stays the same everytime you run the proc is
> looks like it's not inserting anything.
Hi Steen,
Yes that is the problem, despite my stored procedure returning the
next ID number like the data has been entered, when I check the table
nothing new has actually been added.
Does the proc look like it is correct? If so then I think maybe I will
try a VB.Net newsgroup and see if there is a problem with my code, but
it all looks ok to me.
Phil
|||Do you check for execution errors on return?
Also, just to be safe, I consider it good practice to wrap the entire
SP in a BEGIN/END pair. I don't recall if that's an issue when doing
this stuff thru VB, but it could be.
J.
On 19 Mar 2007 14:41:44 -0700, philhey@.googlemail.com wrote:

>Hi,
>I have a problem where an insert stored procedure does not commit to
>the database from a vb.net program. I can run the stored procedure
>fine through the IDE, but when I use the following vb code the message
>box shows the next ID number but when I check the database no new row
>has been added. Any ideas?
>Phil
>*******STORED PROCEDURE
>CREATE PROCEDURE MYSP_InsertEposTransaction
>@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
>@.TransactionTypeID AS Integer, @.UserID AS Integer,
>@.PaymentTypeID AS Integer
>AS
BEGIN

>SET IMPLICIT_TRANSACTIONS OFF
>INSERT EposTransaction
>(
>TransactionDate,
>CustomerID,
>TransactionTypeID,
>UserID,
>PaymentTypeID
>)
>VALUES
>(
>@.TransactionDate,
>@.CustomerID,
>@.TransactionTypeID,
>@.UserID,
>@.PaymentTypeID
>)
>SELECT SCOPE_IDENTITY() AS ID
END

>
>*VB CODE
>Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
>\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Epos Till.mdf;Integrated
>Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
>procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
>Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
>SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try

insert stored procedure does not commit to the database

Hi,
I have a problem where an insert stored procedure does not commit to
the database from a vb.net program. I can run the stored procedure
fine through the IDE, but when I use the following vb code the message
box shows the next ID number but when I check the database no new row
has been added. Any ideas?
Phil
*******STORED PROCEDURE
CREATE PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer
AS
SET IMPLICIT_TRANSACTIONS OFF
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SELECT SCOPE_IDENTITY() AS ID
*VB CODE
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposTill.mdf;Integrated
Security=True;User Instance=True"
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MYSP_InsertEposTransaction"
' Create a SqlParameter for each parameter in the stored
procedure.
Dim transDateParam As New SqlParameter("@.TransactionDate",
Now())
Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
Dim transactionTypeParam As New
SqlParameter("@.TransactionTypeID", 1)
Dim userIDParam As New SqlParameter("@.UserID", 1)
Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
cmd.Parameters.Add(transDateParam)
cmd.Parameters.Add(customerIDParam)
cmd.Parameters.Add(transactionTypeParam)
cmd.Parameters.Add(userIDParam)
cmd.Parameters.Add(paymentTypeParam)
Dim previousConnectionState As ConnectionState = conn.State
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
MsgBox(cmd.ExecuteScalar)
Finally
If previousConnectionState = ConnectionState.Closed Then
conn.Close()
End If
End Try<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
>
Is there an existing transaction running in the connection? Perhaps a
System.Transactions.TransactionScope? Or some other bit of code running a
BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
>0 there is a transaction.
David|||On 19 Mar, 21:52, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> Is there an existing transaction running in the connection? Perhaps a
> System.Transactions.TransactionScope? Or some other bit of code running a
> BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
> >0 there is a transaction.
> David
Hi David,
Thanks for the reply, I dont think so, I'm running this bit of code as
the first thing my application does in the Form Load Code. I changed
the last line of the SP to
SELECT @.@.trancount AS ID , and the message box returned a blank or
empty string, is this how I was supposed to check?|||Phil,
With "SET IMPLICIT_TRANSACTIONS OFF" you need to somewhere explicitly commit
the transaction. Your VB code looks like you are closing you connection
without ever issuing a commit.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposTill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>|||General Issues:
1) SET NOCOUNT ON should be first line of almost any sproc.
2) Use an OUTPUT variable for a single row, single value return from a
sproc.
Specific Question Issues:
3) Get rid of SET IMPLICIT_TRANSACTIONS OFF
4) Use BEGIN TRAN..DML..Check for Error..COMMIT/ROLLBACK TRAN methodology in
your sproc.
5) if the above doesn't work, it could be something funky with the MSGBOX
workings. Set a variable equal to the sproc return and then display that
variable in the msgbox.
--
TheSQLGuru
President
Indicium Resources, Inc.
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposTill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>|||Ok, here is my SProc, and I have changed my VB code to display the
output parameter in the message box, but still no joy, it give me the
next ID number in the table, but when I check the table still nothing
there.
ALTER PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SET @.ID = SCOPE_IDENTITY()
COMMIT TRAN|||philhey@.googlemail.com wrote:
> Ok, here is my SProc, and I have changed my VB code to display the
> output parameter in the message box, but still no joy, it give me the
> next ID number in the table, but when I check the table still nothing
> there.
> ALTER PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
> AS
> SET NOCOUNT ON
> BEGIN TRAN
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SET @.ID = SCOPE_IDENTITY()
> COMMIT TRAN
>
I know it's a very stupid question, but are you sure that you look for
the new record in the correct table?
What if you extend your code to do a SELECT for the record with the
actual @.ID? If that gives you a record, it should be in the database.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||On 20 Mar, 08:50, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> phil...@.googlemail.com wrote:
> > Ok, here is my SProc, and I have changed my VB code to display the
> > output parameter in the message box, but still no joy, it give me the
> > next ID number in the table, but when I check the table still nothing
> > there.
> > ALTER PROCEDURE MYSP_InsertEposTransaction
> > @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> > @.TransactionTypeID AS Integer, @.UserID AS Integer,
> > @.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
> > AS
> > SET NOCOUNT ON
> > BEGIN TRAN
> > INSERT EposTransaction
> > (
> > TransactionDate,
> > CustomerID,
> > TransactionTypeID,
> > UserID,
> > PaymentTypeID
> > )
> > VALUES
> > (
> > @.TransactionDate,
> > @.CustomerID,
> > @.TransactionTypeID,
> > @.UserID,
> > @.PaymentTypeID
> > )
> > SET @.ID =3D SCOPE_IDENTITY()
> > COMMIT TRAN
> I know it's a very stupid question, but are you sure that you look for
> the new record in the correct table?
> What if you extend your code to do a SELECT for the record with the
> actual @.ID? If that gives you a record, it should be in the database.
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator- Hide quoted text -
> - Show quoted text -
Hi Steen,
thanks for replying, yes I did consider that, and will try it when I
get home tonight, however I'm sure that I'm on the right database
because I always get the ID no 10, every time I run it, and in the
table that I check the last ID number was 9.
I'm starting to think maybe its some sort of permissions problem, but
I dont really know much about security in SQL 2005. The fact the the
procedure works fine when I try it in the IDE is the bit that is
really confusing me.|||philhey@.googlemail.com wrote:
> On 20 Mar, 08:50, "Steen Schlüter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
>> phil...@.googlemail.com wrote:
>> Ok, here is my SProc, and I have changed my VB code to display the
>> output parameter in the message box, but still no joy, it give me the
>> next ID number in the table, but when I check the table still nothing
>> there.
>> ALTER PROCEDURE MYSP_InsertEposTransaction
>> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
>> @.TransactionTypeID AS Integer, @.UserID AS Integer,
>> @.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
>> AS
>> SET NOCOUNT ON
>> BEGIN TRAN
>> INSERT EposTransaction
>> (
>> TransactionDate,
>> CustomerID,
>> TransactionTypeID,
>> UserID,
>> PaymentTypeID
>> )
>> VALUES
>> (
>> @.TransactionDate,
>> @.CustomerID,
>> @.TransactionTypeID,
>> @.UserID,
>> @.PaymentTypeID
>> )
>> SET @.ID = SCOPE_IDENTITY()
>> COMMIT TRAN
>> I know it's a very stupid question, but are you sure that you look for
>> the new record in the correct table?
>> What if you extend your code to do a SELECT for the record with the
>> actual @.ID? If that gives you a record, it should be in the database.
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator- Hide quoted text -
>> - Show quoted text -
> Hi Steen,
> thanks for replying, yes I did consider that, and will try it when I
> get home tonight, however I'm sure that I'm on the right database
> because I always get the ID no 10, every time I run it, and in the
> table that I check the last ID number was 9.
> I'm starting to think maybe its some sort of permissions problem, but
> I dont really know much about security in SQL 2005. The fact the the
> procedure works fine when I try it in the IDE is the bit that is
> really confusing me.
>
>
Maybe I'm misunderstanding what you are saying, but if you get the same
ID every time it looks like it's not inserting anything? If the insert
is succesfully, the @.ID should be incremented with the IDENTITY
increment value. If the ID stays the same everytime you run the proc is
looks like it's not inserting anything.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||> Maybe I'm misunderstanding what you are saying, but if you get the same
> ID every time it looks like it's not inserting anything? If the insert
> is succesfully, the @.ID should be incremented with the IDENTITY
> increment value. If the ID stays the same everytime you run the proc is
> looks like it's not inserting anything.
Hi Steen,
Yes that is the problem, despite my stored procedure returning the
next ID number like the data has been entered, when I check the table
nothing new has actually been added.
Does the proc look like it is correct? If so then I think maybe I will
try a VB.Net newsgroup and see if there is a problem with my code, but
it all looks ok to me.
Phil|||philhey@.googlemail.com wrote:
>> Maybe I'm misunderstanding what you are saying, but if you get the same
>> ID every time it looks like it's not inserting anything? If the insert
>> is succesfully, the @.ID should be incremented with the IDENTITY
>> increment value. If the ID stays the same everytime you run the proc is
>> looks like it's not inserting anything.
> Hi Steen,
> Yes that is the problem, despite my stored procedure returning the
> next ID number like the data has been entered, when I check the table
> nothing new has actually been added.
> Does the proc look like it is correct? If so then I think maybe I will
> try a VB.Net newsgroup and see if there is a problem with my code, but
> it all looks ok to me.
> Phil
>
Hi Phil,
Since you've been able to successfully run the stored proc alone, I
assume that's ok. I'm not a programmer so unfortunately I'm not able to
tell if your VB code looks ok or not. It might be a good idea to let
somebody in a VB group look a the code - unless somebody else in here
can verify it's ok?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Do you check for execution errors on return?
Also, just to be safe, I consider it good practice to wrap the entire
SP in a BEGIN/END pair. I don't recall if that's an issue when doing
this stuff thru VB, but it could be.
J.
On 19 Mar 2007 14:41:44 -0700, philhey@.googlemail.com wrote:
>Hi,
>I have a problem where an insert stored procedure does not commit to
>the database from a vb.net program. I can run the stored procedure
>fine through the IDE, but when I use the following vb code the message
>box shows the next ID number but when I check the database no new row
>has been added. Any ideas?
>Phil
>*******STORED PROCEDURE
>CREATE PROCEDURE MYSP_InsertEposTransaction
>@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
>@.TransactionTypeID AS Integer, @.UserID AS Integer,
>@.PaymentTypeID AS Integer
>AS
BEGIN
>SET IMPLICIT_TRANSACTIONS OFF
>INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
END
>
>*VB CODE
>Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
>\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposTill.mdf;Integrated
>Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
>procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
>Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
>SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try

insert stored procedure does not commit to the database

Hi,
I have a problem where an insert stored procedure does not commit to
the database from a vb.net program. I can run the stored procedure
fine through the IDE, but when I use the following vb code the message
box shows the next ID number but when I check the database no new row
has been added. Any ideas?
Phil
*******STORED PROCEDURE
CREATE PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer
AS
SET IMPLICIT_TRANSACTIONS OFF
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SELECT SCOPE_IDENTITY() AS ID
*VB CODE
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\EposTill.mdf;Integrated
Security=True;User Instance=True"
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MYSP_InsertEposTransaction"
' Create a SqlParameter for each parameter in the stored
procedure.
Dim transDateParam As New SqlParameter("@.TransactionDate",
Now())
Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
Dim transactionTypeParam As New
SqlParameter("@.TransactionTypeID", 1)
Dim userIDParam As New SqlParameter("@.UserID", 1)
Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
cmd.Parameters.Add(transDateParam)
cmd.Parameters.Add(customerIDParam)
cmd.Parameters.Add(transactionTypeParam)
cmd.Parameters.Add(userIDParam)
cmd.Parameters.Add(paymentTypeParam)
Dim previousConnectionState As ConnectionState = conn.State
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
MsgBox(cmd.ExecuteScalar)
Finally
If previousConnectionState = ConnectionState.Closed Then
conn.Close()
End If
End Try<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
>
Is there an existing transaction running in the connection? Perhaps a
System.Transactions.TransactionScope? Or some other bit of code running a
BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
>0 there is a transaction.
David|||On 19 Mar, 21:52, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:

> Is there an existing transaction running in the connection? Perhaps a
> System.Transactions.TransactionScope? Or some other bit of code running a
> BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. I
f
> David
Hi David,
Thanks for the reply, I dont think so, I'm running this bit of code as
the first thing my application does in the Form Load Code. I changed
the last line of the SP to
SELECT @.@.trancount AS ID , and the message box returned a blank or
empty string, is this how I was supposed to check?|||Phil,
With "SET IMPLICIT_TRANSACTIONS OFF" you need to somewhere explicitly commit
the transaction. Your VB code looks like you are closing you connection
without ever issuing a commit.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\EposTill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>|||General Issues:
1) SET NOCOUNT ON should be first line of almost any sproc.
2) Use an OUTPUT variable for a single row, single value return from a
sproc.
Specific Question Issues:
3) Get rid of SET IMPLICIT_TRANSACTIONS OFF
4) Use BEGIN TRAN..DML..Check for Error..COMMIT/ROLLBACK TRAN methodology in
your sproc.
5) if the above doesn't work, it could be something funky with the MSGBOX
workings. Set a variable equal to the sproc return and then display that
variable in the msgbox.
TheSQLGuru
President
Indicium Resources, Inc.
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.com...
> Hi,
> I have a problem where an insert stored procedure does not commit to
> the database from a vb.net program. I can run the stored procedure
> fine through the IDE, but when I use the following vb code the message
> box shows the next ID number but when I check the database no new row
> has been added. Any ideas?
> Phil
> *******STORED PROCEDURE
> CREATE PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer
> AS
> SET IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\EposTill.mdf;Integrated
> Security=True;User Instance=True"
> Dim cmd As New SqlCommand()
> cmd.Connection = conn
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "MYSP_InsertEposTransaction"
> ' Create a SqlParameter for each parameter in the stored
> procedure.
> Dim transDateParam As New SqlParameter("@.TransactionDate",
> Now())
> Dim customerIDParam As New SqlParameter("@.CustomerID", 1)
> Dim transactionTypeParam As New
> SqlParameter("@.TransactionTypeID", 1)
> Dim userIDParam As New SqlParameter("@.UserID", 1)
> Dim paymentTypeParam As New SqlParameter("@.PaymentTypeID", 1)
> cmd.Parameters.Add(transDateParam)
> cmd.Parameters.Add(customerIDParam)
> cmd.Parameters.Add(transactionTypeParam)
> cmd.Parameters.Add(userIDParam)
> cmd.Parameters.Add(paymentTypeParam)
> Dim previousConnectionState As ConnectionState = conn.State
> Try
> If conn.State = ConnectionState.Closed Then
> conn.Open()
> End If
> MsgBox(cmd.ExecuteScalar)
> Finally
> If previousConnectionState = ConnectionState.Closed Then
> conn.Close()
> End If
> End Try
>|||Ok, here is my SProc, and I have changed my VB code to display the
output parameter in the message box, but still no joy, it give me the
next ID number in the table, but when I check the table still nothing
there.
ALTER PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SET @.ID = SCOPE_IDENTITY()
COMMIT TRAN|||philhey@.googlemail.com wrote:
> Ok, here is my SProc, and I have changed my VB code to display the
> output parameter in the message box, but still no joy, it give me the
> next ID number in the table, but when I check the table still nothing
> there.
> ALTER PROCEDURE MYSP_InsertEposTransaction
> @.TransactionDate AS DATETIME, @.CustomerID AS Integer,
> @.TransactionTypeID AS Integer, @.UserID AS Integer,
> @.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
> AS
> SET NOCOUNT ON
> BEGIN TRAN
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SET @.ID = SCOPE_IDENTITY()
> COMMIT TRAN
>
I know it's a very stupid question, but are you sure that you look for
the new record in the correct table?
What if you extend your code to do a SELECT for the record with the
actual @.ID? If that gives you a record, it should be in the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||On 20 Mar, 08:50, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> phil...@.googlemail.com wrote:
>
>
>
> I know it's a very stupid question, but are you sure that you look for
> the new record in the correct table?
> What if you extend your code to do a SELECT for the record with the
> actual @.ID? If that gives you a record, it should be in the database.
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator- Hide quoted text -
> - Show quoted text -
Hi Steen,
thanks for replying, yes I did consider that, and will try it when I
get home tonight, however I'm sure that I'm on the right database
because I always get the ID no 10, every time I run it, and in the
table that I check the last ID number was 9.
I'm starting to think maybe its some sort of permissions problem, but
I dont really know much about security in SQL 2005. The fact the the
procedure works fine when I try it in the IDE is the bit that is
really confusing me.|||philhey@.googlemail.com wrote:
> On 20 Mar, 08:50, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
> Hi Steen,
> thanks for replying, yes I did consider that, and will try it when I
> get home tonight, however I'm sure that I'm on the right database
> because I always get the ID no 10, every time I run it, and in the
> table that I check the last ID number was 9.
> I'm starting to think maybe its some sort of permissions problem, but
> I dont really know much about security in SQL 2005. The fact the the
> procedure works fine when I try it in the IDE is the bit that is
> really confusing me.
>
>
Maybe I'm misunderstanding what you are saying, but if you get the same
ID every time it looks like it's not inserting anything? If the insert
is succesfully, the @.ID should be incremented with the IDENTITY
increment value. If the ID stays the same everytime you run the proc is
looks like it's not inserting anything.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||
> Maybe I'm misunderstanding what you are saying, but if you get the same
> ID every time it looks like it's not inserting anything? If the insert
> is succesfully, the @.ID should be incremented with the IDENTITY
> increment value. If the ID stays the same everytime you run the proc is
> looks like it's not inserting anything.
Hi Steen,
Yes that is the problem, despite my stored procedure returning the
next ID number like the data has been entered, when I check the table
nothing new has actually been added.
Does the proc look like it is correct? If so then I think maybe I will
try a VB.Net newsgroup and see if there is a problem with my code, but
it all looks ok to me.
Phil

insert statement

When I run below statement, I got 3 records insertion.
I only want 1 record added when there was any update on any column on the
source data. I don't want update statement because, I would like to see all
the change from time to time.
Please help,
Culam.
INSERT INTO CUSTOMER_PROFILE_HIST
([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
FROM
CUSTOMER_PROFILE src
LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
WHERE
ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)try using inner join.. Just a guess
--
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||You will have problems after there are 2 records for the customer in
the history file because one will always be different than the current.
You need to only compare to the latest historical record.|||Thanks Jeff.
Do you know the way to insert 1 record when multiple fields are changed?
My method will insert new records for each changed field.
Lam
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||try this.
INSERT INTO CUSTOMER_PROFILE_HIST
([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
FROM
CUSTOMER_PROFILE src
WHERE
not exists( select 1 from CUSTOMER_PROFILE_HIST dst where
src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
ISNULL(src.[RATE], 0) = ISNULL(dst.[RATE],0)
AND ISNULL(src.[AGE1], 0) = ISNULL(dst.[AGE1], 0)
AND ISNULL(src.[AGE2], 0) = ISNULL(dst.[AGE2], 0)
)|||Thanks, it works.
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||On Tue, 9 May 2006 13:32:03 -0700, culam wrote:

>Thanks Jeff.
>Do you know the way to insert 1 record when multiple fields are changed?
>My method will insert new records for each changed field.
Hi Lam,
No, it won't.
It will insert new rows for each existing row in the history table. If
you have three rows in CUSTOMER_PROFILE_HIST, you'll get three
additional rows (or rather: maximum three rows - if any of the existing
history rows happens to match the current rw on all columns, you'll only
get two new rows).
Check JeffB's reply - he hit the nail right on the head.
If yoou need more assitance, then please check out www.aspfaq.com/5006
to find out what additional information yoou need to give to make it
possible for us to help you.
Hugo Kornelis, SQL Server MVP|||All Credit goes to Jeff. I just ex[anded his point of view.
--
"culam" wrote:
> Thanks, it works.
> "culam" wrote:
>|||Unless 3 changed fields actually causes 3 separate inserts. I have seen
this happen (on the application side) where a change to department, salary,
and jobcode actually triggers 3 separate transactions. This is not to
suggest that your post is inaccurate, only that the OP could possibly be
referring to something else here...
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:e23262pmojsjfnt5aph2jdausbrmmv9lcc@.
4ax.com...
> On Tue, 9 May 2006 13:32:03 -0700, culam wrote:
>
> Hi Lam,
> No, it won't.
> It will insert new rows for each existing row in the history table. If
> you have three rows in CUSTOMER_PROFILE_HIST, you'll get three
> additional rows (or rather: maximum three rows - if any of the existing
> history rows happens to match the current rw on all columns, you'll only
> get two new rows).
> Check JeffB's reply - he hit the nail right on the head.
> If yoou need more assitance, then please check out www.aspfaq.com/5006
> to find out what additional information yoou need to give to make it
> possible for us to help you.
> --
> Hugo Kornelis, SQL Server MVP

Insert Statement

When I execute the INSERT statement on SQL Server 2000, the table on which I
run this statement is not affected. What could be the problem? Could anyone
please tell me what's the maximum size of a table in SQL Server 2000.
Thanking you in advance.Choi
Please read this article in the BOL
"Estimating the Size of a Table"
"Choi" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD1E2F3-E9DE-4E87-9FAB-68FC64912C40@.microsoft.com...
> When I execute the INSERT statement on SQL Server 2000, the table on which
I run this statement is not affected. What could be the problem? Could
anyone please tell me what's the maximum size of a table in SQL Server 2000.
> Thanking you in advance.|||Hi,
Check whether the implicit transaction is set to "on".
How to verify,
1. In Query Analyzer, go to tools -- Options
2. Go to connection Properties tab
3. Check whether SET IMPLICIT_TRANSACTIONS is selected.
If selected remove that option and re-run the insert statement.
Other possibility for this can be ,
BEGIN TRAN
insert statement
ROLLBACK TRAN
Thanks
Hari
MCDBA
"Choi" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD1E2F3-E9DE-4E87-9FAB-68FC64912C40@.microsoft.com...
> When I execute the INSERT statement on SQL Server 2000, the table on which
I run this statement is not affected. What could be the problem? Could
anyone please tell me what's the maximum size of a table in SQL Server 2000.
> Thanking you in advance.

Insert Statement

When I execute the INSERT statement on SQL Server 2000, the table on which I run this statement is not affected. What could be the problem? Could anyone please tell me what's the maximum size of a table in SQL Server 2000
Thanking you in advance.Choi
Please read this article in the BOL
"Estimating the Size of a Table"
"Choi" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD1E2F3-E9DE-4E87-9FAB-68FC64912C40@.microsoft.com...
> When I execute the INSERT statement on SQL Server 2000, the table on which
I run this statement is not affected. What could be the problem? Could
anyone please tell me what's the maximum size of a table in SQL Server 2000.
> Thanking you in advance.|||Hi,
Check whether the implicit transaction is set to "on".
How to verify,
1. In Query Analyzer, go to tools -- Options
2. Go to connection Properties tab
3. Check whether SET IMPLICIT_TRANSACTIONS is selected.
If selected remove that option and re-run the insert statement.
Other possibility for this can be ,
BEGIN TRAN
insert statement
ROLLBACK TRAN
Thanks
Hari
MCDBA
"Choi" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD1E2F3-E9DE-4E87-9FAB-68FC64912C40@.microsoft.com...
> When I execute the INSERT statement on SQL Server 2000, the table on which
I run this statement is not affected. What could be the problem? Could
anyone please tell me what's the maximum size of a table in SQL Server 2000.
> Thanking you in advance.

INSERT SELECT?

Hi all,
I am trying to run an INSERT SELECT statement. This should be an easy
query!!!
Code:
SET IDENTITY_INSERT asmt_v1_areas ON
INSERT INTO asmt_v1_areas ('asmt_v1_area_id','name','mid') SELECT
assmnt_area_id, name, mid FROM assmnt_areas
When i try to run this query i get an error message saying invalid object
name 'asmt_v1_areas'
This object does exists and i can issue select statements on it.
I assume i have some kind of syntax error.
Can someone enlighten me'
Cheers,
AdamAdam Knight,
Can you check the object's owner?. SQL Server is looking for the table
dbo.asmt_v1_areas. The table should have an identity column.
AMB
"Adam Knight" wrote:

> Hi all,
> I am trying to run an INSERT SELECT statement. This should be an easy
> query!!!
> Code:
> SET IDENTITY_INSERT asmt_v1_areas ON
> INSERT INTO asmt_v1_areas ('asmt_v1_area_id','name','mid') SELECT
> assmnt_area_id, name, mid FROM assmnt_areas
> When i try to run this query i get an error message saying invalid object
> name 'asmt_v1_areas'
> This object does exists and i can issue select statements on it.
> I assume i have some kind of syntax error.
> Can someone enlighten me'
> Cheers,
> Adam
>
>|||Don't use quotation marks around column names - instead of:
INSERT INTO asmt_v1_areas ('asmt_v1_area_id','name','mid')
use:
INSERT INTO asmt_v1_areas (asmt_v1_area_id, name, mid)
"Adam Knight" <dev@.brightidea.com.au> wrote in message
news:u6bzvfP2FHA.3788@.tk2msftngp13.phx.gbl...
> Hi all,
> I am trying to run an INSERT SELECT statement. This should be an easy
> query!!!
> Code:
> SET IDENTITY_INSERT asmt_v1_areas ON
> INSERT INTO asmt_v1_areas ('asmt_v1_area_id','name','mid') SELECT
> assmnt_area_id, name, mid FROM assmnt_areas
> When i try to run this query i get an error message saying invalid object
> name 'asmt_v1_areas'
> This object does exists and i can issue select statements on it.
> I assume i have some kind of syntax error.
> Can someone enlighten me'
> Cheers,
> Adam
>

insert question

This is more of a sql query syntax question then anything but I need to run it on my handheld.

I have 3 data collection tables, and 3 staging tables that will be used to PUSH the data back to the SQL Server.

so here is what happens:

to populate staging table 1 I do

insert into stagingtable1 (id, col1, col2, col3) select newid, col1, col2, col3 from datacollection table1

staging table 2:

insert into stagingtable2 (id) select id from stagingtable1

staging table 3.

insert into stagingtable2 (id - (now this ID field needs to be the same id field as staging table 2 and staging table 1 have) col1, col2, col3, col4, from DataCollectionTable2

how can I create the insert SQL for my staging table 3?

revised:

there is no columns I can join on to get the uploadID to insert into stagingtable 3

Insert Query has Conflict with Foreign Key

there have a exception when I trying to run a insert query

the exception is occur on cmd.ExecuteReder(); and shows
Insert Query conflict with Foreign key..

why? and how to resolve it?

thank you

You need to look up foreign keys in books online, but basically it has to do with putting invalid data into a column that references the data in the primary (or unique) key of another table. As an example:

use tempdb
go
create table parent
(
parentId int primary key
)
go
create table child
(
childId int primary key,
parentId int foreign key references parent(parentId)
)
go
insert into child
values (1,1)
go
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__child__parentId__0CBAE877". The conflict occurred in database "tempdb", table "dbo.parent", column 'parentId'.
The statement has been terminated.
go
insert into parent
values (1)
go
insert into child
values (1,1)

INSERT query gets String data right truncation through ODBC but it works in SQL Server 7?

Hello Helpfull Helpers,
When I try to run an insert query from my Web site I get the following
error...
Error Diagnostic Information
ODBC Error Code = 22001 (String data right truncation)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
would be truncated.
The error occurred while processing an element with a general
identifier of (CFQUERY), occupying document position (701:3) to
(701:73).
...but when I copy and paste the query into SQL Server Enterprise
Manager, the Insert succeeds.
Why does the query work in SQL Server Enterprise Manager but not from
my Web page? The Web page used to work fine.
My System Information:
======================
Windows 2000 Server (Operating System)
Cold Fusion 4.5 Server (Dynamic Web Server)
SQL Server 7.0 (Database Server)
The Query That Fails In The Web Page But Succeeds In Enterprise
Manager:
================================================== ======================
INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
f13, f16, f17, f18, f19)
VALUES(
14036,
'09/17/2004 1:3:7 PM',
0,
'o',
'Testing new limit region functionality',
'Limit region should include cascading city, state or province,
country, and zip.',
'Fully functional.',
'',
'11',
'p',
'',
'',
'3',
'5',
0,
0 )
Table Information (field names have been changed to protect the
innocent):
================================================== ========================
CREATE TABLE [dbo].[dboJob] (
[JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[CUID] [decimal](18, 0) NOT NULL ,
[f1] [varchar] (2) NOT NULL ,
[f2] [datetime] NOT NULL ,
[f3] [datetime] NOT NULL ,
[f4] [money] NOT NULL ,
[f5] [varchar] (1) NOT NULL ,
[f6] [varchar] (100) NULL ,
[f7] [varchar] (3000) NOT NULL ,
[f8] [varchar] (1500) NOT NULL ,
[f9] [varchar] (3000) NULL ,
[f10] [numeric](18, 0) NOT NULL ,
[f11] [char] (1) NOT NULL ,
[f12] [varchar] (200) NOT NULL ,
[f13] [varchar] (64) NULL ,
[f14] [varchar] (1) NOT NULL ,
[f15] [varchar] (1) NOT NULL ,
[f16] [varchar] (1) NOT NULL ,
[f17] [varchar] (1) NOT NULL ,
[f18] [money] NOT NULL ,
[f19] [money] NOT NULL ,
[f20] [datetime] NOT NULL
) ON [PRIMARY]
GO
Thanks,
Nate
I suggest you try the INSERT statement through QA. After the INSERT, check whether the data has been
truncated. Also, read about SET ANSI_WARNING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nate" <nathandeneau@.cox.net> wrote in message
news:4630acfb.0409141227.64eb7823@.posting.google.c om...
> Hello Helpfull Helpers,
> When I try to run an insert query from my Web site I get the following
> error...
> Error Diagnostic Information
> ODBC Error Code = 22001 (String data right truncation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
> would be truncated.
> The error occurred while processing an element with a general
> identifier of (CFQUERY), occupying document position (701:3) to
> (701:73).
> ...but when I copy and paste the query into SQL Server Enterprise
> Manager, the Insert succeeds.
> Why does the query work in SQL Server Enterprise Manager but not from
> my Web page? The Web page used to work fine.
> My System Information:
> ======================
> Windows 2000 Server (Operating System)
> Cold Fusion 4.5 Server (Dynamic Web Server)
> SQL Server 7.0 (Database Server)
> The Query That Fails In The Web Page But Succeeds In Enterprise
> Manager:
> ================================================== ======================
> INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
> f13, f16, f17, f18, f19)
> VALUES(
> 14036,
> '09/17/2004 1:3:7 PM',
> 0,
> 'o',
> 'Testing new limit region functionality',
> 'Limit region should include cascading city, state or province,
> country, and zip.',
> 'Fully functional.',
> '',
> '11',
> 'p',
> '',
> '',
> '3',
> '5',
> 0,
> 0 )
> Table Information (field names have been changed to protect the
> innocent):
> ================================================== ========================
> CREATE TABLE [dbo].[dboJob] (
> [JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
> [CUID] [decimal](18, 0) NOT NULL ,
> [f1] [varchar] (2) NOT NULL ,
> [f2] [datetime] NOT NULL ,
> [f3] [datetime] NOT NULL ,
> [f4] [money] NOT NULL ,
> [f5] [varchar] (1) NOT NULL ,
> [f6] [varchar] (100) NULL ,
> [f7] [varchar] (3000) NOT NULL ,
> [f8] [varchar] (1500) NOT NULL ,
> [f9] [varchar] (3000) NULL ,
> [f10] [numeric](18, 0) NOT NULL ,
> [f11] [char] (1) NOT NULL ,
> [f12] [varchar] (200) NOT NULL ,
> [f13] [varchar] (64) NULL ,
> [f14] [varchar] (1) NOT NULL ,
> [f15] [varchar] (1) NOT NULL ,
> [f16] [varchar] (1) NOT NULL ,
> [f17] [varchar] (1) NOT NULL ,
> [f18] [money] NOT NULL ,
> [f19] [money] NOT NULL ,
> [f20] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Thanks,
> Nate

INSERT query gets String data right truncation through ODBC but it works in SQL Server 7?

Hello Helpfull Helpers,
When I try to run an insert query from my Web site I get the following
error...
Error Diagnostic Information
ODBC Error Code = 22001 (String data right truncation)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
would be truncated.
The error occurred while processing an element with a general
identifier of (CFQUERY), occupying document position (701:3) to
(701:73).
...but when I copy and paste the query into SQL Server Enterprise
Manager, the Insert succeeds.
Why does the query work in SQL Server Enterprise Manager but not from
my Web page? The Web page used to work fine.
My System Information:
====================== Windows 2000 Server (Operating System)
Cold Fusion 4.5 Server (Dynamic Web Server)
SQL Server 7.0 (Database Server)
The Query That Fails In The Web Page But Succeeds In Enterprise
Manager:
======================================================================== INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
f13, f16, f17, f18, f19)
VALUES(
14036,
'09/17/2004 1:3:7 PM',
0,
'o',
'Testing new limit region functionality',
'Limit region should include cascading city, state or province,
country, and zip.',
'Fully functional.',
'',
'11',
'p',
'',
'',
'3',
'5',
0,
0 )
Table Information (field names have been changed to protect the
innocent):
========================================================================== CREATE TABLE [dbo].[dboJob] (
[JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[CUID] [decimal](18, 0) NOT NULL ,
[f1] [varchar] (2) NOT NULL ,
[f2] [datetime] NOT NULL ,
[f3] [datetime] NOT NULL ,
[f4] [money] NOT NULL ,
[f5] [varchar] (1) NOT NULL ,
[f6] [varchar] (100) NULL ,
[f7] [varchar] (3000) NOT NULL ,
[f8] [varchar] (1500) NOT NULL ,
[f9] [varchar] (3000) NULL ,
[f10] [numeric](18, 0) NOT NULL ,
[f11] [char] (1) NOT NULL ,
[f12] [varchar] (200) NOT NULL ,
[f13] [varchar] (64) NULL ,
[f14] [varchar] (1) NOT NULL ,
[f15] [varchar] (1) NOT NULL ,
[f16] [varchar] (1) NOT NULL ,
[f17] [varchar] (1) NOT NULL ,
[f18] [money] NOT NULL ,
[f19] [money] NOT NULL ,
[f20] [datetime] NOT NULL
) ON [PRIMARY]
GO
Thanks,
NateI suggest you try the INSERT statement through QA. After the INSERT, check whether the data has been
truncated. Also, read about SET ANSI_WARNING.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nate" <nathandeneau@.cox.net> wrote in message
news:4630acfb.0409141227.64eb7823@.posting.google.com...
> Hello Helpfull Helpers,
> When I try to run an insert query from my Web site I get the following
> error...
> Error Diagnostic Information
> ODBC Error Code = 22001 (String data right truncation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
> would be truncated.
> The error occurred while processing an element with a general
> identifier of (CFQUERY), occupying document position (701:3) to
> (701:73).
> ...but when I copy and paste the query into SQL Server Enterprise
> Manager, the Insert succeeds.
> Why does the query work in SQL Server Enterprise Manager but not from
> my Web page? The Web page used to work fine.
> My System Information:
> ======================> Windows 2000 Server (Operating System)
> Cold Fusion 4.5 Server (Dynamic Web Server)
> SQL Server 7.0 (Database Server)
> The Query That Fails In The Web Page But Succeeds In Enterprise
> Manager:
> ========================================================================> INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
> f13, f16, f17, f18, f19)
> VALUES(
> 14036,
> '09/17/2004 1:3:7 PM',
> 0,
> 'o',
> 'Testing new limit region functionality',
> 'Limit region should include cascading city, state or province,
> country, and zip.',
> 'Fully functional.',
> '',
> '11',
> 'p',
> '',
> '',
> '3',
> '5',
> 0,
> 0 )
> Table Information (field names have been changed to protect the
> innocent):
> ==========================================================================> CREATE TABLE [dbo].[dboJob] (
> [JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
> [CUID] [decimal](18, 0) NOT NULL ,
> [f1] [varchar] (2) NOT NULL ,
> [f2] [datetime] NOT NULL ,
> [f3] [datetime] NOT NULL ,
> [f4] [money] NOT NULL ,
> [f5] [varchar] (1) NOT NULL ,
> [f6] [varchar] (100) NULL ,
> [f7] [varchar] (3000) NOT NULL ,
> [f8] [varchar] (1500) NOT NULL ,
> [f9] [varchar] (3000) NULL ,
> [f10] [numeric](18, 0) NOT NULL ,
> [f11] [char] (1) NOT NULL ,
> [f12] [varchar] (200) NOT NULL ,
> [f13] [varchar] (64) NULL ,
> [f14] [varchar] (1) NOT NULL ,
> [f15] [varchar] (1) NOT NULL ,
> [f16] [varchar] (1) NOT NULL ,
> [f17] [varchar] (1) NOT NULL ,
> [f18] [money] NOT NULL ,
> [f19] [money] NOT NULL ,
> [f20] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Thanks,
> Nate

Insert Problem.

I am using a DTS package to insert data into a SQL table. This has worked
correctly for the last 2 years. However when I run the code now I get a
Timeout error. I have rebooted the server with no success. If I try and run
the data into a temporary table it works fine.
Is the table corrupt ? If so how can I uncorrupt it ?
Si
Can you provide a little more information on where the table is stored, how
the dts package looks like?
What's the source? What's the destination?
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:CA096C29-74E5-48F2-923A-A00D2321BDF4@.microsoft.com...
>I am using a DTS package to insert data into a SQL table. This has worked
> correctly for the last 2 years. However when I run the code now I get a
> Timeout error. I have rebooted the server with no success. If I try and
> run
> the data into a temporary table it works fine.
> Is the table corrupt ? If so how can I uncorrupt it ?
> Si

Insert Problem.

I am using a DTS package to insert data into a SQL table. This has worked
correctly for the last 2 years. However when I run the code now I get a
Timeout error. I have rebooted the server with no success. If I try and run
the data into a temporary table it works fine.
Is the table corrupt ? If so how can I uncorrupt it ?
SiCan you provide a little more information on where the table is stored, how
the dts package looks like?
What's the source? What's the destination?
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:CA096C29-74E5-48F2-923A-A00D2321BDF4@.microsoft.com...
>I am using a DTS package to insert data into a SQL table. This has worked
> correctly for the last 2 years. However when I run the code now I get a
> Timeout error. I have rebooted the server with no success. If I try and
> run
> the data into a temporary table it works fine.
> Is the table corrupt ? If so how can I uncorrupt it ?
> Si

Insert performance different between two servers

Hi,

Any suggestions on the following as I've kind of run out of ideas.

I have 2 servers which are the same spec ie box, processor etc. The
only difference I can tell is that the production box has raid setup
but the test box hasn't (I think).

I have created a stored procedure to insert 10k rows into a dummy table
with two columns.

I have logged onto the boxes directly so there are no networks issues
here. Also the boxes only have light traffic on them really, there
isn't much going on at the point of running.

The production box inserts the rows two times faster than the test box
i.e 30 secs rather than 1 min. Does anyone have any idea why this could
be, do you think it could be raid?

The prod box has 8 disks I think in hardware Raid 5 but as the test box
has 4 disks and it looks as if all the space is available i doubt raid
is being employed.

Thanks

Ian.

ps. does anyone know if there is a way to check the raid configuration
of a box from within windows? or do you have to re-boot and go through
the setup?"wriggs" <ian.w@.btinternet.com> wrote in message
news:1116923667.752900.260220@.f14g2000cwb.googlegr oups.com...
> Hi,
> Any suggestions on the following as I've kind of run out of ideas.
> I have 2 servers which are the same spec ie box, processor etc. The
> only difference I can tell is that the production box has raid setup
> but the test box hasn't (I think).
> I have created a stored procedure to insert 10k rows into a dummy table
> with two columns.
> I have logged onto the boxes directly so there are no networks issues
> here. Also the boxes only have light traffic on them really, there
> isn't much going on at the point of running.
> The production box inserts the rows two times faster than the test box
> i.e 30 secs rather than 1 min. Does anyone have any idea why this could
> be, do you think it could be raid?
> The prod box has 8 disks I think in hardware Raid 5 but as the test box
> has 4 disks and it looks as if all the space is available i doubt raid
> is being employed.
> Thanks
> Ian.
> ps. does anyone know if there is a way to check the raid configuration
> of a box from within windows? or do you have to re-boot and go through
> the setup?

Regarding performance, if the disk arrangement is the only difference
between the two servers, i.e. same CPU, same memory then that leaves only
the disk configuration. (Its unlikely to have any noticeable bearing, but
you could check that neither system is using heavily fragmented disks.)

Your production box may have raid 5 and 8 disks but are you saying that all
8 disks are used in the raid to produce a single logical disk?

If you consider a raid 5 across 3 disks compared with a single disk system.
Each time you write 2MB of data, each of the 3 disks in the raid would have
1MB of data written, but the single disk system would need to write all 2MB
to the disk. Thus on paper the raid would give the impression of being twice
as fast (i.e only half the time to write). But if you were to change your
single disk system to a dual disk arrangement and have the log file and data
files on separate disks then you would get similar-ish performance to the 3
disk raid. The actual performance you get in reality is tempered by other
factors such as how much data you can push through the bus and how many
buses the disks are on, the characteristics of the individual disks, whether
you are using software or hardware raid and in the case of hardware raid the
characteristics of the raid controller (such as how large a cache it has and
whether it does write behind caching).

One other thought, on one of my Windows 2003 servers my sql server database
was crawling along when doing bulk inserts. It turned out to be that
write-behind caching on the disk (single disk system) was turned off. That
meant that each time sqlserver updated a data file or wrote to the log, it
couldn't carry on until the disk write had been completed. Turning on
caching in windows and it was much much faster.

As for checking the raid configuration from windows. If you are using a
software raid then you should be able to see the raid configuration from
within computer management > storage > disk management. If it is a hardware
raid then it may have come with a utility for allowing you to configure (or
monitor) it from within windows. You will need to check with the
manufacturer of the raid card. Failing that, reboot and the raid card should
give you the option to manage/view the raid configuration. If you do that
just be careful that you don't change the raid configuration - do that and
you loose your raid and everything on it.

Hope this helps,

Brian.

www.cryer.co.uk/brian|||hmmm.. well i checked with the win2000 guys who originally setup the
box and it seems both boxes are setup with raid 5, its just that the
production box has got 8 drives in and the test box has 4 and obviously
half the space, so could this be the problem as less data is being
written to each individual drive on the production box??

One other point to note is that the test box seems to have a better
raid controller. the production one has a hp smart array 5300 where the
test box has a 6400 controller.

But obviously it isn't making that much difference|||"wriggs" <ian.w@.btinternet.com> wrote in message
news:1116938928.959078.246100@.z14g2000cwz.googlegr oups.com...
> hmmm.. well i checked with the win2000 guys who originally setup the
> box and it seems both boxes are setup with raid 5, its just that the
> production box has got 8 drives in and the test box has 4 and obviously
> half the space, so could this be the problem as less data is being
> written to each individual drive on the production box??
> One other point to note is that the test box seems to have a better
> raid controller. the production one has a hp smart array 5300 where the
> test box has a 6400 controller.
> But obviously it isn't making that much difference

I think you've now answered your original question. If both are identical
systems, except the production one has an 8 disk raid 5 and the test one a 4
disk raid 5, then each disk write on the production system will be
distributed across 8 disks whereas on the test on it will be distributed
across 4 disks. The significant bit performance wise is that on your 8 disk
production system each disk only needs half as much data written to it as on
your 4 disk test box, and thus it should take about half the time - which is
what you are experiencing.

Personally I'm normally a bit sceptical about one controller being better
than another - if one has more ram on it then I can understand - but if you
are inserting a lot of records then once the cache becomes full then it
doesn't matter how large the cache is because you are still governed by how
fast the controller can stream the data to the disks. Also, if the
controllers are configured not to cache writes then it doesn't matter how
much ram they might have on them.

I know this isn't part of your question, but I assume that on both systems
when doing your data insert that the systems became disk bound - i.e.
disk/raid lights came on, stayed on and cpu usage fell away. In scenarios
like this it is the speed of your disks/raid which become the critical
factor. You could double the speed of your cpu and it wouldn't make any
difference.

Brian.

www.cryer.co.uk/brian|||Thanks for the info Brian, think I'll put it down to this anyway.

Brian Cryer wrote:
> "wriggs" <ian.w@.btinternet.com> wrote in message
> news:1116938928.959078.246100@.z14g2000cwz.googlegr oups.com...
> > hmmm.. well i checked with the win2000 guys who originally setup the
> > box and it seems both boxes are setup with raid 5, its just that the
> > production box has got 8 drives in and the test box has 4 and obviously
> > half the space, so could this be the problem as less data is being
> > written to each individual drive on the production box??
> > One other point to note is that the test box seems to have a better
> > raid controller. the production one has a hp smart array 5300 where the
> > test box has a 6400 controller.
> > But obviously it isn't making that much difference
> I think you've now answered your original question. If both are identical
> systems, except the production one has an 8 disk raid 5 and the test one a 4
> disk raid 5, then each disk write on the production system will be
> distributed across 8 disks whereas on the test on it will be distributed
> across 4 disks. The significant bit performance wise is that on your 8 disk
> production system each disk only needs half as much data written to it as on
> your 4 disk test box, and thus it should take about half the time - which is
> what you are experiencing.
> Personally I'm normally a bit sceptical about one controller being better
> than another - if one has more ram on it then I can understand - but if you
> are inserting a lot of records then once the cache becomes full then it
> doesn't matter how large the cache is because you are still governed by how
> fast the controller can stream the data to the disks. Also, if the
> controllers are configured not to cache writes then it doesn't matter how
> much ram they might have on them.
> I know this isn't part of your question, but I assume that on both systems
> when doing your data insert that the systems became disk bound - i.e.
> disk/raid lights came on, stayed on and cpu usage fell away. In scenarios
> like this it is the speed of your disks/raid which become the critical
> factor. You could double the speed of your cpu and it wouldn't make any
> difference.
> Brian.
> www.cryer.co.uk/brian