Stored Procedure Problem

**MxTxL** · Feb 18, 2004 12:44

I'm new to stored procedures so if this is common knowledge, please excuse my ignorance.

I'm trying to convert an ASP page that's been around forever to use a stored procedure instead of the straight sql call that it's been doing for years.

Anyway, here's the SP:

Code:

 
CREATE PROCEDURE   SP_fastrunlog
(
   @runlogdate datetime

)   
AS

SELECT * from VW_RUNLOG WHERE (apptdate =   @runlogdate) ORDER BY appttime

 return
GO

The view VW_RUNLOG is big, huge and terribly complex. It returns dozens of fields. I'm able to loop through the results and all of that fun stuff... I'm able to see all of the fields, and everything is cheery EXCEPT one field doesn't show up in the results. The field is a text field named 'comments'. It works find if i call the view, but it doesn't come through if i use the SP.

I get the same results when i paste the SQL from the view into the SP. And i get the same results when i put just the important code on a page by itself like so:

Code:

sql="EXECUTE sp_fastrunlog '2/18/2004'"

'sql="SELECT * FROM VW_RUNLOG WHERE (apptdate = '2/18/2004') ORDER BY appttime"
Set rs = server.createobject("adodb.recordset")
			rs.open SQL,Objconnect,1,1
	 
	 
Do While rs.EOF = False

 Response.Write "Name: " &rs("name") & " Comments: " & rs("comments") &"<br>"

rs.MoveNext
LOOP
rs.Close
set rs = nothing

Alternatly commenting out the SQL commands, the one using the view displays both the name and the comments... using the SP, it only displays the name and not the comments.

SO: WTF?

**wwb_99** · Feb 18, 2004 16:05

That comment field wound not happen to be a TEXT field would it?

WWB

**dhtmlgod** · Feb 18, 2004 16:19

Wow, that stored procedure is so "wrong" its hard to know where to start! So I won't

Is there data in "comments!?

**MxTxL** · Feb 21, 2004 00:56

wwb:
Yes, it's a text field. I had the idea that was the problem but i'm unable to find any way to either fix it or get around it. I'm new to using stored procedures, so if it's 'common' knowledge I apolize.

dhtmlgod:
yes, there are data in comments.

**moospot** · May 5, 2004 07:35

bump

**r937** · May 5, 2004 18:32

that's the mostest curiousest bump i ever saw

a sitepoint community advisor bumping a thread?

that he/she didn't participate in?

ten weeks later?

why?

**moospot** · May 6, 2004 08:58

Why? Because I would like to know what the answer is to the problem.

**MarcusJT** · May 7, 2004 16:47

Originally Posted by dhtmlgod

Wow, that stored procedure is so "wrong" its hard to know where to start! So I won't

Awww! Go on!

(Is it really *THAT* bad?!!)

**Sapporo** · Jun 8, 2004 17:55

Not sure what causes this, but I have ran into this problem before. I have fixed it in the past by specifying all of the field names in the query. I always put the text field last.

In other words:

Do:

Select column1, column2, columntext from table

Don't:

Select * from table

Hope this helps!

**wwb_99** · Jun 9, 2004 05:05

To add to sappro's answer, it is an issue with the way the memory cursors work in the SQL Server ADO and ODBC drivers. Text and image columns must be the far-right hand columns in your select clause. Smarter cookies always put it to the far right in their tables.

Figured this out on a PHP project, which had some interesting results when text was not to the far right . . .

WWB

**MarcusJT** · Jun 9, 2004 07:17

I've heard of that happening, but I've never encountered it myself. Putting the variable length/binary fields last is the "official" workaround.

However, I have a feeling that this bug doesn't crop up with the OLEDB drivers... so I suspect that you are using the old ODBC ones. If this is the case (why?!), I highly recommend that you switch to using the moderm, leaner, meaner, more stable OLEDB drivers instead.

**wwb_99** · Jun 9, 2004 11:55

Had to use ODBC for the PHP app--could not install the MSSQL extensions. Not to mention it started life running on access until we knew we could secure a production sql server.

It came out pretty good in the end.

WWB

PS: Correct me if I am wrong, but are not the ODBC drivers for MSSQL a wrapper around the oledb drivers?

**MarcusJT** · Jun 9, 2004 18:39

Originally Posted by wwb_99

Correct me if I am wrong, but are not the ODBC drivers for MSSQL a wrapper around the oledb drivers?

Precisely, which is why ODBC drivers are slower and less stable than pure OLEDB, as well as often generating less useful error messages...

http://www.adopenstatic.com/faq/whyOLEDB.asp