|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
UPDATE in a SQL script file is not working.I've got an upgrade.bat file that calls osql to run an upgrade.sql file. In
this file, I create a couple of stored procedures, alter a couple stored procedures, add permissions to those procedures, and also include an UPDATE statement to change one row in one table. All of the stored procedures get created and altered correctly. Also, all of the permissions get added correctly. However, the fields never get their value changed from the UPDATE statement. I get no error message or any other text, so I can't tell if anything is failing. My update statement is: UPDATE Models SET Discount = 0.15, Prep = 0 WHERE Code = '316 07' Is there anything peculiar about that line that wouldn't work? Or am I just not understanding the functionality or limitation of SQL scripts? Please help me out. Thanks! Adam,
Add one more line right before the update SELECT * INTO MyInvestigation FROM Models WHERE Code = '316 07' to see if any rows qualify at the time it is running and if the code is executed at all. Apparently, the code is NOT being executed. There's no "MyInvestigation"
table after it's run. However it DOES create MyInvestigation and puts the correct row in it if I run that line in Query Analyzer. ....and for the others asking, YES, there is supposed to be a space in there. Ideas? Thanks! Show quote "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1157125197.631320.152110@m79g2000cwm.googlegroups.com... > Adam, > > Add one more line right before the update > SELECT * INTO MyInvestigation FROM Models WHERE > Code = '316 07' > > to see if any rows qualify at the time it is running and if the code is > executed at all. > Adam Porter wrote:
> Apparently, the code is NOT being executed. There's no "MyInvestigation" What happens immediately before this step in your script? Is the update > table after it's run. However it DOES create MyInvestigation and puts the > correct row in it if I run that line in Query Analyzer. > > ...and for the others asking, YES, there is supposed to be a space in there. > > Ideas? Thanks! > command possibly being included in one of your sproc definitions? This is my sql script, with the "filler" removed :
---------- USE Showroom GO CREATE PROCEDURE dbo...... GO CREATE PROCEDURE dbo..... GO CREATE PROCEDURE dbo..... GO CREATE PROCEDURE dbo..... GO ALTER PROCEDURE dbo..... GO ALTER PROCEDURE dbo..... GO UPDATE Models SET Discount = 0.15, Prep = 0 WHERE Model = '316 07' GO GRANT EXECUTE ON .... TO [User] GO GRANT EXECUTE ON .... TO [User] GO GRANT EXECUTE ON .... TO [User] GO GRANT EXECUTE ON .... TO [User] GO GRANT EXECUTE ON .... TO [User] GO GRANT EXECUTE ON .... TO [User] GO GRANT EXECUTE ON .... TO [User] GO ------- I've checked the stored procedures that I'm altering right before the UPDATE line. No part of that stored procedure shows anything related to the UPDATE line. Does that help at all? Thanks. Do you have an output file for the OSQL operation? Is there any messages,
error or otherwise, in the output file? -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Adam Porter" <ad***@REMOVE.formulaboats.com> wrote in message news:%23Cu862fzGHA.3752@TK2MSFTNGP02.phx.gbl... > This is my sql script, with the "filler" removed : > > ---------- > USE Showroom > GO > > CREATE PROCEDURE dbo...... > GO > > CREATE PROCEDURE dbo..... > GO > > CREATE PROCEDURE dbo..... > GO > > CREATE PROCEDURE dbo..... > GO > > ALTER PROCEDURE dbo..... > GO > > ALTER PROCEDURE dbo..... > GO > > UPDATE Models SET Discount = 0.15, Prep = 0 WHERE Model = '316 07' > GO > > GRANT EXECUTE ON .... > TO [User] > GO > > GRANT EXECUTE ON .... > TO [User] > GO > > GRANT EXECUTE ON .... > TO [User] > GO > > GRANT EXECUTE ON .... > TO [User] > GO > > GRANT EXECUTE ON .... > TO [User] > GO > > GRANT EXECUTE ON .... > TO [User] > GO > > GRANT EXECUTE ON .... > TO [User] > GO > ------- > > I've checked the stored procedures that I'm altering right before the > UPDATE line. No part of that stored procedure shows anything related to > the UPDATE line. Does that help at all? > > Thanks. > The only thing appearing is this ("filler" removed):
Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 There is already an object named '.....' in the database. Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 There is already an object named '.....' in the database. Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 There is already an object named '.....' in the database. Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 There is already an object named '.....' in the database. Although, that's only because I've ran the script before, so the procedures already exist. If I remove the stored procedures and run it again (which I JUST did), then my output file is empty. ...and of course my procedures and permissions are created and set properly. It's just the UPDATE that doesn't run. And the UPDATE statement works properly when executed from QA?
-- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Adam Porter" <ad***@REMOVE.formulaboats.com> wrote in message news:ezF5FDgzGHA.3304@TK2MSFTNGP05.phx.gbl... > The only thing appearing is this ("filler" removed): > > Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 > There is already an object named '.....' in the > database. > Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 > There is already an object named '.....' in > the database. > Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 > There is already an object named '.....' in the > database. > Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 > There is already an object named '.....' in the > database. > > Although, that's only because I've ran the script before, so the > procedures already exist. If I remove the stored procedures and run it > again (which I JUST did), then my output file is empty. ...and of course > my procedures and permissions are created and set properly. It's just the > UPDATE that doesn't run. > (1 row(s) affected)
Yes, it works when executed from QA. Show quote "Arnie Rowland" <ar***@1568.com> wrote in message news:Ov$akGgzGHA.3280@TK2MSFTNGP02.phx.gbl... > And the UPDATE statement works properly when executed from QA? > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Adam Porter" <ad***@REMOVE.formulaboats.com> wrote in message > news:ezF5FDgzGHA.3304@TK2MSFTNGP05.phx.gbl... >> The only thing appearing is this ("filler" removed): >> >> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 >> There is already an object named '.....' in the >> database. >> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 >> There is already an object named '.....' in >> the database. >> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 >> There is already an object named '.....' in the >> database. >> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 >> There is already an object named '.....' in the >> database. >> >> Although, that's only because I've ran the script before, so the >> procedures already exist. If I remove the stored procedures and run it >> again (which I JUST did), then my output file is empty. ...and of course >> my procedures and permissions are created and set properly. It's just the >> UPDATE that doesn't run. >> > > Not having [0 Row(s) affected] in the output file causes me to believe that
the UPDATE command line is being somehow skipped over or not properly reported. Add [ -e -r1 -m-1 ] to the OSQL command line so that the output file will echo the actual command being executed and pick up informational messages. (Square brackets for display purposes only. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Adam Porter" <ad***@REMOVE.formulaboats.com> wrote in message news:eeEM0KgzGHA.4968@TK2MSFTNGP05.phx.gbl... > (1 row(s) affected) > > Yes, it works when executed from QA. > > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:Ov$akGgzGHA.3280@TK2MSFTNGP02.phx.gbl... >> And the UPDATE statement works properly when executed from QA? >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "Adam Porter" <ad***@REMOVE.formulaboats.com> wrote in message >> news:ezF5FDgzGHA.3304@TK2MSFTNGP05.phx.gbl... >>> The only thing appearing is this ("filler" removed): >>> >>> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 >>> There is already an object named '.....' in the >>> database. >>> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 >>> There is already an object named '.....' in >>> the database. >>> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 >>> There is already an object named '.....' in the >>> database. >>> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 >>> There is already an object named '.....' in the >>> database. >>> >>> Although, that's only because I've ran the script before, so the >>> procedures already exist. If I remove the stored procedures and run it >>> again (which I JUST did), then my output file is empty. ...and of course >>> my procedures and permissions are created and set properly. It's just >>> the UPDATE that doesn't run. >>> >> >> > > In EM, check the last procedure just prior to the update statement. See if
the update statement is at the end of the procedure, instead of being outside it. Show quote "Adam Porter" <ad***@REMOVE.formulaboats.com> wrote in message news:eeEM0KgzGHA.4968@TK2MSFTNGP05.phx.gbl... > (1 row(s) affected) > > Yes, it works when executed from QA. > > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:Ov$akGgzGHA.3280@TK2MSFTNGP02.phx.gbl... > > And the UPDATE statement works properly when executed from QA? > > > > -- > > Arnie Rowland, Ph.D. > > Westwood Consulting, Inc > > > > Most good judgment comes from experience. > > Most experience comes from bad judgment. > > - Anonymous > > > > > > "Adam Porter" <ad***@REMOVE.formulaboats.com> wrote in message > > news:ezF5FDgzGHA.3304@TK2MSFTNGP05.phx.gbl... > >> The only thing appearing is this ("filler" removed): > >> > >> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 > >> There is already an object named '.....' in the > >> database. > >> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 > >> There is already an object named '.....' in > >> the database. > >> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 > >> There is already an object named '.....' in the > >> database. > >> Msg 2714, Level 16, State 5, Server ADAMP\ASDF, Procedure ....., Line 2 > >> There is already an object named '.....' in the > >> database. > >> > >> Although, that's only because I've ran the script before, so the > >> procedures already exist. If I remove the stored procedures and run it > >> again (which I JUST did), then my output file is empty. ...and of course > >> my procedures and permissions are created and set properly. It's just the > >> UPDATE that doesn't run. > >> > > > > > > I would try to qualify Models as much as possible:
USE YourDBName go UPDATE YourDBName.YourSchema.Models ... also I would
PRINT @@ROWCOUNT PRINT DB_NAME() immediately after the update Code = '316 07' looks suspicious, are you sure that space is supposed
to be there? Denis the SQL Menace http://sqlservercode.blogspot.com/ Adam Porter wrote: Show quote > I've got an upgrade.bat file that calls osql to run an upgrade.sql file. In > this file, I create a couple of stored procedures, alter a couple stored > procedures, add permissions to those procedures, and also include an UPDATE > statement to change one row in one table. > > All of the stored procedures get created and altered correctly. Also, all of > the permissions get added correctly. However, the fields never get their > value changed from the UPDATE statement. I get no error message or any other > text, so I can't tell if anything is failing. > > My update statement is: UPDATE Models SET Discount = 0.15, Prep = 0 WHERE > Code = '316 07' > > Is there anything peculiar about that line that wouldn't work? Or am I just > not understanding the functionality or limitation of SQL scripts? Please > help me out. > > Thanks! Is code supposed to contain a space between the 316 and the 07?
Syntactically, the code looks fine. Show quote "Adam Porter" <ad***@REMOVE.formulaboats.com> wrote in message news:OJgSxudzGHA.480@TK2MSFTNGP06.phx.gbl... > I've got an upgrade.bat file that calls osql to run an upgrade.sql file. In > this file, I create a couple of stored procedures, alter a couple stored > procedures, add permissions to those procedures, and also include an UPDATE > statement to change one row in one table. > > All of the stored procedures get created and altered correctly. Also, all of > the permissions get added correctly. However, the fields never get their > value changed from the UPDATE statement. I get no error message or any other > text, so I can't tell if anything is failing. > > My update statement is: UPDATE Models SET Discount = 0.15, Prep = 0 WHERE > Code = '316 07' > > Is there anything peculiar about that line that wouldn't work? Or am I just > not understanding the functionality or limitation of SQL scripts? Please > help me out. > > Thanks! > >  |
|||||||||||||||||||||||
Other interesting topics