June 7, 2013 at 8:15 am
Hi,
I have an access to oracle linked server called [oracleLS]
it is there under the SERVER OBJECT => LINKED SERVER => [ORACLE LS] => [CATALOG] => [DEFAUL] => [TABLES] => AND LIST OF TABLES WITH THEIR SCHEMA NAME.TABLES NAMES.
I want to find out list of tables from these tables which has specific column called 'business address';
I USED FOLLOWING ONE..BUT GIVES ME ERROR OF INVALID OBJECT.
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
ALL_TAB_COLUMNS
WHERE
COLUMN_NAME LIKE '%PATTERN%';
I AM ALSO WONDERING WHERE I HAVE TO SPECIFY THE LINKED SERVER NAME IN ABOVE QUERY!!
I would also like to try another option if you have any idea!!
Please help me.
thanks.
June 7, 2013 at 8:20 am
it's incredsibly similar; you just have to use the four part naming convention:
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
MyOracleLinkedServer...ALL_TAB_COLUMNS
WHERE
COLUMN_NAME LIKE '%PATTERN%';
if you are not a sysadmin on the linked server, you might have to use USER_TAB_COLUMNS instead.
Lowell
June 7, 2013 at 8:20 am
Hi,
select OBJECT_NAME(object_id),* from linkedserver.master.sys.columns where name like '%%'
June 7, 2013 at 8:26 am
WHEN I tried folowing query..
select OBJECT_NAME(object_id),* from [13.137.16.51].master.sys.columns where name like 'LAST%'
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "OraOLEDB.Oracle" for linked server "10.197.6.61". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
Is it something related to previllages?
need a help!
June 7, 2013 at 8:28 am
when i tried this one , it gives me different error,
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
[13.137.16.51]...USER_TAB_COLUMNS
WHERE
COLUMN_NAME LIKE 'L%';
the error is
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "13.137.16.51" does not contain the table "USER_TAB_COLUMNS". The table either does not exist or the current user does not have permissions on that table.
June 7, 2013 at 8:43 am
I am just wondering about the the tables as they are located as following structre in SQL SERVER MANAGMENT STUDIO
SERVER OBJECT => LINKED SERVER => [ORACLE LS] => [CATALOG] => [DEFAUL] => [TABLES] => AND LIST OF TABLES WITH THEIR SCHEMA NAME.TABLES NAMES
THERE IS NO MASTER.SYS. IN THIS STRUCTURE, IS THAT THE REASON FOR THE ERROR!!!
June 7, 2013 at 10:00 am
prtk.raval (6/7/2013)
I am just wondering about the the tables as they are located as following structre in SQL SERVER MANAGMENT STUDIOSERVER OBJECT => LINKED SERVER => [ORACLE LS] => [CATALOG] => [DEFAUL] => [TABLES] => AND LIST OF TABLES WITH THEIR SCHEMA NAME.TABLES NAMES
THERE IS NO MASTER.SYS. IN THIS STRUCTURE, IS THAT THE REASON FOR THE ERROR!!!
master.sys.columns would only exist on a SQL 2005 thru SQL2012 linked server; the advice does not apply at all in your case , since your linked server is Oracle based.
if your linked server is working, meaning you can query specific tables, or run EXEC sp_tables_ex [myOracleLinkedServer] and get results, you will have to get with the oracle administrator to give your ORACLE login permissions to see the user_Tab_columns view, and similar metadata views.
Lowell
June 7, 2013 at 12:34 pm
Hi finally I manage to get it done with following query.
select * from [13.137.16.51]..SYS.ALL_TAB_COLS
where owner = 'PBR'
and column_name = 'LASTWRITTEN'
June 7, 2013 at 12:44 pm
Now I am trying to get latest update or last modified timestamp, which itsef is "LASTWRITTEN" from each table.
when I tried above query it give me list of tables..which are more than 1450.
I came to know that with the following query I can achive this objective for one table.
select max(lastwritten) from [13.137.16.51]..DBO.ADDRESS
Now my issue is how to get this solution for each 1450 tables
I am getting 1450 tables from above query, because they have "LASTWRITTEN" columns.
I want to get max(lastwritten) from each of this table in one or two go , instead of writing it for 1450times.
Please Help Me.
Thanks.
June 7, 2013 at 2:32 pm
here's two ways how i might do it.
first create yourself a temp table to capture the results from the code below:
the code below is doing TWO examples,
one to build 1450 selperate linked server queries to copy and paste;
the other to build the core of the SQL for a view to create in Oracle itself.
Idea1:
CREATE Table #tmp(TableName Varchar(128),lastWritten datetime )
use the metadata to generate your 1450 commands.
run this, and copy and paste the results of Column 1 into SSMS:
SELECT
'INSERT INTO #TMP(TableName,lastWritten) SELECT '''+ TABLE_NAME +'''As Tbl, MAX(LASTWRITTEN) AS LastWritten FROM [13.137.16.51]..DBO.' + TABLE_NAME +';' AS CMD,
'SELECT '''+ TABLE_NAME +'''As Tbl, MAX(LASTWRITTEN) AS LastWritten FROM ' + TABLE_NAME + ' UNION ALL' As Cmd2
FROM [13.137.16.51]..SYS.ALL_TAB_COLS
WHERE OWNER = 'PBR'
AND COLUMN_NAME = 'LASTWRITTEN'
now you can do it like that, or you can the results of Column 2 in this query use that list of tables, to create a view on oracle that gathers that same info,
note there will be a trailing UNION ALL that you must remove in order to run the results:
it'd be a lot easier if the view gathered it on the Oracle Side, instead of 1450 Linked Server Queries., then you could have something that looked like this in oracle:
CREATE OR REPLACE VIEW VW_LASTWRITTEN
AS
SELECT 'ORDERS' As Tbl, Max(LASTWRITTEN) FROM Orders UNION ALL
--imagine releated 1448 more times for all the otehr tables
SELECT 'CUSTOMERS' As Tbl, Max(LASTWRITTEN) FROM Customers;
that makes it a zillion times easier to query from a linked Server, since it's pre-aggregated for you:
SELECT * FROM [13.137.16.51]...VW_LASTWRITTEN;
Lowell
June 7, 2013 at 3:00 pm
Hi champion, What is SSMS..where I have to copy the result of column1.?
June 7, 2013 at 3:04 pm
prtk.raval (6/7/2013)
Hi champion, What is SSMS..where I have to copy the result of column1.?
SSMS = SQL Server Management Studio, sorry about the acronym!
Lowell
June 7, 2013 at 3:20 pm
Hi Champion,
when i used your second option , I got following errorrmessage.
here studentDetails is the table name exist in oracle server.
and it is listed by your query on the top.
so when I run that creating view query , i got following error message.
when i just made comment..
it gives an error for second table name .
Msg 208, Level 16, State 1, Procedure VW_LASTWRITTEN, Line 3
Invalid object name 'studentDetails'.
June 10, 2013 at 3:05 pm
Hi,
my issue is
My table is as follows:
CREATE Table Table_Update(TableName Varchar(128) primary key,StartlastWritten datetime, EndlastWritten datetime)
When I run following query :
SELECT 'UPDATE Table_Update SET EndlastWritten= (SELECT MAX(LASTWRITTEN)
FROM [13.137.16.51]..PBR.' + TABLE_NAME +' WHERE TABLENAME='+TABLE_NAME+');'
FROM [13.137.16.51]..SYS.ALL_TAB_COLS
WHERE OWNER = 'PBR' AND COLUMN_NAME = 'LASTWRITTEN'
I am getting update statement for different tables as follows :
UPDATE Table_Update SET EndlastWritten= (SELECT MAX(LASTWRITTEN) FROM [13.137.16.51]..PBR.STUDNET WHERE TABLENAME=STUDENT);
But It gives me error due to invalid object, beacuse of inverted comma missing near the STUDENT in above example..
Actually I want to get like following query.
UPDATE Table_Update SET EndlastWritten= (SELECT MAX(LASTWRITTEN) FROM [13.137.16.51]..PBR.STUDNET WHERE TABLENAME='STUDENT');
I tried so many different ways..but coudn't workout..
Please help me.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply