Executing MDX queries from inside SQL Server Stored Procedures (SQL Server 2005)

Hello all,

Does anyone have any idea how to access a cube from stored procedures in SQL Server 2005?

My idea was to use SQLCLR and write a function in .NET that accessed the cube through ADOMD, but there are problems with that.

See the following code sample:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.AnalysisServices.AdomdClient;

public partial class UserDefinedFunctions
{

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString MDXRdr()
{
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = @."Provider=SQLNCLI.1;Data Source=JOAHSE0\SQL2005;Integrated Security=SSPI;Initial Catalog=dbRenTAK";

conn.Open();
// Just output cube name
string str = conn.Cubes[0].Name;
conn.Close();

return new SqlString(str);
}

};

First, I wasn't able to add a reference to AdomdClient from Visual Studio. I then did add a reference manually in the project file and it compiles. But when I try to deploy, I get an error message that the "assembly adomdclient was not found in the SQL catalog".

Thanks in advance for any suggestions or hints!

Best Regards,

Johan ?hln
Consultant, IFS

Moving to the "Data Mining" Forum, which is better suited for this question.

executing arbitrary statements in mdx

for example if I want to get a feel for how a particular function operates, can I do something like this ...

select {cdate("1/1/2005")} on columns

from [itdev1 hk]

what would be the correct syntax?

Hello! My recommendation is to start with simple MDX expression like calculated members.

Here is a good site wih a lot of MDX tutorials for beginners and advanced users:

http://www.databasejournal.com/features/article.php/3593466

Look for William.E.Pearson

HTH

Thomas Ivarsson

|||It usually looks something like this:

Code Snippet

WITH MEMBER [Measures].[TestMeasure] AS 'cdate("1/1/2005")'
SELECT {[Measures].[TestMeasure]} ON COLUMNS

I can't remember if you need a FROM when you aren't referring to any real dimensions/measures. If it complains, just point it at a cube.
|||

note: i did need to put in a cube name, and after that it worked great.

nice one!

|||etetetet

executing arbitrary statements in mdx

for example if I want to get a feel for how a particular function operates, can I do something like this ...

select {cdate("1/1/2005")} on columns

from [itdev1 hk]

what would be the correct syntax?

Hello! My recommendation is to start with simple MDX expression like calculated members.

Here is a good site wih a lot of MDX tutorials for beginners and advanced users:

http://www.databasejournal.com/features/article.php/3593466

Look for William.E.Pearson

HTH

Thomas Ivarsson

|||It usually looks something like this:

Code Snippet

WITH MEMBER [Measures].[TestMeasure] AS 'cdate("1/1/2005")'
SELECT {[Measures].[TestMeasure]} ON COLUMNS

I can't remember if you need a FROM when you aren't referring to any real dimensions/measures. If it complains, just point it at a cube.
|||

note: i did need to put in a cube name, and after that it worked great.

nice one!

Executin SQL statement from variable in functions

Hello All

Within my function depends on value forming a SQL statement

As Example

set @.SqlString = 'select ' + @.ColumnName + ' from ' + @.tableName + ' where ' + @.whereCond

How can i execute this Statement. within function

Thanks in advance

RK

> Within my function depends on value forming a SQL statement

Sorry, no can do, you cannot execute dynamic SQL in a function. Maybe you

meant to use a stored procedure, not a function.

|||

I can change function to stored procedure within storedprocedure how can i execute a dynamic SQL

My requirement is to open cursor with dynamic SQL.
table name, columns list and conditions are depends on user parameters

|||

> My requirement is to open cursor with dynamic SQL.

You need a cursor and dynamic SQL. Fantastic. First, please read this:

http://www.smmarskog.se/dynamic_sql.html

Then, if you can give better specs, maybe we can help.

http://www.aspfaq.com/5006

|||looks like the site was down (http://www.smmarskog.se/dynamic_sql.html)

is there a way i can a open cursor with dynamic sql.

Our requirement is we have set of related tables with different data but all integer columns

We are trying to create few statistical functions/procs So user can pass Table name and column name and optionally any conditions, so that my function/procs returns results.

|||

Sorry, typo

http://www.sommarskog.se/dynamic_sql.html

> looks like the site was down

Execute Stored Procedure from User Defined Function

Is it possible to execute a Stored Procedure from within a user defined
function.
The purpose of the user defined function is to be able to use the results of
the Stored Procedure in a select statement. The user defined function should
return a table.JC,
Why do you need both? Can't you just use a stored procedure?
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:009B25FA-06E1-481C-B563-6B4F3745717C@.microsoft.com...
> Is it possible to execute a Stored Procedure from within a user defined
> function.
> The purpose of the user defined function is to be able to use the results
> of
> the Stored Procedure in a select statement. The user defined function
> should
> return a table.|||I need to use the results of the stored procedure in an inner join.
Here is an Example:
Select *
From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID=
<ResultReturnedBy_StoredProcedure>.ID
You can use UDF functions in this manner but as far as I know I can only get
results from a Stored Procedure using the Execute statement. If there is a
another way to accomplish what I'm trying to do besides using UDF please let
me know.
"Jerry Spivey" wrote:

> JC,
> Why do you need both? Can't you just use a stored procedure?
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:009B25FA-06E1-481C-B563-6B4F3745717C@.microsoft.com...
>
>|||JC,
Create a temp table, load the data into the temp table from the proc via
INSERT...EXEC, join with the temp table.
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@.microsoft.com...
>I need to use the results of the stored procedure in an inner join.
> Here is an Example:
> Select *
> From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID=
> <ResultReturnedBy_StoredProcedure>.ID
> You can use UDF functions in this manner but as far as I know I can only
> get
> results from a Stored Procedure using the Execute statement. If there is a
> another way to accomplish what I'm trying to do besides using UDF please
> let
> me know.
>
> "Jerry Spivey" wrote:
>|||Jerry,
Thanks for your responses.
I had considered the temp table idea but was hesitant about performance. Is
performance really a concern with temp tables. Also just to know, Execute
statements are not allowed inside a UDF, Y or N?
"Jerry Spivey" wrote:

> JC,
> Create a temp table, load the data into the temp table from the proc via
> INSERT...EXEC, join with the temp table.
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@.microsoft.com...
>
>|||Depends on the number of records. I don't really use UDFs too much so I
don't know the answer to the second question.
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@.microsoft.com...
> Jerry,
> Thanks for your responses.
> I had considered the temp table idea but was hesitant about performance.
> Is
> performance really a concern with temp tables. Also just to know, Execute
> statements are not allowed inside a UDF, Y or N?
> "Jerry Spivey" wrote:
>|||The temp table will consist of one int primary key column and no other
columns. It will usually have between 5K to 20K rows but in some instances i
t
can potentially have more.
"Jerry Spivey" wrote:

> Depends on the number of records. I don't really use UDFs too much so I
> don't know the answer to the second question.
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@.microsoft.com...
>
>|||I'm having the same problem. I would like to use a UDF to call my stored
procedure so I could create a view using it. I am developing an application
in C# that uses Crystal Reports. It is so much easier for the Reports to us
e
Tables/Views/Functions (Stored Procs are not even listed) by the wizard. An
y
suggestions?
"JC" wrote:
> The temp table will consist of one int primary key column and no other
> columns. It will usually have between 5K to 20K rows but in some instances
it
> can potentially have more.
> "Jerry Spivey" wrote:
>

Execute SQL Task - SSIS

A stored function is created in MySQL Server running on Linux with following info:

mysql > delimiter $$

mysql > CREATE FUNCTION GetMaxKeyForSampleTable()

-> RETURNS DOUBLE READS SQL DATA

-> BEGIN

-> RETURN (SELECT MAX(MJD) FROM tblSampleTable);

-> END;

Query OK, 0 rows affected (0.02 sec)

mysql > delimiter ;

***The above function is successfully stored in the database.

"Execute SQL Task" add-in module has following info:

Name: Execute SQL Task

Description: Execute Task

TimeOut: 0

CodePage: 1252

ResultSet: Single row

Connection Type: ADO.NET

Connection: .MySQLServerLinux

SQLSourceType: Direct Input

SQL Statement: SELECT GetMaxKeyForSampleTable() AS MaxKey

IsQueryStoredProcedure: True

ByPassPrepare: False

Result Set:

Result Name Variable Name

MaxKey User:_dMaxKey

The following steps are taken:

1. Clicked "Parse Query" push button which results a pop-up dialog box -

"The query parsed correctly".

2. Clicked "OK" button.

3. Right clicked "Execute SQL Task" add-in module.

4. Selected "Execute Task".

"Execute SQL Task" changed its color from white to yellow to red

which means excution failed.

Please help me to figure out what should be done to make the excution successful.

Thank you very much in advance.

Paul Cho

First try the Progress tab, after it fails for some more details on the error|||

Do you mean break points by saying Progress Tab?

I set break points for "Execute SQL Task" add-in module.

However, the excution does not stop at break points.

Would you please help me on this issue?

If you meant something different, please elaborate on Progress Tab please.

Thank you very much in advance.

Paul Cho

|||

You have several tabs across the top of the SSIS designer, labeled Control Flow, Data Flow, etc. Progress is one of those. Error messages are reported on this tab.

execute functions in sql reporter service

Hello
a. How can I calculate in a function in SQL reporter the date for tomorrow.
Now I've filled in a criteria {fn > now()} which shows me all the coming
projects from today.
I only want to show reports for tomorrow. I know how to do it in VB.NET but
can I used in SQL reporter service ?
b. When I export date values to excel, they are shown as numeric value. In
PDF exporter, it works fine? I've changed my dateformat several times,
nothing helps
c. I need a function like this. I a certain value is smaller than 10, the
word "LOW VALUE" is reporter, otherwise the word "HIGH VALUE" is shownReport expressions are essentially VB.NET
So you should be able to everything inside of the expressions.
With the only caveat is that when report is compiled, by default we import
only following namespaces:
System, System.Convert, System.Math and Microsoft.VisualBasic
If you want to get something from any other namespace you'll need to use
full name notation which include full namespace.
"DEME-Carlo" <DEME-Carlo@.discussions.microsoft.com> wrote in message
news:931C2DD3-84A2-4526-A761-55C5A465E12C@.microsoft.com...
> Hello
> a. How can I calculate in a function in SQL reporter the date for
tomorrow.
> Now I've filled in a criteria {fn > now()} which shows me all the coming
> projects from today.
> I only want to show reports for tomorrow. I know how to do it in VB.NET
but
> can I used in SQL reporter service ?
> b. When I export date values to excel, they are shown as numeric value.
In
> PDF exporter, it works fine? I've changed my dateformat several times,
> nothing helps
> c. I need a function like this. I a certain value is smaller than 10,
the
> word "LOW VALUE" is reporter, otherwise the word "HIGH VALUE" is shown
>
>
>
>

Execute Function

hi all,

say for eg i have a function and proc named as fun_sample and proc_sample respectivley.

why do we specify "dbo.fun_sample" for executing the function, whereas only "proc_sample" is enought for executing the proc?

For scalar function:

select dbo.fun_sample()

or

declare @.v int

set @.v = dbo.fun_sample()

For table-value function:

select * from dbo.fun_sample()

|||sorry, u have not got my question.

my doubt is why we are specifying the ownername for executing a function?|||"dbo." isn't owner name, it's schema. If you use function without schema name, SQL Server think that it's system function. For stored procedures "sp_" prefix is a reason to resolving as system stored procedure.|||

firstly its a schema name or owner name, depending on the version of sql server being used...owner for 2000 and schema for 2005

for the main question...why dbo. for a function... i guess its just a syntax thing ...and it does saves time for the server... so maybe i'll use it evn if its not mandatory.... use it for sps as well....

Execute Create table command from asp.net

I have a little application that I have designed where I need to be able to execute create table and create function comands against the database.

It seems that it does not like my sql file. Does anyone know of a different method of doing this?

Error message

Line 2: Incorrect syntax near 'GO'.

Line 4: Incorrect syntax near 'GO'.

Line 8: Incorrect syntax near 'GO'.

'CREATE FUNCTION' must be the first statement in a query batch.

Must declare the variable '@.usb'.

Must declare the variable '@.usb'.

Must declare the variable '@.i'.

A RETURN statement with a return value cannot be used in this context.

Line 89: Incorrect syntax near 'GO'.

Line 91: Incorrect syntax near 'GO'.

Line 94: Incorrect syntax near 'GO'.

ProtectedSub Install() Dim errAsString =""While err.Length < 1' Dim your StreamReader Dim TextFileStreamAs System.IO.TextReader 'Load the textfile into the stream

TextFileStream = System.IO.File.OpenText(Request.PhysicalApplicationPath &

"Scripts\0.sql") 'Read to the end of the file into a String variable.

executesql(TextFileStream.ReadToEnd, err)

err =

"Susscessful"EndWhileIf err ="Susscessful"Then

Response.Redirect(

"Default.aspx")Else Me.lblError.Text = errEndIfEndSubPrivateFunction executesql(ByVal sAsString,ByRef errAsString)AsBooleanTry Dim connAsNew Data.SqlClient.SqlConnection(GenConString()) Dim cmdAsNew Data.SqlClient.SqlCommand(s, conn)

conn.Open()

cmd.ExecuteNonQuery()

conn.Close()

ReturnTrue Catch exAs Exception

err = ex.Message.ToString

ReturnFalse

EndTry

EndFunction

Example sql file

SET QUOTED_IDENTIFIER ON

GO

SET

ANSI_NULLSON

GO

if exists

(select*fromdbo.sysobjectswhereid =object_id(N'[dbo].[MyFunc]')andxtypein(N'FN', N'IF', N'TF'))

drop

function [dbo].[MyFunc]

GO

CREATE

FUNCTION [dbo].[MyFunc]

(

-- Add the parameters for the function here

)

RETURNS

varchar(1000)

AS

BEGIN

-- Declare the return variable hereDECLARE@.Resultvarchar(1000)-- Add the T-SQL statements to compute the return value here-- Do something here-- Return the result of the functionRETURN@.Result

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLSON

GO

You cannot use ExecuteNonQuery to run a batch of SQL commands. Try using the OSQL command utility instead.