Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the error
.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "test2".
Can anyone tell me what I am doing wrong? Thanks!Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just fo
r
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:

> Hello, I am trying to create a linked server to an Excel file (SQL 2005).
I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the err
or.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>

Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "test2".
Can anyone tell me what I am doing wrong? Thanks!
Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just for
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:

> Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the error.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>

Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "test2".
Can anyone tell me what I am doing wrong? Thanks!Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just for
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:
> Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the error.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>

Creating an ADSI Linked Server

I am having quite a bit of difficulty setting up a linked server with ADSI.
I have used the code list in the various MSDN and BOL articles
(sp_addlinkedserver...) but any queries return the following error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
I know I am missing some key piece in my linked server set up here. Any
information would be appreciated.
Chris Whinihan
chrisw@.hma.regence.comHi Chris
There are quite a few posts for this error message!
http://tinyurl.com/b9fcf
Have specified created a mapping for the logins using sp_addlinkedsrvlogin?
John
"Chris Whinihan" wrote:

> I am having quite a bit of difficulty setting up a linked server with ADSI
.
> I have used the code list in the various MSDN and BOL articles
> (sp_addlinkedserver...) but any queries return the following error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepar
e
> returned 0x80040e14].
> I know I am missing some key piece in my linked server set up here. Any
> information would be appreciated.
> Chris Whinihan
> chrisw@.hma.regence.com

Creating a View linked to a VFP table

I have a SQL database and I need to link to a VFP table. I have been told
that a view is what I am needing.
Can someone lead me in the right direction of creating and using a view to a
VFP table?
Hi Preacher,
Lee's mentioned a "Local" view but I have a different take on what you're
after.
Are you working in VFP and want to get data from an SQL Server database? You
would use a VFP Remote View for that. Once you've USEd the view it will
behave like a Fox table and you can do things like indexing it and setting a
relation to other open Fox tables or cursors.
Or, are you working in SQL Server and wanting to open a Fox table as if it's
a SQL Server table? For that you need a SQL Server "Linked Server." To do
this you'll need to have the VFP OLE DB data provider installed. It installs
with VFP, and is also downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates.
The actual setup differs a little by which version of the SQL Server IDE
you're working in, but in SQL 2005 you can do this:
Open your SQL Server in the Object Explorer. Navigate down the tree to
Server Objects > Linked Servers. Verify that VFPOLEDB is included in the
Providers list. Right-click and choose New Linked Server... . In the dialog
that comes up fill in something like the following:
Linked Server: MyLinkedServer
Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro"
Product name: Visual FoxPro (I think this is optional)
Data source: "C:\Program Files\Microsoft Visual
FoxPro9\Samples\Northwind\Northwind.dbc" (Include quotes since there's
spaces in the string.)
Provider string: VFPOLEDB.1
Location: (blank)
Catalog: (blank)
Security page and Server Options page take defaults.
To select data from your linked server use the 4-part naming convention,
although the second and third items will be missing. Code looks like the
following:
Select * From MyLinkedServer...Customers
Select A.Field1*, B.Field1
From SomeSQLTable A
Left Join MyMyLinkedServer...Customers B On
A.Whatever = B.Whatever ......
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:eIl7%23U2VGHA.4308@.TK2MSFTNGP12.phx.gbl...
>I have a SQL database and I need to link to a VFP table. I have been told
> that a view is what I am needing.
> Can someone lead me in the right direction of creating and using a view to
> a
> VFP table?
>
|||That is correct. I am trying to pull from VFP into SQL. I tried your
instructions below, and I can't seem to follow them with SQL 2000 which is
what I am using. What needs to be different?
Thanks.
"Cindy Winegarden" <cindy_winegarden@.msn.com> wrote in message
news:%23MJpAW3VGHA.5232@.TK2MSFTNGP11.phx.gbl...
> Hi Preacher,
> Lee's mentioned a "Local" view but I have a different take on what you're
> after.
> Are you working in VFP and want to get data from an SQL Server database?
> You would use a VFP Remote View for that. Once you've USEd the view it
> will behave like a Fox table and you can do things like indexing it and
> setting a relation to other open Fox tables or cursors.
> Or, are you working in SQL Server and wanting to open a Fox table as if
> it's a SQL Server table? For that you need a SQL Server "Linked Server."
> To do this you'll need to have the VFP OLE DB data provider installed. It
> installs with VFP, and is also downloadable from
> msdn.microsoft.com/vfoxpro/downloads/updates.
> The actual setup differs a little by which version of the SQL Server IDE
> you're working in, but in SQL 2005 you can do this:
> Open your SQL Server in the Object Explorer. Navigate down the tree to
> Server Objects > Linked Servers. Verify that VFPOLEDB is included in the
> Providers list. Right-click and choose New Linked Server... . In the
> dialog that comes up fill in something like the following:
> Linked Server: MyLinkedServer
> Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro"
> Product name: Visual FoxPro (I think this is optional)
> Data source: "C:\Program Files\Microsoft Visual
> FoxPro9\Samples\Northwind\Northwind.dbc" (Include quotes since there's
> spaces in the string.)
> Provider string: VFPOLEDB.1
> Location: (blank)
> Catalog: (blank)
> Security page and Server Options page take defaults.
> To select data from your linked server use the 4-part naming convention,
> although the second and third items will be missing. Code looks like the
> following:
> Select * From MyLinkedServer...Customers
> Select A.Field1*, B.Field1
> From SomeSQLTable A
> Left Join MyMyLinkedServer...Customers B On
> A.Whatever = B.Whatever ......
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
> "Preacher Man" <nospam> wrote in message
> news:eIl7%23U2VGHA.4308@.TK2MSFTNGP12.phx.gbl...
>
|||Hi Preacher,
I'm really sorry I don't have the SQL 2000 IDE available to know how to walk
you through a Linked Server setup using the GUI interface. In any case you
can create it with code like this:
-- Master should be the active database
Use Master
Go
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND',
@.srvproduct=N'Visual FoxPro 9',
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"',
@.provstr=N'VFPOLEDB.1'
Did you know that you can use the SQL 2005 GUI with an SQL 2000 database?
Don't be confused by the OpenRowset(), OpenQuery() etc. methods used in the
second article Lee posted in the VFP group.
I despise them. I have to use them with some Oracle data I access. The
4-part naming convention (MyLinkedServer...Customers) is so much more in
keeping with the familiar format of Select statements.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:eTlGlQ$VGHA.4308@.TK2MSFTNGP12.phx.gbl...
> That is correct. I am trying to pull from VFP into SQL. I tried your
> instructions below, and I can't seem to follow them with SQL 2000 which is
> what I am using. What needs to be different?
sql

Creating a View linked to a VFP table

I have a SQL database and I need to link to a VFP table. I have been told
that a view is what I am needing.
Can someone lead me in the right direction of creating and using a view to a
VFP table?Hi Preacher,
Lee's mentioned a "Local" view but I have a different take on what you're
after.
Are you working in VFP and want to get data from an SQL Server database? You
would use a VFP Remote View for that. Once you've USEd the view it will
behave like a Fox table and you can do things like indexing it and setting a
relation to other open Fox tables or cursors.
Or, are you working in SQL Server and wanting to open a Fox table as if it's
a SQL Server table? For that you need a SQL Server "Linked Server." To do
this you'll need to have the VFP OLE DB data provider installed. It installs
with VFP, and is also downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates.
The actual setup differs a little by which version of the SQL Server IDE
you're working in, but in SQL 2005 you can do this:
Open your SQL Server in the Object Explorer. Navigate down the tree to
Server Objects > Linked Servers. Verify that VFPOLEDB is included in the
Providers list. Right-click and choose New Linked Server... . In the dialog
that comes up fill in something like the following:
Linked Server: MyLinkedServer
Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro"
Product name: Visual FoxPro (I think this is optional)
Data source: "C:\Program Files\Microsoft Visual
FoxPro9\Samples\Northwind\Northwind.dbc" (Include quotes since there's
spaces in the string.)
Provider string: VFPOLEDB.1
Location: (blank)
Catalog: (blank)
Security page and Server Options page take defaults.
To select data from your linked server use the 4-part naming convention,
although the second and third items will be missing. Code looks like the
following:
Select * From MyLinkedServer...Customers
Select A.Field1*, B.Field1
From SomeSQLTable A
Left Join MyMyLinkedServer...Customers B On
A.Whatever = B.Whatever ......
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:eIl7%23U2VGHA.4308@.TK2MSFTNGP12.phx.gbl...
>I have a SQL database and I need to link to a VFP table. I have been told
> that a view is what I am needing.
> Can someone lead me in the right direction of creating and using a view to
> a
> VFP table?
>|||That is correct. I am trying to pull from VFP into SQL. I tried your
instructions below, and I can't seem to follow them with SQL 2000 which is
what I am using. What needs to be different?
Thanks.
"Cindy Winegarden" <cindy_winegarden@.msn.com> wrote in message
news:%23MJpAW3VGHA.5232@.TK2MSFTNGP11.phx.gbl...
> Hi Preacher,
> Lee's mentioned a "Local" view but I have a different take on what you're
> after.
> Are you working in VFP and want to get data from an SQL Server database?
> You would use a VFP Remote View for that. Once you've USEd the view it
> will behave like a Fox table and you can do things like indexing it and
> setting a relation to other open Fox tables or cursors.
> Or, are you working in SQL Server and wanting to open a Fox table as if
> it's a SQL Server table? For that you need a SQL Server "Linked Server."
> To do this you'll need to have the VFP OLE DB data provider installed. It
> installs with VFP, and is also downloadable from
> msdn.microsoft.com/vfoxpro/downloads/updates.
> The actual setup differs a little by which version of the SQL Server IDE
> you're working in, but in SQL 2005 you can do this:
> Open your SQL Server in the Object Explorer. Navigate down the tree to
> Server Objects > Linked Servers. Verify that VFPOLEDB is included in the
> Providers list. Right-click and choose New Linked Server... . In the
> dialog that comes up fill in something like the following:
> Linked Server: MyLinkedServer
> Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro"
> Product name: Visual FoxPro (I think this is optional)
> Data source: "C:\Program Files\Microsoft Visual
> FoxPro9\Samples\Northwind\Northwind.dbc" (Include quotes since there's
> spaces in the string.)
> Provider string: VFPOLEDB.1
> Location: (blank)
> Catalog: (blank)
> Security page and Server Options page take defaults.
> To select data from your linked server use the 4-part naming convention,
> although the second and third items will be missing. Code looks like the
> following:
> Select * From MyLinkedServer...Customers
> Select A.Field1*, B.Field1
> From SomeSQLTable A
> Left Join MyMyLinkedServer...Customers B On
> A.Whatever = B.Whatever ......
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
> "Preacher Man" <nospam> wrote in message
> news:eIl7%23U2VGHA.4308@.TK2MSFTNGP12.phx.gbl...
>|||Hi Preacher,
I'm really sorry I don't have the SQL 2000 IDE available to know how to walk
you through a Linked Server setup using the GUI interface. In any case you
can create it with code like this:
-- Master should be the active database
Use Master
Go
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND',
@.srvproduct=N'Visual FoxPro 9',
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"',
@.provstr=N'VFPOLEDB.1'
Did you know that you can use the SQL 2005 GUI with an SQL 2000 database?
Don't be confused by the OpenRowset(), OpenQuery() etc. methods used in the
second article Lee posted in the VFP group.
I despise them. I have to use them with some Oracle data I access. The
4-part naming convention (MyLinkedServer...Customers) is so much more in
keeping with the familiar format of Select statements.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:eTlGlQ$VGHA.4308@.TK2MSFTNGP12.phx.gbl...
> That is correct. I am trying to pull from VFP into SQL. I tried your
> instructions below, and I can't seem to follow them with SQL 2000 which is
> what I am using. What needs to be different?

Creating a view from a linked server

Hi
i have created a new database and a new linked server that points to an
AccessDB using an ODBC DSN.
Now inside that new sql db i have create i need to created a new view so
i open EM went to views and paste the following
select * from openquery (AccessLinkedServer,'select * from mytable')
i press run and i see the data ok .but when i try to save the view i get
the following
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [DataDirect][ODBC dBase driver]Optional feature not
implemented.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
0x8004d00a].
*** Sent via Developersdex http://www.examnotes.net ***try creating the view via query analyzer. there are all kinds of funky
issues with em.
-oj
"tolisss" <nospam@.devdex.com> wrote in message
news:e7H8X6ZRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>
> Hi
> i have created a new database and a new linked server that points to an
> AccessDB using an ODBC DSN.
> Now inside that new sql db i have create i need to created a new view so
> i open EM went to views and paste the following
> select * from openquery (AccessLinkedServer,'select * from mytable')
> i press run and i see the data ok .but when i try to save the view i get
> the following
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: [DataDirect][ODBC dBase driver]Optional feature not
> implemented.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
> [OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
> 0x8004d00a].
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Creating a view from a linked server

Hi

i have created a new database and a new linked server that points to an
AccessDB using an ODBC DSN.
Now inside that new sql db i have create i need to created a new view so
i open EM went to views and paste the following

select * from openquery (AccessLinkedServer,'select * from mytable')

i press run and i see the data ok .but when i try to save the view i get
the following

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [DataDirect][ODBC dBase driver]Optional feature not
implemented.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
0x8004d00a].

*** Sent via Developersdex http://www.developersdex.com ***tolisss (nospam@.devdex.com) writes:
> i have created a new database and a new linked server that points to an
> AccessDB using an ODBC DSN.
> Now inside that new sql db i have create i need to created a new view so
> i open EM went to views and paste the following
> select * from openquery (AccessLinkedServer,'select * from mytable')
> i press run and i see the data ok .but when i try to save the view i get
> the following
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.

Try creating the view from Query Analyzer. I seem to recall
that when you create a view from Enterprise Manager, it starts a
transaction. Later when you come to create the view itself, the OLE DB
provider cannot upgrade the transaction to a distributed one.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating a Trigger which updates a linked server

Hi all,
I read some message in FAQ about my problem but i doesn't work any
more...
I have 2 SQL-Servers that use sql-server and windows security
integrity.
On first Server, i add a linkServer to the other by using
sp_addlinkedserver and use a specified user ...
So i can use select command, execute store procedure, update , insert
and any command to read,update or write data .
I said Coool :-) I could put arrival data from one database to the
other database by using a trigger !!!
Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
When the trigger is firing, and only when i want to execute the insert
command, anything happen ... Also the @.@.error don't work...
Let me show what happen in the trigger :
CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
AFTER INSERT
AS
DECLARE ...
SET XACT_ABORT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET implicit_transactions off
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT ...
FROM OPENXML (@.idoc, '/EXECID',2)
WITH ( ... )
EXEC sp_xml_removedocument @.idoc
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
TOTO')
SET @.v_StrMess = 'The problem is HERE ...'
--raiserror (@.v_StrMess , 16 , 1)
COMMIT TRAN
if @.@.error >0
BEGIN
raiserror (@.v_StrMess , 16 , 1)
END
SET implicit_transactions on
END
I know that it's possible to update a linkedserver whith a trigger but
i don't know how !!!
Please, could you explain in good word how to do it or is there
another solution ?
Thx for answers ...
ByeWhat is the error you are getting?
AMB
"Mick" wrote:

> Hi all,
> I read some message in FAQ about my problem but i doesn't work any
> more...
> I have 2 SQL-Servers that use sql-server and windows security
> integrity.
> On first Server, i add a linkServer to the other by using
> sp_addlinkedserver and use a specified user ...
> So i can use select command, execute store procedure, update , insert
> and any command to read,update or write data .
> I said Coool :-) I could put arrival data from one database to the
> other database by using a trigger !!!
> Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
> When the trigger is firing, and only when i want to execute the insert
> command, anything happen ... Also the @.@.error don't work...
> Let me show what happen in the trigger :
> CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
> AFTER INSERT
> AS
> DECLARE ...
> SET XACT_ABORT ON
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> SET implicit_transactions off
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT statement using OPENXML rowset provider.
> SELECT ...
> FROM OPENXML (@.idoc, '/EXECID',2)
> WITH ( ... )
> EXEC sp_xml_removedocument @.idoc
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
> TOTO')
> SET @.v_StrMess = 'The problem is HERE ...'
> --raiserror (@.v_StrMess , 16 , 1)
> COMMIT TRAN
> if @.@.error >0
> BEGIN
> raiserror (@.v_StrMess , 16 , 1)
> END
> SET implicit_transactions on
>
> END
>
>
> I know that it's possible to update a linkedserver whith a trigger but
> i don't know how !!!
> Please, could you explain in good word how to do it or is there
> another solution ?
> Thx for answers ...
> Bye
>|||@.@.Error is only captured for the very LAST executed statement. In your case,
you would always be 0.
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
SET @.v_StrMess = 'The problem is HERE ...' -- <++++You reset the
@.@.error value here
You should change it to this to capture the @.@.error and set your @.msg
declare @.err int
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
SELECT @.err=@.@.error, @.v_StrMess = 'The problem is HERE ...'
IF @.err=0
COMMIT
ELSE
BEGIN
ROLLBACK
raiserror (@.v_StrMess , 16 , 1)
END
-oj
"Mick" <mickymickmc@.yahoo.fr> wrote in message
news:79cab08b.0502151254.3b8d3d9a@.posting.google.com...
> Hi all,
> I read some message in FAQ about my problem but i doesn't work any
> more...
> I have 2 SQL-Servers that use sql-server and windows security
> integrity.
> On first Server, i add a linkServer to the other by using
> sp_addlinkedserver and use a specified user ...
> So i can use select command, execute store procedure, update , insert
> and any command to read,update or write data .
> I said Coool :-) I could put arrival data from one database to the
> other database by using a trigger !!!
> Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
> When the trigger is firing, and only when i want to execute the insert
> command, anything happen ... Also the @.@.error don't work...
> Let me show what happen in the trigger :
> CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
> AFTER INSERT
> AS
> DECLARE ...
> SET XACT_ABORT ON
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> SET implicit_transactions off
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT statement using OPENXML rowset provider.
> SELECT ...
> FROM OPENXML (@.idoc, '/EXECID',2)
> WITH ( ... )
> EXEC sp_xml_removedocument @.idoc
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
> TOTO')
> SET @.v_StrMess = 'The problem is HERE ...'
> --raiserror (@.v_StrMess , 16 , 1)
> COMMIT TRAN
> if @.@.error >0
> BEGIN
> raiserror (@.v_StrMess , 16 , 1)
> END
> SET implicit_transactions on
>
> END
>
>
> I know that it's possible to update a linkedserver whith a trigger but
> i don't know how !!!
> Please, could you explain in good word how to do it or is there
> another solution ?
> Thx for answers ...
> Bye|||Thx for your answer ...
But the problem is not the @.@.error message or else !!!
The problem is that when the insert instruction start, nothing happen
...
I run a trace to understand what sql-server that run trigger do but
when the BEGIN DISTRIBUTED TRANSACTION start , there is the end of
transaction and nothing else.
For example, when i make an insert into srvdb1 by using sql-query
analyser, the trigger start and i wait until the connection is
broken...
i will test something and go back later ...
"oj" <nospam_ojngo@.home.com> wrote in message news:<eruW3o7EFHA.2176@.TK2MSFTNGP15.phx.gbl>.
.
> @.@.Error is only captured for the very LAST executed statement. In your cas
e,
> you would always be 0.
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
> SET @.v_StrMess = 'The problem is HERE ...' -- <++++You reset the
> @.@.error value here
> You should change it to this to capture the @.@.error and set your @.msg
> declare @.err int
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
> SELECT @.err=@.@.error, @.v_StrMess = 'The problem is HERE ...'
> IF @.err=0
> COMMIT
> ELSE
> BEGIN
> ROLLBACK
> raiserror (@.v_StrMess , 16 , 1)
> END
>
> --
> -oj
>
> "Mick" <mickymickmc@.yahoo.fr> wrote in message
> news:79cab08b.0502151254.3b8d3d9a@.posting.google.com...

Creating a table from one server to another

I have two server Server1 and Server2
I need to create a table in Server2 from Server1
I do have a linked server
please advice how this can be done
Thanks
samay
The quickest and easiest way is to use DTS.
In SEM, expand your database node.
Right-click on the database you wish to import the table to. Choose
All-Tasks, Import Data.
Follow the wizard through. You can import just the table structure, or both
the structure and the data.
Note: If you just want the structure, using the Query Analyzer, find the
table in Server1, right-click and have it create the CREATE statement for
you. Copy and paste that statement in the query analyzer (choose the
appropriate database and server *before* you run the script.
Rick
RICHARD SAWTELL
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>
|||Number of options available..... I'd use DTS
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>
|||I am using the Script to backup data. and I was wondering if instead of 1st careting a table in Server2 and then the calculated script if i can create atable + insert the required records in it in one script.Is this posiible
PLease advice
2....another Question
Right now I am creating a tabl in server 2 and updating it from Server 1
and when i try to Update the Server2 Table A it gives me this Error
Could not open table "LinkedServer."TABLEA"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID
VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Please advice
"Simon" wrote:

> Number of options available..... I'd use DTS
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
>
>
|||Yes, if you want to do it that way. Build the empty table and then refer
to the populated table
from the linked server.
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:739516F9-E466-4DDE-8866-AD73BBB5E29D@.microsoft.com...
> I am using the Script to backup data. and I was wondering if instead of
1st careting a table in Server2 and then the calculated script if i can
create atable + insert the required records in it in one script.Is this
posiible
> PLease advice
> 2....another Question
> Right now I am creating a tabl in server 2 and updating it from Server 1
> and when i try to Update the Server2 Table A it gives me this Error
> Could not open table "LinkedServer."TABLEA"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was
done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...[vbcol=seagreen]
> Please advice
> "Simon" wrote:
<KritiVermahotmailcom@.discussions.microsoft.com> [vbcol=seagreen]
news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...[vbcol=seagreen]

Creating a table from one server to another

I have two server Server1 and Server2
I need to create a table in Server2 from Server1
I do have a linked server
please advice how this can be done
Thanks
samayThe quickest and easiest way is to use DTS.
In SEM, expand your database node.
Right-click on the database you wish to import the table to. Choose
All-Tasks, Import Data.
Follow the wizard through. You can import just the table structure, or both
the structure and the data.
Note: If you just want the structure, using the Query Analyzer, find the
table in Server1, right-click and have it create the CREATE statement for
you. Copy and paste that statement in the query analyzer (choose the
appropriate database and server *before* you run the script.
Rick
RICHARD SAWTELL
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>|||Number of options available..... I'd use DTS
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>|||Yes, if you want to do it that way. Build the empty table and then refer
to the populated table
from the linked server.
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:739516F9-E466-4DDE-8866-AD73BBB5E29D@.microsoft.com...
> I am using the Script to backup data. and I was wondering if instead of
1st careting a table in Server2 and then the calculated script if i can
create atable + insert the required records in it in one script.Is this
posiible
> PLease advice
> 2....another Question
> Right now I am creating a tabl in server 2 and updating it from Server 1
> and when i try to Update the Server2 Table A it gives me this Error
> Could not open table "LinkedServer."TABLEA"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was
done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Please advice
> "Simon" wrote:
> > Number of options available..... I'd use DTS
> >
> > "KritiVerma@.hotmail.com"
<KritiVermahotmailcom@.discussions.microsoft.com>
> > wrote in message
news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> > > I have two server Server1 and Server2
> > >
> > > I need to create a table in Server2 from Server1
> > > I do have a linked server
> > >
> > > please advice how this can be done
> > >
> > > Thanks
> > > samay
> > >
> >
> >
> >

Creating a table from one server to another

I have two server Server1 and Server2
I need to create a table in Server2 from Server1
I do have a linked server
please advice how this can be done
Thanks
samayThe quickest and easiest way is to use DTS.
In SEM, expand your database node.
Right-click on the database you wish to import the table to. Choose
All-Tasks, Import Data.
Follow the wizard through. You can import just the table structure, or both
the structure and the data.
Note: If you just want the structure, using the Query Analyzer, find the
table in Server1, right-click and have it create the CREATE statement for
you. Copy and paste that statement in the query analyzer (choose the
appropriate database and server *before* you run the script.
Rick
RICHARD SAWTELL
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>|||Number of options available..... I'd use DTS
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...
> I have two server Server1 and Server2
> I need to create a table in Server2 from Server1
> I do have a linked server
> please advice how this can be done
> Thanks
> samay
>|||I am using the Script to backup data. and I was wondering if instead of 1st
careting a table in Server2 and then the calculated script if i can create
atable + insert the required records in it in one script.Is this posiible
PLease advice
2....another Question
Right now I am creating a tabl in server 2 and updating it from Server 1
and when i try to Update the Server2 Table A it gives me this Error
Could not open table "LinkedServer."TABLEA"' from OLE DB provider 'SQLOLEDB'
. The provider could not support a row lookup position. The provider indica
tes that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation genera
ted errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset r
eturned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPST
ATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROP
STATUS_OK], [PROPID=Unknown PropertyID
VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=T
rue STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Please advice
"Simon" wrote:

> Number of options available..... I'd use DTS
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com..
.
>
>|||Yes, if you want to do it that way. Build the empty table and then refer
to the populated table
from the linked server.
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:739516F9-E466-4DDE-8866-AD73BBB5E29D@.microsoft.com...
> I am using the Script to backup data. and I was wondering if instead of
1st careting a table in Server2 and then the calculated script if i can
create atable + insert the required records in it in one script.Is this
posiible
> PLease advice
> 2....another Question
> Right now I am creating a tabl in server 2 and updating it from Server 1
> and when i try to Update the Server2 Table A it gives me this Error
> Could not open table "LinkedServer."TABLEA"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was
done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=60
0
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...[vbcol=seagreen]
> Please advice
> "Simon" wrote:
>
<KritiVermahotmailcom@.discussions.microsoft.com>[vbcol=seagreen]
news:BC2D8A71-94FE-41CC-A96A-AC6B8A040595@.microsoft.com...[vbcol=seagreen]

Creating a shared ODBC dsn for Access to SQL Server connection

I have a Microsoft Access application which uses linked SQL Server tables. I would like to create an ODBC DSN which would be available to all users so that I don't have to create a DSN on each machine. Can this be done? The Access application resides on a shared drive (Windows). Thanks for your help.

Don,

Will a File DSN work for what you are trying to do? I don't have much experience with those, so I can't help you more in that area.

I too had a similar problem as you do, and I didn't want to go through manually creating a DSN on everybody's machine. I found this script that will make your life slightly easier:

http://www.enterpriseitplanet.com/resources/scripts_win/article.php/3089341

With a little modification to the example included in that web page, I had all my users that needed a DSN ready to rock within a mouse-click!

Thanks,

Chuck

|||Thanks, Chuck. The script will certainly make the process easier if there isn't a way to create a global ODBC DSN. Appreciate the help.

Creating a linked server to DB2 from SQl server 2000 64bit

Hi,
I am trying to create a linked server to DB2 using IBM
64 bit OLEDB provider, as Microsoft doesn't have a DB2
OLEDB driver for 64bit.
When I test the connection to DB2 using the driver, it
works, but when I try to query DB2 using a OPENQUERY it
gives me this message.
"Error 7399:OLEDB provider 'IBMDADB2' reported an error.
Authentication Failed".
The userID I am using in the linked server properties, has
access to DB2 tables.
Is there anyway I could trace, to see where the connection
is failing.
I would really appreciate if someone could help me with
this.
Please let me know if I am posting on the wrong Newsgroup.
Thanks
Geetha
You can turn on a trace flag to try to get additional
information on the 7399 error. Execute
dbcc traceon(7300,3604)
and then run the query. You could also use profiler and
capture the OLEDB Errors event.
-Sue
On Tue, 6 Jul 2004 14:56:14 -0700, "Geetha Dabbara"
<gdabbara@.brownshoe.com> wrote:

>Hi,
> I am trying to create a linked server to DB2 using IBM
>64 bit OLEDB provider, as Microsoft doesn't have a DB2
>OLEDB driver for 64bit.
>When I test the connection to DB2 using the driver, it
>works, but when I try to query DB2 using a OPENQUERY it
>gives me this message.
>"Error 7399:OLEDB provider 'IBMDADB2' reported an error.
>Authentication Failed".
>The userID I am using in the linked server properties, has
>access to DB2 tables.
>Is there anyway I could trace, to see where the connection
>is failing.
>I would really appreciate if someone could help me with
>this.
>Please let me know if I am posting on the wrong Newsgroup.
>Thanks
>Geetha
|||When I set the trace. I see this error message.
Server: Msg 7403, Level 16, State 1, Line 1
Could not locate registry entry for OLE DB
provider 'IBMDADB2.1'.
OLE DB error trace [Non-interface error: Provider not
registered.].
I can see 'IBMDADB2.1' in the registry. How can I register
the provider? Thanks for your help.
-Geetha.
[vbcol=seagreen]
>--Original Message--
>You can turn on a trace flag to try to get additional
>information on the 7399 error. Execute
>dbcc traceon(7300,3604)
>and then run the query. You could also use profiler and
>capture the OLEDB Errors event.
>-Sue
>On Tue, 6 Jul 2004 14:56:14 -0700, "Geetha Dabbara"
><gdabbara@.brownshoe.com> wrote:
IBM[vbcol=seagreen]
has[vbcol=seagreen]
connection[vbcol=seagreen]
Newsgroup.
>.
>
|||Try reinstalling the DB2 client. You could try just
unregistering and reregistering the dll for the provider
using regsvr32.exe but it might be safer to just reinstall
the client in case other files didn't get registered or
installed correctly.
-Sue
On Thu, 8 Jul 2004 08:47:37 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>When I set the trace. I see this error message.
>Server: Msg 7403, Level 16, State 1, Line 1
>Could not locate registry entry for OLE DB
>provider 'IBMDADB2.1'.
>OLE DB error trace [Non-interface error: Provider not
>registered.].
>I can see 'IBMDADB2.1' in the registry. How can I register
>the provider? Thanks for your help.
>-Geetha.
>IBM
>has
>connection
>Newsgroup.

Creating a linked server to DB2 from SQl server 2000 64bit

Hi,
I am trying to create a linked server to DB2 using IBM
64 bit OLEDB provider, as Microsoft doesn't have a DB2
OLEDB driver for 64bit.
When I test the connection to DB2 using the driver, it
works, but when I try to query DB2 using a OPENQUERY it
gives me this message.
"Error 7399:OLEDB provider 'IBMDADB2' reported an error.
Authentication Failed".
The userID I am using in the linked server properties, has
access to DB2 tables.
Is there anyway I could trace, to see where the connection
is failing.
I would really appreciate if someone could help me with
this.
Please let me know if I am posting on the wrong Newsgroup.
Thanks
GeethaYou can turn on a trace flag to try to get additional
information on the 7399 error. Execute
dbcc traceon(7300,3604)
and then run the query. You could also use profiler and
capture the OLEDB Errors event.
-Sue
On Tue, 6 Jul 2004 14:56:14 -0700, "Geetha Dabbara"
<gdabbara@.brownshoe.com> wrote:

>Hi,
> I am trying to create a linked server to DB2 using IBM
>64 bit OLEDB provider, as Microsoft doesn't have a DB2
>OLEDB driver for 64bit.
>When I test the connection to DB2 using the driver, it
>works, but when I try to query DB2 using a OPENQUERY it
>gives me this message.
>"Error 7399:OLEDB provider 'IBMDADB2' reported an error.
>Authentication Failed".
>The userID I am using in the linked server properties, has
>access to DB2 tables.
>Is there anyway I could trace, to see where the connection
>is failing.
>I would really appreciate if someone could help me with
>this.
>Please let me know if I am posting on the wrong Newsgroup.
>Thanks
>Geetha|||When I set the trace. I see this error message.
Server: Msg 7403, Level 16, State 1, Line 1
Could not locate registry entry for OLE DB
provider 'IBMDADB2.1'.
OLE DB error trace [Non-interface error: Provider not
registered.].
I can see 'IBMDADB2.1' in the registry. How can I register
the provider? Thanks for your help.
-Geetha.

>--Original Message--
>You can turn on a trace flag to try to get additional
>information on the 7399 error. Execute
>dbcc traceon(7300,3604)
>and then run the query. You could also use profiler and
>capture the OLEDB Errors event.
>-Sue
>On Tue, 6 Jul 2004 14:56:14 -0700, "Geetha Dabbara"
><gdabbara@.brownshoe.com> wrote:
>
IBM[vbcol=seagreen]
has[vbcol=seagreen]
connection[vbcol=seagreen]
Newsgroup.[vbcol=seagreen]
>.
>|||Try reinstalling the DB2 client. You could try just
unregistering and reregistering the dll for the provider
using regsvr32.exe but it might be safer to just reinstall
the client in case other files didn't get registered or
installed correctly.
-Sue
On Thu, 8 Jul 2004 08:47:37 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>When I set the trace. I see this error message.
>Server: Msg 7403, Level 16, State 1, Line 1
>Could not locate registry entry for OLE DB
>provider 'IBMDADB2.1'.
>OLE DB error trace [Non-interface error: Provider not
>registered.].
>I can see 'IBMDADB2.1' in the registry. How can I register
>the provider? Thanks for your help.
>-Geetha.
>
>IBM
>has
>connection
>Newsgroup.|||hi,
even i am getting the error like
OLE DB error trace[Non-interface error:Provider not registered.].
Could not locate registry entry for OLE DB provider
'provider=Microsoft.Jet.OLEDB.4.0'.
Deferred prepare could not be completed.
As sue said try to reinstall the DB2 client. is it possible to
reinstall the oledb driver. how can i do it? where can i view the
registry files ? please help me its very urgent?
thans shybi
shybi
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message836063.html|||The provider is installed with MDAC. You can download MDAC
versions for reinstall and also download component checker
to check the MDAC version from:
http://msdn.microsoft.com/data/ref/mdac/downloads/
Using Component Checker is a good way to check your MDAC
installation - easier than going through the registry.
Component Checker will check for mismatches.
-Sue
On Tue, 1 Aug 2006 01:06:46 -0500, shybi
<shybi.2buky9@.mail.mcse.ms> wrote:

>hi,
>even i am getting the error like
>OLE DB error trace[Non-interface error:Provider not registered.].
>Could not locate registry entry for OLE DB provider
>'provider=Microsoft.Jet.OLEDB.4.0'.
>Deferred prepare could not be completed.
>As sue said try to reinstall the DB2 client. is it possible to
>reinstall the oledb driver. how can i do it? where can i view the
>registry files ? please help me its very urgent?
>thans shybi

Creating a Linked Server to AS400 in SQL Server 2005

I am trying to create a linked server in SQL Server 2005 to show tables in our AS400. I made the connection, however, the tables are not showing up under the Linked Server name.

How do you get the linked tables to display in the Linked Server folder?

David

Are you sure you did provide the right catalog ? If you don't have access rights to the catalog or to specific objcts you also won't see them in the list. Make sure that the user associated with the linked server has the appropiate rights to see the objects.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I am trying almost the same thing. But I am not even able to create the connection to the server.

I tried it like this:

Provider: Microsoft OLE DB Provider for ODBC Driver

Product name: IBMDASQL ?

Data source: MyAS400

Catalog: MyCatalog

And I create a local login.

When I try to access the database with a query I get the error: can not start the connection. Do you know what is wrong with my Link Server

|||

I downloaded a new driver from the Microsoft website: Microsoft OLEDB Provider for DB2

I had better success with this:

When setting up the connection, I used the following settings:

Settings:

Data Source (Enter IP address of AS/400)

NetworkTCP/IP Connect…. (Enter IP address of AS/400)

Single sign on is unchecked

Username

Pw

Initial Catalog : AS/400 System name (from the sign on screen)

Package: QSYS

Default Schema: (Enter name of Library where data is located )

Advanced Tab: DB2/400

Hope this helps.

David

Creating a Linked Server to AS400 in SQL Server 2005

I am trying to create a linked server in SQL Server 2005 to show tables in our AS400. I made the connection, however, the tables are not showing up under the Linked Server name.

How do you get the linked tables to display in the Linked Server folder?

David

Are you sure you did provide the right catalog ? If you don't have access rights to the catalog or to specific objcts you also won't see them in the list. Make sure that the user associated with the linked server has the appropiate rights to see the objects.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I am trying almost the same thing. But I am not even able to create the connection to the server.

I tried it like this:

Provider: Microsoft OLE DB Provider for ODBC Driver

Product name: IBMDASQL ?

Data source: MyAS400

Catalog: MyCatalog

And I create a local login.

When I try to access the database with a query I get the error: can not start the connection. Do you know what is wrong with my Link Server

|||

I downloaded a new driver from the Microsoft website: Microsoft OLEDB Provider for DB2

I had better success with this:

When setting up the connection, I used the following settings:

Settings:

Data Source (Enter IP address of AS/400)

NetworkTCP/IP Connect…. (Enter IP address of AS/400)

Single sign on is unchecked

Username

Pw

Initial Catalog : AS/400 System name (from the sign on screen)

Package: QSYS

Default Schema: (Enter name of Library where data is located )

Advanced Tab: DB2/400

Hope this helps.

David

Creating a Linked Server to Access DB

I'm trying to create a linked server to an Access database that resides on a separate machine.

On my PC, I can create a link to the Access db, and view, update, add and delete data. If I create the same linked server on our production Server, I can again view, update, add and delete data. All's well so far.

If I now go back to my own PC (used for developing) I cannot access the linked server on the production machine.

The Access database is stored on a separate PC, so the I'm linking to a remote db. As I said this works fine if I'm sat in front of the PC that the linked server is created on - but not if I use a client PC to connect.

I create the linked server using the following command:
exec sp_addlinkedserver
@.server = 'AccLinkedServer',
@.provider = 'Microsoft.jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = '\\DatabaseServer\AccessDatabase.mdb'

If I run the SQL statement:

SELECT * FROM AccLinkedServer...AnyTableYouLike

I get this error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\DatabaseServer\AccessDatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

I've searched in the MS knowledge base, and this forum and followed all of the advice that was available, but it still won't work.

Anyone got any other advice, before I go totally insane with this.

Cheers.But the linked server is registered with the sql server instance...

This doesn't make sense...are you sure you're pointing to the same instance...

can you see the linked server in EM?|||Originally posted by Brett Kaiser
But the linked server is registered with the sql server instance...

This doesn't make sense...are you sure you're pointing to the same instance...

can you see the linked server in EM?

A bit more detail may help explain. We have a server (MainServer) which runs SBS and SQL. We have another PC which we use as a server (DatabaseServer).

I'm developing on my PC (DevelopmentPC) with a copy of SQL Developer and VB6.

I have created on my PC a linked server (AccLinkedServer) to the DatabaseServer, which allows me full access to this.

I can also create a linked server (AccLinkedServer) on the MainServer to the same data, and also have full access to the data from the MainServer.

If I try to run the command select * from AccLinkedServer...tblDataTable (where AccLinkedServer is the MainServer version) from Query Analyser on my PC I get the error shown in my original post.

I would like to be able to access the data we store on DatabaseServer from within SQL/VB - it would save a lot of time for people.

Creating a linked server to a server with a dash in the name...

I need to create a linked server to a SQL 2005 server (B) from another SQL 2005 server (A). Problem is that Server B's name has a dash in it, and SQL Server doesn't like dashes in the name when running a query against the linked server. Server B's network name is Server-B. For example, from Mgt Studio on Server A, I've created a link to Server B whose name is Server-B, so in my query from Server A, I issue: SELECT * FROM Server-B, and get the following error message:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.

Any ideas,

Thnx

Roz

Try

SELECT * FROM [Server-B].....

HTH!

|||

This worked perfectly. So simple.

Thnx

Roz

creating a linked server

I am trying to use linked servers as a means of accessing files in an Access
database. I have unsuccessfully tried 2 methods:
1) using sp_addlinkedserver. I am told that I cannot create a linked server
for Access tables, unless the Access file exists on the same server as SQL.
This seems odd to me. Is this true? Can I create a linked server for an
.mdb file that exists on a different server? And can I do this using
sp_addlinkedserver?
2) using OPENROWSET. I get an "Authentication failed" error message with
the following:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\temp\z.mdb';'';'', Untitled)
Do I need to specify some kind of security? What am I missing?
My knowledge on security issues is very limited. Any help on getting me in
the right direction would be appreciated. I am using SQL 8.00.194
Thanks
DionI would recommend looking at this thread in the
microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
just reviewed it there.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> I am trying to use linked servers as a means of accessing files in an
Access
> database. I have unsuccessfully tried 2 methods:
> 1) using sp_addlinkedserver. I am told that I cannot create a linked
server
> for Access tables, unless the Access file exists on the same server as
SQL.
> This seems odd to me. Is this true? Can I create a linked server for an
> .mdb file that exists on a different server? And can I do this using
> sp_addlinkedserver?
> 2) using OPENROWSET. I get an "Authentication failed" error message with
> the following:
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\temp\z.mdb';'';'', Untitled)
> Do I need to specify some kind of security? What am I missing?
> My knowledge on security issues is very limited. Any help on getting me
in
> the right direction would be appreciated. I am using SQL 8.00.194
> Thanks
> Dion|||So the Access file needs to be either on the local drive, or on the same
server as sql, right? Thanks much for the thread.
Dion
"Simon Worth" wrote:

> I would recommend looking at this thread in the
> microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". W
e
> just reviewed it there.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> Access
> server
> SQL.
> in
>
>|||The .mdb database file must reside on the server. data_source is evaluated
on the server, not the client, and the path must be valid on the server.
I didn't try it with the access db on the local drive. I assume that won't
work, as it's counter to what BOL says. But try it out, and see if it
works.
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...[vbcol=seagreen]
> So the Access file needs to be either on the local drive, or on the same
> server as sql, right? Thanks much for the thread.
> Dion
>
> "Simon Worth" wrote:
>
We[vbcol=seagreen]
for an[vbcol=seagreen]
with[vbcol=seagreen]
me[vbcol=seagreen]|||Sorry, Simon, but if I can ask one last question (my hardware knowledge is
pretty limited). When you say "database file must reside on the server,"
you mean the Access file needs to be on the SAME server as SQL, right? In
other words, my probelm is I am trying to access one server from another
server.
Thanks
Rick
"Simon Worth" wrote:

> The .mdb database file must reside on the server. data_source is evaluated
> on the server, not the client, and the path must be valid on the server.
> I didn't try it with the access db on the local drive. I assume that won'
t
> work, as it's counter to what BOL says. But try it out, and see if it
> works.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> We
> for an
> with
> me
>
>|||So here's the thing.
You can add an Access DB .mdb file to SQL Server as a linked server. You
can do so if the file is on a network share somewhere. However (and this
may not always be the case - I have limited knowledge about access linked
servers) you will only be able to query the linked access db server from the
server itself if it's on a network share. If you want to connect to the
access linked server from another sql server - the access db must reside on
a local drive of the server you added the link to.
example
I have a desktop computer and I'm running SQL Server 2000 dev edition.
I have an access db on a network drive (\\prodserv1\accessdbs\MyDB.mdb)
I can add \\prodserv1\accessdbs\MyDB.mdb to my desktop computer running sql
server, and can query it from Query analyzer.
But, if I log into my desktop computer running SQL Server from another
desktop computer using Query Analyzer, I cannot query the linked access
server anymore (select * from LinkedServerName...TableName). I get an error
message.
Now, if I copy \\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb, and
change the data source in the linked server from
\\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb - I can then query the
linked server that I defined on my desktop computer from another desktop
computer.
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:3769D201-5F4C-465E-A79A-0D88918F5EF7@.microsoft.com...[vbcol=seagreen]
> Sorry, Simon, but if I can ask one last question (my hardware knowledge is
> pretty limited). When you say "database file must reside on the server,"
> you mean the Access file needs to be on the SAME server as SQL, right? In
> other words, my probelm is I am trying to access one server from another
> server.
> Thanks
> Rick
> "Simon Worth" wrote:
>
evaluated[vbcol=seagreen]
won't[vbcol=seagreen]
same[vbcol=seagreen]
SQL".[vbcol=seagreen]
an[vbcol=seagreen]
linked[vbcol=seagreen]
server as[vbcol=seagreen]
server[vbcol=seagreen]
using[vbcol=seagreen]
message[vbcol=seagreen]
getting[vbcol=seagreen]