Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  stored proc returns blank values for some fields

Author  Topic  wengang Starting Member 4 Posts Posted - 2005-09-26 : 03:16:00 Hi all. I'm having a strange problem with a stored procedure. To start the SP looks like this @ID int select * from tblArticles where articleID = @ID I can run this as an sql query on my ASP page and it returns the single record with all data intact. I can also run the sql query in Query Analyzer and it returns the same. And I can run the SP in QA as: GetArticle 16670 --(for example) But, the strange part, when I run the stored procedure from my ASP page, several of the data fields come back blank. and what's more, it isn't the same data fields that come back blank, depending on which one I call first in my ASP code. Here is one way I called the SP: SET rs6=CreateObject("ADODB.Recordset") rs6.Open "GetArticle 16670",connSS Here is another way (as recommended by friends at vbforums.com) set objCmd=Server.CreateObject("ADODB.command") with objCmd .CommandText = "GetArticle" .ActiveConnection = ConnSS .CommandType = adCmdStoredProc .Parameters.Append objCmd.CreateParameter("@Id", adInteger, adParamInput,,16670) end with Set rs6 = objCmd.Execute() But in both cases I have the same problem: response.write rs6("Text") & "<br>" response.write rs6("publishdate") & "<br>" response.write "source: " & rs6("source") & "<br>" with the above 3 lines of code, the first two lines print out data, but the third line (source) prints out blank (tested by adding the word "SOURCE:" to make sure it was writing) to add to the strangeness, I switched the lines around: response.write "source: " & rs6("source") & "<br>" response.write rs6("publishdate") & "<br>" response.write rs6("Text") & "<br>" and this time, the first two lines print their values and the third line is blank (just as before) but since I switched the column names, it is now the "Text" column that shows blank. I can't figure this out. Why are some fields coming back blank? Why are they different depending on which was referenced first? I need any possible reasons/suggestions for this problem. For reference, here is a listing of the entire table structure: ArticleID INT IDENTITY TITLE NVARCHAR 250 HTML NTEXT TEXT NTEXT FILENAME NVARCHAR 200 SOURCE NVARCHAR 600 PUBLISHDATE DATETIME STATUS INT VIEWS INT STATSMAIN INT STATSMONTH INT STATSYEAR INT LANG INT TOPSTORY BIT HAVEIMAGE BIT HAVETABLE BIT TEXT1 NVARCHAR 4000 FILTERS VARCHAR 100 EMAIL BIT the reason i use nvarchar and ntext instead of varchar and text is that these columns may contain Japanese and Chinese characters. I've never seen this problem before but it is consistent and I can't find anything in the code that would cause this strange behavior. Ideas? Thanks Wengang