|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Multi-data source query? |
| Author |
Message |
|
|
| Posted : 2009-02-18 04:53:26 |
I was wondering if it is possible to do a single query between a Quickbooks company file and a SQL Server database.
From what I've read on a couple sites is that two datasources can be used in a single query without having to create a linked server (using either the OPENROWSET() or OPENDATASOURCE() functions in the FROM close of the sql statement).
(http://www.bokebb.com/dev/english/2025/posts/2025132387.shtml)
Ex.
SELECT p.PortalID, p2.PortalID as Portal2
FROM dnn_Portals, OPENDATASOURCE ('SQLOLEDB', 'Data Source=<other server>;User Id=<user>;Password=<pw>;' ).DotNetNuke.dbo.dnn_Portals AS p2
Now this is with 2 SQL Server databases, albeit on two different servers. I was attempting to emulate this to work with the QODBC, but I can't seem to get it to work. The following query keeps returning the error "Invalid object name: Customer."
SELECT p.PortalID, c.Name
from dnn_Portals p, OPENDATASOURCE('MSDASQL', 'DSN=Quickbooks Data;UID=<user>;PWD=<password>').Customer AS c
I am not sure what is incorrect with the syntax or if anyone has got this to work. Is this even possible to do, or is a linked server absolutely necessary?
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-18 10:17:13 |
QuickBooks only allows for one Company file to be open at the one time. To link mulitple company files or QuickBooks on demand you will need to link and unlink seperate company files by specifing different paths in the DFQ=fullpathname connection parameter with a delay between executing sp_addlinkedserver and sp_dropserver.
For example:
EXEC sp_addlinkedserver @server = N'QODBC', @srvproduct=N'QODBC', @provider=N'MSDASQL', @datasrc=N'QuickBooks Data', @provstr = 'ODBC;DSN=QuickBooks Data;DFQ=C:\Program Files\QODBC Driver for QuickBooks\sample04.qbw;SERVER=QODBC;UseDCOM=Y; OptimizerDBFolder=C:\Program Files\QODBC Driver for QuickBooks\Optimizer; OptimizerAllowDirtyReads=Y'
EXEC sp_dropserver @server = N'QODBC'
These stored procedures also allow you to specify the location of the OptimizerDBFolder.
|
|
|
|
|
|
| Posted : 2009-02-18 10:30:50 |
I was not trying to access more than one company file. I am trying to access one company along with tables in a separate SQL Server database. What I meant was is it possible to create a query using the OPENDATASOURCE() function where we would open the quickbooks dsn in the function? Or, is utilizing the DCOM method absolutely necessary?
We do not want to have SQL Server on our Quickbooks machine, so we were trying to find another way to do so. I had not seen someone try to use that query method, so I wanted to see if it was possible. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-18 11:28:05 |
| The QODBC tables can only be local linked tables on the MS SQL Server itself and only linked on that server. You can't access the QODBC tables from another MS SQL Server. |
|
|
|
|