Derived column expression question......

Greetings,

I have an existing 2000 DTS package that uses the following case statement:

Case

When TERMS_PERCENT ='0'

then 0

else cast(TERMS_PERCENT as decimal(6,2))/100

end as TermsPct

to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.

mjanzou wrote:

Greetings,

I have an existing 2000 DTS package that uses the following case statement:

Case

When TERMS_PERCENT ='0'

then 0

else cast(TERMS_PERCENT as decimal(6,2))/100

end as TermsPct

to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.

Something like this perhaps?

[TERMS_PERCENT] == "0" ? (DT_NUMERIC, 5, 2)[TERMS_PERCENT] : (DT_NUMERIC, 5, 2)0

-Jamie

|||

Jamie Thomson wrote:

[TERMS_PERCENT] == "0" ? (DT_NUMERIC, 5, 2)[TERMS_PERCENT] : (DT_NUMERIC, 5, 2)0

-Jamie

The TRUE and FALSE values are reversed . Try:

Code Snippet

[TERMS_PERCENT]== "0" ? (DT_NUMERIC,6,2)0 : ((DT_NUMERIC,6,2)[TERMS_PERCENT]) / 100

|||

Ahhh goddamnit!!!

Deployment - Database Configurations

Greetings,

I've been looking around all morning and can't seem to find a good answer on this so I thought I'd post a message here.

I'm using the recommended practice of keeping the ASPNETDB.MDF data separate from my site data. (I have my site data located in separate databases but still keep them in the App_Data directory.)

I'm using SQL Server 2005 Express on a Server 2003 box and I've implemented scheduled backups on the databases in the Sql Server.

The questions I'm having are:

1. Why isn't the ASPNETDB.MDF database getting attached to the server? Is it using User Instances?

2. If it is using User Instance then isn't this degrading performance? If so, how to force it to not use User Instance?

3. If User Instance is how it is supposed to work then how are we supposed to do a full backup of the ASPNETDB.MDF database if it isn't attached in the server? (xcopy doesn't seem to work on User Instances because of the dreaded "is being used by another process" error.)

Any recommendations, advice, links, or answers to my questions would be greatly appreciated.

1. Yes.

2. Yes, but insignificantly.

3. I believe backup software that is designed to do backups of live SQL Databases (using SQL Agents), can backup User Instances. However, if you are using SQL Express, you are likely running a low traffic site, and it might be easier to run a batch process that stops the IIS Application, xcopy the .MDF and restart the application. You can probably write a quick batch file to create a dummy offline.htm file into your web application directory, wait a few seconds, xcopy, then delete offline.htm.

There may be better approaches, but those are the ones I have off the top of my head, and I don't claim to have a vast knowledge of running Express databases on live servers.

Deploying Analysis service solution

Greetings,

How do I deploy an Analysis services project on to diference machines. I have followed the process laid out in MSDN and changed the server to the remote server name while deploying. It throws up an error that the user does not have permissions to create objects on remote server.

Please help me understanding the process of deployment and how the analysts can open up the deployed cube in their machines

Thanks/Regards

Shiv

You must had the users in the security panel of your remote Analysis Services.

In Microsoft SQL Server Management Studio, open a connection to your Analysis Services Server. Click Right on the name of your server (XXXX (Microsoft Analysis Services...))

Choose Properties, Security, and add the users that must access to your server

|||

Hi,

I am facing the same problem, i did as u said and got the following error:

TITLE: Connect to Server

Cannot connect to myserver.

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

An existing connection was forcibly closed by the remote host (System)

BUTTONS:
OK

pls any help ?

Thanks

Tarek Ghazali

MS SQL Server MVP

Deploying Analysis service solution

Greetings,

How do I deploy an Analysis services project on to diference machines. I have followed the process laid out in MSDN and changed the server to the remote server name while deploying. It throws up an error that the user does not have permissions to create objects on remote server.

Please help me understanding the process of deployment and how the analysts can open up the deployed cube in their machines

Thanks/Regards

Shiv

You must had the users in the security panel of your remote Analysis Services.

In Microsoft SQL Server Management Studio, open a connection to your Analysis Services Server. Click Right on the name of your server (XXXX (Microsoft Analysis Services...))

Choose Properties, Security, and add the users that must access to your server

|||

Hi,

I am facing the same problem, i did as u said and got the following error:

TITLE: Connect to Server

Cannot connect to myserver.

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

An existing connection was forcibly closed by the remote host (System)

BUTTONS:
OK

pls any help ?

Thanks

Tarek Ghazali

MS SQL Server MVP