Cursor and UDf

Hello:

I am trying to define a cursor as follows:

DECLARE EmployeeList
CURSOR FOR dbo.GetRecord(@.EmployeeID,@.CurrentDate)
Can't I use a UDF in the CURSOR FOR ?
Help please.
thank you.

I think it is the other way your UDF can use local Cursors, try the links below for more info; the second link is a UDF expert. I am assuming you know Cursors can be avoided because the query processor is not created to perform such tasks. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/1442221

http://www.novicksoftware.com/

|||SomeNewTricks2, what does your GetRecord UDF return?
|||What I am trying to do is that, I have an input parameter for my stored procedure, if it is set to 1, I want to get all records else one specific record, I want to build the select statement dynamically, then put it in the CURSOR FOR statement.
Is that possible Terri?
Thank you|||

Also, can I use something like:
CURSOR FOR
SELECT * FROM dbo.GetRecord(@.EmployeeID,@.CurrentDate)
?
thanks a lot.

|||

Hello tmorton: I was able to do so:
CREATE FUNCTION [dbo].[GetEmployeeRecord]
(
@.EmployeeID INT
)
RETURNS @.EmployeeRecord TABLE
(
EmployeeID INT,
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
)
AS
BEGIN
DECLARE @.strQuery NVARCHAR(1000)
DECLARE @.parameterList NVARCHAR(1000)
SELECT @.strQuery =
N'
-- Create Global Table (Temporarily)
Create Table ##TempEmployeeData
(
EmployeeID INT,
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
)
-- Insert the needed records to the above table
INSERT INTO ##TempEmployeeData SELECT * FROM Employees WHERE (1=1)'

-- If employee id = -1, means get all records else, get specifi record
IF (@.EmployeeID != -1)
SELECT @.strQuery = @.strQuery + N' AND (EmployeeID = @.EmployeeID)'

-- Add parameter list, only one parameter present
SET @.parameterList =N'@.EmployeeID INT'

-- Execute dynamic query
EXECUTE SP_EXECUTESQL @.strQuery, @.parameterList, @.EmployeeID

-- Fill our returned table
INSERT INTO
@.EmployeeRecord
SELECT * FROM ##TempEmployeeData

-- Drop the Temp table
DROP TABLE ##TempEmployeeData

RETURN
END
I get this error:
Server: Msg 2772, Level 16, State 1, Procedure GetEmployeeRecord, Line 72
Cannot access temporary tables from within a function.
Server: Msg 2772, Level 16, State 1, Procedure GetEmployeeRecord, Line 74
Cannot access temporary tables from within a function.
Once I finish this udf, I will use
CURSOR FOR
SELECT * FROM dbo.GetEmployeeRecord.
Can you help please. thanks you.

|||Whoa. It seems to be you are making this much more difficult than it has to be.

SomeNewTricks2 wrote:

Also, can I use something like:
CURSOR FOR
SELECT * FROM dbo.GetRecord(@.EmployeeID,@.CurrentDate)

Yes, that would be the correct way to access data from a tabel-valuedUDF. Your function returns a table, so use need to SELECT fromit. You can't just put the name of the UDF into a command byitself and expect SQL to know what to do.
You are trying to use global temp tables (##) which is usually not agood idea for a web application. When user #2 hits the site andtries to run the page you are going to start running into problems.
Maybe I am not really following what you are trying to do exactly, but why aren't you just doing something like this (untested)?

CREATE FUNCTION [dbo].[GetEmployeeRecord]
(
@.EmployeeID INT
)
RETURNS @.EmployeeRecord TABLE
(
EmployeeID INT,
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
)
AS
BEGIN
INSERT INTO
@.EmployeeRecord
(
EmployeeID,
UserName,
FirstName,
LastName
)

SELECT
EmployeeID,
UserName,
FirstName,
LastName
FROM
Employees
WHERE
@.EmployeeID = -1 OR @.EmployeeID = EmployeeID
RETURN

END

|||thank you Terri, that solved the problem even without temp tables.
The reason I used ## is that to be able to access it outside the context of the dynamically executed query. I mean when I run a dynamic query, in which I create a temp table, I need to access it outside the context of its execution, that is why i used ##.
One more thing, I am trying to get all records when EmlpoyeeId = -1 and get specific record when there is a valid employee id
my question, why did u write EmployeeID = -1 OR EmployeeID = @.EmployeeID ?
if EmployeeID = -1, then I will go into the table search for EmployeeID = -1, cannot find any record, however, it is returning all records, what is the logic behind it?
thank you
|||

SomeNewTricks2 wrote:

why did u write EmployeeID = -1 OR EmployeeID = @.EmployeeID ?

Actually, I wrote@.EmployeeID= -1 OREmployeeID = @.EmployeeID. That's a huge difference and is the keyto why the statement works. You have 2 conditions in your WHEREclause, with an OR between them, so only one of the conditions mustbe true in order for the WHERE criteria to be met and for a row to bereturned.
Here's a chart representing representing the above explanation:
@.EmployeeID = 5
UserName EmployeeID Condition1 (@.EmployeeID=-1) Condition 2(@.EmplyeeID=EmployeeID) Row Returned?
jones 123 False False No
smith 5 False True Yes
brown 99 False False No

@.EmployeeID = -1
UserName EmployeeID Condition1 (@.EmployeeID=-1) Condition 2(@.EmplyeeID=EmployeeID) Row Returned?
jones 123 True False Yes
smith 5 True False Yes
brown 99 True False Yes
|||

Fantastic Terri, you are really fantastic.

I once read that Books Online are good, but are there are resources for SQL Server that you usally trust?

Thanks a lot.

|||Terri, what I am doing is kind of a complicated thing.
It is a stored procedure, where I am using like 7 UDFs inside it, each has its own responsibility.
I have one more question, in a stored procedure, can I return a table?
I mean, inside the SP I will have several SELECT statements, I have like 8 fields, I want to have data for them for each employee, sometimes I might return 1 row, other times many rows, so what I am doing is, go through each row in the employees table, using the CURSOR, and then process eac employee alone, either I process one employee or many.
during procesing I will get a record of 8 fields for the employee,I want to insert that into a table, because I might have several employees, how to do that in SP ?
thanks a lot really, you are saving me.|||I'm glad to help :-)

SomeNewTricks2 wrote:

I once read that Books Online are good,but are there are resources for SQL Server that you usallytrust?

I recently changed jobs, and currently Books Online is the onlyresource for SQL Server that I have. I use it *continually*.
For websites, I most often use Google. I've also gotten a lot of help fromhttp://www.sql-server-performance.com.
On the shelf at my last job we had Ken Henderson'sThe Guru's Guide to Transact-SQL, which is excellent. Also, I have not used this book myself, but it comes highly recommended: Rob Veiera'sProfessional SQL Server 2000 Programming. I still have lots more to learn. But I have learned what I know so farwith both practical on-the-job experience, and reading and replying toposts here and on the lists athttp://www.sqladvice.com. I've certainly made mistakes in some of the advice I've given, and whenothers have come along and made a different/better suggestion this iswhere I've learned the most, I think.


|||

SomeNewTricks2 wrote:

Terri, what I am doing is kind of a complicated thing.
It is a stored procedure, where I am using like 7 UDFs inside it, each has its own responsibility.
I have one more question, in a stored procedure, can I return a table?
Imean, inside the SP I will have several SELECT statements, I have like8 fields, I want to have data for them for each employee, sometimes Imight return 1 row, other times many rows, so what I am doing is, gothrough each row in the employees table, using the CURSOR, and thenprocess eac employee alone, either I process one employee or many.
duringprocesing I will get a record of 8 fields for the employee,I want toinsert that into a table, because I might have several employees, howto do that in SP ?
thanks a lot really, you are saving me.

Are you certain you need to use cursors? You need to break freeof the procedural mindset and start thinking about sets of data. When you say you need to process each employee, what is it that you aredoing?
Explaining it further, rather than creating a loop with a cursor toinsert one record at a time into a table, you can do it in one fellswoop with a statement like this:

INSERT INTO
someTable
(
column1,
column2
)
SELECT
column1,
column2
FROM
someOtherTable
WHERE
@.someID = -1 OR @.someID = someID

|||

Hi Terri:

What do you mean by "sets of data", what is the difference between that and Cursors?
What I am doing is the following:
1- I am getting a list of all employees
2- for each employee I do the following:
2.1 Get attendance sheet (timein, timeout, absence, remarks)
3- if employee is scehduled
3.1 Get the schedule of the employee
3.2 get new values (required time in, required time out, required work time)
3.3 get difference in time in
3.4 get difference in time out
4- If not scheduled
4.1 I get normal schedule for all employees
4.2 I set the values for (required time in, ... as above)
I am doing that in one sp, it is in a project I have employees and I want to keep track of their attendance.
so, I am lopping through each employee, generated one record as output:
EmployeeName, timeIn, Requiredtimein, timeout, requiredtimeout, worktime, requried work time, break. diffin, diffout, absence, remarks
That is the record that should be returned for each employee.
So what are your ideas?
Thanks a lot and good luck in your work.

|||You should really be able to do all of that in one SQL statement. Below is a very rough idea of what I think you are going after(understanding that I am not sure of how your tables relate exactly,nor how you might determine the "normal schedule", and that theWorkTime, DiffIn, and DiffOut calculations probably should use theDATEDIFF function):

SELECT
E.EmployeeName,
A.TimeIn,
ISNULL(S.RequiredTimeIn, NS.RequiredTimeIn) AS RequiredTimeIn,
A.TimeOut,
ISNULL(S.RequiredTimeOut, NS.RequiredTimeOut) ASRequiredTimeOut,
A.TimeOut - A.TimeIn AS WorkTime,
ISNULL(S.RequiredWorkTime, NS.RequiredWorkTime) ASRequiredWorkTime,
A.Break,
A.TimeIn - ISNULL(S.RequiredTimeIn, NS.RequiredTimeIn) AS DiffIn,
A.TimeOut - ISNULL(S.RequiredTimeOut, NS.RequiredTimeOut) AS Diffout,
A.Absence,
A.Remarks
FROM
Employee E
LEFT OUTER JOIN
Attendance A ON E.EmployeeID = A.EmployeeID
LEFT OUTER JOIN
EmployeeSchedule S ON S.employeeID = E.employeeID
LEFT OUTER JOIN
NormalSchedule NS ON NS.employeeID = E.employeeID

I looked up a few resources to help you with the concept of set-based logic (instead of procedural logic):

SQL Cheat Sheet: Query By Example
Thinking SQL: Set-based logic can improve query performance
Procedural Versus Declarative Languages

Cube Roles - How to restrict access to certain members

I have a dimension called Account that has five levels. I'm trying to define a cube role to deny access to accounts in the lowest level that starts with the letters GL. I tried applying a filter in the dimension data tab in the role designer, however, this only seems to create a static rule. The account structure gets updated all the time and I want to restrict access to all accounts that start with the letters GL now and in the future.

When I created a rule in another dimension using a top-level static member, a denied member set was created in the advanced tab. I think I need to create something similar for the Account dimension, but I just can't get the syntax of MDX. I tried the BOL samples but can't find anything since I want to filter only part of the member name.

Try typing something like the following expression for DeniedSet:

Filter(Account.Levels(4), Account.Name >= 'GL' AND Account.Name < 'GM')

|||

Thanks, but that didn't work. In addition, I tried Filter([Account].Members, Left([Account].CurrentMember.Name, 2) = "GL") but that didn't work either. I'm wondering if there are too many members to filter at the bottom level.

Oddly, I was able to solve part of the problem by Enabling Visual Total in my other dimensions. I can filter GL type accounts in my other dimensions because there is only one fixed GL member in those dimensions. Since GL accounts only match up to the GL members in the other dimensions and since those GL members have been filtered, GL Accounts are not displayed in the cube result.

I will still try to find a solution, although I can restrict GL data from being displayed in the cube, the GL accounts still show up in the drop down. It's a good thing that when the user selects GL accounts, nothing is displayed.

Cube Partitions Setup

I have use the cube partitions in AS2000. It allows us to use the data slice to define the subset of the cube data. However, I don't see this in AS2005. If I partition the cube in monthly basis, then how AS2005 to handle the query. Is it look through all the partitions since it don't have the data slice setup?

In AS2005 partition slices are defined bit differently.

To define what data should go into partition you can define partition binding. You can change source for your partition to define restriction on the data coming into Analysis Services.

As for Anlaysis Services, it will detect the slice automatically. ( this is MOLAP case)

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

Cube deployment issue SSAS 2005

Hello all,

I got an issue with deploying my cube. I am new to SSAS 2005, and I cannot find the option to define which account to use with deploying. I managed to choose the server for deployment, but strangely enough, no user can be selected, so when it deploys, it throws an error stating the standard windows login isn't valid (wich is correct, but I do not want to use standard windows login).And why do I have to use a local windows user account? Why can't the account from my server?|||The only thing I can find wich is remotely connected with this problem is the following:
http://msdn2.microsoft.com/en-us/library/ms166576.aspx

But nothing about a login or something like that.
Please help, I am totally clueless.|||From your description it is not clear to me what exactly you are doing and what is happening. Can you please decsribe it in more detail, and also provide the exact error message you get. Thanks.|||

I made a database + tables, I defined it a data source in Visual Studio/Business Intelligence/Analysis Services, made a view, and then I defined a simple cube. In AS 2000, I only needed to process the cube. But in 2005 I also need to deploy it. Then it gives an error that it doesn't reckognise the user (wich is a local windows user). Of course it needs to be a SQL Server user (I made an account in SQL Server 2005). I cannot find anywhere where to define the user for deploying the cube.

Thanks in advance.

|||

SSAS does not use SQL Server accounts for authentication. It only uses Windows accounts. This is why there is not where to specify a particular user as it always uses the current windows account. When you are setup using a domain this is not an issue as everything can be specified in terms of domain accounts.

In your situation it sounds like you have local accounts on your workstation and on the server. In this case what you have to do is to set up an identical account (same username and password) on both your workstation and the server. Then when you connect to the server you will effectively be "mapped" onto the local account on the server. So you will need to make sure the account on the server has the appropriate priviledges to deploy databases.

|||That sounds like a workaround.

I rebuilt the cube under the new account, and now I got exactly the same failure as the following:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=239828&SiteID=1

(and yes, I've altered the 'localhost' to the appropriate server running sql server 2005)|||

It could be seen as a work around, but it is the only way I know of to authenticate when you are not using a AD domain.

It sounds like you are having connectivity issues, possibly caused by a firewall or something. There is a great article on diagnosing connection issues here http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

I am suspicious that there may still be an underlying connection issue here. If you have the sample Adventure Works database you could use that to test that you can connect and query it. If you don't want to install the sample, would it be possible to copy the project files to the server and try to deploy the project locally on the server? If you can get this to work it would eliminate actual deployment issues and identify if we are dealing with a network connection issue.

|||I have watched the firewall, and the port configuration seems to be okay. The connection is closed abruptly. (FYI, I use ISA2004, and port 2382 is open, 2383 I do not get opened yet, but it isn't accessed either)
The error message is:
Error 1 The project could not be deployed to the '<server>' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0
(<server> is either localhost, or the server running SQL Server 2005 + AS2005)
The MDX application wich came with AS2000 connects fine to AS2005.
Unfortunately, to set it up via http is not an option in my case.
Could anyone give me a link to where I can find out how and where AS2005 stores cubes etc?
My gratitude is yours.

Regards,

Eyso|||

The fact that you are seeing a connection on port 2382 suggests to me that you might be using a named instance (eg. <server>\<instance>). If this is the case, it will not use port 2383 and unless you have set a specific port for that instance you will not necessarily know which port is being used. With a named instance the client connects to the SQL Browser service on port 2382 to ask it which port the particular instance is listening on and the tries to use that port. If this is the case it sounds like you might want to set a specific port for the instance to use so that you can open up that port in your firewall, by default a named instance will just search for a free port number each time it starts up if one is not specifically set.

> The MDX application which came with AS2000 connects fine to AS2005.

This is unusual if you are getting "A connection cannot be made" errors - as they use the same sort of connection, its just the commands that are sent over the connection that vary. Are you using the same server name in the MDX Sample as in the deployment options in BIDS? I would expect a different error from this, but are you sure your user has the rights to deploy a database?

> Could anyone give me a link to where I can find out how and where AS2005 stores cubes etc?

The "where" is easy, there is a data directory set on the server and if needed you can override this on a partition basis. As to the "how" they are stored in a proprietary format I don't believe there is any public information on this (any you really should not need to know). All your access to SSAS should go through one of the documented API's - XMLA, AMO, ADOMD, ADOMD.NET etc.

|||Well, I made an account in SQL Server 2005 with the same name (as windows login) to my network account, and I open BIDS with my normal account, then if I want to deploy it, I get the following error message:
Error 1 Either the '<usergroup\username>' user does not have permission to create a new object in '<server>', or the object does not exist. 0 0
While it does exists, and I gave it every permission possible.
(as suggested in this thread: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=398382&SiteID=1).|||Great.
Now I try to add securables to that account, run it as a script. Server Management says script has executed succesfully, but when I click again on the account, nothing has changed.
If I try to deploy the cube, nothing is being written in the logfile of SQL Server.|||Hm, if I try to add the server to 'servers' (BIDS -> Tools -> Add server), it doesn't seem to reckognise SQL Server 2005. Though it does reckognise SQL Server 2000, wich runs on the same server.|||

Eyso Zanstra wrote:

Well, I made an account in SQL Server 2005 with the same name (as windows login) to my network account

No - SQL Server accounts have nothing to do with Analysis Services. You can uninstall SQL Server completely and still run Analysis Services.

If you are logging into a windows domain you must setup that network account with administrative rights in order to be able to deploy a new database. You got me chasing down the wrong path when you started talking about using a local windows account earlier in the thread, and I assumed that you must have been running in a workgroup or using a novel network. if you are logged in to a windows domain you cannot map to a local account on the server - you can only assign the rights to the network account.

|||Thanks, that answers my question about how these cubes are being stored. :-)
Is it suffice when I get those rights only on that particular directory where the cube is stored?

Cube Datasource is not on SQL Server

Hi,

is it possible to define a Cube on SQL Server 2005 where the data source itself is on a DB2 or Oracle Database? I would like to have the Cube to collect the data from the DB2 Database to build the Cube. The Cube itself can copy some data to the SQL Server if necessary. What I'm trying to avoid is to have an ETL Process to copy over everything to the SQL Server and then build a Cube on top of it. It is currently very difficult to synchronize the data from the DB2 to the SQL Server. It is also not possible to go over a time period as it can happen that you get new records for the last year. We have to work with a lot of data (several million per day).

My goal is to find an intelligent Solution to make sure that the Cube has all data.

Thanks.

Ertan

Analysis Services supports building cubes by directly connecting to several relational databases. Oracle , DB2 are on the list of supported sources. Please refer to the books online to exact list of supported DB versions.

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

Cube data storage location

Hi -

What level of storage locations we can define in SSAS database e.g. Can we define a separate location for these SSAS ojects.

Instance

Database,

dimensions

Measure Gruop

Measure

Partitons

We do want to store cube database in root data folder.Can some one gide me on this, how to do this. Appreciate your help.

You can set a per instance data folder (indeed each instance will have it's own install folder and the data folder is under that by default). But below that, although you can set the storeage location for cubes and measure groups, these settings pretty much just serve as the default for any new partitions that are created. It is really only at the partition level that you have granular control over where the data is stored. Even then this is really only worth changing if you want to spread your data across mulitple disks. If you are not doing this I would recommend that you just stay with the default folder structure.

I believe that the metadata for databases cubes and measure groups (which are quite small) are all stored in the data folder for the instance along with the dimension data and metadata.

On my laptop I have configured the default folder for my instance to a folder outside of "program files", but I have left everything else at the default settings.