|
OK - I have a database with a table [User permissions] which controls what the user of the print monitoring application is allowed to do (i.e. see only own documewnts, or all etc.)
When a user submits a print job their name is passed to the database by the print monitoring service. If they have not been added to this table then they are added with the minimum permissions. I would like this step to also set up their database log in so have set a trigger thus:-
<br />
<br />
CREATE TRIGGER User_Permissions_Insert<br />
ON dbo.[User Permissions]<br />
FOR INSERT<br />
AS<br />
<br />
DECLARE @DOMAIN VARCHAR(200),<br />
@FULL_USERNAME VARCHAR(255)<br />
<br />
<br />
SELECT @DOMAIN = ISNULL([Parameter Value],'')<br />
FROM dbo.[PUMA Parameters]<br />
WHERE [Parameter Name] = 'NT Domain'<br />
<br />
IF @DOMAIN = ''<br />
SELECT @FULL_USERNAME = [User Name]<br />
FROM inserted<br />
ELSE<br />
SELECT @FULL_USERNAME = LTRIM(@domain) + '\' + Ltrim([User Name])<br />
FROM inserted<br />
<br />
<br />
-- Add the NT login to the database logins list<br />
EXECUTE sp_grantlogin @FULL_USERNAME<br />
<br />
-- And grant them access to the current database<br />
EXECUTE sp_grantdbaccess @FULL_USERNAME<br />
<br />
<br />
RETURN
However when I cause the trigger to fire I get the error message:-
The procedure 'sp_grantlogin' cannot be executed within a transaction.
Any ideas how I would go about doing this, or is a trigger the wrong solution for this case?
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
A trigger is really the wrong solution for this. You'll never be able to avoid the transaction because, even if not in an explicit transaction created with BEGIN TRANSACTION, a trigger executes in the context of the statement that caused it to fire, which is implicitly transactional.
I'd argue that triggers are for two purposes - for enforcing consistency (although you should normally use a constraint for that), and for selective denormalization - the trigger updates the denormalized data as the normalized data is updated. For example, updating an 'order total' field of an order record when order line records are inserted, updated or deleted. They're also useful for trapping and redirecting attempted updates to a view - this is really another case of handling denormalization or partitioning.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Agreed - I have decided to use an NT Group to adminsiter permissions to the database. Thus membership of teh group becomes a LAN team job 
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
Hello,
I am using C# and creating a ASP.Net application that uses SQL Server 2000. I have customers that have to be input by a user. etc, name, address, e-mail. But I don't want the user to enter a customer ID number. I want to be able to automatically generate a unique number, so the user does not have to input one themselfs. In MS Access you had auto number that did this for you. Is there something that can be used in SQL Server.
Many thanks in advance,
Steve
|
|
|
|
|
You can use IDENTITY columns, or you could use the uniqueidentifier data type, which is a GUID. Generate a new GUID using the NEWID function.
The former is more human-friendly, but does have the property that, if a clustered index is built on the identity column, new records are placed on the last page, which can lead to contention for that page. The GUIDs, being essentially random, in general avoid this problem.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi, I dont know if you have solved your problem yet, but this is possible in SQL. Is the customerID you mention an int type?
|
|
|
|
|
Hello,
The problem has been solved now.
There are 2 ways to do this either using the GUID, which will generate a randon number or setting the SQL datatype to an Int and setting its property to Identifier. I have done it using the latter.
Many thanks for all your help,
Steve
|
|
|
|
|
hi guys i've posted this on both the SQL and ASP.Net forums cause i ain't sure which one i should use so sorry about clogging up the message board.
Basicly i'm a newbie with ASP.net and i was looking for somw help with this query
i got a site and as part of the functionality i need to create a page where the user can create their own SQL statements to query a database (obc one thats connected to the web page). I got fairly basic knowledge when it comes to asp.net but i know how to access stored procedures and basic DB conectivity, but i've know idea how to do it when the user is meant to define the whole query from multiple tables (if possible) i know its asking a lot but any advice no matter how small would be appriciated...oh and while i remember its an Access DB im connecting to.
|
|
|
|
|
I'd suggest avoiding this approach at all costs, because it means your user can essentially enter *anything*, which makes it VERY insecure.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
ok thats cool, could u suggest how it could be done anyway (its for a project you see and it has to allow this kind of reporting or something similar, the project will never go online anyway)
|
|
|
|
|
|
k any suggestions as to what i can do instead?
|
|
|
|
|
One system I built a couple of years ago gives very strict access to the account the ASP.NET web application was running in. It had SELECT access only on the tables is absolutely needed. Then the query was built using a standard strucutre (obviously this might be a little restrictive) whereby the user could only select valid table names and so on - no free text box. When all this information was posted back the details were checked again before permitting the query to run.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
k i guess i'll try that thanks for the advice
|
|
|
|
|
Hi there,
I've got a table which acts as a repository for searchable text data (lets call it MyInfoTable). Through a User Interface (Web) I'd like to let the user allow searching the Content field (type text) of MyInfoTable. The user can add various terms which are then used to build the query.
A couple of questions arise:
1. How can I build a injection-safe query which makes use of the WHERE clause and adds all the search terms into it. I'd like to do it through a stored proc but how can I specify a collection of params (one for each search term) to pass into it?
2. Is there a best-practise regarding searches on midium to large datasets? Things I have to take care of, etc.?
Sorry, if this questions are quite dumb, but I've never seen a fulltext search implemented and just trying to get started... 
Thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Hi, I think this may help you - I created this stored procedure for use in Northwind:
CREATE PROCEDURE SearchProducts (
@ProductName NVARCHAR(40) = null,
@UnitPrice MONEY = null,
@UnitsInStock SMALLINT = null
)
AS
SELECT *
FROM Products
WHERE (IsNull(@ProductName, ProductName) = ProductName) AND
(IsNull(@UnitPrice, UnitPrice) = UnitPrice) AND
(IsNull(@UnitsInStock, UnitsInStock) = UnitsInStock)
GO
Basically make sure you insert a null for all the parameters that you dont use.
|
|
|
|
|
The easiest way is to use Boolean logic in the where clause.
CREATE PROCEDURE ....
@LastName varchar(20),
@Department int
...
AS
SELECT @LastName = '%' + @LastName + '%'
SELECT ...
FROM ...
WHERE (@LastName Is Null
OR table.LastName LIKE @LastName)
AND (@DepartmentID Is Null
OR @DepartmentID = table.DepartmentID)
...
... This should get you started with the concept. Either the parameter is Null or the paramenter must represent a column value.
Michael
|
|
|
|
|
I have this one question in regards to configuration file - what if I need to configure for MULTIPLE DATASOURCE?
Example:
<nhibernate>
<add
key="hibernate.connection.provider"
value="NHibernate.Connection.DriverConnectionProvider"
/>
<add
key="hibernate.dialect"
value="NHibernate.Dialect.MsSql2000Dialect"
/>
<add
key="hibernate.connection.driver_class"
value="NHibernate.Driver.SqlClientDriver"
/>
<add
key="hibernate.connection.connection_string"
value="Server=localhost;initial catalog=MyDataSrc01;uid=SomeUser; pwd=xxx;"
/>
<add
key="hibernate.connection.connection_string"
value="Server=localhost;initial catalog=MyDataSrc02;uid=SomeUser; pwd=xxx;"
/>
</nhibernate>
This obviously doesn't work. Any suggestion? Thanks in advance.
Norman Fung
|
|
|
|
|
I got total pages difference between Print-Preview and Layout-Preview
I've set paper size.
in Layout-Priview the report generate 2 pages, but in print-preview the report generate 10 pages, it seems print-preview going wrong.
any idea ?
email : sukasukabo@hotmail.com
|
|
|
|
|
not sure what you meant sayign "Print-Preview and Layout-Preview". I had the same problems with pages# when tried to render to html. i were googling but no success then I've resolved that by addind the "page n of m". i parse a resulting html content using the regex "page \d+ of \d+". found max(m) and then removed page # from the html
|
|
|
|
|
what is the syntax of the query which gets the member properties asscociated with every level which are then used as predicted attributes.
that's because i am building a decision tree on data coming out from cube.
If I write this Query
select{[Customers].[Name].members,[Customers].[Country].members,[Customers].[City].members}on columns,
{[Time].[Year].members}on rows
from sales
he gives me on columns all the names of customers and their cities and their countries and inside each cell their measures related to every year with some empty cells.
I want to get the result as 2 dimensional table to build the tree and can calculate the greatest gain to get the root and then trace the path from the root to reach the leafs which hold the classes or the categories predicted.
Please anyone who is interested in olap cubes and data mining algorthims help me.
|
|
|
|
|
Hello,
Is there a way to select a row from a table based on a minimum? Or what is the correct syntax?
This query returns two rows when I only need a row with the min column... 
select min(col1ID), col2ID, col3 from tbl where col2ID = 'xxxx' group by col2ID, col3
Thanks,
jay
|
|
|
|
|
Jaydeanster wrote:
This query returns two rows when I only need a row with the min column...
select min(col1ID), col2ID, col3 from tbl where col2ID = 'xxxx' group by col2ID, col3
Your GROUP BY clause causes more than one row to be returned. I am guessing that you want the row with the MIN(col1ID) with the other columns too. So, assuming that each col1ID value is unique then the following should work:
SELECT m.col1ID, t.col2ID, t.col3
FROM tbl AS t
INNER JOIN (SELECT MIN(col1ID) as col1ID
FROM tbl
WHERE col2ID = 'xxxx') AS m ON m.col1ID = t.col1ID
WHERE col2ID = 'xxxx'
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
is it possible to work in rdl with an external dataset without the datasource mentioned alongwith it ?
Prithiv thirugnanasambandam Vasudevan
|
|
|
|
|
looks like you can't. read my previous post it seems i've described how to solve that
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
