November 12, 2009 at 8:49 am
I had a similar challenge and I did it to myself. We underwent a new database design for the next generation of one of our products. We decided to "do it right" rather than the old way of "add a column when you have something to save".
We also have common columns (not fields) in most of the tables. There are intersection tables and relationship tables that don't need the common columns. So while parsing my column definition file if I came up with a table that was not defined I issued a CREATE TABLE statement. After that it was ALTER TABLE all the way. My tool is in .Net and I used text files.
What is surprising is that somebody bought a 3rd party product that has no views or stored procedures at all. I have several major works in production that way but no more. Did they give you no guidance whatever as to indexing? It could be that you left that out for the sake of the article. Please say that this is true. If not then you are being very kind by not outing the vendor. Bone up on your missing index DMV.
ATBCharles Kincaid
November 12, 2009 at 8:57 am
Hi,
I'm using a similar approach to generate tables, indexes and stored procedures to build and populate data warehouses, all from Excel.
Basically you have one tool to create, maintain and generate your mappings. Plus, they're documented.
I use VBA to generate all the database objects.
René
Kind regards,
René Berends
November 12, 2009 at 9:01 am
Why not use SQL Server Integration Services? In SSIS 2008, you can use the import/export wizard to specify the input as an Excel file. You can then choose to create a table, rather than move the sheet to an existing one.
Granted, you'd have to use the wizard once for each sheet, but from the specs that sounds easier.
Of course, there can be some pesky data typing issues...
November 12, 2009 at 9:04 am
rsteckly (11/12/2009)
Why not use SQL Server Integration Services? ...
Because his spreadsheet has specifications for that date and not the actual data.
ATBCharles Kincaid
November 12, 2009 at 9:06 am
Duh. Right, sorry.
November 12, 2009 at 10:00 am
It seems like it would be an easy thing to add some VBA code to the excel sheet with an ADO connection to the database.
ADOdb.Open (connectionstring)
Loop table names
Apply Filter
ADOdb.CommandText = Concatenate all the relevant cells
ADOdb.Execute
Loop Back
ADOdb.close
This should run each create statement in a couple of minutes
November 12, 2009 at 11:28 am
Personally, I would have not created a SQL script at all. I would have created a VBA module (formerly known as an Excel macro) that reads the spreadsheet columns and creates a text file/SQL script as output, containing Create Table statements, which will create all of the tables. Then you just have to open the generated SQL script and run it. The Excel macro could create one script per table, or one per several tables.
That seems simpler to me. But then, I'm good with both SQL and VBA.
November 12, 2009 at 12:01 pm
maybe i'm over-simplifying. but with only 4 or 5 tables, why couldn't you just import them using the Import/Export Wizard? every solution i've seen including the OP seems way more complicated than it needs to be. VB module? there's already a built-in wizard for this.
November 12, 2009 at 12:59 pm
Seems like you were working with inept management. If the vendor I'm working with doesn't trust their own work, I don't think I would be comfortable with them. At the least hold back the 10% or whatever retention should have been written into the contract subject to my satisfaction.
November 12, 2009 at 1:08 pm
The requirements should have been written into the document. If you're looking at state and federal level - the request for proposal might need to be rewritten. It could be a change control issue or an out for the vendor if there are problems. The vendor has a low level of confidence in their product or is trying to apply it generically across a number of RDMS.
November 12, 2009 at 2:15 pm
All of these last statements are true, but the author specifically said it was too late for any of those things, and sometimes you just find yourself saying 'Next time I won't....' but until then, you just have to deal with it.
I'm also sure he did not then say 'Hey, now I have this cool program/script generator, who needs contracts and project specs anymore'
If you don't need it, don't use it. Unfortunately, in the real world, this kind of thing happens more frequently than we would like.
November 13, 2009 at 11:53 am
Well it's a good 5-6 months since I created that script, and it looks as if the project to implement this new software has been delayed until March next year!
Quite frankly, it's difficult to guess at what the politics are.
The consultancy, who is not the software vendor, may well not totally trust the software.
Someone in the higher management structure is keen to be one of the first organisations to successfully implement this software .... in order to impress /their/ (government) client ... or to attract a bonus funding reward ... maybe!
And somewhere along the line, it had been decided, between the consultancy and software client, that we had to deliver something before the consultancy did!! [and they still haven't, as far as I know!!]
The chain of relationships is far from simple to say the least!
Software Vendor
- Software consultancy
- Government body i) (End user) and
- Government body ii) (Information Management)
Inevitably, with such an extended relationship between software vendor and end user, is it any surprise that focus can be pulled in a number of directions. Ideally, both government bodies will have elected a single representative, equally responsive to both, to simplify that relationship chain.
However, my experience of governmental bodies is that they are very reluctant to relinquish control, if they can possibly help it!
November 18, 2009 at 2:51 am
ian.hadlington (11/13/2009)
Well it's a good 5-6 months since I created that script, and it looks as if the project to implement this new software has been delayed until March next year!Quite frankly, it's difficult to guess at what the politics are.
The consultancy, who is not the software vendor, may well not totally trust the software.
Someone in the higher management structure is keen to be one of the first organisations to successfully implement this software .... in order to impress /their/ (government) client ... or to attract a bonus funding reward ... maybe!
And somewhere along the line, it had been decided, between the consultancy and software client, that we had to deliver something before the consultancy did!! [and they still haven't, as far as I know!!]
The chain of relationships is far from simple to say the least!
Software Vendor
- Software consultancy
- Government body i) (End user) and
- Government body ii) (Information Management)
Inevitably, with such an extended relationship between software vendor and end user, is it any surprise that focus can be pulled in a number of directions. Ideally, both government bodies will have elected a single representative, equally responsive to both, to simplify that relationship chain.
However, my experience of governmental bodies is that they are very reluctant to relinquish control, if they can possibly help it!
Unfortunately that post can be boiled down to 2 words:
Public Sector
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply