IBM AS400 Linked Server Keeps Crashing SQL Server

I recently had to set up a new Linked Server on SQL Server 2012. It connects to a remote AS400 server using IBM iSeries OLE DB driver (IBM DB2 for I5/OS IBM DA400 Ole DB Provider)

Setting it up was no problem, except, whenever I ran a query, SQL Service would crash when the query finished returning results.

I found loads of good ideas (Switch of DEP, check for Heap Corruption, etc) but nothing worked. Finally I tried each parameter until it worked and got it working!!

To fix:
* Open Linked Servers -> Providers
* Right Click on ABMDA400, Properties
* Tick "Allow InProcess"
* Close the windows

BOOM - It works!!

From what I can gather, using it "out of process" protects SQL from errors on 64-bit machines,

"cannot create the window" message when you open SQL SSMS or Visual Studio

So, on our production machine, we started getting the error "cannot create the window" when most users start SQL Server Management Sudio (SQL 2012) and/or Visual Studio (VS2010).

Short version, Permissions to Machine.Config in C:\Windows\Microsoft.Net\\Config were changed when we installed the AS400 OLEDB drivers (IBM System i Access for Windows V6R1M0), and almost nobody had rights to the file (the only person with effective permissions was me, apparently because I installed it)

I reinstalled .Net,.. No joy... Reinstalled the drivers... no joy... Then I did the most obvious thing I can think of - Give "Everyone" read access on the file itself (right-click, properties, security).

Lo and behold, it works.

Changing the ID of a cube

So, we decided to do a checkpoint at work, and redeploy the production cubes from 8 months ago. That way, we can run both cubes, and show users what has been improved. however, when trying to deploy the SSAS project, it kept trying to deploy to the production cube. To solve it:

Pre-amble: Assume our cube Database is called JoeSoapSales. 

I changed the name to JoeSoapSales_Feb2014 by renaming it, as well as changing it everywhere I could find in VS. Yet, when I deploy I get a message that the cube "JoeSoapSales" already exists.

Finally, thanks to a lot of googling, I found that, in the solution folder, there is a .database XML file ("JoeSoapSales.Database")... Just change the ID in there using your favourite text editor, and save... Sorted!!

Auto-shrink all Database logs

One of the key differences between an OLTP system and a Data Warehouse, is that a DW can be completely truncated and reloaded from the OLTP, since it is purely an aggregation and reporting area. So while Log files are important for OLTP, you don't need have heavy logging on the DW. I always set my DW's to "Simple". However, even in "Simple" mode some logging does occur, and log files grow over time. Not all my customers have the luxury of a DBA, and in order to help them out, I have written this script to automatically shrink log files.

Some notes before you use it:

• You must have the necessary permissions on the databases
• Change the "SomeNameFilter" in the code to filter out only databases you want to filter. 

EXEC sp_MSForEachDB
' use ?;
declare @SQL varchar(2000);
select @SQL = ''DBCC SHRINKFILE ('' + name + '')''
from sys.database_files
where type = 1
and name like ''SomeNameFilter%'';

if @SQL <> ''''
begin;
exec (@SQL);
end '

Enjoy!!