script it…walk away.
people are talking about me again… o_o
However instead of plots to overthrow my kingdom, or curses that would cause me to eat pie and lose weight, or saying my name three times in a mirror so that I become summoned and forced to bust a cap…instead of stuff like THAT…they are props for working on some code!
I made some new changes and fixed an embarrassing bug in my previous contribution for the sp_DatabaseRestore. In an ideal world I would just skip the part of this post where I point out my mistakes and explain what they were, however Github does a great job at keeping history so…
script it…walk away.
Over the last a couple of weeks I started reviewing an automated restore solution I had been playing around with for a couple years. RedGate SQL Backup had thrown some errors and since we couldn’t go without backups while waiting on RedGate support we decided to move ALL backup operations to Ola Hallengren’s scripts. We also decided to ditch RedGate because they sent us back a list of reported bugs we had submitted and told us they aren’t going to fix it. So in other words “despite the fact that you have multiple licenses of multiple softwares across many servers, we feel your issues as a customer aren’t important to us as a software company.” With that we decided to roll our own solutions that we can customize for our environment…
I was reading the Voice of the DBA and it referred to a post by Brent Ozar about caching at the database level. After discussing it with my homey, he thought it sounded a lot like memoization (I like to say it with a 3 stooges voice, mem moy Zay Shun). According to wiki, Memoization is a specific case of optimization, which seems to be what we are doing when we cache the results of a stored procedure.
testing is science…
In college one of my mentors was a jedi master of simulations and testing. The guy could write a simulation for just about anything you can think of, and with anything you can think of. I’m pretty sure that he once made a neural network for simulating dinosaur procreation rates…using sticks and mud (true story). Simulating workloads can help you thoroughly test ideas and present data to management that will allow them to make informed decisions. IRL, your career will be much happier if you can test your ideas BEFORE deploying them to production. No one is asking you to have good ideas all the time, but the business is depending on you to come up with ideas that will work as intended. The only way to accomplish this responsibly, is to test.
The best defense is a strong CHAINSAW SWORD!!!
There may come a time in your career when you are tasked with finding out how a 3rd party application functions because some 3rd parties have completely closed source applications, or better yet, APIs that perform poorly. Microsoft SQL Server Profiler can help you identify what tables are used, procedures and what data is inserted when functions are run.
Pipboy level utility!
I came across a wonderful script by SQLDenis at Less Than Dot – Blog. I have been mapping data between a Numara TrackIt 8 system and a ManageEngine ServiceDesk system. While trying to find out how ManageEngine maps fields (with no database documentation, or assistance from their support team), I came across some fields on the web form that I needed to map, but I couldn’t find them in the database. SQL Search by RedGate helped to some degree, but in this case I needed something a little faster.
Using the sp written by SQLDenis at http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions–1#2 , I was able to put an identifying string (think codeword) then search it out. This enabled me to find form fields that used columns in the db named “Attribute_301” etc.
Thanks SQLDenis, you may take your place among the real men of genius.
Your hero for the day!
So you might already know this, I didn’t and it kicked my ass for 30 minutes.
So you have a report deployed in reporting services, it takes date parameters, you want it to execute lets say…between Yesterday and Today…
Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option
EXEC sp_configure ‘show advanced options’ , ‘1’;
EXEC sp_configure ‘clr enabled’ , ‘1’ ;
Script it…then walk away…
After creating and deploying your SSIS package, you might want to schedule it.
Script it…then walk away!
The problem: Create a SSIS package that will update a table with only new records
The quick and dirty solution:…
1. Drop a Data Flow Task
2. Inside 1, Drop (in this order) an OLE DB Source, Lookup, and SQL Server Destination
3. The OLE DB Source is your select statement from data source, for example mine is:
SELECT data1, data2, data3, etc, from sourceTable
4. The lookup contains a query to compare, for example mine is:
SELECT data1, data2, data3, etc, from destinationTable
5. Configure the Error Output on the Lookup to Redirect Row
6. Connect the Red Arrow from the Lookup to your SQL Server Destination