Access Report/Import Groupings

Hi Folks
Importing Access reports that have grouping are really quite a struggle.
All of the grouping sections are imported as controlled by report parameters.
Am I missing something, this is incredibly painfull with all of the build
errors in VS.NET?
RobI've reported this earlier, but the importation truely 'mangles' Access
Reports that are based on SQL Server sprocs that dynamically create record
sets (in my case dynamic creation of fields related to creating pivot tables
on the fly).
The issue is 'what does SSRS feel that it's important to do' in order to
retrieve a record set associated with a report.
The way to get ALL of this to work is a 3 step process:
1. Import the report from Access, which will give you a working sproc
dataset, but a mangle report import (that contains all kind of parameterized
stuff that SSRS couldn't figure out how to map).
2. Run the sproc standalone (in either Access or SQL Server) and create a
'working table' from the result set. Next, remap your Access query to this
'hard coded' table.
3. Finally, rerun the import on the remapped Access Report. The import is
almost correct (things like page breaks have to be redone and some other
little anomolies).
Delete the second dataset associated with 2nd import and delete the 1st
.rdl. This leaves you with a good dataset from the 1st import and a good
report from the 2nd import. Now just remap the dataset for the .rdl.
This is all because SSRS gets a little TOO carried away with trying to
interpret calls to sprocs (which use things like #tables and dynamically
define columns). This needs to be changed in the report creation and import
features of the product. This is TOO constraining to the point where the
product is making poor import decisions as well as being an absolute 'show
stopper' on new report creation where it uses a sproc.
Rob
"tutor" wrote:
> Hi Folks
> Importing Access reports that have grouping are really quite a struggle.
> All of the grouping sections are imported as controlled by report parameters.
> Am I missing something, this is incredibly painfull with all of the build
> errors in VS.NET?
> Rob

Access managment in SQL

Hi,
I have just imported my old access database into my new
SQL database and have come over a few problems.
All memo fields have been changed to "ntext" fields. This
is no good for me as I have lots of text in the fields.
When I try and change one of the fileds I get this error:
'creek_products' table
- Unable to modify table.
ADO error: Cannot create a row of size 8129 which is
greater than the allowable maximum of 8060.
The statement has been terminated.
I'm only trying to change one field to nchar because then
it will let me have a lenght of 4000.
Can anyone help me with this?
I need to get the max lenght field type working.
Thakns
I hope you got answer to your question but if not:
In SQL Server every row has a limit in size.
Consider when converting, creating tables first and then pouring data into it.
I am still puzzled what's wrong with ntext data type?
"Luca" wrote:

> Hi,
> I have just imported my old access database into my new
> SQL database and have come over a few problems.
> All memo fields have been changed to "ntext" fields. This
> is no good for me as I have lots of text in the fields.
> When I try and change one of the fileds I get this error:
> 'creek_products' table
> - Unable to modify table.
> ADO error: Cannot create a row of size 8129 which is
> greater than the allowable maximum of 8060.
> The statement has been terminated.
> I'm only trying to change one field to nchar because then
> it will let me have a lenght of 4000.
> Can anyone help me with this?
> I need to get the max lenght field type working.
> Thakns
>
|||Luca wrote:
> Hi,
> I have just imported my old access database into my new
> SQL database and have come over a few problems.
> All memo fields have been changed to "ntext" fields. This
> is no good for me as I have lots of text in the fields.
> When I try and change one of the fileds I get this error:
> 'creek_products' table
> - Unable to modify table.
> ADO error: Cannot create a row of size 8129 which is
> greater than the allowable maximum of 8060.
> The statement has been terminated.
> I'm only trying to change one field to nchar because then
> it will let me have a lenght of 4000.
> Can anyone help me with this?
> I need to get the max lenght field type working.
> Thakns
Why is ntext no good for you? If you have lots of text, as you
mentioned, then ntext or text is what you want. ntext is unicode and
requires twice the storage of text. If you don't require unicode
support, use text.
The nchar(4000) requires SQL Server reserve 4000 bytes for each row.
That will give you terrible performance because only a couple rows will
fit on each SQL Server page (same as nvarchar(4000) would do). There's a
limit of about 8060 total bytes stored within a row (excluding the text
in text and ntext columns). Using text or ntext fixes this by only
requiring a 16 byte pointer in the table. The text is stored elsewhere,
on a different filegroup if you prefer.
Can you explain what problem you feel you are having using ntext?
David G.