SQL server connection: update

Showing posts with label update. Show all posts

Tuesday, March 20, 2012

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:

>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base tableYou can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:

>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base tableThis is a multi-part message in MIME format.
--000602000304030604090506
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:
>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>
--000602000304030604090506
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can "update" a view in certain restricted conditions (like
there can't be any joins in the view for instance - see BOL for the
complete list of restrictions).Â Alternately, you can write an INSTEAD
OF trigger on the view so that the trigger code handles the updating of
the underlying tables when a user runs an UPDATE statement against the
view.Â You can use this method when the basic updatable view
requirements cannot be met. 
 
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view). 
 
Cheers, 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
KathyV wrote:
<blockquote cite="mid282DBEFC-5100-4936-A1EA-A11561D1DDD4@.microsoft.com"
type="cite">
<pre wrap="">How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
</pre>
</blockquote>
</body>
</html>
--000602000304030604090506--

Monday, March 19, 2012

another sql query ?

I'm trying to do a mass update, w/different values: I have an Approval Table with the columns, FacilityID, CompanyID, EmployeeID, and SupervisorID. I would like to take the Users SuperID in the [User] Table who have a roleid= 'Supervisor' and place it in the Approval Table. However in order to know which super matches which employee you must match the Employee tables DeptID to the User Tables Department field.

I did the following and it gives me an error:

UPDATE Approval SET SupervisorID = (select u.EmployeeID from [user] u INNER JOIN Employee e ON u.Department = e.deptid WHERE roleid = 'supervisor')

The error is:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

The select gives me the right data--this much I've checked.

You sub-query is probably returning more than 1 row in some cases. You have to make sure you only get one result row. Try:

UPDATE Approval SET SupervisorID = (selectTOP 1u.EmployeeID from [user] u INNER JOIN Employee e ON u.Department = e.deptid WHERE roleid = 'supervisor')|||Well see that's what I'm confused about--I would like to update all of the SupervisorID's in the table at once. There are 125 rows in the Approval Table, and about 10 different superviosrID's in the User Table. Am I going about this wrong?|||

You have to make sure to provide a condition where the table you're updating has one-to-one relationship with the sub-query value returned for each row. In your query, you have nothing that defines the relationship between the Approval table (one you're updating) and other tables in the sub-query. I can't tell you what it should look like since I have no clue on what the tables look like. There must be some column in the Approval table that links to other tables being referenced in your sub-query, so provide that relationship in your where clause.

|||

You've almost got it. Problem is your subselect is returning ALL the supervisors for every department. Your statement therefore is trying to set the Supervisor in the Approval table to every supervisor, which...You can't do.

You want to set the supervisor in the approval table to a specific supervisor. In order to do that, your subselect needs to know how it relates to the rows in the approval table. In english, that means you need something else in your WHERE clause in the subselect, something along the lines of... AND e.EmployeeID=approval.EmployeeID.

|||I see I got it now--your addition of query code worked great. Appreciate the help.|||Ok--since I'm stuck I might as well keep on asking--this is a new SQL query I have an SP already and it's the following:

SELECT
e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e
LEFT JOIN EmployeeEval ev --this left join will return all rows from Employee, but only rows from Eval where the employee is in.
ON e.EmployeeID = ev.EmployeeID AND ev.PeriodID = @.Period
WHERE
(
(ev.Approved = 0 OR ev.Approved IS NULL) --get the ones that aren't approved
OR ev.EmployeeID IS NULL -- get the ones that haven't reviewed
)
AND (e.DeptID = @.deptID) and Status = 'Active'
GO

So now I need to incorporate my Approval Table, we're trying to get rid of being dependent on finding employees based on Dept, b/c we need some supervisors to be in charge of multiple departments.

So I need to Pull back the Info in the SELECT above and keep the EmployeeEval stuff. As well I need to remove the Dept data at the bottom of the above query. My approval table has the following columns: FacilityID, CompanyID, EmployeeID, and SupervisorID so I was thinking do something like this:

SELECT
e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e LEFT JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID AND ev.PeriodID = 175
INNER JOIN Approval a ON e.employeeid = a.employeeid
WHERE
(
(ev.Approved = 0 OR ev.Approved IS NULL) --get the ones that aren't approved
OR ev.EmployeeID IS NULL -- get the ones that haven't reviewed
)
AND e.Status = 'Active' AND a.supervisorID = '09246'
GO

