Precision Problem

Hi,

I can't seem to get a stored procedure to return decimal places. Here are the steps to re-create the problem:

--first create the following procedure
CREATE PROCEDURE test_precision
AS
BEGIN
RETURN 5.2
END

--then run the following code
DECLARE @.x DECIMAL(18,2)
EXECUTE @.x = test_precision
SELECT @.x

I would like this code to return 5.20, but instead it returns 5.00. Any assistance would be greatly appreciated. Thanks.if you are using SQL 2005 that is the problem. Your stored Procedure would work correctly in SQL 2000.

The work around
declare @.x FLOAT(18,2)

give that a try.|||ooops!! allow me to take that back - wrong thought process. sorry.

you need to edit your proc to read 5.20sql

Post Setup Maintenance

Hi all,
I have designed and implimented my 2K pubs and msde subscribers, anonymous
pull.
There isn't much docs on maintenance steps and less on the "Validate all
users." Does that just show validation stats in the history or will it set
the user to reinitialize if something is off or something else.
My goal would be minimal work to catch invalid data each week or day and
make it right? Suggestions ?
Mike
It will show up in the distribution agent history. You can configure it to
send a new snapshot if it fails validation or just to stop.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:69656639-A716-4E15-8857-289050ED6F61@.microsoft.com...
> Hi all,
> I have designed and implimented my 2K pubs and msde subscribers, anonymous
> pull.
> There isn't much docs on maintenance steps and less on the "Validate all
> users." Does that just show validation stats in the history or will it set
> the user to reinitialize if something is off or something else.
> My goal would be minimal work to catch invalid data each week or day and
> make it right? Suggestions ?
>
> --
> Mike
>
|||So it's the same as changing each agent to the validation agent profile.
It seems like the best option is to validate all regularly, say weekly and
have it apply a send the snapshot automatically, to keep it clean. A job to
run on weekends for the main pub.
Does that make good sense?
The other pubs have filter joins which it apparently won't validate
correcly. A subscriber side job to check table counts based on the joins
might work. Any simpler suggestions for filter join health checking?
Thanks !
Mike
If data falls in the woods and nobody is there to see it ...... ?
"Hilary Cotter" wrote:

> It will show up in the distribution agent history. You can configure it to
> send a new snapshot if it fails validation or just to stop.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
> news:69656639-A716-4E15-8857-289050ED6F61@.microsoft.com...
>
>
|||I seldom run validations as I have confidence replication works well.
AFAIK - validations work with join filters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:8DEB0974-6669-4985-A479-7EB5B935E68E@.microsoft.com...[vbcol=seagreen]
> So it's the same as changing each agent to the validation agent profile.
> It seems like the best option is to validate all regularly, say weekly and
> have it apply a send the snapshot automatically, to keep it clean. A job
> to
> run on weekends for the main pub.
> Does that make good sense?
> The other pubs have filter joins which it apparently won't validate
> correcly. A subscriber side job to check table counts based on the joins
> might work. Any simpler suggestions for filter join health checking?
> Thanks !
>
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "Hilary Cotter" wrote:

POSSIBLE STEPS TO TAKE IN BUILDING MY AUTOMATED EXAMINATION PROJEC

Please help...:-)
hi
for something like and automated examination, u need to have a question
bannk with a
answers in a table.
you can also have a table that has user's information, that holds date of
examination
the questions attempted and the answers provided. you can have a check that
says
how many of them were correct.
Randomly picking questions can be handled from the front end.
hope this answers the question. please revert back if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Janmar" wrote:

> Please help...:-)

POSSIBLE STEPS TO TAKE IN BUILDING MY AUTOMATED EXAMINATION PROJEC

Please help...:-)hi
for something like and automated examination, u need to have a question
bannk with a
answers in a table.
you can also have a table that has user's information, that holds date of
examination
the questions attempted and the answers provided. you can have a check that
says
how many of them were correct.
Randomly picking questions can be handled from the front end.
hope this answers the question. please revert back if u have any questions
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Janmar" wrote:
> Please help...:-)|||can you please specify what tables im going to create and the possible
content of each table? that is if its ok with you...thanks:-)

POSSIBLE STEPS TO TAKE IN BUILDING MY AUTOMATED EXAMINATION PROJEC

Please help...:-)hi
for something like and automated examination, u need to have a question
bannk with a
answers in a table.
you can also have a table that has user's information, that holds date of
examination
the questions attempted and the answers provided. you can have a check that
says
how many of them were correct.
Randomly picking questions can be handled from the front end.
hope this answers the question. please revert back if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Janmar" wrote:

> Please help...:-)

Possible bug: Lose members of server role when changing DataDir server property

Hello,

I think I have found a bug in AS and I would like it confirming before I report it. Please could someone try the following steps (don't do this on a production machine):

Connect to your Analysis Server in SSMS with a user that is a member of the server role but NOT the user that installed the software

Right-click on the server in Object Explorer. Select 'Properties...'

On the general tab, change the value of the DataDir property to a different folder (it doesn't matter what)

Click 'OK' on "Analysis Server properties" dialog. You will get a message telling you that the server needs to be restarted

Restart your Analysis Server in Services panel.

Re-Connect to the Analysis Server in Object Explorer again.

Right-click on the server in Object Explorer. Select 'Properties...'

You won't be able to see any server properties and if you click on the 'Security' tab you will not be able to see yourself in the list of server role members. In other words...it seems you have been removed from the server role just by changing the data directory. If you try and do anything like deploy to the server you will get an error message "Either the 'username' user does not have permission to create a new object in 'LNVDMFIISDEV01', or the object does not exist."

It gets stranger...

Log onto the server as the user that originally installed Analysis Services (for this is now the only user that will have server role privelages)

Change the DataDir back to what it was before. Restart AS.

After that all of the server role members will be back where they were before. Everything as normal.

What gives? Can someone from MSFT confirm that this is a bug? If so I will raise it as such on Microsoft Connect.

Also, with this in mind, what should be the proper procedure for changing the data directory?

Regards

Jamie

I'm on SP1 by the way!

Thanks Jamie for catching this.

Please go ahead and file it.
The problem is indeed requires some attention. Maybe books online should explain better what is going on.

Changing Data directory is very big change. You will be loosing all of your data and metadata. More to it, you need to have physical access to the server machine to make sure new data folder has proper security permissions. It should be granted access to the SQLServer2005MSOLAPUser$MACHINENAME$MSSQLSERVER local group. So that during service account change Analysis Server still has an access to the data folder.

You also have an option to copy entire content of the old datafolder into the new location. In this case you will retain your data and metadata.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Thanks for the reply. I have raised it on Connect here: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=146652

-Jamie

|||

I had a similar problem when trying to change the DataDir. I lost all my cubes in the old DataDir. I changed it back to the old DataDir as per Jamie's instructions to recover. But the cube I restored in the new DataDir is no longer visible. Is there any way to recover this metadata?

Edward, My serivce pack version SP1 is 2047. I saw you note dated 8/24/06 regarding next version? Is the fix planned for SP2?

A hint for others. Change/set your DataDir immediately after install of SQL Server 2005 so that you do not encounter this problem.

|||

I found a way to do this without having the problems. I posted it to my blog, so rather than retype it, here's the link

http://skinnytrail.blogspot.com/2006/08/changing-datadir-in-ssas-sql-2005.html

Possible bug: Lose members of server role when changing DataDir server property

Hello,

I think I have found a bug in AS and I would like it confirming before I report it. Please could someone try the following steps (don't do this on a production machine):

Connect to your Analysis Server in SSMS with a user that is a member of the server role but NOT the user that installed the software

Right-click on the server in Object Explorer. Select 'Properties...'

On the general tab, change the value of the DataDir property to a different folder (it doesn't matter what)

Click 'OK' on "Analysis Server properties" dialog. You will get a message telling you that the server needs to be restarted

Restart your Analysis Server in Services panel.

Re-Connect to the Analysis Server in Object Explorer again.

Right-click on the server in Object Explorer. Select 'Properties...'

You won't be able to see any server properties and if you click on the 'Security' tab you will not be able to see yourself in the list of server role members. In other words...it seems you have been removed from the server role just by changing the data directory. If you try and do anything like deploy to the server you will get an error message "Either the 'username' user does not have permission to create a new object in 'LNVDMFIISDEV01', or the object does not exist."

It gets stranger...

Log onto the server as the user that originally installed Analysis Services (for this is now the only user that will have server role privelages)

Change the DataDir back to what it was before. Restart AS.

After that all of the server role members will be back where they were before. Everything as normal.

What gives? Can someone from MSFT confirm that this is a bug? If so I will raise it as such on Microsoft Connect.

Also, with this in mind, what should be the proper procedure for changing the data directory?

Regards

Jamie

I'm on SP1 by the way!

Thanks Jamie for catching this.

Please go ahead and file it.
The problem is indeed requires some attention. Maybe books online should explain better what is going on.

Changing Data directory is very big change. You will be loosing all of your data and metadata. More to it, you need to have physical access to the server machine to make sure new data folder has proper security permissions. It should be granted access to the SQLServer2005MSOLAPUser$MACHINENAME$MSSQLSERVER local group. So that during service account change Analysis Server still has an access to the data folder.

You also have an option to copy entire content of the old datafolder into the new location. In this case you will retain your data and metadata.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Thanks for the reply. I have raised it on Connect here: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=146652

-Jamie

|||

I had a similar problem when trying to change the DataDir. I lost all my cubes in the old DataDir. I changed it back to the old DataDir as per Jamie's instructions to recover. But the cube I restored in the new DataDir is no longer visible. Is there any way to recover this metadata?

Edward, My serivce pack version SP1 is 2047. I saw you note dated 8/24/06 regarding next version? Is the fix planned for SP2?

A hint for others. Change/set your DataDir immediately after install of SQL Server 2005 so that you do not encounter this problem.

|||

I found a way to do this without having the problems. I posted it to my blog, so rather than retype it, here's the link

http://skinnytrail.blogspot.com/2006/08/changing-datadir-in-ssas-sql-2005.html