Well today was my first attempt outside of a classroom setting to dig my heels into SSAS DMX. I have an idea that I want to put into action (automating a process) and I believe it will benefit my organization. So fire up SSMS and connect to instance that has Analysis Services.
1) Start a New DMX Query
2) Execute Script
SELECT * FROM $system.DBSCHEMA_CATALOGS
3) Returning Specific Columns
Similar to Transact-SQL you just specify the columns you want; however, you need to wrap up the column names with brackets [column].
SELECT [CATALOG_NAME] FROM $system.DBSCHEMA_CATALOGS
Pretty simple. Now let’s try with XMLA, which is XML for Analysis Services.
Just like before connect to an instance which has Analysis Services and published cubes.
1. Start a New XMLA Query
2. Execute the Script
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis"> <RequestType>DBSCHEMA_CATALOGS</RequestType> <Restrictions /> <Properties /> </Discover>
There are many ways to go about getting the last backup date for any and all database but I tend to lean towards set based methods over using the GUI for many reasons. One in particular is for the fact that I can turn set based into an actionable report since monitoring backups is important after all.
The method I use the most is the following because it only returns information about databases that currently exists. I really do not need to see information about what used to exist, but I will also show that example as well. Before I go on you might want to understand the anatomy of both the master.sys.databases catalog view and msdb.dbo.backupset table.
/* GET LAST BACKUP DATE FOR ALL EXISTING DATABASES */ SELECT d.name, MAX(b.backup_finish_date) 'Last Backup Date' FROM master.sys.databases d JOIN msdb.dbo.backupset b ON d.name = b.database_name WHERE (b.type = 'D' OR b.type = 'I') GROUP BY d.name ORDER BY d.name
The results indicate (104 row(s) affected). So I have 104 databases on this particular instance.
This is the example of returning the history for the last backup date which may contain information about past databases which depends on your maintenance for backup history. So this may vary.
/* GET LAST BACKUP DATE FROM BACKUPSET */ SELECT database_name, MAX(backup_finish_Date) 'Last Backup Date' FROM msdb.dbo.backupset WHERE (type = 'D' OR type = 'I') GROUP BY database_name ORDER BY database_name
The results indicate (108 row(s) affected) so between the two statements you can see there is a difference of four rows which is why I avoid using the msdb.dbo.backupset as the only source of record.
The easiest way to determine which databases no longer exists is to execute the following.
/* RETURN NON EXISTING DATABASE NAMES */ SELECT DISTINCT database_name FROM msdb.dbo.backupset WHERE ( database_name NOT IN ( SELECT name FROM master.sys.databases ) )
The results indicate that (4 row(s) affected) and the names of the database that are not current.
I recently stumbled upon a problem where there were duplicate commands within the sub menu of Data in Business Intelligence Development Studio 2008. To give you a birds eye view below is what we were facing.
With a little research and some musical chairs with keywords I stumbled upon the following post: Visual Studio team System 2008 Database Edition FAQ which pinpointed the solution. You’ll need to scroll down a bit till you see “I have installed the GDR, but now I see duplicate commands on the menus in the IDE.”
1) Be sure to exit out of all the visual studio sessions
2) Open a command prompt
3) Within the command prompt navigate to %ProgramFiles%\Microsoft Visual Studio 9.0\DBPro\
Execute the following:
You will not see a confirmation message after running the above statement. To verify launch BIDS and click on Data. It should now look like the following.
When I first encountered this I thought to myself… must be a database or service or some sort, not being entirely sure of what to expect. I searched throughout the instance starting with the database engine and moved to the SSAS instance and was unable to find anything remotely named “flight recorder”. At this point I thought this error must have risen from an external call looking for a resource that no longer exists. Little did I realize that it is actually the SSAS log. It’s also been around sin SQL Server 2005.
If you need to determine if it is enabled then this is how you go about it.
Using SSMS connect Analysis Services for that instance. Once connected right-click on the instance and select “Properties” and in the name column just about the 13th row down you will see “Log \ Flight Recorder \ Enabled”
Let’s face it we have all spent countless hours developing and/or polishing up our sql scripts deep into the wee hours of the night to the point of near exhaustion. We rise early the next morning (or a few hours later in most cases), pour a cup of coffee and head over to our laptop to find that our system rebooted. Then reality sinks in and you suddenly realize that you didn’t save anything before calling it a night. Now you’re thinking “[enter swear phrase of choice here]“!
Typically when you re-open SQL Server Management Studio you’re prompted with a nice little screen that politely asks if you would like to recover the selected files or queries. Which is extremely helpful, but what happens when you don’t get that prompt, what then?
The answer is easy enough and may require you to change your folder options to show hidden files and folders. In the event you find yourself in a similar situation simply navigate to (assuming you are running Windows 7) C:\Users\”[your username goes here]“\AppData\Local\Temp\ and look for files similarly named like the ones illustrated below.
My problem started shortly after I switched the service account that powered the SQL Services. On the initial install I used my domain login during the install. Then I received the good old change password nag and changed my password three or four days later. Life went on just peachy until I had to reboot. So in order to avoid this again I decided to use a different domain account. Using SQL Server Configuration Manager I changed all the services to use the new domain account and began the process of starting each service leaving the agent as the last. To my surprise I received a nice little error:
The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.
At this point I thought a reboot might resolve this which it didn’t. So I decided to listen to the error message and search through the error logs. In the Windows Event Viewer I found the following:
Login failed for user ‘xxxxx\sqlservice’. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
After reading this I thought the SQL permissions were incorrect so I double checked those and they turned up fine. So I proceeded on. This time to the SQL error logs in the instance directory. In my case “C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV08R2\MSSQL\Log”.
I opened the ERRORLOG with notepad and began reviewing the contents and I found some interesting information. Essentially it stated:
Error: 18456, Severity: 14, State: 38.
Login failed for user ‘XXXXX\sqlservice’
Now I am thinking perhaps I mistyped the password which seemed like a viable action seeing the rest of the services were working fine. So I visit the Configuration Manager again and attempt to redo the password. For Schnitz and Giggles I decided to deliberately enter in the wrong password and surprisingly I received a nice error:
The specified network password is not correct.
Now I am puzzled. The password was right all along yet the previous error is saying that the login failed. WTF? To rule out if this is a Windows permissions or SQL Server permissions issue I added the service account to the local Administrators group and suddenly I was able to start the service. At the very least I now know that this is a Windows permission issue. Now I just need to figure out where this permissions issue resides.
I started to review the accounts and remembered during the installation that SQL Server creates Windows groups. So I hone in on the SQLServerMSSQLUser$XXXXXX$DEV08R2 group and find that the service account is not listed. So I add the service account to this group and kick off the Agent service and boom… it works! I tried changing the service account on a SQL Server 2005 instance and I noticed that the Windows group was updated so I am not sure why SQL Server 2008 R2 didn’t behave the same way.
The actual permissions are required within the instance directory. Here is where I found it.
And the required permissions are as illustrated.
Once you switch the service account make sure you take a look at the owner for the databases and jobs. In my case I wanted all the databases and jobs to have the owner of the sqlservice so I ran the following query to address the database owner.
SELECT name ,'USE [' + name + ']; EXEC sp_changedbowner ''xxxxxx\sqlservice''' 'cmd' ,SUSER_SNAME(owner_sid) 'owner' FROM sys.databases WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice') OR (SUSER_SNAME(owner_sid) IS NULL)
Then I copied the contents from the ‘cmd’ column and executed them to make the change.
Note: you cannot change the owner of the master, model, msdb and tempdb databases. If you attempt to change the owner of the system databases you will be greeted with an error.
To circumvent this you can add an additional condition to the WHERE clause as followed and this will skip the system databases.
SELECT name ,'USE [' + name + ']; EXEC sp_changedbowner ''xxxxxx\sqlservice''' 'cmd' ,SUSER_SNAME(owner_sid) 'owner' FROM sys.databases WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice') OR (SUSER_SNAME(owner_sid) IS NULL) AND (database_id NOT IN (1,2,3,4))
The stored procedure sp_changedbowner will be removed in a future version of SQL Server. Which means you need to learn how to use ALTER AUTHORIZATION. Which I picked up from Jes’s article Changing a SQL Server Database. Here is the revised script you would use to generate the ALTER AUTHORIZATION statement.
SELECT name ,SUSER_SNAME(owner_sid) 'owner' ,'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO [sa]' 'cmd' FROM sys.databases WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice') OR (SUSER_SNAME(owner_sid) IS NULL)
Lastly I ran the following query to generate the ‘cmd’ statement which changed the job owner for all the jobs I had set up. Keep in mind that if you have any backup jobs that are backing up to a network share you will need to update the share permissions and add the service account to permit writing to that location.
SELECT name ,'EXEC msdb..sp_update_job @job_name = ''' + name + ''', ''@owner_login_name = xxxxxx\sqlservice''' 'cmd' ,SUSER_SNAME(owner_sid) 'owner' FROM msdb..sysjobs WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice') OR (SUSER_SNAME(owner_sid) IS NULL)
I like to write my our procedures for the sheer fact that I like the control. Which means I had sprocs that cleared out the history logs retaining only the records within the last 90 days. I also had sprocs that performed system and user database backups which would backup to a network share and append my timestamp (i.e. master_YYYYMMDD_HHMM.bak) which were all executed by agent jobs. Depending on the need I would setup Full, differential and log backups; furthermore, my process would also purge old back up files.
SELECT '_' + CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS VARCHAR) + '_' + CAST(REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','') AS VARCHAR) + '.bak'
I am here to say I have given up my ways and have been walking a different path. I moved away from my custom methods and adopted Maintenance Plans. I use them for backing up the system and user databases if the system is not being serviced by a backup system as well as running DBCC CHECKDB against each database. I also use maintenance plans to purge the history logs that falls outside of a 90 day threshold and perform reorgs or rebuilds of my indexes. For a complete task list visit: Maintenance Tasks.
I am kidding, it’s hard to break away from writing my own procedures. Maintenance Plans are good if you’re getting started with SQL Server and really don’t understand the fundamentals of writing your own queries to accomplish the same outcome. I don’t know if this makes me a control freak or not. Oh wait I said that in the beginning of this post. Anyhow if you want to setup maintenance plans here is what you need to do.
One thing to know before we jump in is that you need to grant the SQL Agent Service account write access to the backup share in my case the Agent service is “sqlservice”. Typically you would use a domain account (i.e. domain\sqlservice) which you will need to grant Change access on the directory where you will be storing the backups (i.e. \\server\backups\).
If you are using the default file path which was set during the initial install of SQL Server then the path would be like <drive letter>:\..\MSSQL10_50.<instance name>\MSSQL\Backup\ and will be using the SQLServerMSSQLUser$<computer name>$<instance name> which has enough permissions.
Also you need know Maintenance Plans and the SQL Agent are not features you will find in Express editions. Well technically the SQL Agent is there, but it cannot be used/started in Express edition, so hopefully you have at the very least developer edition.
Now that I touched on a few reasons on why to use Maintenance Plans let’s kick our heels up and dig right in. You’ll need to be a sysadmin in order to create a maint plan so if you’re not then the following steps will be more informational than anything. If you have worked with SQL Server Integration Services then the Maintenance Plans design surface will look a bit familiar.
For this post we will be using two tasks: Backup Database and Maintenance Cleanup to schedule routine backups of the system database: (master, model and msdb). Once you run through this process creating another Maintenance Plan to address the user databases is extremely similar with literally one item to change. You’ll see what I am talking about in a moment. Let’s being…
Creating a Maintenance Plan for daily backups of System Databases
Step 1: Open up Management Studio, connect to your instance and expand Management
Step 2: Right click on Maintenance Plans and select New Maintenance Plan…
Step 3: Enter a specific name: (i.e. Daily_System_DB_Full_Backup) & click OK
If you are going to schedule a FULL backup let’s say on Sunday then the name of the Maintenance Plan would be along the lines of (i.e. Weekly_System_DB_Full_Backup). Then if you were going to incorporate daily differentials you might consider calling the Maintenance Plan: (i.e. Daily_System_DB_Diff_Backup). Perhaps you want to perform Full backups of the DBs daily and then perform Transaction Log backups hourly or every four hours, etc… you would want to make the names meaningful to the types of backups the Maintenance Plan is performing. Enough harping on that… I am beginning to hear crickets at his point.
Once you have clicked OK you arrive at the design surface where you will build your workflow process. The default is always Subplan_1 which you can leave or you can change the name. If your Maintenance Plan only utilizes a single subplan then the naming really doesn’t matter, but if you add additional subplans then you might want to consider naming them accordingly to better identify their purpose or intent as opposed to having Subplan_2, 3, etc… you get my drift.
To rename the subplan just double-click on the Subplan name.
Rename the Subplan and specify a description and simply click OK
Since I am going to use only one subplan for this post I am going to leave that name as Subplan_1.
Step 4: Drag the Back Up Database Task to the designer surface area
Now in the Toolbox pane to the left select and drag Back Up Database Task from within the Maintenance Plans Tasks to the designer surface area and release.
Step 5: Edit the task
Right click on the Back Up Database Task and select Edit…
Step 6: Perform a quick hat trick (hockey reference)
A) Click the Database(s) select list
B) Select System databases
C) Click OK
Step 7: Specify the backup path in the Folder: field and click OK
If the Folder: is already populated using a local file system path then you can leave it as is. It will work as is. I am using a network share just as an example to illustrate that you would normally want to backup to a network share which should be routinely backed up via an enterprise backup system.
Step 8: Drag the Maintenance Cleanup Task to the designer surface area and release. The Maintenance Cleanup Task will purge all files with a specific file extension using a given date specification.
Step 9: Set the workflow
A) Click the Back Up Database Task
B) Drag the Green Arrow to the Maintenance Cleanup Task and release
This means when the backup task completes successfully to proceed and execute the Maintenance Cleanup Task.
Step 10: Edit the Maintenance Cleanup Task
This is similar to Step 5 except you are right clicking on Maintenance Cleanup Task instead and selcting Edit…
Step 11: Four point play…
A) Specify the backup path in the Folder: field
B) Specify the extension bak in the File extention field
C) Specify a value unit of time in numbers
D) Specify a value unit of time for the time frame then click OK
Step 12: Scheduling the Maintenance Plan
Click the Calendar Icon just above the Maintenance Plan name
Step 13: Secondary hat trick…
A) I like to remove the .Subplan_1 from the Maintenance Plan name (optional)
B) Click the occurs: select list and choose Daily
C) Set the field occurs once at: to when you want this to kick off and click OK
Step 14: Close & save the Maintenance Plan
Click Yes to confirm
Done! Well at least with creating the Maint Plan. We just need to test it.
Step 15: Start the newly created SQL Job
Right click on Daily_System_Database_Full_Backup.Subplan_1 and select Start Job at Step…
If the process fails make sure your service account has Change permissions on the network share. If you are running the SQL Agent Service under the Network Service account then set the permissions as illustrated. This also applies to what ever account you are running the Agent Service under like a domain account and such.
Otherwise if everything is configured correctly you will see GREEN!
And the databases will have been backed up as such…
You should read Brad McGehee’s eBook: Brad’s Sure Guide to SQL Server Maintenance Plans to get a better understanding of some of the gotchas you need to be aware about in regards to Maintenance Plans.
I got my work laptop environment finally squared away and today was the first attempt to update my instances of SQL Server 2008 R2 Developer and Express to CU3. Yes I know the post title say CU6, trust me I’ll get to that in a moment. Originally I was going to update to CU3 reason being because it addressed an issue I was experiencing with creating, adding or editing SQL Agent jobs in SSMS. Matter of fact here is the Microsoft article address: http://support.microsoft.com/kb/2315727
Here is the specific error I received when I attempted to edit an agent job.
Related Connect feedback
So I downloaded CU3 from Microsoft.
- Cumulative Update package 1 for SQL Server 2008 R2
- Cumulative Update package 2 for SQL Server 2008 R2
- Cumulative Update package 3 for SQL Server 2008 R2
- Cumulative Update package 4 for SQL Server 2008 R2
- Cumulative Update package 5 for SQL Server 2008 R2
- Cumulative Update package 6 for SQL Server 2008 R2
When I tried to apply CU3 I encountered an error which basically suggested that I had to uninstall SQL Native Client because it was not installed via sqlcnli.msi. Here is the url that the error pointed me to.
Needless to say I decided to reboot hoping that would some how, some way correct the issue auto-magically. WRONG! So I decided (executive decision) to push forward and apply CU6. See I told you I would eventually get to it! Well I downloaded CU6 and attempted to install and ran into another error.
Here is the full Summary.txt
Well again I tried a reboot and that did not work, so I surfed around and then stumbled on a post by Jonathan Kehayias (@SQLSarg | blog) titled: SQL Server 2008 failed installation blocks Service Pack 1 (SP1) installation. He essentially performed a repair which resolved his issue so I felt inclined to do the same and BOOM… CU6 applied oh so nicely. I cannot say if this will help you, but it helped me. Both my Dev and Express editions are at CU6 so I am a happy camper.
On a recent project assignment I was tasked to comb through a client database and target a specific field that resided within a single table. This field was powered by a free form text field on a .NET web application. Instead of separating the required bits of data into their own fields respectively everything was lumped into one NVARCHAR field. Definitely not an ideal situation. Nonetheless the task needed to be done. I spoke with the our Business Analyst and obtained the requirements.
So I fired up SSMS 2008R2 and started to formulate the query. That was my first mistake! I regressed back to my rookie days without really thinking it through and instead of really researching in-depth I started to ask for assistance (twitter hash tag: #sqlhelp to the rescue). Don’t get me wrong… asking for help isn’t terrible, it’s just that thinking it trough first really makes a significant difference. To make a long story short let me tell you what steps I took to make this move along smoother.
Step 1: Always review the data first
If I would have carefully inspected the data first I would have noticed the different ways the end users were entering the data. Then I could have easily constructed special conditions to target those specific instances. For example here is a short list of what the data kind of looked like:
- Select on plan/Spec 283
- Side Menu/Spec 118 Sht 30
- Side Menu/Spec 474 Sht C1
- Select on plan/Spec 283 Sheet 17
- Select on Plan/Spec 278 Sht 44 Dtl A
- Select on Plan/Spec 389 Sht 4 Bypass Piping
- Select on Plan/Spec 408.1 Sht M-2
- Site Piping Menu/Spec 408.1 Sht C-1 Plan
- Surge Tank Menu/Spec 408.1 Sht M-4 Dtl B
- Select on plan/Sheet 8 Detail C
- Select from Side Menu/Sheet 7 Detail C
- Menu from Discharge Relief Valve/Sheet 8 Detail F
- Menu for Valve/Sheet 8 Detail A
- Select on plan/Sheet 12 Detail C
- Menu for Pressure Transmitter Cabinet/Sheet 20 Detail B
- Menu for Suction Surge Tank 2/Sheet 15 Detail V
My task was to find the instance of SPEC, SHEET and SHT and grab the following numbers or char. As you can see there is only one instance of each one so that made it a bit easier.
Step 2: Identify the patterns
I took each keyword and began analyzing the data carefully:
For SPEC the following three were always numeric (e.g. Spec 283) and there were a small handful the had three numeric followed by a period and another numeric digit (e.g Spec 408.1) so my options are grab the following four char or six to the right.
For SHEET it was pretty easy. Always trailing SHEET was a numeric digit but the only difference was either a single digit (e.g. Sheet 2) or double digits (e.g. Sheet 12). So that means I can grab the following three char to the right and trim the left and right to satisfy this requirement.
The SHT on the other hand was a bit more difficult since it varied the most. This either had a trailing single numeric digit (e.g. Sht 2), double numeric digit (e.g. Sht 13) or mixed with alpha-numeric chars like: (e.g. Sht M-1 or Sht P8 or Sht P20).
Now that I can clearly see what patterns to look for I can begin to construct my query. Nothing like a little Function action using CHARINDEX mixed with a little dose of PATINDEX to assist in situations likes this. Of course there are many ways to approach this, but this worked for me and satisfied the requirements.
Step 3: Approach each requirement one at a time
This permitted me to concentrate on a specific goal until I was able to correctly return the desired output. Naturally I felt that attacking the easiest ones first would be best, but after reading the book, “Eat that Frog!” I learned to eat the biggest and ugliest frog first as it provides the greatest sense of accomplishment. So I started swinging at SHT. Then once that was crystal clear I moved onto SPEC and SHEET. I must mention that a fellow SQL buff by the name of Aaron Bertrand [ Twitter | Blog ] actually provided a very cool script that tackled the SPEC and covered all the basis. So Cheers to him!
Step 4: Piece everything together and test again
Once everything is working accordingly piece everything together and test it again. I cannot emphasize this enough. Q/A is necessary and must be done until everything comes out the way we need it to. Data is critical to any organization and we as data professionals know it better than anyone else. So test, test and retest. Everything worked out as needed and the results are positive.
Off the subject
If I didn’t mention this before #sqlhelp is extremely useful and there are many people that are extremely talented that are willing to lend you a helping hand. It’s a community thing and I love it! If twitter ain’t your thing that’s fine no biggie; however, if you are looking into it… do it! You won’t be sorry.