How To Get Information From System Table Or View For Trigger's Definition
May 29, 2008Such as check all triggers that assign value to some columns ?
Thank you very much.
ADVERTISEMENT
Indexing View Definition Table Columns
Sep 29, 2007Hi experts,
Im very very new to sql server world..wanted to know what kind of indexes to be created on the below mentioned table columns for making this view run fastly.As of now there are no indexes created on these view definition columns
CREATE View hrinu.Parity as
select
T1.Matcle as CorpID,
T2.Nmpres as Name,
T4.DATDEB as LeaveFrom,
T4.TEMDEB as PM,
T4.DATFIN as LeaveTo,
T4.TEMFIN as AM,
T10.LIBLON as LeaveType,
T8.LIBLON as Location,
T12.LIBLON as ParentOrg
from HRINU.zy00 T1,
HRINU.zy3y T2,
HRINU.zy39 T3,
HRINU.zyag T4,
HRINU.zy38 T5,
HRINU.zy1s T6,
HRINU.zd00 T7,
HRINU.zd01 T8,
HRINU.zd00 T9,
HRINU.zd01 T10,
HRINU.zd00 T11,
HRINU.zd01 T12
where T4.Nudoss = T3.nudoss
and T4.Nudoss = T1.Nudoss
and T1.Nudoss = T2.nudoss
and T3.nudoss = T5.nudoss
and T6.nudoss = T1.nudoss
AND T7.NUDOSS = T8.NUDOSS
AND T9.NUDOSS = T10.NUDOSS
AND T11.NUDOSS = T12.NUDOSS
AND T3.IDWKLO = T7.CDCODE
AND T4.MOTIFA = T9.CDCODE
AND T5.IDESTA = T11.CDCODE
and T6.stempl = 'A'
and t7.cdstco = 'z04'
AND T8.CDLANG = 'U'
and t9.cdstco = 'DSJ'
AND T10.CDLANG= 'U'
and t11.cdstco= 'DRE'
AND T12.CDLANG= 'U'
and T4.DATDEB <= T3.DTEN00 and T4.DATFIN >= T3.DTEF00
and T3.DTEN00 <= T5.DTEN00 and T3.DTEN00 >= T5.DTEF00
and T6.dtef1s <= getdate() and T6.datxxx > getdate()
Also Please suggest me some links where i can get info about the indexes that has to be created on these types of queries where joins are involved on these many tables.
Also throw some light on how to analyse the execution plan for further enhancements.
Thanks in advance
Regrds
Arvind L
Trigger On System View
Mar 24, 2006Hi,
Is there a way to create a Trigger on the Systemview [sys].[dm_exec_connections] ?
The Statement below Fails.
CREATE TRIGGER [dbo].[TG_CLEAN_LOCK_ID] ON [sys].[dm_exec_connections]
AFTER DELETE
AS
I need a trigger to be fired when a Connection ([sys].[dm_exec_connections] .[connection_id]) drops.
Is it principally impossible to create a trigger on a system view or is it just a lack on permissions making my SQL fail?
Is there any other way for surveying wether a connection is alive or not, without polling?
Thanks in advance
Raimund
Is There A System Table Containing The Information If A Package Has Been Successful
Feb 2, 2004Hi all,
Im not really sure if this is a PHP or and SQL problem but here goes.
Im using MSSQL and have developed a webpage that enables users to run various PACKAGES manually, however I need to display if the package has been successfully run.
Is there a system table that logs package information or is there a PHP function that I can use.
Thanks
P.s I know there are some system tables with the information for jobs but I do not want to create a job for each package.
How Do I View Table Information Such As Constraints?
Sep 19, 2007I remember once using a command in Query Analyser that gave me all theinformation about a table. It showed details about the columns,constraints, keys, indexes etc.I can't remember how I did this? Can anyone help me?It could of been a stored proc that I called or a query on the systemtables.I do know that it was very simple to do and it was executed in QueryAnalyser.Can anyone help me?
View 2 Replies View RelatedDifferent Query Plans For View And View Definition Statement
Mar 9, 2006I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedTrigger On System Table...
Mar 30, 2005Hi,
I want to create a trigger on sysfiles. I had logged in as sa account but able to create it... I am getting the below error...
Server: Msg 229, Level 14, State 5, Procedure TAU_Sysfiles_WSCLog_Reminder, Line 65535
CREATE TRIGGER permission denied on object 'sysfiles', database 'WorldScope_Dev', owner 'dbo'.
Can any body please help me how to go ahead creating trigger in the system tables...
Thanks in Advance...
-Mohit.
Trigger On System Table
Jun 4, 2002Hi,
Can I create trigger on a system table in the Master database, what I need is to create trigger on sysxlogins table.
Thanks
IF Statement In View Definition
Apr 7, 1999We are trying to create a view that references lengths in both metres and feet.
What we want to do is to create a baselength column which either holds the value of the metres column or calculates the metric value of the feet column if there is no value in the metric column.
We can do all the maths for the calculations etc, it is just putting the IF statement into the view to test the values that we are struggling with.
Any help would be appreciated.
Thanks
View Definition From Schema
Mar 12, 2004I'm using the following to get table/column names:
select so.name, sc.name
from sysobjects so, syscolumns sc
where so.id = sc.id
and so.type = 'U'
order by so.name, sc.colid
What is the equivalent for views?
Thanks
Can You Use Cursors Within A View Definition?
May 13, 2008
I want to create a view on the table "workorder" that contains a serial id "woid". Another table, "wo_comment" contains multiple comments for each "woid".
I would like to create a view view_workorder with a column "allcomments" that is basically a concatenation of all the comments within the table "comment".
Table: workorder has columns: woid, startdate (plus many others)
Table: wo_comment has columns: woid, comment, sequence
Desired View: view_workorder has columns: woid, startdate, allcomments
I tried to create a view as follows but get errors concerning the "CURSOR" statement making me wonder if I can use declarations and CURSORS within a view?
create view [workorder_view] as (
Select
w.woid,
w.startdate,
cast( DECLARE @all_comments nvarchar(4000),
@curr_comment nvarchar(256)
DECLARE wo_comment_cur CURSOR
FOR
SELECT woc.Comments
FROM WO_Comment as woc
WHERE woc.woid = w.woid
OPEN wo_comment_cur
FETCH NEXT FROM wo_comment_cur INTO
@curr_comment
WHILE (@@FETCH_STATUS = 0)
BEGIN
Set @all_comments = @all_comments + @curr_comment
FETCH NEXT FROM wo_comment_cur INTO
@curr_comment
END
CLOSE wo_comment_cur;
DEALLOCATE wo_comment_cur;
as nvarchar(80000)) as COMMENTS
from workorder AS w
Thanks for any help you can provide!
Using WHERE Clause In Trigger Definition
Dec 3, 2007I've got a table that gets updated with live-data constantly throughout the day. I've got to run some statistical analysis on the data in this table based off of the value of one of the columns. The column can be 4 values, but I'm only looking to analyze data for rows containing one of two of the 4 total values. IETable |Status| more data....||1|......| |2|......||3|......||4|......| |1|......||4|......| I want to only analyze the data of rows with 1 and 4 as values. I don't care about 2 or 3... I was thinking of using a Trigger, but I'm not aware of a way to analyze the data that fires the Trigger.Ideally, I'd like to do something likeCREATE TRIGGER analyze ON table AFTER INSERT WHERE column = '1' OR column = '4' AS 'Trigger sql here'Is this possible?
View 13 Replies View RelatedSQL 2012 :: View Definition Of Objects?
Sep 8, 2014I want to create a login with some restriction like the following...
1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only,
2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login.
3.If we Deny the View definition option he can't able to see the Objects which are there under the DB.
4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....
SQL 2012 :: Grant View Definition
Nov 19, 2014I'd like to find out whether or not people grant VIEW DEFINITION to their developers in UAT and PROD environments. My view is that a developer shouldn't be able to touch a PROD environment at all (we also include UAT as PROD), and any issue in a production environment should be investigated by a DBA and escalated to the dev if necessary.
View 1 Replies View RelatedCOUNT_BIG() In Indexed View Definition.
Apr 1, 2008Hi,
When I am trying to create an indexed view using group and COUNT(*), it gave me the following error:
€œMsg 10136, Level 16, State 1, Line 2
Cannot create index on view "AdventureWorks.Sales.vOrders" because it uses the aggregate COUNT. Use COUNT_BIG instead.€?
When I refered the SQL Books Online, I found the following statement: €œIf GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING€?.
Though my question is basic, I am curious to know the difference between COUNT() and COUNT_BIG(). The only difference I knew is COUNT_BIG() returns bigint. If this is the only difference, why can€™t we use COUNT() in indexed view definition and why COUNT_BIG() is allowed?
Regards,
-Aazad.
Error1'System.Data.SqlClient.SqlConnection' Does Not Contain A Definition For 'Connect'
May 3, 2008<code>
public static DataTable GetCountries() { SqlConnection myConnection = new SqlConnection(ConfigurationManager.AppSettings["omegaloveConnectionString"]);
if (myConnection.Connect()) .............. problem
{ SqlCommand cmd = new SqlCommand("prcGetCountries", myConnection); cmd.CommandType = CommandType.StoredProcedure; DataTable countries = new DataTable(); countries.Columns.Add("CountryId", typeof(string)); countries.Columns.Add("Country", typeof(string)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { DataRow dr = countries.NewRow(); dr[0] = reader["CountryId"].ToString(); dr[1] = reader["Country"].ToString(); countries.Rows.Add(dr); } reader.Close(); return countries; } else return null; } }
</code>
SQL 2012 :: Get View Definition Using Modules With Linebreaks
May 6, 2014SELECT [DEFINITION]
FROM SYS.SQL_MODULES WHERE [OBJECT_ID] = OBJECT_ID(@OBJECTNAME)
I know I can use SP_HELPTEXT to get linebreaks, The reason I want to use this is I can exclude comments section from SQL_Modules, but cannot exclude the comments section from SP_HELPTEXT.
View Definition Includes No Output Columns ...
Jan 18, 2007hello all,
i am trying to create a view but i keep getting the error 'View definition includes no output columns or no items in the FROM clause.'
below is the select statement that's the basis of my view. the explanation i got from the F1 help of enterprise manager was ...
View definition includes no output columns or no items in the FROM clause.
A view definition must have at least one table or table-structured object in the FROM clause, and must have at least one column in the select list. The view definition is missing one or both. Modify the view definition accordingly.
query:
select
Case_CaseId,
Logged,
CAST(DATEDIFF(minute, Logged, Waiting)/60.0 AS NUMERIC(9, 2)) AS Waiting,
CAST(DATEDIFF(minute, Logged, Investigating) /60.0 AS NUMERIC(9, 2)) AS Investigating,
CAST(DATEDIFF(minute, Logged, Rejected) /60.0 AS NUMERIC(9, 2)) AS Rejected,
CAST(DATEDIFF(minute, Logged, Resolved) /60.0 AS NUMERIC(9, 2)) AS Resolved,
CAST(DATEDIFF(minute, Logged, Solved) /60.0 AS NUMERIC(9, 2)) AS Solved,
CAST(DATEDIFF(minute, Logged, Closed) /60.0 AS NUMERIC(9, 2)) AS Closed
from
(
SELECT
Case_CaseId,
MIN(CASE WHEN case_stage = 'Logged' THEN Case_CreatedDate END) AS Logged,
MIN(CASE WHEN case_stage = 'Waiting' THEN Case_CreatedDate END) AS Waiting,
MIN(CASE WHEN case_stage = 'Investigating' THEN Case_CreatedDate END) AS Investigating,
AS Rejected, MIN(CASE WHEN case_stage = 'Resolved' THEN Case_CreatedDate END) AS Resolved,
MIN(CASE WHEN case_stage = 'Solved' THEN Case_CreatedDate END) AS Solved,
MIN(CASE WHEN case_stage = 'Closed' THEN Case_CreatedDate END) AS Closed
FROM
CaseProgress
GROUP BY Case_CaseId
) as temp
order by Case_CaseId
View / Underlying Column Definition Metadata
Mar 18, 2008Have many views based on legacy tables that have different table and column names. Want to create a table that shows view table / column and underlying table column, e.g.
CREATE VIEW [dbo].[Branch]
AS
SELECT
divbra_id BranchID,
cmpny_id CompanyID,
divbra_cde BranchCode,
divbra_nme BranchName
FROM MyDatabase.dbo.divbranc
GO
is an existing view. I want to pull out the following metadata:
divbranc divbra_id Branch BranchID
divbranc cmpny_id Branch CompanyID
divbranc divbra_cde Branch BranchCode
divbranc divbra_nme Branch BranchName
Is there anyway to get this from SQL metadata without actually parsing the view SELECT statement in code?
SQL Security :: View Definition Permission On Target Database
May 15, 2015I am trying to do a schema compare and data compare via VS2012 and I am getting below error: The reverse engineering operation cannot continue because you do not have View Definition permission on the 'Target' database.
Whats interesting is I created a viewdefinition role and added the group(to which the user belongs) to the role. However I dont get the error if I make the group the dbowner. Is this a bug?
SQL Server 2008 :: Granting Explicit View Definition Permissions On Stored Procedure To DBO?
Mar 6, 2013The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
SQL Server 2014 :: Trigger On A View If Any Insert / Update Occurs On Base Table Level
Apr 21, 2015I 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.
System Information About Indexes
Jan 17, 2006Hi,Is het possible to find information about indexes in SQL Server 2000?Information like pad index, fill factor, index fields, SortinTempdb?Gr,Hennie
View 3 Replies View RelatedWhy Additional Information: System Error.?
Dec 18, 2003I have a DataGrid, it will return a table in SQL2000 server.
the problem is:
no data in the datagird, only a record "null" in all field.
I find a sample is add a "sqlDataAdapter1.Fill".
but if I add the code:
sqlDataAdapter1.Fill(dataSet21);
I got the error:
---------------
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error.
-------------
I have search this problem fix many many time, many people has this problem, looks like no result I can find.
Please help, thanks very very much.
Thanks.
System Information Tables For MS Access
Oct 24, 2005Hi
Sorry if i am posting in the wrong section but i can't find a forum for MS Access.
Does Access know of a equivelent to SQL Servers INFORMATION_SCHEMA tables?
I am writing a application that needs to extract information from a access database like, table names, relationships etc..
Please note that i need to do this via SQL.
Thanks Wayne
View Job Information
May 15, 2008What access I should give for a non-admin user to be able to view the sql-job information?
------------------------
I think, therefore I am - Rene Descartes
Trigger Information
Jul 26, 2001Hi,
I have a problem here in my SQL Server Database. I want to find which trigger is working backend affecting my table input. Since the original author of this trigger was somebody else, I am not able to find the trigger information
A simple line of syntax would be great.
Thanks in advance
sandeep
Trigger Information
Jul 26, 2000Is there a way to view the triggers on a table using query analyzer?
I have a developer that wants to see this information without having to right click every table in the database and viewing the triggers through enterprise manager.
Thanks,
Brad
Job Execution Information Not Written To System Tables
Jul 3, 2007SS 2005 64Bit SP2 Hello Chaps Intermittent problem with the SQL Agent job history not getting written to the history table. Background:Today we noticed the account SQL Agent runs under cropping up in sp_who2. A quick check of the activity monitor said nothing was running. We ran a trace and, based on the SQL being executed, had a word with one of the developers who confirmed they had manually executed one of the jobs. There was no record anywhere that the job had run. There has been an issue with this particular job, when executed by this user, not showing up in history before but, as mentioned, this had been intermittent and we thought that a restart of the service had sorted it. Stuff run to try to track the job:EXEC sp_help_jobactivity @job_name = 'MyJob' EXEC sp_help_jobhistory @job_name = 'MyJob' SELECT *FROM dbo.sysjobhistory WHERE job_id = 'MyJob GUID'The first returned a row with no details in the columns indicating activity (e.g. last_executed_step_date and other columns were null). sp_help_jobhistory had some historical records but nothing since mid last month. sysjobhistory correlated with sp_help_jobhistory as you would expect. Right clicking the job in SSMS and viewing history correlated with sp_help_jobhistory (i.e. some records but nothing since mid-June). We edited the SQL in the job step and got the developer to rerun the job and, typically, everything appeared as it should in all the above result sets.Obviously this is tricky to track down since it has been intermittent but does anyone recognise anything that I have described above? I have of course googled but there doesn't really seem to be anything about it. We have considered there may be a problem in MSDB and may try running CHECKDB to see if anything comes up but somehow I doubt it will. Ta!
View 7 Replies View RelatedWhich System Tables Hold SSIS Pkg Information
Apr 4, 2006Hello Everyone,
I need to know which system tables hold the pkg information when you import a pkg into SQL Server 2005.
Thanks
Shabnam
Need Information For Before Select Trigger
Jun 8, 2006hi guys!Currently i'm facing a bit odd situation. i cant modify my code so ihave to make changes in SQL Server.the problem is, i've modified one table by adding new column and now iwant only those row which has not null value in that column wheneverthe select statement gets fire. i mean in every select statement onlythose rows should be returned which has NOT NULL value in the newlyadded column.for this i've created one view and tried to run the application but thei'm facing some problem as my application is developed in .net 2.0,i've used lots of .net functionalities and now i cant make any changesin Code.another solution i think is triggers. if i can fire a trigger beforeany select statements, my problem can be solved instantly but i'mafraid i can not write andy "before select" trigger.if anyone has any idea or any other solution to my problem, please letme know.all suggestions are welcomed.thanks,Lucky
View 4 Replies View RelatedHow To Create A System Type Table/ Change User Table To System Table.
May 23, 2007Is there any Posibility to change a User Table to System Table.
How to create one system table.
I am in Big mess that One of the Table I am using is in System Type.
I cant Index the same. Is there any Mistake we can change a user table to system table.....
Trigger To Capure Delete Information ???
May 24, 2006Hi There
We have some mystery deletes happening on a table.
The server is very busy, so a profiler trace on this table would result in thousands of events to sift through.
So i would like to put a trigger on this table that on delete could capture information such as the sql statement or user that caused he delete.
Anyone got any ideas on the best way to do this? Or any way to do this ?
Thanx