Determine the size of an Image datatype

Is there any method to find out the size of an image datatype currently in the database. For example I have 2 tables that contain the same image, but the archive table is not displaying the same as the main. I am thinking the image file was corrupted somehow but don't know how to check this out.
Any ideas?DataLength() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_4ep4.asp)

-PatP

determine if value in field is number

I have a varchar field in a table that could contain values such as 123,
345, ABC, or CDF
I want to do a select statement on the table to return records where this
field could be number (123, 345) and exclude those that are not (ABC, CDF).
Is there an IsNum function or something like that?
hi matt,
make use of isnumeric function, which returns 1 for valid numeric value.
ex:
select *
from
<table>
where isnumeric(col) = 1
Vishal Parkar
vgparkar@.yahoo.co.in

Detecting Carriage Returns in a column

Can anyone provide me with some SQL that will identify rows from a table where a varchar column named "Notes" contain Carriage Returns?

I know that with report writer SQR I can translate CR's to white space but I do not know of any Sybase function that will allow me to do the same, any ideas on this well would be appreciated.For Microsoft SQL Server, I'd use CharIndex. For Sybase, it would depend on which of the Sybase servers you are using. Different Sybase engines have different string handling syntaxes.

-PatP|||Thanks Pat. One other dumb question is how is a Carriage Return represented in SQL? char[13]?? THanks again for your help, I used to know this but I'm a little rusty.|||That depends on what you mean by a carriage return. ;)

The problem is complex, because different operating systems store the "line end" marker differently, and because different SQL implementations have different ways of dealing with character expressions.

In Microsoft SQL Server, you represent a lone carriage return character asChar(13)...but, based on your previous question I think you really want to find a "line end" instead of a carriage return character. If you want to find a "line end" as used in Microsoft SQL Server, then you want:Char(13) + Char(10)

-PatP

Design question related to Grouping

Hello,

I am using SQL 2005 Reporting Services to create a user status report. Each page of the report will contain information about 1 user.

On a single page, there are 4 different views of the data (pulling from different tables). Ideally, I would like to group the data (by user for each page, then by category for each of the 4 views on the page). I grouped by user, and the separate pages came out fine. But attempting to group by category, I found that the grouping applies to all the data...and I only want to group a subset of the data.

Is this possible? Will I need to nest select statements to make it happen? Is there a better way to approach this?

Go easy on me...I'm new to this.

Thanks,
Davidi think what your looking for is an outer join.
select * from table1 1
left outer join table2 2 on 1.column = 2.column

Design advice needed

I have several dimensions that contain useful information to be used in calculations. For example, if I had a product dimension that included Cost, but my fact table was at a transaction level, I would still like to compute "Average Cost" at various levels within the cube. Cost per transaction would be the total cost of all products (or a subset of selected products) within a specific time frame, which would allow me to total the # of transactions (from the fact table) divided by the total costs of all products within the same selection (by totaling the cost value contained at the product dimension level).

This seems like a very common scenario, but seems to elude me how to model and create the calculations. I can't, for example, store the product cost in the fact table because it is at a lower level of granularity and would overstate the cost. The obvious place to have the cost value would be within the product dimension, but in what form? Member property? I have tried this approach, but get inconsistent or bad results when I try to filter along other dimensions, or multiselect many products.

Example:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[Product Cost] AS Val(Product.CurrentMember.Properties("Product Cost")))

This "seems" to get me partways, by displaying the cost along the product dimension, but doesn't behave like a regular measure in that it doesn't automatically aggregate along other dimensions.

Am I missing something obvious here?

Thanks

-Kory

Dear Friend,

Be carefuly in the design of your project. I need to know your project requirements, but for example, I'm agree that the product is a Dimension but without the cost. The cost of the product will be the same for all the time? never be changed?

And what hapen if you change the product cost this month? The report for this month is OK but the reports for past months? will be based in the cost that you updated today? So, you must consider this cost as FACT relating to the Product dimension with the attributes for example (Name and Description).

In your example, on thing you must see is to create named calculations inside the datasource view in spite of calculated member inside cube.

I hope I helped you!!

regards!!

|||

Thanks for the reply. My example was simplistic, but if the product cost changed, and we wanted to keep history of changes, the dimension table would be a type II slowly-changing dimension. So a new record with a new surrogate key would be inserted in the dimension table with the new product cost.

By putting the product cost in the fact table, even as a named calculation in the DSV, you would be duplicating the cost across many fact records, and any calculation using this fact amount would be overstated.

An alternative would be to create a fact table at the product level of granularity, but then I can see ending up with many, many fact tables out of what appear to be dimensions.

I'm still not convinced there is a good way to handle this- I just haven't found it yet.

-Kory

|||

Korys,

Check my post on my blog and tell me waht you think.

http://pedrocgd.blogspot.com/2007/07/ssas-slowly-changing-values.html

Are you more convinced?

regards!

|||

Hello KoryS. I would recommend you to have the product cost as a measure in the fact table.

This means that you would have to solve this problem in the ETL-process with Integration Services.

It is a lot more complicated to solve this problem with MDX in a cube by using a product cost on each product in the dimension table.

If your leaf level in the fact table is the transaction level that should not be a hard ETL-problem to solve.

HTH

Thomas Ivarsson

|||

I'm agree wit you Thomas.

I did not understood very well what's the problem, because I'm very busy, but i'm with you!

regards to both!!!