terça-feira, 22 de maio de 2012

Um pouco de SQL para relaxar: Order by em Operações de conjunto

Apesar de nós sabermos que não é necessário ter a coluna de ordenação ou filtro (a seleção) na lista de campos de retorno (a projeção), a não ser que...

Explicando... em caso de operações de conjunto (união, intersecção, ou exceção) a coluna do predicado (cláusulas da seleção, representada pelo where) deve estar na lista, pois haverá uma ordenação do grupo resultante.
Isto também nos impede de usar a Order By na parte superior da operação, ficando este sempre para o final da consulta. Isto se aplica a todos os comandos que venham a atuar no resultado final da operação.
Um caso que não se aplica é a cláusula Where, pois filtra cada parte antes da operação.

Login failed for user ''.

I was receiving log file entry in the summary of SQL Server log, wich states Login failed for user ''. There were no user name indicated inside the quotes. After many searches in the web, with no success, I've come to a answer.
Before the grand finalle I must say I've done many reconfigurations in IIS, SQL server properties, SQL server services,  Web config of the ASP.NET application, etc. Everything I could find in the Internet I've tried.
Then, I just look the application code for some clue. All of a sudden, a old database name pops up in my screen, prefixing a domain table, used to fill a dropdown list box. I've just deleted the old reference, and voilá (or Eureca!), the login failed for user '<empty>' was eliminated!
No problems with my database configurations, nor the IIS, but only with the application code, ok?
I hope to help many friends with my experience is this situation, when it will be the case, of course. I've spend many hours untill get successful, you don't need anymore.
That's all for now, folks.

Unreacheable code, can be "unthinked" code

It´s just a tip, not related to DB administration, but valid for all areas where the development can reach (including database). When programming, and the compiler warns you about an unreacheable code (e.g. warning CS0162: unreacheable code detected), don't use the pragma compilation instruction which tells the compiler to not to do its job: warn you! It's a bad decision because this means, in major cases, you make a bad algorithm. And against bad programming there is no such hardware power or enough memory. It will be always a heavy weight, maybe a failure or a breach to the security or a bug. But if it's unreacheable, how can it be a problem? What if a hacker change some status which make this part of your code to be reacheable (by tampering, or else)? Treat you code. It will reduce the cyclomatic complexity, become more "dry", and, who knows, more secure. 

Well, we know there are exceptions for almost everything, so, if that's the case, look how to use it in C#:
Pragma warning link: http://msdn.microsoft.com/en-us/library/441722ys(v=vs.80).aspx

Think! That's your job!

quinta-feira, 3 de maio de 2012

Upgrading SDF files from 2.0 to higher versions

When upgrading my .sdf file into a newer version (3.5), I got the error 8007007e the specified module could not be found. This way, no clues, no dinner, no love. After looking around (Internet seems to be infinite when you cant find what you´re lookin for), I have the idea to copy all files from the folder of \program files\SQL Server compact...\ into the folder EN, inside of this same folder, where the isqlw.exe lies down. Voilá, no more problem when upgrading. For some reason, my files were outside the language folder, hence the libraries were also outside, unreacheable for the isqlw.
This applies to SQL Server Compact running on Windows CE 5, using .NET Compact framework 2.0 and 3.5. My desktop has VS2008 and VS2010 ultimate installed.
Hope to help, just in case.