May 10, 2011 at 2:32 am
I have never in my 4 1/2 years working with SQL Server come accross anything quite this bizarre.
I have 2 supposedly identical tables. the 'FIL' authority list, and the 'RIA' authority list.
one contains 250 rows of data for the company 'FIL' and the other contains 123 rows of data for the company 'RIA'
an ASP page on a remote IIS 6.0 server creates an adodb recordset and outputs the contents of both tables to an html formatted table on screen.
the RIA table returns all 123 rows fine. the FIL table returns 250 blank rows.
querying both tables through SSMS works fine.
My query to select data is quite simply:
"SELECT * FROM [fil delegated authority] ORDER BY [Cost Centre]"
in the ASP - recordset("cost centre") holds 250 blank strings and outputs blanks
if you change that to
"SELECT 'foo' as 'cost centre' FROM [fil delegated authority] ORDER BY [Cost Centre]"
in the ASP - recordset("cost centre") holds 'foo' 250 times and it DOES output foo on the html.
yet the table for RIA works fine!
This is all very perplexing and I'm contemplating dropping the table and rebuilding it.
unfortunately it's part of a 40GB database so dbcc checkdb might take a while.
anyone come accross this or similar before?
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 10, 2011 at 2:43 am
can you provide:
- @@version info ?
- ddl of both objects
- did this start all of a sudden or is this a new release ?
- can you find anything in sqlserver errorlog ?
- are you performing errorhandling in your query/sproc ? (serverside or asp side)
- are there special characters you the data ( that may confuse presentation ?) ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 10, 2011 at 2:49 am
Just chnage the permission of the user(asp) and seen the result. might be the problem with user define data type
May 10, 2011 at 3:08 am
Hi
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FIL Delegated Authority](
[Cost Centre] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 1] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 1] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 2] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 2] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 3] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 3] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 4] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 4] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 5] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 5] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 6] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 6] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RIAS Delegated Authority](
[Cost Centre] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 4] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 4] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 5] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 5] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approver 6] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Level 6] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
I notice a difference with ANSI_PADDING... dont know if that would have any impact...
It seems to have happened suddenly.
Nothing in the log.
no real error handling - if anything goes wrong it gives an error 500 and dumps the message into the iis log - which is also clear
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 10, 2011 at 3:09 am
ah.
I've just seen one table has the wrong data types.
I'll play with that and see if it makes a difference...
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 10, 2011 at 3:12 am
yep thats fixed it.
asp does not seem to like varchar(max) - I can only assume a colleague changed the table for some reason whilst I was on holiday!
appologies for the time-waster!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 10, 2011 at 3:20 am
BenWard (5/10/2011)
yep thats fixed it.asp does not seem to like varchar(max) - I can only assume a colleague changed the table for some reason whilst I was on holiday!
appologies for the time-waster!
No problem. it has been an eye opener 😉
If you didn't install sqlncli at the asp level or aren't using the sqlncli driver, it will not be able to use the new data types correct !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 10, 2011 at 3:26 am
That's the wierd thing, the IIS box has SQL2005 on it anyway - and has the sqlncli driver selected for this connection.
maybe it's to do with the 'max' setting.
who knows?!
I can't RDP to that box to test it at the moment. I'll have to check that out later.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply