[T-SQL] Urgent Help.. find which columns are updated. |
- Urgent Help.. find which columns are updated.
- Query for Emp Time Logging
- How do you query users database roles?
- Report keeps on failing
| Urgent Help.. find which columns are updated. Posted: 14 Jun 2013 04:17 PM PDT In want to find which columns are updated in a row of a table, and what is their new value and previous value.i know a trigger can work here, with use of columns_updated(). but i want an exact T-SQL script, and any other alternatives for this process.Thanks a Ton in advance. :-) |
| Posted: 14 Jun 2013 02:04 PM PDT This is a question asked in a test.There is a EmpWorkTime table having the columns and data as shown.ID Name LoggedTime In/Out---- -------- ---------------- ---------001 Alen 9:00 I002 Alice 9:43 I001 Alen 9:49 O003 Bitchel 9:54 I002 Alice 12:03 O003 Bitchel 12:04 OThe required result is like this.ID Name Duration001 Alen 0:54 002 Alice 2: 20 003 Bitchel 2:10 |
| How do you query users database roles? Posted: 14 Jun 2013 04:19 AM PDT I created a query, that loops through the databases, but [database name].sys.database_principals seems to only have a record for db_owner if there are no other custom roles for that user.How do you get a true listing of roles? This user has three custom roles.. showing up in dbzlb.sys.database_principals, but when you go and right click on the user in SSMS and look under Database Role Membership it shows those three PLUS db_owner, db_securityadmin and db_ddladmin.I even did the following.. but only got the three custom roles.select * from zemeter_lb.sys.database_principals -- picked a userselect * from zemeter_lb.sys.database_role_members where member_principal_id like '625' -- there were three recordsselect * from zemeter_lb.sys.database_principals where principal_id in ('69','16400','16418')Now the code in the stored proc that I created for my audit==============================================DECLARE c1 CURSOR for SELECT name FROM master.sys.databasesOPEN c1FETCH c1 INTO @nameWHILE @@FETCH_STATUS >= 0BEGINSELECT @sql = 'INSERT INTO #tmpTableSELECT N'''+ @name + ''', a.name, c.name, a.[SID]FROM [' + @name + '].sys.database_principals a JOIN [' + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_idJOIN [' + @name + '].sys.database_principals c ON c.principal_id = b.role_principal_idWHERE a.name != ''dbo'''EXECUTE (@sql)FETCH c1 INTO @nameENDCLOSE c1DEALLOCATE c1 |
| Posted: 14 Jun 2013 01:27 AM PDT Hi,I am new in this forum and this is my first topic. I have created an SSRS report that is called from a stored procedure. The SP is in server A, it query 2 databases. One in server A and the other in server B. A and B are linked servers. The report is running fine both in SSRS and report server. But after every few minutes I get a log on error when I am trying to run the report from SSRS or from the report server. If I manually execute the stored procedure and then try to run the report, it works fine then. Can anyone please help me with this issue? Thanks a lot in advance. |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment