Error Calling SQL Stored Procedure from Access

[Joe Williams]

I am getting an error when I try to call a stored procedure from Access. The
error is as follows: "[Microsoft][OBDC SQL Driver][SQL Server] Invalid
length parameter passed to the substring function."

It has been working fine for months until yesterday, with no code changes
being done. I thought maybe teh data is the problem, but if I run the stored
procedure from the SQL Query Analyzer on the server, I receive no error
message. It seems to be something between Access and SQL.

What could be up? Below is the code that lies behind a form button that
calls the stored procedure. Help!

Dim objConn As ADODB.Connection
Dim objCmd As New ADODB.Command

' Open a connection without using a Data Source Name (DSN)
Set objConn = New ADODB.Connection
objConn.ConnectionString = "Driver=SQL Server;Server=TRADB1;" & _
"Database=AllData;UID=user;PWD=password;"

objConn.open

Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "WorkOrderTransferImport"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Refresh
objCmd.Execute

' clean up
objConn.Close

Set objConn = Nothing


Thanks,

Joe

 

[Jens Süßmeyer]

I think the problem is not located within the ADO Code rather than the SQL
Code, your calling something like a stringfunction which expects paramters
like len, e.g. LEFT(Text,Lenght). That expression evaluates not right and
that s the problem whats all about. If the problem appears now, without code
changing, a special row was inserted meanwhile which causes the error. As
far as you didn´t post your SP i can´t guess what the problem in the SP is,
just post it, we will help you.


HTH, Jens Süßmeyer.

 

[Joe Williams]

Thanks Jens

The odd part is that when you run it from SQL server you do not receive any
error messages. That is very odd.

The other thing is that the store procedure has about 10 other nested stored
procedures so I am not sure where to even start debugging it!

Is there a way to have SQL be more specific about what record, SP, etc that
the error is occuring on?

Thanks

Joe

I think the problem is not located within the ADO Code rather than the SQL
Code, your calling something like a stringfunction which expects paramters
like len, e.g. LEFT(Text,Lenght). That expression evaluates not right and
that s the problem whats all about. If the problem appears now, without
code changing, a special row was inserted meanwhile which causes the error.
As far as you didn´t post your SP i can´t guess what the problem in the SP
is, just post it, we will help you.


HTH, Jens Süßmeyer.

---
http://www.sqlserver2005.de
---

I am getting an error when I try to call a stored procedure from Access.
The error is as follows: "[Microsoft][OBDC SQL Driver][SQL Server] Invalid
length parameter passed to the substring function."

It has been working fine for months until yesterday, with no code changes
being done. I thought maybe teh data is the problem, but if I run the
stored procedure from the SQL Query Analyzer on the server, I receive no
error message. It seems to be something between Access and SQL.

What could be up? Below is the code that lies behind a form button that
calls the stored procedure. Help!

Dim objConn As ADODB.Connection
Dim objCmd As New ADODB.Command

' Open a connection without using a Data Source Name (DSN)
Set objConn = New ADODB.Connection
objConn.ConnectionString = "Driver=SQL Server;Server=TRADB1;" & _
"Database=AllData;UID=user;PWD=password;"

objConn.open

Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "WorkOrderTransferImport"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Refresh
objCmd.Execute

' clean up
objConn.Close

Set objConn = Nothing


Thanks,

Joe

 

[Jens Süßmeyer]

Always helpful is to put debugging information in the procedures, like

Print 'Hit First Procedure' --and so on.

EXEC Something

Print 'Done with First Procedure'

Run Profiler to see, where the error occures, display the exceptions when
you secify the column to display.

Jens Süßmeyer.

Thanks Jens

The odd part is that when you run it from SQL server you do not receive
any error messages. That is very odd.

The other thing is that the store procedure has about 10 other nested
stored procedures so I am not sure where to even start debugging it!

Is there a way to have SQL be more specific about what record, SP, etc
that the error is occuring on?

Thanks

Joe

I think the problem is not located within the ADO Code rather than the SQL
Code, your calling something like a stringfunction which expects paramters
like len, e.g. LEFT(Text,Lenght). That expression evaluates not right and
that s the problem whats all about. If the problem appears now, without
code changing, a special row was inserted meanwhile which causes the
error. As far as you didn´t post your SP i can´t guess what the problem in
the SP is, just post it, we will help you.


HTH, Jens Süßmeyer.

---
http://www.sqlserver2005.de
---

I am getting an error when I try to call a stored procedure from Access.
The error is as follows: "[Microsoft][OBDC SQL Driver][SQL Server]
Invalid length parameter passed to the substring function."

It has been working fine for months until yesterday, with no code
changes being done. I thought maybe teh data is the problem, but if I
run the stored procedure from the SQL Query Analyzer on the server, I
receive no error message. It seems to be something between Access and
SQL.

What could be up? Below is the code that lies behind a form button that
calls the stored procedure. Help!

Dim objConn As ADODB.Connection
Dim objCmd As New ADODB.Command

' Open a connection without using a Data Source Name (DSN)
Set objConn = New ADODB.Connection
objConn.ConnectionString = "Driver=SQL Server;Server=TRADB1;" & _
"Database=AllData;UID=user;PWD=password;"

objConn.open

Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "WorkOrderTransferImport"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Refresh
objCmd.Execute

' clean up
objConn.Close

Set objConn = Nothing


Thanks,

Joe