July 17, 2013 at 5:21 am
After reading the "I hate software" posts just now, I have an urge to start a new topic...
Post the most twisted, warped, bizzarre, yet functional SQL code you can...
Whether it's something that uses multiple nested cursors and other freakish abominations of code to do something simple or a short little two-liner that does something incredibly complex, you'd post it...
And I'm sure some of us here (myself not included) could come up with some doozies...
😀
July 17, 2013 at 6:14 am
At risk of being lynched for bringing a code convo into The Thread...
Is it just me or does SQL 2008 not script out DROP CONSTRAINT correctly?
The script it gave me is looking at dbo.sysobjects for default constraints, and since the constraints aren't in dbo.sysobjects, it's not dropping them at all.
July 17, 2013 at 6:17 am
Brandie Tarvin (7/17/2013)
At risk of being lynched for bringing a code convo into The Thread...Is it just me or does SQL 2008 not script out DROP CONSTRAINT correctly?
The script it gave me is looking at dbo.sysobjects for default constraints, and since the constraints aren't in dbo.sysobjects, it's not dropping them at all.
Default constraints are in sysobjects, unless I'm going mad. xtype='D'?
July 17, 2013 at 6:27 am
HowardW (7/17/2013)
Brandie Tarvin (7/17/2013)
At risk of being lynched for bringing a code convo into The Thread...Is it just me or does SQL 2008 not script out DROP CONSTRAINT correctly?
The script it gave me is looking at dbo.sysobjects for default constraints, and since the constraints aren't in dbo.sysobjects, it's not dropping them at all.
Default constraints are in sysobjects, unless I'm going mad. xtype='D'?
I ran the drop code SQL Server gave me. The constraints did NOT drop but then did not show up in Sysobjects.
I don't know what's going on and this is not the first time SQL has done this to me. Anyone mind testing this to see if they get the same results?
July 17, 2013 at 6:30 am
Brandie Tarvin (7/17/2013)
My latest help desk ticket.I've been getting dropped out of SystemX regularly the past week and it just keeps getting worse and is happening on a constant basis. Pls fix.
Oh. Okay. Care to share any *specific* details with me so I can help resolve the problem? Or do I just get to guess with my Magic Fix-It darts?
<headdesk>
Get some glitter, put in an envelope.
Also insert instructions to sprinkle magic pixie dust on monitor next time it happens.
Include in note some questions you would like more details about it this does not fix the issue.
Send to requestor.
July 17, 2013 at 6:31 am
Brandie Tarvin (7/17/2013)
HowardW (7/17/2013)
Brandie Tarvin (7/17/2013)
At risk of being lynched for bringing a code convo into The Thread...Is it just me or does SQL 2008 not script out DROP CONSTRAINT correctly?
The script it gave me is looking at dbo.sysobjects for default constraints, and since the constraints aren't in dbo.sysobjects, it's not dropping them at all.
Default constraints are in sysobjects, unless I'm going mad. xtype='D'?
I ran the drop code SQL Server gave me. The constraints did NOT drop but then did not show up in Sysobjects.
I don't know what's going on and this is not the first time SQL has done this to me. Anyone mind testing this to see if they get the same results?
Just did, worked fine on my environment, scripting the drop from SSMS.
Do you have code that recreates the issue?
July 17, 2013 at 6:32 am
Greg Edwards-268690 (7/17/2013)
Brandie Tarvin (7/17/2013)
My latest help desk ticket.I've been getting dropped out of SystemX regularly the past week and it just keeps getting worse and is happening on a constant basis. Pls fix.
Oh. Okay. Care to share any *specific* details with me so I can help resolve the problem? Or do I just get to guess with my Magic Fix-It darts?
<headdesk>
Get some glitter, put in an envelope.
Also insert instructions to sprinkle magic pixie dust on monitor next time it happens.
Include in note some questions you would like more details about it this does not fix the issue.
Send to requestor.
LOVE IT!
July 17, 2013 at 6:42 am
HowardW (7/17/2013)
Brandie Tarvin (7/17/2013)
HowardW (7/17/2013)
Brandie Tarvin (7/17/2013)
At risk of being lynched for bringing a code convo into The Thread...Is it just me or does SQL 2008 not script out DROP CONSTRAINT correctly?
The script it gave me is looking at dbo.sysobjects for default constraints, and since the constraints aren't in dbo.sysobjects, it's not dropping them at all.
Default constraints are in sysobjects, unless I'm going mad. xtype='D'?
I ran the drop code SQL Server gave me. The constraints did NOT drop but then did not show up in Sysobjects.
I don't know what's going on and this is not the first time SQL has done this to me. Anyone mind testing this to see if they get the same results?
Just did, worked fine on my environment, scripting the drop from SSMS.
Do you have code that recreates the issue?
Yes. I'll put a post in the regular forum and link back in a minute. This is getting annoying.
EDIT: You know what I just remembered? It's a schema issue. I'm not using dbo for the table, so I need to do some funky addition to the DROP script to get it to look at the schema. DOH.
July 17, 2013 at 6:46 am
Brandie Tarvin (7/17/2013)
The script it gave me is looking at dbo.sysobjects for default constraints, and since the constraints aren't in dbo.sysobjects, it's not dropping them at all.
All constraints are in sysobjects (and sys.objects) as they are first class objects in the database.
TRWTF is Management studio still using sysobjects...
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 17, 2013 at 6:48 am
GilaMonster (7/17/2013)
Brandie Tarvin (7/17/2013)
The script it gave me is looking at dbo.sysobjects for default constraints, and since the constraints aren't in dbo.sysobjects, it's not dropping them at all.All constraints are in sysobjects (and sys.objects) as they are first class objects in the database.
They don't return if you use OBJECT_ID() and forget to stick the non-default schema in front of the name. Which is where I was getting confused.
I don't do this nearly often enough to remember that annoying little fact.
July 17, 2013 at 6:50 am
I see a SQL Spackle article in my future... Plus a blog post followup.
July 17, 2013 at 7:39 am
Brandie Tarvin (7/17/2013)
HowardW (7/17/2013)
Brandie Tarvin (7/17/2013)
At risk of being lynched for bringing a code convo into The Thread...Is it just me or does SQL 2008 not script out DROP CONSTRAINT correctly?
The script it gave me is looking at dbo.sysobjects for default constraints, and since the constraints aren't in dbo.sysobjects, it's not dropping them at all.
Default constraints are in sysobjects, unless I'm going mad. xtype='D'?
I ran the drop code SQL Server gave me. The constraints did NOT drop but then did not show up in Sysobjects.
I don't know what's going on and this is not the first time SQL has done this to me. Anyone mind testing this to see if they get the same results?
I have seen this in the past! I don't use SQL Server's auto-scripting to drop things often, but I do remember this happening once. When I looked in Sysobjects they weren't there. It was awhile ago though, so I don't have much more to offer.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 17, 2013 at 7:51 am
Stefan Krzywicki (7/17/2013)
Brandie Tarvin (7/17/2013)
HowardW (7/17/2013)
Brandie Tarvin (7/17/2013)
At risk of being lynched for bringing a code convo into The Thread...Is it just me or does SQL 2008 not script out DROP CONSTRAINT correctly?
The script it gave me is looking at dbo.sysobjects for default constraints, and since the constraints aren't in dbo.sysobjects, it's not dropping them at all.
Default constraints are in sysobjects, unless I'm going mad. xtype='D'?
I ran the drop code SQL Server gave me. The constraints did NOT drop but then did not show up in Sysobjects.
I don't know what's going on and this is not the first time SQL has done this to me. Anyone mind testing this to see if they get the same results?
I have seen this in the past! I don't use SQL Server's auto-scripting to drop things often, but I do remember this happening once. When I looked in Sysobjects they weren't there. It was awhile ago though, so I don't have much more to offer.
VINDICATION!!!! WHOO HOO!
July 17, 2013 at 7:52 am
Koen Verbeeck (7/17/2013)
Jeff Moden (7/16/2013)
Steve Jones - SSC Editor (7/16/2013)
Arrgggg, some days I hate software.BWAAA-HAAA!!!! You mean like the stuff that generates the SSC News Letter? :sick::-D:-P
Touché 😀
Yeah, that's been annoying lately. An argument is underway
July 17, 2013 at 8:36 am
I've been having my own little annoyance with something SQL Server related lately. If you use SSIS to create text files and save it from one server to another, into SQL Server, the text qualifier changes from <none> to _x003C_none_x003E_ in every connection manager that affects a text file. To fix it I have to open the SSIS package on the local machine, delete the text qualifier and save it again. It isn't a huge deal, but it is annoying and I have to remember every time I deploy a new SSIS package from Dev to Prod that creates a text file and I don't do that very often.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 40,576 through 40,590 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply