OraFAQ Forum: Networking and Gateways » Not seeing All Columns and Values of SQL Server Table

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Not seeing All Columns and Values of SQL Server Table [message #661979]

Mon, 10 April 2017 23:35

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

Report message to a moderator

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

Michel Cadot
Messages: 68739
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

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

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]

Attachment: Screenshot-Isql-sqlplus.png
(Size: 22.12KB, Downloaded 5344 times)

[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

Michel Cadot
Messages: 68739
Registered: March 2007
Location: Saint-Maur, France, https...

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:

Code: [Select all] [Show/ hide]

help Type

and

Code: [Select all] [Show/ hide]

desc "Type"@<yourlink>

Report message to a moderator

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

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)

Report message to a moderator

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

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>

Report message to a moderator

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

Littlefoot
Messages: 21823
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 ...

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68739
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

ashraf_arkesh
Messages: 9
Registered: October 2006
Location: Dhaka ,Bangladesh

Junior Member

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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

Michel Cadot
Messages: 68739
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Mar 08 17:43:29 CST 2025