I'd like to access Sage line 50 from within SQL server,

I have a test ODBC data source, DSN=sagedevel, user=manager, password
is blank.

I have tried

select * from
opendatasource('SQLOLEDB','Data Source=sagedevel;User
id=manager')...sales_ledger

but this is obviously wrong. Can anyone point me at the correct
documentation to work out what I need here.

On Fri, 29 Feb 2008 11:43:02 +0000, Iain Sharp <iains@pciltd.co.uk>
wrote:

In Management Studio expand into Server Objects > Linked Servers and
add a linked server.
-Tom.


>I'd like to access Sage line 50 from within SQL server,
>
>I have a test ODBC data source, DSN=sagedevel, user=manager, password
>is blank.
>
>I have tried
>
>select * from
>opendatasource('SQLOLEDB','Data Source=sagedevel;User
>id=manager')...sales_ledger
>
>but this is obviously wrong. Can anyone point me at the correct
>documentation to work out what I need here.

Iain Sharp (iains@pciltd.co.uk) writes:
> I'd like to access Sage line 50 from within SQL server,
>
> I have a test ODBC data source, DSN=sagedevel, user=manager, password
> is blank.
>
> I have tried
>
> select * from
> opendatasource('SQLOLEDB','Data Source=sagedevel;User
> id=manager')...sales_ledger
>
> but this is obviously wrong. Can anyone point me at the correct
> documentation to work out what I need here.

I have never heard of Sage line 50 before. I did find a site through
Google, but I cannot tell whether it uses SQL Server or something else.

But if Sage Line 50 runs on SQL Server, you will need to specify the
database, unless the user manager already has the desired database as
its default database.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Fri, 29 Feb 2008 22:17:15 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Iain Sharp (iains@pciltd.co.uk) writes:
>> I'd like to access Sage line 50 from within SQL server,
>>
>> I have a test ODBC data source, DSN=sagedevel, user=manager, password
>> is blank.
>>
>> I have tried
>>
>> select * from
>> opendatasource('SQLOLEDB','Data Source=sagedevel;User
>> id=manager')...sales_ledger
>>
>> but this is obviously wrong. Can anyone point me at the correct
>> documentation to work out what I need here.
>
>I have never heard of Sage line 50 before. I did find a site through
>Google, but I cannot tell whether it uses SQL Server or something else.
>
>But if Sage Line 50 runs on SQL Server, you will need to specify the
>database, unless the user manager already has the desired database as
>its default database.

Unfortunately, it uses its own database. It does come with an ODBC
driver, and I did find the Linked server option to set it up. This has
met with limited success. (One table appears to return all values but
there are only 15 in the database, one fails after about 30).

I shall persevere.

Iain

Iain Sharp (iains@pciltd.co.uk) writes:
> Unfortunately, it uses its own database. It does come with an ODBC
> driver, and I did find the Linked server option to set it up. This has
> met with limited success. (One table appears to return all values but
> there are only 15 in the database, one fails after about 30).

In that case, specifying SQLOLEDB is no road to success, but it seems
that you have already returned from that blind alley.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx