Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Error In Accessing Data Via Linked Server


 

Hi,

     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.       

 

Thanks,

Philip

 

                   

                   

 


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Error Open/accessing A MSSQL Linked Server
Hi!

I’m having the fallowing error when I try to open/access a linked server
Error:
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.
Code:
EXEC sp_addlinkedserver
@server='LINKED_OLAP',
@srvproduct='',
@provider='MSOLAP.2',
@datasrc='MyServerName.MyDomain’,
@catalog='MyDataBase'

I’m allowed to create the linked server but I can’t use it.

Information:
OS: Windows 2003EE
SQL: MSSQL 2000 + SP3a
MDAC: MDAC2.8 rtm

Thanks,
Paulo

View Replies !   View Related
Linked Servers: Accessing View Data
Hi!
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
Fábio

View Replies !   View Related
Accessing UDF On A Linked Server
Hi all-

I need to invoke a UDF on a linked server. Is this possible?

eg:
I need to execute the following query from Server1

select *
from Server2.Database.owner.fnGetTree() AS Server2Tree
inner join Database.owner.fnGetTree() AS Server1Tree
on Server1Tree.NodeId = Server2Tree.NodeId

Thanks for your time

View Replies !   View Related
Accessing Linked Server
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.

View Replies !   View Related
Accessing Linked Excel Server
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.

Thanks

David Davis

Schuette Inc.

View Replies !   View Related
Error Source : Microsoft Data Transformation Services (DTS) Package Error Description : Error Accessing Windows Event Log
 

Hi,
 
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
 
Srinivas
 
 
 
 

View Replies !   View Related
Accessing Users && Members In Active Directory Via Linked Server
Hi,

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.

Cheers

Chris

View Replies !   View Related
Problem Accessing A SQL Server 2000 Linked Server From SQL Server 2005
Hi
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.
Regards,
Salman Shehbaz.

View Replies !   View Related
Accessing Novell EDirectory From SQL Server Linked Server
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!

View Replies !   View Related
Error Querying Data From DB2 Linked Server Via IBMDASQL
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.

Error:
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'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120'
GO
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?

View Replies !   View Related
Inconsistent Meta Data Error While Selecting Records From Remote Server Using Linked Server
Hi,

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.


Thanks
John Jayaseelan

View Replies !   View Related
Error Query Data Through Linked Server , SQL Server 2005
Hello,
 
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?
 
Thanks,
Simba

View Replies !   View Related
Error Accessing SAP Via SAP Data Provider
 

Hi,
 
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??
 
Thanks!
 
 

View Replies !   View Related
Error In Accessing Shared Data Source
 

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.






















 

View Replies !   View Related
I Am Accessing Data Using Data Access Pages In IIS 7 To SQL Server 2005 Authentication Is Failing
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.

View Replies !   View Related
Accessing SQL Server Data Using Thread
Hi,
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.
Please help.
Thanks
 
 

View Replies !   View Related
Accessing External Data From SQL Server
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

View Replies !   View Related
Accessing Sql Server Data From Unix
Hello !

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.

Thanks
Veena

View Replies !   View Related
Accessing System Views Via Linked Servers
Is there anyway to access system views on/from a linked server?

I have unsuccessfully tried various permutations of
select *
from [MDEDATAWTDss2005].master.[information_schema.colums]

Thanks

View Replies !   View Related
Accessing Oracle Data In Sql Server 2005
HI,
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.
 
 

View Replies !   View Related
Error Accessing Report From Anywhere But Local Server
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:

View Replies !   View Related
Accessing SQL Server Data From Oracle Developer 2000 Apps
Hello there

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

View Replies !   View Related
Query Server Error When Accessing Visual FoxPro 9.0 Via ODBC
 

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..

View Replies !   View Related
Error While Accessing Database From Server Using Management Studio In The Client Machine
hi all,
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?
plz help
thanks in advance

View Replies !   View Related
Linked Server Authentication Error - [SQLSTATE 42000] (Error 7303)
Hi Guys:

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.

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.

 

Thank you.
 

View Replies !   View Related
Linked Servers Error Microsoft SQL Server, Error: 7411
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?

Thankssss

TJ_1

View Replies !   View Related
Error With Linked Server
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

View Replies !   View Related
Error With Linked Server
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?'

Thanks!
Josh

View Replies !   View Related
Linked Server Error
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.


Any ideas.

View Replies !   View Related
Linked Server Error
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

update paeddb1.gold.dbo.controls_peg
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]

View Replies !   View Related
Linked Server Error
Hi List

