error converting varchar to numeric

i have a huge stored procedure abt 500 lines..and i am calling this sp from an asp.net page...thn i got this error - error converting varchar to numeric - and am trying to debug...is there any way we can find out where the error is coming from...like aproxly which line number..etcor do i have to go through each line manually and see where i am doing the conversion...

thanksNever tried it myself but it should let you step through a sproc like you would your C#/VB.Net code behind.

Walkthrough: Debugging Hello World, a SQL Stored Procedure|||hi MMS

I was able to isolate the line tht was causing the problem..however i will go through the article to see if it will help with some info for future use.

thanks.

Error Code 207

I am getting the 207 error code and can't figure out the problem. I have a coldfusion page that is calling a stored procedure. The page allows users to sort the displayed columns and it is passing the column numbers into my stored procedure. I am using a case statement to decide which sort to use.

The error message is: Invalid column name 'SCHOOL'.
I've tried adding single and double quotes here and it worked before I added the case statment when I just type 'order by school'.

Below is some of my code:

SELECT P.NAME,
P.ASORG,
P.MAILSTOP,
P.OFCPHONE,
R.EMPLID,
SCHOOL = S.DESCR,
R.DEGREE_LEVEL,
R.DISCIPLINE,
TEAM = T.TEAM_NAME,
NAT_EVENT = NE.EVENT_NAME,
R.SCHOOL_CODE,
R.TEAM_CODE,
R.EVENT_CODE,
FROM HR_EXTRAS..RECRUITERS R,
HRWH..PS_SCHOOL_TBL S,
HRWH..PERSON P,
HR_Extras..UP_TEAMS T,
HR_Extras..UP_NATIONAL_EVENTS NE,
WHERE R.SCHOOL_CODE = S.SCHOOL_CODE
AND R.EMPLID = P.SNL_ID
AND T.TEAM_CODE = R.TEAM_CODE
AND NE.EVENT_CODE = R.EVENT_CODE
ORDER BY CASE WHEN @.SORT = 1 THEN P.NAME
WHEN @.SORT = 2 THEN P.ASORG
WHEN @.SORT = 3 THEN SCHOOL
WHEN @.SORT = 4 THEN T.TEAM_NAME
WHEN @.SORT = 5 THEN NE.EVENT_NAME
END

Can anyone offer suggestions?WHEN @.SORT = 3 THEN S.DESCR-PatP|||WHEN @.SORT = 3 THEN S.DESCR

That doesn't work because the SQL statement is actually part of a union query. The previous statements pull the exact same data but have

SCHOOL = ' NM-Not Assigned',

and

SCHOOL = ' CA-Not Assigned',

Sorry I didn't include that previously. The two values above are not stored in the PS_SCHOOL_TBL, thus the explicit call.|||deleted comment

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

Error calling CreateReportHistorySnapshot method.

Hello,

I am getting an error when calling the CreateReportHistorySnapshot method from a custom assembly. The error is:

Response is not well-formed XML.

The data source is an XML file which is updated by the custom assembly before the call into the CreateReportHistorySnapshot method.

Has anyone seen this problem before or have any suggestions?

The Data Source XML is well-formed by the way... :-0)

I have had this error before when i wasnt the db admin. it seems there are a few errors along these lines where error messages flag up issues which are actually unrelated.

Try Permission and i would assume this will fix your error.

Andy

|||

Hi Andy,

Thanks for this!! Can you just clarify what would fix the error.

Many thanks.

|||

The user that is running the createsnapshot method needs to be set as the DB Owner within SQL, this fixed the same problem i was having with this a couple of months ago.

It is often the case with many of these areas, subcriptions is another area where db admin comes into play.

If you hear of another fix please let me know as this is not ideal for me as a long term solution.