July 30, 2012 at 4:06 am
I am getting the above error when running the below code.
This has started happening on this bit of sql after a backup and restore
anyone any ideas?
SET IDENTITY_INSERT Seminars ON
INSERT INTO xTable(xID, yID, zID, Title, Hours, MaxPeople, StatusID, IH, PFID)
VALUES
(-1, -1, 71, 'one', 0, 0, 1, 0, 0),
(-2, -2, 71, 'two', 0, 0, 1, 0, 0),
(- 3, - 3, 71, 'three', 0, 0, 1, 0, 0),
(- 4, - 4, 71, 'four', 0, 0, 1, 0, 0),
(- 5, - 5, 71, 'five', 0, 0, 1, 0, 0),
(- 6, - 6, 71, 'six', 0, 0, 1, 0, 0),
(- 7, - 7, 71, 'seven', 0, 0, 1, 0, 0),
(- 8, - 8, 71, 'eight', 0, 0, 1, 0, 0),
(- 9, - 9, 71, 'nine', 0, 0, 1, 0, 0),
(- 10, - 10, 71, 'ten', 0, 0, 1, 0, 0)
SET IDENTITY_INSERT Seminars OFF
thanks in advance
July 30, 2012 at 4:18 am
There's no subquery there. Have you got any triggers on the table?
The normal cause of this error is a subquery returning > 1 value, which is of course invalid as the return value is going into 1 row.
July 30, 2012 at 4:19 am
ahhhhhhhhhhh
someone has been medalling
thanks for the reply
July 30, 2012 at 8:08 am
erics44 (7/30/2012)
ahhhhhhhhhhhsomeone has been medalling
thanks for the reply
If it was a trigger then you seriously need to fix that trigger to handle multiple row statements. Don't just work around the issue by running a whole bunch of single row inserts.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2012 at 8:51 am
nope trigger fixed
July 30, 2012 at 8:53 am
erics44 (7/30/2012)
nope trigger fixed
Sweet!! 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2012 at 9:47 am
erics44 (7/30/2012)
nope trigger fixed
And trigger developer educated?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2012 at 9:54 am
GilaMonster (7/30/2012)
erics44 (7/30/2012)
nope trigger fixedAnd trigger developer educated?
ha, i wish
i wouldnt know where to start to pick appart the existing code in the sql databases
the most annoying thing is the amount of union queries there are, splitting tables and putting them back together and people are continuing to develop like that
July 31, 2012 at 6:02 am
erics44 (7/30/2012)
GilaMonster (7/30/2012)
erics44 (7/30/2012)
nope trigger fixedAnd trigger developer educated?
ha, i wish
i wouldnt know where to start to pick appart the existing code in the sql databases
the most annoying thing is the amount of union queries there are, splitting tables and putting them back together and people are continuing to develop like that
Please take my business card!! I would LOVE to work in your environment for a week or two! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 31, 2012 at 6:28 am
the guy who wrote it is really protective of his code too
if i change anything he has a paddy
i have found myself approaching things in a rubbish way just to stay common with the existing methods
i wrote a couple of table functions and he had a paddy because they weren't stored proceedure (full of unions :-))
if anyone has any ideas how to deal with someone like that then please let me know
July 31, 2012 at 7:19 am
erics44 (7/31/2012)
if anyone has any ideas how to deal with someone like that then please let me know
That somewhat depends on how that person is viewed in the department/company. If they all think this person is awesome there is little you can do.
That type of person is really hard to educate because they have done the same crappy code over and over and it always performs the same (horrible). The problem is they don't understand enough to know that the performance is horrible and their fragile ego get bruised when somebody shows them another approach.
If they are in good with the company about all you can do is dust off the resume and make sure you leave some comments on your way out. If they are not in great standing then you can shed some light on the lack of expertise. That can make the separation a little easier. The one thing I would say is to watch your back. That type of person tends to throw people under the bus that they consider a threat, and usually anybody who says they can improve existing processes is considered that type of threat.
What an awful spot you are in. Hope something good changes in the near future for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2012 at 7:33 am
Sean Lange (7/31/2012)
erics44 (7/31/2012)
if anyone has any ideas how to deal with someone like that then please let me knowThat somewhat depends on how that person is viewed in the department/company. If they all think this person is awesome there is little you can do.
That type of person is really hard to educate because they have done the same crappy code over and over and it always performs the same (horrible). The problem is they don't understand enough to know that the performance is horrible and their fragile ego get bruised when somebody shows them another approach.
If they are in good with the company about all you can do is dust off the resume and make sure you leave some comments on your way out. If they are not in great standing then you can shed some light on the lack of expertise. That can make the separation a little easier. The one thing I would say is to watch your back. That type of person tends to throw people under the bus that they consider a threat, and usually anybody who says they can improve existing processes is considered that type of threat.
What an awful spot you are in. Hope something good changes in the near future for you.
i think you have summed it up really well
i dont think he kind of accepts his position in the coding world (as in the level he is at) and as a result has no ambition to improve, or he is scared to look daft maybe if someone suggests a way he isnt familiar with
a recent example is I created a query based on a recurrsive CTE, now this is something i have got to grips with recently because i was looking at the result set i needed and knew there must be a better way than the over the top query i was writing with a number of nested queries
I went on holiday last week and whilst i was away it was noticed that there was a slight issue with the data my query was bringing back and it was slight (and a slight change in the where clause fixed it)
he obviously didnt like my query and rewrote it with 4 union queries on the same table all nested, his worked and mine didnt so what can i say without looking bitter or defensive and i know how he reacts so ive said nothing
and yes there are 3 developers in the team now and for the previous 2 years he has had full control working with the IT junior, he has kept an old flagging system going so is well respected in the company for that and he continues to be the messenger from the IT team to "management"
July 31, 2012 at 8:11 am
erics44 (7/31/2012)
Sean Lange (7/31/2012)
erics44 (7/31/2012)
if anyone has any ideas how to deal with someone like that then please let me knowThat somewhat depends on how that person is viewed in the department/company. If they all think this person is awesome there is little you can do.
That type of person is really hard to educate because they have done the same crappy code over and over and it always performs the same (horrible). The problem is they don't understand enough to know that the performance is horrible and their fragile ego get bruised when somebody shows them another approach.
If they are in good with the company about all you can do is dust off the resume and make sure you leave some comments on your way out. If they are not in great standing then you can shed some light on the lack of expertise. That can make the separation a little easier. The one thing I would say is to watch your back. That type of person tends to throw people under the bus that they consider a threat, and usually anybody who says they can improve existing processes is considered that type of threat.
What an awful spot you are in. Hope something good changes in the near future for you.
i think you have summed it up really well
i dont think he kind of accepts his position in the coding world (as in the level he is at) and as a result has no ambition to improve, or he is scared to look daft maybe if someone suggests a way he isnt familiar with
a recent example is I created a query based on a recurrsive CTE, now this is something i have got to grips with recently because i was looking at the result set i needed and knew there must be a better way than the over the top query i was writing with a number of nested queries
I went on holiday last week and whilst i was away it was noticed that there was a slight issue with the data my query was bringing back and it was slight (and a slight change in the where clause fixed it)
he obviously didnt like my query and rewrote it with 4 union queries on the same table all nested, his worked and mine didnt so what can i say without looking bitter or defensive and i know how he reacts so ive said nothing
and yes there are 3 developers in the team now and for the previous 2 years he has had full control working with the IT junior, he has kept an old flagging system going so is well respected in the company for that and he continues to be the messenger from the IT team to "management"
Well since it is apparent that no amount of discussion is going to help you can use the tried and true method...proof. Take his abomination with dozens of unions and such and compare it to yours. First prove that they both return the same accurate results. This is useful to of course first demonstrate that what you are looking for is performance. It also lets you coddle the person a little bit by saying something like "using your query as the basis for correct results, I wanted to see if this other approach could produce the same results but faster". Some soft of semi-accurate BS to sooth the potentially bruised ego. 😉
Then create a testbed of sample data for both queries. Figure out which one is truly faster. Be prepared of course that yours might actually be slower, this does happen sometimes. This is why you build this up on your own first so you can find a different example down the road. The real challenge is to gain respect from this person. This can be difficult when they have been in the same position writing the same crap for a long time. The "old dog, new trick" issue.
It is a battle and not usually an easy one. If the company is a good fit for you and you think this battle is worth it, then go for it. Just keep in mind you have tread lightly and press softly. This is a major paradigm shift and often takes a lot of time. If on the other hand, the company is not the place you see yourself in 5-10 years, maybe it is time to seek a better environment sooner rather than later.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2012 at 3:12 pm
i have a battle plan
i have editted 4 reports over the last couple of days that he had written queries for, replacing the queries with those for a newer schema
not one i have looked at is fully accurate so im gonna start pointing out his mistakes, play dumb as if im not sure what his query is doing, then sending him my query to ask him if he thinks it is doing what it is suppose to be
🙂
July 31, 2012 at 3:21 pm
Another thing to "ease" into discussions is something like this.
"I just read about some really cool thing that people are using to increase their performance. I thought I would try it here to see if it helps us at all, and it does! We should really start using this to make our department look better."
That makes them feel like it is not really your idea and comes across less threatening.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply