Extracting Text from Access Fields into New Fields

Extracting Text from Access Fields into New Fields

All too often, people will put multiple data sets into one field in a table. This could hinder querying or reporting on the data or if you were to convert databases down the road.

I encountered data like this in Access and needed to extract it:

A1234567 -> Okay!

A1234567/B1234567

and even:

A1234567/B1234567/C1234567

What is the formula to extract this?

We want to break this into three fields in an Access Query.

To grab the first set regardless of how many entries:

Field1: IIf(Mid([FIELD],9,1)='/',Left([FIELD],8),[FIELD])

To extract the second field:

Field2: IIf(Mid([FIELD],9,1)='/' And Mid([FIELD],18,1)='/',Mid([FIELD],10,8),IIf(Mid([FIELD],9,1)='/' And Mid([FIELD],18,1)<>'/',Right([FIELD],8)," "))

Finally, the third field:

Field3: IIf(Mid([FIELD],9,1)='/' And Mid([FIELD],18,1)='/',Right([FIELD],8)," ")

So that was the easy one. Why? This was easy because all the data was the same length. What would you do about cases where you encounter variable data?

CompanyA

CompanyA/CorpB

CorpB/CompanyA

What is the formula to extract this?

Again, we want to break this into two fields in an Access Query.

To grab the first set regardless of how many entries:

Field1: IIf(([FIELD]) Like "*/*",Left([FIELD],InStr(1,[FIELD],"/")-1),[FIELD])

For the second field:

Field2: IIf(([FIELD]) Like "*/*",Right(Trim([FIELD]),Len(Trim([FIELD]))-InStr(1,[FIELD],"/")),"")

If you were converting from Access to SQL Server, for example, I suggest doing the work before converting. Access offers good querying tools and it is a good idea to take advantage of them. Don’t carry forward the mistakes of the past if you don’t have to!

SSRS E-Mail Subscription Setup

One of the best parts of a report repository like SQL Server Reporting Services or Business Objects Enterprise is the ability of the system to push out reports to users. One of the most popular ways is a subscription through the e-mail server. However, undoubtedly convenient for the users, the administrator can sometimes have a difficult time getting it setup. This was a case during a recent setup of a SSRS environment where the task was to setup e-mail.
On the SSRS server, I was using a service account in the local domain and had setup the SMTP server based off how I had Business Objects setup. By the way, what you put in for the sender address does not seem to matter and can be what your users want to see.

After everything was setup and I tried a test subscription, I got an error message in the status: “Failure sending mail: The report server has encountered a configuration error. Mail will not be resent.”




Without any detail in the above message, I went to the log file located on the RS server. You can find it at: C:\Program Files\Microsoft SQL Server\(RS Version).(Instance)\Reporting Services\LogFiles.

The meaningful message I got out of this was:
library!WindowsService_8!7c0!01/21/2011-15:00:03:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service account doesn't have rights to check domain user SIDs., Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;

The common solution to this issue seems to be adding the service account to Windows Authorization Access Group (http://support.microsoft.com/Default.aspx?kbid=842423).

Access Form Data Updates

When using a form in Microsoft Access, you can typically have a gateway directly to your database. Once you make an update, it is updated in the database, sometimes without having to press a save button or the like. So what happens if you make a mistake and add, delete or mangle what you have in a field? You might go to the next database record and not know what you did. Worse, it can create a problem for your users or others who rely on your data.

The best solution is to add a confirmation yes or no type box that presents itself to you. Below is a good example of code that will help keep you save and ask you before the changes are committed. Add this code by right clicking in the field in design mode, then clicking over to event and choose "Before Update."

   If MsgBox("You have made changes to this record." _
       & vbCrLf & vbCrLf & "Do you want to save the changes?" _
       , vbYesNo, "Changes Made") = vbYes Then
           DoCmd.Save
       Else
           Me.Undo
   End If

Now you are set, but at a client site recently, they asked that the message box only pop up if the field was not null before. I agreed because having to confirm on every field could slow you down. Here is the code if you only want to display if you updated a non-null or even an empty field. Otherwise, it will come up every time. Add the code here, based on the previous example:

    If IsNull([Data].OldValue) Then
        DoCmd.Save
    Else
        If MsgBox("Changes have been made to this record." _
            & vbCrLf & vbCrLf & "Do you want to save these changes?" _
            , vbYesNo, "Changes Made...") = vbYes Then
                DoCmd.Save
        Else
            Me.Undo
        End If
    End If

Date and Time Conversions from int using dbo.sysjobhistory

I was charged with developing a T-SQL query to list backup job histories, found in dbo.sysjobs and dbo.sysjobhistory. One of the requirements was to have the end date of the job. In the dbo.sysjobhistories table, there is an integer start date (run_date), time (run_time) and duration (run_duration) field, but no end date or end time data. I came across some code to create those end date and end time fields, plus convert them and the others into a usable datetime field.

Anyway, if you need a way to track backup job histories or even make it into a view, here is some helpful code:

SELECT
--Server
CONVERT(char(100), SERVERPROPERTY('Servername')) AS hostname,
--Instance
@@SERVICENAME As instance,

CONVERT(DATETIME, RTRIM(run_date)) +
((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS startdatetime,
--EndDate
CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS enddatetime,
--Job Name
sj.name as "job name",
--Error
CASE WHEN run_status = '0' THEN message
WHEN run_status = '0' THEN ''
END AS "error message",
--Duration
LEFT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) as duration,
--Status
CASE WHEN run_status = '1' THEN 'Success'
WHEN run_status = '0' THEN 'Failure'
END AS "status"

FROM dbo.sysjobs sj

INNER JOIN dbo.sysjobhistory sh on sh.job_id = sj.job_id

WHERE sh.step_id = '1'
--Backups Only
AND sj.name like '%Backup%'
--Order by Start Time Descending
ORDER BY CONVERT(DATETIME, RTRIM(run_date)) +
((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) DESC

SQL Server Reporting Services (SSRS) & .NET

When using Reporting Services on the Server for the first time, you might find that reports won’t run in Visual Studio or on the web.

The error you get is: “Execution of user code in the .NET Framework is disabled. Enable clr enabled configuration option.”

By default .NET Framework is disabled in SQL2005 and SQL2008.

To enable it in 2005:
Explore "SQL Server 2005/Configuration Tools/Surface Area Configuration" in your Start menu.
2. Select "Surface Area Configuration for Features"
3. For the "CLR Integration" option, activate it and save.

To enable it in 2008:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

SSIS and an Oracle OLE DB Connection

Here is an interesting situation I came across working with SSIS and Oracle.

I have a server with operating system Windows 2008 Server Standard Edition and SQL Server 2005(x64 bit) installed on it. I accidently installed the Oracle 10.2 client twice. I promptly removed the second Oracle client and verified I could still connect to Oracle databases in SQLPlus.

I have a SQL Server Integration Services (SSIS) solution that extracts from SQL Server and sends to an Oracle database. After my client install misadventure, when checking the Oracle Provider for OLE DB connection, I noticed something was wrong.

When trying to test connection to get an error, I got:
"oraoledb.oracle.1 provider is not registered"

Yikes, this was working before. How can I fix this and save the 13 packages that will run tonight? Surely, they will all error!

When trying to run the package anyway, I would get this error:
Code: 0xC020801C Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle Provider for OLE DB" failed with error code 0xC0202009

After looking through a myriad of other posts, it appears that the Oracle OraOLEDB10.dll file became unregistered at some point.

The fix:
START/Run then,
regsvr32 C:\oracle\product\10.2.0\client_1\BIN\OraOLEDB10.dll

Yes! The connection worked in the packages and the jobs all ran to success!