I was trying to create linked server. I was using the following syntax to create and it's successful.
sp_addlinkedserver 'Servername'
sp_addlinkedsrvlogin 'Servername',false,Null,'sa',''

While I was trying to execute the following query I got the following error message. Any help will be appreciated.

"Select top 100 * from Servername.DB.dbo.Tablename"

Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.

Thanks in advance.

Regards
Leong...

View Replies !   View Related
Linked Server Error
I got the following error when using linked server:


OLE DB provider "SQLNCLI" for linked server "SACPANRPT" returned message "Cannot start more transactions on this session.".
Msg 7395, Level 16, State 2, Line 1
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "SACPANRPT". A nested transaction was required because the XACT_ABORT option was set to OFF.

View Replies !   View Related
Linked Server Error !!!
Hi Pals,

We have an SSIS package within which we are calling a stored procedure which eventually call a sql server dbo.fn() which contains code to lookup data inside oracle using Linked Server for Oracle.

We are calling the Package dynamically from the stored procedure by creating a SQL Server Agent Job, I am getting the below error very often. Can we fix the error in any way?


“EXEC sp_UpdateTname 369,'939390',2008 " failed with the following error: "Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "ORATEST".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


When I hard code global variables and ran the package I am able to see the successful execution of the package.

Can anybody point out where could the error lies?

View Replies !   View Related
Linked Server Error
HI
 
I have win 2003 64 sp2 SQL 2005 sp2.  I downloaded an informix 64bit driver 3.00FC then set system dsn which works fine when i apply and test the connection.
 
I tried to create a new linked server using Microsoft OLE simple provider against the odbc when I get this error
 
Does anyone have any suggestions??
 
 
Many thanks
 
 
Robert
 
 
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDAOSP" for linked server "CERP". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3186&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

View Replies !   View Related
Linked Server Error
Hello,

SQL Server version is Enterprise Edition 8.00.2039 SP4
and one oracle database linked via Microsoft OLe DB provider for Oracle. Oracle db name: SCP.

 
One of the job inserts data to sql database from querying oracle database;  randomly fails with the error message below.

**************************
INSERT INTO  mom_services ('SQL TABLE')
SELECT *
FROM   OPENQUERY(SCP,'SELECT SERIAL_NO_ FROM SCADMIN.DEVICEM1 WHERE CONTAINER=''MOM''')
***************************

Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error. 
[OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:   ].

 
What I have done;
Oracle client uninstalled server restarted and oracle client installed .
Linked server deleted and recreated but nothing changed.

 
 
Any ideas ?

Osman

View Replies !   View Related
Error :Execute Trigger From Remote Server To Another Server By Linked Server
i  did "Linked server" between To Servers , and it's Working.

---------------------------

 

For Example :

Server 1 =S1.

Server = S2.

i create table in S1 : name = TblS1

i create same table in S2 : name TblS2

 

 and i create trigger(name  tr_cpD)  From S1 in TblS1  For send data  To TblS2 in S2

/****************** trigger Code ***************

CREATE TRIGGER dbo.tr_cpD

ON dbo.TblS1

AFTER INSERT

AS


BEGIN


 


SET NOCOUNT ON;


insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND

**************************************************

 

result is :

Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14

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

Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14

Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

 

 

how i can  execute this trigger

 

 

View Replies !   View Related
Problems Reading Data From Linked Server To Excel In SQL Server 2005
I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)

Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?

View Replies !   View Related
Problems Getting Data Using Linked Server
Hi

I am using SQL server 2000 with SP3 with SQL_Latin1_CP1_CS_AS as char set. I created linked servers to get data from sybase ASE (12.5.0.3).For sybase, we were using iso_1 char set and now we are changing it to cp850(in syabse).
We have some german characters in our sybase database.
After this char set change, when I try to get data from one of my views(with german characters), I dont get correct data rather I get some junk chars in place of those german chars.
If I use iso_1 for sybase, I get correct data.

In Linked server properties, I am setting remote collation to true.

Could you please help me out?

Rgds

Wilson

View Replies !   View Related
Linked Server Will Not Return Data
I have a SQL 7 server on which I have created a SQL 6.5 linked server. We are using NT
Authentication for both servers. We have been trusted by the Domain that has the 6.5
server and can open up QA and run queries against it. When I set up the linked server
I set the security to be impersonated to. When I run a query server.dbname.dbo.table
the query will not return data. It will time out. When the query runs in what context does the
server security run. Is that the problem. Has anyone seen this before.
Thanks

View Replies !   View Related
Can See But Cannot Query DB2 Data Via Linked Server
I have an ODBC DSN that lets me see the data via Access, however, in EM I set up the DB2 server as a linked server using the OLEDB provider for ODBC, with a Linked server name of DB2DB, a product name of MY DB2 connection and the data source of db2 (which is the DSN name)
The Provider string, Location and Catalog I leave blank
Under security, I assign all users to be a valid username/password

I can then see the tables in EM, but when I try to query them in QA I get this

Select * from db2db...customer

Server: Msg 7313, Level 16, State 1 Line 1
Invalid Schema or catalog specified for provider 'MSDASQL'

I am using SS7.0, and DB2 connect V7.1
I do not have/use SNA server

My actual requirement is to have a job autmatically run every day to copy certain records onto the SQL Server, but the first step is to be able to query the DB2 data

any ideas ? I think I need to enter something in the catalog box, but what ?

thanks in advance.

View Replies !   View Related
Query Data From Linked Server
I am trying to run a select statements against linked server.
I have NT server running SQL7 Enterprice and Rumba2000. I used Rumba driver to configure a DSN (ODBC based) to a AS400/DB2. Then I created a linked server. Good news are : I can see a list of tables.
Bad news : when I am trying to run a select statement either through stored procedures or directly in SQL analyzer I am receiving error message Object 'linkedservername.databasename.tablename' doesn't exist.
What am I missing here?

View Replies !   View Related
Acces Data Of A Linked Server
Hi, I'm using Sql 2000 and I have a linked server called SRVSRV.
The link works because I can list all tables.

How can I access the data i'm trying:


SELECT * FROM [SRVSRV].Database.Table

And it don't works.
What am i doing bad?
Thanks.

View Replies !   View Related
Cannot See Data From SYS.CONVERSATION_ENDPOINTS Over A Linked Server
Hello,

 

I have a server that is used to query other servers to ensure that things are functioning correctly.  On some of our servers we are running ServiceBroker and some of the monitoring involves querying the SYS.CONVERSATION_ENPOINTS view on these servers.

When I query the SYS.CONVERSATION_ENPOINTS view over a linked server it returns zero rows, though when I run the same query locally it returns data.  I initially thought it was a permission issue but it seems I can see data across the linked server to other system views within the sys schema (ie sys.all_objects).

 

Is there something different/special about SYS.CONVERSATION_ENDPOINTS that prevents me from seeing its data across linked servers or have I simply got permission problems.

 

Thanks

 

Ian

View Replies !   View Related
Getting Data Out Of Mysql Using Linked Server
Good Morning

the available oledb providers for mysql don't seem to work with sqlserver 2005.

So whats the best way these days of exposing data from within sql server from a mysql data base.

What I wanted to do was create a linked server connection to the mysql server using either oledb or odbc, but neither seem to work.

There is a .net provider for mysql, but that won't work to create a linked server connection.

any ideas appreciated.

What I want to do is write some scheduled proceedual sql from within sql server to query some tables in mysql and update some sqlserver tables.

thanks

 

David Hills

 

 

 

 

 

View Replies !   View Related
Linked Server 2000 To 2005: Error 17 Sql Server Does Not Exist Or Access Denined.
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.

View Replies !   View Related
Importing Data From Oracle 10g To Sql Server 2005 Using Linked Server
Hi,
 
I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :
 
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.

 
Please let me know.
 
Thanks
 

View Replies !   View Related
Oracle Linked Server Error
Hi,

Recently upgraded MDAC on server and now a simple query that looks up a value on a linked Oracle database doesn't work. This lookup happens as part of a transaction and what I noticed is that if I do lookup outside of the transaction it works fine. Example 1 below works fine but example 2 gives following error:

OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-01084: invalid argument in OCI call
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80004005: ].


Example 1:
DECLARE @ref_nb varchar(255)
DECLARE @ref_nb_ora varchar(255)
SELECT @ref_nb='123456789'
SELECT @ref_nb_ora=CASE WHEN ORDID IS NULL THEN ORDERID ELSE ORDID END from SERV..ER1.VW_ACCOUNT_ORD_2 where TRANS_ID =@ref_nb

Example 2:
BEGIN TRANSACTION
DECLARE @ref_nb varchar(255)
DECLARE @ref_nb_ora varchar(255)
SELECT @ref_nb='123456789'
SELECT @ref_nb_ora=CASE WHEN ORDID IS NULL THEN ORDERID ELSE ORDID END from SERV..ER1.VW_ACCOUNT_ORD_2 where TRANS_ID =@ref_nb
ROLLBACK

Any help would be appreciated.

Thanks
David

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved