I'm trying to update something in AS400 from sql server 2000 through openquery like following. UPDATE OPENQUERY(odbcname, 'SELECT * FROM "libname"."filename" WITH NC') SET NBFLAG01=1 WHERE NBFLAG01 <> 1 Here's the error I'm getting. "OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: Insufficient base ...
I'm trying to update something in postgresql from sql server 2000 through openquery like following. UPDATE OPENQUERY(odbcname, 'SELECT name FROM person where id=1') SET name='bla' Here's the error I'm getting. "OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: the content was changed before updating] OLE ...
I have a SQL Server 2005 database that is linked to an Oracle database. What I want to do is run a query to pull some ID numbers out of it, then find out which ones are in Oracle. So I want to take the results of this query: SELECT pidm FROM sql_server_table And do something like this to query the Oracle database (assuming that the ...
I have a sql server pass-thru query like this: *select * into myTable from openquery (yourComputer, 'select x,y,z, from yourTable')* The problem is the columns in myTable are defaulting to not null and I will later want to add rows to this table with some columns null. Can I get round this? ...
Scenario: I am using a T-SQL stored proc (Sql Server Management Studio) to return search matches for text documents using the MS Indexing Service and this (simplified) query: SELECT * FROM openquery( filesystem2, 'SELECT Path, Rank, Filename FROM SCOPE('' "e:\test\documents" '') WHERE CONTAINS('' FORMSOF ...
I just recently moved a SQL DB from SQL2005 32 bit --> 64 bit. I am having an issue connecting to Oracle using the OraOLEDB.Oracle Provider. I was able to install Oracle 10G Client , ODAC 64 bit. I was also able to add a linked server to the Oracle instance. I am able to run a query using the linked server name directly: SELECT top 1...
Hello, Does open query exist in sybase ? Or more generally, in sybase, what are the possible way to select among the result of a procedure (temporary tables, out params, others ??) ...
I am well aware that MS SQL Server does not allow variables to be used in the OPENQUERY statement and I'm aware of the workarounds. What I'd like to know is, does anyone know WHY variables or concatenated strings are not allowed? The hoops this causes people to jump through is simply astounding and it just isn't in line with other func...
I'm writing a script that is supposed to run around a bunch of servers and select a bunch of data out of them, including the local server. The SQL needed to SELECT the data I need is pretty complicated, so I'm writing sort of an ad-hoc view, and using an OPENQUERY statement to get the data, so ultimately I end up looping over a statement...
Hello, I have a table called Table with columns: ID (int, primary key, clustered, unique index) TEXT (varchar 15) on a MSSQL linked server called LS. Linked server is on the same server computer. And: When I call: SELECT ID, TEXT FROM OPENQUERY(LS, 'SELECT ID, TEXT FROM Table') It takes 400 ms. When I call: SELECT ID, TEXT F...
I am using OPENQUERY to do an insert from MSSQL 2005 to MySQL. How do I get at the last inserted id on the MySQL db back into my MSSQL procedure? ...
I have a number of stored procedures structured similarly to this: DECLARE @sql NVARCHAR(MAX) DECLARE @mdx NVARCHAR(MAX) CREATE table #result ( [col1] NVARCHAR(50), [col2] INT, [col3] INT ) SET @mdx = '{some dynamic MDX}' SET @sql = 'SELECT a.* FROM OpenQuery(LinkedAnalysisServer, ''' + @mdx + ''') AS a' INSERT INTO #result EXE...
Hello everyone, I am using SQL Server 2008 (let us call this source database server in this question discussion), and in SSMS, I have created a linked server to another SQL Server 2008 database (let us call this destination database server in this question discussion). When I issue statement -- select * from [linked server name]....
June 29, 2010 - I had an un-committed action from a previous delete statement. I committed the action and I got another error about conflicting primary id's. I can fix that. So morale of the story, commit your actions. Original Question - I'm trying to run this query: with spd_data as ( select * from openquery(IRPROD,'select * from ...
Hi, I do have an oracle 8 database from which I want to fetch data to SQL Server 2005. The following statement works fine, if the table on SQL Server 2005 is empty. If I run with let's say one entry missing, it does not work. Please let me know, if any additional information would be usefull! SELECT wdmsoracle.NO FROM (Select * from OP...
How can I use a parameter inside sql openquery, such as: SELECT * FROM OPENQUERY([NameOfLinkedSERVER], 'SELECT * FROM TABLENAME where field1=@someParameter') T1 INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK ...
I want to execute the following statement through from a linked server (openquery): UPDATE SAP_PLANT SET (OWNER, OWNER_COUNTRY) = (SELECT import.AFNAME, import.COUNTRY FROM SAP_IMPORT_CUSTOMERS import, SAP_PLANT plant WHERE plant.SAP_FL = import.SAP_NO ...
I have a job that I'd like to run in SQLServer 2008 that needs to get some data from our SQLServer 2000 db so I'm using OPENQUERY to get that data. When I execute the job I get the following error: Executed as user: SEDONAGROUP\sqlage. Login failed for user 'SEDONAGROUP\sqlage'. [SQLSTATE 28000] (Error 18456). The step failed. The St...
This can be a debatable answer, but I'm looking for the case where a local Excel file needs to be exported to a local SQL Server 2008' table. Has anyone ever had the chance to check execution time to compare OpenRowSet/OpenQuery/OpenDataSource for a very large file import in SQL Server 2008? I'm able to use any of the 3 options, and th...
This happens when I query a text field using linked server. For example: select * from openquery(LS,'select text_field from table') then my server blows up! HELP? ...