IIS7 introduced the option to run your application pool as AppPoolIdentity. With the release of IIS7.5, AppPoolIdentity was promoted to the default option. You see this change if you’re running Windows 7 or Windows Server 2008 R2. On my Windows 7 machine, I’m able to define my Application Pool Identity and then create an associated [...]
Continue reading about Issue Creating SQL Login for AppPoolIdentity on Windows Server 2008
This morning I needed to compose a very simple SQL routine and it took me around five compiles until I got the syntax right. All I needed to do was transfer an active status from one entity (in my case a computer) to another. I decided to implement this by updating the same table in [...]
Why would you use “Stuff” instead of “Replace?” Good question. Both perform a similar function – replace a string with another string (or no string at all), but Replace has a simpler syntax. The Replace function looks like this: select replace(‘abcdef’,'bc’,'xx’) result: axxdef No locations to find, just replace the string. However, what if we had [...]
Here is a simple example of a routine to capture the results of an executed stored procedure so they can be acted upon. To be honest, I am posting this completely for selfish reasons as I tend to forget this syntax more often than I wish to admit. CREATE TABLE #Results ( Column1 varchar(64), Column2 varchar(64), ) [...]
For the uninitiated, a derived table is where you create a query that contains another query but that second query is wrapped in parenthesis and you will normally join to it for further results or set operations. The pseudo-code is like: Select a.Blah, b.BlahBlah From FirstTable a Join (select BlahBlah from SecondTable ) b On [...]
We can create a new copy of a table from an existing one, using the “select into” function. However, while “select into” is a powerful function it can also be a server killer due to the i/o overhead copying a bazillion rows in a single batch (if the source was a large table), and more [...]
Here’s something to contemplate: What’s the difference between “group by” and “select distinct” in a simple query like: select distinct a.LastName from dbo.Account a with (nolock) could be rewritten as: select a.LastName from dbo.Account a with (nolock) group by a.LastName Who can tell us what the difference in execution would be? When would you pick [...]
We use the @@Error and @@rowcount system variables all the time in queries and sprocs. However, one might be lead astray thinking they behaved like “regular” variables in programming, i.e., they are persistent, but they are not, they only hold the value from the most recent statement. Here’s an example where I declare a couple [...]