Dear Freinds,
we are having an requirement to log the unauthorized backend update on database.Is there any other way ,apart from going and creating the triggers at each table to log the backend update.
Note :Is Database base level trigger is possible on SQL Server 2000?
Looking for your reply.
With Cheers :beer: ,
Sathesh.M
Hello,
Is there any ability to do database-level triggers in SQL 2000? I have a SQL 2000 database, and I was asked if we could create a trigger that whenever anyone touches the data in a database, to create an entry in an event log? If not, I have a main table I can put a trigger on; however, my question is how do you write to a file in a trigger as well?
Thanks.
I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance
How can i write a trigger which gets fired for each row that is being deleted
i.e. Row level Trigger
I tried something like this
ALTER TRIGGER [dbo].[Del_Msg]
ON [Seat27].[dbo].[Email_Messages]
AFTER DELETE
&.......
it gets executed once for the triggering SQL statement
Hi all
I am doing a DB porting project in which i need to translate oracle specific queries to sql server.I need to convert following trigger to sqlserver specific .Any help will be appreciated
CREATE OR REPLACE TRIGGER T_BI_R_TARGET_OBJECTIVE
BEFORE INSERT
ON TARGET_OBJECTIVE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (NEW.TARGET_OBJECTIVE_ID IN (NULL,0))
DECLARE
seq_id NUMBER;
BEGIN
SELECT TARGET_OBJECTIVE_ID_SEQ.NEXTVAL INTO seq_id FROM dual;
:new.TARGET_OBJECTIVE_ID := seq_id;
END;
Regards
sreenath
Hi,
Is there any row-level trigger available in SQL server(in oracle is does)
my problem is ..I am trying to write a trigger which updates all the items
But it give me error:
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.
My statement is : SET @var_item=(SELECT item_no FROM deleted)
How can i make it possible?
Note: I am trying convert Oracle trigger to SQL Server trigger
hi
i want to know if row level trigger works with sql 2005.
because in 2000.. only statement level trigger was used.
Thks in advance.
hi
actually i have a temporay table wich has four columns
say col_1,col_2,col_3,col_4.
so firstly i wud bulk insert from a text file wich mite contain thousands of rows.
then from this temp table col_1 and col_2 shd go to master_a.wich has an identiy column as primary key.
say id,col_1, col_2.
i will have another table detail_b where in there is a foreign key to the table master_a for id.
so it will have f_id, col_3,col_4.
so i am riting a trigger on master_a. so whenver row is inserted in master_a. coresponding id and col 3 col4 shd be inserted into detail_b.
For this in oracle we have row level trigger. where in for each insertion in master a .. correspoding trigger will fire but in sql 2000..
but i wud like to implemtn row level trigger..
can u hlep me out..
I need to pass a application userid on every call to my database for triggers on the db to use.
I am connecting to my database using integrated security from my application, all on one windows domain account. However I need to get an application user id over on the connection for triggers to use to stamp in changelogs.
Currently everytime we open a connection we create a #table with the user id in it, but that is expensive, especially in our new SOA where connections are frequently created and dropped on requests.
Is there any way I can get a connection level variable over to the database server so triggers could read it?
Any help is greatly appreciated.
The problem that I am having is that with Visual Web Developer I am creating a webpage and having it directly put online, so for example when I start a new ASP.NET page, I select the location to be HTTP, with the location http://MYWEBSERVER/Website and for the language and Visual BasicI notice a couple of things, first that there is no longer a a link under the Main toolbar "Website" selection called the ASP.NET configuration. So how can I configure what I want to have users be able to do? It seems that this choice is only available if I am building the ASP.NET page on my "localhost". So that is the first problem. So I am able to get the pages to work, atleast the things such as the textboxes to show up etc, (Even things as advanced as the "Login" box). How ever when I try to get someone to try to login I am taken to a page that has an server error. The error is:
"The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. "
The Stack Trace Errors are at the bottom. I think this is happening because the automatically generated databases are not getting built online as they are on my computer. On my computer I have MSSQL express. So either the databases are not getting built for some reason, (and I think that is the case as I don't see any in the folder). So I think that somehow I have to create a database on my server, and then somehow configure the ASP.NET file, perhaps in the Web.Config file to look for that new database. Is this the correct methodology? Is there some simpler way that I can just somehow upload things as they are and have them work correctly on my server? The error says that either the Server did not find the database or that the trust level was insufficient. I don't think that is it as I just looked again and I don't see any .MDF files. So how would I go about getting this to work right? Is there a way to do this with MySQL also? So that I don't have to use MSSQL? My server only allows 1 DataBase for that.
Thanks and I hope my question makes sense. It is basically how can I get it to be able to create and check users etc. online?
Brian
[ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.] System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2555237 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
hello guysi am using a table that its secondary key connected to its primary key...and as sql server 2000 doesnt allow cascade delete fore such,i had to write a trigger myselfso i wrote the following triggerCREAT TRIGGER nameON tableFOR DeleteASBEGINIF @@ROWCOUNT >0Delete from table where table.parentID in (select sortID from deleted);ENDthen i went to the table and i tried to delete...and it gave me an error....that there are records that have there parentID= sortID of the table i am trieng to delete...so i deleted the relationship...and kept the triggerand now ...when i delete one...it deletes one level down....but not more....i mean when i delete sortID=4it deletes all the records that has parentID=4...and NOT more..whereas my aim was to have it recursive not to have records lost in my databasehope i explained good as much as i hope to find an answer soon...a clear one...and thanks in advanced...
View 3 Replies View RelatedHello all.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
tia,
Jeremy
Hi all,
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
Hello,
I am running this query
"delete from ims_domains where id=61"
and got the error
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
Please let me know what should be the reason?
Thanks,
Ravi
Hi,
I face this error when i try to run my store procedure.
The sample of store procedure as following:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_addUserAccess
with encryption
AS
SET NOCOUNT ON
DECLARE @COUNTER INT
SET @COUNTER = 0
DECLARE @i_compId INT
BEGIN
DECLARE C1 SCROLL CURSOR FOR
SELECT i_compId
FROM ltd_cms_company WHERE (i_owner = 176 or i_owner = 268) AND ti_recStatus = 1
END
OPEN C1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ltd_cms_userAccess ( i_loginId, i_groupId, i_compId, ti_updComp, ti_updLog, ti_updAccess, ti_owner, ti_acctMgr, ti_updContact, ti_updEvent )
VALUES ( 124, 0, @i_compId, 1, 1, 1, 1, 1, 1, 1)
SET @COUNTER = @COUNTER + 1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
END
CLOSE C1
DEALLOCATE C1
SET NOCOUNT OFF
anyone can help me identify this error?
Thanks
Regards,
Jojomay
I have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
Hi all, I get this message when trying to update a tabel i have whichhas nested hierarchies.The current hierarchies beginning from root = 1 are up to the level 5.Before going into details and sample data with all the sql queries andprocedures, this limitation from Microsoft for nested levels .. isthere any way or trick to increase the level in generic?
View 1 Replies View RelatedI have created a delete trigger in Table1 and Table2. Once I delete a certain record in Table1 it will also delete that record in Table2 or vice versa. But once i delete certain record either in Table1 or Table2 it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". Can you help me on this?
View 4 Replies View RelatedHi,We have encountered deadlock on a table which is used to generatesequential numbers for different categories eg typical entriesCategory ValueTRADE_NO 1456JOB_NO 267.....The applications reference the relevant category applicable to themand updatethe Value accordingly. This is table is very small, occupying 1 page.However, it has no index as it was not seen to be appropriate for atable this size.However, can someone please advise whether1. An index is required for row level locking2. If an index on a table as small as above is likely to reduce thedeadlock rate.Also, please consider the following but which I am not sure isrelevant for above query.We noted that when we migrated the database concerned from SQL 6.5 toSQL 2000, using DTS, that the database was NOT strictly in SQL 2000format for non clustered indexes (NC) ie the clustered key was notpart of the NC index until the clustered index was rebuilt.Given this should I just rebuild this table with a fake index and dropit thereafter.We are aware of the different techniques used to avoid deadlocks (egtables accessed in same order etc) and have , as much as possible,implemented those practices.I thank you in advance for any help you may be able to offer.ThanksPuvendran
View 12 Replies View RelatedDevteam writes "Hi,
WIndows version 5.0 (SP4)
I am using sql server 2000. in our application i am using simple select statement to retrieve the value from the database. Sometime the same query is accessed i did not get the result.i have tried to run the same query using query analyzer. but the query is keep on running. at the same time when accessing other rows in the table, it gets retrieve the values.
what is the problem here... is there anything locked that the row before executing the select statement.
if there is any lock is present then how to identify it and also how to prevent a row from locking... if the lock is occured then the other transactions also not executed. why the lock is placed in the first place."
Hallo Everyone,
I have an SQL database that I need to detach from an SQL2005 server and reattach to an SQL 2000 database. I tried to set the Compatibility level from SQL Server 2005 (90) to SQL Server 2000 (80). This did not work
Any ideas?
Nigel...
HiIs it possible to force row level locking in one or more tables insome database. We have some problems when SQL Server decides to choosepage- or table-level locking.We are using SQL Server 2000.Best regardsAarno
View 1 Replies View RelatedI did an in place upgrade of my SQL 2000 server to SQL 2005.
Now all the system and user databases are set to compatibilty mode 80 and in the options tab there is no compatibilty mode 90 option.
How can I set the databasbes to compatibility mode 90?
Thanks!
We had a disaster last week (SAN Corruption) and it hit a bunch of my sql serves.
I have been able to recover all but one. The one I am having a problem with is a NAMED Instance that I obviously don't know the original SP level on. When I try to restore MSDB it won't let me because of a version conflict
Is there any way to tell what the SP Level is either from a system DB .mdf or LDF file or from a backup file without restoreing?
Right now I'm installing SQL Server to a test server and I'm going to try and restore the system dbs at each patch level.. seems like there must be a better way!!
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.
I found this script online..
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
but the results are this: 2 columns - User and Permission
User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT
IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?
Hi!
After upgrading SQL Server from 6.5 to 7.0 my production database compatibility level is "65".
I checked that by executing sp_dbcmptlevel <database_name>.
I can change it to "70" but my question is how it's going to affect the application and do I have to change it?
Thank you
Lena
Database level password security
View 2 Replies View RelatedI have many tables and in those i require to change some data. Sayfrom ARCA to ARCAEX. I am sure that the string is unique in the sensethere will be no ARCAABC. So what do i do change by not manuallyneeding to search in each table and the whole database and still canbe sure that the changes have taken place. Please helpRegards,Rajesh
View 2 Replies View RelatedHi,
Is there a way to allow specific databases access to the CLR. Currently, my understanding is that when this setting is enabled, it applies to all databases within the instance.
Kind regards,
Jan.
Hi,
How do I lock entire database? I want an exclusive lock on the db by
a user
who is the dbo of that database only (not sa
user).
The
scenario is we have a web application and each week we need to do data
uploads (with etl). During this upload, the users accessing the website
should not be able to read data. This is why I want database lock.
Now
the catch here is, the application access the data using user say abc.
Abc is dbo for that database and the etl is also done by abc login. So
will db locking help in this case as the website can also read the data
being a abc user?
Thanks,
Tanmaya
Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance
Hi,i created at general database level a login 'Network service' (windows server 2003).At level of 'mydb' database, i created an user 'aspnet' and i associated it to the login 'Network service'. That user 'aspnet' receives the necessary schemas (db_read /db_write) rrquired by the application.Now, i removed the login 'Network Service' at general database level (not the user 'aspnet') but the application still runs properly.My question is: is it allowed to remove a defined login at general database level (in my case the login 'Network Service')?ThanksJean
View 5 Replies View Related