Saving large text file in table

Have created an SQL Server 2000 table which includes two columns each data type varchar(8000) in order to accomodate a semi-large amount of text (want to avoid using text data type). I enter the max in the first column but I keep getting an error of "Cannot create a row of size 12066 which is greater than the allowable maximum of 8060" when I try to save any more in the second column of the table. What am I doing wrong?

SQL Server stores rows (and indexes) in pages. Pages are 8K in size. There is some required header information (i.e. next block in chain, etc.), which drops the usable space for rows to 8060. So a single row cannot exceed 8060 bytes and rows cannot span multiple pages. You have two choices:

Split the table into two tables:
Table1
Id int (PK)
Text1 varchar(8000)

Table2
Id int (PK and FK to Table1)
Text2 varchar(8000)

Use the text data type:
The text data type stores the data in separate data pages which can span multiple pages. In the row, you simply store a pointer to the first data page. You can optimize the table so that the text type is stored inline with the row so long as it doesn't exceed a certain size.

|||Only had a couple of pages that were over the 8060 limit and using two tables works perfect. Thanks for the advice.|||

Glad I could help. You can always throw an updatable view overtop of the two tables so that they appear as one table to queries. Just write a view that INNER JOINs the two tables and then write INSTEAD OF INSERT, UPDATE, and DELETE triggers.

save sql SUM() into a variable

I'm just wondering if we can save sql aggregate functions into a variable.

I have this query:

select company, dept, sum(pers) as pers1, sum(amount) as amount1,
sum(amount)/sum(pers) as wage
from xxtestsum
group by company, dept

instead of calling sum(amount) and sum(pers) again in "sum(amount)/sum(pers) as wage",
I would like to save them in some kinda variable in the select clause so it will save process time.

Sorry but im new to sql programming. So thx for your understanding.

If you are worried about a performance loss in that sql sentence because of the repeating sum, you shoudn't be. Sql Server's query optimized will automatically cache the results of each aggregate function, so the second call to sum(amount) will actually not force the sum aggregate to run again. That is, provided both sum aggregates are being affected by the very same group by clause.

Anyway, if you still want to get those values into a variable and you are using an sqldatasource (this forum is for that...), then you should know that you can call the "Select" method on the datasource manually. This will return you an IEnumerable object which, depending on how the datasource is configured, will either be a DataView or a DataReader. Using any of those, you can extract the full result of your query and do with it as you wish.

|||

Thanks for the clarification

Sample output

hi, i want to know how to do that:

table: costtypes

fields : typecode

values: amount, vat, duty,

table bills:

fields: billid, costtype, amount

value

bi1 amount 1000

bi1 vat 10

bi2 amount 200

i need to create a view that will display amount, vat, duty as fields. note that these are values in the table.

so that i will have

billid amount vat duty

bi1 1000 10 0

bi2 200 0 0

thanks

-- Create #Bills table and values

Create Table #CostTypes (TypeCode varchar(10))

Go

Insert Into #CostTypes

Select 'Amount'

Union

Select 'Vat'

Union

Select 'Duty'

Go

-- Create #Bills table and values

Create Table #Bills (BillId int, CostType varchar(10), value int)

Go

Insert Into #Bills

Select 1, 'Amount', 1000

Union

Select 1, 'Vat', 10

Union

Select 2, 'Amount', 200

Go

-- Create view (you could use this code in a table function)

Declare @.fieldname varchar(10)

Declare @.stmt varchar(4000)

Declare Fields Cursor For Select TypeCode From #CostTypes

Set @.stmt = 'Select Distinct BillId'

Open Fields

Fetch Next From Fields Into @.fieldname

While @.@.Fetch_Status = 0 Begin

Set @.stmt = @.stmt + ', Value * Case When CostType = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname

Fetch Next From Fields Into @.fieldname

End

Close Fields

Deallocate Fields

Set @.stmt = @.stmt + ' From #Bills'

Exec(@.stmt)

I hope it's useful

Giulio

|||Thats too good . can you explain whats happenin in the code though . only the fetch etc part|||

Declare @.fieldname varchar(10)

Declare @.stmt varchar(4000)

Declare Fields Cursor For Select Amounttype From Amounttypes

Set @.stmt = 'Select Distinct BillId'

Open Fields

Fetch Next From Fields Into @.fieldname

While @.@.Fetch_Status = 0 Begin

Set @.stmt = @.stmt + ', amountfc * Case When Amounttype = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname

Fetch Next From Fields Into @.fieldname

End

Close Fields

Deallocate Fields

Set @.stmt = @.stmt + ' From Bill'

Exec(@.stmt)

|||

billid amount vat duty

699190BD-1410-47A8-AFAD-05EC38FEDBF8 0.00 50.00 0.00
B9B71063-D2AD-4735-8CC0-106335990A93 1.00 0.00 0.00
48A82C3C-CF64-4202-B6FA-5DD5A3D32D3C 0.00 0.00 210.00

|||

Sorry, I forgot table function definition requires you know returned table structure.

You could create a scalar function to get SQL Select statement and use this to get data...

Bye,

Giulio

|||

Declare @.fieldname varchar(10) -- variable used to get field value from AmountTypes table and use it as field name in your view

Declare @.stmt varchar(4000) -- variable that contains sql select statement to get data

Declare Fields Cursor For Select Amounttype From Amounttypes -- cursor to scan AmountTypes records and get view fields list

Set @.stmt = 'Select Distinct BillId' -- initialize @.stmt.

Open Fields -- Open Fields cursor

Fetch Next From Fields Into @.fieldname -- get first record

While @.@.Fetch_Status = 0 Begin -- while reading AmountTypes...

Set @.stmt = @.stmt + ', amountfc * Case When Amounttype = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname -- ...add field to select fields list

Fetch Next From Fields Into @.fieldname -- read next record

End

Close Fields -- close Fields cursor

Deallocate Fields -- remove Fields cursor

Set @.stmt = @.stmt + ' From Bill' -- add FROM clause to select statement

Exec(@.stmt) -- execute select

At the end of code below, your select statement should be something like

Select Distinct BillId, amountfc * Case When AmountType = 'Amount' Then 1 Else 0 End As Amount, amountfc * Case When AmountType = 'Vat' Then 1 Else 0 End As Vat, amountfc * Case When AmountType = 'Duty' Then 1 Else 0 End As Duty From Bill

Simply I read AmountTypes to get fields list. Since you have AmountType in Bill as value, I multiply amountfc by 1 if AmountType is equal to current cursor's AmountType or by 0 else.

Ok?

|||

ca101 AD2574DB Estimated 0.00 0.00 2100.00
ca101 AD2574DB Estimated 3000.00 0.00 0.00
ca101 7BB1C98B Cheque 0.00 1.00 0.00
ca101 18ACADF5 Cheque 0.00 0.00 500.00
ca101 18ACADF5 Cheque 0.00 1100.00 0.00
nmh500 AD2574DB Estimated 0.00 0.00 10.00
nmh500 AD2574DB Estimated 2000.00 0.00 0.00

okay if u see the rows., those in same colour should have been on the same row, so

ca101 AD2574DB Estimated 3000.00 0.00 2100.00

code i used follows;

Declare @.fieldname varchar(10)

Declare @.stmt varchar(4000)

Declare Fields Cursor For Select Amounttype From Amounttypes

Set @.stmt = 'Select pono, myid, billtype'

Open Fields

Fetch Next From Fields Into @.fieldname

While @.@.Fetch_Status = 0 Begin

Set @.stmt = @.stmt + ', amountfc * Case When Amounttype = ''' + @.fieldname + ''' Then 1 Else 0 End As ' + @.fieldname

Fetch Next From Fields Into @.fieldname

End

Close Fields

Deallocate Fields

Set @.stmt = @.stmt + ' From multiplebillsviewall'

Exec(@.stmt)

|||

sql never got so interesting in my life.

i know mere basics u know thanks millions.

|||

Sorry, I forgot it...

Set @.stmt = @.stmt + ', Sum(Value * Case When CostType = ''' + @.fieldname + ''' Then 1 Else 0 End) As ' + @.fieldname

and

Set @.stmt = @.stmt + ' From #Bills Group By BillId '

it should be ok...

|||

ca101 AD2574DB-69C0-496F-AD82-67B87AEF2052 Estimated 3000.00 0.00 2100.00
ca101 7BB1C98B-923B-471D-AC86-9F331671F789 Cheque 0.00 1.00 0.00
ca101 18ACADF5-E7D0-4B97-B79D-E266F07C804D Cheque 0.00 1100.00 500.00
nmh500 AD2574DB-69C0-496F-AD82-67B87AEF2052 Estimated 2000.00 0.00 10.00

lovely bra.

thanks.

by the way how do i create a view out of this now?

i will simply use the view in .net and display .

|||

okay no problemo i created a stored procedure.

views cannot contain declare, set etc..

but if the possibility that i can create a view out of this exists . let me know.

shot bra SQL ROCKS

|||

I don't think this possibility exists...

|||so whats the next proposal .. i only need to be able to bring the data to my application in .net and display it in a datagridview.|||

you could simply execute a select statement got by calling a scalar function that returns it...

Create Function dbo.xGetAmountSelectStmt()
Returns Varchar(4000)
As Begin
Declare @.fieldname varchar(10)
Declare @.stmt varchar(4000)
Declare Fields Cursor For Select AmountType From AmountTypes

Set @.stmt = 'Select BillId'

Open Fields
Fetch Next From Fields Into @.fieldname

While @.@.Fetch_Status = 0 Begin
Set @.stmt = @.stmt + ', Sum(Value * Case When AmountType = ''' + @.fieldname + ''' Then 1 Else 0 End) As ' + @.fieldname
Fetch Next From Fields Into @.fieldname
End

Close Fields
Deallocate Fields

Set @.stmt = @.stmt + ' From Bills Group By BillId '
Return(@.stmt)
End

When you have to get data, get below function return value and use it as select statement...

If you need to inject fields, join, where etc you could may simply modify last Set @.stmt as

Set @.stmt = @.stmt + '/*Select*/ From Bills /*Joins*/ /*Where*/ Group By BillId /*Group*/ /*Order*/'

and replace these optional pseudo-tags as your needs

/*Select*/ -> Other fields to get using joins or as static values beginning with ","

/*Joins*/ -> Table joins

/*Where*/ -> Where clause

/*Group*/ -> Other fields used in Group By beginning with ","

/*Order*/ -> Order By clause

Since they are delimited by /*..*/ if you don't need to use tags below, you may leave they as are

I think this could be better (more readable, simple and clean) than execute all every time... And more, if you have to change anything in function code, you only have to modify server-side function...