Dimension, CurrentMember and a hierarchy

Greetings -

I am writing some MDX for a RS report, and seem to have hit a bit of a wall. The business folks keep changing their minds on what hierarchy they want to report from, so I would like to have it as a parameter to the report. Problem is that I don't know if there is a way to get the CurrentMember.Properties('Caption') without explicitly declaring the hierarchy. My MDX:

With Set [MySet] as

StrToSet(@.HierarchyAndMember)

Set [DateSet] as

StrToSet(@.Date, Constrained)

Member [Measures].[Display] as

[Dimension].[Hierarchy Name].CurrentMember.Properties('Caption')

I would like to do StrToSet(@.HierarchyMember).CurrentMember.Properties('caption') here

Select { [measures].[created invoices], [measures].[display] } on 0,

{ [MySet] * [DateSet] } on 1

from [cube]

I have tried several different things, and this is the closest I have found. Placing the StrToSet in either it's own set or directly in the MDX doesn't affect the results. Is there any way to get the current member of the dimension without hardcoding the hierarchy? Does anyone have any creative solutions, or at least any directions to go?

Thanks in advance,

John Hennesey

It turns out that the RS designer is a pain in the sense it wants you to explicitly select a hierarchy & member so it knows what fields are returned from MDX. It doesn't check the validity of the items during runtime, it passes the values into MDX and lets AS take over. I have the ability to get done exactly what I am looking to do.

Thanks though,

John

sql

Dimension Properties and Dimension Attributes not found

I am writing an MDX query and I want to include dimension attributes as part of the DIMENSION PROPERTIES section of the query.

SELECT

{ Measures.SalesAmount, Measures.ShipQuantity } on columns,

{ (Items.ItemNumber.Allmembers,

Time.Month.AllMembers) }

DIMENSION PROPERTIES

Items.Description, Items.Category,

MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

From Sales

My problem is that while I can include Items.Description in the dimension properties list, I cannot include Items.Category in the list. The query parser gives an error,

The [Items].[Category] dimension attribute was not found.

I look at the definition of the dimension, and I see no difference between the two attributes in the schema.

What should I be looking at to see if there are differences between these two attributes? Or am I doing something else wrong?

Mike

I'm guessing that Items.ItemNumber is an attribute hierarchy and so Items.ItemNumber.AllMembers includes the All member plus the members of the attribute. Since the server will only return dimension properties that apply to all the members returned for that dimension, having members from different levels can unintentionnally restrict the dimension properties. Try changing the mdx to remove the all member (probably something like Items.ItemNumber.ItemNumer.AllMembers).

Another possibility is that Items.Category is a valid member property name, but not one associated with the level you are querying. Check for a similarly named member property as its easy to confuse member properties for attribute hierarchies and user defined hierarchies. In this case the name you want might be something like "ItemNumber.Category" since it probably only applies to the ItemNumber level.

|||

I'm not sure I am understanding your answer.

Items.ItemNumber is an attribute heirarchy, and so (it appears) are Series, Family, Category, and a bunch of others.

There are two user-defined hierarchies.

I don't understand when to use Items.Description and Items.Description.Description - both work in the DIMENSION properties statement - the first is the hierarchy, the second is the level. Another items attribute hierarchy I have is [Product Life Cycle], and neither Items.[Product Life Cycle] nor Items.[Product Life Cycle].[Product Life Cycle] works in the dimension properties statement, tho they appear identical in the dimension editor.

Here is the Item Dimension in the BIDS dimension editor:

|||

Now I know why Items.Description works in my DIMENSION PROPERTIES statement.

It is referring to the built-in property of the dimension, the Description property that I see in the Dimension editor property window when I select the top node in the tree, the Items dimension node itself. It also works for Items.ID and Items.Name built-in properties.

Still, now the question remains, why can't I use Items.Category or Items.Whatever in my dimension properties statement?

|||

If Items.ItemNumber.Allmembers returns an all member, then that is likely your problem. Try the MDX satement with a single specific member.

Here's a re-wording of basically what I wrote in my first reply that might help.

When including member properties in MDX queries using the DIMENSION PROPERTIES syntax, here are two common causes for confusion.

First, the server will only return member properties that apply to all members requested in a hierarchy. This means that if you request members from multiple levels, you will only get member properties which you have requested and which exist for all of the levels from which you have requested members.So if you request “[MyDim].[MyHier].Members” you will only get member properties applying to all levels.Instead, you should specifically request just members of a single level using something like “[MyDim].[MyHier].[MyLevel].Members”.Note that most attributes hierarchies contain two levels – the all level and the attribute level.This means you can get different results from [MyDim].[MyAttribute].Members and [MyDim].[MyAttribute].[MyAttribute].Members.

A second source of confusion is the fact that you can have similarly named member property for attribute hierarchy levels and user defined hierarchy levels based on the same attributes.This is true even if the attribute hierarchy is not visible.As a result, you may be unintentionally using a perfectly valid member property name that doesn’t correspond to the level of the members you are requesting.This results in no error, but you don’t get back any member properties.

Difficulty in writing a DateTime to a table

I'm developing an application in VB 2005 Express using SQL 2005 Express. I need to put a timestamp into my table each time I create a row...

The following is a snippet...

Dim DDate As [SqlDateTime] = Now()

Dim TheQuery As String = "INSERT INTO Groups (PC_Name_Stamp, OperatorNo, Group_Type, Date_Time) VALUES ('Development', '2', 'Test',' " & DDate & " ')"

Which won't work as I am attempting to concatinate a SqlDateTime into a string.

My best guess is that I need to somehow to use a DEFAULT value in the table that persists so each time a row is created the datetime it was created is saved with the row, rather than being re-calculated each time the table is opened. There are probably several other ways of doing it and this may not be the easiest.

I'm not a programmer, just an Engineer, so I can only read Help for 5 minutes at a time.

Hi Ian,

What is the data type of the field named Date_Time? What is the error you are getting?

I would suggest removing the quotes from your query and just letting SQL handle the data type change from SqlDateTime into what ever Date_Time is defined as. If it is a string, the conversion will likely happen automatically. (At least it does in T-SQL.)

If that doesn't work, consider using a type conversion function rather than just wraping the value in quotes. You should be able to find information about data type conversion functions in the VB help documentations.

One final consideration, if you only need to determine if a row has been updated and the actual time is not really important, you might consider the timestamp data type. This data type automatically updates when a row is added or changed, but it stores a relative time, no the time associated with a clock.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||

Mike,

In order: "datetime"

The error I get is a VB pre-complier error warning that I am attempting to mix "String" with "SqlDateTime".

Removing the quotes does not help as the connection to the database is made with the following command:

Dim TheCommand As SqlCommand = New SqlCommand(TheQuery, TheConnection)

Where both "TheQuery" and "TheConnection" are strings.

Unfortunately I need to know the time and date, this will be used for locating information later.

My most recent attempts have used :

Dim TheQuery As String = "INSERT INTO Groups (PC_Name_Stamp, OperatorNo, Group_Type, Date_Time) VALUES ('Development', '2', 'Test',' GETDATE() ')"

Which throws "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column." on execution (of the VB program).

I have attempted to set the default on the Date_Time column in "Groups" to be calculated as Getdate() and persistent, but then I get the following error: "Computed column 'DateTime' in table 'Tmp_Groups' cannot be persisted because the column is non-deterministic." I note that a calculated column in the table has no "format", which makes sense if it is not to be saved.

I am assuming that this is the correct forum, I think that the problem is related to SQL rather than .NET or VB.

I will be off the air now for 54 hours due to the weekend.

Thanks for your time Mike.

Ian

|||

It's interesting that you're getting an error specifying a timestamp field. Timestamp is different than datetime, you should make sure that you really have a datetime field.

That said, you're still trying to create string values by putting quotes around them. That isn't how you do it, you have to conver non-string values into strings using a conversion function, in this case CStr(). I managed to write an insert from VB by converting the output of Now() to a string and passing that in my query. I've povided the table definition and VB code below for you to examine.

Table Def-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Shippers](

[ShipperID] [int] IDENTITY(1,1) NOT NULL,

[CompanyName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Phone] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[tsTime] [datetime] NULL,

[sTime] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [aaaaaShippers_PK] PRIMARY KEY NONCLUSTERED

(

[ShipperID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

-- End Table -

- Code--

Sub Main()

Dim cnn As New SqlConnection

cnn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=NorthwindSQL_Upsize;Integrated Security=True"

cnn.Open()

Dim cmd As New SqlCommand

cmd.CommandText = "INSERT INTO Shippers (CompanyName, Phone, tsTime) VALUES('VB Shippers','555-1212','" & CStr(Now()) & "')"

cmd.Connection = cnn

cmd.ExecuteNonQuery()

End Sub

- End Code --

|||

Mike,

A very astute observation, I do have an issue with the column format.

A search of my harddisk shows a total of 8 copies of this database, two I have as backups, one is a copy I was importing data into using Management Studio, the rest have uncertain pedigree, (some I no doubt created in error).

I long for the days of SQL Server 2000 when I only kept two copies of the database, (one live & one copy). Having a cut-back latest version makes it very tempting to develop & use the latest, but I wonder if SQL Server Express has been emasculated to the extent that it is no longer worthy of the title "Database". Yes, the mistake was mine, I think it was an easier mistake to make due to the "over enthusiastic feature removal".

Thank you for you help Mike.

Ian

sql

differnce between 2 aggregate columns

Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
Please post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegr oups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegr oups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||If I cut and paste those statements, they don't work.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegr oups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:

>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP
|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?

differnce between 2 aggregate columns

Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:

>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?

differnce between 2 aggregate columns

Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
--
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?

different datatypes with UPDATE or INSERT

I'm writing an SP that retrieves data on a linked SQL server, and
selectively updates or inserts like-named rows on the local server. Two
columns on the remote server are Mileage varchar(25) and Price varchar(25),
whereas on the local server the datatypes are INT and MONEY.
As an example of what's needed for 3 sample rows:
Mileage (remote) = 23,456; 'Call for Details'; 56,789
Mileage (local) = 23,456; NULL; 56,789
Price (remote) = $9,995.00; 'Call Us'; $14,900.00
Price (local) = $9,995.00; 'NULL'; $14,900.00
How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
other words, if there are 'non-int' or 'non-money' values coming over from
the remote server, will SQL Server automatically convert these 'invalid'
values to NULL, or do I need to handle it somehow, maybe via a CASE
expression in the UPDATE or INSERT INTO, or...?
Thanks.
Message posted via http://www.webservertalk.comSQL Server will attempt a cast from a character field to a numeric. If it
fails, it will throw an error. A better option would be casting yourself and
logging any failures, including parameters that caused the failure. Humans
can then read the log and correct the data.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"The Gekkster via webservertalk.com" wrote:

> I'm writing an SP that retrieves data on a linked SQL server, and
> selectively updates or inserts like-named rows on the local server. Two
> columns on the remote server are Mileage varchar(25) and Price varchar(25)
,
> whereas on the local server the datatypes are INT and MONEY.
> As an example of what's needed for 3 sample rows:
> Mileage (remote) = 23,456; 'Call for Details'; 56,789
> Mileage (local) = 23,456; NULL; 56,789
> Price (remote) = $9,995.00; 'Call Us'; $14,900.00
> Price (local) = $9,995.00; 'NULL'; $14,900.00
> How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
> other words, if there are 'non-int' or 'non-money' values coming over from
> the remote server, will SQL Server automatically convert these 'invalid'
> values to NULL, or do I need to handle it somehow, maybe via a CASE
> expression in the UPDATE or INSERT INTO, or...?
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||It will try to automatically convert the data from varchar to integer.
However, if any value in the insert is invalid, it will crash. A good way
to handle this is using an Instead Of trigger. Instead of just inserting
the data, you run a check on the data to see if it is valid. Bad data goes
into an exception table, good into the real table.
There are quite a few different routines around to validate that a value is
a reasonable numeric value, but you will likely not want to use isNumeric as
it is very liberal. Search on groups.google.com for isNumeric and you will
see that is covered quite often.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"The Gekkster via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:852fc5294d584b37b647b85f7d799e95@.SQ
webservertalk.com...
> I'm writing an SP that retrieves data on a linked SQL server, and
> selectively updates or inserts like-named rows on the local server. Two
> columns on the remote server are Mileage varchar(25) and Price
> varchar(25),
> whereas on the local server the datatypes are INT and MONEY.
> As an example of what's needed for 3 sample rows:
> Mileage (remote) = 23,456; 'Call for Details'; 56,789
> Mileage (local) = 23,456; NULL; 56,789
> Price (remote) = $9,995.00; 'Call Us'; $14,900.00
> Price (local) = $9,995.00; 'NULL'; $14,900.00
> How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
> other words, if there are 'non-int' or 'non-money' values coming over from
> the remote server, will SQL Server automatically convert these 'invalid'
> values to NULL, or do I need to handle it somehow, maybe via a CASE
> expression in the UPDATE or INSERT INTO, or...?
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||You need to test the values in the update, and update the local table to nul
l
when the remote value is not numeric... (This should handle 99.99% of teh
cases
Update <Table> Set
LocalCol = Case IsNumeric(RemoteCol)
When 1 Then Cast (RemoteCol as Integer)
Else Null End
From ...
"The Gekkster via webservertalk.com" wrote:

> I'm writing an SP that retrieves data on a linked SQL server, and
> selectively updates or inserts like-named rows on the local server. Two
> columns on the remote server are Mileage varchar(25) and Price varchar(25)
,
> whereas on the local server the datatypes are INT and MONEY.
> As an example of what's needed for 3 sample rows:
> Mileage (remote) = 23,456; 'Call for Details'; 56,789
> Mileage (local) = 23,456; NULL; 56,789
> Price (remote) = $9,995.00; 'Call Us'; $14,900.00
> Price (local) = $9,995.00; 'NULL'; $14,900.00
> How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
> other words, if there are 'non-int' or 'non-money' values coming over from
> the remote server, will SQL Server automatically convert these 'invalid'
> values to NULL, or do I need to handle it somehow, maybe via a CASE
> expression in the UPDATE or INSERT INTO, or...?
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||That's the same conclusion I came to. Even though IsNumeric may not be
'ideal' it seems to serve the purpose here well.
Thanks to all for the input.
Message posted via http://www.webservertalk.com

Different behaviors when writing Database file into the SD Memory card.

Hi All,

I have seen some different behaviors when I am writing SQL Server compact edition Database file into SD Memory card or any user removable storage media.

The following Steps for reproducing these behaviors.

1. Set your database location is in the SD Memory card.

2. Create a database and establish session.

3. Writing first record into the database is done

4. Remove your SD card manually from the system

5. Try to write a Second record into the database

6. You will get the error from this "SqlCeWriteRecordProps" API call - This is fine.

7. Put your SD card back into the system.

8. Writing Second record again into the database is done - This is fine.

So far the behaviors are good after that

9. Remove your SD card manually from the system again

10. Try to write a Third record into the database

11. You will suppose to get the error from this "SqlCeWriteRecordProps" API call but you won’t get that error – I don’t know why?

12. Then Try to write a Forth record into the database

13. You won't get any error from the API call "SqlCeWriteRecordProps" and return status is success.

14. Now put your SD card back into the system.

15. Then write Fifth record but this time its writing record Third, Fourth and Fifth into the database.

Note:

I am clearing (means Free) my record structure everything after calling this function "SqlCeWriteRecordProps". So I don’t know why its wring Third, fourth and fifth record into the database.

Please Let me know your feedback.

Thanks,

Rajendran

This must be related to at what point of time the changes are flushed to SD card. It could be that during 2nd record write there is an attempt to flush the changes and the disk missing error. The next attempt to write records happens only during 5th write.

By default the flush interval is set to 10 seconds. This can be varied through connection string.

[If your question is answered, please mark it as answered]

-Thanks