Microsoft Odbc Driver For Oracle Syntax Error Or Access Violation
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss error [42000] [oracle][odbc]syntax error or access violation the workings and policies of this site About Us Learn more about microsoft odbc sql server driver syntax error or access violation Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow sqlstate 42000 Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Microsoft ODBC driver for Oracle Syntax error or access violation (-2147217900) up vote 0 down vote favorite I have a large VB program that connects to Oracle database. strCn = "Driver={Microsoft ODBC for Oracle};" & _ "SERVER=PSPROD;" Set Cn = New ADODB.Connection Cn.ConnectionString = strCn Cn.CursorLocation = adUseNone Cn.Open There are many users of my program so I have a table that contains each user's login name and their access rights to the various tables. I create a recordset of all users when the program is started and then select USERNAME and GRANTED_ROLE from the record set where USERNAME and PASSWORD are found. I use a "Set role 'GRANTED_ROLE' identified by 'password'" statment and Cn.Execute statement to set up the user's access rights. This is all done in a Module. On a form, I want to call a Stored Procedure that will SELECT, INSERT and UPDATE information into another schema's tables. I am able to call and run the stored procedure when I create a new connection to the database with this code: Dim cmd5040 As ADODB.Command Dim conn5040 As ADODB.Connection Dim param5040 As ADODB.Parameter Set conn5040 = New ADODB.Connection conn5040 = "Driver={Microsoft ODBC for Oracle};" & _ "SERVER=PSPROD; UID=XXXXXXX; PWD=XXXXXXXX" conn5040.Open Set cmd5040 = New ADODB.Command With cmd5040 .ActiveConnection = conn5040 .CommandType = adCmdStoredProc .CommandText = "S4115040_IMPORT_NEWBIDITEMSPES.S4115040_CheckTime" .Parameters.Append .CreateParameter(, adInteger, adPar
> .NET Framework Class Libraries Question 0 Sign in to vote I have a very simple Oracle 10g SP: CREATE OR REPLACE procedure DEV.SL_CLOB_TEST(numId IN PLS_INTEGER,id IN PLS_INTEGER, strText IN CLOB) as begin insert into test_table values (numId,id, strText, sysdate, user); end; And I have .Net 4.0 code to consume it static void Main(string[] args) { string connectionString = "Driver={Microsoft ODBC for http://stackoverflow.com/questions/2030531/microsoft-odbc-driver-for-oracle-syntax-error-or-access-violation-2147217900 Oracle};Server=myserver;Uid=username;Pwd=password"; var connection = new OdbcConnection(connectionString); connection.Open(); IDbCommand command = connection.CreateCommand(); command.CommandText = "{call SL_CLOB_TEST(?,?,?)}"; command.CommandType = CommandType.StoredProcedure; OdbcParameter parameter1 = new OdbcParameter("NUMID", OdbcType.Int); parameter1.Value = 123; parameter1.Direction = ParameterDirection.Input; command.Parameters.Add(parameter1); OdbcParameter parameter2 = new OdbcParameter("ID", OdbcType.Int); parameter2.Value = 234; parameter2.Direction = ParameterDirection.Input; command.Parameters.Add(parameter2); OdbcParameter parameter3 = new OdbcParameter("STRTEXT", OdbcType.Text); parameter3.Value https://social.msdn.microsoft.com/Forums/vstudio/en-US/92308dcf-f68a-4363-90c5-38bcb84cb2bd/odbcnet-and-oracle-10g-stored-procedure-problem?forum=netfxbcl = getClob(); parameter3.Direction = ParameterDirection.Input; command.Parameters.Add(parameter3); command.ExecuteNonQuery(); } private static string getClob() { return new string('a', 10); } When I ran it O got error ERROR [42000] [Microsoft][ODBC driver for Oracle]Syntax error or access violation. The whole stack trace is System.Data.Odbc.OdbcException was unhandled Message=ERROR [42000] [Microsoft][ODBC driver for Oracle]Syntax error or access violation Source=msorcl32.dll ErrorCode=-2146232009 StackTrace: at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteNonQuery() at ConsoleApplication1.Program.Main(String[] args) in D:\Temp\ConsoleApplication1\ConsoleApplication1\Program.cs:line 32 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to https://www.experts-exchange.com/questions/21021178/Microsoft-ODBC-driver-for-Oracle-Syntax-error-or-access-violation-Error.html Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live https://communities.sas.com/t5/General-SAS-Programming/Call-Oracle-stored-procedure-using-odbc/td-p/89822 Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > [Microsoft][ODBC driver for Oracle]Syntax error or access violation Error Want to Advertise Here? Solved [Microsoft][ODBC driver for Oracle]Syntax error or access violation Error Posted on 2004-06-10 Oracle Database 1 Verified Solution 4 Comments 2,297 Views Last Modified: 2010-08-05 Hi all, I get this syntax error error when I try to call a package from my ASP page. Does anyone know why? Thanks, xyz 0 Question by:MaryZhou Facebook Twitter LinkedIn Google LVL 1 Best Solution bymtae We've had similar problems, unfortunately error messages from ASP for Oracle are pretty vague. When we've had this problem it has been because number of records coming back from Oracle are greater than Go to Solution 2 Comments LVL syntax error or 7 Overall: Level 7 Oracle Database 6 Message Expert Comment by:bvanderveen2004-06-10 Because you have done something wrong. The actual error message would help :-) Check your parameters first, are they the correct number and the correct type to start with. 0 LVL 1 Overall: Level 1 Oracle Database 1 Message Accepted Solution by:mtae2004-06-11 We've had similar problems, unfortunately error messages from ASP for Oracle are pretty vague. When we've had this problem it has been because number of records coming back from Oracle are greater than the size of the recordset definied in ASP. For some reason you have to guess the size of the recordset that is going to come back!! This can take up valuable memory on the web server if you make the recordset to big and can cause the problem above if it's too small. If you are trying to return a recordset try increasing the size of it in the ASP and see if that helps. Unfortunately that error could be due to many things... 0 Write Comment First Name Please enter a first name Last Name Please enter a last name Email We will never share this with anyone. Comment Submit Your Comment By clicking you are agreeing to Experts Exchange's