"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.

Analysis Services Unknown Role that already exists

I was trying to re-deploy a SSAS project using Visual Studio 2010. Every time it deployed, I got the error "the 'role' with 'id' = 'role' doesn't exist in the collection"... Except, we don't have a role called "role".

Googling just led me to things like manually trying to update the .role file and correct it... except WE DON'T HAVE A ROLE CALLED "ROLE"

Desperate times calls for... thinking outside the box!! So I opened the solution in Visual Studio 2013 and deployed... and it worked first time around!!

Not sure why this works, so anyone who sees this post is more than welcome to leave an explanation, but I'll take it as a cheap win, and just say that the problem is fixed :-)

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!!

TimeXtender deployment still references old server

We use an ETL tool called TimeXtender (v2014), which is a SSIS wizard.

We recently had to migrate to a new server. After changing the connections via the front-end wizard, saving and deploying the solution, I had an interesting experience. No matter how many times I ran it, my data didn't change. But when I logged into the old server, I was shocked - all my production data had disappeared. Logic told me that TimeXtender had tried to run against my old server, not the new one.

I confirmed all my connections were pointing to the old server.

I approached the vendor, who reconfirmed the basics (did I save, run the wizard, etc... Which I did do).

Eventually, I exported the project to XML, and opened it in notepad... Lo and behold the SSIS server was still set to the old server. I did a quick find/Replace, and imported the project back in... I ran it, and it worked.

---

So, the vendor contacted me this morning. It turns out, that next to the Login credentials button, there is a "Advanced" button, which doesn't change the login properties, but mostly SSIS properties, including server name.

Access Denied to .Net folders when processing a SQL 2012 SSAS Cube

We recently migrated to a new server. The deployment went smooth, up unto the point where we had to process the cube. When trying to process, it failed immediately. The error was: "Errors in the high-level relational engine. The following exception occurred while an operation was being performed on a data source view: Configuration system failed to initialize;An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config' is denied. (C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config)
Access to the path 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config' is denied.."

Access to path? Easy-peasy fix... except it wasn't. I added the service accounts to the admin groups, gave the folders explicit permissions, no joy. Suddenly I felt very silly - such a simple looking problem, and I can't get it sorted? Google yielded no help... So, out of desperation, I yielded to my AD Admin  days, and thought what I would do then... So, to resolve:

• Reset the permissions of the service accounts
• Added the service accounts to a group with the right permissions
• Changed the SQL and SSAS Service accounts to my own account
• Stop and restart the SQL & SSAS Services
• Changed the SQL and SSAS Service accounts back to the Service Accounts
• Stop and restart the SQL & SSAS Services

Voila, it worked :)

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!!

Setting up POP3 and SMTP Relays with Exchange 2010

Wow, recently had SO much fun struggling with Exchange 2010, to set up a POP3 connectors for external mailboxes and an SMTP relay for an internal SQL mail server. The challenge was, that when I could relay internal mail to external, my POP mailboxes fell over, and vice versa...In the end, I had to delete all the connectors Exchange created by default, and set them up manually.

It all started when I was trying to send a SQL mail using CDO. I kept getting "-2147220977" error code, which translates into "0x8004020FL - The server rejected one or more recipient addresses" (Convert the code to hex, and look at TechNet for the meaning). When I rewrote my code to C# (.Net), it popped up with "5.7.1 Unable to relay". So, at this point, I was sure SMTP was the problem.

I deleted all the SMTP connectors, and recreated one for outgoing routing, and it worked... Or so I thought... even though the mail goes out, I now could not download any mail from POP3 mailboxes. When I started the POP3 download, Event log had an error event :"Cannot connect to the SMTP server 'localhost' on port 25. The error code was 0x800ccc0f. Verify that the Microsoft Exchange Transport service is running and that the Exchange receive connectors are properly configured"



Googling did not help much, as it seems not a lot of people have had this problem before, so out of desperation, I took to my trusty whiteboard, and designed what I wanted it to do (I know, such a developer thing to do, but hey, it worked). I came up with an action plan, executed it, and lo and behold, it worked.

This is what I did...

1. In the Exchange Management Console, go to Server Configuration, Hub Transport, and delete ALL receive connectors

Now let the fun start:

1. Using the Wizard, Create a new Connector, called "POP3 Mailbox Connector". Set the intended use to "Custom" and click on next
2. At Local Network Settings, remove the default, and add specific IP 127.0.0.1
   . For the FQDN, use the FQDN of the Exchange server, and click on next - This will tell the server which server to send the incoming mail to.
3. At Remote Network Settings, remove the default, and add "127.0.0.1". This will tell the server which machine will be doing the POP3 pickups. Click on next, and run through the confirmation screens (New, Finish)

At this point the connection is set, but because of security not set, it can't do anything

4. Double click on the new connection
5. Go to Authentication, and enable Basic Authentication only. This will allow the POP connector to send credentials to the remove POP mailbox server
6. Click on Permission Groups, and set Anonymous Users and Exchange Servers. This will let the current server connect to the boxes, either through the NW Server account or through and Exchange account
7. Click on Apply, and the POP3 component is set.


Now for the SMTP relay

1. Using the Wizard, Create a new Connector, called "SMTP Relay Connector". Set the intended use to "Custom" and click on next
2. At Local Network Settings, remove the default, and add the specific IP address of the exchange server
   for the FQDN, use the FQDN of the Exchange server, and click on next - This will tell the server which server to send the incoming mail to.
3. At Remote Network Settings, remove the default, and add the IP of the server you want to send emails from. Click on next, and run through the confirmation screens (New, Finish)

At this point the connection is set, but because of security not set, it can't do anything

4. Double click on the new connection
5. Click on Permission Groups, and set Exchange Servers. This will let the current server allow connections from the sending server
6. Go to Authentication, and enable Transport Layer Security (TLS) and Externally Secured. This will allow the server to receive the internally generated emails, and get the response from external domains
7. Click on Apply, and the SMTP component is set.

Now test, and there ya go... Hopefully this will make your searching simpler than what I had to go through to get it to work :)