Altering functions and CHECK constraints

Let's say I create a multi-statement function like this:

CREATE FUNCTION dbo.Test ()
RETURNS @.res TABLE (N int NOT NULL CHECK (N >= 0))
AS
BEGIN

INSERT INTO @.res
SELECT 1

RETURN
END

That works fine. Then I make a change in the function's body, replace the
CREATE FUNCTION with ALTER FUNCTION, and execute the batch. I get an error:

Server: Msg 3729, Level 16, State 3, Procedure Test, Line 9
Cannot ALTER 'dbo.Test' because it is being referenced by object
'CK__Test__N__5D2E32EB'.

Indeed, if I look at the list of dependencies for the function in QA's
object tree, I can see the check constraint referenced in the error
message.

ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
definition of the @.res table.

So it seems that the only way to modify such a function is to drop and
recreate. Is that a known behavior? Is there any particular reason for it?

Thanks.

--
(remove a 9 to reply by email)Dimitri Furman (dfurman@.cloud99.net) writes:
> ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
> definition of the @.res table.
> So it seems that the only way to modify such a function is to drop and
> recreate. Is that a known behavior? Is there any particular reason for it?

I will have to admit that I was not aware of this. As for why, my guess
is that this is an artefact of the metadata structure in SQL Server, and
the SQL Server developers did not write the necessary code to avoid this.

Anyway, the restriction is not there in SQL 2005, so whatever the reason
for this in SQL 2000, it is not likely to be a compelling one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Alter table with PRIMARY KEY

I have an existing table (with records) having the ff: structure:

CREATE TABLE [dbo].[TEMP2_WORKORDER] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[JobType] [varchar] (3) NULL ,
[JobID] [varchar] (10) NULL ,

I want to be modify the structure to add a PRIMARY KEY to the [WorkOrderID] column.

I was using ALTER TABLE but can't get the right syntax. Please help!

ThanksALTER TABLE dbo.TEMP2_WORKORDER ADD CONSTRAINT
PK_testtable PRIMARY KEY CLUSTERED
(
WorkOrderID
)|||Thank you. It did the trick!

Alter table Question in Sql 2000

How to add multiple columns with alter table command in Sql 2000 ?

ALTER TABLE [deneme].[dbo].[Mudurluk]
ADD HarcamaYetkilisi varchar(50) COLLATE Turkish_CI_AS NULL

//below gives error
ADD MaliKontrolYetkilisi varchar(50) COLLATE Turkish_CI_AS NULL ,
ADD Memur varchar(50) COLLATE Turkish_CI_AS NULL ,
ADD Sef varchar(50) COLLATE Turkish_CI_AS NULL ,
ADD MuhasebeYetkilisiYardimcisi varchar(50) COLLATE Turkish_CI_AS NULL

Can you help me with this ?

Thanks alot in advance

Specify ADD only the first time.

ALTER Table query

Hi, I just want to know to turn this:
CREATE TABLE [dbo].[tblTierCs] (
[idTierC] [int] NOT NULL ,
[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
[noSubDomain] [int] NOT NULL ,
[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
[noOldTierC] [int] NULL ,
[noRSDTierC] [int] NULL
) ON [PRIMARY]
into this:
CREATE TABLE [dbo].[tblTierCs] (
[idTierC] [int] IDENTITY (1, 1) NOT NULL ,
[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
[noSubDomain] [int] NOT NULL ,
[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
[noOldTierC] [int] NULL ,
[noRSDTierC] [int] NULL
) ON [PRIMARY]

using an ALTER TABLE query. I tried using:
ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY
(1, 1) NOT NULL but it's not working. Anyone has any idea how I could
do it? Thanks.Heist (advertiseallyouwant@.hotmail.com) writes:
> Hi, I just want to know to turn this:
> CREATE TABLE [dbo].[tblTierCs] (
> [idTierC] [int] NOT NULL ,
> [txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
> [noSubDomain] [int] NOT NULL ,
> [txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
> [noOldTierC] [int] NULL ,
> [noRSDTierC] [int] NULL
> ) ON [PRIMARY]
> into this:
> CREATE TABLE [dbo].[tblTierCs] (
> [idTierC] [int] IDENTITY (1, 1) NOT NULL ,
> [txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
> [noSubDomain] [int] NOT NULL ,
> [txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
> [noOldTierC] [int] NULL ,
> [noRSDTierC] [int] NULL
> ) ON [PRIMARY]
> using an ALTER TABLE query. I tried using:
> ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY
> (1, 1) NOT NULL but it's not working. Anyone has any idea how I could
> do it? Thanks.

You cannot use ALTER TABLE to change a column into IDENTITY column
(except on SQL Server CE!). One way is to rename the table, create
a new and move over the data. You need to have SET IDENTITY_INSERT
on for the table when you move the data.

You can also do it in Enterprise Mangager - which will renamed and
move data behind the scenes.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What I ended up doing is using SQL Server Entreprise Manager to
"manually" alter the table and then I used the script generator to
create a script I could then used. Thanks.sql

Alter table permission to dbo

I have the following requirement

I am creating a login and database user 'test' on a database with dbo
role .
I want to remove create table , alter table permisions to this user.
I am able to revoke create table permission but alter table goes
through.
I gave a command deny insert,delete,update on ssycolumns to test.
Still I am not able to prevent user altering schema . Alter table
successfully goes throgh.

I do not want to use datreader and datwriter role.
since I want user 'test' to create storred procedure with dbo owner

Is there a way to achieve this ?

Thanks

M A Srinivas"M A Srinivas" <masri@.vsnl.com> wrote in message
news:f7e90f78.0309260634.3791a935@.posting.google.c om...
> I have the following requirement
> I am creating a login and database user 'test' on a database with dbo
> role .
> I want to remove create table , alter table permisions to this user.
> I am able to revoke create table permission but alter table goes
> through.
> I gave a command deny insert,delete,update on ssycolumns to test.
> Still I am not able to prevent user altering schema . Alter table
> successfully goes throgh.
> I do not want to use datreader and datwriter role.
> since I want user 'test' to create storred procedure with dbo owner
> Is there a way to achieve this ?
> Thanks
> M A Srinivas

You can't prevent the user from modifying/dropping an existing object. If
you need to create objects with dbo owner, then the user must be in the
db_owner role, and that means he can modify/drop any dbo object. If you can
explain why you need the test user to create stored procedures, then perhaps
someone can suggest an alternative approach. Are you creating the procedures
dynamically, are you deploying new code to several server, etc.

Simon

ALTER TABLE MOVE TO

Hi All !

Does anybody knows why doesn't work this ?

ALTER TABLE dbo.MyTable1 MOVE TO MyFileGroup1.

Thx. DBT.

Is that valid syntax?

Looks like the move is a drop clustered constraint option.

Try building a clustered index on the filegroup then dropping it.

|||

I don't know that this is valid syntax.

I don't want to alter any column.

I don't want to alter any constraint.

I want to move the table to another Filegroup.

|||The way to move a table to another filegroup is to (re)build the clustered index with the new filegroup specified.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The Problem same here ...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1768940&SiteID=1

And the solution not works

ALTER TABLE MyTable1 DROP CONSTRAINT PK_MyTable1_xxxxx WITH (MOVE TO 'FileGroup1')

because this constraint referenced by another table(s).

ALTER TABLE MODIFY

hi!

i encountered problems when running this code in SQL Query

ALTER TABLE [dbo].[amsSchedule]
MODIFY(CutOff1 datetime NULL,
[FileName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

my aim is to modify the two fields to change its data type. BUt when im trying to run this command in the query analyzer, itsays "incorrect syntax error '(' "

What do i have to do? please help me...thanks

Hi,

ALTER TABLE (...) when modifying a column only supports one change at a time.

HTH, Jens Suessmeyer.

|||

Hi Jens!

Thanks a lot for the tip...now i know what to do since u told me that alter table only supports one change at a time...

thanks a lot!

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||This is a security feature.
> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||This is a security feature.

> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegroups
.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.
Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>
|||This is a security feature.

> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>
|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

Allow users to see logins (SQL2005)

Hello,
With SQL2000, the dbo's were able to see all the logins, so that they could
add them as users to their databases. We just starting setting up SQL
Server 2005 and it came to our attention that they no longer have this
ability. What rights do dbo's need to have in order to view all logins?
Thanks,
sck10Hi,
To let we better understand your issue, could you please tell me more on
this issue so that I can reproduce your issue? You may also mail me
(changliw@.microsoft.com) a screenshot of your issue.
As far as I know, dbo is a database role, while login is for the SQL Server
instance. If you want to see all the logins of a SQL Server, the login
account should be a member of sysadmin. Even in SQL Server 2000, one
database owner cannot run sp_helplogins to see all the logins unless he is
a sysadmin. You can log on your SQL Server instance with a system
administrator and assign the login account with the server role sysadmin,
then try again.
Hope this helps! Please feel free to let us know if you have any other
questions or concerns.
Happy New Year!
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Charles,
I sent an email with the snapshots.
I am using Enterprise Mgr 2005 to connect to both a SQL2000 database and a
SQL2005 database. I am the dbo for databases on both machines. With
SQL2000, when I add a user to my database, I can see all the users in the
database. With SQL2005, I can only see myself and the sa.
Thanks again,
sck10
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Charles
> As far as I know, dbo is a database role,
I have been always thinking that 'dbo' is 'privileged' user not a database
role.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||:(
Uri Dimant wrote:
>Hi Charles
>> As far as I know, dbo is a database role,
>I have been always thinking that 'dbo' is 'privileged' user not a database
>role.
>> Hi,
>> To let we better understand your issue, could you please tell me more on
>[quoted text clipped - 26 lines]
>> rights.
>> ======================================================--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Hi Charles
dbo is NOT a database role, it is a privileged database user. There is a
database role called db_owner, of which the dbo user is always a member.
It's hard enough understanding logins, users and roles, we need to be really
careful to use these terms correctly.
Thanks
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Kalen,
Thanks for your pointing it out!
I am sorry for using that wrong words and I appologize for that.
Your explanation is meaningful here. I will pay attention to this from now
on!
Thank you!
Charles Wang
Microsoft Online Community Support|||Hi sck10,
I had sent you an email for this issue.
I reproduced your issue now but I think that the behavior of showing logins
of SQL Server 2000 is not reasonable. I need to consult the SQL Server 2005
product team for this issue and I will let you know their replies as soon
as possible.
If you have any other questions or concerns, please feel free to contact
us. It is always our pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support|||Hi Steven,
I got the response from SQL team. The reason is as following:
This is because of security restrictions to view the metadata in SQL Server
2005. A user can only see metadata that the user either owns or on which
the user has been granted some permission. This policy prevents users with
minimal privileges from viewing metadata for all objects in an instance of
SQL Server 2005.
However if you do not want this security, the following statement can be
used to override metadata-visibility limitations at the instance level. All
metadata in the instance will be visible to the granted user. Doing so
would allow the user to see all other logins not only logins but all
metadata which is not a recommended practice.
GRANT VIEW ANY DEFINITION TO <USERNAME>
The <username> has to be replaced by the actual user who needs this. This
statement has to be executed by sysadmin.
Hope this helps!
Please feel free to let me know if you have any other questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support|||You might also be interested in this article on Metadata Security that I
wrote for TechNet Magazine:
http://www.microsoft.com/technet/technetmag/issues/2006/01/ProtectMetaData/?rss=y
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:NngjapFNHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi Steven,
> I got the response from SQL team. The reason is as following:
> This is because of security restrictions to view the metadata in SQL
> Server
> 2005. A user can only see metadata that the user either owns or on which
> the user has been granted some permission. This policy prevents users with
> minimal privileges from viewing metadata for all objects in an instance of
> SQL Server 2005.
> However if you do not want this security, the following statement can be
> used to override metadata-visibility limitations at the instance level.
> All
> metadata in the instance will be visible to the granted user. Doing so
> would allow the user to see all other logins not only logins but all
> metadata which is not a recommended practice.
> GRANT VIEW ANY DEFINITION TO <USERNAME>
> The <username> has to be replaced by the actual user who needs this. This
> statement has to be executed by sysadmin.
> Hope this helps!
>
> Please feel free to let me know if you have any other questions or
> concerns.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
>

Allow users to see logins (SQL2005)

Hello,
With SQL2000, the dbo's were able to see all the logins, so that they could
add them as users to their databases. We just starting setting up SQL
Server 2005 and it came to our attention that they no longer have this
ability. What rights do dbo's need to have in order to view all logins?
Thanks,
sck10Hi,
To let we better understand your issue, could you please tell me more on
this issue so that I can reproduce your issue? You may also mail me
(changliw@.microsoft.com) a screenshot of your issue.
As far as I know, dbo is a database role, while login is for the SQL Server
instance. If you want to see all the logins of a SQL Server, the login
account should be a member of sysadmin. Even in SQL Server 2000, one
database owner cannot run sp_helplogins to see all the logins unless he is
a sysadmin. You can log on your SQL Server instance with a system
administrator and assign the login account with the server role sysadmin,
then try again.
Hope this helps! Please feel free to let us know if you have any other
questions or concerns.
Happy New Year!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Charles,
I sent an email with the snapshots.
I am using Enterprise Mgr 2005 to connect to both a SQL2000 database and a
SQL2005 database. I am the dbo for databases on both machines. With
SQL2000, when I add a user to my database, I can see all the users in the
database. With SQL2005, I can only see myself and the sa.
Thanks again,
sck10
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Charles
> As far as I know, dbo is a database role,
I have been always thinking that 'dbo' is 'privileged' user not a database
role.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||
Uri Dimant wrote:[vbcol=seagreen]
>Hi Charles
>I have been always thinking that 'dbo' is 'privileged' user not a database
>role.
>
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1|||Hi Charles
dbo is NOT a database role, it is a privileged database user. There is a
database role called db_owner, of which the dbo user is always a member.
It's hard enough understanding logins, users and roles, we need to be really
careful to use these terms correctly.
Thanks
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Kalen,
Thanks for your pointing it out!
I am sorry for using that wrong words and I appologize for that.
Your explanation is meaningful here. I will pay attention to this from now
on!
Thank you!
Charles Wang
Microsoft Online Community Support|||Hi sck10,
I had sent you an email for this issue.
I reproduced your issue now but I think that the behavior of showing logins
of SQL Server 2000 is not reasonable. I need to consult the SQL Server 2005
product team for this issue and I will let you know their replies as soon
as possible.
If you have any other questions or concerns, please feel free to contact
us. It is always our pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support|||Hi Steven,
I got the response from SQL team. The reason is as following:
This is because of security restrictions to view the metadata in SQL Server
2005. A user can only see metadata that the user either owns or on which
the user has been granted some permission. This policy prevents users with
minimal privileges from viewing metadata for all objects in an instance of
SQL Server 2005.
However if you do not want this security, the following statement can be
used to override metadata-visibility limitations at the instance level. All
metadata in the instance will be visible to the granted user. Doing so
would allow the user to see all other logins not only logins but all
metadata which is not a recommended practice.
GRANT VIEW ANY DEFINITION TO <USERNAME>
The <username> has to be replaced by the actual user who needs this. This
statement has to be executed by sysadmin.
Hope this helps!
Please feel free to let me know if you have any other questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support|||You might also be interested in this article on Metadata Security that I
wrote for technet Magazine:
http://www.microsoft.com/technet/te...p://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:NngjapFNHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi Steven,
> I got the response from SQL team. The reason is as following:
> This is because of security restrictions to view the metadata in SQL
> Server
> 2005. A user can only see metadata that the user either owns or on which
> the user has been granted some permission. This policy prevents users with
> minimal privileges from viewing metadata for all objects in an instance of
> SQL Server 2005.
> However if you do not want this security, the following statement can be
> used to override metadata-visibility limitations at the instance level.
> All
> metadata in the instance will be visible to the granted user. Doing so
> would allow the user to see all other logins not only logins but all
> metadata which is not a recommended practice.
> GRANT VIEW ANY DEFINITION TO <USERNAME>
> The <username> has to be replaced by the actual user who needs this. This
> statement has to be executed by sysadmin.
> Hope this helps!
>
> Please feel free to let me know if you have any other questions or
> concerns.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
>

Allow a user to alter views.

Hi everyone,
Is it possible to allow a plain user(not a member of any roles) to alter
views created by dbo ?
Regards.What version are you using?
Take a look at GRANT ALTER VIEW command in the BOL
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||What version are you using? To create/modify dbo-owned objects in SQL 2000,
a user needs to be either:
1) a sysadmin role member
2) the database owner
3) a member of the db_owner role
4) a member of the db_ddladmin role
Why do your 'pain users' need to modify dbo-owned views? Perhaps there is
an alternative.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
>
You realize that this will allow the user to SELECT and possibly UPDATE and
DELETE _every_ table owned by dbo.
David|||Thanks for the replies. We are using SQL Server 2000.
We have 2 databases - one is the live one and the other is for development.
One of the departments uses views for generating reports. The SQL Login they
use is a member of the db_owner role in the development db , so they create
and modify views as required. The same SQL Login is a plain user(member of
public only) in the live database.After creating/altering views in the
development db they need to apply the changes to the live db. It is not
happening very often. They can send the script to me and I can execute it in
the live db. As an alternative we can write a small application to connect
to the live db with sufficient user credentials(hard coded) and execute the
script.
Best Regards.
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||> They can send the script to me and I can execute it in the live db. As an
> alternative we can write a small application to connect to the live db
> with sufficient user credentials(hard coded) and execute the script.
The app solution is probably best as long as you can justify the development
effort and there is no additional value with DBA involvement, like reviewing
the queries. Be sure to implement an application security technique to
ensure only authorized users can run it. One method is to first connect to
the live db using normal user credentials and then verify that the user
exists in an AuthorizedUsers table.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:uif4Wk1aGHA.5000@.TK2MSFTNGP05.phx.gbl...
> Thanks for the replies. We are using SQL Server 2000.
> We have 2 databases - one is the live one and the other is for
> development. One of the departments uses views for generating reports. The
> SQL Login they use is a member of the db_owner role in the development db
> , so they create and modify views as required. The same SQL Login is a
> plain user(member of public only) in the live database.After
> creating/altering views in the development db they need to apply the
> changes to the live db. It is not happening very often. They can send the
> script to me and I can execute it in the live db. As an alternative we can
> write a small application to connect to the live db with sufficient user
> credentials(hard coded) and execute the script.
> Best Regards.
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
>

Allow a user to alter views.

Hi everyone,
Is it possible to allow a plain user(not a member of any roles) to alter
views created by dbo ?
Regards.What version are you using?
Take a look at GRANT ALTER VIEW command in the BOL
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||What version are you using? To create/modify dbo-owned objects in SQL 2000,
a user needs to be either:
1) a sysadmin role member
2) the database owner
3) a member of the db_owner role
4) a member of the db_ddladmin role
Why do your 'pain users' need to modify dbo-owned views? Perhaps there is
an alternative.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
>
You realize that this will allow the user to SELECT and possibly UPDATE and
DELETE _every_ table owned by dbo.
David|||Thanks for the replies. We are using SQL Server 2000.
We have 2 databases - one is the live one and the other is for development.
One of the departments uses views for generating reports. The SQL Login they
use is a member of the db_owner role in the development db , so they create
and modify views as required. The same SQL Login is a plain user(member of
public only) in the live database.After creating/altering views in the
development db they need to apply the changes to the live db. It is not
happening very often. They can send the script to me and I can execute it in
the live db. As an alternative we can write a small application to connect
to the live db with sufficient user credentials(hard coded) and execute the
script.
Best Regards.
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||> They can send the script to me and I can execute it in the live db. As an
> alternative we can write a small application to connect to the live db
> with sufficient user credentials(hard coded) and execute the script.
The app solution is probably best as long as you can justify the development
effort and there is no additional value with DBA involvement, like reviewing
the queries. Be sure to implement an application security technique to
ensure only authorized users can run it. One method is to first connect to
the live db using normal user credentials and then verify that the user
exists in an AuthorizedUsers table.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:uif4Wk1aGHA.5000@.TK2MSFTNGP05.phx.gbl...
> Thanks for the replies. We are using SQL Server 2000.
> We have 2 databases - one is the live one and the other is for
> development. One of the departments uses views for generating reports. The
> SQL Login they use is a member of the db_owner role in the development db
> , so they create and modify views as required. The same SQL Login is a
> plain user(member of public only) in the live database.After
> creating/altering views in the development db they need to apply the
> changes to the live db. It is not happening very often. They can send the
> script to me and I can execute it in the live db. As an alternative we can
> write a small application to connect to the live db with sufficient user
> credentials(hard coded) and execute the script.
> Best Regards.
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
>> Hi everyone,
>> Is it possible to allow a plain user(not a member of any roles) to alter
>> views created by dbo ?
>> Regards.
>>
>

All Users are being IDed as 'dbo'

We have a SQL Server 2005 database set up. We are trying to add new users to one of four define roles. Even though we are creating new login, then assigning each new login to one of the four roles. The server is returning 'dbo' as the user no matter who is logging in. Is there some setting that is causing this behavior?

Thanks of any help.

Can you post more information about the roles you mentioned and the commands you used to create the logins and assign them role memberships?

Thanks
Laurentiu

|||We have created 4 roles with permission to a select set of stored procedures. When one of the front end applications opens, it runs a procedure that gets the USER id and which of one or more roles that user has. On the test system, each of the users are properly ID and shown the correct roles. But on the Production system, all of the login/users return the 'dbo' USER ID, thus the roles are not indicated correctly. We believe that using "SQL Server Management Studio 2005", is setting all the logins to 'dbo' even though when we look at the settings, it shows the proper roles for each login.|||

One likely possibility is that in your production system, the client is using credentials with SYSADMIN privileges (i.e. the login they are using is a member of the server fixed role SYSADMIN). Members of SYSADMIN will always have a user-identity of “dbo” in any database in the system.

-Raul Garcia

SDE/T

SQL Server Engine

alittle problem

hi every one this is my stored procedure

CREATE PROCEDURE dbo.pr_EmployerSeekerSearch
@.CS_Parameter_Id int = null,
@.CC_Id_Nationality int = null,
@.CC_Id_Residence int = null,
@.Seeker_Owns_Car bit = null,
@.Keyword1 varchar(150) = null,
@.Keyword2 varchar(150) = null,
@.KeywordSearch char(5)= null,
@.CR_Experience_Years1 int = null,
@.CR_Experience_Years2 int = null,
@.Major_Id_1 int = null,
@.Major_Id_2 int = null,
@.Major_Id_3 int = null,
@.University_Id_1 int = null,
@.University_Id_2 int = null,
@.University_Id_3 int = null,
@.JF_Name varchar(1000) = null,
@.Language_Id_1 int = null,
@.Language_Id_2 int = null,
@.Language_Param varchar(5) = null,
@.employer_id int,
@.type varchar(20),
@.Seeker_Country int = null
AS
declare @.emp_id int
declare @.AllMajors varchar(50)
select @.allMajors= Majors.Major_Name from Majors where Major_name='All' and (Major_Id=@.Major_Id_1 or Major_id=@.Major_Id_2 or Major_ID =@.Major_Id_3)
--Added new by Hussein
--The change was to check on Mjors when the @.allmajor = null else i'll return all Majors
if @.allMajors =null
begin
if (@.type='Employer')
Begin
 set @.emp_id=@.employer_id
End
Else
Begin
 set @.emp_id=-@.employer_id
End
if @.KeywordSearch = 'AND'
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
 Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
 [CR_Modification_Date] ,
 Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
 Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
 CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
 LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
 LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
 LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
 LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
 CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
 AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
 AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
 AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
 AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
 AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
 AND (
 (
 (@.keyword1 is null) or
 (Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword1 + '%')
 )
 AND
 (
 (@.keyword2 is null) or
 (Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword2 + '%')
 )
 )
 AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
 AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
 AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
 AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
 AND (
 (@.JF_Name is null)
 OR
 (
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
 OR
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
 )
 )
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
 and(VT_ID=1 or VT_ID=2)
end
else if @.KeywordSearch = 'OR'
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
 Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
 [CR_Modification_Date] ,
 Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
 Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
 CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
 LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
 LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
 LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
 LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
 CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
 AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
 AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
 AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
 AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
 AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
 AND (
 (
 (@.keyword1 is not null) and (
 (Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword1 + '%'))
 )
 OR
 (
 (@.keyword2 is not null) and (
 (Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword2 + '%'))
 )
 )
 AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
 AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
 AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
 AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
 AND (
 (@.JF_Name is null)
 OR
 (
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
 OR
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
 )
 )
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
 and(VT_ID=1 or VT_ID=2)
end
else
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
 Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
 [CR_Modification_Date] ,
 Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
 Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
 CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
 LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
 LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
 LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
 LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
 CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
 AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
 AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
 AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
 AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
 AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
 AND (
 (
 (@.keyword1 is null) or
 (Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword1 + '%')
 )
 AND
 (
 (@.keyword2 is null) or
 (Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword2 + '%')
 )
 )
 AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
 AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
 AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
 AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
 AND (
 (@.JF_Name is null)
 OR
 (
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
 OR
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
 )
 )
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
 and(VT_ID=1 or VT_ID=2)
end
end
else
begin
if (@.type='Employer')
Begin
 set @.emp_id=@.employer_id
End
Else
Begin
 set @.emp_id=-@.employer_id
End
if @.KeywordSearch = 'AND'
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
 Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
 [CR_Modification_Date] ,
 Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
 Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
 CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
 LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
 LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
 LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
 LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
 CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
 AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
 AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
 AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
 AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
 AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
 AND (
 (
 (@.keyword1 is null) or
 (Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword1 + '%')
 )
 AND
 (
 (@.keyword2 is null) or
 (Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword2 + '%')
 )
 )
 AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
 AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
 --AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
 AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
 AND (
 (@.JF_Name is null)
 OR
 (
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
 OR
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
 )
 )
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
 and(VT_ID=1 or VT_ID=2)
end
else if @.KeywordSearch = 'OR'
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
 Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
 [CR_Modification_Date] ,
 Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
 Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
 CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
 LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
 LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
 LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
 LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
 CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
 AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
 AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
 AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
 AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
 AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
 AND (
 (
 (@.keyword1 is not null) and (
 (Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword1 + '%'))
 )
 OR
 (
 (@.keyword2 is not null) and (
 (Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword2 + '%'))
 )
 )
 AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
 AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
 --AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
 AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
 AND (
 (@.JF_Name is null)
 OR
 (
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
 OR
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
 )
 )
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
 and(VT_ID=1 or VT_ID=2)
end
else
begin
SELECT DISTINCT top 1001 RType+Cast(CR_Id as varchar) as R_Id, RType, Seekers.Seeker_Id,
 Seekers.Seeker_First_Name + ' ' + Seekers.Seeker_Last_Name AS Seeker_Name,
 [CR_Modification_Date] ,
 Majors.Major_Name, Universities.University_Name, Countries_Cities.CC_Name as Nationality,
 Countries_Cities_1.CC_Name as Residence, CR_Experience_Years, JF_Id_1, JF_Id_2,
 CASE WHEN Seekers.Seeker_Id in (select seeker_id from seekers_competency_test where sct_show = 1) then '' else '' end as Competency
FROM Seekers With (NOLOCK) INNER JOIN Resumes With (NOLOCK) ON Resumes.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seeker_Universities With (NOLOCK) ON Seeker_Universities.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Seekers_Language_Skills With (NOLOCK) ON Seekers_Language_Skills.Seeker_Id = Seekers.Seeker_Id
 INNER JOIN Universities With (NOLOCK) ON Universities.University_Id = Seeker_Universities.University_Id
 LEFT OUTER JOIN Parameters With (NOLOCK) ON CS_Parameter_Id = Parameter_Id
 LEFT OUTER JOIN Majors With (NOLOCK) ON Seeker_Universities.Major_Id = Majors.Major_Id
 LEFT OUTER JOIN Countries_Cities With (NOLOCK) ON Countries_Cities.CC_Id = Seekers.CC_Id_Nationality
 LEFT OUTER JOIN Countries_Cities Countries_Cities_1 With (NOLOCK) ON Countries_Cities_1.CC_Id = Seekers.CC_Id_Residence
WHERE completed=1 AND
 CR_Delete=0 AND ((status <> @.emp_id and status <> 0) or status is null) AND (Seeker_Deleted = 0 OR Seeker_Deleted IS NULL)
 AND (@.CS_Parameter_Id is null or CS_Parameter_Id>=@.CS_Parameter_Id)
 AND (@.CC_Id_Nationality is null or CC_Id_Nationality = @.CC_Id_Nationality)
 AND (@.CC_Id_Residence is null or CC_Id_Residence = @.CC_Id_Residence)
 AND (@.Seeker_Owns_Car is null or dbo.Seekers.Seeker_Owns_Car = @.Seeker_Owns_Car)
 AND (@.Seeker_Country is null or @.Seeker_Country in (SELECT CC_Id FROM Seekers_Countries WHERE Seekers.Seeker_Id = Seekers_Countries.Seeker_Id))
 AND (
 (
 (@.keyword1 is null) or
 (Resumes.CR_career_objective like '%' + @.keyword1 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword1 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword1 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword1 + '%')
 )
 AND
 (
 (@.keyword2 is null) or
 (Resumes.CR_career_objective like '%' + @.keyword2 + '%') or
 (Resumes.CR_Training_Courses like '%' + @.keyword2 + '%') or
 (Resumes.CR_Community_Services like '%' + @.keyword2 + '%') or
 (Resumes.CR_Other_Information like '%' + @.keyword2 + '%')
 )
 )
 AND (@.CR_Experience_Years1 is null or CR_Experience_Years >= @.CR_Experience_Years1)
 AND (@.CR_Experience_Years2 is null or CR_Experience_Years <= @.CR_Experience_Years2)
 --AND ((@.Major_Id_1 is null AND @.Major_Id_2 is null AND @.Major_Id_3 is null) OR (Majors.Major_Id in (@.Major_Id_1,@.Major_Id_2,@.Major_Id_3)))
 AND ((@.University_Id_1 is null AND @.University_Id_2 is null AND @.University_Id_3 is null) OR (Universities.University_Id in (@.University_Id_1,@.University_Id_2,@.University_Id_3)))
 AND (
 (@.JF_Name is null)
 OR
 (
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_1))
 OR
 (@.JF_Name like (SELECT '''%' + JF_Job_Field + '%''' FROM Jobs_Fields WHERE JF_Id = Resumes.JF_Id_2))
 )
 )
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'AND') or (@.Language_Id_1 = Seekers_Language_Skills.Language_Id) OR (@.Language_Id_2 = Seekers_Language_Skills.Language_Id))
 AND ((@.Language_Id_1 is null AND @.Language_Id_2 is null) OR (@.Language_Param = 'OR') or (@.Language_Id_1 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id) AND @.Language_Id_2 in (SELECT DISTINCT Language_Id FROM Seekers_Language_Skills WHERE Seekers.Seeker_Id = Seekers_Language_Skills.Seeker_Id)))
 and(VT_ID=1 or VT_ID=2)
end
end
GO

my problem here that if i chooesed one major_id only it will consider the other majors id as null coz i give initalize value and it will return majors which contain null values
i want it if it found majorid2 for example null ignore it just search for majors which have specific id not null hope anybody can helpHello can anybody help|||I haven't replied because that is just too much code to look through and I am not clear on what/where your problem is.

Could you pare down the code to just a relevant chunk and re-explain your problem?

Terri|||i don't know where the problem in the code
but my problem here that i the user may have 3 majors
if he chooe one the otheres will be null but i don't want my query to return null value
for example
the user enter
major_id_1 = 183
then the others will be null
i want to recieve the result with just major_id 183 i don't want any null value hope u get this but where the error in the code i don't know|||You are saying "my query" but you have 7 long queries below with embedded logic. That is too much to expect someone to look at, understand, and debug.

Please pare your code down to 1 or 2 queries that are not functioning as you you desire and then we should be able to help. You need to strip out all of the code not related to the problem -- and actually in doing so you might discover the solution for yourself.

Terri

Alignment result

USE PUBS
GO
CREATE FUNCTION [dbo].[GetSpace] ()
RETURNS int AS
BEGIN
RETURN (SELECT max(len(fname)) + 1 FROM employee)
END
GO
SELECT top 5 fname + SPACE([dbo].GetSpace() - LEN(fname)) + lname AS
Expr1, [dbo].GetSpace() AS Expr2, LEN(fname) AS Expr3
FROM dbo.employee
When I execute the query over the query analyzer it gave me the correct
result like that...
Expr1
Aria Cruz
Annette Roulet
Ann Devon
Anabela Domingues
Carlos Hernadez
When I execute the query over the Enterprise Manger > View > Create New View
>
SELECT top 5 fname + SPACE([dbo].GetSpace() - LEN(fname)) + lname AS
Expr1, [dbo].GetSpace() AS Expr2, LEN(fname) AS Expr3
FROM dbo.employee
It gave the result on view pannel like that...
Expr1
Aria Cruz
Annette Roulet
Ann Devon
Anabela Domingues
Carlos Hernadez
I mean no alignment in the view pannel and when we call the same view over
the front end so it will gave the same unalign result.
Thanks
GetSpace user function returns the appropriate number of spaces but in
Enterprise Manager the problem is the font used.
Copy the result from Enterprise Manager in a word editor (like Microsoft
Word) and set the font to Courier New and Size 10. You will see that the
results will be aligned. The default font for results in Query Analyzer is
Courier New and Size 10.
Cristian Lefter, SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:%235HNgnSYFHA.228@.TK2MSFTNGP12.phx.gbl...
> USE PUBS
> GO
> CREATE FUNCTION [dbo].[GetSpace] ()
> RETURNS int AS
> BEGIN
> RETURN (SELECT max(len(fname)) + 1 FROM employee)
> END
> GO
> SELECT top 5 fname + SPACE([dbo].GetSpace() - LEN(fname)) + lname AS
> Expr1, [dbo].GetSpace() AS Expr2, LEN(fname) AS Expr3
> FROM dbo.employee
> When I execute the query over the query analyzer it gave me the correct
> result like that...
> Expr1
> Aria Cruz
> Annette Roulet
> Ann Devon
> Anabela Domingues
> Carlos Hernadez
> When I execute the query over the Enterprise Manger > View > Create New
> View
> SELECT top 5 fname + SPACE([dbo].GetSpace() - LEN(fname)) + lname AS
> Expr1, [dbo].GetSpace() AS Expr2, LEN(fname) AS Expr3
> FROM dbo.employee
> It gave the result on view pannel like that...
> Expr1
> Aria Cruz
> Annette Roulet
> Ann Devon
> Anabela Domingues
> Carlos Hernadez
> I mean no alignment in the view pannel and when we call the same view over
> the front end so it will gave the same unalign result.
> Thanks
>
|||You are right. Thanks
"Cristian Lefter" <nospam_CristianLefter@.hotmail.com> wrote in message
news:ONgxYpcYFHA.2768@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> GetSpace user function returns the appropriate number of spaces but in
> Enterprise Manager the problem is the font used.
> Copy the result from Enterprise Manager in a word editor (like Microsoft
> Word) and set the font to Courier New and Size 10. You will see that the
> results will be aligned. The default font for results in Query Analyzer is
> Courier New and Size 10.
> Cristian Lefter, SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:%235HNgnSYFHA.228@.TK2MSFTNGP12.phx.gbl...
over
>