The Remote Procedure Call Failed

I have a named instance of SQL Server where I had to do an edition upgrade from SQL Express to Standard edition. A while later I notice that the SQL Agent is not running. I go to SQL Server Configuration Manager to start the Agent. First I need to change the start mode to either manual or automatic. However, I get the error "The remote procedure call failed." I try changing the log on account to something else but I am still unable to start the Agent. I search and Google and finally find this little weird tip. Do this in the service mmc! Normally you do not want to make any changes to SQL Service related items there but for some strange reason that was the solution for me as well.

SSIS Logging with Custom Messages

SSIS Logging is a great thing. I use it in every single SSIS Package I create. I normally use the SSIS log provider for SQL Server. I am not going to explain here how to set up logging for SSIS. There are plenty of other blog posts or BOL articles about that. Here all I was going to discuss how to add your own custom messages to log.

Drag the Script task control flow item on the Control Flow area and connect it to the desired component or components. Name the script task component wisely. The name of the script task will show up in the log's source column.

Double-click the script task and specify the ReadOnlyVariables if you want to display their values as your custom message. In this example I use Microsoft Visual Basic 2008 as my script language as that is my preferred coding choice because I just can't stand the curly braces of C#. :)

Click Edit Script which will bring up the script editor. Now look at the big block of comments. The information I am providing in this log is right there!

' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)

This is what I used in an existing SSIS package I created.



Public Sub Main()
        Dts.Events.FireInformation(1, "Filename", Dts.Variables("User::SchemaRawfileLocation").Value, "", 0, False)
        Dts.TaskResult = ScriptResults.Success
 End Sub

So you can see that I created an Information event and my message is the value of the SchemaRawfileLocation variable.

On the msdn site you can look at the parameters to pass into the method. Not 100% sure on all the choices but my above example has been working.

These are the parameters:

FireInformation(informationCode as Integer, subComponent as String, description as String, helpFile as String, helpContext as Integer, byRef fireAgain as Boolean)

In my example:

informationCode: 1

subComponent: "FileName" (this does not seem to display in the SQL log though)

description: Dts.Variables("User::SchemaRawfileLocation").Value (the value of the variable, it's displayed in the message column)

helpFile: ""

helpContext: 0

fireAgain: False (no I do not want to fire this event again)

And there it is. Happy coding!

System.OutOfMemoryException in SSMS

It's quite annoying to have a query running for a while just to get this error message: System.OutOfMemoryException. I am still researching this error as while the error message seems to be quite clear, it is misleading. My local machine and the server seems to have plenty of memory so that is the deal? So far I found a few possible workarounds:

1. Since only a 32-bit SSMS exists run the query on a 32-bit machine. 
2. Change the query results to text and not grid. When the results are displayed in grid, it uses the .net CLR which is a memory hog. Or so I read.
3. Of course, the third obvious solution would be closing some other large query windows if open.

Cannot Generate SSPI Context

Oh this cursed error! If you google it you will find tons of possible solutions that fixes this dreaded issue. In this short post I will describe what fixed the problem for me.

First a little background on previous events. One of our SQL Servers was rebuilt by creating a separate VM for it with the same name as the original after the original was taken offline.

Next day I am unable to use Windows Authentication to log into this new server. I get the Cannot Generate SSPI Contect message. I can log into the server itself and access the databases without any problem locally. I can even do that on someone else's computer, just not mine.

I play around with the setspn command line utility for a while but nothing helps.

Finally I log into the server using a SQL account. After that is successful I try the Windows login and finally, I'm in!

Such a simple solution. I hope this will help someone else too.

SSAS - Renaming Project /Database

At my new job I am learning about SSAS and cubes. I have created a project to practice what I have learned in my SSAS training course. Since I like to practice on something that may deem to be useful I created my first cube based on the data warehouse developed by my co-worker. While I did not name my project Hello World, I named equally silly: Testcube. At first I didn't think it's silly until I realized that this is an actual project that can be put in to use in production very easily.

So I set out to the task of renaming it. Renaming the project and the database itself was easy enough since no one was using it yet. However, after a while I noticed that the directory it creates is still called testcube. Ok, so users would never see that but I didn't want some rookie DBA or server admin come after me and delete the folder thinking it can't possibly be used.

Upon some research I realized that SSAS databases have names and IDs. At initial creation the 2 are the same. However, while I was successful at changing the name in BIDS, it did not change the ID and apparently the ID is used to name the directory. (Upon my research I also found out that SSIS also uses the ID.)

In SSMS I can see what the ID is but I can't change it. So I was looking around in BIDS. No luck. I, then looked at the files BIDS created. And so the solution was born. Do these steps AFTER you renamed everything you possibly could in BIDS.

1. Close your project if it's open.
2. Rename the .database file to the name. In my case I renamed it from testcube.database.
3. Open the .database file in Notepad and change names in the ID and Name nodes. Save.
4. Open the dwproj file in Notepad and fine the section. Change the Name and Fullpath nodes to refer to the database file in step 3.
5. Delete the SSAS database in SSMS.
6. Open the project in BIDS and redeploy. Note the Name and ID of the database.

And you are done.

In my case since the database was not used yet and it was quick to redeploy, the name and ID change didn't present other barriers. In production, this may not be such as easy matter.