Error when trying to create a linked server

Hi all,

I have a problem when i try to create a linked server to a MsAccess Db. That's what i do:

linked server drop (in case it already exist)

sp_dropserver 'XXXXX', 'droplogins'

linked server creation

sp_addlinkedserver XXXXX, 'Jet 4.0','Microsoft.Jet.OLEDB.4.0', '\ \server\folder\db1.mdb'

login creation

sp_addlinkedsrvlogin XXXXX, FALSE, NULL, Admin, NULL

fill tables

sp_tables_ex XXXXX

File Db1.mdb is located on a partition (D) of the server where Sql is installed.

On login creation i get the following error :

Error -2147217900 Error during decryption. (15466) Source: Microsoft OLE DB Provider for SQL Server.

(This is not the real error message, it has been translated)

Some ideas?

Thanks

It seems like the system is having problems decrypting the service master key. Have you moved the master DB from one machine to another and/or changed the service service account for SQL server? If you changed the service account, try using the original account.

You can also try the following:

SELECT * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'

The information from this DMV will help us find out to determine if indeed it was the SMK decryption or something else.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

I must verify but i think that the customer has Sql Express 2005 SP1 installed. It's ok for retriving information or he has to install SP2?

Thanks

Peo

|||

Unfortunately in order to use the ring buffer DMV you need SQL Server SP2, but that information would only help us to confirm if it was the SMK the one that failed to be decrypted or if it something else. Feel free to update to SP2 if you consider it is necessary

The SMK is protected using DPAPI, and it is linked to the machine and the service account. Hopefully the following questions will help us to find out the root cause of the problem:

· Was the master DB created in a different machine? (i.e. recovered by a RESTORE operation)

· Has the service account for SQL Server service changed?

· What type of account is SQL Server service running as (i.e. domain account, local machine account, local system, network service, etc.)?

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Error when executing a distributed query on a linked server

Hi,
It's a repost of an old thread with more information included
I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits. I've
been trying to link a sql server 2000 SP4 to this but I'm having some
troubles. When creating the linked server from the Sql Server Management
Studio/New Linked Server I choosed Server type to be "SQL Server" for the 2K
linked server. The linked server was created. Then I go to
Properties/Security and configure the linked server to be accesed as sa. But
when I run a distributed query like
Select * from linkedserver.database.dbo.table I get the error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 2
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
server "sqlservhpold". A four-part name was supplied, but the provider does
not expose the necessary interfaces to use a catalog or schema.
I tested the link by using sp_testlinkedserver and it returns no failure.
I also created a linked server in the reverse. I mean let server A be the
Sql Server 2005 64 bits Enterprise, and server B be a Sql Server 2000
Standard. I'm in A making B as linked server and trying to make a
distributed query from A to B (Select * from B.database.dbo.table) and this
fails. I tryed in B create A as a linked server. Then I made a distributed
query from B to A (Select * from A.database.dbo.table) and it works as
expected.
The most frustrating is the message that error 7399 returns: "The provider
did not give any information about the error." I cn not find nothing about
such an error message...
Any hint is welcomed
Thanks in advance
SammySammyBar (sammybar@.gmail.com) writes:
> I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits.
> I've been trying to link a sql server 2000 SP4 to this but I'm having
> some troubles. When creating the linked server from the Sql Server
> Management Studio/New Linked Server I choosed Server type to be "SQL
> Server" for the 2K linked server. The linked server was created. Then I
> go to Properties/Security and configure the linked server to be accesed
> as sa. But when I run a distributed query like
> Select * from linkedserver.database.dbo.table I get the error:
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
> error. The provider did not give any information about the error.
> Msg 7312, Level 16, State 1, Line 2
> Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
> server "sqlservhpold". A four-part name was supplied, but the provider
> does not expose the necessary interfaces to use a catalog or schema.
Can you double check the SQL 2000 instance, by doing SELECT @.@.version?
I seem to recall that to run linked queries from SQL 2005 to SQL 2000,
the latter needs some catalog procedures that comes with SP4, or possbly
SP2.
Also the exact command you use to create the linked server could be of
interest.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Error when executing a distributed query on a linked server

