Role Playing Dimensions

What is the proper way to include the date dimension table into your DSV if you plan on using multiple role-playing dimensions? It seems if you can use this dimension as a role playing dimension, and assign the "join" using the Dimension Usage tab, then it doesn't need to be joined to the fact table in the DSV... is that correct? Or should you add the Date Dim table to the DSV and join it to every date-specific fact foreign keys? I see three possibilities for the DSV:

1) Include the Date Dim table but not join to any other table in the DSV

2) Include the Date Dim table and join to at least one fact foreign key

3) Include the Date Dim table and join to all fact foreign keys for dates in the DSV

Which method would be the most appropriate (or what are the consequences of each method)?

Also, if you use a role-playing dimension, is there anyway of manipulating the levels to display the type of date. For example, of the user picks the Order Date dimension, the year level says "Order Year" but if they use the Shipped Date dimension, the year level would say "Shipped Year"... etc.?

Thanks

Kory

Hi KoryS. Check the Adventure Works sample BI project that clearly shows that no 3 is the correct way of doing it. It is a very simple concept, instead of using three tables of views for time you join one table to the different time keys in the fact table. There have been some concerns regarding using role playing dimensions like here(http://mgarner.wordpress.com/2006/06/27/role-playing-dimensions-not-materialized/) but now it is OK regarding performance.

Regards

/Thomas Ivarsson

|||

Thanks- I'll follow the example from the Adventureworks db.

Do you know if I can rename some of the levels in my role-playing dimensions (see second half of my original thread)?

-Kory

|||

I think you will have to use the same names but you name each dimension like DueDate, OrderDate, ShipDate and so on. You can see this is in the Adventure Works AS2005 project.

Regards

/Thomas

sql

Role playing dimension and member naming question.

I have a fact table with invoice information that has multiple date columns.

I had originaly only needed to join my time dimension to this fact table on it's create date, but I have now added a role-playing dimension to join to the invoice date.

When I had 1 date dimension all of it's members where called 'week','year', 'day', etc.
Now that I have the role-playing dimension I have two dimensions with member names like 'Date.week', 'Date.year', 'Date.day', 'Invoice Date.week', 'Invoice Date.year', 'Invoice Date.day'.

So many queries I had written to reference the original date dimension no longer work because of the extra 'Date.' prefix. Is there a way to hide this prefix for my original date dimension?

Thanks in advance.

I had the same problem earlier on. I had to bite the bullet and change my MDX when I had multiple 'date' dimensions. Can you globally change the MDX or are you using a third party tool?

Unless someone knows better.

|||

I am using ProClarity as a front end to the cube. I have found a sort of work around, rather than creating a role-playing dimension. I created a whole new date dimension on the same date table and it doesn't mess up the naming. The only downside I can see to this right now is that if I have one date dimension on rows and one date dimension on columns your result could look like:

may june july august
may
june
july
august

so it's not really clear which dimension is where, but I could get around this by changing the names of the members in the new date dimension.

-Preston

|||

Hi,

Although this solution will ultimately work, I guess we are duplicating process and space by repeating a dimension.

I've not tested it thorougly yet, but, the cube dimension has a property named HierarchyUniqueNameStyle, that allows for two values: IncludeDimensionName and ExcludeDimensionName. Using the later in the "default" dimension, let's say "Date" vs. "Delivery Date", the MDX will run fine as it was originally, without need to edit all of them.

Jordi Rambla

SQL Server MVP

Certia (http://www.certia.net)

SolidQualityLearning (http://www.solidqualitylearning.com)

Role for access just information schema.

Which db fixes roles can I use to give a user permission to see all table
information but not the data itself ?
The db_datareader can select on everything, that's not what I want ! I want
to give access just to table information also, give access to the list of
logins and database users.
All readonly mode without seeing data.
Can anybody help on this ?
Thanks in advance,I just did a test that took me about 3 minutes. I added a login named
"test". I then added him as user to a db. No perimssions to do anything and
no roles involved. Just a user. I logged in as him. He can run sp_help, see
logins, and see db users. Am I not understanding your question correctly?
"Tim Conner" <timconner@.hotmail.com> wrote in message
news:#99Z6kPaDHA.1832@.TK2MSFTNGP10.phx.gbl...
> Which db fixes roles can I use to give a user permission to see all table
> information but not the data itself ?
> The db_datareader can select on everything, that's not what I want ! I
want
> to give access just to table information also, give access to the list of
> logins and database users.
> All readonly mode without seeing data.
> Can anybody help on this ?
>
> Thanks in advance,
>sql

Role design issue

I have a strange roles problem.

Is there a way we can accomplish this:

Say, the fact table has 100 records. each record having a new account number (100 accounts in 100 records).

The problem is we have users who have to see only some accounts. For example, user 1 has to see only accounts 1-10, user 2 has to see only accounts 11-20, user 3 has to see only accounts 21-30. and so on...

I know we can create different roles and create perspectives for these roles, but the problem is the list that defines what they can see is a table of 1000 user entries, each row has its own conditions for each user type. (Actually, each condition is a combination of different dimensions. For example, user 1 has to see account 1-10 and products A-D)

Does anyone have any idea on how to do this?

Thanks in advance

Luckily the problem you describe is very common. So common it has it's own name, it is oftenly referred as "Dynamic Dimension Security".

There are quite a few articles about it. Here is one for you http://sqljunkies.com/WebLog/mosha/archive/2004/12/16/5605.aspx

Search for it, you will find quite a bit information.

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

|||Thanks Edward, I looked at it and I think thats what am looking for. will come back for your help if I cant figure something out :-)

Role

hi,
The issue for using a role is everytime if I create a new stored
procedure/view/table/udf, I need to go to the role and grant permissions for
that new role.
Am I right?
EdIf all the users in the role need to execute the stored procedure, then yes,
you will have to grant the EXECUTE previleges to the role. You can do this
with a simple GRANT statement like:
GRANT EXECUTE ON usp TO public
where usp is the procedure & public is the role.
Anith|||Thanks for your answer, that helps...
I am thinking in the Production Server, we don't want to grant Execute
permission to the uers, right?
"Anith Sen" wrote:

> If all the users in the role need to execute the stored procedure, then ye
s,
> you will have to grant the EXECUTE previleges to the role. You can do this
> with a simple GRANT statement like:
> GRANT EXECUTE ON usp TO public
> where usp is the procedure & public is the role.
> --
> Anith
>
>|||>> I am thinking in the Production Server, we don't want to grant Execute
I am not sure if I follow you. Stored procedures are meant to be executed by
database users. Without execute permission, regular database users cannot
run any procedures which they do not own.
Btw, I used public as an example in my previous post, all users belong to
public role by default.
Anithsql

Rogue view _hypmv_0

We had difficulty dropping a table the other day as the system stated that
another object was dependant on it - this is staging database so we don't do
that kind of thing.
Investigation showed that a view _hypmv_0 had been created, this must have
been generated by the system as the box is tightly controlled and only used
by me, plus the name is a bit left-field for us.
The view had been created with the SCHEMABINDING attribute, partial DDL
follows. Can anybody shed any light on this?
Thanks,
Paul
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1
)
drop view [dbo].[_hypmv_0]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECTHi
Someone ran the SQL Server Index Tuning Wizard on your DB.
_hypmv_0 is an Indexed view.
Drop the view and then you can drop the table.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul Smith" <paul@.spamno_sagestore.com> wrote in message
news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
> We had difficulty dropping a table the other day as the system stated that
> another object was dependant on it - this is staging database so we don't
> do that kind of thing.
> Investigation showed that a view _hypmv_0 had been created, this must have
> been generated by the system as the box is tightly controlled and only
> used by me, plus the name is a bit left-field for us.
> The view had been created with the SCHEMABINDING attribute, partial DDL
> follows. Can anybody shed any light on this?
> Thanks,
> Paul
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') =
1)
> drop view [dbo].[_hypmv_0]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECT
>|||Thanks for that.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oj7MOPAdFHA.1412@.TK2MSFTNGP10.phx.gbl...
> Hi
> Someone ran the SQL Server Index Tuning Wizard on your DB.
> _hypmv_0 is an Indexed view.
> Drop the view and then you can drop the table.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul Smith" <paul@.spamno_sagestore.com> wrote in message
> news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
>sql

Rogue view _hypmv_0

We had difficulty dropping a table the other day as the system stated that
another object was dependant on it - this is staging database so we don't do
that kind of thing.
Investigation showed that a view _hypmv_0 had been created, this must have
been generated by the system as the box is tightly controlled and only used
by me, plus the name is a bit left-field for us.
The view had been created with the SCHEMABINDING attribute, partial DDL
follows. Can anybody shed any light on this?
Thanks,
Paul
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[_hypmv_0]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECTHi
Someone ran the SQL Server Index Tuning Wizard on your DB.
_hypmv_0 is an Indexed view.
Drop the view and then you can drop the table.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul Smith" <paul@.spamno_sagestore.com> wrote in message
news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
> We had difficulty dropping a table the other day as the system stated that
> another object was dependant on it - this is staging database so we don't
> do that kind of thing.
> Investigation showed that a view _hypmv_0 had been created, this must have
> been generated by the system as the box is tightly controlled and only
> used by me, plus the name is a bit left-field for us.
> The view had been created with the SCHEMABINDING attribute, partial DDL
> follows. Can anybody shed any light on this?
> Thanks,
> Paul
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[_hypmv_0]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECT
>|||Thanks for that.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oj7MOPAdFHA.1412@.TK2MSFTNGP10.phx.gbl...
> Hi
> Someone ran the SQL Server Index Tuning Wizard on your DB.
> _hypmv_0 is an Indexed view.
> Drop the view and then you can drop the table.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul Smith" <paul@.spamno_sagestore.com> wrote in message
> news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
>> We had difficulty dropping a table the other day as the system stated
>> that another object was dependant on it - this is staging database so we
>> don't do that kind of thing.
>> Investigation showed that a view _hypmv_0 had been created, this must
>> have been generated by the system as the box is tightly controlled and
>> only used by me, plus the name is a bit left-field for us.
>> The view had been created with the SCHEMABINDING attribute, partial DDL
>> follows. Can anybody shed any light on this?
>> Thanks,
>> Paul
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1)
>> drop view [dbo].[_hypmv_0]
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>> SET ANSI_NULLS ON
>> GO
>> CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECT
>

Rogue view _hypmv_0

We had difficulty dropping a table the other day as the system stated that
another object was dependant on it - this is staging database so we don't do
that kind of thing.
Investigation showed that a view _hypmv_0 had been created, this must have
been generated by the system as the box is tightly controlled and only used
by me, plus the name is a bit left-field for us.
The view had been created with the SCHEMABINDING attribute, partial DDL
follows. Can anybody shed any light on this?
Thanks,
Paul
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[_hypmv_0]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECT
Hi
Someone ran the SQL Server Index Tuning Wizard on your DB.
_hypmv_0 is an Indexed view.
Drop the view and then you can drop the table.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul Smith" <paul@.spamno_sagestore.com> wrote in message
news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
> We had difficulty dropping a table the other day as the system stated that
> another object was dependant on it - this is staging database so we don't
> do that kind of thing.
> Investigation showed that a view _hypmv_0 had been created, this must have
> been generated by the system as the box is tightly controlled and only
> used by me, plus the name is a bit left-field for us.
> The view had been created with the SCHEMABINDING attribute, partial DDL
> follows. Can anybody shed any light on this?
> Thanks,
> Paul
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[_hypmv_0]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[_hypmv_0]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW _hypmv_0 WITH SCHEMABINDING AS SELECT
>
|||Thanks for that.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Oj7MOPAdFHA.1412@.TK2MSFTNGP10.phx.gbl...
> Hi
> Someone ran the SQL Server Index Tuning Wizard on your DB.
> _hypmv_0 is an Indexed view.
> Drop the view and then you can drop the table.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul Smith" <paul@.spamno_sagestore.com> wrote in message
> news:%238J67u$cFHA.464@.TK2MSFTNGP15.phx.gbl...
>

ROBUST PLAN error

Hi all
I'm trying to INSERT rows from one table to the other in a Oracle Linked server to SQL server
My query is something like this
INSERT INTO ORACLE_LINK..User.Table
SELECT *
FROM ORACLE_LINK..User.Table
I'm getting the following error
Server: Msg 510, Level 16, State 1, Line
Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint
(Both are oracle tables only)
Can anybody tell the reason for this error
Thanks
Siva.It looks like the rows in the Oracle tables are longer than the maximum
rowlength in SQL Server, which is 8060 bytes.
You are probably best off to issue this query as a pass-through query using
OPENQUERY(ORACLE_LINK, 'INSERT INTO User.Table1SELECT * FROM User.Table2')
--
Jacco Schalkwijk
SQL Server MVP
"Siva" <siva116@.yahoo.com> wrote in message
news:0CD3DD0E-9B5A-441F-A040-030C9E108750@.microsoft.com...
> Hi all,
> I'm trying to INSERT rows from one table to the other in a Oracle Linked
server to SQL server.
> My query is something like this:
> INSERT INTO ORACLE_LINK..User.Table1
> SELECT *
> FROM ORACLE_LINK..User.Table2
> I'm getting the following error:
> Server: Msg 510, Level 16, State 1, Line 1
> Cannot create a worktable row larger than allowable maximum. Resubmit your
query with the ROBUST PLAN hint.
> (Both are oracle tables only).
> Can anybody tell the reason for this error?
> Thanks,
> Siva.

ROBUST PLAN error

Hi all,
I'm trying to INSERT rows from one table to the other in a Oracle Linked se
rver to SQL server.
My query is something like this:
INSERT INTO ORACLE_LINK..User.Table1
SELECT *
FROM ORACLE_LINK..User.Table2
I'm getting the following error:
Server: Msg 510, Level 16, State 1, Line 1
Cannot create a worktable row larger than allowable maximum. Resubmit your q
uery with the ROBUST PLAN hint.
(Both are oracle tables only).
Can anybody tell the reason for this error?
Thanks,
Siva.It looks like the rows in the Oracle tables are longer than the maximum
rowlength in SQL Server, which is 8060 bytes.
You are probably best off to issue this query as a pass-through query using
OPENQUERY(ORACLE_LINK, 'INSERT INTO User.Table1SELECT * FROM User.Table2')
Jacco Schalkwijk
SQL Server MVP
"Siva" <siva116@.yahoo.com> wrote in message
news:0CD3DD0E-9B5A-441F-A040-030C9E108750@.microsoft.com...
> Hi all,
> I'm trying to INSERT rows from one table to the other in a Oracle Linked
server to SQL server.
> My query is something like this:
> INSERT INTO ORACLE_LINK..User.Table1
> SELECT *
> FROM ORACLE_LINK..User.Table2
> I'm getting the following error:
> Server: Msg 510, Level 16, State 1, Line 1
> Cannot create a worktable row larger than allowable maximum. Resubmit your
query with the ROBUST PLAN hint.
> (Both are oracle tables only).
> Can anybody tell the reason for this error?
> Thanks,
> Siva.

Rights on sproc with dynamic sql

I have a sproc z on on which user x has execute rights.
The sproc z dynamically builds a sql statement based on input param.
The sql quey table y on which user x does not have execute rights.
When the sql is executed at the end of the sproc I get an error saying
that "user x does not have exec rights on table y".
Shouldn't it be sufficient to grant user x exec rights on sproc z?
/M
Not when you're using dynamic sql. Thats one of the problems. Dynamic sql is
executed in the security context of the user, while 'normal' sql is executed
in the security context of the sp owner (usually dbo).
Or, to bi more precise, grantable permissions are executed in the owner
context. You cant grant permission to execute dynamic sql and it is executed
as user...
MC
<job@.bla.com> wrote in message
news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com...
>I have a sproc z on on which user x has execute rights.
> The sproc z dynamically builds a sql statement based on input param.
> The sql quey table y on which user x does not have execute rights.
> When the sql is executed at the end of the sproc I get an error saying
> that "user x does not have exec rights on table y".
> Shouldn't it be sufficient to grant user x exec rights on sproc z?
> /M
|||Hmm. Thanks. Guess I'll have to rethink my approach
On Thu, 9 Mar 2006 14:15:01 +0100, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:

>Not when you're using dynamic sql. Thats one of the problems. Dynamic sql is
>executed in the security context of the user, while 'normal' sql is executed
>in the security context of the sp owner (usually dbo).
>Or, to bi more precise, grantable permissions are executed in the owner
>context. You cant grant permission to execute dynamic sql and it is executed
>as user...
>
>MC
><job@.bla.com> wrote in message
>news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com.. .
>
|||> Not when you're using dynamic sql. Thats one of the problems. Dynamic sql
> is executed in the security context of the user, while 'normal' sql is
> executed in the security context of the sp owner (usually dbo).
It is a common misconception that the security context is the owner. The
actual behavior is that permissions on indirectly referenced objects are not
checked at all when the ownership chain is unbroken (all objects owned by
the same user). Dynamic SQL is considered a direct object reference so user
permissions are checked just like when the ownership chain is broken.
SQL Server 2005 introduces EXECUTE AS but ownership chains still apply and
take precedence. See http://www.sommarskog.se/grantperm.html.
Hope this helps.
Dan Guzman
SQL Server MVP
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uZGzwm3QGHA.3052@.TK2MSFTNGP09.phx.gbl...
> Not when you're using dynamic sql. Thats one of the problems. Dynamic sql
> is executed in the security context of the user, while 'normal' sql is
> executed in the security context of the sp owner (usually dbo).
> Or, to bi more precise, grantable permissions are executed in the owner
> context. You cant grant permission to execute dynamic sql and it is
> executed as user...
>
> MC
> <job@.bla.com> wrote in message
> news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com...
>
|||Does that mean that deny on table to table_owner woudlnt actually be
effective if the table_owner is the sp owner as well?
MC
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OmaDO03QGHA.4696@.tk2msftngp13.phx.gbl...
> It is a common misconception that the security context is the owner. The
> actual behavior is that permissions on indirectly referenced objects are
> not checked at all when the ownership chain is unbroken (all objects owned
> by the same user). Dynamic SQL is considered a direct object reference so
> user permissions are checked just like when the ownership chain is broken.
> SQL Server 2005 introduces EXECUTE AS but ownership chains still apply and
> take precedence. See http://www.sommarskog.se/grantperm.html.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:uZGzwm3QGHA.3052@.TK2MSFTNGP09.phx.gbl...
>
|||It serves no purpose to DENY object permissions to the object owner since
the owner always has full permissions anyway. In any case, I know of no
exceptions to the ownership chain rule - permissions are not checked when
the chain is unbroken.
Hope this helps.
Dan Guzman
SQL Server MVP
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:%23OaJoF4QGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Does that mean that deny on table to table_owner woudlnt actually be
> effective if the table_owner is the sp owner as well?
>
> MC
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OmaDO03QGHA.4696@.tk2msftngp13.phx.gbl...
>
|||Ok, thanks for explaining.
MC
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:erVTCW%23QGHA.5584@.TK2MSFTNGP12.phx.gbl...
> It serves no purpose to DENY object permissions to the object owner since
> the owner always has full permissions anyway. In any case, I know of no
> exceptions to the ownership chain rule - permissions are not checked when
> the chain is unbroken.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:%23OaJoF4QGHA.4452@.TK2MSFTNGP12.phx.gbl...
>

Rights on sproc with dynamic sql

I have a sproc z on on which user x has execute rights.
The sproc z dynamically builds a sql statement based on input param.
The sql quey table y on which user x does not have execute rights.
When the sql is executed at the end of the sproc I get an error saying
that "user x does not have exec rights on table y".
Shouldn't it be sufficient to grant user x exec rights on sproc z?
/MNot when you're using dynamic sql. Thats one of the problems. Dynamic sql is
executed in the security context of the user, while 'normal' sql is executed
in the security context of the sp owner (usually dbo).
Or, to bi more precise, grantable permissions are executed in the owner
context. You cant grant permission to execute dynamic sql and it is executed
as user...
MC
<job@.bla.com> wrote in message
news:gq80129hctb468t8hfbe40lahsmagad4gh@.
4ax.com...
>I have a sproc z on on which user x has execute rights.
> The sproc z dynamically builds a sql statement based on input param.
> The sql quey table y on which user x does not have execute rights.
> When the sql is executed at the end of the sproc I get an error saying
> that "user x does not have exec rights on table y".
> Shouldn't it be sufficient to grant user x exec rights on sproc z?
> /M|||Hmm. Thanks. Guess I'll have to rethink my approach
On Thu, 9 Mar 2006 14:15:01 +0100, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:

>Not when you're using dynamic sql. Thats one of the problems. Dynamic sql i
s
>executed in the security context of the user, while 'normal' sql is execute
d
>in the security context of the sp owner (usually dbo).
>Or, to bi more precise, grantable permissions are executed in the owner
>context. You cant grant permission to execute dynamic sql and it is execute
d
>as user...
>
>MC
><job@.bla.com> wrote in message
> news:gq80129hctb468t8hfbe40lahsmagad4gh@.
4ax.com...
>|||> Not when you're using dynamic sql. Thats one of the problems. Dynamic sql
> is executed in the security context of the user, while 'normal' sql is
> executed in the security context of the sp owner (usually dbo).
It is a common misconception that the security context is the owner. The
actual behavior is that permissions on indirectly referenced objects are not
checked at all when the ownership chain is unbroken (all objects owned by
the same user). Dynamic SQL is considered a direct object reference so user
permissions are checked just like when the ownership chain is broken.
SQL Server 2005 introduces EXECUTE AS but ownership chains still apply and
take precedence. See http://www.sommarskog.se/grantperm.html.
Hope this helps.
Dan Guzman
SQL Server MVP
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uZGzwm3QGHA.3052@.TK2MSFTNGP09.phx.gbl...
> Not when you're using dynamic sql. Thats one of the problems. Dynamic sql
> is executed in the security context of the user, while 'normal' sql is
> executed in the security context of the sp owner (usually dbo).
> Or, to bi more precise, grantable permissions are executed in the owner
> context. You cant grant permission to execute dynamic sql and it is
> executed as user...
>
> MC
> <job@.bla.com> wrote in message
> news:gq80129hctb468t8hfbe40lahsmagad4gh@.
4ax.com...
>|||Does that mean that deny on table to table_owner woudlnt actually be
effective if the table_owner is the sp owner as well?
MC
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OmaDO03QGHA.4696@.tk2msftngp13.phx.gbl...
> It is a common misconception that the security context is the owner. The
> actual behavior is that permissions on indirectly referenced objects are
> not checked at all when the ownership chain is unbroken (all objects owned
> by the same user). Dynamic SQL is considered a direct object reference so
> user permissions are checked just like when the ownership chain is broken.
> SQL Server 2005 introduces EXECUTE AS but ownership chains still apply and
> take precedence. See http://www.sommarskog.se/grantperm.html.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:uZGzwm3QGHA.3052@.TK2MSFTNGP09.phx.gbl...
>|||It serves no purpose to DENY object permissions to the object owner since
the owner always has full permissions anyway. In any case, I know of no
exceptions to the ownership chain rule - permissions are not checked when
the chain is unbroken.
Hope this helps.
Dan Guzman
SQL Server MVP
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:%23OaJoF4QGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Does that mean that deny on table to table_owner woudlnt actually be
> effective if the table_owner is the sp owner as well?
>
> MC
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OmaDO03QGHA.4696@.tk2msftngp13.phx.gbl...
>|||Ok, thanks for explaining.
MC
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:erVTCW%23QGHA.5584@.TK2MSFTNGP12.phx.gbl...
> It serves no purpose to DENY object permissions to the object owner since
> the owner always has full permissions anyway. In any case, I know of no
> exceptions to the ownership chain rule - permissions are not checked when
> the chain is unbroken.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:%23OaJoF4QGHA.4452@.TK2MSFTNGP12.phx.gbl...
>sql

Rights on sproc with dynamic sql

I have a sproc z on on which user x has execute rights.
The sproc z dynamically builds a sql statement based on input param.
The sql quey table y on which user x does not have execute rights.
When the sql is executed at the end of the sproc I get an error saying
that "user x does not have exec rights on table y".
Shouldn't it be sufficient to grant user x exec rights on sproc z?
/MNot when you're using dynamic sql. Thats one of the problems. Dynamic sql is
executed in the security context of the user, while 'normal' sql is executed
in the security context of the sp owner (usually dbo).
Or, to bi more precise, grantable permissions are executed in the owner
context. You cant grant permission to execute dynamic sql and it is executed
as user...
MC
<job@.bla.com> wrote in message
news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com...
>I have a sproc z on on which user x has execute rights.
> The sproc z dynamically builds a sql statement based on input param.
> The sql quey table y on which user x does not have execute rights.
> When the sql is executed at the end of the sproc I get an error saying
> that "user x does not have exec rights on table y".
> Shouldn't it be sufficient to grant user x exec rights on sproc z?
> /M|||Hmm. Thanks. Guess I'll have to rethink my approach :(
On Thu, 9 Mar 2006 14:15:01 +0100, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:
>Not when you're using dynamic sql. Thats one of the problems. Dynamic sql is
>executed in the security context of the user, while 'normal' sql is executed
>in the security context of the sp owner (usually dbo).
>Or, to bi more precise, grantable permissions are executed in the owner
>context. You cant grant permission to execute dynamic sql and it is executed
>as user...
>
>MC
><job@.bla.com> wrote in message
>news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com...
>>I have a sproc z on on which user x has execute rights.
>> The sproc z dynamically builds a sql statement based on input param.
>> The sql quey table y on which user x does not have execute rights.
>> When the sql is executed at the end of the sproc I get an error saying
>> that "user x does not have exec rights on table y".
>> Shouldn't it be sufficient to grant user x exec rights on sproc z?
>> /M
>|||> Not when you're using dynamic sql. Thats one of the problems. Dynamic sql
> is executed in the security context of the user, while 'normal' sql is
> executed in the security context of the sp owner (usually dbo).
It is a common misconception that the security context is the owner. The
actual behavior is that permissions on indirectly referenced objects are not
checked at all when the ownership chain is unbroken (all objects owned by
the same user). Dynamic SQL is considered a direct object reference so user
permissions are checked just like when the ownership chain is broken.
SQL Server 2005 introduces EXECUTE AS but ownership chains still apply and
take precedence. See http://www.sommarskog.se/grantperm.html.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uZGzwm3QGHA.3052@.TK2MSFTNGP09.phx.gbl...
> Not when you're using dynamic sql. Thats one of the problems. Dynamic sql
> is executed in the security context of the user, while 'normal' sql is
> executed in the security context of the sp owner (usually dbo).
> Or, to bi more precise, grantable permissions are executed in the owner
> context. You cant grant permission to execute dynamic sql and it is
> executed as user...
>
> MC
> <job@.bla.com> wrote in message
> news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com...
>>I have a sproc z on on which user x has execute rights.
>> The sproc z dynamically builds a sql statement based on input param.
>> The sql quey table y on which user x does not have execute rights.
>> When the sql is executed at the end of the sproc I get an error saying
>> that "user x does not have exec rights on table y".
>> Shouldn't it be sufficient to grant user x exec rights on sproc z?
>> /M
>|||Does that mean that deny on table to table_owner woudlnt actually be
effective if the table_owner is the sp owner as well?
MC
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OmaDO03QGHA.4696@.tk2msftngp13.phx.gbl...
>> Not when you're using dynamic sql. Thats one of the problems. Dynamic sql
>> is executed in the security context of the user, while 'normal' sql is
>> executed in the security context of the sp owner (usually dbo).
> It is a common misconception that the security context is the owner. The
> actual behavior is that permissions on indirectly referenced objects are
> not checked at all when the ownership chain is unbroken (all objects owned
> by the same user). Dynamic SQL is considered a direct object reference so
> user permissions are checked just like when the ownership chain is broken.
> SQL Server 2005 introduces EXECUTE AS but ownership chains still apply and
> take precedence. See http://www.sommarskog.se/grantperm.html.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:uZGzwm3QGHA.3052@.TK2MSFTNGP09.phx.gbl...
>> Not when you're using dynamic sql. Thats one of the problems. Dynamic sql
>> is executed in the security context of the user, while 'normal' sql is
>> executed in the security context of the sp owner (usually dbo).
>> Or, to bi more precise, grantable permissions are executed in the owner
>> context. You cant grant permission to execute dynamic sql and it is
>> executed as user...
>>
>> MC
>> <job@.bla.com> wrote in message
>> news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com...
>>I have a sproc z on on which user x has execute rights.
>> The sproc z dynamically builds a sql statement based on input param.
>> The sql quey table y on which user x does not have execute rights.
>> When the sql is executed at the end of the sproc I get an error saying
>> that "user x does not have exec rights on table y".
>> Shouldn't it be sufficient to grant user x exec rights on sproc z?
>> /M
>>
>|||It serves no purpose to DENY object permissions to the object owner since
the owner always has full permissions anyway. In any case, I know of no
exceptions to the ownership chain rule - permissions are not checked when
the chain is unbroken.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:%23OaJoF4QGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Does that mean that deny on table to table_owner woudlnt actually be
> effective if the table_owner is the sp owner as well?
>
> MC
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OmaDO03QGHA.4696@.tk2msftngp13.phx.gbl...
>> Not when you're using dynamic sql. Thats one of the problems. Dynamic
>> sql is executed in the security context of the user, while 'normal' sql
>> is executed in the security context of the sp owner (usually dbo).
>> It is a common misconception that the security context is the owner. The
>> actual behavior is that permissions on indirectly referenced objects are
>> not checked at all when the ownership chain is unbroken (all objects
>> owned by the same user). Dynamic SQL is considered a direct object
>> reference so user permissions are checked just like when the ownership
>> chain is broken.
>> SQL Server 2005 introduces EXECUTE AS but ownership chains still apply
>> and take precedence. See http://www.sommarskog.se/grantperm.html.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
>> news:uZGzwm3QGHA.3052@.TK2MSFTNGP09.phx.gbl...
>> Not when you're using dynamic sql. Thats one of the problems. Dynamic
>> sql is executed in the security context of the user, while 'normal' sql
>> is executed in the security context of the sp owner (usually dbo).
>> Or, to bi more precise, grantable permissions are executed in the owner
>> context. You cant grant permission to execute dynamic sql and it is
>> executed as user...
>>
>> MC
>> <job@.bla.com> wrote in message
>> news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com...
>>I have a sproc z on on which user x has execute rights.
>> The sproc z dynamically builds a sql statement based on input param.
>> The sql quey table y on which user x does not have execute rights.
>> When the sql is executed at the end of the sproc I get an error saying
>> that "user x does not have exec rights on table y".
>> Shouldn't it be sufficient to grant user x exec rights on sproc z?
>> /M
>>
>>
>|||Ok, thanks for explaining.
MC
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:erVTCW%23QGHA.5584@.TK2MSFTNGP12.phx.gbl...
> It serves no purpose to DENY object permissions to the object owner since
> the owner always has full permissions anyway. In any case, I know of no
> exceptions to the ownership chain rule - permissions are not checked when
> the chain is unbroken.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:%23OaJoF4QGHA.4452@.TK2MSFTNGP12.phx.gbl...
>> Does that mean that deny on table to table_owner woudlnt actually be
>> effective if the table_owner is the sp owner as well?
>>
>> MC
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:OmaDO03QGHA.4696@.tk2msftngp13.phx.gbl...
>> Not when you're using dynamic sql. Thats one of the problems. Dynamic
>> sql is executed in the security context of the user, while 'normal' sql
>> is executed in the security context of the sp owner (usually dbo).
>> It is a common misconception that the security context is the owner.
>> The actual behavior is that permissions on indirectly referenced objects
>> are not checked at all when the ownership chain is unbroken (all objects
>> owned by the same user). Dynamic SQL is considered a direct object
>> reference so user permissions are checked just like when the ownership
>> chain is broken.
>> SQL Server 2005 introduces EXECUTE AS but ownership chains still apply
>> and take precedence. See http://www.sommarskog.se/grantperm.html.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
>> news:uZGzwm3QGHA.3052@.TK2MSFTNGP09.phx.gbl...
>> Not when you're using dynamic sql. Thats one of the problems. Dynamic
>> sql is executed in the security context of the user, while 'normal' sql
>> is executed in the security context of the sp owner (usually dbo).
>> Or, to bi more precise, grantable permissions are executed in the owner
>> context. You cant grant permission to execute dynamic sql and it is
>> executed as user...
>>
>> MC
>> <job@.bla.com> wrote in message
>> news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com...
>>I have a sproc z on on which user x has execute rights.
>> The sproc z dynamically builds a sql statement based on input param.
>> The sql quey table y on which user x does not have execute rights.
>> When the sql is executed at the end of the sproc I get an error saying
>> that "user x does not have exec rights on table y".
>> Shouldn't it be sufficient to grant user x exec rights on sproc z?
>> /M
>>
>>
>>
>

Rights for Access2000 Upsizing-Wizard

I granted CREATE TABLE to the signed-in user, but the export fails. With sa
account it would work.
Which permissions are missing?
ThanksIf the database exists, the user will still need create
database permissions as well as permissions to create
tables, views, stored procedures, triggers, etc depending on
what objects you have in your Access database and what
options you select. If the database does not exist, the user
needs permissions to select from system tables in master.
Just granting Create table won't be enough.
-Sue
On Mon, 7 Mar 2005 05:59:04 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:

>I granted CREATE TABLE to the signed-in user, but the export fails. With sa
>account it would work.
>Which permissions are missing?
>Thanks|||Hi Sue
Thanks first for your help.
The database exists. I droped the user for recreation in master, added the
role db_owner and granted create database in master and new db.
Still don't work. Any idea? I import just tables, index and relations with
DRI.|||Go into master and execute:
grant create database to YourUser
Then go into the database that will be used for upsizing and
make the user the owner of the database by executing:
sp_changedbowner 'YourUser'
-Sue
On Tue, 8 Mar 2005 06:41:02 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:

>Hi Sue
>Thanks first for your help.
>The database exists. I droped the user for recreation in master, added the
>role db_owner and granted create database in master and new db.
>Still don't work. Any idea? I import just tables, index and relations with
>DRI.
>|||Hello Sue

> Go into master and execute:
> grant create database to YourUser
Done. What I do in a scratch database is:
use master
CREATE DATABASE myDB
go
EXEC sp_addlogin 'usr', 'pwd'
EXEC sp_grantdbaccess 'usr'
EXEC sp_addrolemember 'db_owner', 'usr'
grant CREATE DATABASE to usr
go
use myDB
EXEC sp_grantdbaccess 'usr'
exec sp_addrolemember 'db_owner', 'usr'
go

> Then go into the database that will be used for upsizing and
> make the user the owner of the database by executing:
> sp_changedbowner 'YourUser'
When I execute this statement, I get the error that it is already owner of
db. Maybe because of sp_addrolemember 'db_owner'?|||Remove the user from the database - you don't want the
account being a user in the database when assigning the
account as the owner of the database. Then execute
sp_changedbowner.
-Sue
On Tue, 8 Mar 2005 23:43:04 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:

>Hello Sue
>
>Done. What I do in a scratch database is:
> use master
> CREATE DATABASE myDB
> go
> EXEC sp_addlogin 'usr', 'pwd'
> EXEC sp_grantdbaccess 'usr'
> EXEC sp_addrolemember 'db_owner', 'usr'
> grant CREATE DATABASE to usr
> go
> use myDB
> EXEC sp_grantdbaccess 'usr'
> exec sp_addrolemember 'db_owner', 'usr'
> go
>
>When I execute this statement, I get the error that it is already owner of
>db. Maybe because of sp_addrolemember 'db_owner'?
>|||Sorry Sue
Don't understand anything. If I remove the user it can't be the owner
(because it doesn't exist). If I remove the dbaccess, I can't login anymore.
What do you mean?
Could you send me a script from scratch database like:
use master
CREATE DATABASE myDB
go
EXEC sp_addlogin 'usr', 'pwd'
EXEC sp_grantdbaccess 'usr'
EXEC sp_addrolemember 'db_owner', 'usr'
grant CREATE DATABASE to usr
go
use myDB
EXEC sp_grantdbaccess 'usr'
exec sp_addrolemember 'db_owner', 'usr'
go
Thanks|||This is the part you don't want to use. This adds the
account as a user in the database. You don't want the
account added as a user in the database. Don't execute this
part at all. Instead, make the user the database owner NOT a
member of the db_owner role.
So instead of this part, execute:
use myDB
exec sp_changedbowner 'usr'
There is a difference between being the database owner and
being a member of the db_owner role.
You script has another reference where you are adding the
usr to the db_owners role for master and you don't want
that.
Your entire script would read like:
use master
go
CREATE DATABASE myDB
go
EXEC sp_addlogin 'usr', 'pwd'
EXEC sp_grantdbaccess 'usr'
grant CREATE DATABASE to usr
go
use myDB
go
EXEC sp_changedbowner 'usr'
go
I just ran it and upsized a database logging in as usr and
using myDB as the destination database, upsizing tables,
indexes and DRI.
-Sue
On Fri, 11 Mar 2005 00:29:02 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:

>use myDB
> EXEC sp_grantdbaccess 'usr'
> exec sp_addrolemember 'db_owner', 'usr'
> go|||Hi Sue
Thanks for you replies and patience. Unfortunately your script still doesn't
work. The tables in Access are skipped...|||Don't know what else to tell you - the permissions keep working for me just
fine. I just tried in another different environment (so we are up to three
now - different server, different PCs with Access DBs) and it worked fine.
I have upsized different databases 6 times now with a user with create
database permissions and the owner of the destination database for the
upsized objects. Upsized tables, indexes, DRI. Just followed the origninal
steps:
Go into master and execute:
grant create database to YourUser
Then go into the database that will be used for upsizing and
make the user the owner of the database by executing:
sp_changedbowner 'YourUser'
If the tables are skipped with no errors then basically nothing was upsized
as the indexes and DRI couldn't be done. You will need to track down where
the error is and provide an easily repro scenario as I am unable to reproduc
e
your problems.
-Sue

Right-Click Table -> Import data GONE?

Hi all
Apologies if this should be a tools question!

In the 'good old days' you could right-click a table, select Import data and have the target pre-selected....not only does that no longer exist for SQL 2005 you now always have to explicitly select the source and target....

Is there a work-around or is this a security cleanup gone mad and is thus a 'live with it :( ?Right-click on the database-->Tasks-->Import Data...

-Jamie|||Hi Jamie
You are of course correct, however this does mean that you have to explicitly select the source and target. My point is that beforehand you could implicitly select the target by right-clicking the table and select import data thereafter typically performing a text file import.

Surely we should not have to open the BI Studio or have to run an import at the database not table level?|||

Charl wrote:

Hi Jamie
You are of course correct, however this does mean that you have to explicitly select the source and target. My point is that beforehand you could implicitly select the target by right-clicking the table and select import data thereafter typically performing a text file import.

Surely we should not have to open the BI Studio or have to run an import at the database not table level?

It does seem an oversight I agree. However, is it really that much of a hardship to have to explicitly select the table? In my opinion its not - its only my opinion however!

I dare say MS will have had some UE type people on this (if they didn't - they should have done) and so will be able to explain the decision.

-Jamie|||Hi Jamie
It's not so much that it is a hardship to explicitly select the table rather that you have to jump through more hoops.

One of the beauties of the right-click-->import data on a table was that you were left with a visual clue as to the table to be imported into. Now you first select the target, set the text qualifier (and silently lose information if you don't - my .csv file imported successufully but lost everything after the first comma),set the source column types (and if these are too short if you have done the basic suggest types it will error with truncation warnings, but not where you expect?!) then select the target Server & DB.

Finally the summary screen shows your "quick import" - you now have to expand the window and the source / destination columns to see what you are trying to do - not something you want to go through again if it fails!

All I am doing here is to populate a set of static tables, nothing fancy required as these are simple .csv files that will be imported infrequently.

Thanks for your replies

ATB

Charl

Right single quotation mark errors

Hi,
I've created a table in SQL Server 2000 and I'm now trying to search
through the data and return specific rows. I'm using this command:

select * from Export where libelle_court='Recherche d'investisseurs'

The problem is this: The search fails whenever there is a curly
single quotation mark within the table field ( ' as opposed to ' ).

For example, if the field entry in my table is this:
Recherche d'investisseurs

then both of the following commands retun no fields:
select * from Export where libelle_court='Recherche d'investisseurs'
select * from Export where libelle_court='Recherche d''investisseurs'

However, if the field entry in my table is this:
Recherche d'investisseurs

then both of the commands quoted above succeed.

How can I get SQL Server to treat the curly quotation mark correctly
and return the right results? I've tried changing the collation but
with no success.

Thanks,

RobHi

In general if there is a quotation mark in the field it can be escaped with
another quotation mark, therefore I am not sure why the second example does
not work.

> select * from Export where libelle_court='Recherche d''investisseurs'

What does

select * from Export where libelle_court like 'Recherche d%'

return?

Also check out:

http://msdn.microsoft.com/library/d...con_03_7mch.asp

http://msdn.microsoft.com/library/d...earchvalues.asp

Collation will not effect this.

John

"Robert Garrett" <rgagarrett@.hotmail.com> wrote in message
news:b9c50dd6.0311030229.1ea4c288@.posting.google.c om...
> Hi,
> I've created a table in SQL Server 2000 and I'm now trying to search
> through the data and return specific rows. I'm using this command:
> select * from Export where libelle_court='Recherche d'investisseurs'
> The problem is this: The search fails whenever there is a curly
> single quotation mark within the table field ( ' as opposed to ' ).
> For example, if the field entry in my table is this:
> Recherche d'investisseurs
> then both of the following commands retun no fields:
> select * from Export where libelle_court='Recherche d'investisseurs'
> select * from Export where libelle_court='Recherche d''investisseurs'
> However, if the field entry in my table is this:
> Recherche d'investisseurs
> then both of the commands quoted above succeed.
> How can I get SQL Server to treat the curly quotation mark correctly
> and return the right results? I've tried changing the collation but
> with no success.
> Thanks,
> Rob|||Just another thought, depending on what the datatypes and options are, check
out trailing spaces. (Also see ANSI_PADDING in BOL)

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:bo5fp2$6q8$1@.sparta.btinternet.com...
> Hi
> In general if there is a quotation mark in the field it can be escaped
with
> another quotation mark, therefore I am not sure why the second example
does
> not work.
> > select * from Export where libelle_court='Recherche d''investisseurs'
> What does
> select * from Export where libelle_court like 'Recherche d%'
> return?
> Also check out:
>
http://msdn.microsoft.com/library/d...con_03_7mch.asp
>
http://msdn.microsoft.com/library/d...earchvalues.asp
> Collation will not effect this.
> John
> "Robert Garrett" <rgagarrett@.hotmail.com> wrote in message
> news:b9c50dd6.0311030229.1ea4c288@.posting.google.c om...
> > Hi,
> > I've created a table in SQL Server 2000 and I'm now trying to search
> > through the data and return specific rows. I'm using this command:
> > select * from Export where libelle_court='Recherche d'investisseurs'
> > The problem is this: The search fails whenever there is a curly
> > single quotation mark within the table field ( ' as opposed to ' ).
> > For example, if the field entry in my table is this:
> > Recherche d'investisseurs
> > then both of the following commands retun no fields:
> > select * from Export where libelle_court='Recherche d'investisseurs'
> > select * from Export where libelle_court='Recherche d''investisseurs'
> > However, if the field entry in my table is this:
> > Recherche d'investisseurs
> > then both of the commands quoted above succeed.
> > How can I get SQL Server to treat the curly quotation mark correctly
> > and return the right results? I've tried changing the collation but
> > with no success.
> > Thanks,
> > Rob|||Thanks for the help.

I'm not sure exactly what the problem was but it has gone now. I was
working on a number of things so I don't know quite what it was that
fixed the problem. I thought it might be because I was changing the
collation, but I cannot use this to repeat the fault. The web sites
were useful, though, so thanks again for the help.

Robsql

Right Question?

Hi Group,
I have a question in SQL Server 2000.
The question is, I have a table with 2 millions of records and I want
to fetch the record from 200 to 400 rows and the table doesn't content
any identity cols nor any numeric col all the varchar field. Can you
put some light on it?
Regards
ArijitHi
http://www.aspfaq.com/show.asp?id=2120
<arijitchatterjee123@.yahoo.co.in> wrote in message
news:1154433609.267834.40780@.h48g2000cwc.googlegroups.com...
> Hi Group,
> I have a question in SQL Server 2000.
> The question is, I have a table with 2 millions of records and I want
> to fetch the record from 200 to 400 rows and the table doesn't content
> any identity cols nor any numeric col all the varchar field. Can you
> put some light on it?
> Regards
> Arijit
>|||Hi
To get a number of records in a range you need to be able to order them!!!
Check out http://www.aspfaq.com/show.asp?id=2120 for various techniques that
can be used on SQLServer 7.0 and 2000. For SQL Server 2005 check out the new
ranking and TOP functionality.
John
"arijitchatterjee123@.yahoo.co.in" wrote:

> Hi Group,
> I have a question in SQL Server 2000.
> The question is, I have a table with 2 millions of records and I want
> to fetch the record from 200 to 400 rows and the table doesn't content
> any identity cols nor any numeric col all the varchar field. Can you
> put some light on it?
> Regards
> Arijit
>

Right Question?

Hi Group,
I have a question in SQL Server 2000.
The question is, I have a table with 2 millions of records and I want
to fetch the record from 200 to 400 rows and the table doesn't content
any identity cols nor any numeric col all the varchar field. Can you
put some light on it?
Regards
ArijitHi
http://www.aspfaq.com/show.asp?id=2120
<arijitchatterjee123@.yahoo.co.in> wrote in message
news:1154433609.267834.40780@.h48g2000cwc.googlegroups.com...
> Hi Group,
> I have a question in SQL Server 2000.
> The question is, I have a table with 2 millions of records and I want
> to fetch the record from 200 to 400 rows and the table doesn't content
> any identity cols nor any numeric col all the varchar field. Can you
> put some light on it?
> Regards
> Arijit
>|||Hi
To get a number of records in a range you need to be able to order them!!!
Check out http://www.aspfaq.com/show.asp?id=2120 for various techniques that
can be used on SQLServer 7.0 and 2000. For SQL Server 2005 check out the new
ranking and TOP functionality.
John
"arijitchatterjee123@.yahoo.co.in" wrote:
> Hi Group,
> I have a question in SQL Server 2000.
> The question is, I have a table with 2 millions of records and I want
> to fetch the record from 200 to 400 rows and the table doesn't content
> any identity cols nor any numeric col all the varchar field. Can you
> put some light on it?
> Regards
> Arijit
>

Right Pane of Report

I have a report where I have setup a table and am using a subreport for the header and using the Page footer for the footer (Subreports can't be used in traditional Page Headers. I also have a Right pane. What i'd like to do is create a table that will use the space that isn't the header/footer/right pane for the body of a report but I can't seem to figure it out.
Is there any absolute positioning in RS? I'd love to be able to take the right pane (which is just a rectangle with sub-items) and say, please print this at x,y on every page. Am I asking too much?
My alternative approach (which I don't love) is to create an image of the template and use it as a page background, then draw the data over the the image. But that is hard to maintain long-term.
Ideas? Please?
Thanks,
Shawn Wildermuth
http://adoguy.com
http://magenic.comI haven't tried what you're doing yet, but am very interested in it. Are
you using rectangles to size and position elements? I'm finding that
placing items in rectangles gives me much more control over how they render
relative to each other.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Shawn Wildermuth" <swildermuth_at_adoguydotcom> wrote in message
news:%23qiQwt71EHA.3616@.TK2MSFTNGP11.phx.gbl...
>I have a report where I have setup a table and am using a subreport for the
>header and using the Page footer for the footer (Subreports can't be used
>in traditional Page Headers. I also have a Right pane. What i'd like to
>do is create a table that will use the space that isn't the
>header/footer/right pane for the body of a report but I can't seem to
>figure it out.
> Is there any absolute positioning in RS? I'd love to be able to take the
> right pane (which is just a rectangle with sub-items) and say, please
> print this at x,y on every page. Am I asking too much?
> My alternative approach (which I don't love) is to create an image of the
> template and use it as a page background, then draw the data over the the
> image. But that is hard to maintain long-term.
> Ideas? Please?
> Thanks,
> Shawn Wildermuth
> http://adoguy.com
> http://magenic.com

Right characters from ~

Hai,
Who can help me '
I'm making a query from a table.
In that table there is a column with the name NR_
The rows of that column give a result as
~2000252
~2003
~26578
What i want as result, the most right character from the ~character and as
result in the same column
200252
2003
26578
Who can help me ?There are several string related functions that will do what you want. Look
up documentation on right(), substring() and replace()
"Jaap" <Jaap@.discussions.microsoft.com> wrote in message
news:B0A0060F-EA08-4AE0-A516-67C4623564CA@.microsoft.com...
> Hai,
> Who can help me '
> I'm making a query from a table.
> In that table there is a column with the name NR_
> The rows of that column give a result as
> ~2000252
> ~2003
> ~26578
> What i want as result, the most right character from the ~character and as
> result in the same column
> 200252
> 2003
> 26578
> Who can help me ?|||Hi
Try something like:
SELECT RIGHT (col1,CHARINDEX('~',REVERSE(col1))-1)
FROM
( SELECT '~2000252' as col1
UNION ALL SELECT '~2003'
UNION ALL SELECT '~26578'
UNION ALL SELECT 'abc~def' ) A
John
"Jaap" <Jaap@.discussions.microsoft.com> wrote in message
news:B0A0060F-EA08-4AE0-A516-67C4623564CA@.microsoft.com...
> Hai,
> Who can help me '
> I'm making a query from a table.
> In that table there is a column with the name NR_
> The rows of that column give a result as
> ~2000252
> ~2003
> ~26578
> What i want as result, the most right character from the ~character and as
> result in the same column
> 200252
> 2003
> 26578
> Who can help me ?|||Hi,
Use the below query
select replace(column_name,'~','') as column from table_name
Thanks
Hari
SQL Server MVP
"Jaap" <Jaap@.discussions.microsoft.com> wrote in message
news:B0A0060F-EA08-4AE0-A516-67C4623564CA@.microsoft.com...
> Hai,
> Who can help me '
> I'm making a query from a table.
> In that table there is a column with the name NR_
> The rows of that column give a result as
> ~2000252
> ~2003
> ~26578
> What i want as result, the most right character from the ~character and as
> result in the same column
> 200252
> 2003
> 26578
> Who can help me ?|||yes, this is great it works
many thanks
"John Bell" wrote:

> Hi
> Try something like:
> SELECT RIGHT (col1,CHARINDEX('~',REVERSE(col1))-1)
> FROM
> ( SELECT '~2000252' as col1
> UNION ALL SELECT '~2003'
> UNION ALL SELECT '~26578'
> UNION ALL SELECT 'abc~def' ) A
> John
> "Jaap" <Jaap@.discussions.microsoft.com> wrote in message
> news:B0A0060F-EA08-4AE0-A516-67C4623564CA@.microsoft.com...
>
>sql