October 4, 2007 at 9:50 am
To narrow this down..
can you create this procedure
create procedure dbo.stp_testovlimit
@myinput varchar(50),
@myoutput varchar(50)output
as
select @myoutput = @myinput
select @myinput
--and run this
begin
declare @theoutput varchar(50)
exec stp_testovlimit 'The quick brown fox jumped over the lazy dog',@myoutput = @theoutput output
end
October 4, 2007 at 9:50 am
I'm not sure if you can post it on a web-site, but I do know that you can call it in. You could go to the MS web-site and poke around the support area as they may have what you are looking for there. Keep in mind that they may want to charge you if you do not have a support agreement. If you are unsure, just ask them if you will be charged before you give the the details to work with. From the sounds of it, you may not care even if you do have to pay as whatever is causing this needs fixed.
October 4, 2007 at 10:20 am
When I run this
begin
declare @theoutput varchar(50)
exec stp_testovlimit 'The quick brown fox jumped over the lazy dog',@myoutput = @theoutput output
end
I get a recordset that contains the entire string (displayed on the grids screen), but no output (nothing on the messages screen)
When I run this
begin
declare @theoutput varchar(50)
exec stp_testovlimit 'The quick brown fox jumped over the lazy dog',@myoutput = @theoutput output
SELECT @theoutput
end
I get two recordsets with the complete string, but no output parameters. I've even tried bringing back the 30 leftmost characters, and the 20 rightmost characters as output parameters, and they all come back (with duplications of course, unless the string length is 50 characters). So the SP is definitely retrieving the whole thing, it just won't return it to me. I'm really trying to avoid the workaround of 50 parameters, each one character, and then concatenating it all back in the application.:w00t:
Mattie
October 4, 2007 at 10:50 am
MattieNH (10/4/2007)
I get two recordsets with the complete string, but no output parameters. I've even tried bringing back the 30 leftmost characters, and the 20 rightmost characters as output parameters, and they all come back (with duplications of course, unless the string length is 50 characters). So the SP is definitely retrieving the whole thing, it just won't return it to me. I'm really trying to avoid the workaround of 50 parameters, each one character, and then concatenating it all back in the application.
Mattie,
I don't quite get what you mean here. You say that you get 2 recordsets when you execute the code with the 'SELECT @theoutput' added but no output parameters. @theoutput is the output parameter. Can you clarify what you mean here?
October 4, 2007 at 11:01 am
I didn't see this in any of the posts, but will throw it out there anyway. If you do a convert to varchar, without specifying a length, it defaults to a length of 30. Do you do any converts in any of the code?
SELECT CONVERT(VARCHAR,'1234567890123456789012345678901')
returns
123456789012345678901234567890
while
SELECT CONVERT(VARCHAR(31),'1234567890123456789012345678901')
returns
1234567890123456789012345678901
Brian
October 4, 2007 at 11:13 am
Well, I can clarify what I'm seeing, whether or not that clarifies what I mean is another thing...
In QA, there are usually three tabs: Editor, Grids, Messages. When I run a stored procedure that returns output parameters (like the one I wrote originally), I get something like the following on the Messages tab.
Stored Procedure: AOC_Applications.dbo.stp_TestReturn
Return Code = 0
Output Parameter(s):
@TypeDesc = Retired Supreme Court Associat
When I run a stored procedure that returns a recordset, I get something like the following on the Grids tab.
(No column name)
Retired Supreme Court Associate Justice
So in the code Mr. Polecat gave me, there was nothing on the Grids tab. Even after I added the
SELECT @theoutput
statement, the output appeared on the Grids tab (as a recordset), not on the Messages tab (as an output parameter).
So it's the fact that it's a parameter that causes the truncation. If I were willing to take the data as a recordset (which I'm not), I wouldn't have the truncation problem.
Mattie
October 4, 2007 at 11:29 am
Brian,
Excellent, at least there's a relationship between the number 30 and a known behavior!
Unfortunately, I'm not converting anything anywhere. In fact, I thought maybe I could trick the SP into returning all the positions with the following code (after reading your post)
SELECT @TypeDesc =
convert(varchar(50), ISNULL((Description), ''))
FROM JudicialServicesPersonnelTypes
WHERE PersonnelType = 'R2'
but I still ended up with only thirty characters.
Same with this code
SELECT @TypeDesc =
cast(ISNULL((Description), '') as varchar(50))
FROM JudicialServicesPersonnelTypes
WHERE PersonnelType = 'R2'
and this
SELECT @TypeDesc = cast(Description as varchar(50))
FROM JudicialServicesPersonnelTypes
WHERE PersonnelType = 'R2'
I appreciate these, and any other thoughts you have on this problem. This was supposed to be the easy part of my day.
Mattie
October 4, 2007 at 11:50 am
October 4, 2007 at 12:03 pm
The print statement returns the entire phrase on the Messages tab:
(1 row(s) affected)
The quick brown fox jumped over the lazy dog
The version of QA is SQL 8.00.2039 (from the about tab), but I can't help but think it's bigger than QA. The reason I'm even testing it in QA is because in my ASP application I'm getting the same truncation. Here's the connection string I'm using there, in case it points to something of interest.
dbAOCApplicationsServer = _
"Provider=sqloledb;" &_
"Network Library=DBMSSOCN;" &_
"Data Source=1.2.333.44,1433;" &_
"Initial Catalog=Applications;" &_
"User ID=UserName;" &_
"Password=Password"
Mattie
October 4, 2007 at 12:12 pm
October 4, 2007 at 12:29 pm
I am still a bit confused as to what you are seeing. I don't use grid, I prefer text and when you execute a SP in text, you don't get the messages tab. I tried to execute stp_testovlimit using grid and I did not get the type of output that you've described.
With all that said, none of this matters. I wouldn't be concerned with anything in the messages tab. What you need to be concerned with is the value that is in @theoutput. The bottom line here is that @theoutput is the output parameter value regardless of any infromation SQL Server chooses to give you in the messages tab. If this value contains the whole string (which I think you've stated it does) than your stored procedure is returning the whole string untruncated. Am I missing something?
EDIT: You also stated that you get the truncation in your ASP code. How are you checking for the output parameter in your ASP call to the SP? Can you post that code as well?
October 4, 2007 at 12:43 pm
Several years ago, there was a series of ESPN commercials that showed the Sunday Night Football announcing team in college. And whoever was playing the professor announced 'there are no stupid questions, only stupid people who ask questions'. For some reason, that occurs to me now.
Here's the answer: When you let QA build the query for you, it builds it like this:
DECLARE @rc int
DECLARE @TypeDesc nvarchar(50)
DECLARE @Reason varchar(35)
DECLARE @CourtName varchar(35)
EXEC @rc = [AOC_Applications].[dbo].[stp_TestReturn] @TypeDesc OUTPUT , @Reason OUTPUT , @CourtName OUTPUT
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: Applications.dbo.stp_TestReturn'
SELECT @PrnLine = 'Return Code = ' + CONVERT(nvarchar, @rc)
PRINT @PrnLine
PRINT 'Output Parameter(s): '
SELECT @PrnLine = '@TypeDesc = ' + isnull( CONVERT(nvarchar, @TypeDesc), ' ' )PRINT @PrnLine
SELECT @PrnLine = '@Reason = ' + isnull( CONVERT(nvarchar, @Reason), ' ' )
PRINT @PrnLine
SELECT @PrnLine = '@CourtName = ' + isnull( CONVERT(nvarchar, @CourtName), ' ' )
PRINT @PrnLine
Notice the absence of an explicit value following nvarchar (thanks, Brian). So now that I have a reason why it's being truncated in QA, I know it can't be the same reason in my stored procedure. So sure enough, the old cut and paste monster has reared its ugly head, and I've copied an ADO parameter line that limits it to 35 characters, instead of the 50. Yes, I know, it's not 30, but close enough, and still causing truncation, and still similar enough to make me think there was a relationship between the two.
But the fact is, I never would have figured this out if Brian hadn't mentioned the default behavior of nvarchar, and if Mr. Polecat hadn't given me the scripts to run, and John hadn't tried this all. So my grateful apologies to you all.
I was right about one thing. As embarrassed as I am about this, it's still better than being baffled.
Mattie
October 4, 2007 at 12:57 pm
October 4, 2007 at 1:06 pm
October 4, 2007 at 2:15 pm
mrpolecat (10/4/2007)
Are we using the same QA? I would love to know how you get QA to build the query for you.I bring up the Object Browser, right click on the stored procedure I want to run, and select Open. That brings up a dialog box with the parameters, which I fill in, and then I click the Execute button.
Or maybe not if it plays tricks on you.:D
An excellent consideration. A tool is a dangerous thing.
Mattie
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply