Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts

Wednesday, March 21, 2012

Error 7874 Can't find the object tableName

I have an an MSAccess data project front end linked to an SQL Server Database.
I keep getting Error number 7874: database cannot find the object tblname
when I look however the table is there.
I am calling a procedure which deletes all records in the table and then repopultates it with new records.
so the table is never deleted and recreated.
Once the procedure has run, then I transfertext the table to a local directory.

this works when I run it. However when I try and get the user to do it, they get the error - which doesn't make any sense, because the table is still there, and the procedure call has worked because the table has the selected information in there, it just won't transfer it?

Can anyone help out on this please?

thanks.This should probably be a SQL Server thread rather than Access, but unless I miss my guess, this is a permissions issue. A table may well not be visible to the user unless they have been set up to access SQL Server.
As a developer, it's almost certainly been done for your account.|||Hi,
have checked permissions everywhere I can think of, for the user, and all looks fine.
I don't think you can use t transfertext method in access data project. there doesn't seem to be a way of creating a specification/template for export format, where I can tell it to use tab as a delimiter.
triedtput to method, but that does not give the required result in the text file.

any other ideas of how to get a table out into a text file delimited by tabs?|||Would it surprise you to know I still think it's a permissions issue?
I can move this over to the SQL Server forum if you like. I can't see other ideas as helping at all if the access to the SQL Server table is denied anyway. Sorry.|||

Quote:

Originally Posted by Sami Ward

I have an an MSAccess data project front end linked to an SQL Server Database.
I keep getting Error number 7874: database cannot find the object tblname
when I look however the table is there.
I am calling a procedure which deletes all records in the table and then repopultates it with new records.
so the table is never deleted and recreated.
Once the procedure has run, then I transfertext the table to a local directory.

this works when I run it. However when I try and get the user to do it, they get the error - which doesn't make any sense, because the table is still there, and the procedure call has worked because the table has the selected information in there, it just won't transfer it?

Can anyone help out on this please?

thanks.



What version SQL Server are you using? what version Access are you using?.. on what basis are you logging into SQL server from the client machines are you logging in using Windows integrated security? or standard SQL server permissions?. Are your users assigned to roles? if so what permissions do those roles have for your users compared with yourself who I would guess... is sysadmin role yes? Is this 'procedure' as you call it a 'stored procedure' on the server

These questions are necessary to try and assess your situation in that objects in SQL server rely on the owner creating them. The default owner is 'dbo' in SQL server. You can see this in enterprise manager the standard SQL Server GUI

You will appreciate without sufficient info any advice is blind here... my first simple question or statement would be this:

"If your user opens an Access data project and when they see the tables to which they have permissions do the tables show themselves in the database window with a 'dbo' suffix or not? because if it does then that is where your error lies basically. ADP files are terribly unforgiving when you do not reference a table with the actual owner ie: dbo.tblName in things like listboxes, comboboxes and other routines within code, this is particularly so in a multi user environment"

Why is this? you may ask well the simple answer is this users in SQL server can 'create tables' and other objects ie views having the same name essentially and SQL server distinguishes between them by reference to the OWNER hence the 'dbo' owner prefix in SQL server (cum 'suffix' in an ADP frontend yes confusing isnt it?).

If your SQL server is version 2000 and you do NOT have service pack 1 installed then your table objects for your non sysadmin users will be suffixed with a figure 1. This was a known issue and was fixed in Service pack 1.

(I am not saying this is your answer by the way I am merely illustrating that there can be more reasons than you might think as to why the Access project frontend file the ADP cannot see an explicitly named table called tblName for any users other than yourself who will have the correct permissions.

My suspicions are that you need to look at your 'roles' if you have any and the permissions assigned to the role again more closely and users assigned to those roles.

In terms of getting your data out to tab delimited in a way other than transfertext then yes that can be done at your frontend ADP file using VBA using open file for output methods where you write out your recordset very quickly indeed to a disk text file. Alternatively you could SQL Server BCP out your data super fast to a text file to a specified location on the server side.

It largely depends on what you need to do what your strategies are and where you need your data to go. if its a local directory then if that local directory is also mapped to the server then the BCP option server side could be scheduled to a job on a timer with no user intervention at all.

I use ADP project files extensively so if you get stuck get back to me, but at the moment your information, for me, is too generic and sketchy to enable me to target any one specific thing for you. What I do know is whatever the case this is resolvable... its just a matter of tracking the issue of non recognition down and it will most probably be a simple tick or two here or there on the permissions side.


Regards

Jim :)|||Hi Jim,

thank you very much for your reply. we SQL Server 2000, and I have created an Access 2003 adp to link to the database in sql Server.
there is a group set up, which all relevant users are added to.
the group has access to read, write and execute queries.
everything works fine. all procedures seem to work ok, and users can create tables and update no problem.
the only issue I had was using the transferText method to get the table out into a text file. I have since found another way, by adding a line at the end of the stored procedure that populates the table,
the line is

exec master..xp_cmdshell 'bcp "SELECT * FROM MyDatabase.dbo.MyTable" queryout FileName.txt -c'

This executes a shell, and gets all the records in the table out to the specified text file. the default delimiter is tab which is just what I wanted. there was no way of specifying this in Access because I couldn't use an Export Specification, it just had no option to save it there.

Now the problem is the stored procedure runs fine the table is populated, but the text file is not created - but there are no errors, when the user runs it.
but works ok when I run it and the text file is created.|||Hi,
you can move this to SQL Server forum if you like, I don't mind.

thanks.

Quote:

Originally Posted by NeoPa

Would it surprise you to know I still think it's a permissions issue?
I can move this over to the SQL Server forum if you like. I can't see other ideas as helping at all if the access to the SQL Server table is denied anyway. Sorry.

|||I'll do that Sami. It seems appropriate.|||

Quote:

Originally Posted by Sami Ward

Hi Jim,

thank you very much for your reply. we SQL Server 2000, and I have created an Access 2003 adp to link to the database in sql Server.
there is a group set up, which all relevant users are added to.
the group has access to read, write and execute queries.
everything works fine. all procedures seem to work ok, and users can create tables and update no problem.
the only issue I had was using the transferText method to get the table out into a text file. I have since found another way, by adding a line at the end of the stored procedure that populates the table,
the line is

exec master..xp_cmdshell 'bcp "SELECT * FROM MyDatabase.dbo.MyTable" queryout FileName.txt -c'

This executes a shell, and gets all the records in the table out to the specified text file. the default delimiter is tab which is just what I wanted. there was no way of specifying this in Access because I couldn't use an Export Specification, it just had no option to save it there.

Now the problem is the stored procedure runs fine the table is populated, but the text file is not created - but there are no errors, when the user runs it.
but works ok when I run it and the text file is created.



Sami,

Your problem is going to be with the running of the xp_cmdshell process which is an extended sproc held in the MASTER database and runnable ONLY by people having sysadmin role permissions of which I can assume your users are not members of.

I take it obviously not all your users have sysadmin permissions therefore in order to actually run this process you will need to setup a PROXY account in SQL Server that impersonates a sysadmin and runs the process on their behalf.

Rather than me reinvent the wheel here, below is a couple of web references that explain the way you need to go directionally so to speak

http://msdn2.microsoft.com/en-us/li...00(SQL.80).aspx

http://www.novicksoftware.com/Artic...dshell-Woes.htm

As an aside... (because you say you have rectified your text file write out by invoking BCP on the server) if you DO need to write out the text file tab delimited locally and wish to control that at the client side enabling users to save it to a drive/folder of their choice then I can advise and provide you with the VBA to do that if necessary if the server side BCP write out ended up being a compromise.

Regards

Jim :)|||Hi Jim,

Thank you very much for your reply. I tried looking at the proxy thing, but after much fiddling about couldnot get the thing to work. Set up a DTS instead and called that from the procedure by using the
EXEC msdb.dbo.sp_start_job @.job_name = 'dtsPackageName'

and the same happened worked for me but not the user - because this need to use a proxy account too.
in the end I went to the Security section in the SQL Enterprise Manager, went to Logins, and found the relevant group, clicked on Properties, and went to the Server Roles tab, and ticked the box that said System administrator. That got everything working nicely thank you.

The users are not going to be doing anything in the system, they would just be using the front end application - so that has solved the problem.

hooray!
--------------------

Quote:

Originally Posted by Jim Doherty

Sami,

Your problem is going to be with the running of the xp_cmdshell process which is an extended sproc held in the MASTER database and runnable ONLY by people having sysadmin role permissions of which I can assume your users are not members of.

I take it obviously not all your users have sysadmin permissions therefore in order to actually run this process you will need to setup a PROXY account in SQL Server that impersonates a sysadmin and runs the process on their behalf.

Rather than me reinvent the wheel here, below is a couple of web references that explain the way you need to go directionally so to speak

http://msdn2.microsoft.com/en-us/li...00(SQL.80).aspx

http://www.novicksoftware.com/Artic...dshell-Woes.htm

As an aside... (because you say you have rectified your text file write out by invoking BCP on the server) if you DO need to write out the text file tab delimited locally and wish to control that at the client side enabling users to save it to a drive/folder of their choice then I can advise and provide you with the VBA to do that if necessary if the server side BCP write out ended up being a compromise.

Regards

Jim :)

|||

Quote:

Originally Posted by Sami Ward

Hi Jim,

Thank you very much for your reply. I tried looking at the proxy thing, but after much fiddling about couldnot get the thing to work. Set up a DTS instead and called that from the procedure by using the
EXEC msdb.dbo.sp_start_job @.job_name = 'dtsPackageName'

and the same happened worked for me but not the user - because this need to use a proxy account too.
in the end I went to the Security section in the SQL Enterprise Manager, went to Logins, and found the relevant group, clicked on Properties, and went to the Server Roles tab, and ticked the box that said System administrator. That got everything working nicely thank you.

The users are not going to be doing anything in the system, they would just be using the front end application - so that has solved the problem.

hooray!
--------------------




Thanks for posting your result Sami. (It benefits others viewing)

By doing that you are giving total server permissions to those users and therefore there is nothing to stop any one of them deleting objects and doing anything they like on the server but I'm sure you realise security risk anyway.

Jim :)

Error 7410 when doing a SELECT from a Link table in xp_sendmail

Really would appreciate some advice on this problem. I have a cluster, CORPUSGA and a stand alone server CORPUSAPP22. CORPUSAPP22 has a linked server set up to CORPUSGA using 'sa' as the security context. Within a stored procedure I am running a SELECT to CORPUSGA.msdb.dbo.sysjobs.

When this is run from Query Analyzer it is fine and runs under the 'dbo' security context, but when is is run under xp_sendmail that uses the SQL Agent's security context (a domain account called
IntServiceGA) it fails with this message:

ODBC error 7410 (42000) Remote access not allowed for Windows NT user activated by SETUSER.

For the following command:

exec master.dbo.xp_sendmail
@.recipients = 'stelzner_eve@.emc.com;dabas_ravi@.emc.com;bergin_gi llian@.emc.com;lynch_eoin@.e
mc.com;sherman_ nancy@.emc.com',
@.message = @.email,
@.query = @.query_text,
@.no_header = 'true',
@.width=80,
@.subject = 'Job Failed on CORPUSGA ',
@.attach_results = 'true',
@.set_user = 'dbo'

Where @.query_text is something like "select * from CORPUSGA.msdb.dbo.sysjobs"

The IntServiceGA account is identical on both servers and is in the local administrators group on both servers and is in the system administrators role within SQL Server, has access to the database and sysjobs has granted permissions to IntServiceGA (SELECT, INSERT,UPDATE,DELETE).

When I run profiler against this, it tries to execute under IntServiceGA (an Windows authentication account) and then tries to SETUSER to sa . This is what it objects to the switching. I have tried to set up the IntServiceGA as the security context for the linked server too and this does not work, same error.

Any help would be great,
Thanks,
EveWhy do you need @.set_user if SQLAgent service account is already in sysadmin server role?|||I have another stored procedure that uses this and it works with the
@.set_user on this one. However, whether I leave it in or take out the
@.set_user parameter it still fails. Any ideas?

Thanks,
Eve|||Try to run your xp_sendmail after doing SETUSER 'DOMAIN_NAME\IntServiceGA' and see what you get.|||no go, using the SETUSER prior to the xp_sendmail still netted the
same results? Do you know what generates this message- NT or SQL?|||It's NT. It just proved that your SQLAgent account is lacking some rights. Was mail profile setup using SQLAgent service account?|||Yes, the profile was set up on both servers from the
corp\IntServiceGA account that is a local admin on
the both servers. Are there additional policies that need
to be put on for this account that are not in the default?

error 7410

I get the following error when i try to run a query on a linked server.
Executed as user: Job Owner. SQL Web Assistant: Could not execute the SQL
statement. SQLSTATE 42000 (Error 16805) Associated statement is not prepared
SQLSTATE
HY007 (Error 0). The step failed.
any good ideas on how to solve?
Hi!
Do please check http://support.microsoft.com/kb/823429 and
http://support.microsoft.com/default...;en-us;834116.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Fab" <flazzaro@.tac.infostream.ca> wrote in message
news:%23pS25NcfFHA.2372@.TK2MSFTNGP14.phx.gbl...
>I get the following error when i try to run a query on a linked server.
> Executed as user: Job Owner. SQL Web Assistant: Could not execute the SQL
> statement. SQLSTATE 42000 (Error 16805) Associated statement is not
> prepared SQLSTATE
> HY007 (Error 0). The step failed.
> any good ideas on how to solve?
>
>

error 7410

I get the following error when i try to run a query on a linked server.
Executed as user: Job Owner. SQL Web Assistant: Could not execute the SQL
statement. SQLSTATE 42000 (Error 16805) Associated statement is not prepared
SQLSTATE
HY007 (Error 0). The step failed.
any good ideas on how to solve?Hi!
Do please check http://support.microsoft.com/kb/823429 and
http://support.microsoft.com/defaul...b;en-us;834116.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Fab" <flazzaro@.tac.infostream.ca> wrote in message
news:%23pS25NcfFHA.2372@.TK2MSFTNGP14.phx.gbl...
>I get the following error when i try to run a query on a linked server.
> Executed as user: Job Owner. SQL Web Assistant: Could not execute the SQL
> statement. SQLSTATE 42000 (Error 16805) Associated statement is not
> prepared SQLSTATE
> HY007 (Error 0). The step failed.
> any good ideas on how to solve?
>
>

error 7410

I get the following error when i try to run a query on a linked server.
Executed as user: Job Owner. SQL Web Assistant: Could not execute the SQL
statement. SQLSTATE 42000 (Error 16805) Associated statement is not prepared
SQLSTATE
HY007 (Error 0). The step failed.
any good ideas on how to solve?Hi!
Do please check http://support.microsoft.com/kb/823429 and
http://support.microsoft.com/default.aspx?scid=kb;en-us;834116.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Fab" <flazzaro@.tac.infostream.ca> wrote in message
news:%23pS25NcfFHA.2372@.TK2MSFTNGP14.phx.gbl...
>I get the following error when i try to run a query on a linked server.
> Executed as user: Job Owner. SQL Web Assistant: Could not execute the SQL
> statement. SQLSTATE 42000 (Error 16805) Associated statement is not
> prepared SQLSTATE
> HY007 (Error 0). The step failed.
> any good ideas on how to solve?
>
>

