Need to build reports for two databases

Question

Need to build reports for two databases

kdeem

Newbie

Points: 9
More actions
May 7, 2009 at 4:29 pm

#204756

Hello.
I am building my first c#/ASP.net app (3.5) and am using data from two different databases. One is SQL 2008 and the other is Sybase 10. I need build a report from these two databases and the project schedule is extremely aggressive (OK, so I only have 10 days left). I'm completely new to Report Server and am unsure about the best way to get started. My understanding is that I cannot use Report Builder with Sybase 10. What is the best way to proceed? Should I use a dataset that I have in my application. If so, how? I found something online about using data flows in SSIS. I created one and now I'm not sure how to report from it. Could someone give me some direction about the best way to move forward. I would deeply appreciate it.
Thanks,
K

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Shiv-776719 SSC Enthusiast Points: 111 More actions · Answer 1

First Sybase 10 is way too old - ask your Administrators to upgrade.

I would suggest defining a linked server in SQL Server 2008 pointing to Sybase 10 server, using ODBC or OLEDB for Sybase. You should be able to access tables/objects from both servers in SQL Server. If report server causes any problems accessing objects from Sybase - try creating a view or stored procedure joining data from both servers inside them.

Hope it helps!

kdeem Newbie Points: 9 More actions · Answer 2

Hi Shiv.

Thanks for the reply. There is no way that I can upgrade the Sybase db at this time. It's a legacy system central to our business and another business app prevents us from upgrading at this time.

I really liked the Linked Server idea but was unsuccessful so far. I found the Linked Server page in SQL admin. Here is the script I tried:

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver @server = N'FP', @provider=N'MSDASQL',

@datasrc = 'ODBC DSN Name'

GO

USE [master]

GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'username',

@useself = N'False',

@rmtpassword = 'password'

GO

So far that has not been successful. It creates a linked server but I cannot browse tables. From my reading it appears as if I need the Sybase Interactive SQL installed on the SQL 2008 server. My sys admin is currently researching this.

If this fails, my current plan is to load datasets into an XML file and then report from that. That seems like a total pain.

Also, I was able to easily connect to the Sybase server with SSIS and it appears as if I successfully created a package. But I don't know what to do with the package. My googling seems to indicate that running report server against a package is a bad idea for production.

Again, thanks for the reply and I'm grateful for any further suggestions.

Best regards.

kdeem Newbie Points: 9 More actions · Answer 3

Hi Shiv,

Thanks so much for your suggestion about the Linked Server. I got my first report working.

For anyone who is looking for this information, here is what I did to get SQL 2008 SSRS to report from Sybase Anywhere 10:

•Install 64 bit ODBC drivers and Sybase Interactive SQL on the SQL 2008 server

•Reboot

•Create a User DSN for Sybase (e.g. DSNName).

•In Microsoft SQL Server Management Studio navigate to Server Objects > Linked Servers > Providers.

•Right click SAOLEDB.10 and on the General properties page, check the box for Allow inprocess.

•Right click Linked Servers

•On the General Tab, enter the following information

Linked ServerGive the server a name. (ServerName)

ProviderSQL Anywhere OLE DB Provider 10

Product nameDSNName

Data SourceDSNName

Provider Stringuid=username,pwd=password

•On the Server Options tab, choose true for RPC and RPC Out

Shiv-776719 SSC Enthusiast Points: 111 More actions · Answer 4

I'm glad it worked out fine. That 'in process' option is key in making Sybase server working properly as linked server.

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 5

Shiv (5/7/2009)
I would suggest defining a linked server in SQL Server 2008 pointing to Sybase 10 server, using ODBC or OLEDB for Sybase. You should be able to access tables/objects from both servers in SQL Server. If report server causes any problems accessing objects from Sybase - try creating a view or stored procedure joining data from both servers inside them.

:smooooth:

I've used a similar method in the past when opening up an Informix database to reporting services.

Once the drivers were configured properly (took a few days and about £500 in to the swear box) it worked a treat