Wednesday, December 22, 2010

Server 2008 and SSRS

Sadly we do not have a development or test environment for our Reporting Server. However, I wanted to test the upgrade from SQL Server 2008 to SQL Server 2008 R2. I wanted to know how it will affect our reports, most specifically reports that use Dundas controls.

So I asked our server admin to create me a VM for testing. I installed SQL Server 2008 database engine and reporting services, however, I had trouble deploying reports to it. It's been a while since the last time I set up reporting service so I forgot that I had to assign the System Administrator role specifically within reporting services Site Settings section. The problem is that I kept getting the error "The permissions granted to user [] are insufficient for performing this operation. (rsAccessDenied) "

It turns out that the solution was to run IE as Administrator. That made the Settings page available.
Deploy still gave me the same rsAccessDenied error. The solution for that was also to run BIDS as Administrator.

Many thanks to Johan Åhlén's blog where I found the solution.

SSIS - Office 2010 Woes

Good news: I got a new laptop at work that's much faster than my previous one.
Bad news: new laptop = setup issues.

My previous laptop was 32-bit and this one is 64-bit which came with its own challenges. I have the 64-bit Office 2010 installed on it. If you have ever worked with SSIS in a 64-bit environment then you know where this post is going.

I am working on an SSIS package that import data from Excel 2007 to SQL Server. I had created the package on my previous 32-bit laptop and I have to tweak it. Now I get this error: "
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

I search for a solution and I come across a few things. One is to set my project to use the 32-bit runtime. Another one is to download the Microsoft Access Database Engine 2010 Redistributable which has backwards connectivity components. Another thing suggested changing the connection string to Microsoft.ACE.OLEDB.14.0.

Neither of the above solutions have worked. What eventually worked is downloading and installing the 2007 Office System Driver: Data Connectivity Components. Just for reference this is my connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=2";

I hope this post will help someone facing the same problems. Merry Christmas!

Just a note: in order to install the Access Database Engine 2010 32-bit version I had to uninstall any 4-bit versions of Office.

Thursday, October 21, 2010

Fishbowl (Firebird) ODBC Connection

My company decided to use the Fishbowl Point-of-sale system to handle bookstore activities. My project was to get transactions out of this system and imported into our SMS (Student Managment System) for which we use CampusVue.

Fishbowl's back-end is an open-source DBMS called Firebird. CampusVue is running on a SQL Server database.

As usual I decided to use SSIS to achieve this task.

The first challenge was to be able to connect to the bookstore database. Upon reviewing documentation of the Fishbowl product I was able to create an ODBC connection to it. You will need to obtain the credentials used if you are facing the same task. This particular credentials only allows reading of the data but that's all I need.

Here is the way the ODBC connection is set up for all who needs it.

32-bit vs 64-bit in SSIS

My recent project involved using a DSN as the connection string in SSIS. The challenge of the project was that I only had the 32-bit version of the driver to create the system DSN but the server was a 64-bit server. In Administrative tool there IS a 32-bit version of the ODBC editor so that was not a problem.

However, in order to make everything work in SSIS I had to do 2 things.

1) To debug in BIDS I had to change one of the solution properties. Under Debug set the Run64bitRuntime option to False.

2) In order to use the 32-bit runtime once the package was set to run as a SQL job in SSMS I had to check that option on the Step setup on the Execution Options tab.

After this everything was running smoothly.

Wednesday, July 14, 2010

Type is Not Defined in Visual Studio 2010

I work for the University of Advancing Technology and as the name implies we (IT) ought to to be using advancing technologies. It's a little hard to do when we have so many legacy applications to maintain and expand. It took us a years just to move from VS 2003 to VS 2008 (.Net 1.1 to .Net 2.0+) However, one of my projects required using .Net 4.0 and Visual Studio 2010.

In this project I had to create a WCF service and a Windows service. To be used as testing grounds I also added aWindows Forms project to my solution as well.

My first task was to create the WCF service. To my biggest surprise that went pretty smoothly even though I had to reference a code library written in VS 2008 using .NET 3.5. To test my service, however, I wanted to use the Windows Forms project. There I added reference to that same code library I used in my WCF service. And that's where the problems began. For some reason creating an instance of an object from that code library was throwing error: Type 'myobject' is not defined. Interestingly that error only appeared when I tried to compile the project.

