Create objects Failure.
RichardB
Posts: 35
Hi.
Packaged up a read only database, all objects (except users and roles), and selected data tables, into an exe.
1 - Create database worked fine, then the package failed - database had been created read only... :idea:
2 - After resetting the new database to not read only, the upgrade run of the package runs ok until one proc where it comes up with the error 'cannot use empty object or column names. use a single space if necessary'. The only rational explanation I have found so far suggest that it might be an unresolved reference - ie objects created out of order.
3 - I cant now run this package again as it fails with 'There is already an object named xxx in the database'
Is there a simple way to overcome these issues - perhaps I am using the packager in the wrong way...?
Packaged up a read only database, all objects (except users and roles), and selected data tables, into an exe.
1 - Create database worked fine, then the package failed - database had been created read only... :idea:
2 - After resetting the new database to not read only, the upgrade run of the package runs ok until one proc where it comes up with the error 'cannot use empty object or column names. use a single space if necessary'. The only rational explanation I have found so far suggest that it might be an unresolved reference - ie objects created out of order.
3 - I cant now run this package again as it fails with 'There is already an object named xxx in the database'
Is there a simple way to overcome these issues - perhaps I am using the packager in the wrong way...?
Comments
This is a difficult one to explain. First of all, why is the database being created as read-only? The best explanation I can think of is that the configuration of the MODEL database is read-only. Are all new databases you create (for instance, in Enterprise Manager) read-only? If so, you may want to adjust MODEL accordingly.
It is not a great suprise to me that it is then being created read only - I would tho suggest it an oversight on your part... it would make a lot more sense to create the basic database, populate it and then set the assorted readonly etc switches after the package completes!
What I am having real problems with seems to be the missing dependencies, a sad fact of real world databases. :oops:
It's not possible to run any queries other than select on a read-only database. It's a 'feature' of SQL Server.
Which is why I find it curious that the sql packager would take the initial state of read only for a database and create it that way then expect to put the ddl in place.
I am not sure why you would choose to do it that way round, I can only imagine it is a 'feature' that no one seems to have found till now.
I iterate again that running this kind of thing on a logshipped database is a relatively common requirement - certainly without knowing the internals I would be very worried to run it against production.
I also don't see what the problem is with setting up your packager to create a database, then once it's populated, reset the options - or even to have the options as options in the packager.
Furthermore, I am a little concerned that you keep coming back to the point I have already explained that I can work around (I prefer not to have to but hey) and ignore my more important point that it all falls over and can't get round missing dependencies. That is the core point I need to address, without which this packager is pretty useless to me.
You could run a simple query to bring the database on-line (RESTORE DATABASE [x] WITH RECOVERY), but that would break the log shipping.
SQL Packager's dependency handling is very good; the dependencies are generated by text-parsing all of the creation SQL. When we troubleshoot these 'out-of-order' accusations, we normally find a syntax error that caused an underlying object's creation to fail or a settings issue such as not using the 'include dependencies' setting. Only rarely does the dependency engine fail.
Is this an upgrade or a new database package, because you may not know that SQL Packager's scripts are static and creating an upgrade package for a set of two databases and running the package on a third database which is not exactly the same as one of the other two databases will cause these types of problems as well.
I am quite familiar with log shipped databases.
I wish to run the packager against a read only database, extract the ddl, the dml and selected data populations and carry them out of the production server farm into the development environment, creating a new database as and when required.
I only want selected data, as I wish to leave all classified and client personal data within the secure environment.
I am at a complete loss as to why on earth you imagine this is impossible. All the packager needs to do is read from the database. Or are you planning for the packager writing to my production db?
All it needs is the packager to be a bit more smartly constructed, you could quite easily set it up to make sure it doesn't CREATE a read only database, and if the source database is read only (easily checked a number of ways but I use select databasepropertyex('dbname', 'Updateability') ) then once all the packager has completed the write element to then issue the alter database command.
In fact you could easily add in
<create the database>
alter database dbname set READ_WRITE
<run the packager here>
alter database dbname set READ_ONLY
This in effect being the way I get round the issue in the first place.
Now, I see what you are suggesting in the dependency handling part, I have double checked. The issue was caused by the options to ignore quoted identifiers and ansi nulls.
Thanks for the help.
Packager doesn't create databases as read-only when you run the package, that is unless SQL Server creates new databases as read-only on your configuration.
My sql server is not configured to do so, the only dbs that get created read only are from the packages created from a read only database.
I have tried this with PUBS.
Alter database PUBS set READ_ONLY
Create a package.
Try to run the package as create PUBS2.
Fails as it creates the database readonly.
Using 5.2.0.49 Nice mosaic btw.
I see; the problem is this: Packager's packages create the database with the same options as the one you'd packaged originally. If you package a read-only database and run the package, the database is created as read-only and the schema updates go ka-boom. Seems like a bit of an oversight on our part.
The workaround would be to create the database manually, then run the package and tell it to upgrade an existing database and point it at the new database. In other words, do not instruct the package to create a new database.
If you're automating this, you can make a CREATE DATABASE SQL script and save this as a file, then use the package's /presql command to run the database creation script. When you run the package from the command-line, omit the /makedatabase switch.
Sorry for all of the misunderstanding!