Tuesday, January 11, 2005
Blizzards, SQL Scripts & Sunglasses
O.K. So I've finally dug myself from underneath the massive amount of snow dropped upon the west coast since last Friday. Victoria recieved about 1 foot of snow in a day or so. Further up the Island had over 1.5 feet, I heard. Much of Vancouver Island was without power for some time. It really didn't affect me all that much. I was sick on Friday and didn't venture out anyways. We still had power in Victoria. The roads were still kind of rough on Saturday. People from the island really don't know how to drive in winter. They also need to learn to not let your tires go completley bald. There is a reason they are built with treads in them. Tires should always have treads. It keeps your car out of the ditches.
I've been pluggin along lately. Nothing really fancy. Just standard, PHP make it work, scripting. The joys of this project come from working with MS-SQL Server. I know I've found several oddities that just make my brain hurt, and my rants fun filled. I'll share the newest one with you.
I have a server running on the laptop for development, and I want to export everything to the devel server so everyone else can use it. I thought I had done that during my last visit, but apparently there were some errors happening. After my short investigation I discovered the row insertions were failing becuase I had NULL values going into NOT NULL fields. That seems rather bizarre to me, and not something I normally miss in my DB design. So I open up Enterprise manager and check things out. It seems as though the data import did not carry over the default value constraints from table to table.
I went on a mission to try and find and easy way to export a table definition(s) from one DB to another DB on different machines. The export wizard did not seem to work. It's not very intuitive to do something like that in SQL Server. It's soooooooooo easy to go:
Do you think there is a simple way to just get the table defs in MS-SQL? Tasks like this are exactly the reason why I really _dislike_ GUI for administration activities. Some people will say "But there are all these tools out there to do things for you, why not make use of them,. It's not that hard to learn them."
Why should I learn an awkward tool, that provides what I would say is fairly basic functionality, so that it can provide these simple features in a several step process. That simple one line of typing for a PostgreSQL system is translated into these steps for MS-SQL Server
I had to open another program "Query Analyzer" and load a script from a file into the analyzer and then run it. I gave up on this method of doing things when I started to get results like this:
Maximum number of bytes = 8060?!?!?! Per row?!?!?! I'm not sure where 8060 comes from? If it were an 8kb row max wouoldn't the value be 8192? Maybe they have some wierd system table information stored that takes up 132 bytes? Odd ... very odd. At this point I don't even care anymore.
I did discover that you can basically run through almost the same process in the Export Wizard. You have almost just as many steps as exporting to scripts, because you have to register sending and recieving servers, and authenticate. Then the servers communicate with the Microsoft OLE DB blah blah blah object something, spinning pictures, percentages ... O.K. All Done! I ended up doing the import through the wizard, but I _hate_ a procedure like that. Here's why:
I already know that a straight export into (valid) SQL scripts and importing produces some funky warnings. Not that the information said warnings provide is useful really. What has happened to these warnings from the import wizard though? Our GUI friends have been so wonderful to provide me with an awkward tool for me to spend a great deal of time to do a simple task, and to help me out their wizard doesn't even warn me about possible problems I _know_ are there, only becuase I wasted a great deal of time trying to figure out how to do the simple task a completely different way with another set of the awkward tools they have so graciously provided me with.
Just provide me with a peice of software that does exactly what it's supposed to do. Give me a way to control what it's supposed to do just by telling it in the simplest manner possible. Don't make me learn several other pieces of software just to interact with the major software that does all of the work for me anyways. What a bad set up! I'd prefer to actually expand my knowledge and learn the proper way for some technology to work (like knowing how the SQL definitions should work ... for any RDBMS ... not just specifically to one project). But using a toolset that is hard to work with, doesn't even do a very good job of things, and involves me trusting that it's doing what it's actually supposed to do is a bit much to ask of me. Microsoft seems to ask that of me alot. Trust is the issue in the world of software these days, and I don't have much trust in MS products.
I do however trust my sparkly new sunglasses to keep the UV rays from my eyes. I was so excited today when I actually got to wear them on the drive out to Sooke. The sun was shining today ... what a nice feeling.
¶ 1:30:00 AM
I've been pluggin along lately. Nothing really fancy. Just standard, PHP make it work, scripting. The joys of this project come from working with MS-SQL Server. I know I've found several oddities that just make my brain hurt, and my rants fun filled. I'll share the newest one with you.
I have a server running on the laptop for development, and I want to export everything to the devel server so everyone else can use it. I thought I had done that during my last visit, but apparently there were some errors happening. After my short investigation I discovered the row insertions were failing becuase I had NULL values going into NOT NULL fields. That seems rather bizarre to me, and not something I normally miss in my DB design. So I open up Enterprise manager and check things out. It seems as though the data import did not carry over the default value constraints from table to table.
I went on a mission to try and find and easy way to export a table definition(s) from one DB to another DB on different machines. The export wizard did not seem to work. It's not very intuitive to do something like that in SQL Server. It's soooooooooo easy to go:
pg_dump -s -d <database> -t <table> > table.sql
Do you think there is a simple way to just get the table defs in MS-SQL? Tasks like this are exactly the reason why I really _dislike_ GUI for administration activities. Some people will say "But there are all these tools out there to do things for you, why not make use of them,. It's not that hard to learn them."
Why should I learn an awkward tool, that provides what I would say is fairly basic functionality, so that it can provide these simple features in a several step process. That simple one line of typing for a PostgreSQL system is translated into these steps for MS-SQL Server
- Open Enterprise Manager
- Dig into your DB tree to find your database (could be several steps)
- Right click on your DB, choose "All tasks", then "Generate SQL Scripts"
- You now have to click on the "Show All" button
- Select the object(s) you wish to script
- Click on the Formatting tab
- Select the "Include entended properities" checkbox
- Click on the "Options" tab
- Select the checkbox for "Script keys, constraints and defaults" etc.
- Then you get to browse for a place to put the file you want the script in
I had to open another program "Query Analyzer" and load a script from a file into the analyzer and then run it. I gave up on this method of doing things when I started to get results like this:
Warning: The table '' has been created but its maximum row size (10271) exceeds
the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this
table will fail if the resulting row length exceeds 8060 bytes.
Maximum number of bytes = 8060?!?!?! Per row?!?!?! I'm not sure where 8060 comes from? If it were an 8kb row max wouoldn't the value be 8192? Maybe they have some wierd system table information stored that takes up 132 bytes? Odd ... very odd. At this point I don't even care anymore.
I did discover that you can basically run through almost the same process in the Export Wizard. You have almost just as many steps as exporting to scripts, because you have to register sending and recieving servers, and authenticate. Then the servers communicate with the Microsoft OLE DB blah blah blah object something, spinning pictures, percentages ... O.K. All Done! I ended up doing the import through the wizard, but I _hate_ a procedure like that. Here's why:
I already know that a straight export into (valid) SQL scripts and importing produces some funky warnings. Not that the information said warnings provide is useful really. What has happened to these warnings from the import wizard though? Our GUI friends have been so wonderful to provide me with an awkward tool for me to spend a great deal of time to do a simple task, and to help me out their wizard doesn't even warn me about possible problems I _know_ are there, only becuase I wasted a great deal of time trying to figure out how to do the simple task a completely different way with another set of the awkward tools they have so graciously provided me with.
Just provide me with a peice of software that does exactly what it's supposed to do. Give me a way to control what it's supposed to do just by telling it in the simplest manner possible. Don't make me learn several other pieces of software just to interact with the major software that does all of the work for me anyways. What a bad set up! I'd prefer to actually expand my knowledge and learn the proper way for some technology to work (like knowing how the SQL definitions should work ... for any RDBMS ... not just specifically to one project). But using a toolset that is hard to work with, doesn't even do a very good job of things, and involves me trusting that it's doing what it's actually supposed to do is a bit much to ask of me. Microsoft seems to ask that of me alot. Trust is the issue in the world of software these days, and I don't have much trust in MS products.
I do however trust my sparkly new sunglasses to keep the UV rays from my eyes. I was so excited today when I actually got to wear them on the drive out to Sooke. The sun was shining today ... what a nice feeling.
¶ 1:30:00 AM
