Here is the video from my SQL Saturday #279 session on developer best practices & good habits to help DBAs keep the server healthy and secure. Please excuse the fact I was dealing with a sinus & chest infection during the presentation, then halfway through the camera died & we had to switch to Lauri’s Windows Phone…such a superior product! You’ll notice halfway through the presentation the resolution changes, and I apologize. Next time I do a speaking engagement I plan to use a tripod & better camera (or her phone again!)
During the presentation a good question came up regarding the effort required to manage SQL code in a source controlled application vs. in a stored procedure or view, and how I suggested keeping the code in source control can be perceived as a pain in the butt. In no way am I indicating source control is a pain. Source control is a necessary tool in the development process, that in my opinion should be used regularly by all developers regardless of platform or language. My goal is to compare the effort required when a simple change to a data type (for example) is needed, to check the project out, hunt for every location in the code where someone has hard-coded queries or statements that need to be changed, change those queries or statements (and hope you got them all), run unit tests, build/compile your application (assuming nothing else was negatively affected), deploy to test, send to QC for testing, deal with any other “issues” they find, then check the final build back into source control and release to prod, then sit in hypercare hoping nothing else happens…or simply have the DBA look in your stored procedure or view for any changes needed and change them immediately. The end point was it’s way easier to keep these in stored procs or views, not to mention it gives a centrally managed place for your database code that encourages communication between the developer and the DBA, making the entire team more effective. Sorry for any confusion.
Anyway, here’s the link to the slide deck for following along, as well as the presentation
If you attended my session on Connecting a SharePoint list to a SQL table for CRUD ops, thank you for coming! If you missed it, or if you wanted to review it again, my girlfriend was kind enough to record it for everyone! The projector can be difficult to see due to room lighting, but you can always follow along with the slide deck. Both are included in this post:
I fought with this one for nearly 8 hours today, attempting to install SSMS from SQL 2012. This requires that the .Net Framework 3.5 and 2.0 be activated in Windows Features. Sure, there’s a checkbox that you “should” be able to check and everything works fine. Well, not for me. I check it, click OK, it tells me it needs to download files from Windows Update, I say OK, and it throws this error.
Basic troubleshooting courtesy of Google says to make sure Windows Firewall is not blocking the connection, which in my case it wasn’t. Next, make sure you can get to Windows Update in general, which I could. Finally, it said to contact your IT administration…..which doesn’t help me because I *am* IT Administration (seriously?)
The solution (at least for me)? Elevated Command Line! Earlier I had to also activate my copy of Windows 8 Enterprise via command line thanks to a similar error. I found this solution in an MS Support article, but here’s the general idea:
- Run gpedit.msc, and look in Computer Configuration > Administrative Templates > System.
- Locate the “Specify settings for optional component installation and component repair ” and open it up.
- Select “Enabled” at the top, and at the bottom, check the box next to “Contact Windows Update directly to download repair content instead of Windows Server Update Services (WSUS)“.
- Click Apply, then OK.
- Then run gpupdate /force at command line.
Next, make sure your Windows 8 install media is loaded in your CD/DVD drive, flash drive, wherever, open an elevated command line and type the following:
Dism /online /enable-feature /featurename:NetFx3 /All /Source:<drive>:sourcessxs /LimitAccess
Where <drive> is the drive letter of your Windows 8 media. It took a few minutes, but worked like a champ! Now the .Net Framework 3.5 feature was enabled without any errors.
Hope this helps!
I was working on a rather lengthy query that used a typical CASE WHEN THEN ELSE END sequence, but it kept telling me “Case expressions may only be nested to level 10”, even though I wasn’t nesting or doing something unique with them, just like this:
when field1 = ‘data’ then ‘data1’
when field1 = ‘datax’ then ‘data2’
when field1 = ‘datay’ then ‘data3’
when field1 = ‘dataz’ then ‘data4’
when field1 = ‘dataa’ then ‘data5’
when field1 = ‘datab’ then ‘data6’
when field1 = ‘datab’ then ‘data7’
when field1 = ‘datad’ then ‘data8’
when field1 = ‘datae’ then ‘data9’
when field1 = ‘dataf’ then ‘data0’
when field1 = ‘datag’ then ‘data00’
when field1 = ‘datah’ then ‘data000’
end as new_field
But it still would give me the error. After reading around and thinking about the query as a whole, it turns out the reason why it barks about a maximum of 10 statements is because there was a primary call to a linked server. Normally, you can put as many WHEN THEN statements in a CASE, but apparently when there’s a linked server involved, the max is 10. In this one, this CASE statement was one of the selection criteria and the table.field it was looking at was on the same database, but the main FROM DATABASE.TABLE of the query was the linked server, which is what was causing all the problem. Looks like I’ll have to either create a temp table and read from that, or add that table to my SSIS packages each day!