I want to create a user defined table with Foreign Key constrained to uid of
sysusers table. How can this constraint be enforced?
I am using Server Authentication on SQL Server 2000 v8.
Thank you for your assistance.I think you will need to use a trigger or self-coded "preventative" measures
because you cannot set up a constraint against a system table.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"wolfv" <wolfv@.discussions.microsoft.com> wrote in message
news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
>I want to create a user defined table with Foreign Key constrained to uid
>of
> sysusers table. How can this constraint be enforced?
> I am using Server Authentication on SQL Server 2000 v8.
> Thank you for your assistance.
>|||wolf
Why?Please explain what is your purpose?
"wolfv" <wolfv@.discussions.microsoft.com> wrote in message
news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
> I want to create a user defined table with Foreign Key constrained to uid
of
> sysusers table. How can this constraint be enforced?
> I am using Server Authentication on SQL Server 2000 v8.
> Thank you for your assistance.
>|||Hi
FK's can not span databases.
You need to enforce this through a trigger.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"wolfv" wrote:
> I want to create a user defined table with Foreign Key constrained to uid
of
> sysusers table. How can this constraint be enforced?
> I am using Server Authentication on SQL Server 2000 v8.
> Thank you for your assistance.
>|||The purpose of the FK constraint (which is not allowed) is to prevent orphan
s
in the userdefined table when someone attempts to delete a user from the
sysusers table.
"Uri Dimant" wrote:
> wolf
> Why?Please explain what is your purpose?
>
> "wolfv" <wolfv@.discussions.microsoft.com> wrote in message
> news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
> of
>
>|||
"Uri Dimant" wrote:
> wolf
> Why?Please explain what is your purpose?
>
> "wolfv" <wolfv@.discussions.microsoft.com> wrote in message
> news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
> of
>
>|||The sysusers table and the user-defined table reside in the same database.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> FK's can not span databases.
> You need to enforce this through a trigger.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "wolfv" wrote:
>|||Yes, I see what you mean. I found a place in SQL Server Manual that says
constraints against a system table are not permitted.
Thank you.
"AB - MVP" wrote:
> I think you will need to use a trigger or self-coded "preventative" measur
es
> because you cannot set up a constraint against a system table.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "wolfv" <wolfv@.discussions.microsoft.com> wrote in message
> news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
>
>
Showing posts with label constrained. Show all posts
Showing posts with label constrained. Show all posts
Sunday, February 12, 2012
constrained flag in the STRTOSET function violated
I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?
The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.
The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:
STRTOSET(@.yourDateParameter, CONSTRAINED)
change it to:
STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)
the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".
Hope this helps.
|||Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)
I must have done something wrong... please advise.
|||I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?
||| I am also using SSRS2005....|||Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:
WITH
MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'
SELECT {[Measures].[ParameterValue] } on columns,
{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows
FROM [MyCube]
The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.
SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS
FROM ( SELECT (
STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :
STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)
)
ON COLUMNS FROM [MyCube]
)
Hope this helps!
constrained flag in the STRTOSET function violated
I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?
The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.
The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:
STRTOSET(@.yourDateParameter, CONSTRAINED)
change it to:
STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)
the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".
Hope this helps.
|||Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)
I must have done something wrong... please advise.
|||I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?
||| I am also using SSRS2005....|||Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:
WITH
MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'
SELECT {[Measures].[ParameterValue] } on columns,
{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows
FROM [MyCube]
The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.
SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS
FROM ( SELECT (
STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :
STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)
)
ON COLUMNS FROM [MyCube]
)
Hope this helps!
Constrained Delegation for SSRS
I am having a problem implementing constrained delegation for SSRS. I have followed the (very good) instructions located here:
http://sqlblogcasts.com/blogs/stevechowles/archive/2007/06/08/reporting-services-2005-for-the-dba-iis-security.aspx
I have chosen the option of running the application pool for SSRS under a domain user account. This is the same account that I use to run the SSRS service.
I have the authentication providers for the site set to "Negotiate,NTLM".
I also made sure that the application pool user account has rights on the ReportManager and ReportServer directories.
If browse to the URL while logged on to the SSRS server then I am able to access the site
My problem is when I try to access the site from anywhere but locally on the SSRS server:
I get a logon prompt if I try to access the SSRS URL from a different workstation. After three tries to login I get: "You are not authorized to view this page". Even with an account that is local admin on the SSRS Server. If I set the authentication providers for the site to "NTLM" then I am able to access the site from a different workstation but of couse constrained delegation does not work.Have i overlooked something? What could be causing the login prompt?
Can you tell us a bit more about what you trying to achieve? Do you have an ASP.NET application which needs to integrate with SSRS with a trusted account? If so, you need to set the app pool of the application to the Windows account SSRS will trust.
|||Hello Teo,
I have solved the problem but am not sure I understand 100% why. I am using a host header for my SSRS site and I had created two SPNs for the host header. I then created two additional SPNs: One for the net bios name of the SSRS host and one for the FQDN of the host. This solved the problem. So I apparently need four SPNs for the SSRS site when using host headers...
BTW, I love your book on SSAS. Much better than one of the others by a well known publisher which I also bought.
Constrained Delegation for SSRS
I am having a problem implementing constrained delegation for SSRS. I have followed the (very good) instructions located here:
http://sqlblogcasts.com/blogs/stevechowles/archive/2007/06/08/reporting-services-2005-for-the-dba-iis-security.aspx
I have chosen the option of running the application pool for SSRS under a domain user account. This is the same account that I use to run the SSRS service.
I have the authentication providers for the site set to "Negotiate,NTLM".
I also made sure that the application pool user account has rights on the ReportManager and ReportServer directories.
If browse to the URL while logged on to the SSRS server then I am able to access the site
My problem is when I try to access the site from anywhere but locally on the SSRS server:
I get a logon prompt if I try to access the SSRS URL from a different workstation. After three tries to login I get: "You are not authorized to view this page". Even with an account that is local admin on the SSRS Server. If I set the authentication providers for the site to "NTLM" then I am able to access the site from a different workstation but of couse constrained delegation does not work.Have i overlooked something? What could be causing the login prompt?
Can you tell us a bit more about what you trying to achieve? Do you have an ASP.NET application which needs to integrate with SSRS with a trusted account? If so, you need to set the app pool of the application to the Windows account SSRS will trust.
|||Hello Teo,
I have solved the problem but am not sure I understand 100% why. I am using a host header for my SSRS site and I had created two SPNs for the host header. I then created two additional SPNs: One for the net bios name of the SSRS host and one for the FQDN of the host. This solved the problem. So I apparently need four SPNs for the SSRS site when using host headers...
BTW, I love your book on SSAS. Much better than one of the others by a well known publisher which I also bought.
Subscribe to:
Posts (Atom)