Error In Accessing Data Via Linked Server
I am trying to access the View through linked server connection .The linked server connection is between two sql server 2005.
The problem is I am not able to access 2 columns of nvarchar datatype of length 255, from the view through select statement.
I do a SELECT col1,col2 from VIEW,then getting the following error .
Cannot get the data of the row from the OLE DB provider "SQLNCLI" for linked server "SRVXPR". Could not convert the data value due to reasons other than sign mismatch or overflow.
Then I tried with
Select cast(Col1 as nvarchar) ,
cast(col2 as nvarchar) from VIEW,
I am able to get the values.
The collation type is same for all underlying tables and also for the servers.
Is there any restrictions in handling nvarchar data thru linked server/ anyother size limitation.
I don't want to use cast as this will lead to performance problem.
Iím having the fallowing error when I try to open/access a linked server
Error 7302: Could not create an instance of OLE DB provider 'MSOLAP.2'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP.2 returned 0x80004005].
I have already tried to create the server using SEM and also with SQL syntax in ISQLW.
Iím allowed to create the linked server but I canít use it.
OS: Windows 2003EE
SQL: MSSQL 2000 + SP3a
MDAC: MDAC2.8 rtm
I've created a linked server accessing an Oracle database.
I noticed that I can get data only from tables (I see no views listed).
I'm using Microsof OLE DB Provider for Oracle. I've already made sure the user accesses at least one view (by the way, this user accesses all views/tables in the database).
Is there something I am missing?
Thank you very much
I need to invoke a UDF on a linked server. Is this possible?
I need to execute the following query from Server1
from Server2.Database.owner.fnGetTree() AS Server2Tree
inner join Database.owner.fnGetTree() AS Server1Tree
on Server1Tree.NodeId = Server2Tree.NodeId
Thanks for your time
Is there any way to access a linked server without using four-part naming?
My problem is that I am trying to find a way to connect to an access database on a 64 bit system (there is no 64 Jet OleDb provider) and I have to run in a 64 bit process so no WoW solutions will work for me .
I am looking into trying to use a Sql Linked server but am trying to find a workaround without having to re-write all our queries to use four-part naming.
Is there someway to configure a connectionstring to default to executing against a linked server??
Any help or suggestions would be welcomed.
I created a linked Excel server that is stored in a SQL2000 database.
I can run the following from the SQL server with no problem.
Select * From CSCNEDI...EDI$
When I try and run the select from my WinXP computer I get the following from both SQL2000 Query Analyzer or SQL2005 Management Studio (these are configured for client access)
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
I am running dts in Sql Server 2005 management studio from Management, Legacy and data Transformation Services.
Once the dts has run, I get this error message "Error Source : Microsoft Data Transformation Services (DTS) Package Error Description : Error accessing Windows Event Log."
Please help me
thanks in advance
I'm trying to extract all the users and their membership to groups, and the membership of groups to groups from active directory though a link to server.
I can get the users. I can get the groups.... individually.
I can't get the info of what user is a member of or who are members of a group.
Anyone know how to do this or am I going to have to right a vb app? (Anyone already got the code...)
I want to load this data into tables for reporting in my Data Warehouse.
I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts
OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'abc'.
------ Error Message Ends
Consider login name is abc.
Now this login abc has sysadmin rights on sql server 2005.
The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options;
1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 .
2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help.
We are attempting to access information in e-directory via a linkedserver in SQL Server 2000. The problem is, is that we can create alinked server and see the tables, but cannot view their data or runqueries against the tables. With the ODBC for NDS driver installed onmy PC, I can easily query the data using MS Access, though.Simple queries, created in Query Analyzer, just run and run and neverfinish or return anything.I'm not sure if we have not configured the linked server with allnecessary information, or if we are not referring correctly to thedatasource in the query created in query analyzer. We are referringto the datasource as <linkedservername>.<novelltreename>..<tablename>.Any hints or help with this would be greatly appreciated!
I've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.
Msg 7372, Level 16, State 4, Line 1
Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723
Linked Server Script:
/****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120'
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true'
I've tried searching the Internet for solution but yielded no results. Can anyone help?
I tried to select records from remote server using following sql and received the error. Could anyone please help.
select * from <linkedserver>.<dbname>.dbo.<tablename>
Server: Msg 7353, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used
The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.
Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?
I am trying to do this select over a SAP table:
select * from KNA1
But in DataReader component raises an error pointing that Z_EXTRACT_DATA_FULL function module can't be found.
Anyone know where is the problem??
I created the shared data source and the report in VS 2005. After deploying the report to the Report server and trying to access it produces the following error.
An error has occurred during report processing.
Cannot create a connection to data source 'kv_testQA'.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
I checked to see if the Data source exists in the Report Server and it does. But it still produces the error. What is the issue here.
Thanks for the help.
is there a step by step paper to get there? here is what i need to consider. I Iwill have many customers that will need their own set of records and access pages "branded for their company" each customer will have many clients. I am hosting this application on a windows 2003 server with SQL 2005 server enterprise.
I am using windows authentication, I have created a username in windows, then i added the windows user in SQL management studio in security, granted "DB Read" and "DB write" and again under the database security tab. still from the web authentication fails. i must be nissing a step or two?
I expect to set up a username for each database as i setup new customers.
How do we set credentials or contex to a thread.
I create a new thread and within that thread if I am use a connetion string with Integrated Security=True" to talk to the SQL Server, however it seems that the new thread's context/identity is blank and hence failing the SQL Server connection.
Hi,I have a Sybase dataserver containing lots of (huge) tables that Iwould like to access from SQL Server (two different applications,using two different datasources, now want to talk to each other). MSAccess has a feature for 'linking' external data. Can I do similarstuff in SQL Server?Basically, I am trying to avoid having to duplicate the data in boththe dataservers and worry about keeping them in sync.Regards,Tanveer
I am owrking in java developmenton solaris using weblogic6.0
Does any body know how to access sql server data from solaris.
If some body knows that, can you please post me the steps
that i need to do it.
Is there anyway to access system views on/from a linked server?
I have unsuccessfully tried various permutations of
I need some help regarding the data access.
I needs to access some data from tables which are in oracle and load it into sql server tables.
Please let me know the process for this.
Thanks in advance.
I am trying to view a report that i created and I get an error everywhere but on the server, where it runs fine. the report uses the sharepoint lists.asmx web service as a datasource, and I'm using Windows Authentication on the datasource. I'm logged on to my desktop with the same account i use on the server, but on the server it runs and on the desktop i get the following error:
I have a customer that wants to start using SQL Server 7.0 instead of Oracle, but they have a lot of Oracle applications (made in Developer 2000 and Forms). Due to the big effort needed to convert those applications (to Visual Basic), they want to be able somehow to migrate only data from Oracle to SQL Server, and then use some tool that will allow them to use the same applications with the SQL Server 7.0 engine.
I found on the "SQL Server Developer's Kit" a document describing the process of migrating data and applications from Oracle to SQL Server. There is a mention to an Oracle tool called "Oracle Gateway to SQL Server" that will translate calls received by the Oracle engine to the SQL Server engine, but this approach will still require the existence of Oracle servers (which is the thing the customer no longer wants).
The second option mentioned on that document, and the one that seems more attractive, is the use of Oracle "Open Client Adapter" (OCA) that will allow Developer 2000 applications to communicate directly to SQL Server via ODBC. However, I have been unable to find (on the Oracle site) any documentation regarding this tool.
Has anynoe heard or used such tool (OCA) ? Any comments, tips, drawbacks, experience you could share ? Any other idea on how a thing like that could be accomplished ? Perhaps a third-party application that can act as a gateway between the Forms app and ODBC/SQL Server, or that will be able to generate VB code from a Developer 2000/Forms app...
Any help will be appreciated.
Please respond directly to me, as I am not a member of the distribution list.
Luis Francisco Silva
I have a report that accessed a Visual FoxPro 6.0 database via ODBC. Since I upgraded to Visual FoxPro 9.0, now I am getting the error:
Query Server Error
DMS-E-RBI_TABLE The table or view <table name> was not found in the dictionary
I've verified that the right path is setup and I've tried installing about every driver and none of them are working. I created a new database in 9.0 and the I can access it fine. Also, there are old tables that are accessible in the same path, just not the ones I need. Any ideas would be helpful..
i have MS SQL Server 2005 in my server machine
and i have MS SQL Server 2005 client setup in another 3 machines
when i access database from server using management studio in the client machine and while open table, i get the below error
"Object reference not set to an instance of an object. (SQL Editors)"
What is the problem?
How can i solve this?
thanks in advance
Our company wanted to try out SQL Server 2005 Enterprise Edition (64 Bit). So, we were on free trial of the Enterprise Edition for past 5 months. After which we decided to go for SQL SERVER 2005 Standard Edition (64 Bit). And, Last week, we installed the SQL Server Standard Edition (64 Bit) on our server. After installation, everything was restored as before.
The version we are on right now is:
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Strangely, a job which ran fine till then is failing with the following error: And, to be more specific, when this job is run manually in the form of a stored procedure using the query optimizer it runs fine. But, when its executed as a scheduled job on SQL Agent, it fails. The History logs record the following error.
The OLE DB provider "SQLNCLI" for linked server "LV-SQL2" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "LV-SQL2". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI" for linked server "LV-SQL2" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412).
I tried everything possible, even recreated the job, but, no avail.
I also considered the possibility if SQL Agent login account did not have enough permissions. So, I changed the SQL Agent login to windows authentication, but the job still fails. So, its the problem has nothing to do with login accounts either.
For the record, I have cross checked all these too.
1. Enabled the remote connections in Surface Area Config
2. Added local server login to remote server login mappings.
3. Checked 'Rpc' and 'Rpc Out' under server options. Also, the Connection Timeout and Query Timeout have been set to zero (0).
Any suggestions pointing towards problem solution appreciated.
1. Replication Transactional between two servers
-- SQL Server 2005 as Distributors and Subscribers
---SQL Server 2000 as Publishers
2. Linked Servers errors:
" Server (Publication Server) is not configured for DATA ACCESS. (Microsoft SQL Server, Error: 7411)"
Did anyone familiar with this problem?
Hiwhen i try to run a query using linked servers, i get the followingerror.Server: Msg 125, Level 15, State 1, Line 1Case expressions may only be nested to level 10.I do have more than 10 case statements, it works fine when it is lessthan 10. can anyone tell me if there is a way to have more than 10case statements. thanks alot.Jaymy querySelect category, val, Sum(QTY) As QTY , yrFrom(Select val, QTY2 As QTY,KEEP = CaseWhen code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))Then 'N'When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'Else 'Y' EndFromamf a Join linkedserver.source.dbo.table2 b On a.COM = b.COMWhere CATEGORY In ('1') And CODE In ('001','003','004','005')And b.YR Between 2003 And 2004 And b.MON <= 1) xWhere KEEP = 'Y'Group By CATEGORY, YR
I have a "linked server" configured in my SQL Server 2000 (SP4) server, which used to work correctly. However, I had to reinstall SQL Server (I backed up and restored the master/model/etc databases, so all my settings stayed the same). Since then, I've been getting this error when I try to use the linked server:
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].
The linked server is a FoxPro database, which does not use catalog or schema names. So, my select syntax looks like this:
SELECT * FROM Server...Table
SQL Server is aparently expecting something like this:
SELECT * FROM Server.Catalog.Schema.Table
Does anyone know how I can fix it so that it allows the "empty dot" method to work like it used to?'
I created an ODBC connection for a SQL Server Database with Only NT Authentication turned on. I then created a linked server pointing to the ODBC DSN. In query analyzer I can select information from the linked server. When I create a job with one step and try to run the same query, the job fails. The message is:
Could not initialize data source object of OLE DB provider 'MSDASQL'. [SQLSTATE 42000] (Error 7303) Login failed- User: _ Reason: Not defined as a valid user of a trusted SQL Server connection. ] [SQLSTATE 01000] (Error 7312). The step failed.
NOTE: the error message says the user is _. The SQLServer agent is logging on as a domain user.
could someone please explain the following error msg! the code seg that is gen the error is as follows, followed by the err msg. I"m trying to update a table on a linked server (paeddb1.gold). both servers are running MS SQL7
set amt = t.amt
from #temp_peg_control t, paeddb1.gold.dbo.controls_peg p
where t.peg = p.peg
and t.cntl_type = p.cntl_type
and t.fy = p.fy
Could not open table '"gold"."dbo"."controls_peg"' 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: Errors occurred]