List of users in Business Objects

Author: Adrián BO_Worker developed from Julien Bras' userlist utility.
Author notes: Excel 97-2003 VBA application. Displays some lists for users, groups, universes, reports... Only reading, not writing back. Please go to configuration sheet and fill Server, Username and Password in order to get it working. After that try the rest of sheets pressing the buttons to field the data below. Whenever you get orange cells that's a place you should fill. If you get Id and Name to fill you can write one or the other. In names you usually can use % character. Some queries can take a long time (10-15 minutes) depending on your server and number of objects involved.

I hope it's useful for someone and want to thank Julien Bras for previous programming. 

---

http://www.forumtopics.com/busobj/viewtopic.php?t=118177

BOXIR3_Lists97.zip
Description:		Download
Filename:	BOXIR3_Lists97.zip
Filesize:	105.64 KB
Downloaded:	6275 Time(s)

Database snapshot in SQL server 2008

-- CREATE A EMPTY DATABASE

USE [master];

GO

CREATE DATABASE DEEPTEST;

GO

--CREATE A DATABASE SNPSHOT-1

CREATE DATABASE SNPDEEPDB ON

(Name ='DEEPTEST',

FileName='D:\DB_SnapShot\DEEPTEST.SNP')

AS SNAPSHOT OF DEEPTEST;

GO

--CREATE A DATABASE SNPSHOT-2

CREATE DATABASE SNPDEEPDB2 ON

(Name ='DEEPTEST',

FileName='D:\DB_SnapShot\DEEPTEST2.SNP')

AS SNAPSHOT OF DEEPTEST;

GO

--NOW WE HAVE TWO SNPASHOTS AND LETS TRY TO RESTORE ORIGNAL ONE FROM SNPSHOT2

USE [master]

GO

RESTORE DATABASE DEEPTEST

FROM DATABASE_SNAPSHOT = 'SNPDEEPDB';

GO

--You get the error becuase we can only restore from a snapshot if that snapshot is the only existing snapshot of the database while we have two

--so lets try to drop one of snpshot and try again

--Msg 3137, Level 16, State 4, Line 1

--Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

--Msg 3013, Level 16, State 1, Line 1

--RESTORE DATABASE is terminating abnormally.

DROP DATABASE SNPDEEPDB2

--Command(s) completed successfully.

USE [master]

GO

RESTORE DATABASE DEEPTEST

FROM DATABASE_SNAPSHOT = 'SNPDEEPDB';

GO

--Command(s) completed successfully.

--Lets add a new datafile and filegroup to database now

USE [master]

GO

ALTER DATABASE [DEEPTEST] ADD FILEGROUP [SECONDARY]

GO

ALTER DATABASE [DEEPTEST]

ADD FILE

(

    NAME = DEEPTEST3,

    FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST3.NDF',

    SIZE = 5MB,

    MAXSIZE = 100MB,

    FILEGROWTH = 5MB

)

TO FILEGROUP SECONDARY

--Now we have database with multiple files

SELECT SDF.NAME AS [FILENAME],SDF.PHYSICAL_NAME AS 'PHYSICAL NAME',

SIZE/128 AS [SIZE_IN_MB], FG.NAME AS [FILE_GROUP_NAME]

FROM SYS.DATABASE_FILES SDF INNER JOIN SYS.FILEGROUPS FG ON SDF.DATA_SPACE_ID=FG.DATA_SPACE_ID

FILENAME	PHYSICAL NAME	SIZE_IN_MB	FILE_GROUP_NAME
DEEPTEST	D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST.mdf	1	PRIMARY
DEEPTEST2	D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST2.NDF	5	PRIMARY
DEEPTEST3	D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST3.NDF	5	SECONDARY

--Lets try to create a snpshot again and we get the error

--CREATE A DATABASE SNPSHOT-3

CREATE DATABASE SNPDEEPDB3 ON

(Name ='DEEPTEST3',

FileName='D:\DB_SnapShot\DEEPTEST3.SNP')

AS SNAPSHOT OF DEEPTEST;

GO

--Msg 5127, Level 16, State 1, Line 2

--All files must be specified for database snapshot creation. Missing the file "DEEPTEST".

--Msg 5127, Level 16, State 1, Line 1

--All files must be specified for database snapshot creation. Missing the file "DEEPTEST".

-- So We will have to We have to specify files for each of them

CREATE DATABASE DEEPTEST3 ON

(Name = 'DEEPTEST', FileName='D:\DB_SnapShot\DEEPTEST31.SNP'),

(Name = 'DEEPTEST2',Filename='D:\DB_SnapShot\DEEPTEST32.SNP'),

(Name = 'DEEPTEST3',Filename='D:\DB_SnapShot\DEEPTEST33.SNP')

AS SNAPSHOT OF DEEPTEST

--Command(s) completed successfully.

Lets take a datafile offline and  run a snpshot now

ALTER DATABASE DEEPTEST MODIFY FILE (NAME = 'DEEPTEST3', OFFLINE)

NAME	STATE
DEEPTEST	ONLINE
DEEPTEST_log	ONLINE
DEEPTEST2	ONLINE
DEEPTEST3	OFFLINE

CREATE DATABASE DEEPTEST4 ON

(Name = 'DEEPTEST', FileName='D:\DB_SnapShot\DEEPTEST41.SNP'),

(Name = 'DEEPTEST2',Filename='D:\DB_SnapShot\DEEPTEST42.SNP'),

(Name = 'DEEPTEST3',Filename='D:\DB_SnapShot\DEEPTEST43.SNP')

AS SNAPSHOT OF DEEPTEST

--Msg 5114, Level 16, State 1, Line 1

--Log files, offline files, restoring files, and defunct files for database snapshots should not be specified. "DEEPTEST3" is not an --eligible file for a database snapshot.

--here we will have to exclude the offlile datafile while taking snpshot.

CREATE DATABASE DEEPTEST4 ON

(Name = 'DEEPTEST', FileName='D:\DB_SnapShot\DEEPTEST41.SNP'),

(Name = 'DEEPTEST2',Filename='D:\DB_SnapShot\DEEPTEST42.SNP')

--(Name = 'DEEPTEST3',Filename='D:\DB_SnapShot\DEEPTEST43.SNP')

AS SNAPSHOT OF DEEPTEST

--Command(s) completed successfully.

Cross DB Ownership Chaining in SQL Server

From Microsoft MSDN -

Use the Cross DB Ownership Chaining option to configure cross-database ownership chaining for the instance of SQL Server. This security enhancement was added in SQL Server 2000 SP3 to allow you to manage cross-database security.

This server option allows you to control cross-database ownership chaining at the database level or to allow cross-database ownership chaining for all databases:

• When Cross DB Ownership Chaining is off (0) for the instance, cross-database ownership chaining is off. You can turn on cross-database ownership chaining for individual databases using sp_dboption.
  By default all user databases have cross-database ownership chaining turned off. Cross-database ownership chaining is on for the master, tempdb, and msdb system databases. You cannot change cross-database ownership chaining for the master, msdb, model, and tempdb system databases.
• When Cross DB Ownership Chaining is on (1) for the instance, cross-database ownership chaining is on for all databases. This is equivalent to pre-SP3 functionality.
  Setting this option to 1 is not recommended unless all of the databases hosted by the instance of SQL Server must participate in cross-database ownership chaining and you are aware of the security implications of this setting. For more information, see Using Ownership Chains.

Controlling Cross-Database Ownership Chaining

Before turning cross-database ownership chaining on or off, consider the following:

• You must be a member of the sysadmin role to turn cross-database ownership chaining on or off.
  
• Before turning off cross-database ownership chaining on a production server, fully test all applications, including third-party applications, to ensure that the changes do not affect application functionality.
  
• You can change the Cross DB Ownership Chaining option while the server is running if you specify RECONFIGURE with sp_configure.
  
• If you have databases that require cross-database ownership chaining, the recommended practice is to set the Cross DB Ownership Chaining option to 0 and to turn on cross-database ownership for any database that must participate in cross-database ownership chaining.
  For example, if Cross DB Ownership Chaining is currently set to 1 (allowed for all databases), you can run the following statements to turn off cross-database ownership chaining for all user databases, and then turn it on for the Northwind and Pubs database:

  USE master
  EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
  EXEC sp_dboption 'Northwind', 'db chaining', 'ON'
  EXEC sp_dboption 'Pubs', 'db chaining', 'ON'

BCP Utility in SQL Server

The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

to know more please refer to this link - http://msdn.microsoft.com/en-us/library/ms162802.aspx

Process stuck in posting - Posting (stuck).

Know it - 

DISTSTATUS 0 = None
DISTSTATUS 1 = Scheduled (N/A)
DISTSTATUS 2 = Processing
DISTSTATUS 3 = Generated
DISTSTATUS 4 = Unable to Post
DISTSTATUS 5 = Posted
DISTSTATUS 6 = Delete
DISTSTATUS 7 = Posting (stuck).

Find it -


select * from PSPRCSRQST where PRCSINSTANCE = '2152947' AND DISTSTATUS = 7
select * from PSPRCSQUE where PRCSINSTANCE = '2152947' AND DISTSTATUS = 7

Fix it-


UPDATE PSPRCSRQST SET DISTSTATUS = 5 WHERE PRCSINSTANCE = '2152947' AND DISTSTATUS = 7
UPDATE PSPRCSQUE SET DISTSTATUS = 5 WHERE PRCSINSTANCE = '2152947' AND DISTSTATUS = 7

Lookout for a routing name by connector URL

use PSFINTST
select * from PSRTNGDFNCONPRP where PROPVALUE LIKE '%cus%'

Building Peopletools Patch Project

Login to AppDesigner -> Open the Project -> Build -> Project.

Here it will show items needed to build in the (Build Scope section).
i. If views only are displayed here in this "Build Scope", user needs to select only "Create Views" and "Execute and build script" -> then click on BUILD button.
ii. If views and records are displayed, in the "Build Scope" section, you need to select "Create Tables", "Create Views" and "Alter Tables" and "Execute and build script" -> Click on Settings button  (and select the below settings in each and every tab)...

Create (Tab)
------------------
Select the below options ...
Skp table if it already exists
Skip view if it already exists
Recreate index only if modified.

Alter (tab)
--------------
Select the below options ...
skip record if data present
skip record if field too short
Select all checkboxes under "Alter Any "
Select "Alter even if no changes" check box also.

Logging (tab)
------------------
Select the below options ...
Fatal errors and Warnings
Log to output window
Log to file and give the correct path and file name in the log file name text box.

Scripts (tab)
----------------
Select the below options ...
Write alter comments to script
Output to single file (if you wish)
Prompt for each overwrite
give the required path and file name in "All Output FileName".