Tuesday, November 19, 2013

Simple Script to return Table from CSV in Oracle

That's it...in T-SQL a 'select ... where someval...in @tablevar' can be over int or string. But in Oracle the IN will only work over number values, thus we must cast TO_NUMBER. That was fun =).

SELECT TO_NUMBER(column_value) FROM XMLTABLE(<csv_string>)

Migrate from SQL Server to Oracle in .NET using Devart

Steps to migrate from SQL Server to Oracle for Shuts Project.

1.      Download and install Oracle Database 11g – Personal Edition – this edition offers the full feature set of Enterprise Edition and is targeted for development/staging.

2.      Download and install JDK 7.

3.      Upgrade the SQL Developer from 2.x to

4.      Launch SQL Developer and use the Migration workbench to capture an external database.

a.      Click tools -> migration -> Create database capture scripts

5.      Copy the capture scripts to the target server. Execute OMWB_OFFLINE_CAPTURE.BAT and pass in parameters for username/password and target db.

rem  %1 DBA login id

rem  %2 password

rem  %3 database name

rem  %4 database server name

6.      Copy output of capture Oracle server.

7.      Create a new oracle database ‘orclshuts’ using the Data Configuration Assistant.

8.      Create a new oracle database ‘repo’ using the Data Configuration Assistant.

9.      Using SQL Developer login to the newly created database

a.      Right click on the new connection

b.      Expand ‘Migration Repository’

c.       Click ‘Associate Migration Repository’ and complete the wizard

10.   Using the migration workbench

a.      Click tools -> Migration -> Migrate…

b.      Click Next

c.       For repository set the connection to your new ‘repo’ database.

d.      Click Next

e.      Set the project name and output DIR (e.g. d:\migratedb) for scripts.

f.       Click Next

g.      Select source mode as ‘offline’ then browse to output your captured output folder and select the ‘sqlserver2008’.ocp file.

h.      Click Next

i.        Select the database to migrate from the available databases list

j.        Click Next

k.      On the conversion options - Click Next

l.        On the SQL objects to translate – Click Next

m.    Set your target as the ‘dbname' database, click Advanced and under Object Types check ‘Select All’ to include stored procs, functions and triggers -(this will also capture sequences).

n.      Click Next

o.      For move data, set source to ‘repo’ and target to ‘dbname'.

p.      Click Next

q.      Click Finish.

11.   In SQL Developer open your output DIR and select the dropallschemas.sql file, ensure that SQL Developer is running on your ‘dbname' database and execute script.

12.   In SQL Developer open your output DIR and select the master.sql file, ensure that the SQL Developer is running on your ‘orclshuts’ database and execute script. Provider your new user passwords as prompted by the script (should create a user named ‘dbo_MaintenanceShuts’).

13.   Install DevArt DotConnect for Oracle.

14.   Launch Visual Studio and add a new DevArt Entity Model.

15.   Right Click in an empty space within the model and then click on ‘Generate Model from Database’.

16.   On the connection details utilize the new 'dbo_schemaname’ user and use the password you used during execution of the master.sql script. Ensure that the ‘dbname' database is targeted.

17.   Update the Entity Container Name and Namespace if needed.

18.   Update the entity names in the Entity Framework Model to camel casing, instead of full caps as generated from the Oracle schema.

Some gotchas during migration.

The Oracle SQL Developer requires JDK 7.0

The Oracle SQL Developer migration workbench that comes with 11g is not able to access SQL 2008 data and is limited to 2005. So an upgrade to SQL Developer 4.0 was necessary in order to generate the Capture scripts correctly.

The ORM (edml) for entity framework generated by DotConnect generates two distinct namespaces, RTIO.Shuts.Data (the business entities) and the RTIO.Shuts.Data.Store (sql map). Both need to be maintained within the solution and changes to one will inevitably impact the other side.

*The Store and Data are two different things available in designer. After fixing the method call names and adjusting to the new addobject deleteobject functions. I executed the application to find that only one object was being inserted. Sequences were created during migration and direct insertion worked. Each primary key (identity) in both the ORM entity classes and the table (still in ORM designer) must be updated to use the Stored Generation Pattern = “Identity” – then all was well.

*Also all stored procs needed to be right clicked on at the Store and then click on “Create Method” in order to generate the method call and return type in the dbcontext object.

*When using SQL Developer ensure that any worksheet you use – once you are done, to hit commit changes. As all SQL executions are run in a transaction and do not push to the database until the commit is triggered.



SharePoint Set Locale Region on All Web Applications and Site Collections

A quick script works on both SharePoint 2010 and 2013. Make sure to update en-AU to the target locale you are after =).


$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
$websvcs = $farm.Services | ? -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]}
$webapps = @()
foreach ($websvc in $websvcs)
$NewLocale = "en-AU"
foreach ($webApp in $websvc.WebApplications)
foreach ($site in $webApp.Sites)
Write-Host "Updating Site -" $site -ForegroundColor "Green"
$Webs = $site.AllWebs
ForEach ($Web In $Webs)
   If ($Web.locale -ne $NewLocale)
      Write-Host $Web.title "- " -NoNewLine; Write-Host "changing from" $Web.locale "to" $NewLocale -ForegroundColor "Green"
      $Web.Locale = $NewLocale
   Else { Write-Host $Web.title "- " -NoNewLine; Write-Host "already set to" $NewLocale -ForegroundColor "Blue"

Sunday, October 13, 2013

SharePoint 2013 SSRS Integrated Mode Installation Issues

Installing SPS 2013 isn't a very smooth exercise..as a matter of fact neither was 2010 in the early days. But that said, it isn't too difficult to get by using PowerShell to correct or instantiate missing service applications. I've found that SSRS integration is a major pain in the butt. PowerPivot will install fine, with almost any edition and has an advanced configuration tool that report's accurately what is missing. SSRS is just a shot in the dark..it may not work and the error if you get one will be obscure. There were enough got-ya's that writing this post became a must.

This post led me to create this article: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b4d69aa-96a5-4787-b44d-ee473b667ce9/reportserver-not-showing-up-in-iis7

The SQL Server 2012 SP1 installation media is broken and only installs RTM http://support.microsoft.com/kb/2783963.

You will need to install the SharePoint Reporting Services add-in for Service Pack 1 from Microsoft and apply the SQL Server 2012 SP1 on top of the SQL installation in order to correct the version mismatch. SP1 is required to run 2012 in integrated mode with SharePoint 2013.

For Development we used SQL 2012 Developer Edition and need to remember to use SQL 2012 Developer Edition media to setup SSRS.

For Prod we used SQL 2012 Enterprise Edition and needed to remember to use SQL 2012 Enterprise Edition media to setup SSRS otherwise you will receive the following error:

The feature: "The Database Engine instance you selected is not valid for this edition of Reporting Services. The Database Engine does not meet edition requirements for report data sources or the report server database. " is not supported in this edition of Reporting Services.

Also all editions must match up - best place to check is under Control Panel - Add/Remove Programs and check the version numbers match up. But in my case, we needed SQL Server + SP1 + CU6, three separate installers needed to be applied to the SQL Server and BI Server.

Using BI edition (incl SP1) did not work at all...as of Nov-2013 this media has issues, well the version from MSDN anyways.

Once installed you can also install the SSRS add-in from the media should be fine as long as it is upgraded otherwise you need to make sure that the add-in is also the SP1 Version....again emphasizing that the SQL Box = SQL Media (DBEngine) + SP1 + CU6 and the BI Box = SQL Media (SSRS) + SP1 + CU6.

Once you are done with this you can create the SSRS service application...a simple PowerShell script to do this:

# Create service application and service application proxy
$appPool = Get-SPServiceApplicationPool “SSRS AppPool”
$serviceApp = New-SPRSServiceApplication “SSRS Service Application” –ApplicationPool $appPool
$serviceAppProxy = New-SPRSServiceApplicationProxy –Name “SSRS Service Application Proxy” –ServiceApplication $serviceApp

# Add service application proxy to default proxy group.  Any web application that uses the default proxy group will now be
# able to use this service application.
Get-SPServiceApplicationProxyGroup –default | Add-SPServiceApplicationProxyGroupMember –Member $serviceAppProxy

# Grant application pool account access to the port 80 or https for port 443 web application’s content database.
$webApp = Get-SPWebApplication “http://sitename”
$appPoolAccountName = $appPool.ProcessAccount.LookupName()

Once provisioned you will need to ensure that 4 things exist.
1. The Directory for the SSRS web service at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting. If this does not exist it indicates the SSRS add-in did not install properly.
2. Under IIS, the SecureTokenServiceApplication is missing, this is usually the result of a faulty installation and a repair/reinstall will be required - re-provisioning did not re-create this Application under 'SharePoint Web Services'.
2. A corresponding IIS Web Site exists - sometimes the IIS web site will not be created. If you are in CA -> "Manage Service Application" - when you click "Manage" on the SSRS service application you will receive a HTTP 503 Service Unavailable. In the address bar the GUID will be provided and if the reporting folder exists in the hive you can create a new IIS Application underneath "SharePoint Web Services" and map it to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting. Remember to strip the "-" dashes from the GUID when naming the application.
3. That the "SQL Server Reporting Services" is started in CA under "Services on Server". This one got me when I was getting the error: