IVR to Database to Web

Hello all

I am about to begin a project that will be implemented in a local company. It involves using an IVR(interactive voice response) to collect information with regards a customer satisfaction survey. The results are then to be presented on the web. Thus I have an IVR connected to a database which is connected to the web. The results are to be presented in stats and graphs on the web.

I was originally going to use an Oracle database however it has worked out that it is not feesable to use Oracle for such a small application. I am now looking at SQL server. However, I have never dealt with it before. I have extensive knowledge of SQL. What I need to know is SQL server suitable to use when it comes to connecting to a web application, querying the data to show graphs and stats and is it easy to learn for a person who has extensive knowledge of databases? Id appreciate your knowledge and advice on this topicHowdy,

Greetings from sunny Surrey in the UK!!

Well the short answer is - yes - if you are willing to take the time to learn SQLs eccentricities.

Regards size , we have 5 MB databases running ( some with web app links ) so small isnt an issue.

SQL Server is pretty easy to learn, and if you are just quering the database or writing data into it from a web app, it should all be straight forward. Mind you...how long is a bit of string?

Do you have a DBA to set up the server or are you it?

Cheers,

SGsql

Iterative Calculations Analysis Server 2000

Hi all

I'm looking for some help on an iterative cell calculation. I read somewhere that this can be done using a combination of the calculation pass value and calculation pass depth. I'm still having trouble though.

The easiest way of explaining this is to use an example. Sales reps get incentives based on profit, however incentives affect expenses which in turn affects profits. Using a starting income statement with no incentive about I need to go through about 20 iterations to come an answer.

So basically we have the following lines - (Abridged version )

Income - 10000

Other Expenses - 5000

Incentives = ( Profit * 0.025 )

Profit = ( Income - Other Expenses - Incentives )

Over 20 passes we should have the following

Income - 10000

Other Expenses - 5000

Incentives = 121.95

Profit = 4878.05

Any help would be appreciated.

Thanks

Garron

Hi Garron,

Since your sample only involves iteration, without using data from any specific OLAP cube, the MDX below uses the Foodmart Sales cube only as a place-holder. The CALCULATION_PASS_DEPTH=20 specifies the number of iterations, which should culminate at CALCULATION_PASS_NUMBER = 21. In the Sample MDX App, the Ivalues returned are: Incentives = 121.951, Profit = 4878.049

>>

With Member [Measures].[Income] as '10000'
Member [Measures].[OtherExpenses] as '5000'
Member [Measures].[Incentives] as '0'
Member [Measures].[Profit] as
'[Measures].[Income] - [OtherExpenses]
- [Measures].[Incentives]'

Cell Calculation [IterativeIncentives]
for '({ [Measures].[Incentives] })'
as 'CalculationPassValue([Measures].[Profit],
-1, RELATIVE) * 0.025',
CALCULATION_PASS_NUMBER = 21,
CALCULATION_PASS_DEPTH = 20

select {[Measures].[Income],
[Measures].[OtherExpenses],
[Measures].[Incentives],
[Measures].[Profit] } on columns
from Sales

>>

Here's a link to the AS 2000 BOL section which explains Pass Order:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxadvanced_6jn7.asp

>>

Understanding Pass Order and Solve Order

Two of the most powerful and, correspondingly, most difficult concepts in Microsoft? SQL Server? 2000 Analysis Services, solve order and pass order together determine the manner in which a cube is resolved when queries are processed. This topic assumes that you have a basic understanding of cubes, custom members, calculated members, and custom rollups.

>>

|||

Thanks for the help.

Is it possible to do this as a calculated cell within a cube?

Thanks

Garron Mosley

|||

Sure, you could create the same calculated members and cells in a cube, I just used query-scoped calculations as an illustration...

sql

Issue with executing dynamic sql with inbound paramter as varchar striong

Hi All:

I am trying to execute a dynamic sql, the dynamic sql makes use of an inbound paramter defined as varchar.

When I try to execute it fails, because it does not plavce the inbound paramter in quotes.

Any help would be appreciated.

In the bound search as an eaxmple can be" 'NY'

@.P_SEARCH_VALUE='NY'

SET @.V_SQL_FILTER = N' WHERE STATE = '+@.P_SEARCH_VALUE

SET @.V_SQL=@.V_BASE_SQL+@.V_SQL_FILTER

EXEC sp_executesql @.V_SQL

Here is the v_sql out put:

SELECT TOP 100 * FROM V$ZIPCODE_LOOKUP_ALL WHERE STATE = NY

As you can see the sql will fail because the NY is not in quotes.

I tried using '@.P_SEARCH_VALUE''' and other forms but could not get it work.

There are a couple of ways (and variations) to approach this issue.

First, and the most 'robust', is to use the capability of sp_executesql to handle parameters. For the best explanition and demonstration, see Erland's article here.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

Otherwise, you have to devise some scheme to manage quotes. For example, in you code above you are not managing the quotes inside your string. To embed a single quote inside a string, you have to double it up. So your filter would be more like this:

SET @.V_SQL_FILTER = N' WHERE STATE = ''' + @.P_SEARCH_VALUE + ''''

Handling the quotes can seem to get out of hand, and very confusing. For that reason, the first option is the best.

|||

Since you are using the sp_executesql, itself supports the parameterized quires. You need not to concatenate those values (contaminating values may cause sql injection).

You can achieve the same result using the following query,

Code Snippet

SET @.P_SEARCH_VALUE='NY'

SET @.V_SQL_FILTER = N' WHERE STATE = @.P_SEARCH_VALUE'

SET @.V_SQL = @.V_BASE_SQL+@.V_SQL_FILTER

SET @.V_PARAM = N'@.P_SEARCH_VALUE VARCHAR(100)'

EXEC sp_executesql @.V_SQL, @.V_PARAM , @.P_SEARCH_VALUE

Sample,

Code Snippet

Declare @.SQL as Nvarchar(1000);

Declare @.Value as varchar(100);

Declare @.Param as Nvarchar(100);

Set @.SQL = N'Select * from sysobjects where name=@.value'

Set @.Param = N'@.value varchar(100)'

Set @.Value = 'sysobjects'

exec sp_executesql @.SQL, @.Param, @.value

|||

For something like this:

SET @.V_SQL_FILTER = N' WHERE STATE = ''' + @.P_SEARCH_VALUE + ''''

You can use:

declare @.p_search_value nvarchar(20)

set @.p_search_value = 'This is a quote '''

select N' WHERE STATE = ' + quoteName(@.P_SEARCH_VALUE ,'''')

It is best to use sp_executeSQL, but if you have to work with quotes, this is the best tool for the job.|||try the following use three single quotes

@.P_SEARCH_VALUE='''NY'''

SET @.V_SQL_FILTER = N' WHERE STATE = '+@.P_SEARCH_VALUE

SET @.V_SQL=@.V_BASE_SQL+@.V_SQL_FILTER

EXEC sp_executesql @.V_SQL