Archive
List All Analysis Services Databases
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>
Results
Get Last Backup Date
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.
Duplicate Commands within the Data Menu of BIDS 2008
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.”
Resolution Steps
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:
DBProRepair.exe RemoveDBPro2008
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.
Flight Recorder… WTH?
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”
Query LinkedServer and Provider Information
First off I modified the (master.dbo.sp_MSset_oledb_prop) stored procedure just a bit and incorporated my own query to get the results I needed. Which you can find from lines 78 to 104 of the query at the bottom of this post. I cannot take all the credit, I received a lot of help from the sql community. Thanks again all, I appreciate it. Also note I am using SQL Server 2008 R2 Developer Edition.
You’ll notice I changed most of the temp tables to table variables with the exception of the #paramlist. I suspect the table variable loses it declaration when using it within a cursor (not 100% positive on that). The oddity was that the results were still returned but I received the “Msg 1087, Level 15, State 2, Line 1 Must declare the table variable @paramlist” error repeatedly. So I changed it back to a temp table and now all is well.
The Thread
The point of this was to return linked server information as well as the provider properties, specifically Allow Inprocess. I blurred out some details to protect the innocent.
The Results
The Query
SET NOCOUNT ON
DECLARE @provider_name AS SYSNAME, @property_name AS SYSNAME, @property_value AS BIT
DECLARE @providers TABLE
(
name NVARCHAR(100) PRIMARY KEY CLUSTERED
,guid NVARCHAR(100) NULL
,description NVARCHAR(100) NULL
)
INSERT INTO @providers EXEC sys.sp_enum_oledb_providers
CREATE TABLE #paramlist
(
property_name SYSNAME PRIMARY KEY CLUSTERED
,property_value INT
)
INSERT INTO #paramlist (property_name) VALUES ('AllowInProcess')
--INSERT INTO #paramlist (property_name) VALUES ('DisallowAdHocAccess')
--INSERT INTO #paramlist (property_name) VALUES ('DynamicParameters')
--INSERT INTO #paramlist (property_name) VALUES ('IndexAsAccessPath')
--INSERT INTO #paramlist (property_name) VALUES ('LevelZeroOnly')
--INSERT INTO #paramlist (property_name) VALUES ('NestedQueries')
--INSERT INTO #paramlist (property_name) VALUES ('NonTransactedUpdates')
--INSERT INTO #paramlist (property_name) VALUES ('SqlServerLIKE')
DECLARE @oledbprop TABLE
(
provider_name SYSNAME PRIMARY KEY CLUSTERED
,allow_in_process BIT
,disallow_adhoc_access BIT
,dynamic_parameters BIT
,index_as_access_path BIT
,level_zero_only BIT
,nested_queries BIT
,non_transacted_updates BIT
,sql_server_like BIT
)
DECLARE @regpath NVARCHAR(512)
SET @regpath = N'SOFTWARE\Microsoft\MSSQLServer\Providers\' + @provider_name
DECLARE @value int, @sql NVARCHAR(300), @param NVARCHAR(300)
SET @sql = 'EXEC sys.xp_instance_regread N''HKEY_LOCAL_MACHINE'', @regpath, @property_name, @value OUTPUT, @no_output = N''no_output'' ' +
'update #paramlist SET property_value = IsNull(@value, 0) where property_name = @property_name'
SET @param = '@regpath NVARCHAR(512), @property_name SYSNAME, @value int'
DECLARE c_prov CURSOR LOCAL FAST_FORWARD
FOR ( SELECT name FROM @providers )
OPEN c_prov
FETCH NEXT FROM c_prov into @provider_name
WHILE @@fetch_status = 0
BEGIN
SET @regpath = N'SOFTWARE\Microsoft\MSSQLServer\Providers\' + @provider_name
DECLARE c CURSOR LOCAL FAST_FORWARD
FOR ( SELECT property_name FROM #paramlist )
OPEN c
FETCH NEXT FROM c into @property_name
WHILE @@fetch_status = 0
BEGIN
EXEC sp_executesql @sql, @param, @regpath, @property_name, @value
FETCH NEXT FROM c into @property_name
END
CLOSE c
DEALLOCATE c
INSERT @oledbprop (provider_name, allow_in_process , disallow_adhoc_access , dynamic_parameters , index_as_access_path , level_zero_only , nested_queries , non_transacted_updates , sql_server_like)
SELECT @provider_name, AllowInProcess, DisallowAdHocAccess, DynamicParameters, IndexAsAccessPath, LevelZeroOnly, NestedQueries, NonTransactedUpdates, SqlServerLIKE
FROM #paramlist pivot ( max(property_value) FOR property_name in ( [AllowInProcess], [DisallowAdHocAccess], [DynamicParameters], [IndexAsAccessPath], [LevelZeroOnly], [NestedQueries], [NonTransactedUpdates], [SqlServerLIKE] ) ) as p
FETCH NEXT FROM c_prov INTO @provider_name
END
CLOSE c_prov
DEALLOCATE c_prov
/* RETURN RESULTS */
SELECT
s.name 'linked server'
/* ,s.data_source */
,s.product
,CASE
WHEN s.catalog IS NULL THEN QUOTENAME(s.data_source)
ELSE QUOTENAME(s.data_source) + '.' + QUOTENAME(s.catalog)
END 'data source'
,s.provider
,p.[description]
,CASE
WHEN o.allow_in_process = 0 THEN 'No'
ELSE 'Yes'
END 'allow inprocess'
,CASE
WHEN s.is_rpc_out_enabled = 0 THEN 'No'
ELSE 'Yes'
END 'rpc out'
,l.remote_name
,s.modify_date
FROM master.sys.servers s
JOIN master.sys.linked_logins l ON s.server_id = l.server_id
JOIN @oledbprop o ON s.provider = o.provider_name
JOIN @providers p ON o.provider_name = p.name
WHERE (s.name != @@SERVERNAME)
ORDER BY s.name
/* HOUSE CLEANING */
DROP TABLE #paramlist
Recovering unsaved or modified scripts
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]“!
Don’t panic…
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.
SQL Agent Won’t Start After Switching the Service Account
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.
Side Note
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)
Goodbye Stored Procedures… Hello Maintenance Plans
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
Success!
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…
Amended 2011-05-10
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.
Update to SQL Server 2008 R2 CU6
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.
Think and review before diving in head first
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:
SPEC
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.
SHEET
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.
SHT
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.
Flickr Photos
|