Hi,
It's a repost of an old thread with more information included
I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits. I've
been trying to link a sql server 2000 SP4 to this but I'm having some
troubles. When creating the linked server from the Sql Server Management
Studio/New Linked Server I choosed Server type to be "SQL Server" for the 2K
linked server. The linked server was created. Then I go to
Properties/Security and configure the linked server to be accesed as sa. But
when I run a distributed query like
Select * from linkedserver.database.dbo.table I get the error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 2
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
server "sqlservhpold". A four-part name was supplied, but the provider does
not expose the necessary interfaces to use a catalog or schema.
I tested the link by using sp_testlinkedserver and it returns no failure.
I also created a linked server in the reverse. I mean let server A be the
Sql Server 2005 64 bits Enterprise, and server B be a Sql Server 2000
Standard. I'm in A making B as linked server and trying to make a
distributed query from A to B (Select * from B.database.dbo.table) and this
fails. I tryed in B create A as a linked server. Then I made a distributed
query from B to A (Select * from A.database.dbo.table) and it works as
expected.
The most frustrating is the message that error 7399 returns: "The provider
did not give any information about the error." I cn not find nothing about
such an error message...
Any hint is welcomed
Thanks in advance
SammySammyBar (sammybar@.gmail.com) writes:
> I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits.
> I've been trying to link a sql server 2000 SP4 to this but I'm having
> some troubles. When creating the linked server from the Sql Server
> Management Studio/New Linked Server I choosed Server type to be "SQL
> Server" for the 2K linked server. The linked server was created. Then I
> go to Properties/Security and configure the linked server to be accesed
> as sa. But when I run a distributed query like
> Select * from linkedserver.database.dbo.table I get the error:
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
> error. The provider did not give any information about the error.
> Msg 7312, Level 16, State 1, Line 2
> Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
> server "sqlservhpold". A four-part name was supplied, but the provider
> does not expose the necessary interfaces to use a catalog or schema.
Can you double check the SQL 2000 instance, by doing SELECT @.@.version?
I seem to recall that to run linked queries from SQL 2005 to SQL 2000,
the latter needs some catalog procedures that comes with SP4, or possbly
SP2.
Also the exact command you use to create the linked server could be of
interest.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Error when deleting

I have a msde back end for my application.
I also have an Access data file with linked tables to the SQL one.
My app accesses the data through the Access one through ODBC.

I have a data control and when try to delete a record as so:
DataControl.Recordset.Delete
I get a 'ODBC Call Failed' error.

Any ideas why?
The recordset's updateable property is true.

Thanks,THAT is the reason why I hate bound controls! Throw them away, replace dynamic calls with sp's, and live happily ever after! You think you have problems now while developing? Try putting it into prod, with more than 1 user, and you'll see what I mean (this is not an attack, just my heart screaming "WATCH OUT! THIS IS NOT A LIGHT AT THE END OF THE TUNNEL! IT IS A TRAIN!")|||Have you posted this on the Access forum for input?

Have you considered changing your Access interface to an Access Data Project? It synchronizes with SQL Server much better.

Have you checked to make sure that each of your linked tables has an enforced unique key and that Access was aware of this key when your table was linked?

Have you checked that your login has permission to delete those records?

Have you checked to be sure that the records in the table are participating in non-cascading relationships with other tables?|||...Have you checked it's plugged in?

Have you checked its pulse?

Have you checked your pulse?

...Have you checked if the neiborhood bar has started the happy hour? Go check it! It helps me!|||Here is my ms_sql_dba approved response:

OBVIOUSLY it is plugged in, or he wouldn't be getting an error message! But you didn't THINK about that, did you? And computers don't HAVE pulses, which you'd know if you bothered to read the manual!|||...?...|||...That was a joke...though I know I was accused of making my jokes somewhat untranslateable at times...But hey, that's what made me what I am, so there you have it!|||Originally posted by blindman
Here is my ms_sql_dba approved response:

OBVIOUSLY it is plugged in, or he wouldn't be getting an error message! But you didn't THINK about that, did you? And computers don't HAVE pulses, which you'd know if you bothered to read the manual!

Incorrigible

Error when attempt to link to exchange

