Update Sql Errors Using Linked Server

Jul 23, 2005

Hi,

I'm using sql server 2000 sp4.
I've 2 databases linked, an instance and my local.
I'm getting two different errors when trying to update the remote table
(local server) from the instance.
There is only one row of data in the table with an identity field.

1st sql:-
UPDATE [local].[database].dbo.NUMBERS SET [f 1]=3

This gives me the error:-
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.

If I was to remove the space from [f 1] and use [f1] it would work
fine.

"select [f 1] from [dev001].[fashion Master].dbo.numbers"
will return the correct value

Any Ideas ?


2nd sql:-
UPDATE [local].[database].dbo.NUMBERS SET [field1]=isnull([field1],0)+1


This gives me the error:-
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"fashion Master"."dbo"."NUMBERS"' 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...

If I was to remove the isnull part, then it will work ok

Any ideas

View 3 Replies


ADVERTISEMENT

Strange Errors Around Linked Server

Oct 12, 2006

Hi,

I am getting a linked server connectivity errors randomly. We have a set of DTS packages to pull data from OLTP (SQL 2000) to reporting server (SQL 2005) through linked server and both instances are on same server. The process is running ok on Acceptance server, and failing on production server. It is throwing the following error randomly, and it is getting fixed for a while after restarting SQL 2000 service.

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "TCP Provider: The specified network name is no longer available. ". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "OLE DB provider "SQLNCLI" for linked server "GIS_STG" returned message "Communication link failure".". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.".

Appreciate any insights on this annoying issue.

Thanks,

Veera

View 2 Replies View Related

T-SQL (SS2K8) :: Pulling Errors From A Linked Server?

Jan 28, 2013

I've got a linked server to an attomix database. When i send an erroneous statements to the server an error message is returned to the Message pane is SSMS as below:-

OLE DB provider "MSDASQL" for linked server "noble" returned message "[Noble Systems Corp.][ATOMIX ODBC Driver]Atomix error - (-217)Column (crap) not found in any table in the query.

".Msg 7215, Level 17, State 1, Line 5, Could not execute statement on remote server 'noble'.

I'm trying to store this error using the ERROR_MESSAGE() function but i only get the second statement ('Could not execute statement on remote server 'noble'.') rather than the real error in the first line. The first line statement is displayed in black as a message rather than red for an error.

how i can access the first error?

View 3 Replies View Related

T-SQL (SS2K8) :: Errors When Inserting Rows From A Linked Server

Aug 14, 2015

I have a script that inserts rows from a linked server. It basically looks like this:

use mydb;
go
insert into my.table (col1, col2, ...)
select col1, col2, ...
from LinkedServer.db.my.table;

The DDL for both tables is identical: 550 columns (de-normalized from a data warehouse), of which 548 are varchar(max). The remaining two are varchar(255). (not my design! but unchangeable at the moment).

Running my query raises an error:

Cannot create a row of size 9948 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.

Now, inserting rows on the table on the LinkedServer has never been an issue. (Since most columns are varchar(max), SQL has the option to store the data off-row.) The data is there, intact and whole. However, when pulling it into my target server using a LinkedServer (4-part naming), I get the error.

Three pairs of eyes have confirmed that the table definitions are identical on both source and target.

View 9 Replies View Related

Queries Against Linked Server To Informix Via ODBC Errors

Oct 9, 2007

What can be the reason(s) why I can't get data from a linked server using an ODBC datasource that works fast & fine from MS Access?


I have an ODBC connection (System DSN) configured for an Informix ODBC driver.
The Test button (belonging to this driver-setup) reports a successfull connection test.
Getting data from this database by linking tables in MS Access works fast and easy.
But I have tried for many days now to setup a linked server from SQL Server (2005)
Creation goes fine, but as soon as I issue a query, (e.g. 'select * from infrem723...remotetable' or using 'openquery')
I get the following error:
---

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "infrem723" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "infrem723".

---

Why is Access able to read what "MSDASQL" cannot?
I am desperate - can anybody help?Thanks a lot!

View 8 Replies View Related

Update On Machine Runs Immediately, Update On Linked Server Takes 8 Minutes

Jan 2, 2008

What's up with this?

This takes like 0 secs to complete:

update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')

