Error Calling SQL Stored Procedure from Access

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,
JoeI 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 didnt post your SP i cant guess what the problem in the SP is,
just post it, we will help you.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Joe Williams" <joe@.anywhere.com> schrieb im Newsbeitrag
news:ur0LCgDQFHA.2736@.TK2MSFTNGP09.phx.gbl...
>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
>
>|||Hi
Data issue.
In your SP or trigger on the table the SP works with, there is a substring
function that is failing. Possible a string that is shorter than expected (o
r
null).
Regards
Mike
Regards
Mike
"Joe Williams" wrote:

> I am getting an error when I try to call a stored procedure from Access. T
he
> 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 stor
ed
> 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
>
>
>|||Can you post the sp code?
AMB
"Joe Williams" wrote:

> I am getting an error when I try to call a stored procedure from Access. T
he
> 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 stor
ed
> 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
>
>
>|||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
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23R9TMjDQFHA.128@.tk2msftngp13.phx.gbl...
>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 didnt post your SP i cant guess what the problem in the SP
>is, just post it, we will help you.
>
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joe Williams" <joe@.anywhere.com> schrieb im Newsbeitrag
> news:ur0LCgDQFHA.2736@.TK2MSFTNGP09.phx.gbl...
>|||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 Smeyer.
"Joe Williams" <joe@.anywhere.com> schrieb im Newsbeitrag
news:OF05vlDQFHA.3156@.TK2MSFTNGP15.phx.gbl...
> 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
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:%23R9TMjDQFHA.128@.tk2msftngp13.phx.gbl...
>