Error 7405 with a linked server and stored procedure

I'm trying to create a stored procecudure using the openquery command to an linked ODBC server.

Everytime I try to save the procedure I get "error 7405: Heterogenous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection."

I've checked everywhere and set these options on, yet it still doesn't work.

Any ideas?

It's a simple procedure and when I type the sql into query analyzer it works.

HELP?

Here is the procedure.
===============================
CREATE PROCEDURE [dbo].[stpDKLink] AS

select * from openquery(dk, 'select * from pub.invoice')

GO
===============================Yes, SQLServer error message is a bit misleading in this case. Try this out:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[stpDKLink] AS
select * from openquery(dk, 'select * from pub.invoice')
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql

error 7405

I have created a stored procedure wich queries a linked ORACLE database. When
I try to save the procedure I get the following error
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable these
options and then reissue your query.
I have put SET ANSI_WARNINGS ON as the first line of the procedure with no
help.
Are there any suggestions?
Jim Capehart
The connection creating the stored procedure needs to have
the settings on. So you'd want to create the stored
procedure using something along the lines of:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Fri, 17 Dec 2004 08:11:02 -0800, Jim Capehart
<JimCapehart@.discussions.microsoft.com> wrote:

>I have created a stored procedure wich queries a linked ORACLE database. When
>I try to save the procedure I get the following error
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
>set for the connection. This ensures consistent query semantics. Enable these
>options and then reissue your query.
> I have put SET ANSI_WARNINGS ON as the first line of the procedure with no
>help.
>Are there any suggestions?
>Jim Capehart
|||Guys,
This doesn't necessarily work. If your not happ y with adding these
settings to the the server as a whole then try using the ISQL window to
create the procedure.
Be sure to make a copy of the oprginal (just in case) but this will
allow the SET command to intigated at the top of he procedure.
Hope this helps.
JohnT-A
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message654588.html

Error 7399: OLE DB provider 'SQLOLEDB' reported an error.

Hello everyone!
I try to setup a linked server connection to an other SQL Server and receive
the following error message:
Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
I am using a trusted connection.
Does anybody have an idea what I am doing wrong here? Or how I can get a
more specific error message?
Thanks for any hints!
brgds,
tom.Hi Thomas:
Please see the KB article, it may be useful:
http://support.microsoft.com/defaul...kb;en-us;314530
Best Wishes
Wei Ci Zhou

Error 7399 When trying to link an Access database to SqlServer 2K

Hi,
I'm trying to add an Access Database as a linked server to an SqlServer 2000
SP4. Both the server and the access database run on the same PC. The Access
database is protected by a Database password. It is no workgroup password
involved.
After a lot of testing with Visual Basic 6 ADO connection strings I reach to
the following string that is able to open the password protected database by
using VB:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
Files\AccessDB\mydatabase.mdb;Jet OLEDB:Database Password=xxxx"
But when using this string in the definition of the linked server in the
Enterprise Manager I get the following error when trying to browser the
tables of the linked server:
Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I tryed both running the SqlServer as local system account and as a Windows
domain account with domain admin priviledges.
I found also the article http://support.microsoft.com/kb/814398 that
describes a similar problem but that solution did not worked for me.
Any hint is welcomed
Thanks in advance
SammyHi Sammy
You may want to check through http://ask.support.microsoft.com/kb/306518/
Have you checked if the dropping the password from the database works?
You may want to see if moving the database and the security database to a
different directory helps.
John
"SammyBar" wrote:

> Hi,
> I'm trying to add an Access Database as a linked server to an SqlServer 20
00
> SP4. Both the server and the access database run on the same PC. The Acces
s
> database is protected by a Database password. It is no workgroup password
> involved.
> After a lot of testing with Visual Basic 6 ADO connection strings I reach
to
> the following string that is able to open the password protected database
by
> using VB:
> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
> Files\AccessDB\mydatabase.mdb;Jet OLEDB:Database Password=xxxx"
> But when using this string in the definition of the linked server in the
> Enterprise Manager I get the following error when trying to browser the
> tables of the linked server:
> Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> I tryed both running the SqlServer as local system account and as a Window
s
> domain account with domain admin priviledges.
> I found also the article http://support.microsoft.com/kb/814398 that
> describes a similar problem but that solution did not worked for me.
> Any hint is welcomed
> Thanks in advance
> Sammy
>
>

Error 7399 When trying to link an Access database to SqlServer 2K

Hi,
I'm trying to add an Access Database as a linked server to an SqlServer 2000
SP4. Both the server and the access database run on the same PC. The Access
database is protected by a Database password. It is no workgroup password
involved.
After a lot of testing with Visual Basic 6 ADO connection strings I reach to
the following string that is able to open the password protected database by
using VB:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
Files\AccessDB\mydatabase.mdb;Jet OLEDB:Database Password=xxxx"
But when using this string in the definition of the linked server in the
Enterprise Manager I get the following error when trying to browser the
tables of the linked server:
Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I tryed both running the SqlServer as local system account and as a Windows
domain account with domain admin priviledges.
I found also the article http://support.microsoft.com/kb/814398 that
describes a similar problem but that solution did not worked for me.
Any hint is welcomed
Thanks in advance
Sammy
Hi Sammy
You may want to check through http://ask.support.microsoft.com/kb/306518/
Have you checked if the dropping the password from the database works?
You may want to see if moving the database and the security database to a
different directory helps.
John
"SammyBar" wrote:

> Hi,
> I'm trying to add an Access Database as a linked server to an SqlServer 2000
> SP4. Both the server and the access database run on the same PC. The Access
> database is protected by a Database password. It is no workgroup password
> involved.
> After a lot of testing with Visual Basic 6 ADO connection strings I reach to
> the following string that is able to open the password protected database by
> using VB:
> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
> Files\AccessDB\mydatabase.mdb;Jet OLEDB:Database Password=xxxx"
> But when using this string in the definition of the linked server in the
> Enterprise Manager I get the following error when trying to browser the
> tables of the linked server:
> Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> I tryed both running the SqlServer as local system account and as a Windows
> domain account with domain admin priviledges.
> I found also the article http://support.microsoft.com/kb/814398 that
> describes a similar problem but that solution did not worked for me.
> Any hint is welcomed
> Thanks in advance
> Sammy
>
>

Error 7399 When trying to link an Access database to SqlServer 2K

Hi,
I'm trying to add an Access Database as a linked server to an SqlServer 2000
SP4. Both the server and the access database run on the same PC. The Access
database is protected by a Database password. It is no workgroup password
involved.
After a lot of testing with Visual Basic 6 ADO connection strings I reach to
the following string that is able to open the password protected database by
using VB:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
Files\AccessDB\mydatabase.mdb;Jet OLEDB:Database Password=xxxx"
But when using this string in the definition of the linked server in the
Enterprise Manager I get the following error when trying to browser the
tables of the linked server:
Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I tryed both running the SqlServer as local system account and as a Windows
domain account with domain admin priviledges.
I found also the article http://support.microsoft.com/kb/814398 that
describes a similar problem but that solution did not worked for me.
Any hint is welcomed
Thanks in advance
SammyHi Sammy
You may want to check through http://ask.support.microsoft.com/kb/306518/
Have you checked if the dropping the password from the database works?
You may want to see if moving the database and the security database to a
different directory helps.
John
"SammyBar" wrote:
> Hi,
> I'm trying to add an Access Database as a linked server to an SqlServer 2000
> SP4. Both the server and the access database run on the same PC. The Access
> database is protected by a Database password. It is no workgroup password
> involved.
> After a lot of testing with Visual Basic 6 ADO connection strings I reach to
> the following string that is able to open the password protected database by
> using VB:
> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
> Files\AccessDB\mydatabase.mdb;Jet OLEDB:Database Password=xxxx"
> But when using this string in the definition of the linked server in the
> Enterprise Manager I get the following error when trying to browser the
> tables of the linked server:
> Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> I tryed both running the SqlServer as local system account and as a Windows
> domain account with domain admin priviledges.
> I found also the article http://support.microsoft.com/kb/814398 that
> describes a similar problem but that solution did not worked for me.
> Any hint is welcomed
> Thanks in advance
> Sammy
>
>

Error 7391 in Linked Server

Hi,
I was making a linked server connection between SQL Server 2000 and Oracle 8.1.5 (both running on Windows 2000 Server), and I was creating an insert trigger in one of the SQL Server table to insert the same value to the Oracle table. But when I try to make an insert to the table, the following error occurred :

Server: Msg 7391, Level 16, State 1, Procedure <triggername>, Line xx
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8000d048].


I tried to change the trigger into a SELECT statement only and the error still occurred. But when I tried to insert a row directly to the linked server via Query Analyzer, it succeeded.
I have read KB 280106, KB 306212, and KB 329332, and I've tried SET XACT_ABORT ON statement, but none of them can solve the problem.

If there's anyone who has idea on what's wrong or has a suggestion what I should try please help.
Thank you in advance.

Yento,

the reason why the insert using the direct connection to the linked server thru MSDAORA works is, it only requires the oledb provide to support ITransactionLocal. But the trigger case requires ITransactionJoin. I don't know exactly if MSDAORA supports ITransactionJoin or not. (Seems not)

Have you checked MSDTC is running? Perhaps you can also try the Oracle's own oledb provider?

Cheers,

- MC

|||

Hi. I've had the same message and the prob was related to inapropriate permissions of the oracle user. It was missing "Force any transaction" permission. Hope this helps.

Re

Joao Neves

|||

Hi Joao,

Please let me know the steps of granting "force any transaction" permission to oracle user.

Thanks

Anurag

Error 7391 in Linked Server

Hi,
I was making a linked server connection between SQL Server 2000 and Oracle 8.1.5 (both running on Windows 2000 Server), and I was creating an insert trigger in one of the SQL Server table to insert the same value to the Oracle table. But when I try to make an insert to the table, the following error occurred :

Server: Msg 7391, Level 16, State 1, Procedure <triggername>, Line xx
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8000d048].


I tried to change the trigger into a SELECT statement only and the error still occurred. But when I tried to insert a row directly to the linked server via Query Analyzer, it succeeded.
I have read KB 280106, KB 306212, and KB 329332, and I've tried SET XACT_ABORT ON statement, but none of them can solve the problem.

If there's anyone who has idea on what's wrong or has a suggestion what I should try please help.
Thank you in advance.

Yento,

the reason why the insert using the direct connection to the linked server thru MSDAORA works is, it only requires the oledb provide to support ITransactionLocal. But the trigger case requires ITransactionJoin. I don't know exactly if MSDAORA supports ITransactionJoin or not. (Seems not)

Have you checked MSDTC is running? Perhaps you can also try the Oracle's own oledb provider?

Cheers,

- MC

|||

Hi. I've had the same message and the prob was related to inapropriate permissions of the oracle user. It was missing "Force any transaction" permission. Hope this helps.

Re

Joao Neves

|||

Hi Joao,

Please let me know the steps of granting "force any transaction" permission to oracle user.

Thanks

Anurag

Monday, March 19, 2012

Error 7373 in Linked Server

I have created a new linked server entry and when I try to use it, I get the Error 7373 message.

I am rather new to SQLServer, so I would be happy with any help I can get.

Thanks,

DGB

Which sql server version, and which ole db provider (and what is your backend data source) are you using?

The 7373 error code may suggest some errors in the ole db provider itself.

If you are familiar with profiler, you can start the profiler and collect oledb trace events to get more insights into the problem.

Error 7356 - do we have to live with that?

To resolve a well know issue with quering a linked server:
(OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column
was supplied during execution that was not found at compile time.
OLE DB error trace [Non-interface error: OLE DB provider returned an extra
column at execution time: ProviderName=.....)
I change the table name to a view returning all columns of the same table.
I have been doing this since SQL 7.0.
1. Is it an unfixable bug we have to live with?
2. Is there a better way of fixing the problem other than creating a view?
Thanks,
-StanHi Stan,
What is your SQL Server version? Is it SQL Server 7.0 SP4 or SQL Server
2000 SP3?
From your post, I am not sure about the steps to reproduce the problem.
Could you post it so that I can check further?
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Stan" <nospam@.yahoo.com>
| Subject: Error 7356 - do we have to live with that?
| Date: Fri, 12 Sep 2003 14:15:35 -0400
| Lines: 20
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <OjwKinVeDHA.616@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 12.148.36.131
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:306189
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| To resolve a well know issue with quering a linked server:
|
| (OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra
column
| was supplied during execution that was not found at compile time.
| OLE DB error trace [Non-interface error: OLE DB provider returned an
extra
| column at execution time: ProviderName=.....)
|
| I change the table name to a view returning all columns of the same table.
|
| I have been doing this since SQL 7.0.
|
| 1. Is it an unfixable bug we have to live with?
|
| 2. Is there a better way of fixing the problem other than creating a view?
|
| Thanks,
|
| -Stan
|
|
||||Hi Stan,
I have found several articles regarding error "OLE DB provider 'SQLOLEDB'
supplied inconsistent metadata. An extra column was supplied during
execution that was not found at compile time." However, for SQL Server 7.0,
there is an article.
266337 PRB: Some SQL Server 7.0 Service Pack 2 Updates Must be Added
Manually
http://support.microsoft.com/?id=266337
However, I did not find any known issue regarding the same error on SQL
Server 2000. According to your description, it seems that the problem also
occurs on SQL Server 2000. Am I right? Since the problem randomly occurs,
it is very difficult to troubleshoot. However, please try the following
steps first:
1. The output from running "sp_helpserver" from the SQL Server 2000 machine
and the name of the linked server which encounters the problem.
2. Does dropping the linked server and adding it back work?
3. The output from the Component Checker from SQL Server 2000.
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-
BF53-14332EF092C9&displaylang=en
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Stan" <nospam@.yahoo.com>
| References: <OjwKinVeDHA.616@.TK2MSFTNGP11.phx.gbl>
<zOPgrT2eDHA.3900@.cpmsftngxa07.phx.gbl>
| Subject: Re: Error 7356 - do we have to live with that?
| Date: Mon, 15 Sep 2003 08:02:11 -0400
| Lines: 71
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <ejcAJF4eDHA.1712@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 12.148.36.131
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08|||Bill,
> However, I did not find any known issue regarding the same error on SQL
> Server 2000. According to your description, it seems that the problem also
> occurs on SQL Server 2000. Am I right?
It ocurrs in the query on SQL Server 2000 against SQL Server 7.0 linked
server.
So, the culprilt is probably SQL Server 7.0
> 2. Does dropping the linked server and adding it back work?
No, it does not
Thanks!
-Stan|||Bill,
> However, I did not find any known issue regarding the same error on SQL
> Server 2000. According to your description, it seems that the problem also
> occurs on SQL Server 2000. Am I right?
It ocurrs in the query on SQL Server 2000 against SQL Server 7.0 linked
server.
So, the culprilt is probably SQL Server 7.0
> 2. Does dropping the linked server and adding it back work?
No, it does not
Thanks!
-Stan|||Hi Stan,
According to my research, we need to install Service pack 4 and also apply
the Instcat.sql manually.
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Stan" <nospam@.yahoo.com>
| References: <OjwKinVeDHA.616@.TK2MSFTNGP11.phx.gbl>
<zOPgrT2eDHA.3900@.cpmsftngxa07.phx.gbl>
<ejcAJF4eDHA.1712@.TK2MSFTNGP11.phx.gbl>
<noEhU9LfDHA.1388@.cpmsftngxa06.phx.gbl>
<#OO9DMRfDHA.2320@.TK2MSFTNGP12.phx.gbl>
<0fWgE0dfDHA.2588@.cpmsftngxa06.phx.gbl>
| Subject: Re: Error 7356 - do we have to live with that?
| Date: Thu, 18 Sep 2003 15:34:12 -0400
| Lines: 69
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <eNgonvhfDHA.1732@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 12.148.36.131
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:307320
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Bill,
|
| No, we don't have the hotfix on 7.0. Woudn't be better to put the latest
| service pack for SQL 7.0 (I guess it SP4)?
|
| ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
| news:0fWgE0dfDHA.2588@.cpmsftngxa06.phx.gbl...
| > Hi Stan,
| >
| > Have you tried the methods in 266337? I have not found any known issue
| > regarding this in SQL Server 2000.
| >
| > Has the error occurred after you apply 266337 on SQL Server 7.0? If so,
| > could you let me know what operations you have performed before the
error
| > occurs?
| >
| >
| > Bill Cheng
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| > --
| > | From: "Stan" <nospam@.yahoo.com>
| > | References: <OjwKinVeDHA.616@.TK2MSFTNGP11.phx.gbl>
| > <zOPgrT2eDHA.3900@.cpmsftngxa07.phx.gbl>
| > <ejcAJF4eDHA.1712@.TK2MSFTNGP11.phx.gbl>
| > <noEhU9LfDHA.1388@.cpmsftngxa06.phx.gbl>
| > | Subject: Re: Error 7356 - do we have to live with that?
| > | Date: Wed, 17 Sep 2003 07:57:59 -0400
| > | Lines: 21
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Message-ID: <#OO9DMRfDHA.2320@.TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: 12.148.36.131
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:306904
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Bill,
| > |
| > | > However, I did not find any known issue regarding the same error on
| SQL
| > | > Server 2000. According to your description, it seems that the
problem
| > also
| > | > occurs on SQL Server 2000. Am I right?
| > |
| > | It ocurrs in the query on SQL Server 2000 against SQL Server 7.0
linked
| > | server.
| > | So, the culprilt is probably SQL Server 7.0
| > |
| > | > 2. Does dropping the linked server and adding it back work?
| > | No, it does not
| > |
| > |
| > |
| > | Thanks!
| > |
| > | -Stan
| > |
| > |
| > |
| > |
| >
|
|
|

Error 7317 when Accessing Linked Oracle Server

I have a linked server to an Oracle 8i database. When I attempt to query th
e
Oracle tables I get the following error:
Server: Msg 7317, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned an invalid schema definition.
OLE DB error trace [Non-interface error: OLE/DB provider returned an
invalid schema definition.].
The problem is occurring at my customer's site. I have tried duplicating
their environment in my office, but I can not reporduce the problem.
I would appreciate any help you could offer.
Thanks,
Bill Manring
Chief Software Architect
Wavefront Software, Inc.I found an answer to this problem so I thought I should post it in case
anyone else has a similar problem.
If I execute the query using the OPENQUERY function, the query executes
properly. Since OPENQUERY is a peferred way of querying linked srevers
anyway, there really is no problem.
"Bill Manring" wrote:

> I have a linked server to an Oracle 8i database. When I attempt to query
the
> Oracle tables I get the following error:
> Server: Msg 7317, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' returned an invalid schema definition.
> OLE DB error trace [Non-interface error: OLE/DB provider returned an
> invalid schema definition.].
> The problem is occurring at my customer's site. I have tried duplicating
> their environment in my office, but I can not reporduce the problem.
> I would appreciate any help you could offer.
>
> --
> Thanks,
> Bill Manring
> Chief Software Architect
> Wavefront Software, Inc.

Error 7317 when Accessing Linked Oracle Server

I have a linked server to an Oracle 8i database. When I attempt to query the
Oracle tables I get the following error:
Server: Msg 7317, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned an invalid schema definition.
OLE DB error trace [Non-interface error: OLE/DB provider returned an
invalid schema definition.].
The problem is occurring at my customer's site. I have tried duplicating
their environment in my office, but I can not reporduce the problem.
I would appreciate any help you could offer.
Thanks,
Bill Manring
Chief Software Architect
Wavefront Software, Inc.
I found an answer to this problem so I thought I should post it in case
anyone else has a similar problem.
If I execute the query using the OPENQUERY function, the query executes
properly. Since OPENQUERY is a peferred way of querying linked srevers
anyway, there really is no problem.
"Bill Manring" wrote:

> I have a linked server to an Oracle 8i database. When I attempt to query the
> Oracle tables I get the following error:
> Server: Msg 7317, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' returned an invalid schema definition.
> OLE DB error trace [Non-interface error: OLE/DB provider returned an
> invalid schema definition.].
> The problem is occurring at my customer's site. I have tried duplicating
> their environment in my office, but I can not reporduce the problem.
> I would appreciate any help you could offer.
>
> --
> Thanks,
> Bill Manring
> Chief Software Architect
> Wavefront Software, Inc.

Error 7306 when trying to do an update on a linked server

Here is my query run on one server against a Linked server:
UPDATE [Linked-Name].dbname.dbo.[Table name]
SET TimeStamp = '1900-01-01 00:00:00'
WHERE ISDATE(TimeStamp) = 0
I get the following error message:
Could not open table '"dbname"."dbo"."Table name"' 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 help!
Thanks.What platform is the linked server?
<ryan_willow@.hotmail.com> wrote in message
news:1150831576.021477.128570@.p79g2000cwp.googlegroups.com...
> Here is my query run on one server against a Linked server:
> UPDATE [Linked-Name].dbname.dbo.[Table name]
> SET TimeStamp = '1900-01-01 00:00:00'
> WHERE ISDATE(TimeStamp) = 0
> I get the following error message:
> Could not open table '"dbname"."dbo"."Table name"' 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 help!
> Thanks.
>|||SQL Server 2000.
I believe that this is the problem:
http://msdn.microsoft.com/library/d...serr_2_4gqh.asp
i.e. UPDATE, and DELETE statements are not supported on the remote
table if the table does not have a unique index defined on it.
So now I'm here:
I've done DELETEs remotely and had no problem, only errors on UPDATE.
I don't want to modify this established database by defining unique
indices.
How can I get around this?
Mike C# wrote:
> What platform is the linked server?
> <ryan_willow@.hotmail.com> wrote in message
> news:1150831576.021477.128570@.p79g2000cwp.googlegroups.com...|||ryan_willow@.hotmail.com wrote:
> SQL Server 2000.
> I believe that this is the problem:
> http://msdn.microsoft.com/library/d...serr_2_4gqh.asp
> i.e. UPDATE, and DELETE statements are not supported on the remote
> table if the table does not have a unique index defined on it.
> So now I'm here:
> I've done DELETEs remotely and had no problem, only errors on UPDATE.
> I don't want to modify this established database by defining unique
> indices.
> How can I get around this?
> Mike C# wrote:
What is version of MDAC?
and Service Pack?
We have also a problem like this and after updating to latest version
problem was solved
Regards
Amish Shah

Wednesday, March 7, 2012

error 37000

I tried to run my application linked to sqlserver DB. Iobtain this message :
Erreur d'exécution 40002
37000 : [Microsoft][ODBC SQL Server Driver] [SQL Server] the log file for
database "app" si full. Back up the transaction log for the database to free
up some log space
someboy could tell me it there is a possibility to do not write in log file ?
this transaction log is always active ?
thanks
tft
Hi,
Backup the Transaction log (See BACKUP LOG command in books online) and try
accessing the database. If you do not requite a
transaction log backup then change the recovery model for your database to
'SIMPLE'. After that truncate the transaction log using
BACKUP LOG <DBNAME> WITH TRUNCATE_ONLY
The above strategies will clear your trasnaction log.
Thanks
Hari
SQL Server MVP
"ft" <ft@.discussions.microsoft.com> wrote in message
news:19A68E4F-3C6A-4502-AE07-B4C8C9DBFBA3@.microsoft.com...
>I tried to run my application linked to sqlserver DB. Iobtain this message
>:
> Erreur d'excution 40002
> 37000 : [Microsoft][ODBC SQL Server Driver] [SQL Server] the log file for
> database "app" si full. Back up the transaction log for the database to
> free
> up some log space
> someboy could tell me it there is a possibility to do not write in log
> file ?
> this transaction log is always active ?
> thanks
> tft

error 37000

I tried to run my application linked to sqlserver DB. Iobtain this message :
Erreur d'exécution 40002
37000 : [Microsoft][ODBC SQL Server Driver] [SQL Server] the log
file for
database "app" si full. Back up the transaction log for the database to free
up some log space
someboy could tell me it there is a possibility to do not write in log file
?
this transaction log is always active ?
thanks
tftHi,
Backup the Transaction log (See BACKUP LOG command in books online) and try
accessing the database. If you do not requite a
transaction log backup then change the recovery model for your database to
'SIMPLE'. After that truncate the transaction log using
BACKUP LOG <DBNAME> WITH TRUNCATE_ONLY
The above strategies will clear your trasnaction log.
Thanks
Hari
SQL Server MVP
"ft" <ft@.discussions.microsoft.com> wrote in message
news:19A68E4F-3C6A-4502-AE07-B4C8C9DBFBA3@.microsoft.com...
>I tried to run my application linked to sqlserver DB. Iobtain this message
>:
> Erreur d'excution 40002
> 37000 : [Microsoft][ODBC SQL Server Driver] [SQL Server] the l
og file for
> database "app" si full. Back up the transaction log for the database to
> free
> up some log space
> someboy could tell me it there is a possibility to do not write in log
> file ?
> this transaction log is always active ?
> thanks
> tft