hello,
I try to add exchange server as linked server in mssql on SBS2003.
I use sp_addlinkedserver :
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders
Now when trying to run a query or listing tables in EM I always get the
following error:
Error 7304: A new session on OLE DB-Provider
'exoledb.DataSource.1' could not be created.
OLE DB-Errortrace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
Is there anybody out there who could help me;-)
regards
reinhold
1. Have you tried using a UDL connection to connect to the SQL server?
2. Please refer to 287534 PRB: ExOLEDB Provider Cannot Access or Create
Items Remotely http://support.microsoft.com/?id=287534 which explains some
more information.
thanks,
Vikram
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Thanks Vikram,
I've resolved the problem in the meanwhile.
It was related to casesensitivity. When indicating path to webstorage via
"file://.." path must be casesensitive as it seems.
thanks
reinhold
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:aAS$ZhobEHA.2880@.cpmsftngxa06.phx.gbl...
> 1. Have you tried using a UDL connection to connect to the SQL server?
> 2. Please refer to 287534 PRB: ExOLEDB Provider Cannot Access or Create
> Items Remotely http://support.microsoft.com/?id=287534 which explains some
> more information.
> thanks,
> Vikram
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Error when attempt to link to exchange

hello,
I try to add exchange server as linked server in mssql on SBS2003.
I use sp_addlinkedserver :
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders
Now when trying to run a query or listing tables in EM I always get the
following error:
Error 7304: A new session on OLE DB-Provider
'exoledb.DataSource.1' could not be created.
OLE DB-Errortrace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
Is there anybody out there who could help me;-)
regards
reinhold1. Have you tried using a UDL connection to connect to the SQL server?
2. Please refer to 287534 PRB: ExOLEDB Provider Cannot Access or Create
Items Remotely http://support.microsoft.com/?id=287534 which explains some
more information.
thanks,
Vikram
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Thanks Vikram,
I've resolved the problem in the meanwhile.
It was related to casesensitivity. When indicating path to webstorage via
"file://.." path must be casesensitive as it seems.
thanks
reinhold
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:aAS$ZhobEHA.2880@.cpmsftngxa06.phx.gbl...
> 1. Have you tried using a UDL connection to connect to the SQL server?
> 2. Please refer to 287534 PRB: ExOLEDB Provider Cannot Access or Create
> Items Remotely http://support.microsoft.com/?id=287534 which explains some
> more information.
> thanks,
> Vikram
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Error when attempt to link to exchange

hello,
I try to add exchange server as linked server in mssql on SBS2003.
I use sp_addlinkedserver :
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders
Now when trying to run a query or listing tables in EM I always get the
following error:
Error 7304: A new session on OLE DB-Provider
'exoledb.DataSource.1' could not be created.
OLE DB-Errortrace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
Is there anybody out there who could help me;-)
regards
reinhold1. Have you tried using a UDL connection to connect to the SQL server?
2. Please refer to 287534 PRB: ExOLEDB Provider Cannot Access or Create
Items Remotely http://support.microsoft.com/?id=287534 which explains some
more information.
thanks,
Vikram
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Thanks Vikram,
I've resolved the problem in the meanwhile.
It was related to casesensitivity. When indicating path to webstorage via
"file://.." path must be casesensitive as it seems.
thanks
reinhold
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:aAS$ZhobEHA.2880@.cpmsftngxa06.phx.gbl...
> 1. Have you tried using a UDL connection to connect to the SQL server?
> 2. Please refer to 287534 PRB: ExOLEDB Provider Cannot Access or Create
> Items Remotely http://support.microsoft.com/?id=287534 which explains some
> more information.
> thanks,
> Vikram
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Error using UnaryOperatorColumn on a Many-to-Many linked dimension

Hi,
i made a small debugging environment for a problem i am getting, it has 2 fact and 2 dimension tables:

FactAccount - fact - (AccountKey,Balance)
DimAccount - dimension - (AccountKey,Code,Name)
BridgeAccountReport - fact - (AccountKey,ReportKey)
DimReport - dimension - (ReportKey,ParentReportKey,Code,Name,UnaryOperator)

the dimension usage is as follows:

for BridgeAccountReport
DimAccount - Regular
DimReport - Regular

for FactAccount
DimAccount - Regular
DimReport - Many-to-Many using BridgeAccountReport as intermediate fact table

DimReport has a self-referencing hierarchy using ParentReportKey.

The idea is to be able to use the same facts recorded at the DimAccount level under different analysis structures recorded in DimReport.

The problem i am getting is that the UnaryOperatorColumn is only having an effect on the inmediate superior level,
but the result on that level is not afterwards used to calculate the aggregations on upper levels.

Here is a sample result:

Net profit 53 (NO typing error!!)
Profit 6
Income 28
Expenses 22 (unary operator set to '-')
Taxes 3

