SQL Server view reverts
I have a SQL Server database which manages our orders and production scheduling, including integration with our Fedex and UPS meters. The database (not created by me) has a view which is a source table for our UPS & Fedex computers to pull shipment data from.
I have altered the view to include a few additional columns from one of its source tables (in fact I have added a new table to the view definition), and altered the Fedex & UPS integration profiles also. All works perfectly, until the server running SQLserver reboots. When that happens, the view reverts to the original view definition. I have searched online for a solution to this but have found nothing. it's quite a problem because the import/export profiles don't work if the view has reverted and i have to run a script to drop/recreate the view every time the server is rebooted.
As well as this problem, if I open a View in Design mode and alter it, I can't do a 'Save as' to overwrite the existing view definition. I've only been able drop and recreate the view to alter it. Perhaps the two things are related?
I'd appreciate any insight into either of these issues -
I have altered the view to include a few additional columns from one of its source tables (in fact I have added a new table to the view definition), and altered the Fedex & UPS integration profiles also. All works perfectly, until the server running SQLserver reboots. When that happens, the view reverts to the original view definition. I have searched online for a solution to this but have found nothing. it's quite a problem because the import/export profiles don't work if the view has reverted and i have to run a script to drop/recreate the view every time the server is rebooted.
As well as this problem, if I open a View in Design mode and alter it, I can't do a 'Save as' to overwrite the existing view definition. I've only been able drop and recreate the view to alter it. Perhaps the two things are related?
I'd appreciate any insight into either of these issues -
ASKER
I will try that; they are not great about responding in a timely manner so it may be a while before I can report back. I will also try looking for such a script on my own.
Thanks for the input!
Thanks for the input!
It's also possible the view you created is in a different schema than the "standard" view, typically 'dbo' but could be something else in your specific shop.
When *you* use the view, you see the new view definition because that's your default schema.
That could also explain why you can't overwrite the original view -- you don't have ALTER authority on that schema, just your own.
Such schema issues often happen; however, that doesn't explain the reboot part of it.
Perhaps, as jimhorn implied, the db is being restored from a db that does not have/"see" your changes. Some places restore prod over Dev and/or QA automatically periodically, in some cases every night, depending on the local need.
When *you* use the view, you see the new view definition because that's your default schema.
That could also explain why you can't overwrite the original view -- you don't have ALTER authority on that schema, just your own.
Such schema issues often happen; however, that doesn't explain the reboot part of it.
Perhaps, as jimhorn implied, the db is being restored from a db that does not have/"see" your changes. Some places restore prod over Dev and/or QA automatically periodically, in some cases every night, depending on the local need.
ASKER
ScottPletcher & JimHorn -
I am not well versed in SQlserver schema, however, when I log in to MSSMS, I see the tables & views all named as dbo.xxx - How do I identify if that schema is where the view orginated? (if that is the correct way to phrase the question)? Is there a way to tell if other schema are present?
I don't believe there is any hidden copying of a prod or dev version going on - there is only this one db on this sqlserver and no connection to any remote db. Is there a different way it could be doing this that would be completely invisible to me? We are a smallish manufacturer in a specialized industry and the software is written for typical needs in our industry. I don't think the software/db supplier is (or needs to be) as sophisticated as this scenario might imply.
As a workaround, I've created a Sql server agent job which drops and recreates the view on sql server startup. It seems to be working but I still wonder about why the problem in the first place.
Thanks for your ideas - I appreciate the expert help very much.
I am not well versed in SQlserver schema, however, when I log in to MSSMS, I see the tables & views all named as dbo.xxx - How do I identify if that schema is where the view orginated? (if that is the correct way to phrase the question)? Is there a way to tell if other schema are present?
I don't believe there is any hidden copying of a prod or dev version going on - there is only this one db on this sqlserver and no connection to any remote db. Is there a different way it could be doing this that would be completely invisible to me? We are a smallish manufacturer in a specialized industry and the software is written for typical needs in our industry. I don't think the software/db supplier is (or needs to be) as sophisticated as this scenario might imply.
As a workaround, I've created a Sql server agent job which drops and recreates the view on sql server startup. It seems to be working but I still wonder about why the problem in the first place.
Thanks for your ideas - I appreciate the expert help very much.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BINGO!
Running the code above returned no rows... so I took a look at the login information for the user and discovered that it has no default schema. I logged in as a different user with dbo as the default schema and it looks like the problem will be solved; I can now do a 'save as' from view design view - which makes me hopeful that the view definition will stick when the server reboots. I can't test that now so I will leave the question open until I can confirm things... should know by Friday at the latest.
Thanks much for your help in identifying this problem. I need to do some studying up on db schemas, I can see...
Running the code above returned no rows... so I took a look at the login information for the user and discovered that it has no default schema. I logged in as a different user with dbo as the default schema and it looks like the problem will be solved; I can now do a 'save as' from view design view - which makes me hopeful that the view definition will stick when the server reboots. I can't test that now so I will leave the question open until I can confirm things... should know by Friday at the latest.
Thanks much for your help in identifying this problem. I need to do some studying up on db schemas, I can see...
You're welcome!
As you can probably tell, I've seen such schema issues many times before :-), so you're not the only one suffering from this. As DBA, I often create a user in a db just to provide the proper default schema.
Btw, if possible, you should check for other objects with non-dbo owners, especially if they have the same name as an object with a dbo owner.
For example:
dbo.table1
ourdomain/someuser.table1
That is a dangerous situation, particularly in prod (of course) and esp. if people can move code to prod that doesn't specify the table schema, like:
SELECT col1, col2, col3
FROM table1
That code could accidentally read the non-dbo table, which could obviously be nasty in a prod environment.
And any user tables/procs accidentally in the master db (by default, master is the default db; if s/o creates an object w/o verifying which db they are in, and they have the authority, they can create objects in master by accident).
As you can probably tell, I've seen such schema issues many times before :-), so you're not the only one suffering from this. As DBA, I often create a user in a db just to provide the proper default schema.
Btw, if possible, you should check for other objects with non-dbo owners, especially if they have the same name as an object with a dbo owner.
For example:
dbo.table1
ourdomain/someuser.table1
That is a dangerous situation, particularly in prod (of course) and esp. if people can move code to prod that doesn't specify the table schema, like:
SELECT col1, col2, col3
FROM table1
That code could accidentally read the non-dbo table, which could obviously be nasty in a prod environment.
And any user tables/procs accidentally in the master db (by default, master is the default db; if s/o creates an object w/o verifying which db they are in, and they have the authority, they can create objects in master by accident).
ASKER
I was able to do a reboot on this server overnight and the view maintained the new definition.
Thanks again for the help!
Thanks again for the help!
I'd ask the DBA's if there are any scripts that execute on server startup, and if you can eyeball them to determine if they are executing the view in its original state. I've been in places where a developer was not allowed to add an object to a db, and the work-around was to run a script AFTER the db was copied over from it's prod environment.