September 13, 2011 at 4:17 pm
We have a vendor application that has worked in development.
The same upgrade in production.
The USR1.myview gets created in development, but not in production host/server/database. We see the USR1.myview get created without error. Refresh the SSMS Views and two seconds later the view is gone.
We can recreate it, and select * from it (returning 113,208 rows in 19 seconds), refresh SSMS Views, and the view has disappeared and we now get an error when we try again (Msg 208, Level 16 ... Invalid object name...).
Only this view name disappears. We can create other views as dbo or USR1, and they stay resident.
I've looked inside system views:
select * from INFORMATION_SCHEMA.VIEWS where TABLE_NAME like 'myvie%'
select * from sys.views where name like 'myvie%'
select * from sys.all_views where name like 'myvie%'
select * from INFORMATION_SCHEMA.TABLES where table_name like 'myvie%'
and I see our myview2, dbo.myview3, USR1.myview4, but not the USR1.myview that the vendor says must exist.
We cannot seem to create it and have it stay created... sigh....
We're up to date on SP/hotfix for SQL Server 2008. We've run profile while creating the view and quickly selecting it.. and nothing stands out.
Any place in any system catalog or view where there might be something that is invalidating or preventing the save of this view permanently?
Suggestions welcomed! Thanks!
September 13, 2011 at 4:29 pm
Haven't heard of such.
What is the particular view name?
Is create view running inside a transaction? (perhaps rolled back by end of connection).
If you create a dummy view with the same name, what happens?
September 13, 2011 at 4:41 pm
The view that fails is named usr1.AIM_WO_COST. No key words or invalid syntax.
It does not matter what schema owns it. We can create dbo.AIM_WO_COST and it will disappear.
If we append '2' or '3' we can create those under usr1 or dbo schema, and they remain resident, and useable.
We run the script in SSMS 2008 Query window with auto commit. There is no 'if exists...drop' or any of that type of statement in the window. The usr1.AIM_WO_COST can be created in development sql server an another host. It just fails on the production host/server. We can create other objects in SSMS query windows with our same techniques of running queries there, and they remain.
That's why I'm suspicious of this schema update script colliding with something hidden in a system catalog that might have been removed only partially, and thus the system is performing cleanup for us? Possible?
CREATE VIEW [usr1].[AIM_WO_COST2]
AS
SELECT
A.PROPOSAL,
ROUND(ISNULL(A.EST_LABOR,0.00),2) AS EST_LABOR,
ROUND(ISNULL(A.EST_MATERIAL,0.00),2) AS EST_MATERIAL,
... lines deleted here
ISNULL(D.ACTUAL_HOURS,0.00) AS ACTUAL_HOURS
FROM usr1.AIM_WO_COST_EST A
LEFT OUTER JOIN usr1.AIM_WO_COST_ENC_ACT B
ON A.PROPOSAL = B.PROPOSAL
LEFT OUTER JOIN usr1.AIM_WO_ACT_HRS D
ON A.PROPOSAL = D.PROPOSAL
LEFT OUTER JOIN usr1.AIM_WO_COST_UNALLOC_EXP E
ON A.PROPOSAL = E.PROPOSAL
LEFT OUTER JOIN usr1.AIM_WO_BILLED_FND_API F
ON A.PROPOSAL = F.PROPOSAL
go
Thanks
September 13, 2011 at 4:44 pm
cut an paste... the problem is with CREATE VIEW [usr1].[AIM_WO_COST] ... fails
not this version [usr1].[AIM_WO_COST2] = same content but resident, remains present and useable
September 13, 2011 at 5:16 pm
Someone or something is dropping it. SQL is not going to randomly drop objects for no reason.
If it failed to create for any reason you would get an error and the attempt to select from it immediately after would fail.
Check the default trace or put a DDL trigger in place to see what's happening
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
September 13, 2011 at 5:19 pm
The server from which it is disappearing, is it involved in replication?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 14, 2011 at 8:43 am
No replication in this server. No mirroring. Nothing unusual. It is a named instance on this standalone host (no clustering).
Everything works, except saving (permanently) this view yesterday.
Mystery! However..... the view is sitting there this morning. Named USR1.AIM_WO_COST. Perfect. Why?
We're tracking down client & vendor and another DBA who should arrive at work in a hour.
Sequence:
7/13 (yesterday) 7:15pm routine DBA job - checkdb & database backup
7/13 7:53pm create timestamp of view USR1.AIM_WO_COST
This morning, there it is.
We'll see if some person or timed-vendor script ran the create-view script or some process to rebuild application last night. We'll start digging through logs and people recollections now.
Just so puzzled.
SQL Server was not stopped and restarted.
Yesterday, a SQL Compare of DDL was performed between the development database and this production database, and the only differences were that the view existed in development, and our view2 view3 existed on the production side.
The disappearing view was removed so fast from the list of SSMS database views that it was not a human action. It was as fast as creating the view, refreshing the view list, noting it's presence and then refreshing the view list a second time, and it was gone. sigh.
Thanks for you thoughts, and if anything else occurs to anyone we'd love to hear about what else we could have checked or performed.
Next time? - checkdb? followed by scheduled (production) SQL Server restart?
Timing = memory refresh after 4 hours and something can be "cleared" so that the view can be saved?
We do need to track down who ran what at 7:53 PM last night to allow the script to be created successfully.
September 14, 2011 at 8:48 am
fiddi (9/14/2011)
Next time? - checkdb? followed by scheduled (production) SQL Server restart?Timing = memory refresh after 4 hours and something can be "cleared" so that the view can be saved?
No and no. If there was an error the view wouldn't create. Maybe a job (doesn't have to be human action), but something was removing the view. SQL doesn't just drop objects
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
September 14, 2011 at 9:32 am
Solved!
We found that some client-vendor scripts were concatenated by clients. There was a missing 'go' at the end of one of them, and the next script dropped this view and created it (now one large stored proc instead of two.) The database compare we performed against their development and their production databases showed same error introduced, but one of their developers ran the vendor script separately, and cleanly created separate scripts. But still had that one proc with both activities in the same script. There was no development fully-clean (the way the vendor intended the procs to appear).
Now, in production, sure enough, that function #1 was rarely run, but just enough that when it went out to do one particular function it dropped our USR1.myview.
Only someone knowing the functionality of their application in depth could identify what was happening whenever they ran a particular set of code.
SQL Server is stable. We thought that, but we wondered if we had corrupted something in system catalogs.
Recognizing, now, that it was a procedural difference between the way the 35 vendor scripts were run (one at a time by developers) and the script they provided to the DBAs to execute (one concatenated, long script). Yet, after they created the individual runs, they then tested their concatenation script, introducing the error of the view #1 with the drop in it. Thus, their dev script contained an old original USR1.myview and the bad USR1.myview#1 with drop. Thus, the SQL Compare was equivalent except the USR1.myview existed in development with 'older timestamp' from first run of vendor scripts (left over). In their testing in development the two developers just never executed the bit of functionality that would have dropped their USR1.myview.
We thought there could be a drop, but it was not evident in our retry method. We didn't take into account that this was an active production database, and there were lots of users hitting lots of code, and this procedure was likely to get dropped via that section of code.
Nor did we know that the method used to create these objects differed substantially between the way the developers executed the code, and the script provided to the DBA team.
Thanks everyone for suggestions. Very frustrating for our team and the clients.
It was the developer who recognized the code behavior last night. He temporarily has db_owner on their production database (yeah, we know) but it was just to assist with this migration upgrade effort.
September 14, 2011 at 9:39 am
Thanks for your comments, Gail. You nailed it.
We just didn't recognize why our SQL Compare appeared the same, until we could talk to clients and find out how these objects were created in dev versus prod. And, how their dev application testing did not hit the code, but the production executed those procedures frequently.
We danced around a lot in the area we had more control over - sql server behavior - which was behaving perfectly!
September 14, 2011 at 10:05 am
It also occurred to me (now) that I could have scripted out all of the stored procedures into one file (or individual files) and searched for 'if exists' or 'drop ' and seen if (where!) this drop was happening. That way, we would not have had to rely on the knowledge of the application usage, but been able to ask the developer & vendor if the following x, y, and z stored procedures were supposed to be dropping this view? lesson learned.
September 14, 2011 at 1:03 pm
Good to see that the culprit was found.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2011 at 1:46 pm
fiddi,
I had something similar to this - missing GO statements causing a drop with no create. I finally created a little app in VB to concatenate the source files and always stick a GO in between each file. A few extra GO statements don't hurt a thing.
Todd Fifield
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply