Home » RDBMS Server » Networking and Gateways » Not seeing All Columns and Values of SQL Server Table (Oracle 11gR2, dg4ODBC 64 Bit, MsSQL Server 2008 32 Bit)
Not seeing All Columns and Values of SQL Server Table [message #661979] Mon, 10 April 2017 23:35 Go to next message
ashraf_arkesh
Messages: 9
Registered: October 2006
Location: Dhaka ,Bangladesh
Junior Member
i am trying to fetch data from SQL Server 2008(32 Bit) on to Oracle 11gR2(RHEL 6.8 64 Bit).
i could connect.
i could see all columns and all column values in the isql.
but when i am querying from Oracle Database Link its returning abnormal columns/values
please help
Re: Not seeing All Columns and Values of SQL Server Table [message #661982 is a reply to message #661979] Tue, 11 April 2017 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't show us what you have and get we can't help you.

Re: Not seeing All Columns and Values of SQL Server Table [message #661983 is a reply to message #661982] Tue, 11 April 2017 01:28 Go to previous messageGo to next message
ashraf_arkesh
Messages: 9
Registered: October 2006
Location: Dhaka ,Bangladesh
Junior Member
i am attaching a screen shot

/forum/fa/13494/0/

[mod-edit: imaged inserted into messaged body by bb]

[Updated on: Tue, 11 April 2017 16:15] by Moderator

Report message to a moderator

Re: Not seeing All Columns and Values of SQL Server Table [message #661984 is a reply to message #661983] Tue, 11 April 2017 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do not post an image for text, copy and paste the text inside the post.
Before, Please read How to use [code] tags and make your code easier to read.

Post the result of:
help Type
and
desc "Type"@<yourlink>
Re: Not seeing All Columns and Values of SQL Server Table [message #661987 is a reply to message #661984] Tue, 11 April 2017 02:28 Go to previous messageGo to next message
ashraf_arkesh
Messages: 9
Registered: October 2006
Location: Dhaka ,Bangladesh
Junior Member
Sir..

+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> help Type
+---------------------------------------------------------------------------------------------------------------------------------+-- -------------------------------------------------------------------------------------------------------------------------------+----- ----------------------------------------------------------------------------------------------------------------------------+-------- -------------------------------------------------------------------------------------------------------------------------+----------+ ---------------------------------------------------------------------------------------------------------------------------------+--- ---------+--------------+---------------+-------+---------+-------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------+------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+--------------+-----------------+--------------- ---+-----------------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- -----------+-------------+-----------------+---------------+---------------+--------------------------------------------------------- ------------------------------------------------------------------------+------------------------------------------------------------ ---------------------------------------------------------------------+--------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------+--------------------------- ------------------------------------------------------------------------------------------------------+------------------------------ ---------------------------------------------------------------------------------------------------+--------------------------------- ------------------------------------------------------------------------------------------------+-------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE| TYPE_NAME | COLUMN_SIZE| BUFFER_LENGTH| DECIMAL_DIGITS| RADIX | NULLABLE| REMARKS | COLUMN_DEF | SQL_DATA_TYPE| SQL_DATETIME_SUB| CHAR_OCTET_LENGTH| ORDINAL_POSITION| IS_NULLABLE | SS_IS_SPARSE| SS_IS_COLUMN_SET| SS_IS_COMPUTED| SS_IS_IDENTITY| SS_UDT_CATALOG_NAME | SS_UDT_SCHEMA_NAME | SS_UDT_ASSEMBLY_TYPE_NAME | SS_XML_SCHEMACOLLECTION_CATALOG_NAME | SS_XML_SCHEMACOLLECTION_SCHEMA_NAME | SS_XML_SCHEMACOLLECTION_NAME | SS_DATA_TYPE|
+---------------------------------------------------------------------------------------------------------------------------------+-- -------------------------------------------------------------------------------------------------------------------------------+----- ----------------------------------------------------------------------------------------------------------------------------+-------- -------------------------------------------------------------------------------------------------------------------------+----------+ ---------------------------------------------------------------------------------------------------------------------------------+--- ---------+--------------+---------------+-------+---------+-------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------+------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+--------------+-----------------+--------------- ---+-----------------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- -----------+-------------+-----------------+---------------+---------------+--------------------------------------------------------- ------------------------------------------------------------------------+------------------------------------------------------------ ---------------------------------------------------------------------+--------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------+--------------------------- ------------------------------------------------------------------------------------------------------+------------------------------ ---------------------------------------------------------------------------------------------------+--------------------------------- ------------------------------------------------------------------------------------------------+-------------+
| Transaction_Profile | dbo | TYPE | SERIAL_ID | 4 | int identity | 10 | 4 | 0 | 10 | 0 | | | 4 | | | 1 | NO | 0 | 0 | 0 | 1 | | | | | | | 56 |
| Transaction_Profile | dbo | TYPE | TYPE | 12 | varchar | 50 | 50 | | | 0 | | | 12 | | 50 | 2 | NO | 0 | 0 | 0 | 0 | | | | | | | 39 |
+---------------------------------------------------------------------------------------------------------------------------------+-- -------------------------------------------------------------------------------------------------------------------------------+----- ----------------------------------------------------------------------------------------------------------------------------+-------- -------------------------------------------------------------------------------------------------------------------------+----------+ ---------------------------------------------------------------------------------------------------------------------------------+--- ---------+--------------+---------------+-------+---------+-------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------+------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+--------------+-----------------+--------------- ---+-----------------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- -----------+-------------+-----------------+---------------+---------------+--------------------------------------------------------- ------------------------------------------------------------------------+------------------------------------------------------------ ---------------------------------------------------------------------+--------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------+--------------------------- ------------------------------------------------------------------------------------------------------+------------------------------ ---------------------------------------------------------------------------------------------------+--------------------------------- ------------------------------------------------------------------------------------------------+-------------+
SQLRowCount returns -1
2 rows fetched
SQL> quit



SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 11 13:25:16 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc "Type"@MsSqlTPLink
Name Null? Type
----------------------------------------- -------- ----------------------------
TYPE NOT NULL VARCHAR2(50 CHAR)




Re: Not seeing All Columns and Values of SQL Server Table [message #661988 is a reply to message #661987] Tue, 11 April 2017 02:43 Go to previous messageGo to next message
ashraf_arkesh
Messages: 9
Registered: October 2006
Location: Dhaka ,Bangladesh
Junior Member
SQL> select * from type;
+------------+---------------------------------------------------+
| SERIAL_ID | TYPE |
+------------+---------------------------------------------------+
| 1 | CASH |
| 2 | NON CASH |
+------------+---------------------------------------------------+
SQLRowCount returns -1
2 rows fetched
SQL> Select * from DEPT_INFO;
+---------------------------------------+-----------+---------------------------------------------------+--------------+------------- -----------+-----------+------------+---------------------------------------------------+---------------+------------------------+--- ------------------+
| serialNo | deptID | deptName | ENTRY_USER_ID| ENTRY_DATE_TIME | ENTRY_MODE| AUTHOR_MODE| AUTHOR_PROCESS | AUTHOR_USER_ID| AUTHOR_DATE_TIME | FORM_ID |
+---------------------------------------+-----------+---------------------------------------------------+--------------+------------- -----------+-----------+------------+---------------------------------------------------+---------------+------------------------+--- ------------------+
| 1 | HR | Human Resource | | | | | | | | |
| 2 | IT | Inforamtion Technology | | | | | | | | |
| 3 | PD | Production | | | | | | | | |
+---------------------------------------+-----------+---------------------------------------------------+--------------+------------- -----------+-----------+------------+---------------------------------------------------+---------------+------------------------+--- ------------------+
SQLRowCount returns -1
3 rows fetched
SQL>quit



SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 11 13:39:09 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from type@MsSqlTPLink;

TYPE
--------------------------------------------------
CASH


SQL> select * from DEPT_INFO@MsSqlTPLink;

FORM_ID
----------------------------------------




SQL>

Re: Not seeing All Columns and Values of SQL Server Table [message #661990 is a reply to message #661988] Tue, 11 April 2017 03:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is database link correctly created? Are you sure it points to the correct MS SQL Server database? I mean, maybe those SELECT statements return data from different tables ...
Re: Not seeing All Columns and Values of SQL Server Table [message #661992 is a reply to message #661990] Tue, 11 April 2017 03:16 Go to previous messageGo to next message
ashraf_arkesh
Messages: 9
Registered: October 2006
Location: Dhaka ,Bangladesh
Junior Member
i have checked that, too. that Sql Server does not have other Database containing such tables for sure.
Re: Not seeing All Columns and Values of SQL Server Table [message #661994 is a reply to message #661992] Tue, 11 April 2017 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ There inconsistencies in your posts type <> "Type"
2/ Quote:
SQLRowCount returns -1
2 rows fetched
Use SQL*Plus to query Oracle.
Other tools can be just suspect.

Re: Not seeing All Columns and Values of SQL Server Table [message #661995 is a reply to message #661994] Tue, 11 April 2017 05:55 Go to previous messageGo to next message
ashraf_arkesh
Messages: 9
Registered: October 2006
Location: Dhaka ,Bangladesh
Junior Member
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from type;
+------------+---------------------------------------------------+
| SERIAL_ID  | TYPE                                              |
+------------+---------------------------------------------------+
| 1          | CASH                                              |
| 2          | NON CASH                                          |
+------------+---------------------------------------------------+
SQLRowCount returns -1
2 rows fetched
SQL> quit

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 11 16:53:03 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from type@MsSqlTPLink;

TYPE
--------------------------------------------------
CASH


SQL>

[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Tue, 11 April 2017 16:18] by Moderator

Report message to a moderator

Re: Not seeing All Columns and Values of SQL Server Table [message #661996 is a reply to message #661995] Tue, 11 April 2017 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Tue, 11 April 2017 08:32

...
Before, Please read How to use [code] tags and make your code easier to read.
...
Re: Not seeing All Columns and Values of SQL Server Table [message #662057 is a reply to message #661996] Sat, 15 April 2017 23:21 Go to previous message
ashraf_arkesh
Messages: 9
Registered: October 2006
Location: Dhaka ,Bangladesh
Junior Member
Sir,

still, whatever i replied are understandable.. aren't they..

how should i find the problem then..
Any suggestion will be highly appreciated..
Previous Topic: DG4ODBC taking a long time to fetch from HANA database over dblink with a where clause
Next Topic: Querying over DB link from 11gr2 to SQL Server 2008
Goto Forum:
  


Current Time: Thu Mar 28 11:44:05 CDT 2024