InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 17:46:31 GMT20http://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspx[quote][b]ian.hadlington (11/13/2009)[/b][hr]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![/quote]Unfortunately that post can be boiled down to 2 words:Public SectorWed, 18 Nov 2009 02:51:05 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxWell 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!Fri, 13 Nov 2009 11:53:43 GMTian.hadlingtonhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxAll 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.Thu, 12 Nov 2009 14:15:49 GMTDan Guzman - Not the MVPhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxThe requirements should have been written into the &#100;ocument. 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.Thu, 12 Nov 2009 13:08:06 GMTreggiesnellyhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxSeems 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.Thu, 12 Nov 2009 12:59:45 GMTreggiesnellyhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxmaybe 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.Thu, 12 Nov 2009 12:01:07 GMTScott D. Jacobsonhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxPersonally, 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.Thu, 12 Nov 2009 11:28:04 GMTDavid Walker-278941http://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxIt 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 namesApply FilterADOdb.CommandText = Concatenate all the relevant cellsADOdb.ExecuteLoop BackADOdb.closeThis should run each create statement in a couple of minutesThu, 12 Nov 2009 10:00:43 GMTDan Guzman - Not the MVPhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxDuh. Right, sorry.Thu, 12 Nov 2009 09:06:36 GMTrstecklyhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspx[quote][b]rsteckly (11/12/2009)[/b][hr]Why not use SQL Server Integration Services? ...[/quote] Because his spreadsheet has specifications [b][i][u]for[/u][/i][/b] that date and not the [b][i][u]actual[/u][/i][/b] data.Thu, 12 Nov 2009 09:04:54 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxWhy 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...Thu, 12 Nov 2009 09:01:17 GMTrstecklyhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxHi,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éThu, 12 Nov 2009 08:57:16 GMTRené Berendshttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxI had a similar challenge and [b][i]I did it to myself.[/i][/b] 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 [b][i][u]very[/u][/i][/b] kind by not outing the vendor. Bone up on your missing index DMV.Thu, 12 Nov 2009 08:49:11 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspx[quote][b]katesl (11/12/2009)[/b][hr]What I meant to say in my first post is that programmers should know what a programmer's editor can do and when to use it. Time estimates based on document editing when what's needed is a programmer's editor are way high-- unconscionable to contemplate charging your client for many hours of work when half an hour will do it.[/quote]£50 an hour no way, I wouldn't get out of bed for thatThu, 12 Nov 2009 08:36:17 GMTNeurohttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxWhat I meant to say in my first post is that programmers should know what a programmer's editor can do and when to use it. Time estimates based on document editing when what's needed is a programmer's editor are way high-- unconscionable to contemplate charging your client for many hours of work when half an hour will do it.Thu, 12 Nov 2009 07:49:26 GMTkateslhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspx[quote][b]dcawvive (11/12/2009)[/b][hr]I was basing the 10K on the 80 hours estimated in the article @ $100 /hour plus a few hours of debugging, tweaking etc.[/quote]Currency conversion issueI'm working in £, you're dealing with $ ;-)Thu, 12 Nov 2009 07:44:23 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxI was basing the 10K on the 80 hours estimated in the article @ $100 /hour plus a few hours of debugging, tweaking etc.Thu, 12 Nov 2009 07:21:31 GMTdcawvivehttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxI liked the article. Easy to follow and understand. I have been consulting for more than 20 years and I have only found this situation a few times. But those were always ugly and the conditions by which the "politics" occured was equally unique. I disagree with the earlier criticism about 3rd party vendor control and our ability to demand information. I have faced these exact problems in state and federal government situations as well as private sector. The fact is, I do not think you have a lot of outside exposure if you believe you can impose your own policies on the parties you are working with. Keep in mind, and this is to those who are wanting to be judgemental, if it was easy, you probably would not have been called in the first place. Always good to ask for the simple to be possible, but accept the reality that in most cases, people are usually not that stupid and they *always* have their reasons. Otherwise, go work for a single company and then you can impose your policies according to your stupendously supportive boss :)upwards and onwards!Thu, 12 Nov 2009 06:46:08 GMTLambchop4697http://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspx[quote][b]hickymanz (11/12/2009)[/b][hr]you mean I could get 10k just for writing a script ... right im on that :)[/quote]Unfortunately only the big consultancies can get away with charging £1000 per day for consultants.Thu, 12 Nov 2009 06:16:23 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxWith some vendors, once you get past the shiney sales patter and have been convinced into signing on the dotted line, can be very obstructive.Thu, 12 Nov 2009 06:14:11 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxyou mean I could get 10k just for writing a script ... right im on that :)Thu, 12 Nov 2009 06:12:37 GMTNeurohttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxThat's a good workaround but there are modeling tools available that will build a database, logical and physical models from a spreadsheet. Then again, some of the contractors I have dealt with would gladly bill 80 hours to type in table definitions. Why would you deal with a vendor that wouldn't supply a build script? Did the person that purchased this product factor in the additional 10K in cost just to begin to set up the program?Thu, 12 Nov 2009 05:43:27 GMTdcawvivehttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxIn the old version of Kimball's Data Warehouse Toolkit" book there is an excel spreadsheet that can be used to design databases and create/execute DDL. That goes back to 1997 and I recall it's pretty good. Just a thought...Thu, 12 Nov 2009 04:53:53 GMTBill Preachukhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxNice Spreadsheet, in my job , I Tend to get the Data in the Tables rather the the Spec's , it is up to me/the team to decide the specifications Eventually , as I seemed to be doing it on a daily basis I ended up writing some VBA which Analyzed all the Data in the Columns and then built the Tables around the information , this tended to save me much more time. I haven't used it since I upgrade to Excel 2007 and Therefore I have not updated the Context menu's that let me execute this But at some point I'll do that and put up Post for itThere is usually some minor alterations , like setting Keys and and Adding a couple of Columns , but most of the leg work is done so its just tweaksThu, 12 Nov 2009 04:40:23 GMTNeurohttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxWhen the software is a major application designed and tested by a third party, that third party is required to install it on our servers and prove it working before it can be signed off (and they get paid). No way would I get into that awful situation in the first place but well done for finding a solution.Thu, 12 Nov 2009 04:24:15 GMTP Joneshttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxNice article but why do you say you will never need to use this again?I've been using Excel to design tables for a few years, table designs can then easily be shared with the rest of the team, comments can be added and changes easily made. Its also fairly easy to lift the data from Excel and drop it into Word for the design documentation.Once the design is final its a quick job to build the create table/index/foreign key scripts as your article details.Thu, 12 Nov 2009 02:07:18 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxI know the big vendors want to hold programmers captive in their IDEs (studios) but I'll never give up the programmer's editor that I use -- Epsilon. Using Epsilon macros and its regular expression search-and-replace, it's so easy to make scripts from text and to clean huge datasets.Thu, 12 Nov 2009 00:44:30 GMTkateslhttp://www.sqlservercentral.com/Forums/Topic817632-1685-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Excel/66806/">Database defintion from an Excel Spec?</A>[/B]Thu, 12 Nov 2009 00:18:56 GMTian.hadlington