4 ms·

So far my experience is that getting a SQL query correct is fairly easy and usually fun but that the real difficulty, e.g., for SQL Server, is for the rest: (1

by graycat 12y ago

So far my experience is that getting a SQL query correct is fairly easy and usually fun but that the real difficulty, e.g., for SQL Server, is for the rest: (1) Installation. Grim. Barbed wire enema. Wiped out my Windows boot partition requiring that I reinstall everything starting with the Windows DVD. (2) Security. The stuff with users, logins, etc. and nearly everything in SQL Server Management Studio was clear as mud. In the end: (A) Assume that basically what is there is essentially old capabilities and access control lists, imagine how they might work, and make that a first guess. It helped. (B) Use SQL Server Management Studio only for passively reading but never for actual management where change things. (C) For each of the important steps in setting up security, do searches on the Internet, find standard text SQL statements, and try those. Save and profusely document the statements that do work. (D) Do system management only with SQL statements in simple text files and executed with the SQL Server command line utility SQLCMD.EXE. (3) Connection String. The last time I had to get a SQL Server connection string to work took a solid week of throwing things against a wall to see if they would stick. The effort was an unanesthetized upper molar root canal procedure.

jndsn402 12y ago
If you want to get an understanding of basic SQL without all that overhead, you can try Microsoft Access.
- graycat 12y ago
  > basic SQL That was easy. Think of it as close to set theory! I needed to get through all the overhead stuff since I'm using SQL Server in the server farm for my startup. So, I have code for Web pages with ADO.NET and also some stand alone programs that do SQL Server queries. Again, the only difficult stuff was the overhead stuff, and it would appear that the Khan course would not cover that.
- ufmace 12y ago
  Oh god, no more Access. Access has a weird semi-dialect of SQL where it's insanely picky about how you position parenthesis in multiple join statements. I found it impossible to get a query right without the graphical query generator, and that's after routinely writing 5-10 table joins in T-SQL. No thanks, I'd recommend Sqlite over it anyday for a lightweight database. Works great on command line or in any of dozens of helper tools on any platform, and it's Free.
  - marvel_boy 12y ago
    I agree with the SQLlite recommendation.
ufmace 12y ago
I haven't found that stuff overly difficult so far. http://downloadsqlserverexpress.com/ http://downloadsqlserverexpress.com/ is a nice source to install SQL Server and Management Studio from, which also has a link to a blog post kinda mocking how ridiculously hard it is to find the installer on the Microsoft website. I also find it odd just how long it takes to install, versus Postgresql on Windows installing in like 5 minutes I think. I'm not sure what you're trying to do with users and permissions, but I haven't had much trouble with that in Management Studio. Add a new login in the server Logins folder, set either a username/password or domain authentication, select databases and set permissions. Not that I've tried anything tricker like assigning multiple users to a role, or doing stuff with AD groups. https://www.connectionstrings.com/ https://www.connectionstrings.com/ is a decent resource for connection strings too. It seems to be pretty easy, as long as you're connecting from other Microsoft tools, like Management Studio, sqlcmd, ADO.NET, etc. I'm pretty sure I once spent like a day or so trying to figure out how to get ActiveRecord to connect to SQL Server before I got it working. That said, the usual lessons could stand to pay more attention to this kind of management stuff.
- graycat 12y ago
  Many thanks. Your post is a keeper. I've got SQL Server working now with notes on how I did it and with some simple text files with SQL commands to be run with the Microsoft SQLCMD.EXE for setting security. For the connection string, I found something that is working and, of course, have it documented, saved, etc. To define the tables and columns, I have that also as just simple text in a file run with SQLCMD.EXE. To keep SQL Server installation problems from ruining my boot main partition, finally I am good at using NTBACKUP to get a clean backup and know how to restore it. So, before doing anything like a SQL Server install, I will use NTBACKUP to save the main boot partition. If SQL Server messes up, then, boom, I will just restore the boot partition and start over with the SQL Server install. The worst time with SQL Server installs it wasn't clear what the heck to click on, ended up with two versions installed side by side, tried to uninstall one, and ended up with a sick SQL Server that wouldn't do even the simplest things. Another time I wanted SQL Server to use a database from an earlier install, and trying to do this got SQL Server sick. Trying to uninstall and reinstall ruined my boot partition. But, as soon as I can get the other work done, I will get the first server computer for my "server farm", install some recent versions of Windows Server, SQL Server, IIS, etc. and go live. Then, with Windows Server, etc., I will be back into system management again. Today I am fixing a bug in my Web site: I have two Web pages where they use server.transfer to send a user from one to the other, and I have a fairly elaborate session state to be saved in one page and restored in the next one (instead of Redis, wrote my own session state store with just de/serialization from/to byte strings of instances of my session state class and two collection classes). But going between the two pages I must have coded when half asleep and didn't get quite right. So fixing that now. Also putting in lots of documentation to make it clear just what is going on. My wisdom in building a Web site with ASP.NET: Start with the class for the session state you want for your site. That is, concentrate on this data, with essentially global scope. The first crucial work is just being really clear on just what the session state data is to be. The second most important thing is to be clear on just how ViewState works. ViewState is useful and can save some coding. Third, in positioning things on the Web page, maybe make good use of instances of the class Place Holder. Fourth, have clearly in mind all the data that is available from a post back -- some of that data can be useful. Fifth, make good use of the ability to write data to the log file. Last, have clearly in mind when routines Page_Init Page_Load the button click routines Page_Unload etc. run. After that, just keep it all simple and clear for the user.

by graycat 12y ago

jndsn402 12y ago

If you want to get an understanding of basic SQL without all that overhead, you can try Microsoft Access.

graycat 12y ago
> basic SQL That was easy. Think of it as close to set theory! I needed to get through all the overhead stuff since I'm using SQL Server in the server farm for my startup. So, I have code for Web pages with ADO.NET and also some stand alone programs that do SQL Server queries. Again, the only difficult stuff was the overhead stuff, and it would appear that the Khan course would not cover that.
ufmace 12y ago
Oh god, no more Access. Access has a weird semi-dialect of SQL where it's insanely picky about how you position parenthesis in multiple join statements. I found it impossible to get a query right without the graphical query generator, and that's after routinely writing 5-10 table joins in T-SQL. No thanks, I'd recommend Sqlite over it anyday for a lightweight database. Works great on command line or in any of dozens of helper tools on any platform, and it's Free.
- marvel_boy 12y ago
  I agree with the SQLlite recommendation.

ufmace 12y ago

I haven't found that stuff overly difficult so far. http://downloadsqlserverexpress.com/ http://downloadsqlserverexpress.com/ is a nice source to install SQL Server and Management Studio from, which also has a link to a blog post kinda mocking how ridiculously hard it is to find the installer on the Microsoft website. I also find it odd just how long it takes to install, versus Postgresql on Windows installing in like 5 minutes I think. I'm not sure what you're trying to do with users and permissions, but I haven't had much trouble with that in Management Studio. Add a new login in the server Logins folder, set either a username/password or domain authentication, select databases and set permissions. Not that I've tried anything tricker like assigning multiple users to a role, or doing stuff with AD groups. https://www.connectionstrings.com/ https://www.connectionstrings.com/ is a decent resource for connection strings too. It seems to be pretty easy, as long as you're connecting from other Microsoft tools, like Management Studio, sqlcmd, ADO.NET, etc. I'm pretty sure I once spent like a day or so trying to figure out how to get ActiveRecord to connect to SQL Server before I got it working. That said, the usual lessons could stand to pay more attention to this kind of management stuff.

graycat 12y ago
Many thanks. Your post is a keeper. I've got SQL Server working now with notes on how I did it and with some simple text files with SQL commands to be run with the Microsoft SQLCMD.EXE for setting security. For the connection string, I found something that is working and, of course, have it documented, saved, etc. To define the tables and columns, I have that also as just simple text in a file run with SQLCMD.EXE. To keep SQL Server installation problems from ruining my boot main partition, finally I am good at using NTBACKUP to get a clean backup and know how to restore it. So, before doing anything like a SQL Server install, I will use NTBACKUP to save the main boot partition. If SQL Server messes up, then, boom, I will just restore the boot partition and start over with the SQL Server install. The worst time with SQL Server installs it wasn't clear what the heck to click on, ended up with two versions installed side by side, tried to uninstall one, and ended up with a sick SQL Server that wouldn't do even the simplest things. Another time I wanted SQL Server to use a database from an earlier install, and trying to do this got SQL Server sick. Trying to uninstall and reinstall ruined my boot partition. But, as soon as I can get the other work done, I will get the first server computer for my "server farm", install some recent versions of Windows Server, SQL Server, IIS, etc. and go live. Then, with Windows Server, etc., I will be back into system management again. Today I am fixing a bug in my Web site: I have two Web pages where they use server.transfer to send a user from one to the other, and I have a fairly elaborate session state to be saved in one page and restored in the next one (instead of Redis, wrote my own session state store with just de/serialization from/to byte strings of instances of my session state class and two collection classes). But going between the two pages I must have coded when half asleep and didn't get quite right. So fixing that now. Also putting in lots of documentation to make it clear just what is going on. My wisdom in building a Web site with ASP.NET: Start with the class for the session state you want for your site. That is, concentrate on this data, with essentially global scope. The first crucial work is just being really clear on just what the session state data is to be. The second most important thing is to be clear on just how ViewState works. ViewState is useful and can save some coding. Third, in positioning things on the Web page, maybe make good use of instances of the class Place Holder. Fourth, have clearly in mind all the data that is available from a post back -- some of that data can be useful. Fifth, make good use of the ability to write data to the log file. Last, have clearly in mind when routines Page_Init Page_Load the button click routines Page_Unload etc. run. After that, just keep it all simple and clear for the user.