For example, you may have some .Net 1.1.4 COM+ serviced components running under dllhost.exe, and not be able to re-compile them using .net 2.0 for some reasons. You can forcefully run them under .Net 2.0 framework.
You just need to create a dllhost.exe.config file for the %windir%\system32\dllhost.exe. Please note the filename has to be dllhost.exe.config and be placed along with your dllhost.exe. You need this .config file to specify the runtime version you want.
dllhost.exe.config:
<?xml version ="1.0"?>
<configuration>
<startup>
<requiredRuntime version="v2.0.50727" safemode="true"/>
<supportedRuntime version="v2.0.50727" safemode="true"/>
</startup>
</configuration>
DtcGetTransactionManager is typically the first DTC call that application programs and resource managers make when using DTC. This helper function establishes the initial connection to DTC. It returns an interface pointer to one of the interfaces on the DTC proxy core object.
DTC had been working properly untill recently a security patch was installed. DtcGetTransactionManager call fails. Actually this is caused by Group Policy object(GPO) setting applied. You can fix the issue by doing following steps:
- Click on Start -> Administrative Tools -> Domain Controller Security Policy
- Select Security Settings -> System Services
- Look for Distributed Transaction Coordinator on the right and right click Properties on it
- The security policy setting window will pop up
- Check the “Define this policy setting” check box
- Change the service startup mode to Automatic
- Click on Edit Security and add “Everyone” and give Full Control
- Click on OK, Click on OK
- Open a command window and type “gpupdate /force”
- Restart the “Distributed Transaction Coordinator” NT service
- iisreset
You may want to check out this list of the deprecated features in SQL 2005.
I noticed that DBCC DBREINDEX is one on the list. SQL 2005 introduces a new REBUILD option to the ALTER INDEX command to replace it. This new REBUILD option can perform online or offline re-indexing operations.
DBCC DBREINDEX can only perform offline reindexing.
Normally you use the trigger functionality with Data Manipulation Language (DML) commands such as INSERT, UPDATE, and DELETE. Now SQL 2005 introduces new Data Definition Language (DDL) triggers. You can use it to audit schema changes.
Example of DDL Trigger:
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT ‘You must disable Trigger “safety” to drop or alter tables!’
ROLLBACK;
DISABLE TRIGGER safety ON DATABASE;
Please refer to MSDN for details: MSDN: Designing DDL Triggers.
An articel about using DDL triggers: Using SQL Server 2005 to document Sarbanes-Oxley compliance
In its continued commitment to interoperability, Microsoft released a new Java Database Connectivity (JDBC) driver with SQL Server 2005.
The SQL Server 2005 JDBC Driver download is available to all SQL Server users at no additional charge, and provides access to SQL Server 2000 and SQL Server 2005 from any Java application, application server, or Java-enabled applet. This is a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in J2EE (Java2 Enterprise Edition).
The SQL Server 2005 JDBC driver is JDBC 3.0 compliant and runs on the 1.4 JDK and higher. It has been tested against all major application servers including BEA WebLogic, IBM WebSphere, JBoss, and Sun. The new JDBC Driver is also freely redistributable for registered partners. -MSDN
What a great news for Java developers! Now you don’t have to rely on third party drivers.
SQL Server 2005 includes a redesigned enterprise data extraction, transformation, and loading (ETL) platform, called SQL Server Integration Services (SSIS), which replaces DTS 2000.
SSIS (SQL Server Intergration Services) has some new features:
- Business Inteligence Development Studio (BIDS) for creating DTS packages
- SSIS packages can be saved as XML .
- .Net languages can be used for creating SSIS components.
- SSIS allows FTP in/out
- SSIS has new comand line tools(DTSExec replaces DTSRun, DTSExecUI replaces DTSRunUI, DTSUtil).
- And more…
Use DMV and CROSS APPLY to Get Details of SQL’s Cached Query Plans
SQL 2005’s dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. For example, following query fetches SQL server’s cached query plans. You can use the refCounts and useCounts for performance analysis.
SELECT cached.*,
sqltext.*
FROM sys.dm_exec_cached_plans cached
CROSS APPLY sys.dm_exec_sql_text (cached.plan_handle) AS sqltext
You can click here to see the screen snapshot of the query output.
Note: CROSS APPLY, a new feature of SQL Server 2005, is quite simple: an INNER (or outer) JOIN between a table and a table-valued function.
sys.dm_exec_sql_text: Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.
sys.dm_exec_cached_plans: Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.
Comments Off
SQL Server 2005 has a default trace running. Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur. You can execute the following command to view it:
SELECT * FROM fn_trace_gettable(
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default);
GO
The trace is stored in the LOG directory for your SQL Server instance (i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\) at the root of where your SQL Server is installed. To determine if the trace is configured to run, execute sp_configure and review the ‘default trace enabled’ option. When the config_value and run_value are equal to 1, then this trace is running. You can use sp_configure command to turn it off too.
You can search “Default Trace enabled Option” in the SQL Help to find more detailed information about the default trace.
The server has two drives, and the default location for tempdb is c:\mssql7\data, can we change it to somewhere in D drives?
Some one said that he used T-SQL to move it before. While someone told me that it can not be moved. Do you know how?
Thank you!
As the question asked above, you may want to move your database to a new location for better performance. Besides Detach/Attach database, you can simply follow the steps below using ALTER DATABASE MODIFY FILE:
-- Step1: put the database offline
ALTER DATABASE [Products] SET OFFLINE;
GO
-- Step2: move the file to the new location
ALTER DATABASE [Products] MODIFY FILE (NAME = [Products],
FILENAME = 'd:\mssql\data\Products.mdf')
GO
-- Step3: bring the database online
ALTER DATABASE [Products] SET ONLINE
GO
COALESCE (Transact-SQL)
Returns the first nonnull expression among its arguments. (From MSDN)
COALESCE is essentially same as below:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END
Example:
In this sample, we use COALESCE to generate a flat list. You can specify a delimiter to seperate the items in the list.
CREATE TABLE [dbo].[student](
[id] [int] NOT NULL,
[name] [nvarchar](50),
[ssn] [varchar](11),
CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT student VALUES(1, 'andrew', '201-98-9238')
INSERT student VALUES(2, 'lindsay', '656-89-9238')
INSERT student VALUES(3, 'david', '555-22-1111')
GO
DECLARE @list varchar(1024)
SELECT @list = COALESCE(@list + '|', '') + name
FROM student
PRINT @list
Here is the result set:
andrew|lindsay|david
