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
SQL 2005 has introduced a new system database referred as Resource database, namely RDB. Its mdf file name is ‘mssqlsystemresource.mdf’. Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. All system objects such as sys.objects are physically stored in the Resource database, but they logically appear in the sys schema of every database.
You can run
SELECT [name], object_definition(object_id) from sys.system_objects
from any database, which will give a same output.
The master database stores your logins and, most importantly, the pointers to all of your databases. Without the master database, you can’t successfully start SQL Server. To recover your master database, you need to use the Rebuild Wizard (Rebuildm.exe), located in the \Program Files\Microsoft SQL Server\80\Tools\BINN directory.
However, you may not know that RebuildM.exe utility has retired since SQL 2005. In case your system database gets corrupted, how you rebuild Master database?
You will need to use the setup.exe on the installation CD to rebuild system databases. RebuildM.exe now has been merged into the main setup program.
Have you ever writen any BAT file or Perl script before? Now it’s time to use Windows PowerShell instead! It’s a powerful tool for writing automation scripts like build or pre-processing, etc. It’s extremely fun to use it since it’s .Net based and supports Xml and objects.
Microsoft Windows PowerShell command line shell and scripting language helps IT Professionals achieve greater productivity. Using a new admin-focused scripting language, more than 130 standard command line tools, and consistent syntax and utilities, Windows PowerShell allows IT Professionals to more easily control system administration and accelerate automation. Windows PowerShell is easy to adopt, learn, and use, because it works with your existing IT infrastructure and existing script investments, and because it runs on Windows XP, Windows Vista, Windows Server 2003 and Windows Server Longhorn. Exchange Server 2007, System Center Operations Manager 2007, System Center Data Protection Manager V2, and System Center Virtual Machine Manager leverage Windows PowerShell to improve efficiency and productivity.
In short: PowerShell
exposes the power of .NET via a command line environment and scripting language
allows you to pipe objects from one command to another!
provides powerful supports of XML handling and regular expressions
Get Started:
Download Powershell from Microsoft first. Write and run a script. You can run following command to allow you to run unsigned scripts on your local machine.
PS C:\> Set-ExecutionPolicy RemoteSigned
To create a script, just make a text file with the .ps1 extension, like MyBuild.ps1. To run it, you need to type the full path to the script or use .\filename, like .\MyBuild.
Download info for Powershell.
Can you get the address of a Managed Type? look at the code below:
class Point
{
public int x;
public int y;
}
class A
{
unsafe static void Main()
{
Point pt = new Point();
// Using fixed allows the address of pt members to be
// taken, and "pins" pt so it isn't relocated.
fixed (int* p = &pt.x) //This is okay!
{
*p = 1;
}
fixed (int* p = &pt) //This won't compile.
{
//...
}
}
}
Above code won’t compile due to following error:
Cannot take the address of, get the size of, or declare a pointer to
a managed type ('something.Point')
Conclusion: from above sample, you can see that you cannot get the address of a managed type! But you can point to value types of a class.
By the way, you might don’t know much about fixed statement, I copied the definition of it from MSDN.
fixed Statement (C# Reference)
The fixed statement prevents the garbage collector from relocating a movable variable. The fixed statement is only permitted in an unsafe context.
