We have a program that is opening a recordset as a forward only cursor.
What we are experiencing is that the cursor is being opened thousands of
times per minute by our different clients. When this happens our entire
system slows down. I have asked that we not use cursors and return the
entire recordset and release it. During these periods of slow downs we are
experiencing a high number of locks and a high number of Work Tables being
created as well as a high number of Page Splits. The odd thing is that when
I run Profiler, during these slow times, I am not showing any Duration
issues. I do see a large amount of batches being processed (mostly curor
fetches).
I have told the programmers to fix the cursor problem, but I have been
tasked to prove that this is the cause of the slow downs. Any ideas?some thoughts...
1. You're right. This needs to change...
2. you may not see duration problems in profiler. Each individual cursor
(whether it's TSQL or API) might only take a MS or so. But... I just
finsihed working on a system doing over 1000 sp_cursorfetches per second.
Each call showed 0 or 10MS in Profiler. But the aggregate time for these
calls was taking close to 75% of CPU (note, Profiler will generally not show
a duration less than 10MS. SQL is fast, but it's not faster than the speed
of light and cursors take more than 0MS. I usually round to 2.5MS as an apx
time for cursor duration times...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Joe Jackson" <jj@.microsoft.com> wrote in message
news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> We have a program that is opening a recordset as a forward only cursor.
> What we are experiencing is that the cursor is being opened thousands of
> times per minute by our different clients. When this happens our entire
> system slows down. I have asked that we not use cursors and return the
> entire recordset and release it. During these periods of slow downs we
are
> experiencing a high number of locks and a high number of Work Tables being
> created as well as a high number of Page Splits. The odd thing is that
when
> I run Profiler, during these slow times, I am not showing any Duration
> issues. I do see a large amount of batches being processed (mostly curor
> fetches).
> I have told the programmers to fix the cursor problem, but I have been
> tasked to prove that this is the cause of the slow downs. Any ideas?
>|||One other strange thing, CPU was only running at about 30%.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
> some thoughts...
> 1. You're right. This needs to change...
> 2. you may not see duration problems in profiler. Each individual cursor
> (whether it's TSQL or API) might only take a MS or so. But... I just
> finsihed working on a system doing over 1000 sp_cursorfetches per second.
> Each call showed 0 or 10MS in Profiler. But the aggregate time for these
> calls was taking close to 75% of CPU (note, Profiler will generally not
show
> a duration less than 10MS. SQL is fast, but it's not faster than the speed
> of light and cursors take more than 0MS. I usually round to 2.5MS as an
apx
> time for cursor duration times...
>
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Joe Jackson" <jj@.microsoft.com> wrote in message
> news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> > We have a program that is opening a recordset as a forward only cursor.
> > What we are experiencing is that the cursor is being opened thousands of
> > times per minute by our different clients. When this happens our
entire
> > system slows down. I have asked that we not use cursors and return the
> > entire recordset and release it. During these periods of slow downs we
> are
> > experiencing a high number of locks and a high number of Work Tables
being
> > created as well as a high number of Page Splits. The odd thing is that
> when
> > I run Profiler, during these slow times, I am not showing any Duration
> > issues. I do see a large amount of batches being processed (mostly
curor
> > fetches).
> >
> > I have told the programmers to fix the cursor problem, but I have been
> > tasked to prove that this is the cause of the slow downs. Any ideas?
> >
> >
>|||With a very large number of cursor fetches, assuming your clients are on =separate system to the database, you will be generating a very large =number of network round-trips from the client, to SQl Server and back =again.
So I guess your current bottleneck may possibly be network traffic - =with large number of small packets carrying 1 row each. getting rid of =the cursors will most likely help the network as well.
Mike John
"Joe Jackson" <jj@.microsoft.com> wrote in message =news:%233BKP9t2DHA.4060@.TK2MSFTNGP11.phx.gbl...
> One other strange thing, CPU was only running at about 30%.
> > > "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
> > some thoughts...
> >
> > 1. You're right. This needs to change...
> > 2. you may not see duration problems in profiler. Each individual =cursor
> > (whether it's TSQL or API) might only take a MS or so. But... I just
> > finsihed working on a system doing over 1000 sp_cursorfetches per =second.
> > Each call showed 0 or 10MS in Profiler. But the aggregate time for =these
> > calls was taking close to 75% of CPU (note, Profiler will generally =not
> show
> > a duration less than 10MS. SQL is fast, but it's not faster than the =speed
> > of light and cursors take more than 0MS. I usually round to 2.5MS as =an
> apx
> > time for cursor duration times...
> >
> >
> > -- > >
> > Brian Moran
> > Principal Mentor
> > Solid Quality Learning
> > SQL Server MVP
> > http://www.solidqualitylearning.com
> >
> >
> > "Joe Jackson" <jj@.microsoft.com> wrote in message
> > news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> > > We have a program that is opening a recordset as a forward only =cursor.
> > > What we are experiencing is that the cursor is being opened =thousands of
> > > times per minute by our different clients. When this happens our
> entire
> > > system slows down. I have asked that we not use cursors and =return the
> > > entire recordset and release it. During these periods of slow =downs we
> > are
> > > experiencing a high number of locks and a high number of Work =Tables
> being
> > > created as well as a high number of Page Splits. The odd thing is =that
> > when
> > > I run Profiler, during these slow times, I am not showing any =Duration
> > > issues. I do see a large amount of batches being processed =(mostly
> curor
> > > fetches).
> > >
> > > I have told the programmers to fix the cursor problem, but I have =been
> > > tasked to prove that this is the cause of the slow downs. Any =ideas?
> > >
> > >
> >
> >
> >
Showing posts with label experiencing. Show all posts
Showing posts with label experiencing. Show all posts
Thursday, March 29, 2012
Cursor Vs. Performance
We have a program that is opening a recordset as a forward only cursor.
What we are experiencing is that the cursor is being opened thousands of
times per minute by our different clients. When this happens our entire
system slows down. I have asked that we not use cursors and return the
entire recordset and release it. During these periods of slow downs we are
experiencing a high number of locks and a high number of Work Tables being
created as well as a high number of Page Splits. The odd thing is that when
I run Profiler, during these slow times, I am not showing any Duration
issues. I do see a large amount of batches being processed (mostly curor
fetches).
I have told the programmers to fix the cursor problem, but I have been
tasked to prove that this is the cause of the slow downs. Any ideas?some thoughts...
1. You're right. This needs to change...
2. you may not see duration problems in profiler. Each individual cursor
(whether it's TSQL or API) might only take a MS or so. But... I just
finsihed working on a system doing over 1000 sp_cursorfetches per second.
Each call showed 0 or 10MS in Profiler. But the aggregate time for these
calls was taking close to 75% of CPU (note, Profiler will generally not show
a duration less than 10MS. SQL is fast, but it's not faster than the speed
of light and cursors take more than 0MS. I usually round to 2.5MS as an apx
time for cursor duration times...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Joe Jackson" <jj@.microsoft.com> wrote in message
news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
are
when
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
show
apx
separate system to the database, you will be generating a very large =
number of network round-trips from the client, to SQl Server and back =
again.=20
So I guess your current bottleneck may possibly be network traffic - =
with large number of small packets carrying 1 row each. getting rid of =
the cursors will most likely help the network as well.
Mike John
"Joe Jackson" <jj@.microsoft.com> wrote in message =
news:%233BKP9t2DHA.4060@.TK2MSFTNGP11.phx.gbl...
What we are experiencing is that the cursor is being opened thousands of
times per minute by our different clients. When this happens our entire
system slows down. I have asked that we not use cursors and return the
entire recordset and release it. During these periods of slow downs we are
experiencing a high number of locks and a high number of Work Tables being
created as well as a high number of Page Splits. The odd thing is that when
I run Profiler, during these slow times, I am not showing any Duration
issues. I do see a large amount of batches being processed (mostly curor
fetches).
I have told the programmers to fix the cursor problem, but I have been
tasked to prove that this is the cause of the slow downs. Any ideas?some thoughts...
1. You're right. This needs to change...
2. you may not see duration problems in profiler. Each individual cursor
(whether it's TSQL or API) might only take a MS or so. But... I just
finsihed working on a system doing over 1000 sp_cursorfetches per second.
Each call showed 0 or 10MS in Profiler. But the aggregate time for these
calls was taking close to 75% of CPU (note, Profiler will generally not show
a duration less than 10MS. SQL is fast, but it's not faster than the speed
of light and cursors take more than 0MS. I usually round to 2.5MS as an apx
time for cursor duration times...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Joe Jackson" <jj@.microsoft.com> wrote in message
news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
quote:
> We have a program that is opening a recordset as a forward only cursor.
> What we are experiencing is that the cursor is being opened thousands of
> times per minute by our different clients. When this happens our entire
> system slows down. I have asked that we not use cursors and return the
> entire recordset and release it. During these periods of slow downs we
are
quote:
> experiencing a high number of locks and a high number of Work Tables being
> created as well as a high number of Page Splits. The odd thing is that
when
quote:|||One other strange thing, CPU was only running at about 30%.
> I run Profiler, during these slow times, I am not showing any Duration
> issues. I do see a large amount of batches being processed (mostly curor
> fetches).
> I have told the programmers to fix the cursor problem, but I have been
> tasked to prove that this is the cause of the slow downs. Any ideas?
>
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
quote:
> some thoughts...
> 1. You're right. This needs to change...
> 2. you may not see duration problems in profiler. Each individual cursor
> (whether it's TSQL or API) might only take a MS or so. But... I just
> finsihed working on a system doing over 1000 sp_cursorfetches per second.
> Each call showed 0 or 10MS in Profiler. But the aggregate time for these
> calls was taking close to 75% of CPU (note, Profiler will generally not
show
quote:
> a duration less than 10MS. SQL is fast, but it's not faster than the speed
> of light and cursors take more than 0MS. I usually round to 2.5MS as an
apx
quote:|||With a very large number of cursor fetches, assuming your clients are on =
> time for cursor duration times...
>
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Joe Jackson" <jj@.microsoft.com> wrote in message
> news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
entire[QUOTE]
> are
being[QUOTE]
> when
curor[QUOTE]
>
separate system to the database, you will be generating a very large =
number of network round-trips from the client, to SQl Server and back =
again.=20
So I guess your current bottleneck may possibly be network traffic - =
with large number of small packets carrying 1 row each. getting rid of =
the cursors will most likely help the network as well.
Mike John
"Joe Jackson" <jj@.microsoft.com> wrote in message =
news:%233BKP9t2DHA.4060@.TK2MSFTNGP11.phx.gbl...
quote:sql
> One other strange thing, CPU was only running at about 30%.
>=20
>=20
> "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
cursor[QUOTE]
second.[QUOTE]
these[QUOTE]
not[QUOTE]
> show
speed[QUOTE]
an[QUOTE]
> apx
cursor.[QUOTE]
thousands of[QUOTE]
> entire
return the[QUOTE]
downs we[QUOTE]
Tables[QUOTE]
> being
that[QUOTE]
Duration[QUOTE]
(mostly[QUOTE]
> curor
been[QUOTE]
ideas?[QUOTE]
>=20
>
Wednesday, March 21, 2012
Current Security Context Not Trusted When Using Linked Server From SAP
Hello,
I am experiencing a head-scratcher of a problem when trying to use a Linked Server connection to query a remote SQL Server database from our SAP R/3 system. We have had this working just fine for some time, but after migrating to new hardware and upgrading OS, DBMS, and R/3, now we are running into problems.
The target database is a named instance on SQL Server 2000 SP3, Windows 2000 Server. The original source R/3 system was also on SQL Server 2000 (SP4), Windows 2000 Server. I had been using a Linked Server defined via SQL Enterprise Manager (actually defined when the source was on SQL Server 7), which called an alias defined with the Client Network Utility that pointed to the remote named instance. This alias and Linked Server worked great for several years.
Now we have migrated our R/3 system onto new hardware, running Windows Server 2003 SP1 and SQL Server 2005 SP1. I redefined the Linked Server on the new SQL 2005 installation, this time avoiding the alias and referencing the remote named instance directly, and it tests out just fine using queries from SQL Management Studio. It also tests fine with OSQL called from the R/3 server console, both when logged on as the application service account with a trusted connection, and with a SQL login as the schema owner. From outside of the application, I cannot make it fail. It works perfectly.
That all changes when I try to use the Linked Server within an SAP custom program (ABAP), however. The program crashes with a database interface error. The database error code is 15274, and the error text is "Access to the remote server is denied because the current security context is not trusted."
I have set the "trustworthy" property on the R/3 database, I have ensured the service account is a member of the sysadmin SQL role, I've even made it a member of the local Administrators group on both source and target servers, and I've done the same with the SQL Server service account (it uses a domain account). I have configured the Distributed Transaction Coordinator on the source (Win2003) system per Microsoft KB 839279 (this fixed problems with remote queries coming the other way from the SQL2000 system), and I've upgraded the system stored procedures on the target (SQL2000) system according to MS KB 906954. I also tried making the schema user a member of the sysadmin role, but that was disastrous, resulting in an instant R/3 crash (don't try this in production!), so I set it back the way it was (default).
What's really strange is no matter how I try this from outside the R/3 system, it works perfectly, but from within R/3 it does not. A search of SAP Notes, SDN forums, SAPFANS, Microsoft's KnowledgeBase, and MSDN Forums has not yielded quite the same problem (although that did lead me to learning about the "trustworthy" database property).
Any insight someone could offer on this thorny problem would be most appreciated.
Best regards,
Matt
Hi, Matt
The error you came across is a pure security problem, hence I suggest you post your question in SQL Security Forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1
Good Luck!
Ming.
|||I originally posted this message to the Database Access forum, as I was thinking it might be related more to the methods used to access the database, as opposed to a pure security issue, as there does not appear to be an issue when using Microsoft native tools. However, now I am not so sure.
I have been working with SAP tech support on this issue, and this is what we've come up with so far.
The essential difference between running the distributed query from the Management Studio vs from within the R/3 application is the way the R/3 kernel establishes its security context when connecting to the database. The R/3 kernel connects to SQL Server as the NT user SAPServiceSID, and then executes a "SETUSER 'sid'" statement to establish the security context for the 'sid' schema in the database. The 'sid' database user has much reduced permissions from the SAPServiceSID NT user -- it is not a member of the sysadmin fixed server role, for instance -- thus adding to the overall security of the installation.
Normally "mixed mode" authentication is disabled on an R/3 installation, so it is not possible to logon interactively as the sid user. However, we have enabled it so that this linked server interface works coming the other direction (and that part does work), so I was able to connect directly as the 'sid' user and confirm that it CAN use the Linked Server. However, there is something inherently different about interactively logging on as the user vs switching to the user's context with a SETUSER or EXECUTE AS statement.
I have seen posts in other forums discussing the use of distributed transactions and the EXECUTE AS context switch. The essential problem, however, is that from within ABAP we cannot execute SETUSER or EXECUTE AS without causing R/3 to crash. We also cannot modify the permissions given to the 'sid' database user (like making it a sysadmin) without causing the same disastrous result, although I'm not certain why this last should be the case. We have been experimenting with exporting our Native SQL statements into a Stored Procedure with the EXECUTE AS context set on the procedure, but it hasn't worked. We still get the 15274 error ("Security Context Not Trusted") when we call the SP from within ABAP.
Regards,
Matt
|||SETUSER cannot interact with linked servers and in SQL Server 2005 is marked as deprecated. In SQL Server 2005 we introduced a new EXECUTE AS and it has two different scopes: database or server.
The database scoped model is accessed via EXECUTE AS USER or by using the EXECUTE AS clause in a DB-scoped module (i.e. a SP, function, etc.). Because this model is permission based, by default the impersonated context is bound to the database where the impersonation took place, making this context invalid when you try to access other databases or when trying to access server resources (such as linked server information).
The server scoped model is accessed via EXECUTE AS LOGIN or by using EXECUTE AS on server scoped modules (server scoped DDL triggers). This impersonation is implicitly by the server, therefore the impersonated context is valid to access any database and server resources.
My guess is that you are using one of the database-scoped EXECUTE AS features and that’s why you are hitting this problem, can you please verify if this is correct, and in that case try using EXECUTE AS LOGIN instead? It should work with linked servers as long as long as the impersonated context is not a Windows account.
Please, let us know if this information was useful.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||Raul,
Thanks for this clarification. This is helpful, although it doesn't solve the problem. So, apparently we cannot define a Stored Procedure with a server-scoped execution context (CREATE PROC xxx WITH EXECUTE AS LOGIN = 'login' is apparently not legitimate). However it does seem that we can insert an EXECUTE AS LOGIN = 'login' statement within the body of the SP.
Nevetheless, either way, the stored procedure works fine when executed from a Management Studio query window. It does not work when called from the R/3 application.
So, to recap, the R/3 application connects to the database first as a Windows user (SAPServiceD02), and then behind the scenes (transparent to the user and any programs we might write within R/3) it executes a SETUSER 'd02' statement. d02 is a SQL login, and all the user tables are in the d02 schema in the D02 database. This behavior is built into the R/3 kernel and we cannot change that. Although we are running on SQL Server 2005, the application is built to run on either 2005 or 2000.
From SQL tools such as Management Studio, both d02 and SAPServiceD02 can access the linked server. Both have a login mapping defined to a SQL login on the remote server. Likewise, using a Stored Procedure saved in the d02 schema with EXECUTE AS 'linkadm' (linkadm is a SQL user mapped to the dbo schema in both the D02 and master databases, a member of sysadmin, and also mapped in the Linked Server to the remote user), both d02 and SAPServiceD02, and indeed other sysadmin users, can access the Linked Server when executing the SP from Management Studio.
When calling the SP from within the R/3 application, however, which is using the same security credentials as just described, but has that implicit SETUSER 'd02' context, calling the SP doesn't get around the fact that we can't access the Linked Server. We still get the 15274 error, just like when we tried to call the Linked Server directly from within R/3. All of this worked very well when the DBMS was SQL 2000 and the schema was dbo. After upgrading to 2005 and migrating to the d02 schema, our troubles began. The essential problem appears to be the combination of Windows Authentication, schema-based installation with the schema based on a non-Windows, non-sysadmin SQL login name, and distributed transactions.
I think at this point (GoLive is just a few days away, and this is a potential showstopper) we must find some completely different way of executing these distributed transactions, and then hope that in the future there will be a fix, whether from Microsoft or SAP I don't know, that will allow us to revert to a more robust and integrated method. Meanwhile, the users will just have to ignore the man behind the curtain pulling the strings to make this work.
Best regards,
Matt
|||You mentioned updating the R/3 application. Was it performing a SETUSER in the previous version as well?
Thanks
Laurentiu
Laurentiu,
No, I don't believe it was, unless you had an MCOD (Multiple Components One Database) installation, which was not usual. In prior releases the standard installation had all the user tables in the dbo schema. With the most recent releases, however, SAP is moving towards schema-based installations. I believe this might be for multiple reasons, including supporting having multiple applications running in a single database, but also to increase security by having the connections be made using a schema user that normally cannot be interactively logged on (by default, an R/3 installation uses Windows Authentication only, so SQL logins are disabled), and that does not have to be a member of sysadmin. However, I cannot be certain what all the reasons for the switch may be.
Regards,
Matt
|||SETUSER will not work with linked servers. This has been the case in SQL Server 2000 as well, so this issue appears to be hit because the R/3 application changed to perform a SETUSER. I believe that if it would have used an EXECUTE AS LOGIN instead, the issue would have been avoided, but with SETUSER, you are bound to get the 15274 error.
I suggest you contact the vendor of the R/3 application to get further help on this.
Thanks
Laurentiu
Yes, we had been working with SAP (the vendor) from the beginning to resolve this issue, and although they were working diligently with us, a resolution did not look to be forthcoming before our GoLive deadline. In fact, I believe they escalated the issue to Microsoft themselves. Nevertheless, I posted here hoping against hope for something that might help us out.
However, we have now resolved the issue, although in a roundabout way, and not one that will work in a wide array of customer scenarios. The solution depended upon the fact that the remote server, being still on SQL Server 2000, did not have as many restrictions placed upon it with regard to the use of Linked Servers -- at least, the Linked Server connection from the remote system back to SAP continued to work just fine, despite SAP having been upgraded to SQL 2005. Additionally, SAP does provide a native capability to create a client-based connection to remote databases from within their application. It doesn't use Linked Servers, but rather opens up a SQLNCLI-based connection from the SAP server to the remote server, and allows the developer to execute Native SQL code against the remote database. This capability is referred to as DBCON with SAP. The limitation on DBCON, unfortunately, is that when working within this connection, the context is entirely that of the remote database, so access to local (SAP) tables is not available while in that context.
However, access to the Linked Server defined on the remote system is available.
So, we are able to perform distributed transactions between the two systems by executing them in the context of the remote system, while still controlling that execution from the SAP system.
I thank everyone for their suggestions and assistance while we worked on this issue.
Best regards,
Matt
|||We experienced similar problems using Linked Server in combination with user context switching at a customer's site, although no SAP was involved. The scenario was getting data from Win2k3 SQL 2005 (target) by switching a user context to a domain user on another Win2k3 SQL 2005 (source). Logging on directly to the target with the domain user and getting the was no problem, also getting data with a sysadmin user on the target server via linked server on the source server actually worked. The source database also had the trustworthy property set.
The problem only occurred when connecting to the source server, switching the user context with "execute as user ..." and trying to get data from the target via linked server, which produced a connection error. What finally solved the problems was setting the remote connection properties in the surface area config to both TCP/IP and named-pipes, since the target only had tcp-ip activated. In the current config both protocols are activated on source and target server and now everything seems to work. Maybe these additional bits of experience are helpful to those struggling with similar problems.
sqlCurrent Security Context Not Trusted When Using Linked Server From SAP
Hello,
I am experiencing a head-scratcher of a problem when trying to use a Linked Server connection to query a remote SQL Server database from our SAP R/3 system. We have had this working just fine for some time, but after migrating to new hardware and upgrading OS, DBMS, and R/3, now we are running into problems.
The target database is a named instance on SQL Server 2000 SP3, Windows 2000 Server. The original source R/3 system was also on SQL Server 2000 (SP4), Windows 2000 Server. I had been using a Linked Server defined via SQL Enterprise Manager (actually defined when the source was on SQL Server 7), which called an alias defined with the Client Network Utility that pointed to the remote named instance. This alias and Linked Server worked great for several years.
Now we have migrated our R/3 system onto new hardware, running Windows Server 2003 SP1 and SQL Server 2005 SP1. I redefined the Linked Server on the new SQL 2005 installation, this time avoiding the alias and referencing the remote named instance directly, and it tests out just fine using queries from SQL Management Studio. It also tests fine with OSQL called from the R/3 server console, both when logged on as the application service account with a trusted connection, and with a SQL login as the schema owner. From outside of the application, I cannot make it fail. It works perfectly.
That all changes when I try to use the Linked Server within an SAP custom program (ABAP), however. The program crashes with a database interface error. The database error code is 15274, and the error text is "Access to the remote server is denied because the current security context is not trusted."
I have set the "trustworthy" property on the R/3 database, I have ensured the service account is a member of the sysadmin SQL role, I've even made it a member of the local Administrators group on both source and target servers, and I've done the same with the SQL Server service account (it uses a domain account). I have configured the Distributed Transaction Coordinator on the source (Win2003) system per Microsoft KB 839279 (this fixed problems with remote queries coming the other way from the SQL2000 system), and I've upgraded the system stored procedures on the target (SQL2000) system according to MS KB 906954. I also tried making the schema user a member of the sysadmin role, but that was disastrous, resulting in an instant R/3 crash (don't try this in production!), so I set it back the way it was (default).
What's really strange is no matter how I try this from outside the R/3 system, it works perfectly, but from within R/3 it does not. A search of SAP Notes, SDN forums, SAPFANS, Microsoft's KnowledgeBase, and MSDN Forums has not yielded quite the same problem (although that did lead me to learning about the "trustworthy" database property).
Any insight someone could offer on this thorny problem would be most appreciated.
Best regards,
Matt
Hi, Matt
The error you came across is a pure security problem, hence I suggest you post your question in SQL Security Forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1
Good Luck!
Ming.
|||I originally posted this message to the Database Access forum, as I was thinking it might be related more to the methods used to access the database, as opposed to a pure security issue, as there does not appear to be an issue when using Microsoft native tools. However, now I am not so sure.
I have been working with SAP tech support on this issue, and this is what we've come up with so far.
The essential difference between running the distributed query from the Management Studio vs from within the R/3 application is the way the R/3 kernel establishes its security context when connecting to the database. The R/3 kernel connects to SQL Server as the NT user SAPServiceSID, and then executes a "SETUSER 'sid'" statement to establish the security context for the 'sid' schema in the database. The 'sid' database user has much reduced permissions from the SAPServiceSID NT user -- it is not a member of the sysadmin fixed server role, for instance -- thus adding to the overall security of the installation.
Normally "mixed mode" authentication is disabled on an R/3 installation, so it is not possible to logon interactively as the sid user. However, we have enabled it so that this linked server interface works coming the other direction (and that part does work), so I was able to connect directly as the 'sid' user and confirm that it CAN use the Linked Server. However, there is something inherently different about interactively logging on as the user vs switching to the user's context with a SETUSER or EXECUTE AS statement.
I have seen posts in other forums discussing the use of distributed transactions and the EXECUTE AS context switch. The essential problem, however, is that from within ABAP we cannot execute SETUSER or EXECUTE AS without causing R/3 to crash. We also cannot modify the permissions given to the 'sid' database user (like making it a sysadmin) without causing the same disastrous result, although I'm not certain why this last should be the case. We have been experimenting with exporting our Native SQL statements into a Stored Procedure with the EXECUTE AS context set on the procedure, but it hasn't worked. We still get the 15274 error ("Security Context Not Trusted") when we call the SP from within ABAP.
Regards,
Matt
|||SETUSER cannot interact with linked servers and in SQL Server 2005 is marked as deprecated. In SQL Server 2005 we introduced a new EXECUTE AS and it has two different scopes: database or server.
The database scoped model is accessed via EXECUTE AS USER or by using the EXECUTE AS clause in a DB-scoped module (i.e. a SP, function, etc.). Because this model is permission based, by default the impersonated context is bound to the database where the impersonation took place, making this context invalid when you try to access other databases or when trying to access server resources (such as linked server information).
The server scoped model is accessed via EXECUTE AS LOGIN or by using EXECUTE AS on server scoped modules (server scoped DDL triggers). This impersonation is implicitly by the server, therefore the impersonated context is valid to access any database and server resources.
My guess is that you are using one of the database-scoped EXECUTE AS features and that’s why you are hitting this problem, can you please verify if this is correct, and in that case try using EXECUTE AS LOGIN instead? It should work with linked servers as long as long as the impersonated context is not a Windows account.
Please, let us know if this information was useful.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||Raul,
Thanks for this clarification. This is helpful, although it doesn't solve the problem. So, apparently we cannot define a Stored Procedure with a server-scoped execution context (CREATE PROC xxx WITH EXECUTE AS LOGIN = 'login' is apparently not legitimate). However it does seem that we can insert an EXECUTE AS LOGIN = 'login' statement within the body of the SP.
Nevetheless, either way, the stored procedure works fine when executed from a Management Studio query window. It does not work when called from the R/3 application.
So, to recap, the R/3 application connects to the database first as a Windows user (SAPServiceD02), and then behind the scenes (transparent to the user and any programs we might write within R/3) it executes a SETUSER 'd02' statement. d02 is a SQL login, and all the user tables are in the d02 schema in the D02 database. This behavior is built into the R/3 kernel and we cannot change that. Although we are running on SQL Server 2005, the application is built to run on either 2005 or 2000.
From SQL tools such as Management Studio, both d02 and SAPServiceD02 can access the linked server. Both have a login mapping defined to a SQL login on the remote server. Likewise, using a Stored Procedure saved in the d02 schema with EXECUTE AS 'linkadm' (linkadm is a SQL user mapped to the dbo schema in both the D02 and master databases, a member of sysadmin, and also mapped in the Linked Server to the remote user), both d02 and SAPServiceD02, and indeed other sysadmin users, can access the Linked Server when executing the SP from Management Studio.
When calling the SP from within the R/3 application, however, which is using the same security credentials as just described, but has that implicit SETUSER 'd02' context, calling the SP doesn't get around the fact that we can't access the Linked Server. We still get the 15274 error, just like when we tried to call the Linked Server directly from within R/3. All of this worked very well when the DBMS was SQL 2000 and the schema was dbo. After upgrading to 2005 and migrating to the d02 schema, our troubles began. The essential problem appears to be the combination of Windows Authentication, schema-based installation with the schema based on a non-Windows, non-sysadmin SQL login name, and distributed transactions.
I think at this point (GoLive is just a few days away, and this is a potential showstopper) we must find some completely different way of executing these distributed transactions, and then hope that in the future there will be a fix, whether from Microsoft or SAP I don't know, that will allow us to revert to a more robust and integrated method. Meanwhile, the users will just have to ignore the man behind the curtain pulling the strings to make this work.
Best regards,
Matt
|||You mentioned updating the R/3 application. Was it performing a SETUSER in the previous version as well?
Thanks
Laurentiu
Laurentiu,
No, I don't believe it was, unless you had an MCOD (Multiple Components One Database) installation, which was not usual. In prior releases the standard installation had all the user tables in the dbo schema. With the most recent releases, however, SAP is moving towards schema-based installations. I believe this might be for multiple reasons, including supporting having multiple applications running in a single database, but also to increase security by having the connections be made using a schema user that normally cannot be interactively logged on (by default, an R/3 installation uses Windows Authentication only, so SQL logins are disabled), and that does not have to be a member of sysadmin. However, I cannot be certain what all the reasons for the switch may be.
Regards,
Matt
|||SETUSER will not work with linked servers. This has been the case in SQL Server 2000 as well, so this issue appears to be hit because the R/3 application changed to perform a SETUSER. I believe that if it would have used an EXECUTE AS LOGIN instead, the issue would have been avoided, but with SETUSER, you are bound to get the 15274 error.
I suggest you contact the vendor of the R/3 application to get further help on this.
Thanks
Laurentiu
Yes, we had been working with SAP (the vendor) from the beginning to resolve this issue, and although they were working diligently with us, a resolution did not look to be forthcoming before our GoLive deadline. In fact, I believe they escalated the issue to Microsoft themselves. Nevertheless, I posted here hoping against hope for something that might help us out.
However, we have now resolved the issue, although in a roundabout way, and not one that will work in a wide array of customer scenarios. The solution depended upon the fact that the remote server, being still on SQL Server 2000, did not have as many restrictions placed upon it with regard to the use of Linked Servers -- at least, the Linked Server connection from the remote system back to SAP continued to work just fine, despite SAP having been upgraded to SQL 2005. Additionally, SAP does provide a native capability to create a client-based connection to remote databases from within their application. It doesn't use Linked Servers, but rather opens up a SQLNCLI-based connection from the SAP server to the remote server, and allows the developer to execute Native SQL code against the remote database. This capability is referred to as DBCON with SAP. The limitation on DBCON, unfortunately, is that when working within this connection, the context is entirely that of the remote database, so access to local (SAP) tables is not available while in that context.
However, access to the Linked Server defined on the remote system is available.
So, we are able to perform distributed transactions between the two systems by executing them in the context of the remote system, while still controlling that execution from the SAP system.
I thank everyone for their suggestions and assistance while we worked on this issue.
Best regards,
Matt
|||We experienced similar problems using Linked Server in combination with user context switching at a customer's site, although no SAP was involved. The scenario was getting data from Win2k3 SQL 2005 (target) by switching a user context to a domain user on another Win2k3 SQL 2005 (source). Logging on directly to the target with the domain user and getting the was no problem, also getting data with a sysadmin user on the target server via linked server on the source server actually worked. The source database also had the trustworthy property set.
The problem only occurred when connecting to the source server, switching the user context with "execute as user ..." and trying to get data from the target via linked server, which produced a connection error. What finally solved the problems was setting the remote connection properties in the surface area config to both TCP/IP and named-pipes, since the target only had tcp-ip activated. In the current config both protocols are activated on source and target server and now everything seems to work. Maybe these additional bits of experience are helpful to those struggling with similar problems.
Sunday, March 11, 2012
Cumulative update packages for Post-SP2 changes in SQL2005
Hi !
I see there are quite a few cumulated post-SP2 packages that have been
built, some of which fix errors that I have been experiencing in my
environment.
I have doubts if that's good practice to apply those cumulative packages to
my life production servers... is it ?
Any idea on how long we have to wait until SP3 will be built ?
Palli
Hi
I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
you to contact PSS for them. In general hotfixes are cumulative so you only
need to apply the last one.
John
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:etaKos7cIHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hi !
> I see there are quite a few cumulated post-SP2 packages that have been
> built, some of which fix errors that I have been experiencing in my
> environment.
> I have doubts if that's good practice to apply those cumulative packages
> to my life production servers... is it ?
> Any idea on how long we have to wait until SP3 will be built ?
> Palli
>
|||>
> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
> you to contact PSS for them. In general hotfixes are cumulative so you
> only need to apply the last one.
>
I did contact MS about the cumulative packages, and I did receive one from
them for 1 or 2 weeks ago, I think it's number 5.
1) I haven't had the guts yet to apply them, so the question is, should I
apply them to my production servers ?
2) Will I have to uninstall them before applying SP3 ?
Palli
|||Hi
You should apply any service pack or hotfixes to a test machine and make
sure they work as expected and don't break anything else, once satisfied you
can then apply them to a production system, but make sure that you have a
rollback process and recovery procedures in place to copy with any
eventuality.
You should be able to apply any new hotfix/service pack ontop of the one you
have providing the version number is higher.
John
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:eQEm4A8cIHA.2268@.TK2MSFTNGP02.phx.gbl...
> I did contact MS about the cumulative packages, and I did receive one from
> them for 1 or 2 weeks ago, I think it's number 5.
> 1) I haven't had the guts yet to apply them, so the question is, should I
> apply them to my production servers ?
> 2) Will I have to uninstall them before applying SP3 ?
> Palli
>
|||Palli,
If you are registered on Connect then vote here:-
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575
We are running CU2 on one production system and are looking at upgrading to
either CU5 or CU6 but only after fully testing on Development and Acceptance
servers first. On one server we get mini dumps associated with a fix
included in CU4.
Chris
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:eQEm4A8cIHA.2268@.TK2MSFTNGP02.phx.gbl...
> I did contact MS about the cumulative packages, and I did receive one from
> them for 1 or 2 weeks ago, I think it's number 5.
> 1) I haven't had the guts yet to apply them, so the question is, should I
> apply them to my production servers ?
> 2) Will I have to uninstall them before applying SP3 ?
> Palli
>
I see there are quite a few cumulated post-SP2 packages that have been
built, some of which fix errors that I have been experiencing in my
environment.
I have doubts if that's good practice to apply those cumulative packages to
my life production servers... is it ?
Any idea on how long we have to wait until SP3 will be built ?
Palli
Hi
I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
you to contact PSS for them. In general hotfixes are cumulative so you only
need to apply the last one.
John
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:etaKos7cIHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hi !
> I see there are quite a few cumulated post-SP2 packages that have been
> built, some of which fix errors that I have been experiencing in my
> environment.
> I have doubts if that's good practice to apply those cumulative packages
> to my life production servers... is it ?
> Any idea on how long we have to wait until SP3 will be built ?
> Palli
>
|||>
> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
> you to contact PSS for them. In general hotfixes are cumulative so you
> only need to apply the last one.
>
I did contact MS about the cumulative packages, and I did receive one from
them for 1 or 2 weeks ago, I think it's number 5.
1) I haven't had the guts yet to apply them, so the question is, should I
apply them to my production servers ?
2) Will I have to uninstall them before applying SP3 ?
Palli
|||Hi
You should apply any service pack or hotfixes to a test machine and make
sure they work as expected and don't break anything else, once satisfied you
can then apply them to a production system, but make sure that you have a
rollback process and recovery procedures in place to copy with any
eventuality.
You should be able to apply any new hotfix/service pack ontop of the one you
have providing the version number is higher.
John
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:eQEm4A8cIHA.2268@.TK2MSFTNGP02.phx.gbl...
> I did contact MS about the cumulative packages, and I did receive one from
> them for 1 or 2 weeks ago, I think it's number 5.
> 1) I haven't had the guts yet to apply them, so the question is, should I
> apply them to my production servers ?
> 2) Will I have to uninstall them before applying SP3 ?
> Palli
>
|||Palli,
If you are registered on Connect then vote here:-
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575
We are running CU2 on one production system and are looking at upgrading to
either CU5 or CU6 but only after fully testing on Development and Acceptance
servers first. On one server we get mini dumps associated with a fix
included in CU4.
Chris
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:eQEm4A8cIHA.2268@.TK2MSFTNGP02.phx.gbl...
> I did contact MS about the cumulative packages, and I did receive one from
> them for 1 or 2 weeks ago, I think it's number 5.
> 1) I haven't had the guts yet to apply them, so the question is, should I
> apply them to my production servers ?
> 2) Will I have to uninstall them before applying SP3 ?
> Palli
>
Cumulative update packages for Post-SP2 changes in SQL2005
Hi !
I see there are quite a few cumulated post-SP2 packages that have been
built, some of which fix errors that I have been experiencing in my
environment.
I have doubts if that's good practice to apply those cumulative packages to
my life production servers... is it ?
Any idea on how long we have to wait until SP3 will be built ?
PalliHi
I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
you to contact PSS for them. In general hotfixes are cumulative so you only
need to apply the last one.
John
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:etaKos7cIHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hi !
> I see there are quite a few cumulated post-SP2 packages that have been
> built, some of which fix errors that I have been experiencing in my
> environment.
> I have doubts if that's good practice to apply those cumulative packages
> to my life production servers... is it ?
> Any idea on how long we have to wait until SP3 will be built ?
> Palli
>|||>
> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
> you to contact PSS for them. In general hotfixes are cumulative so you
> only need to apply the last one.
>
I did contact MS about the cumulative packages, and I did receive one from
them for 1 or 2 weeks ago, I think it's number 5.
1) I haven't had the guts yet to apply them, so the question is, should I
apply them to my production servers ?
2) Will I have to uninstall them before applying SP3 ?
Palli|||Hi
You should apply any service pack or hotfixes to a test machine and make
sure they work as expected and don't break anything else, once satisfied you
can then apply them to a production system, but make sure that you have a
rollback process and recovery procedures in place to copy with any
eventuality.
You should be able to apply any new hotfix/service pack ontop of the one you
have providing the version number is higher.
John
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:eQEm4A8cIHA.2268@.TK2MSFTNGP02.phx.gbl...
> >
>> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
>> you to contact PSS for them. In general hotfixes are cumulative so you
>> only need to apply the last one.
> I did contact MS about the cumulative packages, and I did receive one from
> them for 1 or 2 weeks ago, I think it's number 5.
> 1) I haven't had the guts yet to apply them, so the question is, should I
> apply them to my production servers ?
> 2) Will I have to uninstall them before applying SP3 ?
> Palli
>|||Palli,
If you are registered on Connect then vote here:-
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575
We are running CU2 on one production system and are looking at upgrading to
either CU5 or CU6 but only after fully testing on Development and Acceptance
servers first. On one server we get mini dumps associated with a fix
included in CU4.
Chris
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:eQEm4A8cIHA.2268@.TK2MSFTNGP02.phx.gbl...
> >
>> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
>> you to contact PSS for them. In general hotfixes are cumulative so you
>> only need to apply the last one.
> I did contact MS about the cumulative packages, and I did receive one from
> them for 1 or 2 weeks ago, I think it's number 5.
> 1) I haven't had the guts yet to apply them, so the question is, should I
> apply them to my production servers ?
> 2) Will I have to uninstall them before applying SP3 ?
> Palli
>|||On Wed, 20 Feb 2008 12:21:42 -0000, Pall Bjornsson wrote:
>Hi !
>I see there are quite a few cumulated post-SP2 packages that have been
>built, some of which fix errors that I have been experiencing in my
>environment.
>I have doubts if that's good practice to apply those cumulative packages to
>my life production servers... is it ?
>Any idea on how long we have to wait until SP3 will be built ?
Hi Palli,
Read
http://sqlblog.com/blogs/hugo_kornelis/archive/2008/02/01/want-a-service-pack-ask-for-it.aspx
Then make yourself heard at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
I see there are quite a few cumulated post-SP2 packages that have been
built, some of which fix errors that I have been experiencing in my
environment.
I have doubts if that's good practice to apply those cumulative packages to
my life production servers... is it ?
Any idea on how long we have to wait until SP3 will be built ?
PalliHi
I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
you to contact PSS for them. In general hotfixes are cumulative so you only
need to apply the last one.
John
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:etaKos7cIHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hi !
> I see there are quite a few cumulated post-SP2 packages that have been
> built, some of which fix errors that I have been experiencing in my
> environment.
> I have doubts if that's good practice to apply those cumulative packages
> to my life production servers... is it ?
> Any idea on how long we have to wait until SP3 will be built ?
> Palli
>|||>
> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
> you to contact PSS for them. In general hotfixes are cumulative so you
> only need to apply the last one.
>
I did contact MS about the cumulative packages, and I did receive one from
them for 1 or 2 weeks ago, I think it's number 5.
1) I haven't had the guts yet to apply them, so the question is, should I
apply them to my production servers ?
2) Will I have to uninstall them before applying SP3 ?
Palli|||Hi
You should apply any service pack or hotfixes to a test machine and make
sure they work as expected and don't break anything else, once satisfied you
can then apply them to a production system, but make sure that you have a
rollback process and recovery procedures in place to copy with any
eventuality.
You should be able to apply any new hotfix/service pack ontop of the one you
have providing the version number is higher.
John
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:eQEm4A8cIHA.2268@.TK2MSFTNGP02.phx.gbl...
> >
>> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
>> you to contact PSS for them. In general hotfixes are cumulative so you
>> only need to apply the last one.
> I did contact MS about the cumulative packages, and I did receive one from
> them for 1 or 2 weeks ago, I think it's number 5.
> 1) I haven't had the guts yet to apply them, so the question is, should I
> apply them to my production servers ?
> 2) Will I have to uninstall them before applying SP3 ?
> Palli
>|||Palli,
If you are registered on Connect then vote here:-
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575
We are running CU2 on one production system and are looking at upgrading to
either CU5 or CU6 but only after fully testing on Development and Acceptance
servers first. On one server we get mini dumps associated with a fix
included in CU4.
Chris
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:eQEm4A8cIHA.2268@.TK2MSFTNGP02.phx.gbl...
> >
>> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
>> you to contact PSS for them. In general hotfixes are cumulative so you
>> only need to apply the last one.
> I did contact MS about the cumulative packages, and I did receive one from
> them for 1 or 2 weeks ago, I think it's number 5.
> 1) I haven't had the guts yet to apply them, so the question is, should I
> apply them to my production servers ?
> 2) Will I have to uninstall them before applying SP3 ?
> Palli
>|||On Wed, 20 Feb 2008 12:21:42 -0000, Pall Bjornsson wrote:
>Hi !
>I see there are quite a few cumulated post-SP2 packages that have been
>built, some of which fix errors that I have been experiencing in my
>environment.
>I have doubts if that's good practice to apply those cumulative packages to
>my life production servers... is it ?
>Any idea on how long we have to wait until SP3 will be built ?
Hi Palli,
Read
http://sqlblog.com/blogs/hugo_kornelis/archive/2008/02/01/want-a-service-pack-ask-for-it.aspx
Then make yourself heard at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Thursday, March 8, 2012
Cubes and Cost-Forecast
Hi,
I'm experiencing trouble with the following requirement:
We have a fact-table containing data about cost-forecasts. This table includes columns for the project-name, for the date the forecast was made, the date the forecast is for and the estimated costs. This could be an example for this table:
projekt, date_of_forecast, date, costs
PR-A, 2007-06-01, 2007-07-01, 2000
PR-A, 2007-06-05, 2007-07-01, 3000
PR-A, 2007-06-10, 2007-07-01, 2500
PR-A, 2007-06-10, 2007-07-15, 2000
For instance, the last row says: We estimated on 2007-06-10 that the costs for Project PR-A will be 2000 at 2007-07-15.
The customer wants a cube wich allows an answer to the following question:
Wich cost did we expect for [Projekt] .... at [date] on [date_of_forecast] ?
So, it's not possible to just sum up all the measures, but I have to look up the last cost behind [date_of_forecast] and [date] ...
How to accomplish the using SQL Server 2005 STANDARD-EDITION ?
Do I need two Time-Dimensions or just one?
Is there a way to create a Cube-Skript for this requirement?
Thanks and best whishes
Manfred
Dear Friend,
You must have a Time dimension and a Project Dimension and you FactTable that could be as you wrote...
Look for the follow post in my blog that could help you... (see the MDX query... I think you can apply to your case, but only seing more carefully)
http://pedrocgd.blogspot.com/2007/07/ssas-slowly-changing-values.html
I hope this helped you!
regards!
|||Hi,
I've tried this. But SSAS seems to get into an infinity-recursion ...
How to work around this issue?
Best Whishes,
Manfred
|||Can you post the calculation code? The usual problem with an infinite recursion is a missing measure reference somewhere.|||Here it is:
iif ( not isEmpty([Measures].[Gewinn]),
[Measures].[Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)
)
)
Btw: I also use currency conversions created by the wizzard. The code for the currency comes first; then this calculation comes ...
Best wishes,
Manfred
|||
You did not include the CREATE MEASURE statement. Is this the code for the [Gewinn] measure or the [Geplanter Gewinn]. I am guessing that it is for the [Gewinn] measure as this would cause an infinite recursion. If I have guessed correctly it is simply that you have transposed the two measures from Pedro's example, try the following
Code Snippet
iif ( not isEmpty([Measures].[Geplanter Gewinn]),
[Measures].[Geplanter Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Gewinn], [Time].[Date].PrevMember)
)
)
In fact we should be able to simplify the whole thing, removing the "if null return null" section and ending up with the following:
Code Snippet
iif ( not isEmpty([Measures].[Geplanter Gewinn])
,([Measures].[Geplanter Gewinn])
,([Time].[Date].PrevMember)
)
Which says
1. If Geplanter Gewinn is not empty return that
2. Else return the value of this calculation for the previous time member (which is where the recursion comes in). So this calculation will keep searching back until it finds a nonEmpty value of Geplanter Gewinn.
|||Hi,
I think, there is a missunderstanding.
[Gewinn] is the Measure in the Cube and has a value for some days.
[Geplanter Gewinn] should return the last non empty value of [Gewinn]
So, the right syntax should be:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Geplanter Gewinn]
AS
iif ( not isEmpty([Measures].[Gewinn]), -- If there is a [Gewinn] for the current day
[Measures].[Gewinn], -- return it
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember) -- Go back one day and retry it
)
)
But this ends up in an infinity-recursion ...
Wishes,
Manfred
|||Dear ManfredSteyer,
The statment in my blog works perfectly... are you sure you saw it right?
Check this:
Code Snippet
'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),[Measures].[ENT_Racio]
,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]
,[DimTime].[Dia].PrevMember)
)
)'
I hope you get it!!!
Regards!
|||Sorry, without the CREATE MEMBER clause I could not tell which was the measure in the cube. In that case it looks OK. I'm wondering if there is something else in your calculation script that might be conflicting.
Are you able to use this type of calculation in an MDX query using the "WITH MEMBER" clause?
|||for you both:
Code Snippet
CREATE MEMBER CURRENTCUBE.[MEASURES].CM_PRM_ENT_Racio
AS 'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),
[Measures].[ENT_Racio]
,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]
,[DimTime].[Dia].PrevMember)
)
)',
VISIBLE = 1;
Helped?
This for me works!
Regards!
|||Hi,
I exactly used this pattern - I also looked up "MDX Solutions" (Wiley) ...
And if I use it directly within a mdx-query, it works too:
with member [Measures].[Geplanter Gewinn]
as
iif ( not isEmpty([Measures].[Gewinn]),
[Measures].[Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)
)
)
select [Measures].[Geplanter Gewinn] on 0
from [Kostenrechnung Sample Db]
where [Time].[Date].&[2007-06-15T00:00:00]
But when I used it as calculated member or within a cube-script, I get an inifinity-recursion ...
Have you used this pattern within cube-script/ as calc. meber or "just" as mdx-query ?
Regards,
Manfred
|||
I use it in a CM...
|||I can't see anything wrong with the implementation of this CM. I think the fact that it works inline in a query proves that there is nothing with it on it's own. There must be a circular dependancy somewhere in the calculation script.
In order to figure this out you could either set a breakpoint in the MDX Script and use the debugger. Stepping through the script until the calc does not work. If it does not work as soon as you hit it with the debugger then there must be something earlier in the script that is upsetting it, but my guess is that it might be something after it in the script. The other approach which you could either use on it's own or in conjunction with the debugger would be to comment out blocks of the script until you isolate what is causing the issue.
|||Hi,
Now, I figured out, that there is not an inifinity-recursion but a realy time consuming recursion. But I can not imagine why this takes that long, cause it's just a proof-of-conecpt project with very few data (~ 15 rows) and a small time-dimension (Jan/2006 - Dec/2007).
Best Whishes,
Manfred
ps.: Perhaps I sould try enterprise edition ...
|||Yeah, sometimes depending in the projectsm could take lot of time! :-(
mark your answer to resolved!
Kind Regards!
Cubes and Cost-Forecast
Hi,
I'm experiencing trouble with the following requirement:
We have a fact-table containing data about cost-forecasts. This table includes columns for the project-name, for the date the forecast was made, the date the forecast is for and the estimated costs. This could be an example for this table:
projekt, date_of_forecast, date, costs
PR-A, 2007-06-01, 2007-07-01, 2000
PR-A, 2007-06-05, 2007-07-01, 3000
PR-A, 2007-06-10, 2007-07-01, 2500
PR-A, 2007-06-10, 2007-07-15, 2000
For instance, the last row says: We estimated on 2007-06-10 that the costs for Project PR-A will be 2000 at 2007-07-15.
The customer wants a cube wich allows an answer to the following question:
Wich cost did we expect for [Projekt] .... at [date] on [date_of_forecast] ?
So, it's not possible to just sum up all the measures, but I have to look up the last cost behind [date_of_forecast] and [date] ...
How to accomplish the using SQL Server 2005 STANDARD-EDITION ?
Do I need two Time-Dimensions or just one?
Is there a way to create a Cube-Skript for this requirement?
Thanks and best whishes
Manfred
Dear Friend,
You must have a Time dimension and a Project Dimension and you FactTable that could be as you wrote...
Look for the follow post in my blog that could help you... (see the MDX query... I think you can apply to your case, but only seing more carefully)
http://pedrocgd.blogspot.com/2007/07/ssas-slowly-changing-values.html
I hope this helped you!
regards!
|||Hi,
I've tried this. But SSAS seems to get into an infinity-recursion ...
How to work around this issue?
Best Whishes,
Manfred
|||Can you post the calculation code? The usual problem with an infinite recursion is a missing measure reference somewhere.|||Here it is:
iif ( not isEmpty([Measures].[Gewinn]),
[Measures].[Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)
)
)
Btw: I also use currency conversions created by the wizzard. The code for the currency comes first; then this calculation comes ...
Best wishes,
Manfred
|||
You did not include the CREATE MEASURE statement. Is this the code for the [Gewinn] measure or the [Geplanter Gewinn]. I am guessing that it is for the [Gewinn] measure as this would cause an infinite recursion. If I have guessed correctly it is simply that you have transposed the two measures from Pedro's example, try the following
Code Snippet
iif ( not isEmpty([Measures].[Geplanter Gewinn]),
[Measures].[Geplanter Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Gewinn], [Time].[Date].PrevMember)
)
)
In fact we should be able to simplify the whole thing, removing the "if null return null" section and ending up with the following:
Code Snippet
iif ( not isEmpty([Measures].[Geplanter Gewinn])
,([Measures].[Geplanter Gewinn])
,([Time].[Date].PrevMember)
)
Which says
1. If Geplanter Gewinn is not empty return that
2. Else return the value of this calculation for the previous time member (which is where the recursion comes in). So this calculation will keep searching back until it finds a nonEmpty value of Geplanter Gewinn.
|||Hi,
I think, there is a missunderstanding.
[Gewinn] is the Measure in the Cube and has a value for some days.
[Geplanter Gewinn] should return the last non empty value of [Gewinn]
So, the right syntax should be:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Geplanter Gewinn]
AS
iif ( not isEmpty([Measures].[Gewinn]), -- If there is a [Gewinn] for the current day
[Measures].[Gewinn], -- return it
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember) -- Go back one day and retry it
)
)
But this ends up in an infinity-recursion ...
Wishes,
Manfred
|||Dear ManfredSteyer,
The statment in my blog works perfectly... are you sure you saw it right?
Check this:
Code Snippet
'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),[Measures].[ENT_Racio]
,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]
,[DimTime].[Dia].PrevMember)
)
)'
I hope you get it!!!
Regards!
|||Sorry, without the CREATE MEMBER clause I could not tell which was the measure in the cube. In that case it looks OK. I'm wondering if there is something else in your calculation script that might be conflicting.
Are you able to use this type of calculation in an MDX query using the "WITH MEMBER" clause?
|||for you both:
Code Snippet
CREATE MEMBER CURRENTCUBE.[MEASURES].CM_PRM_ENT_Racio
AS 'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),
[Measures].[ENT_Racio]
,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]
,[DimTime].[Dia].PrevMember)
)
)',
VISIBLE = 1;
Helped?
This for me works!
Regards!
|||Hi,
I exactly used this pattern - I also looked up "MDX Solutions" (Wiley) ...
And if I use it directly within a mdx-query, it works too:
with member [Measures].[Geplanter Gewinn]
as
iif ( not isEmpty([Measures].[Gewinn]),
[Measures].[Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)
)
)
select [Measures].[Geplanter Gewinn] on 0
from [Kostenrechnung Sample Db]
where [Time].[Date].&[2007-06-15T00:00:00]
But when I used it as calculated member or within a cube-script, I get an inifinity-recursion ...
Have you used this pattern within cube-script/ as calc. meber or "just" as mdx-query ?
Regards,
Manfred
|||
I use it in a CM...
|||I can't see anything wrong with the implementation of this CM. I think the fact that it works inline in a query proves that there is nothing with it on it's own. There must be a circular dependancy somewhere in the calculation script.
In order to figure this out you could either set a breakpoint in the MDX Script and use the debugger. Stepping through the script until the calc does not work. If it does not work as soon as you hit it with the debugger then there must be something earlier in the script that is upsetting it, but my guess is that it might be something after it in the script. The other approach which you could either use on it's own or in conjunction with the debugger would be to comment out blocks of the script until you isolate what is causing the issue.
|||Hi,
Now, I figured out, that there is not an inifinity-recursion but a realy time consuming recursion. But I can not imagine why this takes that long, cause it's just a proof-of-conecpt project with very few data (~ 15 rows) and a small time-dimension (Jan/2006 - Dec/2007).
Best Whishes,
Manfred
ps.: Perhaps I sould try enterprise edition ...
|||Yeah, sometimes depending in the projectsm could take lot of time! :-(
mark your answer to resolved!
Kind Regards!
Subscribe to:
Posts (Atom)