But it's returning 0 rows--I have one person in the approval table with supervisorID set to '09246'. they have no data in the employeeeval table. But in my where clause i have the ev.employeeid is null which should still allow 4 them to be brought back--let me know if this doesn't make sense.|||well I guess my query was right--I just didn't have my settings in the new table set up right--it's now showing up. thanks for all the help.

Sunday, March 11, 2012

Another Question SP

Hi,
I have this I need to be able to update the information based on custid.
DECLARE @.sql1 Varchar(8000)
DECLARE @.custid varchar (10)
DECLARE @.custname varchar (80)
DECLARE @.doc varchar(8000)
SET @.custid='000900'
SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where c.custid='
+ @.custid Is this wrong?
SET @.custname = @.sql1
SET @.doc ='
<STORE>
<ADDRESS>
<CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
<CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a variable
here based on the custid,
<ADDR_1>@.address1</ADDR_1>
<ADDR_2>@.address2</ADDR_2>
<ADDR_CITY>@.city</ADDR_CITY>
<ADDR_STATE>@.region</ADDR_STATE>
<ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
</ADDRESS>
When I run this I am getting NULL what is wrong with this?
Thanks
DibTry,
Select @.custname = c.custname From dbo.tblArCust c where c.custid = @.custid
...
AMB
"Dib" wrote:

> Hi,
> I have this I need to be able to update the information based on custid.
> DECLARE @.sql1 Varchar(8000)
> DECLARE @.custid varchar (10)
> DECLARE @.custname varchar (80)
> DECLARE @.doc varchar(8000)
> SET @.custid='000900'
> SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where c.custid
='
> + @.custid Is this wrong?
> SET @.custname = @.sql1
> SET @.doc ='
> <STORE>
> <ADDRESS>
> <CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
> <CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a variab
le
> here based on the custid,
> <ADDR_1>@.address1</ADDR_1>
> <ADDR_2>@.address2</ADDR_2>
> <ADDR_CITY>@.city</ADDR_CITY>
> <ADDR_STATE>@.region</ADDR_STATE>
> <ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
> </ADDRESS>
> When I run this I am getting NULL what is wrong with this?
> Thanks
> Dib
>
>|||Thanks, it worked. How can I handle NULL value here
Dib
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CF2BF93E-091C-494D-A625-19D36E041E9C@.microsoft.com...
> Try,
>
> Select @.custname = c.custname From dbo.tblArCust c where c.custid =
@.custid
> ...
>
> AMB
> "Dib" wrote:
>
c.custid='
variable|||Doesn't look like you have initialised @.sql1. Set it to a blank space and
try again.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
news:uZZHW4VjFHA.1196@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have this I need to be able to update the information based on custid.
> DECLARE @.sql1 Varchar(8000)
> DECLARE @.custid varchar (10)
> DECLARE @.custname varchar (80)
> DECLARE @.doc varchar(8000)
> SET @.custid='000900'
> SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where
> c.custid='
> + @.custid Is this wrong?
> SET @.custname = @.sql1
> SET @.doc ='
> <STORE>
> <ADDRESS>
> <CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
> <CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a
> variable
> here based on the custid,
> <ADDR_1>@.address1</ADDR_1>
> <ADDR_2>@.address2</ADDR_2>
> <ADDR_CITY>@.city</ADDR_CITY>
> <ADDR_STATE>@.region</ADDR_STATE>
> <ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
> </ADDRESS>
> When I run this I am getting NULL what is wrong with this?
> Thanks
> Dib
>|||This is not working
This is what I did
SET @.custid = ''
SET @.custname = ''
SET @.address1 = ''
SET @.address2 = ''
SET @.city = ''
SET @.region = ''
SET @.zipcode = ''
SET @.doc =''
SET @.custid='000900'
SET @.custname = (Select case WHEN tblArCust.custname = NULL Then ''End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.address1 = (Select case WHEN tblArCust.Addr1 = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.address2 = (Select case WHEN tblArCust.Addr2 = NULL Then ''END From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.city = (Select case WHEN tblArCust.City = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.region = (Select case WHEN tblArCust.Region = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.zipcode = (Select case WHEN tblArCust.PostalCode = NULL Then'' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.doc ='
It worked fine at first when I changed the Custid to 00500 it returned NULL
now nothing works.
Thanks
Dib
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uLcdeFWjFHA.708@.TK2MSFTNGP09.phx.gbl...
> Doesn't look like you have initialised @.sql1. Set it to a blank space and
> try again.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
> news:uZZHW4VjFHA.1196@.TK2MSFTNGP10.phx.gbl...
>|||On Wed, 20 Jul 2005 15:51:26 -0400, Dib wrote:

>This is not working
>This is what I did
>SET @.custid = ''
>SET @.custname = ''
>SET @.address1 = ''
>SET @.address2 = ''
>SET @.city = ''
>SET @.region = ''
>SET @.zipcode = ''
>SET @.doc =''
>
>SET @.custid='000900'
>SET @.custname = (Select case WHEN tblArCust.custname = NULL Then ''End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.address1 = (Select case WHEN tblArCust.Addr1 = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.address2 = (Select case WHEN tblArCust.Addr2 = NULL Then ''END From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.city = (Select case WHEN tblArCust.City = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.region = (Select case WHEN tblArCust.Region = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.zipcode = (Select case WHEN tblArCust.PostalCode = NULL Then'' End Fro
m
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.doc ='
>
>It worked fine at first when I changed the Custid to 00500 it returned NULL
>now nothing works.
>Thanks
>Dib
Hi Dib,
There are several problems with your code.
1. comparing to NULL with the = operator will allways fail - no value is
equal to NULL, not even NULL itself. (This is by design - NULL is
regarded as unknown values, so comparing NULL to NULL is like comparing
two unknown values; you can never say if two unknown values are equal or
unequal). Use IS NULL instead of = NULL for correct results.
2. The CASE statement lacks an ELSE part. If the WHEN part is not true
(which it never is, due to the reason above), and there is no ELSE part,
the result will default to NULL.
3. It is highly inefficient to execute the same query six consecutive
times. Instead, use the Transact-SQL extension to the SELECT statement
to assign values to all variables at once.
Here's some code to replace the snippet above. I also replaced the CASE
expressions with COALESCE expressions, that are (in this case)
equivalent. Oh, and since I use SELECT instead of SET, there's no need
to initialize the variables first - they'll always be assigned a value
in this statement!
SET @.custid = '000900'
SELECT @.custname = COALESCE(custname, ''),
@.address1 = COALESEC(Addr1, ''),
@.address2 = COALESCE(Addr2, ''),
@.city = COALESCE(City, ''),
@.region = COALESCE(Region, ''),
@.zipcode = COALESCE(PostalCode, '')
FROM dbo.tblArCust
WHERE custid = @.custid
(untested, since you didn't provide CREATE TABLE and INSERT statements)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, February 24, 2012

ANN: Updated version of SQL Server 2005 Books Online is available for download

The September 2007 update to SQL Server 2005 Books Online (English version)
is now available for download at
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en.
It is also offered as an Optional update via Microsoft Update.
See the topic "New and Updated Books Online"
(ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
links to the new and revised topics.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
On Dec 1, 5:53 am, "Gail Erickson [MS]" <ga...@.online.microsoft.com>
wrote:
> The September 2007 update to SQL Server 2005 Books Online (English version)
> is now available for download athttp://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00d...
> It is also offered as an Optional update via Microsoft Update.
> See the topic "New and Updated Books Online"
> (ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
> links to the new and revised topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online fromhttp://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
Thanks for the updation

ANN: Updated version of SQL Server 2005 Books Online is available for download

The September 2007 update to SQL Server 2005 Books Online (English version)
is now available for download at
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en.
It is also offered as an Optional update via Microsoft Update.
See the topic "New and Updated Books Online"
(ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
links to the new and revised topics.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspxOn Dec 1, 5:53 am, "Gail Erickson [MS]" <ga...@.online.microsoft.com>
wrote:
> The September 2007 update to SQL Server 2005 Books Online (English version)
> is now available for download athttp://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00d...
> It is also offered as an Optional update via Microsoft Update.
> See the topic "New and Updated Books Online"
> (ms-help://sql90/sqlwhtn9/html/0f2f37d8-fc81-40c5-9976-cab8bd0b44e9.htm) for
> links to the new and revised topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online fromhttp://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
Thanks for the updation

Tuesday, March 20, 2012

Another version of SQL Permission Question

Another version of SQL Permission Question

Another version of SQL Permission Question

Monday, March 19, 2012

another sql query ?

Sunday, March 11, 2012

Another Question SP

Friday, February 24, 2012

ANN: Updated version of SQL Server 2005 Books Online is available for download

ANN: Updated version of SQL Server 2005 Books Online is available for download

SQL server connection

Blog Archive

About Me