Posted on : 05-03-2010 | By : Harry Zheng | In : Microsoft SQL Server
check this link out.
Featured Posts
Generate BCP scripts for all tables check this link out.
Microsoft® SQL Server® 2008 Express Edition Service... Download http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=01af61e6-2f63-4291-bcad-fd500f6027ff
ASP.NET MVC 2 RC is available ASP.NET MVC 2 is a framework for developing highly testable and maintainable Web applications by leveraging the Model-View-Controller (MVC) pattern. The framework encourages developers to maintain a clear...
HP + 3Com against Cisco? I haven't used too much of 3Com products recently, however 3Com is certainly a well known name in the early days. With $2.7 billion on the table, HP is geared up to compete against network giant Cisco....
Can't check for update error code 80070426 Getting a alert on the bottom right cornor of the window saying "Can't check for updates". Start -> Control Panel ->Nothing shows up It is still possible to access Control Panel from Windows Explorer...
Configure multiple schedules on Cruise Control.Net Note the nesting syntax: triggers multitrigger triggers ... See details here.
The page you are requesting cannot be served because... Tried to open a WCF service site and got: HTTP Error 404.3 - Not FoundThe page you are requesting cannot be served because of the extension configuration. If the page is a script, add a handler. If...
IIS 7 HTTP Error 500.19 - Internal Server Error. Error... HTTP Error 500.19 - Internal Server Error The requested page cannot be accessed because the related configuration data for the page is invalid. Module: IIS Web Core Notification: BeginRequest Handler:...
SQL Server 2008 new programming features Declare and initialize variables Declare @SequenceNumber int = 0 Compound Assignment Operators +=, -=, *=, /=, %= Declare @SequenceNumber int = 0 set @SequenceNumber += 1 print @sequenceNumber Table...
SQL Server CPU utilization stays high but there is... Recently encountered a problem with one of the Windows 2008 server box. This server is a combination of SQL Server 2008 database server and IIS 7 Application server. This is on the testing env, so this...
Msg 701, Level 17, State 123, Line 1 There is insufficient... Msg 701, Level 17, State 123, Line 1 There is insufficient system memory in resource pool 'default' to run this query. This is caused by inappropriate configuration in SQL Server Resource Governor. Ref:http://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=SQL%20Server&ProdVer=10.0&EvtID=701&EvtSrc=MSSQLServer&LCID=1033...
System.Web.HttpException: The remote host closed the... Had an issue where some user can't get on the application site, and this is the error detail logged by System Center Operations Manager: System.Web.HttpException: The remote host closed the connection....
Msg 9002 Transaction Log full Haven't seen this in a while: Msg 9002, Level 17, State 4, Line 1 The transaction log for database 'SFSNEXT' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc...
Microsoft Security Essentials - Free Antivirus Software It is a bit late, but Microsoft finally came up with a solution. We still need to wait a bit to see how good it is. Microsoft Security Essentials provides real-time protection for your home PC that guards...
Unique Constraint v.s. Unique Index As we all know creating a unique constraint will automatically create a unique index on the base table. So it looks like unique constraint has more mean than unique index. Let's play with a test table...
@@TRANCOUNT and XACT_STATE() In pre SQL Server 2005 environmet, we always check @@TRANCOUNT to determine whether we need to commit or rollback transactions. Each Begin Tran statement increases @@TRANCOUNT by 1, while each Commit Tran...
SQL Server Set Transaction Isolation Level and locking... Prepare data use AdventureWorks GO select DB_ID() as DBID DBID ------ 10 Drop Table TestTable; Create Table TestTable (ID int primary Key); Declare @Count int = 1 Delete From TestTable; While...
SQL Server Read Committed Snapshot V.S. Snapshot Isolcation... Read Committed Snapshot Database option: Alter Database DBName Set READ_COMMITTED_SNAPSHOT ON Session Setup: Use the default Read-Committed isolation level or SET TRANSACTION ISOLATION LEVEL Read Committed Version...
SQL Server Read Committed with row versioning Read Committed By default, SQL server transaction isolation level is set at Read Committed. Any insert, update or delete statement places an exclusive lock on the row to prevenet other statements from...
Manage local user groups in Windows Vista Home Edition As you may have noticed, Windows Vista Home Basic/Premium Edition does not has an interface to manage local user groups even though the groups do exist in the system. Here is a list of commands to manage...
BizTalk project creation failed Creating a BizTalk 2009 project with Empty BizTalk Server Project template failed with a message "Creating project '{ProjectName}' ... project creation failed." There were no other messages pointing to...
Check if a table has identity column before inserting List all tables with identity column in the database: Select Table_schema, Table_Name From INFORMATION_SCHEMA.TABLES Where OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 Check individual...
Group By Grouping SETS An interesting addition to T-SQL in SQL Server 2008, Grouping Sets lets you union multiple grouping result sets together. Select FundingYear, ApplicationStatusID, COUNT(1) as TotalApplications From...
SQL Server Madison delivers data warehouse scalability... Key features: Data warehouse scalability into the petabyte range Low cost of ownership through industry standard hardware Appliance model simplifies deployment and maintenance Integrates with...
How to attach SQL Server 2008 database from an inconsistant... I had to go through this today, and thought it may help others in the future. If you have an inconsistent mdf file only, you can't attach the database using the without logs option. Obviously it is not supported....
Create SSIS package deployment utility Since this option is not very obvious, I have included here for future reference. In Business Intelligence Development Studio, open the solution that contains the Integration Services project for which...
CREATE XML SCHEMA COLLECTION and get Msg 9402, Level... CREATE XML SCHEMA COLLECTION EIRequestSchema AS N'<?xml version="1.0" encoding="utf-8" ?> <xs:schema xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:b="http://schemas.microsoft.com/BizTalk/2003"...
SQL Server Dynamic Management Views and Functions OS related: select * from sys.dm_os_sys_info select * from sys.dm_os_hosts select * from sys.dm_os_nodes select * from sys.dm_os_tasks select * from sys.dm_os_waiting_tasks select * from...
Slow queries or updates? That could be caused by a lot of reasons including: Slow Network Slow Server (Insufficient CPU, memory or IO) Lack of Statistics Missing indexes Lack of data partitioning Consider capturing...
Trouble shoot SQL Server connectivity issue Installed a SQL Server 2008 Cluster and tried to test connectivity. When SQL Cluser is running on Node1, connection from Node1 to SQL Cluster is fine. However connections from Node2 and other computers...
SQLNexus SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount...
PSSDIAG http://support.microsoft.com/kb/830232 PSSDIAG is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. PSSDIAG...
SQLdiag http://msdn.microsoft.com/en-us/library/ms162833.aspx SQLdiag can collect the following types of diagnostic information: Windows performance logs Windows event logs SQL Server Profiler traces ...
SQL Server Pages and Extents SQL Server's Page size is 8KB by default, of which 96 byte is used by page header. There are 128 pages per MB. SQL Server Page Type: Data Index Text/Image Global Allocation Map/Shared Global...
SQL Server 2008 BOL update May 2009 http://www.microsoft.com/downloads/details.aspx?FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97&displaylang=en
Msg 8114 Error converting data type varchar to numeric SELECT sum(convert(numeric, ltrim(rtrim(value)))) FROM [dbo].[tmp_conversion_AssessmentResult] Where Assessresult_ty_id in (12112009) and value is not null Msg 8114, Level 16, State 5, Line 2 Error...
The Bar Camp session was great Check out their web site: http://www.barcampedmonton.com/ Or join their Facebook group: http://www.facebook.com/group.php?gid=45468727190
Operand data type sql_variant is invalid for sum operator create table T1(t1id int, t1value sql_variant) insert into T1 values (1, 1) insert into T1 values (2, 2) select SUM(t1value) from T1 Msg 8117, Level 16, State 1, Line 1 Operand data type sql_variant...
TSD01234 deploying SQL Server 2008 server project All.fbp6 : Action error "Deploy Project SFSNEXT.SERVER": ERROR TSD01234 in SFSNEXT.SERVER.dbschema(0,0) : Failed to import target model Master. Detailed message A fatal error occurred while the model...
SSIS: failure inserting into the read-only column SSIS validation returns this error message: The ApplicationDisplay is an integer identity column. I thought the failure was due to the identity_insert not set to on. So I added a step before this...
SET IDENTITY_INSERT SET IDENTITY_INSERT dbo.UserTable ON
Hyper-V snapshot disk keeps expanding I have a Hpyer-V server installed on a host computer. Before I make any server config change, I would create a snapshot so that I can always rollback to the point in time if anything goes wrong. I have...
How to view Hyper-V events Start -> Run -> compmgmt.msc On the Computer Management window, navigate to: Computer Management System Tools Event Viewer Custom Views ...
Backup Hyper-V VHD disk with diskshadow Found an interesting article: http://msmvps.com/blogs/erikr/archive/2008/12/27/hyper-v-or-vhd-backup.aspx
SQLCAT article SQL Server 2008 http://technet.microsoft.com/en-us/library/dd334464.aspx
SQL Server 2008 Service Pack 1 - CTP released April... http://www.microsoft.com/downloads/details.aspx?FamilyID=6f26fc45-f0ca-49cf-a6ee-840c7e8bb8af&displaylang=en
Invalid Object Name When Deploying SQL Server 2008... [ Deploy Project MyProject] Configuration : Release DeployToDatabase : True TargetDatabase : MyDB TargetConnectionString : Data Source=localhost;Pooling=False;Integrated Security=True RunCodeAnalysis...
ASP.NET MVC 1.0 is out Download: http://www.microsoft.com/downloads/details.aspx?FamilyID=53289097-73ce-43bf-b6a6-35e00103cb4b&displaylang=en
Add AD user to Local Administrators Group #---------------------------------------------------------------------------------------------------------------------------------------------- # Purpose: Add or remove an AD user to/from the local...
Create SQL Server Partition Function, Scheme, Table... Here is a script to create test Partition Function, Partition Scheme and Partition Table: USE [master] GO IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase1') Begin Alter database...
SQL Server Note Some sql server note/scripts for reference... set nocount off Print 'Check Static settings' Print '@@version' select @@version -- sp_configure 'show advanced options', 1 Print 'exec sp_configure' exec...
Set Single_user with Rollback Immediate restore filelistonly from disk = 'C:BackupDatabaseUserDB1-200903-160735.bak' restore database UserDB1 from disk = 'C:BackupDatabaseUserDB1-200903-160735.bak' with replace Msg 3101, Level 16,...
IncrementalGet v.s. IncrementalBuild <!-- Set this property to true to do an incremental get - this will override the CleanCompilationOutputOnly, SkipInitializeWorkspace, and ForceGet properties. --> <IncrementalGet Condition="...
SQL Reserved Words Checker When you create data model or create tables, sometimes you wonder if a word is reserved. Here is a good utility to help determine: http://www.petefreitag.com/tools/sql_reserved_words_checker/
Code 57A on Office Update Open local temp folder under C:Users[yourloginname]AppDataLocalTemp folder and inspect the update log file. Here is the message: Error 1402.Setup cannot open the registry key UNKNOWNComponents1FA18F7974E099CD0AF18C3B9B1A1EE80002109030000000000000000F01FEC. ...
Cast as datetime, datetime2 and precision This is run on SQL Server 2008: select CAST('2009-03-16 12:24:50.169' as datetime) 2009-03-16 12:24:50.170 -- why do I get an exttra microsecond select CAST('2009-03-16 12:24:50.169' as datetime2) 2009-03-16...
Warning TSD04151 building VSTS Database Project Warning message on build: Warning TSD04151: Procedure: [dbo].[uspCascadeDelete] has an unresolved reference to object [information_schema].[REFERENTIAL_CONSTRAINTS]. Warning TSD04151: Procedure:...
Unable to connect to CruiseControl.NET server. Please... There was an exception trying to carry out your request. Details of the Exception are: Exception Message No connection could be made because the target machine actively refused it ::1:21234 Exception...
::1 localhost entry in hosts file Local service call can't find localhost due to this entry. Not sure why this is happening all of a sudden. IPV6 was enabled for a while and didn't have any issue. To work around, you need to browse...
The trust relationship between this workstation and... After rolling back Hyper-V server to a previous snapshot, I got the following error message when trying to logon to the primary doamin: "The trust relationship between this workstation and the...
Hyper-v virtual server failed to start after resizing... Getting error messages like: "failed to open attachment avhd, one or more arguments are invalid." Apparently it is not recommended to resize a virtual drive if there is snapshots on the virtual machine,...
Team Foundation Error: Windows live messenger is not... The message seems weird that TFS has dependancy to Windows Live Messenger. The fix is to change the value of "HKCUSoftwareIM ProvidersMSN MessengerUpAndRunning" to 1. This may be caused by an unofficial...
MSB3021 when copying files to drop folder Extract from build log: Copying file from "....ReferencedAssembliesNHibernate.dll" to "C:BuildCIDev_20090216.1NHibernate.dll". Copying file from "....ReferencedAssembliesRhino.Mocks.xml" to "C:BuildCIDev_20090216.1Rhino.Mocks.xml". Copying...
Agile Manifesto Customer satisfaction by rapid, continuous delivery of useful software Working software is delivered frequently (weeks rather than months) Working software is the principal measure of progress...
How to assign SQL Server 2008 SysAdmin Role after installation I had a few occations when I installed an SQL Server instance but forgot to add myself to the SysAdmin server role, so I can't get into the instance after the installation. In SQL Server 2005 world,...
TSQL command ParseName Today I read a script that has a command named ParseName, which I have never used before. Checked BOL and found it is a standard T-SQL command. It is useful when you want to strip say 'SchemaName'...
Microsoft released SQL Server 2005 Service Pack 3 Microsoft released SQL Server 2005 Service Pack 3 on Dec 15 2008: http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en It is quite a big...
Configuring Kerberos Authentication for SQL Server Have had a hard time before, so I will keep this for reference: http://www.sqlservercentral.com/articles/Security/65169/
MySQL 5.1 GA released MySQL 5.1 Generally Available release is ready for download at: http://dev.mysql.com/downloads/ Also available for download: MySQL Cluster 6.3, MySQL Workbench 5.0.
Conflicts between VMWare Server and SQL Server 2008... I have both SQL Server 2005 and SQL server 2008 installed on my desktop. SQL Server 2005 is installed as the default instance, and SQL Server 2008 as a named instance. As the project is moving to SQL...
Visual Studio Team System 2008 Database Edition GDR... The download page: http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en And the Data Dude blog: http://blogs.msdn.com/gertd/archive/2008/11/25/visual-studio-team-system-2008-database-edition-gdr-rtm.aspx
SQL Server Preproduction Tasks http://www.sqlservercentral.com/articles/Administration/64732/
Recursive delete data from parent, children and grand... This code should work on SQL Server 2005, 2008: CREATE Procedure uspCascadeDelete @Schema varchar(128), @TableName varchar(128), @WhereClause varchar(MAX), @RowsAffected int...
Mark spt_values as system object If I deploy some user tables to Master database and later one drop all these tables, I sometimes drop spt_values from master database. This cause some function not working from SSMS. I have to recreate...
Is cloud computing a reality Got a news letter and I was checking Azure site. There is already some services offered. Not sure if these are really ready and how many have already subscribed. But I can see the trend is coming this...
Default trace on SQL Server MSDN article regarding default trace. One more place to check any suspicious activities. http://msdn.microsoft.com/en-us/library/ms175513.aspx Default trace provides troubleshooting assistance...
VSTS 2008 Database Edition GDR Release Candidate 1... I have been checking for the release of this version and finally it is out: Microsoft® Visual Studio Team System 2008 Database Edition GDR Release Candidate 1 http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en
Microsoft VSTS 2008 Database Edition GDR September... The official download site: http://www.microsoft.com/DownLoads/details.aspx?familyid=BB3AD767-5F69-4DB9-B1C9-8F55759846ED&displaylang=en#filelist doesn't seem to contain an install file. However...
URLScan causing page not displaying I had some hard time setting up the CruiseControl server to automate the daily build of my database project. It turns out that the problem is not at all CruiseControl related, but rather caused by...
MSB4018 when building Database Project on VSTS SP1... When building a VSTSDB project, I received this error message: C:BuildSolution1SrcApp1.DatabaseDBProj1>C:WINDOWSMicrosoft.NETFrameworkv3.5MSBuild.exe /t:Build DBProj1.dbproj /p:Platform="Debug" Microsoft...
Error when enabling code analysis for db project Visual Studio 2008 SP1 + DB Pro GDR August 2008 C:WINDOWSMicrosoft.NETFrameworkv3.5Microsoft.Common.targets(1850,9)Error MSB4057: The target "CoreCompile" does not exist in the project. This seems...
List of objects in each file group select Sch.Name SchemaName, Obj.Name ObjectName, Ind.Name IndexName, FG.Name FileGroup from sys.Schemas Sch, sys.FileGroups FG, sys.All_Objects Obj, sys.Indexes Ind where Sch.Schema_ID = Obj.Schema_ID and FG.data_space_id...
NewID, NewSequentialID, CombGUID comparison IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestGUID]') AND type in (N'U')) DROP TABLE [dbo].[TestGUID] GO create table TestGUID( TestGUIDID int identity(1,1), InsertDateTime datetime, GUID uniqueidentifier, CombGUID uniqueidentifier, NewSequentialID...
GUID.Comb as clustered PK The alternative way to generate sequential guid instead of using NewSequentialID of SQL 2005 and later has been documented by Jimmy Nilsson: http://www.informit.com/articles/printerfriendly.aspx?p=25862 This...
Order by GUID in SQL Server There is an interesting test on this blog regarding the sorting of GUID columns. http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx This is the modified...
NewSequentialID generation Found an in-depth article regarding newsqeuentialID and how to generate it with client code. http://jorriss.net/cs/blogs/jorrissnet/archive/2008/04/24/unraveling-the-mysteries-of-newsequentialid.aspx
Create Audit Trigger for each table in a database declare @stmt nvarchar(max), @TableSchema nvarchar(50), @Tablename varchar(max) set @TableSchema = 'dbo' declare table_cur insensitive cursor for select table_name from information_schema.tables where...
Create Audit table for each existing table in the database While it is still debatable whether it is a good idea of creating such an audit mechanism, I need to create an audit table for each existing table in our database. Here I came up with a quick and dirty...
Visual Studio Team System 2008 Database Edition GDR... Download it here: http://download.microsoft.com/download/0/a/e/0ae1153a-8798-474a-93e6-d19299f37c8b/setup.exe Installation requires: 1. SQL Express 2005 or SQL Server 2005 2. ...
SQL Server 2008 Data Type Test IF EXISTS (select 1 from information_schema.columns where table_schema = 'dbo' and table_name = 'DataTypeTest') drop table DataTypeTest; create table DataTypeTest ( -- Exact Numberics T1Bit...
Script to get SQL Server instance specific default... declare @rc int, @DataDir nvarchar(4000), @LogDir nvarchar(4000), @BackupDir nvarchar(4000) exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SoftwareMicrosoftMSSQLServerMSSQLServer',N'DefaultData',...
MSBuild TSD01234: '', hexadecimal value 0x07, is an... Error Message: ------ Deploy started: Project: SFSSHR, Configuration: Debug Any CPU ------ Proj1.dbschema(0,0)Error TSD01234: '', hexadecimal value 0x07, is an invalid character. Line 2265, position...
SQL Server 2008 depends on Visual Studio 2008 SP1 If you have VS 2008 installed on your computer and you try to install SQL Server 2008, you will get an error message saying "an earlier version of Visual Studio is installed". Even if you install Visual...
SQL Server 2008 Released August 6 2008 Check the web site to find out more detail: http://www.microsoft.com/sqlserver/2008/en/us/default.aspx
SQL Server 2008 CDC The Change Data Capture feature seems to capture the ddl applied to the base table on CDC.ddl_history. However the _CT table doesn't get updated automatically with the new schema definition. Use Test goEXEC...
SQL Server Login User Mapping error Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) Additional information: ->An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) -->Cannot...
Store User Name in ContextInfo This is useful when the web service is running on a service account which authenticates to SQL Server, and users are not authenticated with SQL Server. In order to track user's action, we need to...
Microsoft SQL Server 2008 and data warehouse http://search.techrepublic.com.com/search/data+warehouse+and+microsoft+sql+server+2008.html
How to delete an unwanted workspace If you want to lock a file that has been checked out by a specific user who left the project, you can unlock the specific file or delete the unwanted workspace. Navigate to the folder that contains...
Table Variable v.s. Temp Table Table variables have the following advantages over temporary tables: Table variables have a well defined scope at the end of which they are automatically cleared. Table variables result in fewer...
Microsoft Certified Master: SQL Server 2008 Microsoft has a new certificate for SQL pro. http://www.microsoft.com/learning/mcp/master/products/default.mspx#EZC
SQL Server 2008 will be available in August http://blogs.technet.com/dataplatforminsider/archive/2008/07/09/sql-server-2008-available-on-august-price-list.aspx
VSTS Database Edition GDR June CTP installation issue Tried to install VSTS Database Edition GDR June CTP as mentioned on Data Dude's blog. Installed Microsoft Visual Studio 2008 Service pack 1 Beta This took quite some time to finish. On one...
SQL Server 2008 RC0 installation issue Encounted this error when setting up SQL Server 2008 Release Candidate 0: The following error has occurred: This access control list is not in canonical form and therefore cannot be modified. The...
Audit data changes on SQL Server 2005 A few options are available: Create one audit table for each base table. Whole row of changed data is saved on the audit table as one row. Create one audit table for the database. Changed data per...
Query plan reuse and dynamic sql It has been well written that sp_executesql can help keep the query plan in cache while exec() dynamic sql will probably not. So I set up two SPs, one with exec() and the other sp_executesql, to perform...
Top SQL Server OLTP Performance Issues http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx
Dynamic SQL and it's performance impact Found this great article on the web which contains several links to other articles by Erland Sommarskog. http://www.sommarskog.se/dynamic_sql.html
Received ASN2207E when setting up new subscription... Problem: ASN2207E The replication queue map "BIDIPAT_BIDI_TO_BIDIDEV_BIDI" is not valid for setting up a connection from the source Q Apply and Q Capture schema "BIDI" on the Q Capture server...
SQL Server 2005 undocumented extended stored procedure We all like the undocumented stuff, so here are a few: EXEC xp_fileexist < filename> [, OUTPUT] exec xp_fileexist 'c:boot.ini' File Exists File is a Directory Parent Directory Exists -----------...
SQL Server catalog views Database and Files Catalog Views: sys.backup_devices sys.databases sys.database_recovery_status sys.database_files sys.database_mirroring sys.master_files Object catalog Views: sys.allocation_units sys.assembly_modules sys.check_constraints sys.columns sys.computed_columns sys.default_constraints sys.events sys.event_notifications sys.extended_procedures sys.foreign_key_columns sys.foreign_keys sys.function_order_columns sys.identity_columns sys.index_columns sys.indexes sys.key_constraints sys.numbered_procedure_parameters sys.numbered_procedures sys.objects sys.parameters sys.partitions sys.procedures sys.service_queues sys.spatial_index_tessellations sys.spatial_indexes sys.sql_dependencies sys.sql_expression_dependencies sys.sql_modules sys.stats sys.stats_columns sys.synonyms sys.table_types sys.tables sys.trigger_event_types sys.trigger_events sys.triggers sys.views Security...
SQL Server Performance Dashboard Reports The Performance Dashboard reports: CPU utilization Current user request waiting for resource User Activity: User Requests /User Sessions Count Elapsed Time (CPU Time, Wait Time) Cache...
Rollup & Cube and deprecated Compute/Compute By Rollup: SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL' ELSE ISNULL(Item, 'UNKNOWN') END AS Item, CASE WHEN (GROUPING(Color) = 1) THEN 'ALL' ELSE ISNULL(Color, 'UNKNOWN') END AS Color, SUM(Quantity)...
Multiple Active Result Sets (MARS) SQL Server 2005 introduces a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per...
Grant access to IIS metabase C:WINDOWSMicrosoft.NETFrameworkv2.0.50727>aspnet_regiis -ga ASPNET Start granting ASPNET access to the IIS metabase and other directories used by ASP.NET. Finished granting ASPNET access to the IIS...
SQL Server 2008 Management Data Warehouse (MDW) The Management Data Warehouse is a single database which holds various performance related statistics. SQL Server 2008 provides three different system data collection definitions: Disk Usage Query...
Set transaction isolation level Read Uncommitted Read Committed If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current...
Lock Type and Mode The lock type: Shared Lock Update Lock Exclusive Lock Intent Locks Schema Locks Bulk Update Locks RID = Lock on a single row in a table identified by a row identifier (RID). KEY...
Join and join hint Join: Inner Join Outer join left outer join: The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause Right outer join: All rows...
rah and db2_all syntax Command: rah Runs the command on all machines. db2_all Runs the command on all database partition servers that you specify. Syntax: | run the commands in sequence using...
db2 move tablespace to a different location db2look to create tablespace creation script db2 "restore db test use tsm replace existing redirect without rolling forward " db2 "set tablespace containers for 3 using (FILE '/.../.../NODE0001/TEST/test_data'...
db2move db2move sourcedb export -sn myschema db2move targetdb import -io REPLACE_CREATE db2move targetdb load -lo REPLACE db2move sourcedb COPY -sn schema1 -co TARGET_DB targetdb USER myuser1 USING...
SQL Server Indexed Views Non-indexed view: standard view is materialized at run time. Indexed view: A view with a unique clustered index. Indexed view can be used in a query execution: The query can reference the indexed...
SQL Server 2008 Improvements SSDS: Merge Statement MERGE Production.ProductInventory AS pi USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail sod JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID...
Find syntax A simple example: find . -name '*.txt' -print Now adds more: find . -name '*.xml' -exec chmod o-r '{}' ; Where: '{}' inserts each found file into the command line. The ; argument indicates...
AWK Search string /^The/ : Line begin with The /The$/ : Line ends with The /[Tt]he/ : Matches The or the /[a-z]/ : Matches any character from a to z /[a-zA-Z0-9]/ : Matches any letter or number /(^The)|(^the)/...
Install Microsoft Query with windows installer error... Follow this article to resolve: http://support.microsoft.com/kb/913754 Cause: Registry key/value missing at HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlNlsCodePage registry subkey. Name Type Value 850 REG_SZ c_850.nls 852 REG_SZ c_852.nls 855 REG_SZ c_855.nls 857 REG_SZ c_857.nls 860 REG_SZ c_860.nls 861 REG_SZ c_861.nls 863 REG_SZ c_863.nls 865 REG_SZ c_865.nls 866 REG_SZ c_866.nls 867 REG_SZ c_867.nls 868 REG_SZ c_868.nls 869 REG_SZ c_869.nls 874 REG_SZ c_874.nls 875 REG_SZ c_875.nls 932 REG_SZ c_932.nls 936 REG_SZ c_936.nls 949 REG_SZ c_949.nls 950 REG_SZ c_950.nls
Choose the number of rings before the Message Centre... This works on Bell Canada's network: To set the number of rings before Message Centre picks up, do the following: From your mobile phone, press *94 followed by the number of standard rings that...
An error occurred while creating a Page Server subprocess Encountered the error "An error occurred while creating a Page Server subprocess" on InfoView when trying to view a report. Didn't find anything useful on the log directory. It may be a resource...
How to rename DTEngine Cluster Name and/or NodeName Connect to the repository database set current schema DTENGINE Select * from DT_CLUSTER_INFO; update DT_CLUSTER_INFO set cluster_name = 'MYCLUSTER1' Select * from DT_CLUSTER_NODES_INFO; update...
SQL Server 2005 Replication Traditional: Replicating data between servers New: Replicating data between a server and clients
Star schema v.s. Snowflake schema The star schema consists of a few "fact tables" referencing any number of "dimension tables". The "facts" that the data warehouse helps analyze are classified along different "dimensions": the fact tables...
Correlated Subquery A correlated sub-query is dependent upon the outer query. The outer query and the sub-query are related typically through a WHERE statement located in the sub-query. The way a correlated sub-query works...
SQL Server 2008 new features summary Trusted Protect valuable information Transparent data encryption Extensible key management Auditing Ensure business continuity Enhanced database mirroring Automatic recovery...
SQL Server Security Checklist Windows OS Physically secure and harden Server Use a low-privilege user account to startup SQL Server services; use different service accounts for different instances of SQL and/or different...
SQL Server 2005 versions SQL Server 2005 Database Engine version: Release: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 SP1: Microsoft SQL Server 2005 - 9.00.2153.00 (Intel...
db2fmcd After shutting down db2instance and do a ps -eaf |grep db2, you can find a process like this: root 13451 1 0 Apr04 ? 00:00:00 /opt/IBM/db2/V8.1/bin64/db2fmcd This...
DB2 SQL1084C Shared memory segments cannot be allocated.... db2prpat@adminbcu001:~/jobs> db2 quiesce db immediate force connections quiesce db immediate force connections SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019 Ran TOP and saw...
DB2 rollforward DB2 ESE with DPF contains two physical nodes and 5 logical nodes. Lost all the data files on Node 1. db2> db2 rollforward db prdev01 to 2008-04-03-12.46.20.000000 using local time and complete rollforward...
DB2 LUW Performance Measure Database Synchronous Read Percentage (SRP) Where: SRP = 100 - (((Asynchronous pool data page reads + Asynchronous pool index page reads) x 100) / (Buffer pool data physical reads + Buffer pool index...
Restore DB2 database after disk partition lost on one... I have a DB2 ESE with DPF on 2 physical node AdminNode and DataNode. The AdminNode contains Node0 only. The DataNode contains 4 logical nodes: Node 1 to Node 4. The db2 files for each logical nodes...
fn_dblog to read SQL Server log I have used LogExplorer for some time before and found it quite useful in problem investigation. I was expect Microsoft release an official tool to dig into the SQL Server logs which Oracle already offers. I...
SQL Server 2008 CTP6 released SQL Server 2008 CTP6 released Download at: http://www.microsoft.com/sql/2008/prodinfo/download.mspx#EDB
Sun acquires MySQL With the ever popularity of MySQL in the online space, Sun finally figured it is an opportunity that can't be missed. So will the competition heats up between Microsoft, IBM, Oracle and the late comer...
runmqchl takes 100% of cpu on Linux Suse I experienced a problem where runmqchl is taking 100% of cpu on the Linux Suse box, and messages are stuck on the transmit queue and accumulating. Checked MQ from Explorer and all the channels are in...
Set DB2_QP_BYPASS_USERS When DB2_QP_BYPASS_USERS is set, all queries submitted by this user are not captured by Query Patroller. This is more efficient than setting submitter profiles to bypass queries. These are the steps: db2set...
Retrive SQL Query Text From Blocking Session declare @spid varchar(6) declare @blocked varchar(6) declare @dbname nvarchar(128) DECLARE @handle binary(20) --exec sp_who2 select distinct spid, blocked, db_name(dbid), sql_handle from master.dbo.sysprocesses...
Automate deletion of db2 backup from TSM #!/bin/bash #clean up the database backup and log backups #****************************************************************************/ #* ...
Count of Objects in DB2 database select T1.Schema, sum(T1.NumOfTable) as NumOfTable, Sum(T1.NumOfIndex) as NumOfIndex, Sum(T1.NumOfTrigger) as NumOfTrigger, Sum(T1.NumOfRoutine) as NumOfRoutine from ( select tabschema as Schema, count(1)...
Convert timestamp to char while keep microsecond value In order to user the DB2 timestamp value in SQL Server query, we need the timestamp formatted in the SQL Server date time format. Here is what I did: select ((to_char(current timestamp, 'YYYY-MM-DD...
Convert date to integer (seconds since 1970-01-01) Oracle Date to number of seconds since 1970-01-01 select (to_date('24-04-2002','dd-mm-yyyy') - TO_DATE('01-01-1970','DD-MM-YYYY')) * 86400 as int from dual Number to date: select to_date(trunc(1019663083/86400,...
Set DB2 command options C:SQLLIBBIN>db2 list command options Command Line Processor Option Settings Backend process wait time (seconds) (DB2BQTIME) = 1 No. of retries to connect to backend ...
Create database on DB2 ESE with logical partitions There are a few tricks when using ' $N ' in the syntax of creating database on DB2 with DPF: When used at container level, it can be specified as: ' $N' db2 => CREATE DATABASE DB8 db2 (cont.)...
DB2 counterpart of SQL & Oracle commands SQL: select * into t2 from t1 ORACLE: create t2 as select * from t1 where 0 = 1 DB2: CREATE TABLE T2 AS (SELECT * FROM T1) DEFINITION ONLY SQL: select Top 10 * from t1 ORACLE:...
Use db2trc db2trc on -i 8M db2trc clr db2trc dump db2trc.dmp db2trc off db2trc flw db2trc.dmp mytrace.flw db2trc fmt db2trc.dmp mytrace.fmt
Backup and restore SQL schemas for DB2 Universal Database http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0602rielau/index.html
DB2 Note db2mtrk - Memory Tracker Command db2mtrk -i -i On UNIX platforms, show instance level memory. On Windows platforms, show instance and database level memory. -d Show database level memory. Not available...
Create Fedareted Database Objects steps Federated Database Object: (Link dblink object on Oracle) Catalog TCPIP node net02 remote net02 server db2c_db2 remote_instance DB2 system Net02 ostype NT Catalog database sample as SAMPLE at Node...
IE7 stuck on runonce2.aspx Whenever I start IE7 it cannot get the runonce2.aspx page to complete. Here is a walk around: Start->Run->REGEDIT Goto: My Computer -> HKEY_CURRENT_USER -> Software -> Microsoft -> Internet...
Almost burned my Intel Pentium D 940 Dual Core CPU I bought a bare bone kit from Tigerdirect and installed all the components in an old case. The system seems to work fine and much faster than the old PCs except the noise level is a bit high. However...
SQL1091C The release number of the database is not... DB2 connect to DBNAME and got error SQL1091C. Possible cause: After upgrading database from lower version to higher version, the database needs to be migrated. Run: "DB2 migrate database dbname"...
DB2 9 product line (Edition) DB2 Everyplace Edition IBM Cloudscape (Apache Derby) DB2 Personal Edition DB2 Express-C (no-charge) DB2 Express-C Fixed Term license (FTL) DB2 Express Edition DB2 Workgroup Edition DB2...
DB2 System Command Explanation dasauto Autostart DB2 Admin Server dascrt create a DB2 Admin server dasdrop Remover a DB2 Admin server dasmigr Migrate the DB2 Admin server dasupdt Update the DB2 Admin server Db2admin ...
DB2DART db2dart - Database Analysis and Reporting Tool Command Examines databases for architectural correctness and reports any encountered errors. Authorization: sysadm Connection: db2dart must be run...
SQL Server 2008 CTP Available SQL Server code name Katmai CTP version is available for download. This is the July CTP: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5470
DB2 Fix Pack Release Date Version 9 FixPak Release_Date 3 Aug 2007 2a May 2007 2 Feb 2007 1 Oct 2006 Version 8 FixPak Release_Date 15 Aug 2007 (Version 8.2 FixPak...
End of Support Date DB2 UDB Version Products Editions General Available Date End of (base) support date End of extended support Date V7.1 DB2 Universal Database and DB2 Connect All except Enterprise...
Is DBAA your next career goal I have been resting for a few years after worked so hard to become a DBA. Now I start to woder where to go next. This is the first time I heard the term DBAA, and here it explains: http://www.sqlservercentral.com/columnists/jyao/2989.asp
SQL Server 2005 High Avalability Solution SQL Server 2005 Clustering Based on Windows Clustering Automatic failover Transparent to client Transactional integrity Rapid failover (in most case, the failover process can complete...
Create SQL Server 2005 Cluster on Microsoft Windows... Step 1: Follow a document titled "Using Microsoft Virtual Server 2005 to Create and Configure a Two-Node Microsoft Windows Server 2003 Cluster" to create a Windows 2003 Cluster. The above document has...
SQL Server snapshot restore failed with error: 3283 When tried to restore a database from a snapshot backup with HP data protector, the restore process finished successfully but got error: 3283 "The file {Filename} failed to initialize correctly." Microsoft...
Database in transition, Error: 952 Steps to recreate the error: Tried to take the database offline and failed. Cancelled the operation and tried to find any open connection. Tried to open a database from Microsoft SQL Server Managment...
Cumulative update package 2 for SQL Server 2005 Service... This is the 3175 release: http://support.microsoft.com/kb/936305 Sqlservr.exe 2005.90.3175.0 29,193,072 14-Jun-2007 15:05 x86
Setting up SQL Server 2005 Database Mirror To create a database for database mirroring Ensure that both the principal and mirror server instances have Microsoft SQL Server 2005 installed and the principle database uses the full recovery model. Back...
Remove force SQL Server 2005 publication Tried to remove a local publication under sql 2005 replication and got an error: 20032. The cause of the error is that the subsciber is unavailable. In my case the subscriber database has been dropped...
Turn off autoplay on windows With the ever popular external hard disk, it becomes quite anoying whenever I plug in the HD and autoplay starts to scan the content of the HD, so here it goes... 1) Click Start, Run and enter...
Disable certain export types on Reporting Service Reports You need to change the rsreportserver.config to make certain types invisible. Here is a sample: Render Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering" ...
Register an SPN for SQL Server 2005 Services You can register SPN using the setspn tool found in: 892777 (http://support.microsoft.com/kb/892777/) Windows Server 2003 Service Pack 1 Support Tools C:Program FilesSupport Tools>setspn Usage: setspn...
Check SQL Server Client Session Authentication Method select auth_scheme from sys.dm_exec_connections where session_id=@@spid
Project posting Rejection Reason "Distribute Labor Costs" job generates the folloing error: - Could not create a valid GL - No open Project Accounting Period - Missing data on expenditure Currently investigating... -...
Backup cube fails Message Executed as user: INTRANETSSAgentTrendP1. ...ystem error: The following error occurred while the '\?H:MSSQLDATATrendP1Trend.0.dbTrend.0.cubClaim Summary V.0.detClaim Summary V.0.prt8.fact.data'...
Create Database ... ATTACH_REBUILD_LOG, Msg 5120 Tried to attach a database from a set of offline backed up data files, however the log files were not backed up. So the attach command failed. Tried to Create Database with Attach_Rebuild_Log option,...
Manually set SQL Server Reporting Service Virtual Directory SSRS configuration failed on creating "Report Server Virtual Directory", however the ReportServer virtual directory did create on IIS. Refreshed the status on the configuration tool and it showed green...
How to change SSAS port Open X:Program FilesMicrosoft SQL ServerMSSQL.xOLAPConfigmsmdsrv.ini Search for update to your port number: nnnn
MS SQL Server 2005 SP2 Released Microsoft SQL Server 2005 SP2 was released on February 19, 2007. http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx Download page: http://www.microsoft.com/downloads/details.aspx?FamilyID=D07219B2-1E23-49C8-8F0C-63FA18F26D3A&displaylang=en SP2 issue: http://support.microsoft.com/kb/933508/en-us Cumulative...
How to check SQL 2005 ReportServer TempDB IO Saturation http://blogs.msdn.com:80/jgalla/archive/2007/03/21/reportservertempdb-io-saturation.aspx
Check for table scan on your database Check if Oracle is routinely scanning a big table in your database. select distinct t1.object_owner, t1.object_name, t2.num_rows from v$sql_plan t1, dba_tables t2 where t1.object_owner = t2.owner and...
US Daylight Saving Time Change 2007 The United States has planned a change to its DST observance beginning in 2007. The Energy Policy Act of 2005 mandates that DST will start on the second Sunday in March and end on the first Sunday...
Set nocount on performance impact According to an article set nocount on boosts the performance by 3%. "SET NOCOUNT OFF is consistently slower than SET NOCOUNT ON. All in all results for this experiment reveal a 3% performance advantage...
Executing a SSIS package in a job fails The package is pretty simple. It contains only one task to update a Cube. Error message: Either the 'MydomainMyUserID' user does not have permission to process the 'Trend Policy' object, or the object...
SQL Server synonym and truncate Encounted a problem with one of the procedures on a SQL Server database. The violating statement is a truncate statement. As we implemented a generic policy to create synonyms for every tables under...
Used update statistics on DEV env to get the PRD execution... Usually the Development environment (DEV) is a scaled down version of the Production env (PRD) due to capacity and/or cost constraints. A potential risk of promoting a code from DEV to PRD is that query...
One full year of hosting with Dreamhost This marks the first anniversary of the hosting provided by Dreamhost.
Time In, Time Out calendar investment Just read this book titled "Time In, Time Out" by Brooke Thackray & Bruce lindsay, and it suggest two key dates: Oct 28 -- In May 5 -- Out Based on their study of the DOW for the past 45 or...
Nightly SQL Server Database full backup failure Job history page: Date 11/23/2006 12:00:00 AM Log Job History (Backup ReportServer Full) Step ID 0 Server WINSRVP13TRENDP1 Job Name Backup ReportServer Full Step Name (Job...
Plan a trip to Cayo Coco I booked an all inclusive vacation package from itravel2000. The hotel NH KRYSTAL LAGUNA is rated 4.5 star however there are some negative reviews posted on the internet. That gets me a little bit worried. I...
Oracle Financial APP-FND-01931 Client reported APP-FND-01931 on the financial system. APP-FND-01931: Your session is no longer valid or your logon information could not be reestablished from your session. Launching oracle applicaion...
Error scripting all object on SQL 2005 database From SQL Server Management Studio -> Database -> Tasks -> Generate scripts -> Script all object in the selected database. The following error was reported: Generate Script Progress - Determining...
Running SSIS task as SQL job which contains SQL 2000... Steps to recreate the error: 1. Create a new SQL 2000 DTS package or upload an existing package to Microsoft SQL Server Management Studio ->Management ->Legacy->Data Transformation Services 2. Create...
How to enable remote errors on SQL Server reporting... Getting an error message when opening a report from the SSRS web site: An error has occurred during report processing. Cannot create a connection to data source 'TrendDB'. For more information about...
SQL Server 2005 Service Account & Permission Requirements Starting from SQL Server 2005, the SQL Server installation process automatically creates a set of local Windows groups to manager various service accounts used by SQL Server 2005. It is recommended...
Cannot connect to SQL server due to default database... Tried to connect to SQL Server database through Management Studio but experienced the following error: Cannot connect to 2235-D-2435TRENDD1. Additional information: Cannot open use default database....
SQL Server suspected: The log scan number ... passed... Database files got out of sync due to backup software did not release the datafiles in time for database startup. Root cause is still under investigation, however our immediate mission is to startup...
SQL Server 2005: Class not registered (Exception from... Received the following error when browsing a cube from the database server: Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) Applied all the hot fixes on the database...
Finger Lake Trip This is another trip we took on the long weekend of Aug 5 2006. Here is the route: 401--> 403 --> QEW West to Niagara Fall QEW West --> Hwy 405 to Queenston-Lewiston Bridge You are in the...
Marineland Got two ticket from a friend for $22 each, we plan to go to Mrineland (http://www.marinelandcanada.com/) tomorrow. However it started to rain today and according to the weather forecast it will rain...
Bruce Peninsula trip We took another trip to Bruce Peninsula (http://www.thebrucepeninsula.com/tobermory/) again this summer. That was the second visit for me in two years. I had some wonderful time last year. The B&B...
How to map windows users to SQL Server 2005 Reporting... Go to home page of SQL Server reporting service web, for example: http://myserver/Reports On the left hand side, the second line has two tabs: Contents and Properties. Click Properties and...
SQL Server 2005 (64bit) advantage Having played with SQL Server 2005 on 32 bit Windows for a few months, I have finally decided to deploy a 64 bit SQL Server. After the initial comfusion regarding 32bit v.s. 64bit, I discovered the...
SQL Server PORT TCP: 1433 SQL Server 2005 default instance 2382 (ms-olap3) Redirector for other instances of SSAS. (The redirector service is part of the SQL Browser Service) 2383 (ms-olap4) Default...
Retrieve Oracle Text Index DDL If you have an existing Oracle text index on your database, it is not easy to get the DDL from OEM or even the Oracle Text Manager. Came with Oracle 9.2, a new package CTX_REPORT provides greate relief...
SQL Server 2005 on Windows 2003 Server with /pae option I have a Win 2K3 server with 8G memory and I want to install a SQL 2005 and take advantage of the AWE option. Upon setup SQL Server to use AWE and restart the instance, I can see the host memory...
SQL Server table and index space usage report This is the SQL 2005 version: create procedure ops.sp_Space_Report @sizeunit char(1) = null, @debug int = 0 as set nocount on if @debug = 1 Select 'Table Space Usage...
$ORACLE_HOME/rdbms/log directory full This morning sees the Oracle home directory filled up pretty quickly and I eventually figured out that the $ORACLE_HOME/rdbms/log directory has tons of core dump files. These files are not so big in size...
How to: Uninstall an Existing Instance / component... Start -> Control Panel -> Add or Remove Programs. Select "Microsoft SQL Server 2005" -> Remove. On the Component Selection - Uninstall page, select any server and/or shared components to...
SQL Server 2005 Service Pack 1 released April 18 2006 SQL Server 2005 Service Pack 1 Extending the Functionality of your SQL Server 2005 System Published: April 18, 2006Download is available at: http://www.microsoft.com/sql/sp1.mspxSpace requirements:Space...
Best Practices for Running Oracle Databases in Solaris... Best Practices for Running Oracle Databases in Solaris Containers Ritu Kamboj and Fernando Castano, September 2005 This article provides an overview of Solaris Containers in the Solaris 10 Operating...
Wordpress upgraded to 2.0.2 A few new features: 1. Password-protect post 2. Easily upload files / photos. Great improvements.
PRODUCT_USER_PROFILE Table Today I read an article about the PRODUCT_USER_PROFILE (PUP) table and wondered how many things I still don't know in Oracle. PUP provides product-level security that supplements the user-level security...
Oracle License puzzle I have read the database pricing doc ( http://www.oracle.com/corporate/pricing/databaselicensing.pdf ), but am still not clear on how to determine the license requirements. I have a production environment...
Database Normalization There are many expressions of the Normal Form, truely understand them are very important in database design. First normal form (1NF) Second normal form (2NF) Third normal form (3NF) 1NF requires...
Truncate log on checkpoint issue on ASE 11.5? Have an ASE 11.5, trunc log on chkpt is set but doesn't seem to work. sp_dboption go database_options ---------------- abort tran on log full allow...
Free Oracle SQL Developer Production version 1.0 is now avaiable for download: http://www.oracle.com/technology/products/database/sql_developer/index.html
logmnr does not show update statement on sql_redo (Oracle... Logmnr is a powerful tool that enables you to view what has happened to your database even without turning database audit on. Here is a typical example: c:h4sql>sqlplus SQL*Plus: Release...
* Senior Database Analyst * Job Posting Reporting to: Manager, Database Management Status: Full-time Location: ...
The Guest user on Oracle Application Please make sure that the Guest user and password values are correct in all the expected places if you want to change the password for the Guest user on Oracle Applications. 1. GUEST_USER_PWD in file...
Startup Oracle database when windows service starts... This is the magic key that determines whether to startup database when the OracleServiceSID starts up. HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOMEx -> ORA_SID_AUTOSTART This key should be set...
Syslogs space occupied when trunc log on chkpt is on... sp_dboption database_options ---------------- abort tran on log full allow nulls by default auto identity dbo...
Audit data changes on SQL Server 2005 A few options are available: Create one audit table for each base table. Whole row of changed data is saved on the audit table as one row. Create one audit table for the database. Changed data...
Rman backup and Unused Block Compression Today I discussed with a DBA regarding whether RMAN skip those blocks that is not currently being used by any table but was used before. Below is the test script: connect hr/hr create table myobject...
Delete a windows service DESCRIPTION: SC is a command line program used for communicating with the NT Service Controller and services. USAGE: sc [command] [service name] ... The option has the form "\ServerName" Further...
Primary key with non-unique index Before I read this article, I was thinking PK would require a unique index in order to maintain the uniqueness of the key. However I double checked the Oracle document on 9i and it says non-unique index...
Some script to help investigate locks and deadlocks Here is a sample deadlock dump file: *** SESSION ID:(75.13484) 2005-12-20 07:55:46.669 DEADLOCK DETECTED Current SQL statement for this session: INSERT INTO "ASI"."SEARCH_RESULT_L" ("USER_ID","LOSS_TYPE_CD","REF_ID","CREATN_TS","SOURCE_TYPE_CD")...
High CPU usage on Oracle RAC invistigation with statspack First lets look at the STATSPACK report: DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ TEST...
Invest strategy 1. Only invest in companies you understand; 2. Only invest in companies that pay a dividend; 3. Look for companies that are selling cheaply; 4. Invest in companies that are recession-proof; 5....
See what is in the database buffer SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE, COUNT(*) NUMBER_OF_BLOCKS FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'XDB',...
Identify who locked obj 1. Symptom: ORA-0054: When Dropping or Truncating Table, When Creating or Rebuilding Index 2. select object_id from dba_objects where object_name='tablename'; 3. select * from v$locked_object...
Restore data using Oracle flashback query function 用法示例: (1) 找出删除前的数据: SELECT ename, sal FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '6' MINUTE) WHERE ename = 'ALLEN'; SELECT ename, sal...
Some Tuning Scripts Prompt 1. Finding top ten work areas requiring the most cache memory: select * from (select workarea_address, operation_type, policy, estimated_optimal_size from v$sql_workarea order by estimated_optimal_size...
Check Users' SQL Statements select a.sid, a.status, b.parse_calls, b.loads, b.rows_processed, b.executions, b.sql_text from v$session a, v$sql b where a.sql_address = b.address and a.sql_hash_value = b.hash_value and a.sid = --...
Diagnosing database hanging issues 1. Describe the Problems. Under all situations go through this sections and describe the problem. 2 Look for errors. alert.log 3. Do the requested queries. SPOOL v_views.log; SELECT * FROM...
Perform incomplete restore to a point of time or scn... -- Restore files from tape using netbackup export ORACLE_SID= ... rman target / restore controlfile from '/backup/app1/app1t2/rman/...'; alter database mount; alter database create datafile '/apps/app1/t2/db/CAAD01.dbf'; sql...
A complete LOGMNR session example -- Build to a dictionaryexec dbms_logmnr_d.build('dictionary.ora', '/oracle9_home/admin/utl', options => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE); -- Build exec dbms_logmnr_d.build( options => dbms_logmnr_d.store_in_redo_logs); --...
Oracle Memory Tuning suggestions Prompt 1. Tuning OS mem requirementsprompt show sgashow sga prompt 2. Tuning the Redo Log Buffershow parameter log_bufferparameter log_buffer prompt select * from v$sysstat where name = 'redo buffer...
Oracle Profiler Script Prompt Tablespace size select t1.name,sum(t2.bytes/1024/1024) as Size_M from v$tablespace t1, v$datafile t2 where t1.ts#=t2.ts# group by t1.name; Prompt File name select t1.ts#, t1.name , t2.file#,...
Oracle® Applications 11i Release 10.2 CD/Media Pack... Oracle® Applications 11i Release 10.2 CD/Media Pack for Microsoft Windows Oracle® Applications 11i Release 11.5.10.2 Rapid Install Start Here - Disk 1 (Part 1 of 7) B24280-01 Part 1 of 7 214M Oracle®...
Install Oracle Application 11.5.1 on WIN2K/XP In oracle Apps 11.5.1 it consist of 12 CD's. 1) Start Here CD(RAPID Installation Wizard) 2)1-11 CD's 1-3 Application Top information 4th Oracle DB 5-6 IAS and other tools 7-11 Oracle Apps DB. You...
DreamHost experience 前几天听gytyl说起这个hosting服务商有很大的discount, 刚开始还不太相信. 我就在网上找了其他几家对比, 发现DreamHost还是不错的. 关键你要找到好的优惠号,...
Oracle backup solution with SUN StorEdge Availability... • Environment OS: Sun Solaris 2.8 Oracle version: 9.2.0.6.0 ORACLE_SID: prod Data volume: /dprod01 /dprod02 /dprod03 • Original backup solution Oracle manual online backup Problem:...
Posted on : 19-12-2009 | By : Harry Zheng | In : Microsoft SQL Server
Posted on : 19-12-2009 | By : Harry Zheng | In : General
ASP.NET MVC 2 is a framework for developing highly testable and maintainable Web applications by leveraging the Model-View-Controller (MVC) pattern. The framework encourages developers to maintain a clear separation of concerns among the responsibilities of the application – the UI logic using the view, user-input handling using the controller, and the domain logic using the model. ASP.NET MVC applications are easily testable using techniques such as test-driven development (TDD).
Download here
Posted on : 30-11-2009 | By : Harry Zheng | In : Microsoft SQL Server
I haven’t used too much of 3Com products recently, however 3Com is certainly a well known name in the early days. With $2.7 billion on the table, HP is geared up to compete against network giant Cisco.
I would be very pleased if I am a share holder of 3Com. Read the story…
Posted on : 28-11-2009 | By : Harry Zheng | In : General
Getting a alert on the bottom right cornor of the window saying “Can’t check for updates”.
Start -> Control Panel ->Nothing shows up
It is still possible to access Control Panel from Windows Explorer (Windows Key + E).
Cause:
Error 80070426 indicates Software Licensing service is not running. The Software Licensing service enables the download, installation and enforcement of digital licenses for Windows and Windows applications. If the service is disabled, Windows and licensed applications may fail to get proper validation and activation.
In the previous version of Windows, it is safe to disable this service as it is mainly used to track how many licenses have been used in an enterprise. Now Microsoft uses it to validate if the Windows software is genuine.
Posted on : 27-11-2009 | By : Harry Zheng | In : General
Note the nesting syntax:
triggers
multitrigger
triggers
…
Posted on : 24-11-2009 | By : Harry Zheng | In : General
Tried to open a WCF service site and got:
HTTP Error 404.3 – Not FoundThe page you are requesting cannot be served because of the extension configuration. If the page is a script, add a handler. If the file should be downloaded, add a MIME map.
Error Code 0×80070032
This resolves:
Run cmd as Administrator.
C:\Windows>cd “C:\Windows\Microsoft.NET\Framework64\v3.0\Windows Communication Foundation”
servicemodelReg -i
Posted on : 24-11-2009 | By : Harry Zheng | In : General
HTTP Error 500.19 – Internal Server Error
The requested page cannot be accessed because the related configuration data for the page is invalid.
Module: IIS Web Core
Notification: BeginRequest
Handler: Not yet determined
Error Code: 0×80070021
Config Error: This configuration section cannot be used at this path. This happens when the section is locked at a parent level. Locking is either by default (overrideModeDefault=”Deny”), or set explicitly by a location tag with overrideMode=”Deny” or the legacy allowOverride=”false”.
Logon Method Not yet determined
Logon User Not yet determined
Config Source:
165:
166:
167:
To override parent level setting in IIS7:
Start cmd prompt with “Run as Administrator”
C:\Windows\system32>%windir%\system32\inetsrv\appcmd.exe unlock config -section:system.webServer/handlers
Unlocked section “system.webServer/handlers” at configuration path “MACHINE/WEBROOT/APPHOST”.
0