Linked Server Query With Quotation Marks
Jul 6, 2007
Hi,
My first post. I am trying to run a select query to a linked server (Cache database on VMS OS) from Microsoft SQL 2000 Query Analyzer.
Here is my problem query:
SELECT *
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM Member_Acct WHERE close_dt > {d'2007-07-01'}'
I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.
I have tried using double quotes and everythingelse I could. Database expect me to send date in this format {d'YYYY-MM-DD'}.
Please help me.
View 12 Replies
ADVERTISEMENT
Oct 31, 2007
Hi There,
I'm having a little trouble with a large query I have written to be sent as an email.
I want to send it as a csv so I've added commas in between each value in the query.
This is causing an error though, I think the ' that surround the commas are closing the query early. Is there any way around this? like encapsulating the query?
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ggsgn'
,@recipients = 'sgsdm'
,@subject = 'Nsgdday'
,@attach_query_result_as_file = 1
,@body_format = 'text'
,@query ='
use sdgsgdgs
select
a1.c#,',', client,',', fr,',', tpe,',', dateReq as DateRequested,',', ... '
View 1 Replies
View Related
May 3, 2006
I have an ASP.Net page that allows people to type in strings and store them into a SQL Server DB; which in turn gets displayed on a website. The project has an admin side that can add/delete/edit announcements, which get displayed on an intranet site. These announcements can be clicked on to display further detail. When announcements are clicked on a javascript popup window is generated that displays the strings. All data is stored in a SQL Server DB.What I need to know is: how do I check to see if a string has a quotation mark or apostrophe in it so that I can replace it with the appropriate HTML code? (Though it seems I can't display an apostrophe, even when using the HTML code ''')If I store the string as was entered by the administrator (with quotation marks instead of '"'), the popup window will not display.
View 7 Replies
View Related
Nov 25, 2007
I have a block of xml that I wish to update in my sql database. The problem I have is the the data has double and single quotation marks in it and all my attempts to send this to my sql database gives me errors reguarding the quotation marks. Is there a way I can send the xml to the database without these problems. I am using the xml in the sql database to make it easy to read and right the xml as xmldatasource does not allow reading and writing easily.
this section shows the problem, i am loading xml from a file and trying to insert this into the sql database
XmlTextReader reader = new XmlTextReader(Server.MapPath("xml/wt.xml"));reader.WhitespaceHandling = WhitespaceHandling.None;XmlDocument xmlDocF = new XmlDocument();xmlDocF.Load(reader);this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] '" + xmlDocF.InnerXml+ "' where [index] = 1" ;this.SqlDataSource1.Update();reader.Close();
pls help
View 6 Replies
View Related
Jan 18, 2006
I have problem with sql query with aspnet.
In my web page, i have a text box for input name or string to search in database. I have problem if the search string consist of quotation mark => ' .
Any suggestions? Thanks.
View 5 Replies
View Related
Oct 9, 2007
Hi Everybody
I've one table named Student. Here is data
Name
Subject
Mraks
Prasad
English
80
Tushar
English
79
Sunil
English
78
Prasad
Geometry
80
Tushar
Geometry
81
Sunil
Geometry
79
Prasad
History
82
Tushar
History
81
Sunil
History
80
Now I want to write a query that displays student with subjects and marks who secured highest marks in each subject.
I want to output like this
Name
Subject
Mraks
Prasad
English
80
Tushar
Geometry
81
Prasad
History
82
So will anybody help me to write a sql query that acheive the same output
View 5 Replies
View Related
May 8, 2001
Greetings all,
I have successfully been able to use a SELECT query to see tables on a remote (Linked) server. Now, I want to be able to join those records with a WHERE clause to the same tables I have on my local database. The idea here is to be able to see current data on my local database(which is not current data), which resides in the remote database(which is current data). Here is what I have so far:
SELECT top 10 * from LinkServer.MC_Card.webuser.POS_TX
But, I'm not sure how to SELECT from two tables. Would I do a SELECT from the local database WHERE all records = records on the remote database?
Not sure how to do this. Is this what would be considered a Distributed Query? And how would I make this work with joins like the existing joins I have to the tables in my local database?
This is new territory for me. Sorry if this is such a newbie question.
Thanks,
Bruce
View 1 Replies
View Related
Nov 7, 2006
I'm using MS SQL Server Express and I've added a linked server. (I created the linked server by right mouse clicking on Server Objects, selecting SQL and entering the SQL2 as the name.) Now I'm trying to query a linked table. The following query works.
SELECT * FROM SQL2.PA.dbo.Counties
This one doesn't:
Select * from OPENQUERY(SQL2,"SELECT * FROM SQL2.PA.dbo.Counties")
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT * FROM SQL2.PA.dbo.Counties'.
Can you tell me why?
View 2 Replies
View Related
Apr 19, 2004
I have two tables in a linked server that I am trying to accomplish the following:
Server is called Medic, schema is informix, catalog is v001
table names are findet and findhdr
1) join indices (patkey and invno)
2) distinct count (finhdr.patid)
3) sum currency(findet.amount)
4) within a date range(findet.ar_date)
5) having specific criteria (findet.proccode = "99201 thru 99205")
Any help is greatly appreciated!
JT
View 2 Replies
View Related
Jan 25, 2008
Hi guys I need some help connecting to a LINKED SERVER. I have setup the LINKED SERVER via Enterprise Manager. This is what I did. I know this is a difficult thing to explain so I will try my best and attempt to give you a good understanding of the scenario.
1. I have three servers named DEVUK, DEVUS and DEVASIA.
2. DEVUK I can connect to via my computer and contains a databases called GOD.
3. I went into DEVASIA server and went to Administrative Tools > New ODBC Connection and called it DEVASIAUK.
4. Setup everything in there and tested the connection which completed successfully.
5. I am now on my computer where I have connected to DEVASIA and gone into Server Objects > Linked Servers.
6. I created a new LINKED SERVER and under GENERAL TAB have stated DEVASIAUK and Provider as SQL Server.
7. Security I have used NT_AUTHORITYand option BE MADE USING THE LOGINS CURRENT SECURITY CONTEXT.
I have the SQL Command: SELECT * FROM DEVASIAUK ... tblLocalTable
Am I doing this right? Or am I missing something here? I am new to Linked Servers but it shows it is executing a query but then it says Login Failed.
Appreciate any help, thanks, Onam
View 2 Replies
View Related
Mar 29, 2001
Is there a way to set four part reference used in distributed query to a short
name in local server, so for each query you do not have to type the LONG four
part ref. repeatedly to linked server objects ?
Thanks a lot.
-Steve
View 1 Replies
View Related
Apr 20, 2001
I need to query a linked server (which is Oracle) with some parameters. When I try to use a four part SQL statement, it does not work. But when I try to use OPEENQUERY statement, it works just fine. Problem comes when I need to send some parameters with the OPENQUERY'S 'query' part.
For example, the following statement works just fine:
SELECT *
FROM OPENQUERY(OracleLinked, "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '2000.01.01' AND '2000.01.31'")
But If I try to use:
DECLARE @DynamicSQL VARCHAR(1000),
@StartDate VARCHAR(10),
@EndDate VARCHAR(10)
SET @StartDate = '2000.01.01'
SET @EndDate = '2000.01.31'
SELECT @DynamicSQL = "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '" + @StartDate + "' AND '" + @EndDate + "'"
--SELECT @DynamicSQL
SELECT *
FROM OPENQUERY(OracleLinked, @DynamicSQL)
it does not work.
Well, I did some research and found out that OPENQUERY does not accept variables for its arguments. See the link below:(http://msdn.microsoft.com/library/psdk/sql/ts_oa-oz_5xix.htm)
Then is there any way I can accomplish what I want to on the Remote server?
Thanks in advance for your help.
View 1 Replies
View Related
Apr 25, 2001
I have a delete query on a linked server that never ends. I can do a select where ID = x and it returns lickity split but when I do a delete where id = x it never comes back? Any Clues???
View 1 Replies
View Related
May 14, 2001
Hi,
I am now facing a problem related to the linked server. I created the connection between server A and B as linked servers. When I execute the following SQL statement on server A,
select * from B.database1.dbo.tableA where id ='12345'
I can get the results within couple of seconds. But the similar query would take several minutes if I switch the server name in the query from B to A and
run it on server B! The tables on server A and B actually have the same sizes and the same indexes.
Do you have any clues and suggestions on this issue?
Thanks in advance.
Keith
View 1 Replies
View Related
May 23, 2001
I have created a linked server using an ODBC connection to an Access database. The command I used for this was:
EXEC sp_addlinkedserver
@server = 'Testaccess',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'test'
GO
where 'Testaccess' is the name of the linked server and 'test' is the name of the ODBC connection. [The ODBC connection stores the name of the Access datbase.]
I can run sp_tables_ex and view the table_schem, table_cat etc etc
BUT when I try to run a query on the linked server, I get the following message:
"7312 - Invalid use of schema and/or catalog for OLE DB provider '%ls'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema."
I am attempting/using the following command specifying the linked server name.database name.owner.table name in the FROM clause:
select a.*
from testaccess.testdb.dbo.temptable a
Any ideas what I am doing wrong??
thanks
Jan
View 1 Replies
View Related
Jun 13, 2001
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 1 Replies
View Related
Jul 26, 2001
We want to read data from a DB2 view so we set up a linked server in SQL7 (ODBC via Neon's Shadow Direct) but when we issue the following type of query from Query analyzer:
select * from openrowset(my_db2link,'select * from test.myview where mycol = ''value''')
it just hangs, and we can't kill the process (the Enterprise manager 'KIll Process' button has no effect!)
- the only way we can get rid of these queries seems to be to stop/restart SQL Server
When we look at current activity in Enterprise manager the process seems to be waiting for a resource (either MISCELLANEOUS or PAGEIOLATCH)
The query works OK if I run it on the server using Shadow direct, so the error must be between SQL Server and Shadow direct
Has anyone seen this error before?
Thanks in advance, John
View 1 Replies
View Related
Oct 18, 2000
Three weeks ago we began a project that involved importing data from an AIX DB2 6 environment via a linked server configuration. Following the data import a second query was executed against the db2 environment using data that resides in the new table within SQL 7 in the join statement (a very basic example is provided below) This was all accomplished in sequence via a package.
SELECT F_NAME, L_NAME, PASSWD
FROM SQL7.LOCALSRV.dbo.NEWUSERTBL as new, DB2.SYSIBM.MASTERTBL.OLDUESRTBL as old
WHERE new.USER_ID = old.USER_ID
Originally we had no problems and while the execute time was not exactly speedy it was tolerable as we would revisit optimization after we established if what we were trying to accomplish was feasible. At the outset the first data import to build the local table was immeadeate and then the distributed query to retirieve more info to build another local table against the DB2 server took aproxiamtly 1 minute per user row returned. Currently we are looking at still having an immeadeate data import (a matter of seconds to build the first table)but now we are looking at more than 1 hour returned for 1 correesponding row of data off of DB2. We are utilizing the the IBM DB2 ODBC DRIVER.
Any input or suggestions as to what could be causing this or perhaps a more efficeint way to code the statement would be much appreciated. Thanks in advance.
Adrian
View 1 Replies
View Related
Oct 12, 1999
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 2 Replies
View Related
Feb 10, 2003
SQL 2000
I have a process that calls several stored procs which access a database on a linked server.
code that fails:
SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPO bp ON
em.er_bpoid = bp.bpo_id
Error message:
Server: Msg 913, Level 16, State 8, Line 1 Could not find database ID 6. Database may not be activated yet or may be in transition.
The database is accessible from query analyzer with a simple select from the linked server. Also if I change any letter in the ues.dbo.Employer em or ues.dbo.BPO bp part to a different case it works fine.
For example: -changed the BPO to BPo- this works!
SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPo bp ON
em.er_bpoid = bp.bpo_id
Please help I can't figure this one out.
Thanks.
View 1 Replies
View Related
Jan 14, 2004
We are using linked servers, additionally, some of our tables are being merge replicated with identity ranges.
When I run a SELECT statement on a non-merged table looking for a constant value, I get the proper return.
When I run a SELECT statement on a merge-repl. table looking for a constant value like "5", it returns 0 records, execution plan says it's using a "Constant Internal table" - however, when I run the same select statement and change the where clause to ">4" it returns the row with "5" and whatever other rows match the criterion. What in the world is going on?
Thanks for your help.
View 5 Replies
View Related
Jul 8, 2013
I run a query using an Oracle Linked Server.
This seems to run forever.
SELECT * FROM LinkedServer..Schema.View WHERE TN= '2034561295'
...and any other field in the where clause work fine.
SELECT * FROM LinkedServer..Schema.View WHERE SPID= '8088'
View 3 Replies
View Related
Feb 13, 2008
I am working on a linked server where a few of the queries use almost exclusively remote tables from 1 other server. I have read somewhere that there are options to specify where a query is performed but can't find it anywhere. Could someone tell me the command and how to use it or point me to something to read about it.
Thanks
View 1 Replies
View Related
Jul 20, 2005
Hi,I'm getting errors when trying to run a query over linked servers thatworks fine on a single server. The error text that comes up in QA isas follows;Server: Msg 8180, Level 16, State 1, Line 1Statement(s) could not be prepared.Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'Qry1208'.Server: Msg 125, Level 15, State 1, Line 1Case expressions may only be nested to level 10.Both servers are using same version SQL 2k, SP3 and (as far as I cansee) the level of nesting goes down 3 maybe 4 deep at the max?Any help on this would be greatly appreciated.Rob.
View 1 Replies
View Related
Dec 8, 2006
Hello,
I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;
-It has one worksheet in it named 'Add Revenue Accts'.
-The name of the workbook is 'Revenue_to_All_Accounts.xls'
-Its location is \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_Accounts
I have the linked server configured as follows;
-Linked Server; REVENUE_TO_ALL_ACCOUNTS
-Provider; Microsoft Jet 4.0 OLE DB Provider
-Data Source; \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls
-Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\Cdnbwfin1DataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls;Persist Security Info=False
When I attempt the following query;
SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')
The following message appears, and no results are returned;
[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, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?
Thank you for your help!
cdun2
View 1 Replies
View Related
Aug 28, 2015
Is it possible to pull name & email from Active Directory using SSMS w/o a linked server connection?
And this would be using SQL 2008
View 7 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
Apr 16, 2015
Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.
Here is my linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null
Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.
SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')
OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType".
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".
View 0 Replies
View Related
Jan 16, 2008
Hello
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
View 5 Replies
View Related
May 27, 2008
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 1 Replies
View Related
Aug 2, 2006
i have set up
a linked server. i can query the linked server in query analyzer and
also do update/delete. but when i try to run the same query for linked
server through insert trigger, i get following error: [OLE/DB provider returned message. [Microsoft][ODBC Sql Server Driver]Distributed transaction error].btw, i am using Sql server 2000, SP4. main server is windows 2003 server and linked server is windows xp pro.any suggestions will be appreciated.
View 1 Replies
View Related
May 28, 2004
I want to create a view in SQL populated with users from our Active Directory. I have learnt that this can be done using linked server. I have tried using the following:
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
go
sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'false', @locallogin = 'sa', @rmtuser = 'lok_applications', @rmtpassword = '9dfFfG374GoiAo6yxxc8oZ'
SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=net"')
I keep getting this error no matter what I try:
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].
Any ideas why??
View 5 Replies
View Related
Jun 9, 2006
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?
When I tried to import system.data.oracleclient, it was not available. Do I need to install anything for being able to use it ?
Thanks a lot.
View 4 Replies
View Related
