1 2 Previous Next 24 Replies Latest reply: Apr 12, 2012 9:45 AM by Mkirtley-Oracle RSS

    "Protocol Violation" message in SQL Developer Worksheet when executingquery

    user336659
      I have cofigured Hetrogeneous connectivity from Oracle to Sql Server Database using DG4ODBC and Sql Server ODBC driver for linux. I am able to query data from Sql server through Sqlplus but when I am trying to do the same using Sql Developer I am getting "Protocol violation" error.

      :~ $ isql mstest username password
      ---------------------------------------
      | Connected! |
      | |
      | sql-statement |
      | help [tablename] |
      | quit |
      | |
      ---------------------------------------
      SQL>

      Does anyone got similar issue?

      Any help is much appreciated!!

      Thanks,
      Steve.
        • 1. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
          user336659
          I have already tried below workaround, it's not working for me

          SQL Developer does pick up the JDBC drivers from your local ORACLE_HOME.
          To ensure your using the shipped JDBC drivers you could use the following small script.
          I would appreciate if you could tell me if this works and what version of Oracle you have installed locally.

          1) Close SQL Developer
          2) Create a sqldeveloper.cmd file in the SQL Developer root directory
          3) With the following contents
          SET ORACLE_HOME=%CD%
          start sqldeveloper.exe
          4) Run sqldeveloper.cmd
          • 2. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
            Kgronau-Oracle
            What's your SQL Developer release, what is the source table description (MS SQL Server table) and what DG4ODBC release (including ODBC driver version and vendor) are you using?
            • 3. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
              Mkirtley-Oracle
              Hi,
              As well as the version information could you let us know where you are running SQL*Developer ?
              Is it on Windows and are you then connecting to the Oracle RDBMS running on linux ?
              I am able to select when running SQL*Developer on Windows connecting to a RDBMS on WIndows that is using a database link with the Windows ODBC SQL*Server driver.

              Regards,
              Mike
              • 4. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                Mkirtley-Oracle
                Steve,
                The following worked for me -

                - SQL*Developer 3.1 on Windows
                - connect to a 11.2.0.3 RDBMS on Linux
                - from SQL*Worksheet run a select such as -
                select * from employee@MSODBCSQLSERVER
                - returns results.
                - DG4ODBC 11.2.0.3
                - using SQL*Server driver libsqlncli-11.0.so.1720.0

                Regards,
                Mike
                • 5. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                  user336659
                  Thanks Mkirtley, Kgronau for your reponses. You both were always helpfull.

                  Below is what I am using

                  SQLDeveloper:

                  Version: 3.0.04

                  Was installed on windows and connecting to database(11.2.0.2) on linux

                  DB4ODBC - I beleive it will be same version as Database. so it's 11.2.0.2

                  ODBC Driver - Microsoft® SQL Server® ODBC Driver for Linux (version 1.0) - http://www.microsoft.com/download/en/details.aspx?id=28160
                  sqlncli-11.0.1720.0.tar.gz

                  Thanks,
                  Steve
                  • 6. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                    Kgronau-Oracle
                    Is there any specific table/data you're trying to select or does it happen with all tables?
                    Could you post a create table statement with some demo data that reproduces the issue?

                    And also please post the odbc.ini file, I'll then try to reproduce.
                    • 7. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                      Mkirtley-Oracle
                      Hi,
                      I've just tried using SQL*Developer 3.0.04 and still cannot reproduce.

                      The following select run from SQL Worksheet returns results when connect to 11.2.0.3 on Linux x86-64 -

                      select * from employee@MSODBCSQLSERVER_DG4ODBC_EMGTW_1123_DB


                      The db link MSODBCSQLSERVER_DG4ODBC_EMGTW_1123_DB is on the Linux machine.
                      What is the query you are running and do all queries give the error ?

                      Regards,
                      Mike
                      • 8. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                        user336659
                        when I execute I am getting resultes
                        select owner,table_name from all_catalog@ora2ms;

                        but when try below statement to select data from table then first time it throws "Java heap error" and from then on "Protocol violation"
                        select * from "ldata_ms"@ora2ms;

                        below is the create statement in sqlserver:

                        CREATE TABLE [dbo].[data_ms](
                             [data_id] [bigint] IDENTITY(1,1) NOT NULL,
                             [data_desc] [nvarchar](2048) COLLATE SQL_Latin1_General_CP850_BIN2 NULL,
                             [data_create_usrid] [nvarchar](128) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,
                             [data_create_dt] [datetime] NOT NULL,
                             [data_modify_usrid] [nvarchar](128) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,
                             [data_modify_dt] [datetime] NOT NULL,
                             [data_rowguid] [uniqueidentifier] NOT NULL,
                        CONSTRAINT [PK_data_ms_data_id] PRIMARY KEY CLUSTERED
                        (
                             [data_id] ASC
                        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                        ) ON [PRIMARY]


                        And this table contains chinese, japanese data also..

                        1     개정과 무     test     2012-01-11 13:40:19.980     test     2012-01-11 13:40:19.980     8A8604CE-A9B0-44F6-8DA8-849C52CCCD20
                        2     一切都好     test     2012-01-11 13:40:19.980 test     2012-01-11 13:40:19.980     AAD0BBF4-29B8-4459-B42C-C10EEDA6984F


                        odbc.ini

                        [DEFAULT]
                        Driver = SQL Server Native Client 11.0

                        [MSTEST]
                        Driver = /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0
                        #Driver = SQL Server Native Client 11.0
                        Description = Microsoft SQL Server ODBC Driver V1.0 for Linux
                        #Address = testxxx
                        Server = testxxx,xxxxx
                        Database = testdb

                        Thanks,
                        Steve.
                        • 9. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                          Kgronau-Oracle
                          I see, sorry for the ping pong, but can you also post the gateway init file - you're using nchars and the gateway parameters HS_NLS_NCHAR, HS_KEEP_REMOTE_COLUMN_SIZE and HS_NLS_LENGTH_SEMANTICS=CHAR
                          impact the behavior.

                          Edited by: kgronau on Mar 6, 2012 5:35 PM
                          Ok - not longer needed
                          I reproduced the issue in SQL Dev 3.1

                          CREATE TABLE [dbo].[data_ms](
                          [data_id] [bigint] IDENTITY(1,1) NOT NULL,
                          [data_desc] [nvarchar](2048) COLLATE SQL_Latin1_General_CP850_BIN2 NULL,
                          [data_create_usrid] [nvarchar](128) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,
                          [data_create_dt] [datetime] NOT NULL,
                          [data_modify_usrid] [nvarchar](128) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,
                          [data_modify_dt] [datetime] NOT NULL,
                          [data_rowguid] [uniqueidentifier] NOT NULL,
                          CONSTRAINT [PK_data_ms_data_id] PRIMARY KEY CLUSTERED
                          (
                          [data_id] ASC
                          )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                          ) ON [PRIMARY]


                          insert into data_ms ([data_desc] , [data_create_usrid],[data_create_dt] ,[data_modify_usrid] ,[data_modify_dt],data_rowguid)values ('개정과 무', 'test', '2012-01-11 13:40:19.980', 'test', '2012-01-11 13:40:19.980', '8A8604CE-A9B0-44F6-8DA8-849C52CCCD20');


                          SQLDeveloper select:
                          Error starting at line 1 in command:
                          select * from "data_ms"@MSODBCSQLSERVER_DG4ODBC_EMGTW_1123_DB
                          Error at Command Line:1 Column:0
                          Error report:
                          SQL Error: Protocol violation

                          SQL*Plus is working
                          So it is a SQL Dev issue mayve again JDBC related
                          • 10. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                            user336659
                            No worries Kgronau, here is the gateway file.

                            #
                            # HS init parameters
                            #
                            HS_FDS_CONNECT_INFO = MSTEST
                            HS_FDS_TRACE_LEVEL = DEBUG
                            HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
                            HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
                            HS_NLS_NCHAR=UCS2
                            #HS_FDS_SUPPORT_STATISTICS=FALSE
                            # ODBC specific environment variables
                            #
                            set ODBCINI= /usr/oracle/.odbc.ini
                            #set ODBCINI=<full path name of the odbc initilization file>


                            #
                            # Environment variables required for the non-Oracle system
                            #
                            #set <envvar>=<value>
                            Thanks,
                            Steve
                            • 11. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                              Kgronau-Oracle
                              SQL*Plus is working fine and SQLDeveloper is causing the protocol violation when nvarchars are in the select list.
                              • 12. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                                user336659
                                yes, that's right..SQL*Plus is working fine and SQLDeveloper is causing the protocol violation.

                                Thanks,
                                Steve.
                                • 13. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                                  Kgronau-Oracle
                                  a first quick look shows that when I switch to the thick JDBC driver inSQLDev the select works. So maybe a thin jdbc issue.
                                  When you have the Oracle client installed also on the machine, you could choose in the connection properties tab TNS instead of BASIC and specify a TNS alias - then the thick JDBC driver based on SQL*Net will be used
                                  • 14. Re: "Protocol Violation" message in SQL Developer Worksheet when executingquery
                                    user336659
                                    I don't think I have client installed on my box. When I am trying to change the connection type to TNS from basic in SQL developer and test the connection it throws me "test-failed: no ocijdbc11 in java.library.path.

                                    Thanks,
                                    Steve.
                                    1 2 Previous Next