fastscot, Member
Are there any plans to extend mydbr to other databases using ODBC? My ERP system uses a Firebird SQL database and the only way I can use mydbr is to migrate/update the data to a mysql database at regular intervals. Although the reports do not need to be real time the whole process is cumbersome and it would be nice to have real-time reports.
Neil
brad@foreverybody, Blocked
My company has myDBR running on mssql server but is able to query any database i have thrown at it as long as I set it up as a linked server (which does work with ODBC) I have 2 mssql database servers, 2 mysql database servers, 1 oracle database server, and 1 microsoft access database all linked together and myDBR has no problem with it.
So I'm thinking you may be able to accomplish something similar by installing myDBR on mySQL then linking your Firebird database. I don't know if mysql does linking link mssql, but its an idea.
myDBR Team, Key Master
In order to achieve the level of automation myDBR has, myDBR utilizes database system's features quite extensively, so we need more than the connection to the database.
To have a direct access to Firebird database the best option at the moment is to use a db gateway (such as MS SQL Server's Linked Server Brad mentioned) or use replication between the master and reporting database.
--
myDBR Team
fastscot, Member
It is not possible to link directly from mysql to a linked database such as firebird as you can in mssql. I had thought of using Brad's suggestion but I need to test to see if it works with the free sqlexpress version. Thanks for your responses.
Neil
fastscot, Member
Well, I got everything working using a linked server in SQLEXPRESS however when I try to run a report I get the following error message: Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server. I can run the procedure from the query analyzer in the mssm studio but not thru mydbr. I searched all over the internet for a solution with no resolution. Any ideas?
brad@foreverybody, Blocked
what version of SQLServer and 32 or 64 bit?
EDIT:
additional question, are you able to run the stored procedure in management studio or just the SQL statement your procedure is based on?
I suspect your problem is a permissions issue, make sure the myDBR user has sufficient permission to query linked servers and/or access ODBCs.
fastscot, Member
I am able to run the stored procedure in management studio. I am also able to run a report if the select statement in the stored procedure uses a table in the mydbr database (see below)
This procedure works:
CREATE PROCEDURE sp_DBR_check1
AS
BEGIN
SET NOCOUNT ON;
select * from mydbr_userlogin
END
This procedure does NOT work:
CREATE PROCEDURE sp_DBR_check2
AS
BEGIN
SET NOCOUNT ON;
select * from DBA...JOBTYPE
END
DBA is the name of the linked server and JOBTYPE is a table in the linked server database.
Both procedures work however in management studio.
I have searched everywhere and am at a total loss on how to fix this. Any help or pointers would be much appreciated.
Thanks,
Neil
brad@foreverybody, Blocked
when you say it doesn't work what happens? do you get an error?
try using OPENQUEY command to see if that works. I had a lot of issues setting up my linked servers, and I recall having to change some database configurations to get them to work right.
fastscot, Member
When I run a report from mydbr web site using the stored procedure accessing the linked server I get the error 'Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server DBA'
When I run a report from the mydbr web site using the stored procedure accessing the mydbr database, the data is displayed.
Hope that clarifies.
I'll try using openquery and see what happens.
Thanks
brad@foreverybody, Blocked
judging by that message I would say your linked server is not setup correctly. forget about myDBR for a min if you will.
from management studio after you set up the linked server try querying from within management studio if it works from management studio but not myDBR I would say you have a permissions problem for the myDBR user.
you can right click a linked server and select 'test connection' to make sure the connection is working.
make sure you enable oledb connections in the sql server configuration.
I promise what you are trying to do is possible. I do it everyday.
You must log in to post.