but From linked server this takes 8 minutes????!!!??!:

update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')


What settings or whatever would cause this to take so much longer from the linked server?

Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.

2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.

View 5 Replies View Related

Linked Server Errors After Failover But Works When It Is Failed Back

Feb 15, 2008

We are using SQL Server 2005 (9.0.3054 on Windows 2003 RC2 SP2 with clustering two instances. We are running an instance on both boxes.
I have 2 linked servers to the same server on the first box (SQL1C) that use different SQL Logins to connect to the same box but to different databases. One uses the name of the server and one uses the name of the server with a 2 at the end of it. (servername and servername2). The second linked server was added recently and is used very frequently by the new application. The other thing is there is a server with the name servername2. They are SQL boxes but I am not sure what version or what they are running on. If it turns out to be important, I will ask.

Here's what is happening:
When we fail over to the second box, up until recently, we had no problems. All linked servers worked well. But since we have added this new linked server, when we fail over, the linked server no longer works and we get an error like from the Cold Fusion application:

[Macromedia][SQLServer JDBC Driver][SQLServer]TCP Provider: An existing connection was forcibly closed by the remote host.


I got a similar error in EMS, but did not copy it to save it. Since this is a production server, I can't really just try things.

We checked to make sure all IPs from box1 and box2 are allowed access to the server and they are not using IP blocking for inside the university domain.

There are no errors in the SQL Logs. I checked the application logs and found no errors for the linked server. The only error I could find and I didn't think it was related but I will include it was this:


Closed event notification conversation endpoint with handle '{4F54167A-F3D0-DC11-97FE-000E0CB2D7CA}', due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.


Any ideas? One of our pplications goes down when the cluster fails over now. Help PLEASE!

Thanks,
Linda

View 7 Replies View Related

SQL Server CE Update Query Causing Errors

Jan 14, 2005

Hello all,

Thought I would post here in case anybody can give some information.

Here is the background information:

I have 2 tables (stores and sales) from the Pubs database in Sql Server 2000 copied down to a SQL Server CE database. There is no foreign key/primary key relationship between the 2 tables in the CE database.

Here are the update queries that cause the error:

UPDATE st
SET st.zip = 66668
FROM stores st
INNER JOIN sales sa ON st.stor_id = sa.stor_id
AND st.stor_id = 6380

Update stores SET stores.zip = 55555
FROM sales, stores
WHERE stores.stor_id = 6380
AND stores.stor_id = sales.stor_id

Here is the error message that is generated when I run the query (Param 0 and Param 1 change according to what column and line the FROM clause is in):

Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25501)
Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]
Interface defining error: IID_ICommand
Param. 0: 2
Param. 1: 1
Param. 2: 0
Param. 3: FROM
Param. 4:
Param. 5:

I ran the 2 queries in SQL Query Analyzer in SQL Server 2000 and they worked just fine. I also created 2 new tables (stores1 and sales1) in SQL Server 2000 using the Select Into clause. The new tables were created from the sales table and stores table in the Pubs database. The new tables had no foreign key/primary key relationship.

I ran the queries again in Query Analyzer against the new tables and the queries produced no errors.

Any suggestions?

Thank you,
Aaron B

View 1 Replies View Related

Linked Server Update

Sep 4, 2003

Updating the sp's fixed the error I was getting but the update takes forever on the linked server. Any way to speed things up?

I just reference the server.db.dbo.table in the update statement.

TIA

View 1 Replies View Related

Linked Server Update

Feb 6, 2002

I know how to do a select statement using linked servers but I need to update a table in Oracle using SQL Server.

For example:
SELECT * FROM OPENQUERY(Oracle, 'SELECT * FROM TEST')

I now need to update TEST using a table in SQL Server. Can someone help?

View 1 Replies View Related

How Can I Update Linked Server (AS400 DB)?

May 16, 2000

Hi!

Please

I got the following Error Message when trying to update a AS400 Database
"(Table Name) on (Data Source) not valid for operation"

My question is? Does Sql Server require journaling the files? How Should I change this option?. Please Help!

I did try to fix the above error, but then I got a new message:

OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.
Could you help me?

I will appreciate your help.
Thanks in advance

Ivette V

View 3 Replies View Related

Linked Server Update Error

Jan 20, 2004

Does Anyone know what this means?

I run the query in the attached file on multiple servers with success but some of them refuse to cooperate. I've tried recreating the linked server on the problem servers but this doesn't help. All I want to do is check db file size and free space and get it to work. Why does it have to be so damn complicated?

Any ideas welcome - including an entirely different way of doing this.

The script and table definitions in question are attached

Could not open table '"Helpdesk_New"."dbo"."LogStats"' 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...

View 1 Replies View Related

Linked Server Update Problem

Mar 15, 2004

I am getting an error trying to update a table in DB2 via SQL Server using a Linked Server. I can query the table using OPENQUERY but not via four-part name.

I can do this...

SELECT

Select * from OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn = 2')

When I try to Query using four-part names I get the following error...

7399 OLE DB Provider 'MSDASQL' reported an error. The provider does not support the necessary method.

UPDATE

When I try to update using this...

Update OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn=2') Set frst_nm = 'Fred'

I get the following error...

The OLE DB provider 'MSDASQL' indicates that the object has no columns.

When I try to update using four part names I get the error that says that the provider does not support the necessary method.

OTHER INFO

I was originially using version 6.1 of the db2 client (db2odbc.dll) but tried the 7.1 client and got the same error.

I believe we are using version 6.1 of db2 connect.

The mainframe db2 is version 7.0 release 1.0

Any help would be greatly appreciated. I referenced Microsoft Knowledge Base Article #270119 to get the workarounds that I attempted...

Gary

View 2 Replies View Related

Dynamic Update Linked Server

Nov 28, 2014

How to write this query so that I can pass a variable (from a cursor) for the were ORDER_ID =.

UPDATE OPENQUERY ([DISPATCHER_LIVE], 'select * from DCSDBA.ORDER_HEADER where ORDER_ID = ''405119-RM18''')
SET CONSIGNMENT = 'UNROUTED'

View 2 Replies View Related

Trigger On Update No Linked Server

Sep 13, 2007



I have 2 database servers ( 2 hardware-servers : A and B ) and I ve written a trigger for update in server A to execute insert statement in server B through linked servers, is there other way to achieve this without linked servers?? All using T-SQL.

Best Regards

Joseph

View 4 Replies View Related

Slow Update With A Linked Server

May 11, 2007

I have a linked server set up on my local SQL 2000 instance. I try and run an update against an SQL 2005 database and it take 29 seconds. I checked the execution plan and it says it takes the entire time on the Remote Scan. Is there something I need to do to speed this up? There is an index on the PK that I am searching against.

View 2 Replies View Related

Linked Server Update Statement

Jan 11, 2008



I've got the output from a cursor that generates the following sql statement.


Update SERVERNAME.DATABASENAME.dbo.TABLENAME set [update] = 'Y', status = NULL, completed = NULL where trigger_id = 10255

The statement generates the following error if the statement is run remotely

Msg 8180, Level 16, State 1, Line 1

Statement(s) could not be prepared.

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'update'.


But the statement executes without error as formatted if run directly on the linked server.

Both servers are running 2000 sp 3a. The servers will be converted to 2005/2008 without patching to sp4.

View 5 Replies View Related

Update Trigger On Linked Server View

Jul 20, 2005

Hi there,I'm pretty new to SQL and am having some porblems with a linked server.I have a table on a SQL server which stores employee information.I also have a view on a linked server which stores the same information.What I would like to happen is, whenever the view changes on the linkedserver I want the information to be changed in the table on my server.I've been trying to write a trigger to do this, but have had noluck so far.Can anyone help me?ThanksSimon--Posted via http://dbforums.com

View 1 Replies View Related

Update In Sybase 11 Linked Server Problem

Jul 27, 2005

When trying to update records in a Sybase 11 ODBC linked server got this error:

View 5 Replies View Related

Linked Server Update Table Error

Jul 31, 2015

We have 2 instances. One is sqlgpscluster and another is [sqlcdsclustersqlcds]. We have created linked server between sqlgpscluster and [sqlcdsclustersqlcds]. We run the below query on sqlgpscluster instance

