Problems Linking to Oracle Server

Has anyone successfully linked to an Linux Oracle server running 9i? We are unable to get the link working.....have installe dvarious drivers and still get connection refused. Any thoughts?

If this is a Linked Server issue I would try the SQL Server Engine forum.

Donald

Problems linking Access to SQL replicated tables

Hello!
I was wondering if anyone could help me.
I am running an Access application that uses linked tables to connect to a SQL server.
Ever since I turned on replication on those tables, I cannot add records to the table using regular Access forms (and, for that matter, I cannot do it by just double-clicking on the table from Access and trying to enter records).

Any help will be greatly appreciated!!!!!Have you also refreshed those links i.e.(deleted the links and recreated them in Access) since you "turned on replication on those tables" (or not)?|||Yes, I have deleted the link and recreated it after turning on replication, but I still get the same error every time I add a record.|||You are able to add a row via query analyzer or EM, and the table is not a read only subscribed table you are attempting to add rows to? (just asking the obvious things)
If all that is in order you might check to see if the generated link schemas include columns that the system automatically populates with data. If there are some (replication related columns) or others such as identity, GUID, timestamp, etc. columns (that are automatically populated), try removing them from the Access link schemas (not the base tables) then recreate your link(s) and attempt a test row insert. If that does not work try creating a view (test insertion in QA / EM), then create a linked table to the view and test its behavior.sql

Problems exporting to Excel

I am having a problem exporting a linked SQL server table from Access 2003
to
Excel. The file is about 2,000 records with several columns. The problem
is that when the table is imported into excel some of the records are not
coming across with the correct data and when numeric fields are summed the
totals are wrong.
When I export directly to Excel from the SQL server using DTS, I don't have
this problem. Has anyone else had this problem? Anyone have ideas of how to
fix it? I need to be able to allow the users to create queries in access
and export the data they need, so setting up a DTS as a solution is not
really workable (or at least I don't think it is).Hi Jim,
Being that this works fine from SQL Server but you are
having problems from MS Access, it's likely an MS Access
issue that you may want to post in one of the MS Access
newsgroups. Maybe try the following group:
microsoft.public.access.externaldata
-Sue
On Mon, 21 Aug 2006 14:52:39 GMT, "JIM" <jcrisp1@.kc.rr.com>
wrote:

>I am having a problem exporting a linked SQL server table from Access 2003
>to
>Excel. The file is about 2,000 records with several columns. The problem
>is that when the table is imported into excel some of the records are not
>coming across with the correct data and when numeric fields are summed the
>totals are wrong.
>When I export directly to Excel from the SQL server using DTS, I don't have
>this problem. Has anyone else had this problem? Anyone have ideas of how t
o
>fix it? I need to be able to allow the users to create queries in access
>and export the data they need, so setting up a DTS as a solution is not
>really workable (or at least I don't think it is).
>

Problems executing DML via linked server

Hey folks..
I have a linked server that connects to a DB2 database through the OLE DB
for ODBC Providers driver. I have a DSN created and can, through the linked
server run SELECT statements to my heart's content. When I want to run a
simple DELETE, for example (DELETE HCEDB.APPLQUE2), I get the following
error.
Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"HCEDB"."APPLQUE2"'.
User did not have sufficient permission to delete the row.
[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 provider returned message: [IBM][CLI Driver] CLI0150E Driver not
capable. SQLSTATE=S1C00]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
I have gone to a colleague's machine to use a DB2 Client and authenticated
using the same user specified in both the DSN and Linked Server and was able
to execute the DELETE. Any ideas as to cause and resolution?
Thanks..
Peace,
Gary HampsonIf you are using a DELETE FROM (Table Name), beware.
I was doing the same thing on a DB2 Database with some test data given to me
, and I would get the same error when I tried to delete using the Linked Ser
ver, where if I tried to do it straight in the DB2 Console, I could execute
without any problems.
So I ran an Event Monitor. Happens out, the Microsoft OLE DB Provider for OD
BC Drivers turns it into count(*) many "DELETE FROM TABLE WHERE field = ?".
So if your table has 1000 rows, you'll have 1000 Delete Statements run in se
quence. Very inefficent, but it gets the job done. The reason for my error w
as that there were 3 rows that were identical to each other, and so it had 3
identical "DELETE FROM" statements.
My solution: Deleted the replicated rows. It apparantly was an oversight whe
n creating the test data.
I still have an issue with it producing all those Delete Statements though.
I'm currently looking into other ways of doing it.

Problems converting Access UNION query to SQL

I've got a Union query that works in Access but I'm unable to get it to work
querying a SQL Server. Access was using linked tables so the data was
really coming from SQL anyway. I want to make this query work within
Reporting Services, but I'm receiving the following error message:
ADO error: ORDER BY items must appear in the select list if the statement
contains a UNION operator.
I was able to take the two Select statements from Access and make good SQL
queries with them, but I'm unable to UNION them together eventhough it works
fine in Access. Here's my two queries I'm trying to Union:
SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
(dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
= 0) AND (dbo.SOP30200.DOCID = 'inv')
ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
UNION
SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturns
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
dbo.viewSalesperson.SLPRSNID
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
'2006-03-01 00:00:00', 102)) AND
(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
(dbo.SOP30200.DOCID = 'returns')order by of the first part of the union should be the problem.
Colin wrote:
>I've got a Union query that works in Access but I'm unable to get it to wor
k
>querying a SQL Server. Access was using linked tables so the data was
>really coming from SQL anyway. I want to make this query work within
>Reporting Services, but I'm receiving the following error message:
>ADO error: ORDER BY items must appear in the select list if the statement
>contains a UNION operator.
>I was able to take the two Select statements from Access and make good SQL
>queries with them, but I'm unable to UNION them together eventhough it work
s
>fine in Access. Here's my two queries I'm trying to Union:
>SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
> (dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
>= 0) AND (dbo.SOP30200.DOCID = 'inv')
>ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
>UNION
>SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturn
s
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
> dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
>dbo.viewSalesperson.SLPRSNID
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
> '2006-03-01 00:00:00', 102)) AND
>(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
>(dbo.SOP30200.DOCID = 'returns')
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Any suggestions on what I need to do to fix it?
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:5cf4869c57190@.uwe...
> order by of the first part of the union should be the problem.
> Colin wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Syntax is:
SELECT ... FROM ...
UNION (ALL)
SELECT ... FROM ...
ORDER BY...
(I also wonder why a TOP 100 PERCENT. That should return everything and
requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
RLF
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>|||Thanks for the help and feedback. I've got it working now. Is there a
reason why Visual Studio or any other design interface doesn't support
Design mode for UNION of two queries?
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uWwHb5uQGHA.2816@.TK2MSFTNGP15.phx.gbl...
> Syntax is:
> SELECT ... FROM ...
> UNION (ALL)
> SELECT ... FROM ...
> ORDER BY...
> (I also wonder why a TOP 100 PERCENT. That should return everything and
> requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
> RLF
> "Colin" <legendsfan@.nospam.nospam> wrote in message
> news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
>|||A few notes that may help you (and others):
1. TOP 100 Percent ... ORDER BY was a workaround/hack/creative piece of SQL
to try to get the optimizer to sort values at a point in the query plan.
Unfortunately, it didn't really work in all cases, and it actually isn't
being honored at all in SQL 2005. So, please consider removing this from
your code in the future.
2. As you've seen from the other posts, ORDER BY should be applied to the
end of the complete statement to affect the presentation order of the
results returned to the client (and not each block of the UNION).
3. The column binding rules for Jet Red (Access's engine) never really
conformed to the ANSI standards. Given the installed base, it's not likely
to be changed. So, just be aware that the queries you may have in your
Access application are sometimes interpreted in slightly different ways.
Best of luck,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>

problems adding records to linked tables

Hi everyone,

I just used the upsize wizard in Access to create a Access front end and SQL server 2000 backend with linked tables, but I am receiving an error that I can't figure out.

When I am trying to add a record into my 'Cases' linked table in the SQL server, I get a error saying: "Invalid object name" with -2147217865 as the error message.

Here is my code:

Dim testrecord As ADODB.Recordset
Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb.1;" & _
"DSN=intelitrak;" & _
"UID=geir;PWD=test"
conn.Mode = adModeReadWrite
conn.Open

Set testrecord = New ADODB.Recordset
testrecord.Open "Cases", conn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect -- the ERROR occurs here!!!

With testrecord
.AddNew
If Forms(stDocName)!NewReopen = "New" Then
!MasterID = NewID
IsReOpen = False
Else

....etc.

I looked all around for an answer, and I partially found a answer at Microsoft's website: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q218/9/95.asp&NoWebContent=1

However, I do not know how to make this work. I tried to create a stored procedure, and test if that made any differance, but it still said it the it was a invalid object.

Can anyone help me?

GeirJust a shot in the dark...

What if you add dbo_ to the front of Cases|||That was not it.

I tried to put 'dbo_Cases' in my VBA code, but the same error happens.

Geir|||Sorry...mayb a post in the VB forum...

Do you have other code that connects?|||none of my code that involves adding new records to the database is working. The code that views the records, however, works perfectly.

Geir

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmaz
themarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmazthemarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmazthemarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Problem: mssql locks several records in a specific table, while no one is editing them

Hi,
I have an Access application with linked tables via ODBC to MSSQL
server 2000.

Having a weird problem, probably something i've done while not being
aware of (kinda newbie).

the last 20 records (and growing)of a specific table are locked - cant
change them - ("another user is editing these records ... ").

I know for a fact that no one is editing records and yet no user can
edit these last records in the MDB - including the administrator -
while able to add new records.

Administrator able to edit records in the ADP (mssql server) where the
tables are stored.

Please help, the application is renedred inert .

Thanks for reading,
Oren.Hi

I can't say that I am an expert on access, but this sounds like the
recordset may need refreshing i.e someone has changed the record and access
knows the local copy is out of date.

John

"Oren" <orentini@.hotmail.com> wrote in message
news:b1368c93.0409160909.128bae81@.posting.google.c om...
> Hi,
> I have an Access application with linked tables via ODBC to MSSQL
> server 2000.
> Having a weird problem, probably something i've done while not being
> aware of (kinda newbie).
> the last 20 records (and growing)of a specific table are locked - cant
> change them - ("another user is editing these records ... ").
> I know for a fact that no one is editing records and yet no user can
> edit these last records in the MDB - including the administrator -
> while able to add new records.
> Administrator able to edit records in the ADP (mssql server) where the
> tables are stored.
> Please help, the application is renedred inert .
> Thanks for reading,
> Oren.|||Hi,
thanks for replying.

There is no local and remote copy of the table, the table is only
stored in the sql server and linked to the access MDB.

Simple refresh wont do (tried it, via the access menubars).

have any more suggestions?

Thanks,

Oren,

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<414b53f6$0$27296$afc38c87@.news.easynet.co.uk>...
> Hi
> I can't say that I am an expert on access, but this sounds like the
> recordset may need refreshing i.e someone has changed the record and access
> knows the local copy is out of date.
> John
> "Oren" <orentini@.hotmail.com> wrote in message
> news:b1368c93.0409160909.128bae81@.posting.google.c om...
> > Hi,
> > I have an Access application with linked tables via ODBC to MSSQL
> > server 2000.
> > Having a weird problem, probably something i've done while not being
> > aware of (kinda newbie).
> > the last 20 records (and growing)of a specific table are locked - cant
> > change them - ("another user is editing these records ... ").
> > I know for a fact that no one is editing records and yet no user can
> > edit these last records in the MDB - including the administrator -
> > while able to add new records.
> > Administrator able to edit records in the ADP (mssql server) where the
> > tables are stored.
> > Please help, the application is renedred inert .
> > Thanks for reading,
> > Oren.|||Hi

You may want to post this is an access news group!

John

"Oren" <orentini@.hotmail.com> wrote in message
news:b1368c93.0409180015.718465e6@.posting.google.c om...
> Hi,
> thanks for replying.
> There is no local and remote copy of the table, the table is only
> stored in the sql server and linked to the access MDB.
> Simple refresh wont do (tried it, via the access menubars).
> have any more suggestions?
> Thanks,
> Oren,
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<414b53f6$0$27296$afc38c87@.news.easynet.co.uk>...
> > Hi
> > I can't say that I am an expert on access, but this sounds like the
> > recordset may need refreshing i.e someone has changed the record and
access
> > knows the local copy is out of date.
> > John
> > "Oren" <orentini@.hotmail.com> wrote in message
> > news:b1368c93.0409160909.128bae81@.posting.google.c om...
> > > Hi,
> > > I have an Access application with linked tables via ODBC to MSSQL
> > > server 2000.
> > > > Having a weird problem, probably something i've done while not being
> > > aware of (kinda newbie).
> > > > the last 20 records (and growing)of a specific table are locked - cant
> > > change them - ("another user is editing these records ... ").
> > > > I know for a fact that no one is editing records and yet no user can
> > > edit these last records in the MDB - including the administrator -
> > > while able to add new records.
> > > > Administrator able to edit records in the ADP (mssql server) where the
> > > tables are stored.
> > > > Please help, the application is renedred inert .
> > > > Thanks for reading,
> > > Oren.

problem with varchar and nvarchar datatype in linked server

Hi,

I am updating a remote table using linked server in sql server 2005.

but in case of varchar and nvarchar i am getting an error :

"OLE DB provider "SQLNCLI" for linked server "LinkedServer1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 16955, Level 16, State 2, Line 1

Could not create an acceptable cursor."

thanks in advance.

Thanks & Regards

Pintu

Pintu, I had a similar problem with deleting rows through a linked sever from SQL Server 2005. I tried from SQL Server 2000 and got a slightly different error :-

"The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements."

which led me to this article:-

http://support.microsoft.com/kb/814581

which solved my problem. Hope it helps you too.

problem with varchar and nvarchar datatype in linked server

Hi,

I am updating a remote table using linked server in sql server 2005.

but in case of varchar and nvarchar i am getting an error :

"OLE DB provider "SQLNCLI" for linked server "LinkedServer1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 16955, Level 16, State 2, Line 1

Could not create an acceptable cursor."

thanks in advance.

Thanks & Regards

Pintu

Pintu, I had a similar problem with deleting rows through a linked sever from SQL Server 2005. I tried from SQL Server 2000 and got a slightly different error :-

"The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements."

which led me to this article:-

http://support.microsoft.com/kb/814581

which solved my problem. Hope it helps you too.

problem with using linked server

:) hello everybody, i am using oracle as linked derver to my sql server db...when i write the query to access data from linked server(oracle) iam getting error like this...iam helpless..pls give ur ideas..
my query is: SELECT * FROM EOPI..EOP.CONTRACT
and the error is
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: ORA-12638: Credential retrieval failed]..

thax and regards
kiran
senior software engineer :cool:When querying by linked server you have to use openquery.

SELECT * FROM OPENQUERY(linkedservername,'SELECT * FROM pubs')

Problem with update tables on linked Sybase server

While working on MsSQL 2000 I'm trying to update table on remote server
(Sybase)
Here is the description of remote table (Sybase)
IF OBJECT_ID('adm.TransClient') IS NOT NULL
drop table adm.TransClient
go
CREATE TABLE adm.TransClient
(
CUST_ID numeric(10,0) NOT NULL,
NAME varchar(254) NOT NULL,
ADR1 varchar(254) NOT NULL,
ADR2 varchar(254) NOT NULL,
CREDIT numeric(17,9) NOT NULL,
SALES varchar(30) NOT NULL,
INN varchar(30) NULL,
TYPEC smallint NOT NULL,
NO_NDS smallint NOT NULL,
NUM_DOG varchar(20) NOT NULL,
LOGIN varchar(16) NOT NULL,
BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
TransType varchar(20) DEFAULT "insert" NOT NULL,
TransDate datetime DEFAULT getdate() NOT NULL,
Transferred smallint DEFAULT 0 NOT NULL,
CONSTRAINT PK1
PRIMARY KEY NONCLUSTERED (CUST_ID)
)
LOCK datarows
go
1. Doing like this
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1
MsSQL returns:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Query-based update failed because the row
to update could not be found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
returned 0x80004005: ].
2. Second atempt
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7
MsSQL returns:
1 row affected
3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
Transferred=1
Althogh MsSQL has returned:
Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.
After that
select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=1 = 202 !!!!!!!
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=0 = 48 !!!!!!!!
No comments from my side - simply do not understand what is going on
Any answers are appreciated
Hi
0x80004005 = Access Denied. Check that you have permissions on the
destination server for what you are doing.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> While working on MsSQL 2000 I'm trying to update table on remote server
> (Sybase)
> Here is the description of remote table (Sybase)
> IF OBJECT_ID('adm.TransClient') IS NOT NULL
> drop table adm.TransClient
> go
> CREATE TABLE adm.TransClient
> (
> CUST_ID numeric(10,0) NOT NULL,
> NAME varchar(254) NOT NULL,
> ADR1 varchar(254) NOT NULL,
> ADR2 varchar(254) NOT NULL,
> CREDIT numeric(17,9) NOT NULL,
> SALES varchar(30) NOT NULL,
> INN varchar(30) NULL,
> TYPEC smallint NOT NULL,
> NO_NDS smallint NOT NULL,
> NUM_DOG varchar(20) NOT NULL,
> LOGIN varchar(16) NOT NULL,
> BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
> TransType varchar(20) DEFAULT "insert" NOT NULL,
> TransDate datetime DEFAULT getdate() NOT NULL,
> Transferred smallint DEFAULT 0 NOT NULL,
> CONSTRAINT PK1
> PRIMARY KEY NONCLUSTERED (CUST_ID)
> )
> LOCK datarows
> go
> 1. Doing like this
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1
> MsSQL returns:
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: Query-based update failed because the
row
> to update could not be found.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
> returned 0x80004005: ].
> 2. Second atempt
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1 where CUST_ID=7
> MsSQL returns:
> 1 row affected
>
> 3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
> Transferred=1
> Althogh MsSQL has returned:
> --
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> --
> After that
> select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=1 = 202 !!!!!!!
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=0 = 48 !!!!!!!!
> No comments from my side - simply do not understand what is going on
> Any answers are appreciated
|||Surely I have access to this server
Otherwise I couldn't do
update no.2
(update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7)
that completed successfully
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> 0x80004005 = Access Denied. Check that you have permissions on the
> destination server for what you are doing.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
> news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> row
>
>

Problem with update tables on linked Sybase server

While working on MsSQL 2000 I'm trying to update table on remote server
(Sybase)
Here is the description of remote table (Sybase)
IF OBJECT_ID('adm.TransClient') IS NOT NULL
drop table adm.TransClient
go
CREATE TABLE adm.TransClient
(
CUST_ID numeric(10,0) NOT NULL,
NAME varchar(254) NOT NULL,
ADR1 varchar(254) NOT NULL,
ADR2 varchar(254) NOT NULL,
CREDIT numeric(17,9) NOT NULL,
SALES varchar(30) NOT NULL,
INN varchar(30) NULL,
TYPEC smallint NOT NULL,
NO_NDS smallint NOT NULL,
NUM_DOG varchar(20) NOT NULL,
LOGIN varchar(16) NOT NULL,
BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
TransType varchar(20) DEFAULT "insert" NOT NULL,
TransDate datetime DEFAULT getdate() NOT NULL,
Transferred smallint DEFAULT 0 NOT NULL,
CONSTRAINT PK1
PRIMARY KEY NONCLUSTERED (CUST_ID)
)
LOCK datarows
go
1. Doing like this
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1
MsSQL returns:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Query-based update failed because the
row
to update could not be found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
returned 0x80004005: ].
2. Second atempt
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7
MsSQL returns:
1 row affected
3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
Transferred=1
Althogh MsSQL has returned:
--
Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.
--
After that
select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=1 = 202 !!!!!!!
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=0 = 48 !!!!!!!!
No comments from my side - simply do not understand what is going on
Any answers are appreciatedHi
0x80004005 = Access Denied. Check that you have permissions on the
destination server for what you are doing.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> While working on MsSQL 2000 I'm trying to update table on remote server
> (Sybase)
> Here is the description of remote table (Sybase)
> IF OBJECT_ID('adm.TransClient') IS NOT NULL
> drop table adm.TransClient
> go
> CREATE TABLE adm.TransClient
> (
> CUST_ID numeric(10,0) NOT NULL,
> NAME varchar(254) NOT NULL,
> ADR1 varchar(254) NOT NULL,
> ADR2 varchar(254) NOT NULL,
> CREDIT numeric(17,9) NOT NULL,
> SALES varchar(30) NOT NULL,
> INN varchar(30) NULL,
> TYPEC smallint NOT NULL,
> NO_NDS smallint NOT NULL,
> NUM_DOG varchar(20) NOT NULL,
> LOGIN varchar(16) NOT NULL,
> BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
> TransType varchar(20) DEFAULT "insert" NOT NULL,
> TransDate datetime DEFAULT getdate() NOT NULL,
> Transferred smallint DEFAULT 0 NOT NULL,
> CONSTRAINT PK1
> PRIMARY KEY NONCLUSTERED (CUST_ID)
> )
> LOCK datarows
> go
> 1. Doing like this
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1
> MsSQL returns:
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: Query-based update failed because the[/vbco
l]
row[vbcol=seagreen]
> to update could not be found.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
> returned 0x80004005: ].
> 2. Second atempt
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1 where CUST_ID=7
> MsSQL returns:
> 1 row affected
>
> 3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
> Transferred=1
> Althogh MsSQL has returned:
> --
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> --
> After that
> select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=1 = 202 !!!!!!!
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=0 = 48 !!!!!!!!
> No comments from my side - simply do not understand what is going on
> Any answers are appreciated|||Surely I have access to this server
Otherwise I couldn't do
update no.2
(update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7)
that completed successfully
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> 0x80004005 = Access Denied. Check that you have permissions on the
> destination server for what you are doing.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
> news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> row
>
>