memo data type Import error while importing data from Access file into SQl Server 2005

I have one column in SQL Server 2005 of data type VARCHAR(4000).

I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column

data type converted into the memo type in the Access database.

now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.

Could you please let me know what is the reason?

I know that memo data type does not supported into the SQl Server 2005.

I am with SQL Server 2005 Standard Edition with SP2.

Please help me to understans this issue correctly?

If the wizard error says Unicode then change your data type in SQL Server 2005 to Nvarchar(max) because memo can grow above 4000 which is the limit for Nvarchar data type without the max key word. Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms186939.aspx

|||

Thanks for the reply.

However why SQL Server 2000 is not throwing this error? MSSQL 2000 is also contains the nvarchar data type.

|||That could the related to 2005 data types definition is more strict than 2000.

Member Property length limitation

I have a member property in a shared dimension MSAS 2000. The length of column from which this member property fetches value is nvarchar(2000). But the OLAP cube is reading only 255 characters ONLY. Is this a common limitation ? If yes, what is the alternative to increase its length to grater than 255 ?

Any help will be greatly appreciated. Thanks.

If you go into the dimension editor, expand the member properties and click on the property and have a look at the advance properties - what is the data size set to? I am guessing that it may be set to 255 and you should be able to set it larger, but it's been a while since I used AS2k.|||I had checked it. It has LongWVarchar(2000).|||

I just fired up a copy of AS2k and did a quick test and got the same behaviour - member properties were truncated to 255 characters. At a guess I would say that they are only using a byte to store the length of the property, so there would be no way of working around this. Also AS2k stores all the dimension members and properties in RAM, so having very large member properties can put a lot of pressure on the RAM usage.

The only thing I could think of is to store an ID that relates back to the record in question and then use an action or something similar to link the two pieces of information together. If you setup a linked server in SQL Server you could write stored procedures that would query AS2k and you could join the cube data with a relational table before sending the results back to the client.

Median Function

I am trying to write a function to give me a median value. I have a matrix.
There is a column group/text box called loanbalance. Displayed in that
field is the SUM of all loan balances for the month.
I have another textbox called "Median". This textbox I want to display the
median value for the values in the "Loanbalance" field/textbox.
I don't have detail rows showing in the report, I am grouping.
I went out to the menu and selected: report, report properties, code, and
tried to write a function MEDIAN(reportitems!loanbalance.value), end
function. I know pretty basic, but I am not an advanced user.
Then in the "median" textbox, expression I have = code.MEDIAN(ReportItems!loanbalance.value).
It does not work. I don't get errors, I just don't get anything back for a
value.
Could someone help me out with this? Am I going about this the right way?
Thanks,Susan,
I have a report where I needed to get the Median time for documents
processed and ended up writing a routine from within a stored procedure to
perform this operation. As of yet I have not found a way to do it from w/in
RS - if anybody out there knows of way to accomplish this your help would be
appreciated.
Basically what I did was to get the row number corresponding to the number
of documents processed and divide that number by 2. I then took the
resulting number as parameter for my where clause-
Ex:
Set @.RowNumber = @.RowNumber / 2
Select @.MedianTime = DocTime From @.ReportDataTable
Where RowNumber = @.RowNumber
I know it's rudimentary but it does work.
Hope this helps.
Bill Youngman
Anexinet, Inc.
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:F2635C55-394A-44FD-A8C0-934C42260417@.microsoft.com...
> I am trying to write a function to give me a median value. I have a
matrix.
> There is a column group/text box called loanbalance. Displayed in that
> field is the SUM of all loan balances for the month.
> I have another textbox called "Median". This textbox I want to display
the
> median value for the values in the "Loanbalance" field/textbox.
> I don't have detail rows showing in the report, I am grouping.
> I went out to the menu and selected: report, report properties, code, and
> tried to write a function MEDIAN(reportitems!loanbalance.value), end
> function. I know pretty basic, but I am not an advanced user.
> Then in the "median" textbox, expression I have => code.MEDIAN(ReportItems!loanbalance.value).
> It does not work. I don't get errors, I just don't get anything back for
a
> value.
> Could someone help me out with this? Am I going about this the right way?
> Thanks,
>

Median

How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.
From Northwind:
select
Median = avg (Quantity)
from
(
select
Quantity = min (Quantity) -- first value ...
from
(
select top 50 percent -- ... in upper half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity desc
) x
union all
select
Quantity = max (Quantity) -- last value ...
from
(
select top 50 percent -- ... in lower half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity asc
) x
) y
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O1vtO0OrFHA.1984@.tk2msftngp13.phx.gbl...
How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.

Median

How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.From Northwind:
select
Median = avg (Quantity)
from
(
select
Quantity = min (Quantity) -- first value ...
from
(
select top 50 percent -- ... in upper half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity desc
) x
union all
select
Quantity = max (Quantity) -- last value ...
from
(
select top 50 percent -- ... in lower half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity asc
) x
) y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:O1vtO0OrFHA.1984@.tk2msftngp13.phx.gbl...
How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.

Median

How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.From Northwind:
select
Median = avg (Quantity)
from
(
select
Quantity = min (Quantity) -- first value ...
from
(
select top 50 percent -- ... in upper half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity desc
) x
union all
select
Quantity = max (Quantity) -- last value ...
from
(
select top 50 percent -- ... in lower half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity asc
) x
) y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O1vtO0OrFHA.1984@.tk2msftngp13.phx.gbl...
How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.sql

Measures to columns in Excel

Dear anyone

I am transfering the information from the cube to Excel.

My wizard places all measures in the pivot table to one column. I am using Excel 2003.

I would like to place the measures to different columns to get a result which is easy to read and copy.

Could anyone advice me how this can be done ?

Thanks !

Matti

You just have to drag & drop the field "Data" from the rows to the columns.|||

Thanks Rmi

Your advice sounds simple, I expected the same.

The problem is that but my Add-button is inhibited when

trying to do that. Could this be caused by cube definitions ?

Matti

|||

If I try to use drag and drop, I get an error from Excel. Translation of error message is something like this: "The field You are transfering can't be placed in this area of the pivot table".

Matti

|||I don't think it comes from your cube. I am sorry if my answer seems a little too simple, but are you sure you drag & drop the field on the header of the columns (not the content of the table). In fact you can put numeric values in the content of table, but dimension attributes need to be in row headers or column headers or filter.|||

I want to summarize, that I can easily drag and drop all dimensions to either lines or to columns Measures I can only place to the same column.

Matti

|||Yes, of course, sorry I got lost... I just wanted you to drag and drop the field "data" which represents the Measures on the header of the column. And if it doesn't work, then I will let someone else help you because I don't have another idea...|||

Thanks for kind help Rèmi !

This is a question of the layout of the report and the form of the paper.

What I get is:

DimA1 DimA2

DimB1 MV1A1B1 MV1A2B1

MV2A1B1 MV2A2B1

DimB2 MV1A1B2 MV1A2B2

MV2A1B2 MV2A2B2

What I want to get:

DimA1 DimA2

DimB1 MV1A1B1 MV2A1B1 MV1A2B1 MV2A2B1

DimB2 MV1A1B2 MV2A1B2 MV1A2B2 MV2A2B2

.

.

.

Matti

measure counting distinct values from a field

How do I count the amount of distinct values from a column in a fact table as a calculated measure

Assume the primary key of my fact table is a composite of three columns (a,b,c).

How can I code a calculated measure to count every distinct value of column a, not the amount of rows in my fact table.

Any advise help would be appreciated!

All you need to do is change the measure type from Count to Distinct Count ... make sure you select Column A as your key column....

MDX: Null value to replace column in Query

I have an MDX query that is not doing what I want it to do.

Currently, I have an application that expects to recieve a certain
number of columns in order to make a chart.In SQL, if I was not
requesting data for all of the columns I could replace the column name
with a NULL and still recieve the other information with the in the
appropriate format (Except for that column would have all NULL values).
I cannot get this to work in MDX... an example in SQL which does work
follows:

i.e.
Requesting real information
SELECT id as col1, dog as col2 FROM SQL

Replace column with null but recieve same table format
SELECT NULL as col1, dog as col2 FROM SQL

I cannot figure out how to do this when requesting data with MDX in
Analysis Services. I have looked all through "MDX Solutions" and cannot
find the solution :) Although there was tons of great stuff in there.
TONS!

My MDX query that requests real data for all columns and works fine is
as follows:
SELECT {
[Measures].[YN] ,
[Measures].[YD] ,
[Measures].[YV] ,
} ON 0,

NONEMPTY(
{[Product].[p Hier Ty3 Bg 1 1].&[R104],[Product].[p Hier Ty3 Bg 1
1].&[R706]}
*{[Customer].[c Hier Ty3 Bg 1 3].&[Australia]}
*EXCEPT([Date 1].[c_month].[2004_M01]:[Date 1].[c_month].[2004_M03],
[Date 1].[c_month].[ALL])
*EXCEPT([Customer].[c Hier Ty3 Bg 2 2].Members,[Customer].[c Hier Ty3
Bg 2 2].[All])

) ON 1
FROM Mimir04

But if I want an empty column to replace {[Customer].[c Hier Ty3 Bg 1
3].&[Australia]}, my guess at a solution (coming from SQL and being a
newbie at MDX) was to put a null set in place of this dimension call...
{NULL}. Unfortunately, this means that nothing gets returned

What exactly is the solution to returning an empty column?

Could you please clarify whether you'd like empty rows or empty columns?

Or, best, show an example of how you expect the query result to look like (axes contents and cell data)?

Thank you