Profit, which is correctly calculated based on unary operator as 6 is not the used as it is to calculate Net Profit which ends up with a value of 53.
Instead the full values of the lower levels seem to be used to calculate it.

To contrast these results i made a similar example, but this time with only:

FactAccount (AccountKey,Balance)
DimAccount (AccountKey,ParentAccountKey,Code,Name,UnaryOperatorColumn)

and i get:

Net profit 9
Profit 6
Income 28
Expenses 22 (unary operator set to '-')
Taxes 3

Correct results.

So, the difference must be somehow related to the indirect relation over the many-to-many link.

Am i forgetting something? Is it a bug in analysis services?

thanks for any hint or comment, af.Hi,
I have encountered the same problem. On the leaf level the unary operator is considered, but on higher level it is ignored.

Have you found any solution or workaround?

Any hint would be helpful ...thx.|||Hi, so i am not crazy :-)
Yours is the first answer i got both from this forum and from the Connect bug report which you can visit here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=200152

I was also planning to describe this more in detail in a forum dedicated to many-to-many relations but i couldnt find the time so far. The forum is in:
http://www.sqlbi.eu/Forum/tabid/72/forumid/17/scope/threads/Default.aspx

On that site there is also an excellent paper on many-to-many modelling.

Let me know if you find some answer to this problem.

af.

Error using UnaryOperatorColumn on a Many-to-Many linked dimension

Hi,
i made a small debugging environment for a problem i am getting, it has 2 fact and 2 dimension tables:

FactAccount - fact - (AccountKey,Balance)
DimAccount - dimension - (AccountKey,Code,Name)
BridgeAccountReport - fact - (AccountKey,ReportKey)
DimReport - dimension - (ReportKey,ParentReportKey,Code,Name,UnaryOperator)

the dimension usage is as follows:

for BridgeAccountReport
DimAccount - Regular
DimReport - Regular

for FactAccount
DimAccount - Regular
DimReport - Many-to-Many using BridgeAccountReport as intermediate fact table

DimReport has a self-referencing hierarchy using ParentReportKey.

The idea is to be able to use the same facts recorded at the DimAccount level under different analysis structures recorded in DimReport.

The problem i am getting is that the UnaryOperatorColumn is only having an effect on the inmediate superior level,
but the result on that level is not afterwards used to calculate the aggregations on upper levels.

Here is a sample result:

Net profit 53 (NO typing error!!)
Profit 6
Income 28
Expenses 22 (unary operator set to '-')
Taxes 3

Profit, which is correctly calculated based on unary operator as 6 is not the used as it is to calculate Net Profit which ends up with a value of 53.
Instead the full values of the lower levels seem to be used to calculate it.

To contrast these results i made a similar example, but this time with only:

FactAccount (AccountKey,Balance)
DimAccount (AccountKey,ParentAccountKey,Code,Name,UnaryOperatorColumn)

and i get:

Net profit 9
Profit 6
Income 28
Expenses 22 (unary operator set to '-')
Taxes 3

Correct results.

So, the difference must be somehow related to the indirect relation over the many-to-many link.

Am i forgetting something? Is it a bug in analysis services?

thanks for any hint or comment, af.Hi,
I have encountered the same problem. On the leaf level the unary operator is considered, but on higher level it is ignored.

Have you found any solution or workaround?

Any hint would be helpful ...thx.|||Hi, so i am not crazy :-)
Yours is the first answer i got both from this forum and from the Connect bug report which you can visit here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=200152

I was also planning to describe this more in detail in a forum dedicated to many-to-many relations but i couldnt find the time so far. The forum is in:
http://www.sqlbi.eu/Forum/tabid/72/forumid/17/scope/threads/Default.aspx

On that site there is also an excellent paper on many-to-many modelling.

Let me know if you find some answer to this problem.

af.

Error using MAX() - linked server to Orcl

I have a linked server to an Oracle 9 db using MS OLE DB Provider for Orcl.
When I query "select MAX(IntColumn) from ..." I get the error below when
IntColumn is NUMBER(5) or NUMBER(38) in Orcl, but not when it is NUMBER(9).
No error for simple "select IntColumn from ..." on same tables.
The problem appeared only after SQL 2000 was uninstalled and reinstalled.
The problem persisted after reapplying SP3 for both SQL and for AS.
Also uninstalled/re-installed Orcl 9 client.
I can get around the problem, but I would like to know what DLL/component
got out of sync with all the uninstall/reinstalling.
Thanks,
Les McPheeI should have added details of the error I get...
OLE DB Provider 'MSDAORA" reported an error.
[OLE/DB provider returned message: ORA-01455: converting column overflows
integer datatype]
"lmcphee" wrote:

> I have a linked server to an Oracle 9 db using MS OLE DB Provider for Orcl
.
> When I query "select MAX(IntColumn) from ..." I get the error below when
> IntColumn is NUMBER(5) or NUMBER(38) in Orcl, but not when it is NUMBER(9)
.
> No error for simple "select IntColumn from ..." on same tables.
> The problem appeared only after SQL 2000 was uninstalled and reinstalled.
> The problem persisted after reapplying SP3 for both SQL and for AS.
> Also uninstalled/re-installed Orcl 9 client.
> I can get around the problem, but I would like to know what DLL/component
> got out of sync with all the uninstall/reinstalling.
> Thanks,
> Les McPhee

Error using CreateSubscription from "My Reports"

I can create subscription from all reports in the normal folder list.
The problem is when I create a linked report in "My Reports", Then try to
create a subscription, I get an error.
A subscription delivery error has occurred. -->
A subscription delivery error has occurred. -->
The value of parameter 'extensionSettings' is not valid.
Check the documentation for information about valid values. -->
A non-privileged user can not set the setting 'Comment'.
Parameters:
Report : "/My Reports/Company Sales"
When I dont set the The Comment Extension setting it works..
How do I get arround this, and still be able to set the comment?By default the folder created for the user does not have "Manage All
Subscriptions" permission. Set this permission on the folder and you should
then be able to set the comment field.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"SouthFLBoiler@.discussions.microsoft.com"
<SouthFLBoilerdiscussionsmicrosoftcom@.discussions.microsoft.com> wrote in
message news:AB32F233-3F08-4158-88B4-1102566D8C1F@.microsoft.com...
>I can create subscription from all reports in the normal folder list.
> The problem is when I create a linked report in "My Reports", Then try to
> create a subscription, I get an error.
>
> A subscription delivery error has occurred. -->
> A subscription delivery error has occurred. -->
> The value of parameter 'extensionSettings' is not valid.
> Check the documentation for information about valid values. -->
> A non-privileged user can not set the setting 'Comment'.
>
> Parameters:
> Report : "/My Reports/Company Sales"
> When I dont set the The Comment Extension setting it works..
> How do I get arround this, and still be able to set the comment?
>

Error Updating DB2 on MVS-MF from a Linked Server

Need to be able to run update queries on DB2 on IBM MF from a Linked Server. Select and Insert queries work but Update and Delete queries don't. DB2 connect is installed and ODBC System dsn's are created for DEV and Production DB2 environments.

The ODBC drivers can be selected when running Imports/Exports but can't be specified through a linked server.

Any Ideas?

Tom...

Linked Servers do not directly support ODBC drivers, but you can plug in ODBC drivers by using a OLE DB to ODBC bridge technology 'Microsoft OLE DB Provider for ODBC (MSDASQL)'. This componentships with MDAC but is not available on 64-bit at this time.

Although option would be to use Microsoft's DB2 OLE DB Provider directly with Linked Server available for download on http://www.microsoft.com/downloads/details.aspx?FamilyId=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en

Microsoft OLEDB Provider for DB2

The Microsoft OLE DB Provider for DB2 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2005 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor. Run the self-extracting download package to create an installation folder. The single setup program will install the provider and tools on x86, x64, and IA64 computers. The package includes product updates in the form of an integrated Service Pack 1. Read the installation guide and Readme for more information.

Error updating AS400 table

SQL2000 SP3a
I have a statement as follows which attempts to insert data into an AS400
table using a linked server.
[b]SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRAN
INSERT OPENQUERY(LS_HMVCASW1,
'SELECT
FIELD
FROM
WARDOUR1.PCUPDDTA.KENNY
WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
SELECT
'X'
COMMIT TRAN
SET XACT_ABORT OFF
GO[/b]
I get the following error:
[b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
returned 0x80004005: The provider did not give any information about the
error.].[/b]
Journaling is set on on the AS400 tables. As far as I can make out, the
provider supports distributed transactions - question is how? The user in
the LS has correct permissions on AS400.
Any help much appreciated.The error does imply Access Denied. Use your AS400 query interface, log on
as the user this job is running under, and manually test the SQL
Jeff
"Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> SQL2000 SP3a
> I have a statement as follows which attempts to insert data into an AS400
> table using a linked server.
> [b]SET XACT_ABORT ON
> GO
> BEGIN DISTRIBUTED TRAN
> INSERT OPENQUERY(LS_HMVCASW1,
> 'SELECT
> FIELD
> FROM
> WARDOUR1.PCUPDDTA.KENNY
> WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
> SELECT
> 'X'
> COMMIT TRAN
> SET XACT_ABORT OFF
> GO[/b]
> I get the following error:
> [b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
> returned 0x80004005: The provider did not give any information about the
> error.].[/b]
>
> Journaling is set on on the AS400 tables. As far as I can make out, the
> provider supports distributed transactions - question is how? The user in
> the LS has correct permissions on AS400.
> Any help much appreciated.|||I cannot log into the green screen using this profile as it has been set up
to boot you off immediately you log on, but I can link to the table from
Access using the same system DSN and user profile, and I can add/edit data
through that, so that to me means permissions are ok.
"Jeff Dillon" wrote:
> The error does imply Access Denied. Use your AS400 query interface, log on
> as the user this job is running under, and manually test the SQL
> Jeff
> "Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
> news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> > SQL2000 SP3a
> >
> > I have a statement as follows which attempts to insert data into an AS400
> > table using a linked server.
> >
> > [b]SET XACT_ABORT ON
> > GO
> > BEGIN DISTRIBUTED TRAN
> > INSERT OPENQUERY(LS_HMVCASW1,
> > 'SELECT
> > FIELD
> > FROM
> > WARDOUR1.PCUPDDTA.KENNY
> > WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
> > SELECT
> > 'X'
> >
> > COMMIT TRAN
> > SET XACT_ABORT OFF
> > GO[/b]
> >
> > I get the following error:
> >
> > [b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
> > any information about the error.
> > OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
> > returned 0x80004005: The provider did not give any information about the
> > error.].[/b]
> >
> >
> > Journaling is set on on the AS400 tables. As far as I can make out, the
> > provider supports distributed transactions - question is how? The user in
> > the LS has correct permissions on AS400.
> >
> > Any help much appreciated.
>
>|||After applying a Client Access SP, I've got it working ... sometimes. If I
remove the BEGIN TRAN and COMMIT TRAN it works. Leave those in and I get :
[b]The operation could not be performed because the OLE DB provider
'MSDASQL' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDASQL'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
[/b]
But if I stop and restart SQL, the TRAN stuff then works.. but only once,
which is bizarre. If I run it a second time it errors again. The field I'm
updating doesn't need to be unique.
Any ideas?
"Jeff Dillon" wrote:
> The error does imply Access Denied. Use your AS400 query interface, log on
> as the user this job is running under, and manually test the SQL
> Jeff
> "Kendo Nagasaki" <Kendo Nagasaki@.discussions.microsoft.com> wrote in message
> news:034E693C-0530-4719-B771-A472C9E83D9B@.microsoft.com...
> > SQL2000 SP3a
> >
> > I have a statement as follows which attempts to insert data into an AS400
> > table using a linked server.
> >
> > [b]SET XACT_ABORT ON
> > GO
> > BEGIN DISTRIBUTED TRAN
> > INSERT OPENQUERY(LS_HMVCASW1,
> > 'SELECT
> > FIELD
> > FROM
> > WARDOUR1.PCUPDDTA.KENNY
> > WHERE 1=0') --ADD WHERE 1=0 TO STOP QUERY RETURNING ANY VALUES
> > SELECT
> > 'X'
> >
> > COMMIT TRAN
> > SET XACT_ABORT OFF
> > GO[/b]
> >
> > I get the following error:
> >
> > [b]OLE DB provider 'MSDASQL' reported an error. The provider did not give
> > any information about the error.
> > OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
> > returned 0x80004005: The provider did not give any information about the
> > error.].[/b]
> >
> >
> > Journaling is set on on the AS400 tables. As far as I can make out, the
> > provider supports distributed transactions - question is how? The user in
> > the LS has correct permissions on AS400.
> >
> > Any help much appreciated.
>
>

Error Trapping

I have a linked server setup in a SQL Server 2000 environment that links to
an Oracle database. I then have a Trigger on a SQL Server table, that on
insert, inserts a row of data into the Oracle table via the linked server.
All of this is driven by a front-end application written in VB.NET. I want
the front end to be oblivious to trigger. In other words, if the linked
server loses connection to the Oracle database I don't want the trigger to
execute. I have a "clean-up" stored proc that will insert any rows that
weren't inserted by the trigger. Any ideas on how I can accomplish this? I
am unable to trap the error before it actually occurs. I would like to be
able to somehow check the status of the linked server before executing the
trigger. If it is offline (or errors) then the trigger should not execute
and no errors should be sent to the VB.NET front end. Thanks for any advice
.
Marcocould you not just write a sProc that uses the below and execute it from .ne
t
as follows:
Private Function RunExecRoutines()
Try
cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password=" &
Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
cn.Open()
Catch ex As Exception
sqlCnError = ("Error: Could not establish database connection")
End Try
cmd = New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandText = ProcToExec -- whatever sProc you wanna run
cmd.CommandTimeout = 9000
cmd.CommandType = CommandType.StoredProcedure
-- any params you want to pass or receive back...
cmd.Parameters.Add("@.pWho", UNameRoutine)
cmd.Parameters(0).Direction = ParameterDirection.Input
cmd.Parameters.Add("@.pRESULT", SqlDbType.Int)
cmd.Parameters(1).Direction = ParameterDirection.Output
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedure"
)
MessageBox.Show(sqlCnError, "AppName", MessageBoxButtons.OK, _
MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End Try
cmd.Dispose()
cn.Close()
cn.Dispose()
end fuction
select srvid, srvstatus, srvname, srvproduct, providername, catalog from
master.dbo.sysservers where srvstatus & 32 <> 0
sp_helptext sp_sqlagent_get_perf_counters -- msdb
sp_helptext sp_enum_oledb_providers -- master
sp_helptext xp_enum_oledb_providers -- master|||Marc,
Thanks for the advice but I am working with a trigger that is executed in
SQL Server. The error occurs in SQL Server and is then reported to the app.
I had thought about the try...catch method but it does me no good since I am
working with a trigger that is idenpendent of .NET. Do you have any other
ideas? I would rather not reengineer my front-end app if at all possible.
Thanks,
Marco
"marcmc" wrote:

> could you not just write a sProc that uses the below and execute it from .
net
> as follows:
> Private Function RunExecRoutines()
> Try
> cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password="
&
> Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
> cn.Open()
> Catch ex As Exception
> sqlCnError = ("Error: Could not establish database connection")
> End Try
> cmd = New SqlClient.SqlCommand
> cmd.Connection = cn
> cmd.CommandText = ProcToExec -- whatever sProc you wanna run
> cmd.CommandTimeout = 9000
> cmd.CommandType = CommandType.StoredProcedure
> -- any params you want to pass or receive back...
> cmd.Parameters.Add("@.pWho", UNameRoutine)
> cmd.Parameters(0).Direction = ParameterDirection.Input
> cmd.Parameters.Add("@.pRESULT", SqlDbType.Int)
> cmd.Parameters(1).Direction = ParameterDirection.Output
> Try
> cmd.ExecuteNonQuery()
> Catch ex As Exception
> sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedur
e")
> MessageBox.Show(sqlCnError, "AppName", MessageBoxButtons.OK, _
> MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
> End Try
> cmd.Dispose()
> cn.Close()
> cn.Dispose()
> end fuction
>
> select srvid, srvstatus, srvname, srvproduct, providername, catalog from
> master.dbo.sysservers where srvstatus & 32 <> 0
> sp_helptext sp_sqlagent_get_perf_counters -- msdb
> sp_helptext sp_enum_oledb_providers -- master
> sp_helptext xp_enum_oledb_providers -- master|||would this be any use?
if @.varName <= 0 goto trig_error
trig_error:
raiserror('Allocation Error - Unable to Allocate Zero Quantity',16,-1)|||Marc,
Thanks for the ideas. What I ended up doing was adding a status indicator
field to my table. In the front end I try to connect to the database. If I
cannot connect it sets this indicator field to a 1. If on insert that field
is set to one the Trigger then ignores the inserted row. This will also
allow me to monitor, a bit more closely, any connectivity issues I may have
with the Oracle database. thanks again for your help.
Marco
"marcmc" wrote:

> would this be any use?
> if @.varName <= 0 goto trig_error
> trig_error:
> raiserror('Allocation Error - Unable to Allocate Zero Quantity',16,-1)
>