SSIS Binary Code is not found - You need to invetigate by youself

Recently when one of my collegues worked in a SSIS script task, she got a "binary code is not found" error. What she did is

• Open the VSTA IDE
• Add a web reference to the reporting service
• Build the code, and close the IDE
• While she tried to close the script window, an error window "binary code is not found" popped up She went back and checked her code, nothing was added yet except for the web reference
• She set the "delay verification" to true to remove the red cross, but she executed the script task, the task failed with identical error message.

When we do a google search, basically you can find hints like:

• The code is wrong, check your syntax
• Set "delay verification" to true if you are using SSIS2008 and later versions
• Set "PrecompileScriptIntoBinaryCode" to true if you are using SSIS2005

Obviously none of above is applicable in her case. So I grab the package and run on my PC, guess what? Nothing is wrong on my PC. So problem must occurs on her machine.

Now look at MSDN at https://msdn.microsoft.com/en-us/library/ms152787.aspx, it says "to develop Reporting Services applications based on the Web service...using Microsoft Visual Studio and the Microsoft .NET Framework SDK"

Now it looks much clear. I downloaded .Net Framework SDK and installed on here machine. Now she can build the script task with web service.

So considering the case I posted before DBCC Error, searching online is good, but you still need to do your own task. :D

Run Subscription from T-SQL

As a multi client facing company, we scheduled quite a lot reporting service subscriptions at the backend. Some of them are data-driven subscriptions, some of them are time-driven subscriptions, and some are running from SSIS, a custom control flow task to generate batch reports by calling ReportExecution2005 web service, for example, customer fulfilment form.

Occasionally we will receive the request to re-run the subscription. Instead of navigating to reporting service site and change the subscription setting manually, below script will shot start subscription immediately:

 use ReportServer  
 go  
 DECLARE @JobID varchar(50)  
  SELECT @JobID = S.ScheduleID   
  FROM ReportSchedule RS JOIN Schedule S ON RS.ScheduleID = S.ScheduleID  
    JOIN Subscriptions SUB ON RS.SubscriptionID = SUB.SubscriptionID  
    JOIN [Catalog] C ON RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID  
  WHERE C.Name LIKE '%agl%' --subscription report name comes here  
  EXEC MSDB..sp_start_job @job_name = @JobID  

SQL Server Spatial - Distance Test

This is just a quick post about using spatial data in business reporting.

Quite often distance to site is a very important element when client is running some types of appointment campaign or service campaign. Below is a short script can be used to do the distance test:

 declare @lat float = 145.41, @lng float = -38.02  
 declare @geo geography   
 select @geo = geography::STGeomFromText('point(' + Cast(@lat as varchar(20)) + ' ' + cast(@lng as varchar(20)) + ')', 4326)  
 select top 10 geom.MakeValid() as Geom  
      , geography::STGeomFromText(geom.MakeValid().AsTextZM(), 4326).STDistance(@geo) as Distance  
      , POA_NAME as Postcode  
 from POA  
 order by 2   

Here is some explanation:

Firstly, POA is a table from Australia Bureau of Statistics (ABS). You can find a full explanation at
http://www.abs.gov.au/ausstats/abs@.nsf/mf/1259.0.30.001?OpenDocument. To get shape file imported into the database, you can use Shape2Sql, which you can find at http://www.sharpgis.net/page/shape2sql

Secondly, we need to know latitude and longitude of a particular place, for instance, a business site or a clinic. I found this site is quite helpful, as it allows you to do batch geocode: http://www.findlatitudeandlongitude.com/batch-geocode/

Thirdly, you may find Paf Postcode file from Australia Post is quite helpful. But unfortunately starts from 2014, this resource is not free to individual user anymore, though they provides a pdf formated postcode file.

So after you get your resources sorted out, running above query will give you some results such as top 10 postcodes to a business site, and with some extra works in SSRS and T-Sql, we can make the result more clear to the end user.

DBCC CheckDB('Master') Error - Rational Diagnostic

This is a error when I deploy was working on SQL Server migration project.

Actually I would like to log the whole migration project, start from the planning, to end of migration, with the exception handlings. Reason for that is because, if you search online, you will find tons of articles talking about best practice for "upgrade", however you couldn't find too many resources about best practice of "migration". The SQL Server migration task, is to migrate databases from our current SQL Server 2008 R2 Standard Edition database server, to SQL Server 2012 Entreprise Edition on another VM server.

But today I would like to talk about the DBCC master error after I built the 2012 instance.

Long in short, I grabbed the installation disc, followed set up wizard, inputted administration account and service account, and assigned the file location. Everything looked good, and it went smooth.

After installation, I restarted the server, opened the SSMS, and as the most common task, did a DBCC check and then did the backup. Then I got below error message

 Msg 8928, Level 16, State 6, Line 1  
 Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:382) could not be processed. See other errors for details.  
 CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.  
 Msg 8939, Level 16, State 98, Line 1  
 Table error: Object ID 44, index ID 1, partition ID 281474979594240, alloc unit ID 281474979594240 (type In-row data), page (1:382). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -1.  
 Msg 8906, Level 16, State 1, Line 1  
 Page (1:382) in database ID 1 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.  
 Msg 8906, Level 16, State 1, Line 1  
 Page (1:502) in database ID 1 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.  
 Msg 2575, Level 16, State 1, Line 1  
 The Index Allocation Map (IAM) page (1:382) is pointed to by the next pointer of IAM page (0:0) in object ID 44, index ID 1, partition ID 281474979594240, alloc unit ID 281474979594240 (type In-row data), but it was not detected in the scan.  
 Msg 8939, Level 16, State 98, Line 1  
 Table error: Object ID 44, index ID 1, partition ID 281474979594240, alloc unit ID 281474979594240 (type In-row data), page (1:382). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -1.  
 Msg 7965, Level 16, State 2, Line 1  
 Table error: Could not check object ID 44, index ID 1, partition ID 281474979594240, alloc unit ID 281474979594240 (type In-row data) due to invalid allocation (IAM) page(s).  
 CHECKDB found 4 allocation errors and 2 consistency errors in table 'sys.sysnsobjs' (object ID 44).  
 CHECKDB found 5 allocation errors and 2 consistency errors in database 'master'.  
 repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (master).  

OMG, if you do a quick google search, you will get tons of troubleshooting methods and suggestions. But probably 9 out of 10 will tell you, your disk has problem.

So let's think about this problem: They are on virtual environment, if one of them has disk fragment problem, eventually all virtual machines should get similar problems, so it should be the case.

So to test my assumption, I uninstalled SQL Server 2012 and then did a reinstall. I re-ran the DBCC command and get the identical error. So think about it, in a virtual environment, install a software  twice and system allocates the same sector to your contents, it doesnt look like a very high chance, isn't it?

So I started to look at this specific object, it is sysnsobjs from the Object_Name function. But when I did a google search, I couldn't find similar errors.

When I look at the MSDN I found the error content actually is related to some schema definition. So is it because of SP/schema change? as I know the installation disc was downloaded in 2012.

SO I downloaded the new installation disc, which was released in 2014 with SP2. Now time to test:

To repeat the error, I built a VM and have two CDs on hand:

• the old CD, 2012 released in 2012
• the new CD, 2012 released in 2014 with SP2

I installed two instances on the VM:

• DBCC CheckDB on the first instance returns identical error.
• DBCC CheckDB on the 2nd instance passed successfully

• Then I tried to apply SP2 on the first instance, error is still there
• Then I tried to use new CD to repair the first instance, error is still there
• Then I tried to repair master database via single user mode, the error is gone

 

So what is the conclusion? While from my experience, troubleshooting sometimes is fun, as you need to use your imagination. :D