I tried the obvious, deleted then readded the reference, opened and closed my project, restarted Visual Studio, restarted the computer. Nothing. Same error.

Then I decided that perhaps I should look at the warnings as well. And one of the warnings game me a clue: The referenced assembly could not be resolved because it has a dependency on "system.Data.Oracleclient, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" which is not in the currently targeted framework ".NETFramework,Version=v4.0,Profile=Client". I am not using Oracle but my middle tier, the code library I am trying to reference uses the Microsoft.Practices.EnterpriseLibrary libraries. It obviously has references to the aforementioned  Oracleclient.

After searching a little I found the solution. I had to have my project reference the full .Net 4.0 framework not only its client profile.

Open the project properties by right-clicking the project file then selecting Properties. On the left side click on Compile.

On this window click on Advanced Compile Options. On the bottom of that screen change the Target Framework to .NET Framework 4.

So it appears that WCF projects automatically use the full .NET Framework and Windows forms and Windows Services by default use the client framework only.

Good to know.

Thursday, April 29, 2010

Migrating SQL Jobs

About once a year I am faced with the fact to migrate one of our SQL Servers to a better, bigger, newer server. Usually it's not a big deal because for the most part it can be done by backing up and restoring the database. How about migrating the SQL Jobs though? Sure, that can be done as well by backing up and restoring the MSDN database but if you are also upgrading SQL Server that may not be a wise idea.

There are a few ways this can be done. SSIS has a Transfer Jobs Task but I ran into a problem with that as most of the jobs had the server specified like this: @server=N'SERVERNAME' so the task was not able to transfer those jobs. Individually scripting out SQL Jobs is possible of course if you only have a handful. In my case I had 87 SQL Jobs and frankly, I am too lazy for that much work. So I found a shortcut.

Click Jobs in Management Studio and then click F7. That will pull up Object Browser with the list of all the SQL Jobs. Highlight all and then right click and select Script Job As>Create To>New Query Window. That will script out all your jobs. Once you have them scripted out you can just a search and replace for server names or other variables you need to change. Then you change the connection to your new SQL Server, hit Execute and you are done!

In my case I was almost done. I also had some Maintenance Jobs to transfer as well. Those are basically SSIS packages kept on SQL Server as opposed to in the File System. The solution is to export them to the file system, change the connection and then import them into the new SQL Server. Here are the steps to accomplish that.

  1. Connect to your server's Integration Services in SSMS. You can do that by hit Connect>Integration Services. Once you are connected you will find the maintenance plan packages under Stored Packages>MSDB>Maintenance Plans.
  2. Export all packages. You can do that by right clicking each package then selecting Export Package. On the dialog box that box up you need to select File System in the Package location section. Then navigate to the location where you want to save your package. I left the default in the Protection level section.
  3. Open the packages in notepad and change the connection information. Just search for the name of the server and replace it with the name of the new server. This step is necessary because you are unable to edit the Local Server Connection in the package editor. You may also change other things as well such as backup locations and such. Save the file.
  4. Import the package. Connect to your new servers Integration Services. Navigate down to the location where the Maintenance Plans are located which should be the same as in the source server (see step 1.) Right click Maintenance Plans and select Import package. Select File System in the Package location then in the Package path using the ellipsis button navigate to the location where you exported your packages to. The Package name will be automatically filled in although you could change it.
  5.  If by accident you skipped step 3, don't fret, you can still do it. You will have to repeat step 4 again and this time you will be overwriting your existing package.
  6. You probably want to double-check on each package to make sure there are no other adjustments need to be done. Connect to the new SQL Server's database engine this time and right-click Modify on the maintenance plans. (Management>Maintenance Plans)
That's all there is to it. I hope I saved someone time with this post.

Monday, April 19, 2010

Timers in Windows Services

Occasionally I have to create a windows service. In most windows services I need to create a timer to execute a certain method periodically. Since I don't work with windows services often enough I always get stumped at the best method to create the timer. I know that by dragging the Timer object from the Toolbox adds the timer from the Windows Forms namespace. That timer has a Tick even which I haven't been able to make work in a Windows Service project. So if you create a windows service in Visual Studio 2008 and you need a timer this is the code you need to add to your class for the service:

Private WithEvents Timer1 As New System.Timers.Timer()

Private Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed
    'Some code
End Sub

Afterwards you can set the elapse time of the timer and enable it in your service start event:

Protected Overrides Sub OnStart(ByVal args() As String)

    Timer1.Interval = 60000 '60000 = 1 minute

    Timer1.Enabled = True

End Sub

Happy coding!

Tuesday, March 2, 2010

Cannot fetch a row from OLE DB provider "BULK" for linked server

Today I come across the error "Cannot fetch a row from OLE DB provider BULK for linked server" in one of my SSIS packages. So I research this error and I find that this seems to be a catch-all error for SQL Server Destination. Therefore it's not very useful as you don't know exactly what causes it. The fix for me is changing the MaxInsertCommitSize from 0 to 99. You can find this property in the Advanced Editor for SQL Server Destination on the Component Properties tab.

Update 2-21-2011: I found another set of condition that may be causing this error. I used a SQL Server Destination but the connection manager was OLEDB. Once I changed the destination to be OLE DB Destination the error went away. The odd thing is this combo worked until I added a Lookup to my Data Flow. [insert rolling eyes here]

Monday, February 1, 2010

The Object Already Exists

In our Active Directory set up the same username cannot exists twice within the domain which is pretty standard. However, the same name cannot exist either within the same organizational unit. I am working on a automated way to create new accounts so these little tidbits of information has come into play. When my application is trying to create an account with the same username OR name it throws a DirectoryServicesCOMException error with the same message "the object already exists." It's not very useful if you try to programmatically resolve it.

Upon examining the details of the exception object I notice that both times they are identical with one exception, the ExtendedError property which contains an integer value. So if the error is raised because the account tries to duplicate the username then the ExtendedError property is 1316, if it's just the name that already exists in the same OU then that number is 8305. Just in case you are wondering if both the username and the name are the same then the ExtendedError property is 1316 so directory services first checks on the username and then the name which makes perfect sense.

I hope this will help someone in need out there.

Wednesday, January 13, 2010

Mind-boggling Problem!

I created an SSIS package that reads a comma delimited (CVS) file from a network share, loads it into a SQL Server table and then moves the file to a folder called Completed. Simple, right? After fighting my battle with the "truncate" error that you could see in my previous post the package runs fine. So it's time to set it up as a SQL job.

I set up the job, run it and it says success but there is no data in the database and the file has not been moved to the Completed folder.

I added logging to the package so that everything will be logged onError, onInformation, and onWarning to the Windows Event log. (You can do that by going to the SSIS>Logging menu item.)

I see that I have a Warning in the event log that the job could not find any files. I check on the permissions and I see that everything is set up right. Both the share permissions and the Security tab shows Modify permissions for the group that the SQL Agent user is in.

I spend all morning with a co-worker of mine (thanks Dennis for your time!) trying all kinds of crazy workarounds and solutions. And what we found that worked is mind-boggling!

If we change the permissions on the share to give Modify rights to the SQL Agent user itself (not just the group it's in) everything works!!!!

Can someone please explain to me why?????

Tuesday, January 12, 2010

SSIS - Annoying Truncation Error

Several of my SSIS projects involve importing data from CSV files. Most of the time if the CSV file contains any text the import will result in the following error in SSIS: "Text was truncated or one or more characters had no match in the target code." I have used a few workarounds to avoid this message. Most of the solutions were posted in a thread on Technet. I will post here the couple of workarounds that has worked for me.

Workaround #1
The Jet engine determines the column types and lengths by the first 8 rows. If after the first 8 rows there are rows that contain text data longer than what is in the first 8 rows, you will get that error. So you can put in a fake row in the very first row with long strings of text. Or move one of the existing real data row with long strings to the first row. It's a bit clumsy but works.

Workaround #2
Convert the CSV file to an Excel workbook. Works like charm!

One other solution has included messing with the registry to tell the JET engine how many rows it should base its guess on the data length. Check out the previous page I posted on the details. I have not tried that but I may have to do it in the future.