Linking Access(urgent)

Dear all
I've a problem when I make a command button in Access's Form which open form or report the message that "error encountered with OLE ActiveX?"
Can any body help me to fix this ?
PLZ Help me urgentCould you give the exact error message, # etc. That would help.

server, heterogenous query

Hi all!

I have to pull new data from an Ingres Server over to my MSSQL server. I set the Ingres box up as a linked server and I can do the following:

declare @.start as datetime

set @.startdate = (select start from synctimes)

select * from openquery(INGRES, 'select * from ingrestable') where date > @.start

This does exactly what I want in my testsetup. In real life, the two boxen are connected via a slow connection and I wonder if that way *all* records are transferred and filtered on the MSSQL side, thus I transfer 99.5% rubbish. Is that true and ifso, is there any way to get around this?

in addition, I want to acheive this

CREATE PROCEDURE import_all AS
declare @.myVariable as Cursor
declare @.start as datetime
declare @.tblname as nvarchar(50)

declare myCursor Cursor for
SELECT tblname from synctables

open myCursor

set @.myVariable = myCursor

fetch next from @.myvariable into @.tblname
while @.@.fetch_status = 0
begin
select * from openquery(INGRES, 'select * from @.table where date > @.start')
fetch next from @.myVariable into @.tblname
end
close myCursor
deallocate myCursor

Is this possible?

TIA,

chrisI wonder if that way *all* records are transferred and filtered on the MSSQL side, thus I transfer 99.5% rubbish. Is that true and ifso, is there any way to get around this?
Yup, you are transfering all the records and THEN filtering, try:

declare @.startdate as datetime, @.TSQL as varchar(255)
select @.startdate = getdate()
set @.TSQL = 'select * from openquery(INGRES, ''select * from ingrestable where date > ''' + convert(varchar(25),@.startdate,121) + ''')'
print @.TSQL
exec(@.TSQL)

I don't exactly recall what date format Ingres uses but you can adjust the convert as needed.

as for your sp, I think you are on the right track but I see some problems. Consider:

CREATE PROCEDURE import_all(
@.Debug bit = 0)
AS

declare @.start as datetime
, @.tblname as nvarchar(50)
, @.TSQL as varchar(255)

select @.tblname = min(tblname from synctables
while (@.tblname is not null) begin
set @.TSQL = 'select * from openquery(INGRES, ''select * from ' + @.table + ' where date ''' + convert(varchar(25),@.startdate,121) + ''')'
if (@.Debug > 0)
raiserror('%s',0,1,@.TSQL)
else
exec(@.TSQL)
select @.tblname = min(tblname from synctables where tblname > @.tblname
end

return 0

Of course I have no way of test this, on your first run you may want to set @.Debug to 1 to check the resulting code.|||Thanks alot. Seems to be just what I needed. Couldn't test it though because of a defective ingres odbc driver I won't reinstall before monday or tuesday. I'll post my results afterwards ;-)

server with windows authentication and wihout AD(kerberos)

Hi All!
I have two Windows Server 2000 with SQL Servers 2000 installed and
uses windows authentication only.
Can i use linked server without Security Account Delegation (no domain, AD,
... ) and not use SQL authentication?
P.S.: Presently I use pass-through windows account authentication (same
username/password on two windows servers), but this not works with linked
server.
Thanks in advance.
Igor.Hi
Mike answered this in microsoft.public.sqlserver.securitytools. Please do
not multi-post.
John
"Igor Aflatunov" <Igor Aflatunov@.discussions.microsoft.com> wrote in message
news:CA9B20F2-C6FA-4F59-83F3-7A809DC09184@.microsoft.com...
> Hi All!
> I have two Windows Server 2000 with SQL Servers 2000 installed and
> uses windows authentication only.
> Can i use linked server without Security Account Delegation (no domain,
> AD,
> ... ) and not use SQL authentication?
> P.S.: Presently I use pass-through windows account authentication (same
> username/password on two windows servers), but this not works with linked
> server.
> Thanks in advance.
> Igor.
>

server via ODBC - help!

Hi All
I have set up several linked SQL servers which work ok - but I'm having
trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
The ODBC points to an 'Alchemy' database and uses an Alchemy driver
ironically i can set up linked tables in an Access DB, via ODBC - and I can
pull the same data into Excel via ODBC - so there is no problem connecting t
o
Alchemy using other MS apps.
When I add the linked server in SQL, it appears to be added but no tables
are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
returns an error "Cannot initialize the data source object of OLE DB provide
r
'MSDASQL' "
Its driving me crazy...Anyone have any ideas'
Thanks in advanceMake sure the driver is compatible with the MDAC version on
the SQL Server box. Make sure you are using the latest
driver. Make sure the driver is correctly installed on the
SQL Server box.
Sometimes those issues can be related to issues specific to
the driver and interactions with service accounts. Try
logging onto the server using the service account for SQL
Server and install the driver while logged in under the
service account SQL Server is running under.
-Sue
On Thu, 28 Sep 2006 15:29:02 -0700, grasshopper
<nospam@.nospam.com> wrote:

>Hi All
>I have set up several linked SQL servers which work ok - but I'm having
>trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
>The ODBC points to an 'Alchemy' database and uses an Alchemy driver
>ironically i can set up linked tables in an Access DB, via ODBC - and I can
>pull the same data into Excel via ODBC - so there is no problem connecting
to
>Alchemy using other MS apps.
>When I add the linked server in SQL, it appears to be added but no tables
>are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
>returns an error "Cannot initialize the data source object of OLE DB provid
er
>'MSDASQL' "
>Its driving me crazy...Anyone have any ideas'
>Thanks in advance

server via ODBC - help!

Hi All
I have set up several linked SQL servers which work ok - but I'm having
trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
The ODBC points to an 'Alchemy' database and uses an Alchemy driver
ironically i can set up linked tables in an Access DB, via ODBC - and I can
pull the same data into Excel via ODBC - so there is no problem connecting to
Alchemy using other MS apps.
When I add the linked server in SQL, it appears to be added but no tables
are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
returns an error "Cannot initialize the data source object of OLE DB provider
'MSDASQL' "
Its driving me crazy...Anyone have any ideas?
Thanks in advance
Make sure the driver is compatible with the MDAC version on
the SQL Server box. Make sure you are using the latest
driver. Make sure the driver is correctly installed on the
SQL Server box.
Sometimes those issues can be related to issues specific to
the driver and interactions with service accounts. Try
logging onto the server using the service account for SQL
Server and install the driver while logged in under the
service account SQL Server is running under.
-Sue
On Thu, 28 Sep 2006 15:29:02 -0700, grasshopper
<nospam@.nospam.com> wrote:

>Hi All
>I have set up several linked SQL servers which work ok - but I'm having
>trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
>The ODBC points to an 'Alchemy' database and uses an Alchemy driver
>ironically i can set up linked tables in an Access DB, via ODBC - and I can
>pull the same data into Excel via ODBC - so there is no problem connecting to
>Alchemy using other MS apps.
>When I add the linked server in SQL, it appears to be added but no tables
>are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
>returns an error "Cannot initialize the data source object of OLE DB provider
>'MSDASQL' "
>Its driving me crazy...Anyone have any ideas?
>Thanks in advance

server to Oracle on Unix

Hi all!
I have a SQL Server from which I need to query a view in a Oracle database.
I have created a linked server connection to the Oracle machine. Oracle,
version 9i, is running on Unix.
Now when I try to use the linked server connection I have some problems.
When I expand the linked server and try to see the tables and views I
sometimes get an error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Sometimes everything works just fine and sometimes I get the error message
above. When the developers are trying to query the Oracle view they
sometimes get the same error and sometimes the queries works just fine.
The thing is that I don't know anything about Unix so I don't know if there
is something I can do on that side.
Any help or suggestions would be greatly appreciated.A few things you can try:
When the developers are querying Oracle, they can turn on
trace flag 7300 to get a more detailed error message. In
Query Analyzer, execute the following:
Dbcc traceon (7300,3604)
and then try executing the queries against Oracle.
Also, pay attention to the time it takes to execute the
queries when they fail and when they are successful. You
could be experiencing remote query timeouts. You can try
increasing the setting using sp_configure. You can find more
information in books online on using sp_configure.
You will also want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Tue, 16 Nov 2004 12:18:31 +0100, "Jaana"
<jaana.lehtonen@.banverket.se> wrote:

>Hi all!
>I have a SQL Server from which I need to query a view in a Oracle database.
>I have created a linked server connection to the Oracle machine. Oracle,
>version 9i, is running on Unix.
>Now when I try to use the linked server connection I have some problems.
>When I expand the linked server and try to see the tables and views I
>sometimes get an error message.
>Error 7399: OLE DB provider 'MSDAORA' reported an error.
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>Sometimes everything works just fine and sometimes I get the error message
>above. When the developers are trying to query the Oracle view they
>sometimes get the same error and sometimes the queries works just fine.
>The thing is that I don't know anything about Unix so I don't know if there
>is something I can do on that side.
>Any help or suggestions would be greatly appreciated.
>
>

server to Oracle on Unix

Hi all!
I have a SQL Server from which I need to query a view in a Oracle database.
I have created a linked server connection to the Oracle machine. Oracle,
version 9i, is running on Unix.
Now when I try to use the linked server connection I have some problems.
When I expand the linked server and try to see the tables and views I
sometimes get an error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Sometimes everything works just fine and sometimes I get the error message
above. When the developers are trying to query the Oracle view they
sometimes get the same error and sometimes the queries works just fine.
The thing is that I don't know anything about Unix so I don't know if there
is something I can do on that side.
Any help or suggestions would be greatly appreciated.
A few things you can try:
When the developers are querying Oracle, they can turn on
trace flag 7300 to get a more detailed error message. In
Query Analyzer, execute the following:
Dbcc traceon (7300,3604)
and then try executing the queries against Oracle.
Also, pay attention to the time it takes to execute the
queries when they fail and when they are successful. You
could be experiencing remote query timeouts. You can try
increasing the setting using sp_configure. You can find more
information in books online on using sp_configure.
You will also want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Tue, 16 Nov 2004 12:18:31 +0100, "Jaana"
<jaana.lehtonen@.banverket.se> wrote:

>Hi all!
>I have a SQL Server from which I need to query a view in a Oracle database.
>I have created a linked server connection to the Oracle machine. Oracle,
>version 9i, is running on Unix.
>Now when I try to use the linked server connection I have some problems.
>When I expand the linked server and try to see the tables and views I
>sometimes get an error message.
>Error 7399: OLE DB provider 'MSDAORA' reported an error.
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>Sometimes everything works just fine and sometimes I get the error message
>above. When the developers are trying to query the Oracle view they
>sometimes get the same error and sometimes the queries works just fine.
>The thing is that I don't know anything about Unix so I don't know if there
>is something I can do on that side.
>Any help or suggestions would be greatly appreciated.
>
>