UPDATE [sqlcdsclustersqlcds].CDSBusiness.dbo.tblInsertNowAppMonitoring
SET NoCabsDateTime = GETDATE(),City=@City
,Area = dbo.fn_FindAreaSubAreaBasedOnLatLong(@PickUpLat,@PickUpLng)
WHERE CustomerMobileNo=@CustomerMobileNo

We are seeing the below error:
The OLE DB provider "SQLNCLI" for linked server "sqlcdsclustersqlcds" could not UPDATE table "[sqlcdsclustersqlcds].[CDSBusiness].[dbo][tblInsertNow AppMonitoring]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

View 16 Replies View Related

VFP And Linked Servers OLE DB Errors

Feb 24, 2005

In migrating our back end database from VFP8 to SQL we use linked servers to do a variety of tasks as well as some customized reporting applications. We are having issues with setting up the linked servers in SQL 2000 to VFP8 tables.

When you set up a linked server, locally it seems to work ok, sometimes we have authentication problems and have to switch back and forth between Windows and SQL which is annoying but manageable.

However when you start using the same SQL instance from other boxes by adding it in Enterprise Manager errors occur.

Error 7303: Could not initialize data source object of OLE DB provider 'vfpoledb.1'.

OLD DB Trace Error [OLE/DB Provider 'vfpoledb.1' IDBInitialize::Initialize returned 0x80040e21].

I have downloaded and installed the VFP8 OLE DB .dll from the Microsoft website but still have this issue. Can anyone shed any light?

Thanks!

View 1 Replies View Related

Remote Update Having A Linked Server Takes Forever To Execute

Oct 17, 2006

UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,

col8=SR.col8,col9=SR.col9,col10=SR.col10

FROM LNKSQL1.db1.DBO.Table1 CD

join Table2 USRI on USRI.col00 = CD.col00

join table3 SR on USRI.col00 = SR.col00

Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you

View 4 Replies View Related

Remote Scan On Linked Server - Fast SELECT/Slow UPDATE

Aug 14, 2001

In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.

However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.

I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.

I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).

How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?

Thank you!

View 2 Replies View Related

SQL Server 2008 :: How To Make Sproc Return Errors For Underlying Table Errors

Jul 1, 2015

I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.

The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?

View 1 Replies View Related

Insert Or Update Errors

May 1, 2007

Is there a way I can stop a form from inserting or updating a record when there is an error.  I have an sql 2000 DB.  I have noticed that if the db field can handle 50 characters and the form field has no limit on the number of characters no errors are displayed to the user if they try to use more than the 50 characters in the textbox.  The record is not saved.  None of the fields are saved. I do notice the autonumber generated is skipped by the db.  That is, the next autonumber for a successful insert skips the logical next number.   How do I capture this error, or any save error and return the user back to the form?  Yes, I have limited the number of characters a user can type on the textbox now, but I would really like to catch save or insert errors.  I use asp.net 2.0 and VB.  I don't know C#. thanksMilton

View 5 Replies View Related

Update Trigger And Optomistic Concurrency Errors

Apr 25, 2008

I wrote an update trigger to set a "Last Updated" value in a table.

I think that this may be causing optomistic concurrency errors when a recordset is updated more than once without being refreshed.


1) Does it make sence that this could be the problem
2) Is there a good way around this?

thanks
Jacob

View 4 Replies View Related

Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager

Mar 25, 2002

Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"

if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)

And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP

Thanks

View 5 Replies View Related

DB Engine :: How To Point Linked Server To Specific Database / Rename Linked Server

Apr 24, 2015

I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.

I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.

How to point the linked server to a specific database? How to rename the Linked Server?

The following is the code that I am using right now:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'Machine123Instance456',
    @srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'  

View 6 Replies View Related

Scripting Stored Procedure Add With Linked Server Reference When Linked Server Is Not Available

Jul 18, 2006

Is there a way to bypass the syntax checking when adding a stored procedure via a script?

I have a script that has a LINKed server reference (see below) .

INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.

ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.

PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.

Thanks,

Terry

View 4 Replies View Related

RS 2000 - Can't Start Report Manager / Permission Errors / Need To Update Data Source

Jul 5, 2007

We are going insane trying to start Report Manager on a SQL Reporting Services 2000 installation. The programmer/admin who originally set this up for us is gone.



We recently upgraded a database/application server to a new server, causing the data source being used by reports in reporting services to no longer be valid. Unfortunately, we do not have access to the original report project to 're-deploy' with the corrected data source.



We desperately need to get the reports that are installed to retrieve their data from the new database location/machine. We understand this can be done by specifying a new data source in Report Manager. (To clarify, we have NOT moved the report servier installation or database, these remain in place - it's just the deployed reports that no longer point to the correct data source.)



For some reason, our Report Manager will no longer run - when we try to launch it, we get a windows login dialog - nothing will work here. We've tried both local and domain admins and constantly get ACCESS IS DENIED 401.3 error messages that we do not have permission/problems with ACL's.



We've gone so far as to allow EVERYONE read/write access to the ReportManager and ReportServer folders and the RS virtual directories, but nothing seems to help.



Can anyone help with this? Ideas on how to change our data source, or how to get back into Report Manager?



Since we are somewhat technical, but not experts, and don't have much more time to waste, we are willing to pay $500 for this project to someone willing to access the server and fix the problem so that the reports point to the correct database and restore access to Report Manager.



Thanks in advance for any help.

View 1 Replies View Related

Update Table From Linked Excel Sheet

Mar 20, 2008

I cant seem to reference columns within a linked Excel server
using MS SQL express

Code:

UPDATE dbo.Items
SET CCC=XL_SERVER.CCC
FROM XL_SERVER...sheet1$
WHERE BBB LIKE '7%'

i.e I want to update column CCC in my Items table from an Excel table column CCC where column BBB begins with 7

I have set up XL_SERVER correctly.

Help!

View 3 Replies View Related

Cannot Update Tables Linked To Access Front-end

Sep 26, 2006

Hi,
I've a bit of a problem that I'm stuck on.

I have a system that has the tables on an SQL Server with the front-end on a Microsoft access database. They are connected via odbc and the tables are linked to the access front-end. The odbc connects to the sql server via a user created for the purpose that only has rights to this database. This user has rights set so that they can access/alter any data in the database.

When my users try to alter data on some of the linked tables they get "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." This does not happen to every table only on some of them.

This happens even when there is only one user accessing the system. If I log into the enterprise manager I can alter those tables/ records without any problem (as some articles I’ve read say if the problem is connected to a corrupt record its not possible to then alter that record and sql server should give me an error message instead).

I have tired.
- Compact and repairing the access front-end.
- Deleting the links in the access front-end then re-linking them.
- Copying the objects from the access to a new blank access db.
- I have tried altering the account the odbc uses to login to the SA account.
- I have asked the server admin to do the 'compact and repair' on the s.q.l server, shrink I believe it is, as some articles suggest this could solve the problem.
- The admin has also tried this on the transaction files (so he has told me).

None of this has worked.

I'm stuck. I don't have any training on SQL server (nor will I be able to have any as I gather the training budget may be needed to fill some finance holes). If anyone has any idea of the answer to my problem or could point me in a direction that I could try investigating I would be very grateful. I have asked the admin if it’s anything to do with transactions, he has told me he does not know how to see those but he will try to find out if we can. Am I shooting in the dark or does this sound like a transaction/process locking problem? Is there something I should be looking for?

Thanks in advance.

View 3 Replies View Related

Update Field Using Value From Linked 2000 Sql Data

Jul 11, 2007

I'm trying to update a field (tmpRequestID) in a SQL Express table (tblTSubRequest) using a value in a SQL 2000 database field (fldtmpRequestID) in the dbo.NewPurchase table.

The procedure I thought would work is:
USE [tempPurchase]
GO
/****** Object: StoredProcedure [dbo].[UpdateSubfldRequestID] Script Date: 07/11/2007 15:01:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateSubfldRequestID]
AS
BEGIN
-- Insert statements for procedure here
UPDATE tblTSubRequest
SET tmpRequestID = tblRequest.fldRequestID
FROM tblTSubRequest INNER JOIN
[NGTXA4-RSMSZ-01].NewPurchase.dbo.tblRequest ON tblTSubRequest.fldRequestID = tblRequest.fldRequestID
WHERE (tblRequest.fldUpdateCode = 99)
END

It appears that the procedure cannot read the field value on the 2000 server and the 2000 server is linked to the 2005 express edition.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved