Friday, February 23, 2007
Thursday, February 22, 2007
How To Find Out Which Columns Have Defaults And What Those Default Values Are
Okay so many many moons ago you created a bunch of tables and those tables have columns of course. You want to know how to find the columns that have defaults.
There are a couple of ways to do this
Below is a list:
1 INFORMATION_SCHEMA.COLUMNS view (2000 and 2005)
2 sysobjects,syscolumns and syscomments (2000 only)
3 sys.default_constraints and sys.sysobjects (2005 only)
4 sp_help (2000 only)
So let's get started with some code
CREATE TABLE blah(id INT DEFAULT 0,
SomeDate DATETIME DEFAULT CURRENT_TIMESTAMP)
INSERT blah DEFAULT VALUES
SELECT * FROM blah
--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'
--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'
--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'
See what happens when you don't specify a name (we will do this later)? You will get wacky names like these: DF__blah__id__15A53433 and DF__blah__SomeDate__1699586C
Instead of specifying the default when creating the table use an alter table add constraint statement.Let's see this in action.
DROP TABLE blah
CREATE TABLE blah(id INT,SomeDate DATETIME)
ALTER TABLE blah
ADD CONSTRAINT DF_Blah_ID_0
DEFAULT 0 FOR id
ALTER TABLE blah
ADD CONSTRAINT DF_Blah_SomeDate_Now
DEFAULT CURRENT_TIMESTAMP FOR SomeDate
INSERT blah DEFAULT VALUES
SELECT * FROM blah
--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'
--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'
--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'
And last we have sp_help
You can use sp_help in SQL Server 2000(you can also use it in SQL server 2005 but it doesn't return the defaults )
Execute the following
sp_help 'blah'
The defaults will be in the last resultset (the one where the first column name = constraint_type)
And last but not least did you notice that we had CURRENT_TIMESTAMP but when we queried the table we saw GETDATE() This is kind of strange since CURRENT_TIMESTAMP is ANSI complaint but GETDATE() is not
There are a couple of ways to do this
Below is a list:
1 INFORMATION_SCHEMA.COLUMNS view (2000 and 2005)
2 sysobjects,syscolumns and syscomments (2000 only)
3 sys.default_constraints and sys.sysobjects (2005 only)
4 sp_help (2000 only)
So let's get started with some code
CREATE TABLE blah(id INT DEFAULT 0,
SomeDate DATETIME DEFAULT CURRENT_TIMESTAMP)
INSERT blah DEFAULT VALUES
SELECT * FROM blah
--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'
--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'
--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'
See what happens when you don't specify a name (we will do this later)? You will get wacky names like these: DF__blah__id__15A53433 and DF__blah__SomeDate__1699586C
Instead of specifying the default when creating the table use an alter table add constraint statement.Let's see this in action.
DROP TABLE blah
CREATE TABLE blah(id INT,SomeDate DATETIME)
ALTER TABLE blah
ADD CONSTRAINT DF_Blah_ID_0
DEFAULT 0 FOR id
ALTER TABLE blah
ADD CONSTRAINT DF_Blah_SomeDate_Now
DEFAULT CURRENT_TIMESTAMP FOR SomeDate
INSERT blah DEFAULT VALUES
SELECT * FROM blah
--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'
--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'
--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'
And last we have sp_help
You can use sp_help in SQL Server 2000(you can also use it in SQL server 2005 but it doesn't return the defaults )
Execute the following
sp_help 'blah'
The defaults will be in the last resultset (the one where the first column name = constraint_type)
And last but not least did you notice that we had CURRENT_TIMESTAMP but when we queried the table we saw GETDATE() This is kind of strange since CURRENT_TIMESTAMP is ANSI complaint but GETDATE() is not
Wednesday, February 21, 2007
SQL Server 2005 Best Practices Analyzer (February 2007 CTP) Available For Download
As if you did not download enough in the last 3 days here is one more: SQL Server 2005 Best Practices Analyzer (February 2007 CTP)
The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.
This download is the February 2007 Community Technology Preview of SQL Server 2005 Best Practices Analyzer.
The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.
This download is the February 2007 Community Technology Preview of SQL Server 2005 Best Practices Analyzer.
SQL Server 2005 Books Online Is Not Part Of The Service Pack 2 Download
If you have downloaded SQL Server 2005 Service Pack 2 you probably have noticed that it does not come with Books On Line. You can download that here: SQL 2005 Books Online Feb 2007 (SP2)
Here are two more downloads:
SQL Server 2005 Reporting Services Add-in for SharePoint Technologies
SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007
Here are two more downloads:
SQL Server 2005 Reporting Services Add-in for SharePoint Technologies
SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007
Tuesday, February 20, 2007
February 2007 Feature Pack for Microsoft SQL Server 2005
Yesterday I told you that SQL server Service Pack 2 has been released. Microsoft also released the February 2007 Feature Pack for Microsoft SQL Server 2005
The February 2007 Feature Pack for Microsoft SQL Server 2005 is a collection of standalone install packages that provide additional value for SQL Server 2005.
Here is what is available:
Microsoft ADOMD.NET
ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.
Microsoft Core XML Services (MSXML) 6.0
Microsoft Core XML Services (MSXML) 6.0 is the latest version of the native XML processing stack. MSXML 6.0 provides standards-conformant implementations of XML 1.0, XML Schema (XSD) 1.0, XPath 1.0, and XSLT 1.0. In addition, it offers 64-bit support, increased security for working with untrusted XML data, and improved reliability over previous versions of MSXML.
Microsoft OLEDB Provider for DB2
The Microsoft OLE DB Provider for DB2 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2005 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor. Run the self-extracting download package to create an installation folder. The single setup program will install the provider and tools on x86, x64, and IA64 computers. The package includes product updates in the form of an integrated Service Pack 1. Read the installation guide and Readme for more information.
Microsoft SQL Server Management Pack for MOM 2005
The Microsoft SQL Server Management Pack enables you to monitor SQL Server 2005 and SQL Server 2000 in an enterprise environment. Included are enterprise-level capabilities to monitor resource availability and configuration, collect performance data, and test default thresholds. Local and remote connectivity checks help ensure database availability.
With the embedded expertise in the SQL Server Management Pack, you can identify issues and manage issues before they become critical. This Management Pack increases the security, availability, and performance of your SQL Server infrastructure.
The Microsoft SQL Server Management Pack Guide that is included describes the content of the management pack and how to deploy it.
Microsoft SQL Server 2000 PivotTable Services
PivotTable Services 8.0 is the OLE DB provider for SQL Server 2000 Analysis Services and is used to connect with an Analysis Services 2000 server. PivotTable Services does not work with SQL Server 2005 Analysis Services. Therefore, client applications that need connect to Analysis Services in both SQL Server 2000 and SQL Sever 2005 will need to install both PivotTable Services 8.0 and Analysis Services 9.0 OLE DB Provider in a side-by-side configuration.
Microsoft SQL Server 2000 DTS Designer Components
The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.
Microsoft SQL Server Native Client
Microsoft SQL Server Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 7.0, 2000 or 2005. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2005 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.
Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider
The Analysis Services 9.0 OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.
Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with SQL Server 2005 and include all fixes shipped through SQL Server 2000 SP4.
Microsoft SQL Server 2005 Command Line Query Utility
The SQLCMD utility allows users to connect, send Transact-SQL batches, and output rowset information from SQL Server 7.0, SQL Server 2000, and SQL Server 2005 instances. SQLCMD is a replacement for ISQL and OSQL, but can coexist with installations that have ISQL or OSQL installed.
Microsoft SQL Server 2005 Datamining Viewer Controls
The Data Mining Web Controls Library is a set of Microsoft Windows Forms controls that enable software developers to display data mining models created using Microsoft SQL Server 2005 Analysis Services in their client-side applications. The controls in this library display the patterns that are contained in Analysis Services mining models.
Microsoft SQL Server 2005 JDBC Driver
In its continued commitment to interoperability, Microsoft has released and supports a new Java Database Connectivity (JDBC) driver for 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 programming interfaces (APIs) available in J2EE (Java2 Enterprise Edition).
Microsoft SQL Server 2005 Management Objects Collection
The Management Objects Collection package includes several key elements of the SQL Server 2005 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2005.
Microsoft SQL Server 2005 Compact Edition
Microsoft SQL Server 2005 Compact Edition is the next version of SQL Server Mobile adding the desktop platform. SQL Server Compact extends the SQL Server Mobile technology by offering a low maintenance, compact embedded database for single-user client applications for all Windows platforms including tablet PCs, pocket PCs, smart phones and desktops. Just as with SQL Server Mobile, SQL Server Compact is a free, easy-to-use, lightweight, and embeddable version of SQL Server 2005 for developing desktop and mobile applications.
Microsoft SQL Server 2005 Notification Services Client Components
The SQL Server 2005 Notification Services Client Components package provides client APIs that enable subscription management and event submission within custom applications that include SQL Server 2005 Notification Services functionality. The subscription management APIs allow developers to create subscriptions and subscribers, and manage subscriber devices. The event submission APIs allow users to specify events using the event APIs or stored procedures.
Microsoft SQL Server 2005 Upgrade Advisor
Upgrade Advisor analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005. Upgrade Advisor identifies deprecated features and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.
Microsoft .NET Data Provider for mySAP Business Suite, Preview Version
SQL Server 2005 includes support for accessing SAP data by using the Microsoft .NET Data Provider for mySAP Business Suite. This provider lets you create an Integration Services package that can connect to a mySAP Business Suite solution and then execute commands to access data via supported interfaces. You can also create Reporting Services reports against a server running the mySAP Business Suite.
You can use the Microsoft .NET Data Provider for mySAP Business Suite in the SQL Server Import and Export Wizard and in various Integration Services features (including the Script task, the DataReader Source component, and the Script component), as well as the data processing extensions in Reporting Services.
The Microsoft .NET Data Provider for mySAP Business Suite is not included in SQL Server 2005. The preview version is licensed as pre-release software as outlined in the License Terms. See the Readme included with the download for information on the prerequisites for using the Microsoft .NET Data Provider for mySAP Business Suite.
Reporting Add-In for Microsoft Visual Web Developer 2005 Express
The Reporting Add-In for Microsoft Visual Web Developer 2005 Express includes a ReportViewer control, an integrated report designer, and a comprehensive API that lets you customize run-time functionality. You can use the control to add interactive real-time data reports to your ASP.NET Web applications. Reports can use data from any ADO.NET DataTable or custom Business object. You can create reports that combine tabular, matrix, and visual data in free-form or traditional report layouts. An integrated report designer is included so that you can create the custom reports that bind to the control.
The Reporting Add-In is the same reporting component that is included with other editions of Visual Studio, but without support for Windows Forms applications. For more information including product documentation and samples, see the ReportViewer Controls (Visual Studio) topic on MSDN.
Microsoft Exception Message Box
The exception message box is a programmatic interface that you can use in your applications for any tasks for which MessageBox may be used. The exception message box is a supported managed assembly designed to elegantly handle managed code exceptions. It provides significantly more control over the messaging experience and gives your users the options to save error message content for later reference and to get help on messages.
Data Mining Managed Plug-in Algorithm API for SQL Server 2005
The Managed Plug-in API is a Microsoft .NET object model that enables software developers to create plug-in data mining algorithms for SQL Server 2005 by using CLI-compliant languages, such as Visual C# 2.0. The object model is available as source code and it needs to be built on the developer’s computer. The package includes a step-by-step tutorial, a compiled HTML help file (.chm), as well as a sample plug-in algorithm developed in C#.
Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies
Microsoft SQL Server 2005 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2005 Service Pack 2 (SP2) report processing and management capabilities in SharePoint. The download provides a Report Viewer web part, web application pages, and support for using standard Windows SharePoint Services.
Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007
Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007 allow you take advantage of SQL Server 2005 predictive analytics in Office Excel 2007 and Office Visio 2007. The download includes Table Analysis Tools for Excel, Data Mining Client for Excel, and Data Mining Templates for Visio.
You can download all the packages here: http://www.microsoft.com/downloads/details.aspx?FamilyID=50B97994-8453-4998-8226-FA42EC403D17&displaylang=en#filelist
The February 2007 Feature Pack for Microsoft SQL Server 2005 is a collection of standalone install packages that provide additional value for SQL Server 2005.
Here is what is available:
Microsoft ADOMD.NET
ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.
Microsoft Core XML Services (MSXML) 6.0
Microsoft Core XML Services (MSXML) 6.0 is the latest version of the native XML processing stack. MSXML 6.0 provides standards-conformant implementations of XML 1.0, XML Schema (XSD) 1.0, XPath 1.0, and XSLT 1.0. In addition, it offers 64-bit support, increased security for working with untrusted XML data, and improved reliability over previous versions of MSXML.
Microsoft OLEDB Provider for DB2
The Microsoft OLE DB Provider for DB2 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2005 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor. Run the self-extracting download package to create an installation folder. The single setup program will install the provider and tools on x86, x64, and IA64 computers. The package includes product updates in the form of an integrated Service Pack 1. Read the installation guide and Readme for more information.
Microsoft SQL Server Management Pack for MOM 2005
The Microsoft SQL Server Management Pack enables you to monitor SQL Server 2005 and SQL Server 2000 in an enterprise environment. Included are enterprise-level capabilities to monitor resource availability and configuration, collect performance data, and test default thresholds. Local and remote connectivity checks help ensure database availability.
With the embedded expertise in the SQL Server Management Pack, you can identify issues and manage issues before they become critical. This Management Pack increases the security, availability, and performance of your SQL Server infrastructure.
The Microsoft SQL Server Management Pack Guide that is included describes the content of the management pack and how to deploy it.
Microsoft SQL Server 2000 PivotTable Services
PivotTable Services 8.0 is the OLE DB provider for SQL Server 2000 Analysis Services and is used to connect with an Analysis Services 2000 server. PivotTable Services does not work with SQL Server 2005 Analysis Services. Therefore, client applications that need connect to Analysis Services in both SQL Server 2000 and SQL Sever 2005 will need to install both PivotTable Services 8.0 and Analysis Services 9.0 OLE DB Provider in a side-by-side configuration.
Microsoft SQL Server 2000 DTS Designer Components
The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.
Microsoft SQL Server Native Client
Microsoft SQL Server Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 7.0, 2000 or 2005. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2005 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.
Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider
The Analysis Services 9.0 OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.
Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with SQL Server 2005 and include all fixes shipped through SQL Server 2000 SP4.
Microsoft SQL Server 2005 Command Line Query Utility
The SQLCMD utility allows users to connect, send Transact-SQL batches, and output rowset information from SQL Server 7.0, SQL Server 2000, and SQL Server 2005 instances. SQLCMD is a replacement for ISQL and OSQL, but can coexist with installations that have ISQL or OSQL installed.
Microsoft SQL Server 2005 Datamining Viewer Controls
The Data Mining Web Controls Library is a set of Microsoft Windows Forms controls that enable software developers to display data mining models created using Microsoft SQL Server 2005 Analysis Services in their client-side applications. The controls in this library display the patterns that are contained in Analysis Services mining models.
Microsoft SQL Server 2005 JDBC Driver
In its continued commitment to interoperability, Microsoft has released and supports a new Java Database Connectivity (JDBC) driver for 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 programming interfaces (APIs) available in J2EE (Java2 Enterprise Edition).
Microsoft SQL Server 2005 Management Objects Collection
The Management Objects Collection package includes several key elements of the SQL Server 2005 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2005.
Microsoft SQL Server 2005 Compact Edition
Microsoft SQL Server 2005 Compact Edition is the next version of SQL Server Mobile adding the desktop platform. SQL Server Compact extends the SQL Server Mobile technology by offering a low maintenance, compact embedded database for single-user client applications for all Windows platforms including tablet PCs, pocket PCs, smart phones and desktops. Just as with SQL Server Mobile, SQL Server Compact is a free, easy-to-use, lightweight, and embeddable version of SQL Server 2005 for developing desktop and mobile applications.
Microsoft SQL Server 2005 Notification Services Client Components
The SQL Server 2005 Notification Services Client Components package provides client APIs that enable subscription management and event submission within custom applications that include SQL Server 2005 Notification Services functionality. The subscription management APIs allow developers to create subscriptions and subscribers, and manage subscriber devices. The event submission APIs allow users to specify events using the event APIs or stored procedures.
Microsoft SQL Server 2005 Upgrade Advisor
Upgrade Advisor analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005. Upgrade Advisor identifies deprecated features and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.
Microsoft .NET Data Provider for mySAP Business Suite, Preview Version
SQL Server 2005 includes support for accessing SAP data by using the Microsoft .NET Data Provider for mySAP Business Suite. This provider lets you create an Integration Services package that can connect to a mySAP Business Suite solution and then execute commands to access data via supported interfaces. You can also create Reporting Services reports against a server running the mySAP Business Suite.
You can use the Microsoft .NET Data Provider for mySAP Business Suite in the SQL Server Import and Export Wizard and in various Integration Services features (including the Script task, the DataReader Source component, and the Script component), as well as the data processing extensions in Reporting Services.
The Microsoft .NET Data Provider for mySAP Business Suite is not included in SQL Server 2005. The preview version is licensed as pre-release software as outlined in the License Terms. See the Readme included with the download for information on the prerequisites for using the Microsoft .NET Data Provider for mySAP Business Suite.
Reporting Add-In for Microsoft Visual Web Developer 2005 Express
The Reporting Add-In for Microsoft Visual Web Developer 2005 Express includes a ReportViewer control, an integrated report designer, and a comprehensive API that lets you customize run-time functionality. You can use the control to add interactive real-time data reports to your ASP.NET Web applications. Reports can use data from any ADO.NET DataTable or custom Business object. You can create reports that combine tabular, matrix, and visual data in free-form or traditional report layouts. An integrated report designer is included so that you can create the custom reports that bind to the control.
The Reporting Add-In is the same reporting component that is included with other editions of Visual Studio, but without support for Windows Forms applications. For more information including product documentation and samples, see the ReportViewer Controls (Visual Studio) topic on MSDN.
Microsoft Exception Message Box
The exception message box is a programmatic interface that you can use in your applications for any tasks for which MessageBox may be used. The exception message box is a supported managed assembly designed to elegantly handle managed code exceptions. It provides significantly more control over the messaging experience and gives your users the options to save error message content for later reference and to get help on messages.
Data Mining Managed Plug-in Algorithm API for SQL Server 2005
The Managed Plug-in API is a Microsoft .NET object model that enables software developers to create plug-in data mining algorithms for SQL Server 2005 by using CLI-compliant languages, such as Visual C# 2.0. The object model is available as source code and it needs to be built on the developer’s computer. The package includes a step-by-step tutorial, a compiled HTML help file (.chm), as well as a sample plug-in algorithm developed in C#.
Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies
Microsoft SQL Server 2005 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2005 Service Pack 2 (SP2) report processing and management capabilities in SharePoint. The download provides a Report Viewer web part, web application pages, and support for using standard Windows SharePoint Services.
Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007
Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007 allow you take advantage of SQL Server 2005 predictive analytics in Office Excel 2007 and Office Visio 2007. The download includes Table Analysis Tools for Excel, Data Mining Client for Excel, and Data Mining Templates for Visio.
You can download all the packages here: http://www.microsoft.com/downloads/details.aspx?FamilyID=50B97994-8453-4998-8226-FA42EC403D17&displaylang=en#filelist
Podcast With Dr. Peter Chen on the Entity Relationship Model and ADO.NET Entity Framework On Channel 9
If you are a SQL developer then you have probably heard of Dr. Peter Chen, he is the person who wrote the original paper on Entity-Relationship model (ER model).
From the site:
Dr. Peter Chen is a Fellow of ACM, IEEE, and AAAS and has received many prestigious awards including ACM/AAAI Allen Newell Award and IEEE Harry Goode Award. He has been listed in Who’s Who in America and Who’s Who in the World for more than 15 years.
Dr. Chen's original paper on the
Entity-Relationship model (ER model), published in 1976 is one of
the most cited papers in the computer software field. Based on one particular
citation database, Chen's paper is the 35th most cited
article in Computer Science. It is the 4th most downloaded
paper from the ACM Digital Library in January 2005 (Communications of ACM, March
2005) even though the paper was published 30 years ago.
Dr. Chen’s work is a cornerstone of software engineering, in particular Computer-Aided Software Engineering (CASE). In the late 80’s and early 90’s, IBM’s Application Development Cycle (AD/Cycle) framework and DB2 repository (RM/MVS) were based on the ER model. Other vendors’ repository systems such as Digital’s CDD+ were also based on the ER model. Prof. Chen has made significant impact on the CASE industry by his research work and by his lecturing around the world on structured system development methodologies. Most of the major CASE tools including Computer Associates’ ERWIN, Oracle’s Designer/2000, and Sybase’s PowerDesigner (and even a general drawing tool like Microsoft’s VISIO) are influenced by the ER model.
In this podcast, Brian Beckman interviews Dr. Chen along with Jose Blakeley, Software Architect, SQL Server, and Britt Johnston, Director of Program Management, Data Programmability
The podcast is 51 minutes and 24 seconds
Visit Channel 9 to download the Podcast
From the site:
Dr. Peter Chen is a Fellow of ACM, IEEE, and AAAS and has received many prestigious awards including ACM/AAAI Allen Newell Award and IEEE Harry Goode Award. He has been listed in Who’s Who in America and Who’s Who in the World for more than 15 years.
Dr. Chen's original paper on the
Entity-Relationship model (ER model), published in 1976 is one of
the most cited papers in the computer software field. Based on one particular
citation database, Chen's paper is the 35th most cited
article in Computer Science. It is the 4th most downloaded
paper from the ACM Digital Library in January 2005 (Communications of ACM, March
2005) even though the paper was published 30 years ago.
Dr. Chen’s work is a cornerstone of software engineering, in particular Computer-Aided Software Engineering (CASE). In the late 80’s and early 90’s, IBM’s Application Development Cycle (AD/Cycle) framework and DB2 repository (RM/MVS) were based on the ER model. Other vendors’ repository systems such as Digital’s CDD+ were also based on the ER model. Prof. Chen has made significant impact on the CASE industry by his research work and by his lecturing around the world on structured system development methodologies. Most of the major CASE tools including Computer Associates’ ERWIN, Oracle’s Designer/2000, and Sybase’s PowerDesigner (and even a general drawing tool like Microsoft’s VISIO) are influenced by the ER model.
In this podcast, Brian Beckman interviews Dr. Chen along with Jose Blakeley, Software Architect, SQL Server, and Britt Johnston, Director of Program Management, Data Programmability
The podcast is 51 minutes and 24 seconds
Visit Channel 9 to download the Podcast
Monday, February 19, 2007
Microsoft Releases SQL Server 2005 Service Pack 2
Microsoft Corp. today released Microsoft® SQL Server™ 2005 SP2, an update to its award-winning data management and analysis platform. Customers can now take advantage of enhancements in the familiar and easy-to-use Windows Vista™ operating system and 2007 Microsoft Office system to easily connect and integrate with the power, security and reliability of SQL Server 2005. In addition, Microsoft announced that it is expanding virtualization use rights to allow unlimited virtual instances on servers that are fully licensed for SQL Server 2005 Enterprise Edition. For customers who want maximum flexibility in their use of virtualization technology, now or in the future, SQL Server 2005 Enterprise Edition is the ideal choice.
“SP2 delivers customer-driven improvements and new features that align with our data platform vision,” said Ted Kummert, corporate vice president of the Data and Storage Platform Division at Microsoft. “SP2 realizes a step forward in enabling organizations to bring the business intelligence capabilities of SQL Server 2005 directly to end users in the tools they use every day.”
Key enhancements to SQL Server SP2 include the following:
• Data Mining Add-ins for the 2007 Microsoft Office system enable data mining functionality from SQL Server Analysis Services (SSAS) to be used directly within Excel® 2007 and Visio® 2007.
• SQL Server Reporting Services (SSRS) compatibility with Microsoft Office SharePoint® Server 2007 provides integration with the Report Center in SharePoint, enabling the seamless consumption and management of SSRS reports within SharePoint.
• SQL Server Analysis Services improvements for Excel 2007 and Excel Services relate to performance and functionality.
• Data compression (varDecimal) is an important feature for data warehousing scenarios, requiring less disk storage of decimal data and increasing overall performance.
• Manageability enhancements, based on customer feedback, provide management capabilities for database administrators such as improvements in database maintenance plans, enhanced management reports and a new copy database wizard.
• Management reports added to SQL Server Express Edition enable customers to get insights into the performance of their Express Edition and SQL Server Compact Edition databases.
• Interoperability improvements including Oracle support in the Report Builder feature enable customers to use its functionality on top of Oracle data sources. Customers also have access to SQL Server Reporting Services to build reports on top of Hyperion’s Essbase cubes.
Customers can download SQL Server 2005 Service Pack 2 immediately from http://www.microsoft.com/sql/sp2.mspx.
“SP2 delivers customer-driven improvements and new features that align with our data platform vision,” said Ted Kummert, corporate vice president of the Data and Storage Platform Division at Microsoft. “SP2 realizes a step forward in enabling organizations to bring the business intelligence capabilities of SQL Server 2005 directly to end users in the tools they use every day.”
Key enhancements to SQL Server SP2 include the following:
• Data Mining Add-ins for the 2007 Microsoft Office system enable data mining functionality from SQL Server Analysis Services (SSAS) to be used directly within Excel® 2007 and Visio® 2007.
• SQL Server Reporting Services (SSRS) compatibility with Microsoft Office SharePoint® Server 2007 provides integration with the Report Center in SharePoint, enabling the seamless consumption and management of SSRS reports within SharePoint.
• SQL Server Analysis Services improvements for Excel 2007 and Excel Services relate to performance and functionality.
• Data compression (varDecimal) is an important feature for data warehousing scenarios, requiring less disk storage of decimal data and increasing overall performance.
• Manageability enhancements, based on customer feedback, provide management capabilities for database administrators such as improvements in database maintenance plans, enhanced management reports and a new copy database wizard.
• Management reports added to SQL Server Express Edition enable customers to get insights into the performance of their Express Edition and SQL Server Compact Edition databases.
• Interoperability improvements including Oracle support in the Report Builder feature enable customers to use its functionality on top of Oracle data sources. Customers also have access to SQL Server Reporting Services to build reports on top of Hyperion’s Essbase cubes.
Customers can download SQL Server 2005 Service Pack 2 immediately from http://www.microsoft.com/sql/sp2.mspx.
Sunday, February 18, 2007
Chuck Boyce Has Some Joe Celko Videos On YouTube
Chuck Boyce left me a comment on the Celko post yesterday. I decided to make it a separate post since most people who use a RSS reader don't see the comments
Thanks Chuck
Link to Chuck's post: http://chuckboyce.blogspot.com/2007/02/joe-celko-on-youtube.html
Here are some videos of Adam Machanic interviewing Joe Celko at the 2006 PASS Summit
that I just published today to our new
Solid Quality Learning YouTube channel.
Joe Celko on the evil of cursors
Joe Celko discusses the problem with the median puzzle
Joe Celko recalls an old-school pre CASE statement trick
Thanks Chuck
Link to Chuck's post: http://chuckboyce.blogspot.com/2007/02/joe-celko-on-youtube.html
Man sues IBM over firing, says he's an Internet addict
I won't comment on the Britney madness but here is another gem:
Right, what's next? I know, I will sue my neighbour because his house is bigger and now I am suffering from Napoleon syndrome because of that.
Here is the link to the CNN story: http://www.cnn.com/2007/LAW/02/18/chat.room.lawsuit.ap/index.html
A man who was fired by IBM for visiting an adult chat room at work is suing the company for $5 million, claiming he is an Internet addict who deserves treatment and sympathy rather than dismissal.
James Pacenza, 58, of Montgomery, says he visits chat rooms to treat traumatic stress incurred in 1969 when he saw his best friend killed during an Army patrol in Vietnam.
In papers filed in federal court in White Plains, Pacenza said the stress caused him to become "a sex addict, and with the development of the Internet, an Internet addict." He claimed protection under the American with Disabilities Act.
Right, what's next? I know, I will sue my neighbour because his house is bigger and now I am suffering from Napoleon syndrome because of that.
Here is the link to the CNN story: http://www.cnn.com/2007/LAW/02/18/chat.room.lawsuit.ap/index.html
Clerks II: Starwars vs Lord of the Ring Clip On YouTube
All right look, there's only one return, okay, and it ain't "of the King," it's "of the Jedi."
I own the extended version of the LOTR trilogy and also all the Star Wars movies. To me this is a useless debate since the LOTR trilogy is much better than the original Star Wars trilogy (Episodes 4-6). The YouTube clip is rated R so don’t say I didn’t warn you. And make sure you read the comments
Here are two of them
...Depth and coherence of a cartoon? At least Mr. Lucas has signed off on allowing people to expand the Star Wars universe. Millions of inhabited planets. Thousands of sentient beings. LIGHTSABERS. How's a ring gonna stop a blade of coherent light through the chest?
Aww...made the LOTR fans a little angry did we? So sorry we made your precious move feel inferior, but they were honestly drawn out way longer then they should have been and, wow, Star Wars was in NO WAY based off LOTR, that's just a silly assumption...stop complaining about it, it's not it matters to anyone, honestly.
Here is the link to the precioussss clip
Wednesday, February 14, 2007
How Is SQL Server Books On Line Created?
Buck Woody one of the Technical Writers in the SQL Product Group has started a blog about documentation. His first post SQL Server Books Online explains how Books On Line is created. Did you know that Books Online has over 58,000 pages of content?
The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools
Mark Russinovich the guy who revealed to the world that Sony CDs had that nasty rootkit installed has made available a single suite of tools for download. These tools are a must have for every single windows developer.
The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools. This file contains the individual troubleshooting tools and help files. It does not contain non-troubleshooting tools like the BSOD Screen Saver or NotMyFault.
Download it here
Here is what is included:
• AccessChk
v2.0 (11/1/2006)
This tool shows you the accesses the user or group you specify has to files, Registry keys or Windows services.
• AccessEnum
v1.32 (11/1/2006)
This simple yet powerful security tool shows you who has what access to directories, files and Registry keys on your systems. Use it to find holes in your permissions.
• AdRestore
v1.1 (11/1/2006)
Undelete Server 2003 Active Directory objects
• Autologon
v2.10 (11/1/2006)
Bypass password screen during logon.
• Autoruns
v8.61 (1/22/2007)
See what programs are configured to startup automatically when your system boots and you login. Autoruns also shows you the full list of Registry and file locations where applications can configure auto-start settings.
• BgInfo
v4.0 (11/1/2006)
This fully-configurable program automatically generates desktop backgrounds that include important information about the system including IP addresses, computer name, network adapters, and more.
• BlueScreen
v3.2 (11/1/2006)
This screen saver not only accurately simulates Blue Screens, but simulated reboots as well (complete with CHKDSK), and works on Windows NT 4, Windows 2000, Windows XP, Server 2003 and Windows 9x.
[EDIT]
BlueScreen is NOT part of the suite, you can download it here
[/EDIT]
• CacheSet
v1.0 (11/1/2006)
CacheSet is a program that allows you to control the Cache Manager's working set size using functions provided by NT. It's compatible with all versions of NT and full source code is provided.
• ClockRes
v1.0 (11/1/2006)
View the resolution of the system clock, which is also the maximum timer resolution
• Contig
v1.53 (11/1/2006)
Wish you could quickly defragment your frequently used files? Use Contig to optimize individual files, or to create new files that are contiguous.
• Ctrl2cap
v2.0 (11/1/2006)
This is a kernel-mode driver that demonstrates keyboard input filtering just above the keyboard class driver in order to turn caps-locks into control keys. Filtering at this level allows conversion and hiding of keys before NT even "sees" them. Full source is included. Ctrl2cap also shows how to use NtDisplayString() to print messages to the initialization blue-screen.
• DebugView
v4.64 (1/8/2007)
Another first from Sysinternals: This program intercepts calls made to DbgPrint by device drivers and OutputDebugString made by Win32 programs. It allows for viewing and recording of debug session output on your local machine or across the Internet without an active debugger.
• DiskExt
v1.0 (11/1/2006)
Display volume disk-mappings
• DiskView
v2.21 (11/1/2006)
Graphical disk sector utility
• Diskmon
v2.01 (11/1/2006)
This utility captures all hard disk activity or acts like a software disk activity light in your system tray.
• Du
v1.31 (11/1/2006)
View disk usage by directory
• EFSDump
v1.02 (11/1/2006)
View information for encrypted files
• Filemon
v7.04 (11/1/2006)
This monitoring tool lets you see all file system activity in real-time.
• Handle
v3.20 (11/1/2006)
This handy command-line utility will show you what files are open by which processes, and much more.
• Hex2dec
v1.0 (11/1/2006)
Convert hex numbers to decimal and vice versa.
• Junction
v1.04 (11/1/2006)
Create Win2K NTFS symbolic links
• LDMDump
v1.02 (11/1/2006)
Dump the contents of the Logical Disk Manager's on-disk database, which describes the partitioning of Windows 2000 Dynamic disks.
• ListDLLs
v2.25 (11/1/2006)
List all the DLLs that are currently loaded, including where they are loaded and their version numbers. Version 2.0 prints the full path names of loaded modules.
• LiveKd
v3.0 (11/1/2006)
Use Microsoft kernel debuggers to examine a live system.
• LoadOrder
v1.0 (11/1/2006)
See the order in which devices are loaded on your WinNT/2K system
• MoveFile
v1.0 (11/1/2006)
Allows you to schedule move and delete commands for the next reboot.
• LogonSessions
v1.1 (11/1/2006)
List the active logon sessions on a system.
• NewSID
v4.10 (11/1/2006)
Learn about the computer SID problem everybody has been talking about and get a free computer SID changer, NewSID, complete with full source code.
• NTFSInfo
v1.0 (11/1/2006)
Use NTFSInfo to see detailed information about NTFS volumes, including the size and location of the Master File Table (MFT) and MFT-zone, as well as the sizes of the NTFS meta-data files.
• PageDefrag
v2.32 (11/1/2006)
Defragment your paging files and Registry hives!
• PendMoves
v1.1 (11/1/2006)
Enumerate the list of file rename and delete commands that will be executed the next boot
• Portmon
v3.02 (11/1/2006)
Monitor serial and parallel port activity with this advanced monitoring tool. It knows about all standard serial and parallel IOCTLs and even shows you a portion of the data being sent and received. Version 3.x has powerful new UI enhancements and advanced filtering capabilities.
• Process Explorer
v10.21 (11/1/2006)
Find out what files, registry keys and other objects processes have open, which DLLs they have loaded, and more. This uniquely powerful utility will even show you who owns each process.
• Process Monitor
v1.01 (11/9/2006)
Monitor file system, Registry, process, thread and DLL activity in real-time.
• ProcFeatures
v1.10 (11/1/2006)
This applet reports processor and Windows support for Physical Address Extensions and No Execute buffer overflow protection.
• PsExec
v1.80 (2/12/2007)
Execute processes with limited-user rights.
• PsFile
v1.02 (12/4/2006)
See what files are opened remotely.
• PsGetSid
v1.43 (12/4/2006)
Displays the SID of a computer or a user.
• PsInfo
v1.74 (12/4/2006)
Obtain information about a system.
• PsKill
v1.12 (12/4/2006)
Terminate local or remote processes.
• PsList
v1.28 (12/4/2006)
Show information about processes and threads.
• PsLoggedOn
v1.33 (12/4/2006)
Show users logged on to a system
• PsLogList
v2.64 (12/4/2006)
Dump event log records.
• PsPasswd
v1.22 (12/4/2006)
Changes account passwords.
• PsService
v2.21 (12/4/2006)
View and control services.
• PsShutdown
v2.52 (12/4/2006)
Shuts down and optionally reboots a computer.
• PsSuspend
v1.06 (12/4/2006)
Suspend and resume processes.
• PsTools
v2.43 (2/12/2007)
The PsTools suite includes command-line utilities for listing the processes running on local or remote computers, running processes remotely, rebooting computers, dumping event logs, and more.
• RegDelNull
v1.10 (11/1/2006)
Scan for and delete Registry keys that contain embedded null-characters that are otherwise undeleteable by standard Registry-editing tools.
• RegHide
v1.0 (11/1/2006)
Creates a key called "HKEY_LOCAL_MACHINE\Software\Sysinternals\Can't touch me!\0" using the Native API, and inside this key it creates a value.
• Regjump
v1.01 (11/1/2006)
Jump to the registry path you specify in Regedit.
• Regmon
v7.04 (11/1/2006)
This monitoring tool lets you see all Registry activity in real-time.
• RootkitRevealer
v1.71 (11/1/2006)
Scan your system for rootkit-based malware
• SDelete
v1.51 (11/1/2006)
Securely overwrite your sensitive files and cleanse your free space of previously deleted files using this DoD-compliant secure delete program. Complete source code is included.
• ShareEnum
v1.6 (11/1/2006)
Scan file shares on your network and view their security settings to close security holes.
• Sigcheck
v1.30 (11/1/2006)
Dump file version information and verify that images on your system are digitally signed.
• Streams
v1.53 (11/1/2006)
Reveal NTFS alternate streams
• Strings
v2.30 (11/1/2006)
Search for ANSI and UNICODE strings in binaryimages.
• Sync
v2.0 (11/1/2006)
Flush cached data to disk
• TCPView
v2.40 (11/1/2006)
Active socket command-line viewer.
• VolumeId
v2.0 (11/1/2006)
Set Volume ID of FAT or NTFS drives
• Whois
v1.01 (11/1/2006)
See who owns an Internet address.
• Winobj
v2.15 (11/1/2006)
The ultimate Object Manager namespace viewer is here.
• ZoomIt
v1.21 (1/19/2007)
Presentation utility for zooming and drawing on the screen.
The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools. This file contains the individual troubleshooting tools and help files. It does not contain non-troubleshooting tools like the BSOD Screen Saver or NotMyFault.
Download it here
Here is what is included:
• AccessChk
v2.0 (11/1/2006)
This tool shows you the accesses the user or group you specify has to files, Registry keys or Windows services.
• AccessEnum
v1.32 (11/1/2006)
This simple yet powerful security tool shows you who has what access to directories, files and Registry keys on your systems. Use it to find holes in your permissions.
• AdRestore
v1.1 (11/1/2006)
Undelete Server 2003 Active Directory objects
• Autologon
v2.10 (11/1/2006)
Bypass password screen during logon.
• Autoruns
v8.61 (1/22/2007)
See what programs are configured to startup automatically when your system boots and you login. Autoruns also shows you the full list of Registry and file locations where applications can configure auto-start settings.
• BgInfo
v4.0 (11/1/2006)
This fully-configurable program automatically generates desktop backgrounds that include important information about the system including IP addresses, computer name, network adapters, and more.
• BlueScreen
v3.2 (11/1/2006)
This screen saver not only accurately simulates Blue Screens, but simulated reboots as well (complete with CHKDSK), and works on Windows NT 4, Windows 2000, Windows XP, Server 2003 and Windows 9x.
[EDIT]
BlueScreen is NOT part of the suite, you can download it here
[/EDIT]
• CacheSet
v1.0 (11/1/2006)
CacheSet is a program that allows you to control the Cache Manager's working set size using functions provided by NT. It's compatible with all versions of NT and full source code is provided.
• ClockRes
v1.0 (11/1/2006)
View the resolution of the system clock, which is also the maximum timer resolution
• Contig
v1.53 (11/1/2006)
Wish you could quickly defragment your frequently used files? Use Contig to optimize individual files, or to create new files that are contiguous.
• Ctrl2cap
v2.0 (11/1/2006)
This is a kernel-mode driver that demonstrates keyboard input filtering just above the keyboard class driver in order to turn caps-locks into control keys. Filtering at this level allows conversion and hiding of keys before NT even "sees" them. Full source is included. Ctrl2cap also shows how to use NtDisplayString() to print messages to the initialization blue-screen.
• DebugView
v4.64 (1/8/2007)
Another first from Sysinternals: This program intercepts calls made to DbgPrint by device drivers and OutputDebugString made by Win32 programs. It allows for viewing and recording of debug session output on your local machine or across the Internet without an active debugger.
• DiskExt
v1.0 (11/1/2006)
Display volume disk-mappings
• DiskView
v2.21 (11/1/2006)
Graphical disk sector utility
• Diskmon
v2.01 (11/1/2006)
This utility captures all hard disk activity or acts like a software disk activity light in your system tray.
• Du
v1.31 (11/1/2006)
View disk usage by directory
• EFSDump
v1.02 (11/1/2006)
View information for encrypted files
• Filemon
v7.04 (11/1/2006)
This monitoring tool lets you see all file system activity in real-time.
• Handle
v3.20 (11/1/2006)
This handy command-line utility will show you what files are open by which processes, and much more.
• Hex2dec
v1.0 (11/1/2006)
Convert hex numbers to decimal and vice versa.
• Junction
v1.04 (11/1/2006)
Create Win2K NTFS symbolic links
• LDMDump
v1.02 (11/1/2006)
Dump the contents of the Logical Disk Manager's on-disk database, which describes the partitioning of Windows 2000 Dynamic disks.
• ListDLLs
v2.25 (11/1/2006)
List all the DLLs that are currently loaded, including where they are loaded and their version numbers. Version 2.0 prints the full path names of loaded modules.
• LiveKd
v3.0 (11/1/2006)
Use Microsoft kernel debuggers to examine a live system.
• LoadOrder
v1.0 (11/1/2006)
See the order in which devices are loaded on your WinNT/2K system
• MoveFile
v1.0 (11/1/2006)
Allows you to schedule move and delete commands for the next reboot.
• LogonSessions
v1.1 (11/1/2006)
List the active logon sessions on a system.
• NewSID
v4.10 (11/1/2006)
Learn about the computer SID problem everybody has been talking about and get a free computer SID changer, NewSID, complete with full source code.
• NTFSInfo
v1.0 (11/1/2006)
Use NTFSInfo to see detailed information about NTFS volumes, including the size and location of the Master File Table (MFT) and MFT-zone, as well as the sizes of the NTFS meta-data files.
• PageDefrag
v2.32 (11/1/2006)
Defragment your paging files and Registry hives!
• PendMoves
v1.1 (11/1/2006)
Enumerate the list of file rename and delete commands that will be executed the next boot
• Portmon
v3.02 (11/1/2006)
Monitor serial and parallel port activity with this advanced monitoring tool. It knows about all standard serial and parallel IOCTLs and even shows you a portion of the data being sent and received. Version 3.x has powerful new UI enhancements and advanced filtering capabilities.
• Process Explorer
v10.21 (11/1/2006)
Find out what files, registry keys and other objects processes have open, which DLLs they have loaded, and more. This uniquely powerful utility will even show you who owns each process.
• Process Monitor
v1.01 (11/9/2006)
Monitor file system, Registry, process, thread and DLL activity in real-time.
• ProcFeatures
v1.10 (11/1/2006)
This applet reports processor and Windows support for Physical Address Extensions and No Execute buffer overflow protection.
• PsExec
v1.80 (2/12/2007)
Execute processes with limited-user rights.
• PsFile
v1.02 (12/4/2006)
See what files are opened remotely.
• PsGetSid
v1.43 (12/4/2006)
Displays the SID of a computer or a user.
• PsInfo
v1.74 (12/4/2006)
Obtain information about a system.
• PsKill
v1.12 (12/4/2006)
Terminate local or remote processes.
• PsList
v1.28 (12/4/2006)
Show information about processes and threads.
• PsLoggedOn
v1.33 (12/4/2006)
Show users logged on to a system
• PsLogList
v2.64 (12/4/2006)
Dump event log records.
• PsPasswd
v1.22 (12/4/2006)
Changes account passwords.
• PsService
v2.21 (12/4/2006)
View and control services.
• PsShutdown
v2.52 (12/4/2006)
Shuts down and optionally reboots a computer.
• PsSuspend
v1.06 (12/4/2006)
Suspend and resume processes.
• PsTools
v2.43 (2/12/2007)
The PsTools suite includes command-line utilities for listing the processes running on local or remote computers, running processes remotely, rebooting computers, dumping event logs, and more.
• RegDelNull
v1.10 (11/1/2006)
Scan for and delete Registry keys that contain embedded null-characters that are otherwise undeleteable by standard Registry-editing tools.
• RegHide
v1.0 (11/1/2006)
Creates a key called "HKEY_LOCAL_MACHINE\Software\Sysinternals\Can't touch me!\0" using the Native API, and inside this key it creates a value.
• Regjump
v1.01 (11/1/2006)
Jump to the registry path you specify in Regedit.
• Regmon
v7.04 (11/1/2006)
This monitoring tool lets you see all Registry activity in real-time.
• RootkitRevealer
v1.71 (11/1/2006)
Scan your system for rootkit-based malware
• SDelete
v1.51 (11/1/2006)
Securely overwrite your sensitive files and cleanse your free space of previously deleted files using this DoD-compliant secure delete program. Complete source code is included.
• ShareEnum
v1.6 (11/1/2006)
Scan file shares on your network and view their security settings to close security holes.
• Sigcheck
v1.30 (11/1/2006)
Dump file version information and verify that images on your system are digitally signed.
• Streams
v1.53 (11/1/2006)
Reveal NTFS alternate streams
• Strings
v2.30 (11/1/2006)
Search for ANSI and UNICODE strings in binaryimages.
• Sync
v2.0 (11/1/2006)
Flush cached data to disk
• TCPView
v2.40 (11/1/2006)
Active socket command-line viewer.
• VolumeId
v2.0 (11/1/2006)
Set Volume ID of FAT or NTFS drives
• Whois
v1.01 (11/1/2006)
See who owns an Internet address.
• Winobj
v2.15 (11/1/2006)
The ultimate Object Manager namespace viewer is here.
• ZoomIt
v1.21 (1/19/2007)
Presentation utility for zooming and drawing on the screen.
Tuesday, February 13, 2007
Microsoft .NET Micro Framework Is Now Available
Today at Embedded World 2007, Microsoft Corp. announced the availability of the software development kit (SDK) for the Microsoft® .NET Micro Framework. With its ability to work seamlessly with Visual Studio®, the .NET Micro Framework extends the power of Microsoft’s embedded offerings into the realm of smaller, less expensive and more resource-constrained devices.
“The .NET Micro Framework was built from the ground up as a .NET solution for small embedded devices,” said Colin Miller, director of the .NET Micro Framework at Microsoft. “It brings the reliability and efficiency of the .NET environment to a new set of applications such as home automation systems, industrial sensors, retail displays and healthcare monitors. Development on this platform works seamlessly with the same tools that are used throughout the Microsoft family of platforms. This decreases the distinction between embedded application development and other application development tasks and helps reduce the cost and risks of these projects.”
“The .NET Micro Framework is a proven platform that opens up a new area of embedded development and adds to the momentum of Microsoft embedded technologies,” said Pieter Knook, senior vice president of the Mobile and Embedded Devices Division at Microsoft.
The .NET Micro Framework SDK enables developers to take full advantage of the C# development language and the rich development and debugging experience that Visual Studio provides. In addition, the SDK offers user-extensible hardware emulation and seamless, graphical debugging of emulated and real hardware to deliver robust solutions in less time than ever before.
The .NET Micro Framework SDK not only works seamlessly with Visual Studio and offers an extensible emulator, but is also supported by a number of hardware platforms based on the ARM7 and ARM9 processor cores. The framework also enables device developers to connect these hardware platforms to virtually any peripheral hardware through industry-standard communication connections and custom-managed drivers.
Those interested in receiving a copy of the SDK for the .NET Micro Framework can visit http://msdn.microsoft.com/embedded/netmf. A minimum of 256 KB of RAM and 512 KB of flash ROM is required for development and deployment.
Partners Unveil Support and Offerings for .NET Micro Framework
Also at Embedded World, Digi International Inc. revealed plans for a preview release of the Digi Connect ME Development Kit for Microsoft .NET Micro Framework. The Digi Connect ME includes support for Ethernet networking, a serial port and general purpose input/output (GPIO) signals. It is the first solution available for .NET Micro Framework to support Ethernet networking. The kit, priced at $299 (U.S.), is available now from Digi’s online store (http://www.digistoreonline.com/gsastore.asp) and through its global network of distribution partners.
EmbeddedFusion, which delivers integrated hardware and software core solutions for embedded systems developers, announced the Meridian CPU, which is a core CPU module that incorporates a Freescale i.MXS processor, RAM, Flash and the .NET Micro Framework. To further assist developers in learning how the .NET Micro Framework is applicable in various embedded scenarios, EmbeddedFusion also created the Tahoe development platform, which enables experimentation and exploration of the .NET Micro Framework right out of the box.
Freescale also introduced a development kit for the .NET Micro Framework to allow customers to deliver differentiated solutions in the marketplace with ARM9 performance at very low power.
“Our expectations for .NET Micro Framework are high, so we continue to add features to enable OEMs, ODMs and others to create a new class of smaller, cost-efficient devices, or add Windows SideShow™ connected wirelessly to existing consumer devices,” said Brad Hale, manager of product management for Freescale’s multimedia applications division.
In addition, Rhode Consulting, a specialist in Microsoft Windows Embedded technologies, announced the availability of the FlexiDis Evaluation Kit with the .NET Micro Framework installed. The FlexiDis platform uses Atmel ARM7 and ARM9 processor cores with speeds of up to 180 MHz. The combination of these speeds, up to 16 MB of flash and SDRAM memory, and a 2.2-inch QVGA display makes the FlexiDis display a component of choice for various kinds of industrial applications in which an embedded HMI or visualization solution is required.
System Requirements for Developing on the .NET Micro Framework:
• Microsoft Windows® XP, Microsoft Windows Vista or Microsoft Windows Server® 2003
• Microsoft Visual Studio 2005 Standard Edition or greater
About .NET Micro Framework
The .NET Micro Framework grew out of the Smart Personal Objects Technology (SPOT) initiative at Microsoft. This framework is a natural extension of Microsoft’s offerings for creating embedded systems and provides an easy-to-use solution for this type of development. Though it is used on very small devices, the Microsoft .NET Micro Framework provides a managed code environment that brings a strong degree of efficiency and reliability to the realm of embedded software development. More information can be found at http://msdn.microsoft.com/embedded/netmf
“The .NET Micro Framework was built from the ground up as a .NET solution for small embedded devices,” said Colin Miller, director of the .NET Micro Framework at Microsoft. “It brings the reliability and efficiency of the .NET environment to a new set of applications such as home automation systems, industrial sensors, retail displays and healthcare monitors. Development on this platform works seamlessly with the same tools that are used throughout the Microsoft family of platforms. This decreases the distinction between embedded application development and other application development tasks and helps reduce the cost and risks of these projects.”
“The .NET Micro Framework is a proven platform that opens up a new area of embedded development and adds to the momentum of Microsoft embedded technologies,” said Pieter Knook, senior vice president of the Mobile and Embedded Devices Division at Microsoft.
The .NET Micro Framework SDK enables developers to take full advantage of the C# development language and the rich development and debugging experience that Visual Studio provides. In addition, the SDK offers user-extensible hardware emulation and seamless, graphical debugging of emulated and real hardware to deliver robust solutions in less time than ever before.
The .NET Micro Framework SDK not only works seamlessly with Visual Studio and offers an extensible emulator, but is also supported by a number of hardware platforms based on the ARM7 and ARM9 processor cores. The framework also enables device developers to connect these hardware platforms to virtually any peripheral hardware through industry-standard communication connections and custom-managed drivers.
Those interested in receiving a copy of the SDK for the .NET Micro Framework can visit http://msdn.microsoft.com/embedded/netmf. A minimum of 256 KB of RAM and 512 KB of flash ROM is required for development and deployment.
Partners Unveil Support and Offerings for .NET Micro Framework
Also at Embedded World, Digi International Inc. revealed plans for a preview release of the Digi Connect ME Development Kit for Microsoft .NET Micro Framework. The Digi Connect ME includes support for Ethernet networking, a serial port and general purpose input/output (GPIO) signals. It is the first solution available for .NET Micro Framework to support Ethernet networking. The kit, priced at $299 (U.S.), is available now from Digi’s online store (http://www.digistoreonline.com/gsastore.asp) and through its global network of distribution partners.
EmbeddedFusion, which delivers integrated hardware and software core solutions for embedded systems developers, announced the Meridian CPU, which is a core CPU module that incorporates a Freescale i.MXS processor, RAM, Flash and the .NET Micro Framework. To further assist developers in learning how the .NET Micro Framework is applicable in various embedded scenarios, EmbeddedFusion also created the Tahoe development platform, which enables experimentation and exploration of the .NET Micro Framework right out of the box.
Freescale also introduced a development kit for the .NET Micro Framework to allow customers to deliver differentiated solutions in the marketplace with ARM9 performance at very low power.
“Our expectations for .NET Micro Framework are high, so we continue to add features to enable OEMs, ODMs and others to create a new class of smaller, cost-efficient devices, or add Windows SideShow™ connected wirelessly to existing consumer devices,” said Brad Hale, manager of product management for Freescale’s multimedia applications division.
In addition, Rhode Consulting, a specialist in Microsoft Windows Embedded technologies, announced the availability of the FlexiDis Evaluation Kit with the .NET Micro Framework installed. The FlexiDis platform uses Atmel ARM7 and ARM9 processor cores with speeds of up to 180 MHz. The combination of these speeds, up to 16 MB of flash and SDRAM memory, and a 2.2-inch QVGA display makes the FlexiDis display a component of choice for various kinds of industrial applications in which an embedded HMI or visualization solution is required.
System Requirements for Developing on the .NET Micro Framework:
• Microsoft Windows® XP, Microsoft Windows Vista or Microsoft Windows Server® 2003
• Microsoft Visual Studio 2005 Standard Edition or greater
About .NET Micro Framework
The .NET Micro Framework grew out of the Smart Personal Objects Technology (SPOT) initiative at Microsoft. This framework is a natural extension of Microsoft’s offerings for creating embedded systems and provides an easy-to-use solution for this type of development. Though it is used on very small devices, the Microsoft .NET Micro Framework provides a managed code environment that brings a strong degree of efficiency and reliability to the realm of embedded software development. More information can be found at http://msdn.microsoft.com/embedded/netmf
Sunday, February 11, 2007
Ten SQL Server Functions That You Hardly Use But Should
Below are 10 SQL Server functions that are hardly used but should be used a lot more
I will go in more detail later on but here is a list of the ten functions that I am talking about
I also cross posted this here: http://dotnetsamplechapters.blogspot.com/2007/09/ten-sql-server-functions-that-you.html
BINARY_CHECKSUM
SIGN
COLUMNPROPERTY
DATALENGTH
ASCII, UNICODE
NULLIF
PARSENAME
STUFF
REVERSE
GETUTCDATE
BINARY_CHECKSUM
BINARY_CHECKSUM is handy if you want to check for data differences between 2 rows of data
In order to see what rows are in table 1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use BINARY_CHECKSUM. You have to run this example o SQL Server 2000 to see it work, you can ofcourse use any tables just modify the queries
Let’s get started…
--let's copy over 20 rows to a table named authors2
SELECT TOP 20 * INTO tempdb..authors2
FROM pubs..authors
--update 5 records by appending X to the au_fname
SET ROWCOUNT 5
UPDATE tempdb..authors2
SET au_fname =au_fname +'X'
--Set rowcount back to 0
SET ROWCOUNT 0
--let's insert a row that doesn't exist in pubs
INSERT INTO tempdb..authors2
SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
FROM tempdb..authors2
WHERE au_id ='172-32-1176'
--*** The BIG SELECT QUERY --***
--Not in Pubs
SELECT 'Does Not Exist On Production',t2.au_id
FROM pubs..authors t1
RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
UNION ALL
--Not in Temp
SELECT 'Does Not Exist In Staging',t1.au_id
FROM pubs..authors t1
LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t2.au_id IS NULL
UNION ALL
--Data Mismatch
SELECT 'Data Mismatch', t1.au_id
FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
WHERE CheckSum1 <> CheckSum2
--Clean up
DROP TABLE tempdb..authors2
GO
SIGN
Sometimes you are asked by the front-end/middle-tier developers to return a rowcount as well with the result set. However the developers want you to return 1 if there are rows and 0 if there are none. How do you do such a thing?
Well I am going to show you two ways. the first way is by using CASE and @@ROWCOUNT, the second way is by using the SIGN function
For CASE we will do this
RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
So that's pretty simple, if @@ROWCOUNT is greater than 0 return 1 for everything else return 0
Using the SIGN function is even easier, all you have to do is this
RETURN SIGN(@@ROWCOUNT)
That's all, SIGN Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. In this case -1 is not possible but the other two values are
So let's see this in action
USE pubs
GO
--Case Proc
CREATE PROCEDURE TestReturnValues
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id
RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
GO
--Sign Proc
CREATE PROCEDURE TestReturnValues2
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id
RETURN SIGN(@@ROWCOUNT)
GO
--Case Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues
SELECT @Rowcount
GO
--Case Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues 'ABC'
SELECT @Rowcount
GO
--Sign Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2
SELECT @Rowcount
GO
--Sign Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2 'ABC'
SELECT @Rowcount
GO
--Help the environment by recycling ;-)
DROP PROCEDURE TestReturnValues2,TestReturnValues
GO
COLUMNPROPERTY
COLUMNPROPERTY is handy if you need to find scale, precision, if it is an identity column and more. I have listed all of them below
CREATE TABLE blah (ID DECIMAL(5,2) not null DEFAULT 99)
INSERT blah DEFAULT VALUES
SELECT * FROM blah
SELECT COLUMNPROPERTY( OBJECT_ID('blah'),'ID','AllowsNull') AS AllowsNull,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsComputed') AS IsComputed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsCursorType') AS IsCursorType,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsDeterministic') AS IsDeterministic,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsFulltextIndexed') AS IsFulltextIndexed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdentity') AS IsFulltextIndexed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdNotForRepl') AS IsIdNotForRepl,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIndexable') AS IsIndexable,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsOutParam') AS IsOutParam,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsPrecise') AS IsPrecise,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsRowGuidCol') AS IsRowGuidCol,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Precision') AS 'Precision',
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Scale') AS Scale,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','UsesAnsiTrim') AS UsesAnsiTrim
FROM Blah
So what does all that stuff mean?
AllowsNull
Allows null values. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsComputed
The column is a computed column. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsCursorType
The procedure parameter is of type CURSOR. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsDeterministic
The column is deterministic. This property applies only to computed columns and view columns. 1 = TRUE
0 = FALSE
NULL = Invalid input. Not a computed column or view column.
IsFulltextIndexed
The column has been registered for full-text indexing. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsIdentity
The column uses the IDENTITY property. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsIdNotForRepl
The column checks for the IDENTITY_INSERT setting. If IDENTITY NOT FOR REPLICATION is specified, the IDENTITY_INSERT setting is not checked. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsIndexable
The column can be indexed. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsOutParam
The procedure parameter is an output parameter. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsPrecise
The column is precise. This property applies only to deterministic columns. 1 = TRUE
0 = FALSE
NULL = Invalid input. Not a deterministic column
IsRowGuidCol
The column has the uniqueidentifier data type and is defined with the ROWGUIDCOL property. 1 = TRUE
0 = FALSE
NULL = Invalid input
Precision
Precision for the data type of the column or parameter. The precision of the specified column data type
NULL = Invalid input
Scale
Scale for the data type of the column or parameter. The scale
NULL = Invalid input
UsesAnsiTrim
ANSI padding setting was ON when the table was initially created. 1= TRUE
0= FALSE
NULL = Invalid input
DATALENGTH
Okay so you know the LEN function but do you know the DATALENGTH function? There are two major difference between LEN and DATALENGTH.
The first one deals with trailing spaces, execute the following code and you will see that LEN returns 3 while DATALENGTH returns 4
DECLARE @V VARCHAR(50)
SELECT @V ='ABC '
SELECT LEN(@V),DATALENGTH(@V),@V
The second difference deals with unicode character data, as you know unicode uses 2 bytes to store 1 character
Run the following example and you will see that LEN returns 3 while DATALENGTH returns 6
DECLARE @V NVARCHAR(50)
SELECT @V ='ABC'
SELECT LEN(@V),DATALENGTH(@V),@V
If you do DATALENGTH(CONVERT(VARCHAR,@V)) you will get the same as LEN because LEN does a RTRIM and converts to VARCHAR before returning
ASCII, CHAR,UNICODE
ASCII will give you the ascii code for a character so for A you will get 65
CHAR does the reverse of ascii CHAR(65) returns A
UNICODE will give you the unicode value for a character
NCHAR will give you the character for a unicode or ascii value
let's see how this works
SELECT ASCII('A'),CHAR(65),CHAR(ASCII('A')),
UNICODE(N'Λ'),NCHAR(923),NCHAR(UNICODE(N'Λ'))
NULLIF
NULLIF Returns a null value if the two specified expressions are equivalent.
Syntax
NULLIF ( expression , expression )
DECLARE @v VARCHAR(20)
SELECT @v = ' '
SELECT NULLIF(@v,' ')
You can combine NULLIF with COALESCE if you want to test for NULLS and Blanks for example
DECLARE @v VARCHAR(20)
SELECT @v = ' '
SELECT COALESCE(NULLIF(@v,' '),'N/A')
Here is another NULLIF example:
CREATE TABLE Blah (SomeCol VARCHAR(33))
INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')
--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah
--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah
Output for both queries
-----------------------
N/A
N/A
N/A
A
B B
PARSENAME
PARSENAME retrieves parts of string delimited by dots. It is used to split DataBaseServer, DataBaseName, ObjectOwner and ObjectName but you can use it to split IP addresses, names etc
DECLARE @ParseString VARCHAR(100)
SELECT @ParseString = 'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'
SELECT PARSENAME(@ParseString,4),
PARSENAME(@ParseString,3),
PARSENAME(@ParseString,2),
PARSENAME(@ParseString,1)
CREATE TABLE #Test (
SomeField VARCHAR(49))
INSERT INTO #Test
VALUES ('aaa-bbbbb')
INSERT INTO #Test
VALUES ('ppppp-bbbbb')
INSERT INTO #Test
VALUES ('zzzz-xxxxx')
--using PARSENAME
SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
FROM #Test
Another example:
CREATE TABLE BadData (FullName varchar(20) NOT NULL);
INSERT INTO BadData (FullName)
SELECT 'Clinton, Bill' UNION ALL
SELECT 'Johnson, Lyndon, B.' UNION ALL
SELECT 'Bush, George, H.W.';
Split the names into 3 columns
Your output should be this:
LastName FirstName MiddleInitial
Clinton Bill
Johnson Lyndon B.
Bush George H.W.
SELECT FullName,PARSENAME(FullName2,NameLen+1) AS LastName,
PARSENAME(FullName2,NameLen) AS FirstName,
COALESCE(REPLACE(PARSENAME(FullName2,NameLen-1),'~','.'),'') AS MiddleInitial
FROM(
SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
REPLACE(REPLACE(FullName,'.','~'),', ','.') AS FullName2,FullName
FROM BadData) x
STUFF
STUFF is another function that is hardly used, it is useful if you want to replace or add characters inside data
Take a look at the code below. the first STUFF will replace X with 98765, the second STUFF will place 98765 before the X and the third stuff will replace X- with 98765
DECLARE @v VARCHAR(11)
SELECT @v ='-X-'
SELECT STUFF(@v, 2, 1, '98765'),
STUFF(@v, 2, 0, '98765'),
STUFF(@v, 2, 2, '98765')
The STUFF function is very handy if you need to insert dashes in a social security. You can accomplish that by using the function STUFF twice instead of using substring,left and right
DECLARE @v VARCHAR(11)
SELECT @v ='123456789'
SELECT @v,STUFF(STUFF(@v,4,0,'-'),7,0,'-')
REVERSE
REVERSE just reverses the value, for example the code below returns CBA
SELECT REVERSE('ABC')
Reverse is handy if you need to split values, take a look at this example
CREATE TABLE #TestCityStateZip (csz CHAR(49))
INSERT INTO #TestCityStateZip VALUES ('city ,st 12223')
INSERT INTO #TestCityStateZip VALUES ('New York City,NY 10028')
INSERT INTO #TestCityStateZip VALUES ('Princeton , NJ 08536')
INSERT INTO #TestCityStateZip VALUES ('Princeton,NJ 08536 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City , NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City ,NY 10013 ')
SELECT LEFT(csz,CHARINDEX(',',csz)-1)AS City,
LEFT(LTRIM(SUBSTRING(csz,(CHARINDEX(',',csz)+1),4)),2) AS State,
RIGHT(RTRIM(csz),CHARINDEX(' ',REVERSE(RTRIM(csz)))-1) AS Zip
FROM #TestCityStateZip
GETUTCDATE
SELECT GETUTCDATE()
Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.
And that is all, those are the ten functions that you should be using but currently you are not using all of them. Look them up in Books On Line so that you can see some more examples
I will go in more detail later on but here is a list of the ten functions that I am talking about
I also cross posted this here: http://dotnetsamplechapters.blogspot.com/2007/09/ten-sql-server-functions-that-you.html
BINARY_CHECKSUM
SIGN
COLUMNPROPERTY
DATALENGTH
ASCII, UNICODE
NULLIF
PARSENAME
STUFF
REVERSE
GETUTCDATE
BINARY_CHECKSUM
BINARY_CHECKSUM is handy if you want to check for data differences between 2 rows of data
In order to see what rows are in table 1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use BINARY_CHECKSUM. You have to run this example o SQL Server 2000 to see it work, you can ofcourse use any tables just modify the queries
Let’s get started…
--let's copy over 20 rows to a table named authors2
SELECT TOP 20 * INTO tempdb..authors2
FROM pubs..authors
--update 5 records by appending X to the au_fname
SET ROWCOUNT 5
UPDATE tempdb..authors2
SET au_fname =au_fname +'X'
--Set rowcount back to 0
SET ROWCOUNT 0
--let's insert a row that doesn't exist in pubs
INSERT INTO tempdb..authors2
SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
FROM tempdb..authors2
WHERE au_id ='172-32-1176'
--*** The BIG SELECT QUERY --***
--Not in Pubs
SELECT 'Does Not Exist On Production',t2.au_id
FROM pubs..authors t1
RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
UNION ALL
--Not in Temp
SELECT 'Does Not Exist In Staging',t1.au_id
FROM pubs..authors t1
LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t2.au_id IS NULL
UNION ALL
--Data Mismatch
SELECT 'Data Mismatch', t1.au_id
FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
WHERE CheckSum1 <> CheckSum2
--Clean up
DROP TABLE tempdb..authors2
GO
SIGN
Sometimes you are asked by the front-end/middle-tier developers to return a rowcount as well with the result set. However the developers want you to return 1 if there are rows and 0 if there are none. How do you do such a thing?
Well I am going to show you two ways. the first way is by using CASE and @@ROWCOUNT, the second way is by using the SIGN function
For CASE we will do this
RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
So that's pretty simple, if @@ROWCOUNT is greater than 0 return 1 for everything else return 0
Using the SIGN function is even easier, all you have to do is this
RETURN SIGN(@@ROWCOUNT)
That's all, SIGN Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. In this case -1 is not possible but the other two values are
So let's see this in action
USE pubs
GO
--Case Proc
CREATE PROCEDURE TestReturnValues
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id
RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
GO
--Sign Proc
CREATE PROCEDURE TestReturnValues2
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id
RETURN SIGN(@@ROWCOUNT)
GO
--Case Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues
SELECT @Rowcount
GO
--Case Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues 'ABC'
SELECT @Rowcount
GO
--Sign Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2
SELECT @Rowcount
GO
--Sign Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2 'ABC'
SELECT @Rowcount
GO
--Help the environment by recycling ;-)
DROP PROCEDURE TestReturnValues2,TestReturnValues
GO
COLUMNPROPERTY
COLUMNPROPERTY is handy if you need to find scale, precision, if it is an identity column and more. I have listed all of them below
CREATE TABLE blah (ID DECIMAL(5,2) not null DEFAULT 99)
INSERT blah DEFAULT VALUES
SELECT * FROM blah
SELECT COLUMNPROPERTY( OBJECT_ID('blah'),'ID','AllowsNull') AS AllowsNull,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsComputed') AS IsComputed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsCursorType') AS IsCursorType,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsDeterministic') AS IsDeterministic,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsFulltextIndexed') AS IsFulltextIndexed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdentity') AS IsFulltextIndexed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdNotForRepl') AS IsIdNotForRepl,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIndexable') AS IsIndexable,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsOutParam') AS IsOutParam,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsPrecise') AS IsPrecise,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsRowGuidCol') AS IsRowGuidCol,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Precision') AS 'Precision',
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Scale') AS Scale,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','UsesAnsiTrim') AS UsesAnsiTrim
FROM Blah
So what does all that stuff mean?
AllowsNull
Allows null values. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsComputed
The column is a computed column. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsCursorType
The procedure parameter is of type CURSOR. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsDeterministic
The column is deterministic. This property applies only to computed columns and view columns. 1 = TRUE
0 = FALSE
NULL = Invalid input. Not a computed column or view column.
IsFulltextIndexed
The column has been registered for full-text indexing. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsIdentity
The column uses the IDENTITY property. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsIdNotForRepl
The column checks for the IDENTITY_INSERT setting. If IDENTITY NOT FOR REPLICATION is specified, the IDENTITY_INSERT setting is not checked. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsIndexable
The column can be indexed. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsOutParam
The procedure parameter is an output parameter. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsPrecise
The column is precise. This property applies only to deterministic columns. 1 = TRUE
0 = FALSE
NULL = Invalid input. Not a deterministic column
IsRowGuidCol
The column has the uniqueidentifier data type and is defined with the ROWGUIDCOL property. 1 = TRUE
0 = FALSE
NULL = Invalid input
Precision
Precision for the data type of the column or parameter. The precision of the specified column data type
NULL = Invalid input
Scale
Scale for the data type of the column or parameter. The scale
NULL = Invalid input
UsesAnsiTrim
ANSI padding setting was ON when the table was initially created. 1= TRUE
0= FALSE
NULL = Invalid input
DATALENGTH
Okay so you know the LEN function but do you know the DATALENGTH function? There are two major difference between LEN and DATALENGTH.
The first one deals with trailing spaces, execute the following code and you will see that LEN returns 3 while DATALENGTH returns 4
DECLARE @V VARCHAR(50)
SELECT @V ='ABC '
SELECT LEN(@V),DATALENGTH(@V),@V
The second difference deals with unicode character data, as you know unicode uses 2 bytes to store 1 character
Run the following example and you will see that LEN returns 3 while DATALENGTH returns 6
DECLARE @V NVARCHAR(50)
SELECT @V ='ABC'
SELECT LEN(@V),DATALENGTH(@V),@V
If you do DATALENGTH(CONVERT(VARCHAR,@V)) you will get the same as LEN because LEN does a RTRIM and converts to VARCHAR before returning
ASCII, CHAR,UNICODE
ASCII will give you the ascii code for a character so for A you will get 65
CHAR does the reverse of ascii CHAR(65) returns A
UNICODE will give you the unicode value for a character
NCHAR will give you the character for a unicode or ascii value
let's see how this works
SELECT ASCII('A'),CHAR(65),CHAR(ASCII('A')),
UNICODE(N'Λ'),NCHAR(923),NCHAR(UNICODE(N'Λ'))
NULLIF
NULLIF Returns a null value if the two specified expressions are equivalent.
Syntax
NULLIF ( expression , expression )
DECLARE @v VARCHAR(20)
SELECT @v = ' '
SELECT NULLIF(@v,' ')
You can combine NULLIF with COALESCE if you want to test for NULLS and Blanks for example
DECLARE @v VARCHAR(20)
SELECT @v = ' '
SELECT COALESCE(NULLIF(@v,' '),'N/A')
Here is another NULLIF example:
CREATE TABLE Blah (SomeCol VARCHAR(33))
INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')
--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah
--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah
Output for both queries
-----------------------
N/A
N/A
N/A
A
B B
PARSENAME
PARSENAME retrieves parts of string delimited by dots. It is used to split DataBaseServer, DataBaseName, ObjectOwner and ObjectName but you can use it to split IP addresses, names etc
DECLARE @ParseString VARCHAR(100)
SELECT @ParseString = 'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'
SELECT PARSENAME(@ParseString,4),
PARSENAME(@ParseString,3),
PARSENAME(@ParseString,2),
PARSENAME(@ParseString,1)
CREATE TABLE #Test (
SomeField VARCHAR(49))
INSERT INTO #Test
VALUES ('aaa-bbbbb')
INSERT INTO #Test
VALUES ('ppppp-bbbbb')
INSERT INTO #Test
VALUES ('zzzz-xxxxx')
--using PARSENAME
SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
FROM #Test
Another example:
CREATE TABLE BadData (FullName varchar(20) NOT NULL);
INSERT INTO BadData (FullName)
SELECT 'Clinton, Bill' UNION ALL
SELECT 'Johnson, Lyndon, B.' UNION ALL
SELECT 'Bush, George, H.W.';
Split the names into 3 columns
Your output should be this:
LastName FirstName MiddleInitial
Clinton Bill
Johnson Lyndon B.
Bush George H.W.
SELECT FullName,PARSENAME(FullName2,NameLen+1) AS LastName,
PARSENAME(FullName2,NameLen) AS FirstName,
COALESCE(REPLACE(PARSENAME(FullName2,NameLen-1),'~','.'),'') AS MiddleInitial
FROM(
SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
REPLACE(REPLACE(FullName,'.','~'),', ','.') AS FullName2,FullName
FROM BadData) x
STUFF
STUFF is another function that is hardly used, it is useful if you want to replace or add characters inside data
Take a look at the code below. the first STUFF will replace X with 98765, the second STUFF will place 98765 before the X and the third stuff will replace X- with 98765
DECLARE @v VARCHAR(11)
SELECT @v ='-X-'
SELECT STUFF(@v, 2, 1, '98765'),
STUFF(@v, 2, 0, '98765'),
STUFF(@v, 2, 2, '98765')
The STUFF function is very handy if you need to insert dashes in a social security. You can accomplish that by using the function STUFF twice instead of using substring,left and right
DECLARE @v VARCHAR(11)
SELECT @v ='123456789'
SELECT @v,STUFF(STUFF(@v,4,0,'-'),7,0,'-')
REVERSE
REVERSE just reverses the value, for example the code below returns CBA
SELECT REVERSE('ABC')
Reverse is handy if you need to split values, take a look at this example
CREATE TABLE #TestCityStateZip (csz CHAR(49))
INSERT INTO #TestCityStateZip VALUES ('city ,st 12223')
INSERT INTO #TestCityStateZip VALUES ('New York City,NY 10028')
INSERT INTO #TestCityStateZip VALUES ('Princeton , NJ 08536')
INSERT INTO #TestCityStateZip VALUES ('Princeton,NJ 08536 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City , NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City ,NY 10013 ')
SELECT LEFT(csz,CHARINDEX(',',csz)-1)AS City,
LEFT(LTRIM(SUBSTRING(csz,(CHARINDEX(',',csz)+1),4)),2) AS State,
RIGHT(RTRIM(csz),CHARINDEX(' ',REVERSE(RTRIM(csz)))-1) AS Zip
FROM #TestCityStateZip
GETUTCDATE
SELECT GETUTCDATE()
Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.
And that is all, those are the ten functions that you should be using but currently you are not using all of them. Look them up in Books On Line so that you can see some more examples
Clippy Is Not Dead, Clippy Is Alive.....On Linux
So we all know that Clippy hasn't made it to Office 2007 but guess what? Clippy is not dead, it's alive! DR. FrankGNUstein has dug up Clippy from the cemetery near Redmond, Clippy was resting (in peace) near MS Bob and now only an empty grave can be found.
Below is a picture of Clippy on Linux, kind of creepy and if you look closely you can still see the scars. Now you know why Seattle had a blackout recently because DR. FrankGNUstein used a tremendous amount of power to resurrect Clippy
Click on the image to see a bigger image. And yes I will post some real SQL later today, it will be about ten hardly used functions in T-SQL (STUFF, NULLIF, PARSENAME, REVERSE, UNICODE and more.....)
And if you want to see more Linux Clippy images and a video as well then visit this link: http://vigor.sourceforge.net/screenshots/
Below is a picture of Clippy on Linux, kind of creepy and if you look closely you can still see the scars. Now you know why Seattle had a blackout recently because DR. FrankGNUstein used a tremendous amount of power to resurrect Clippy
Click on the image to see a bigger image. And yes I will post some real SQL later today, it will be about ten hardly used functions in T-SQL (STUFF, NULLIF, PARSENAME, REVERSE, UNICODE and more.....)
And if you want to see more Linux Clippy images and a video as well then visit this link: http://vigor.sourceforge.net/screenshots/
Saturday, February 10, 2007
Wal-Mart Wine
Wal-Mart announced that, on January 1, 2007, it will begin offering
customers a new discount item - Wal-Mart's own brand of wine.
The world's largest retail chain is teaming up with Ernest & Julio
Gallo Winery of California to produce the spirits at an affordable
price, in the $2-$5 range.
Wine connoisseurs may not be inclined to throw a bottle of Wal-Mart
brand into their shopping carts, but "there is a market for
inexpensive wine," said Kathy Micken, professor of marketing at
University of Arkansas, Bentonville. She said: "But the right name
is important."
Customer surveys were conducted to determine the most attractive
name for the Wal-Mart wine brand. The top surveyed names in order of
popularity were:
10. Chateau Traileur Parc
9. White Trashfindel
8. Big Red Gulp
7. World Championship Riesling
6. NASCARbernet
5. Chef Boyardeaux
4. Peanut Noir
3. I Can't Believe It's Not Vinegar!
2. Grape Expectations
1. Nasti Spumante
The beauty of Wal-Mart wine is that it can be served with either
white meat (Possum) or red meat (Squirrel).
customers a new discount item - Wal-Mart's own brand of wine.
The world's largest retail chain is teaming up with Ernest & Julio
Gallo Winery of California to produce the spirits at an affordable
price, in the $2-$5 range.
Wine connoisseurs may not be inclined to throw a bottle of Wal-Mart
brand into their shopping carts, but "there is a market for
inexpensive wine," said Kathy Micken, professor of marketing at
University of Arkansas, Bentonville. She said: "But the right name
is important."
Customer surveys were conducted to determine the most attractive
name for the Wal-Mart wine brand. The top surveyed names in order of
popularity were:
10. Chateau Traileur Parc
9. White Trashfindel
8. Big Red Gulp
7. World Championship Riesling
6. NASCARbernet
5. Chef Boyardeaux
4. Peanut Noir
3. I Can't Believe It's Not Vinegar!
2. Grape Expectations
1. Nasti Spumante
The beauty of Wal-Mart wine is that it can be served with either
white meat (Possum) or red meat (Squirrel).
ASP.NET:DataFormatString Doesn't Work In A GridView If HtmlEncode Is True
So this took me most of my lunch hour to figure out the other day. If you have a gridview and you want to apply formatting to a column then you have to set HtmlEncode to false, by default HtmlEncode is set to true. Of course I thought initially that my super advanced formatting formula ({0:c}) was wrong. A quick Google search found the answer. So below where you see this part HtmlEncode="False" that is the culprit, initiall this will be HtmlEncode="True"
<Columns><asp:BoundField DataField="OrderCost" DataFormatString="{0:c}" HeaderText="OrderCost" HtmlEncode="False" SortExpression="Currency" /></Columns>
I must say that having been away from front-end programming for 5 years I am kind of glad to be back (back is a strong word, I am doing this mostly during lunch and at home because my plate is full with SQL SERVER tasks). But I still have a lot to learn, yep back to n00b status ;-(
Yesterday I was debugging a web service that I wrote in VS 2005. It's kind of cool to see that that works because after you spend time with the SQL Server debugger you would think that something like that would never work
<Columns><asp:BoundField DataField="OrderCost" DataFormatString="{0:c}" HeaderText="OrderCost" HtmlEncode="False" SortExpression="Currency" /></Columns>
I must say that having been away from front-end programming for 5 years I am kind of glad to be back (back is a strong word, I am doing this mostly during lunch and at home because my plate is full with SQL SERVER tasks). But I still have a lot to learn, yep back to n00b status ;-(
Yesterday I was debugging a web service that I wrote in VS 2005. It's kind of cool to see that that works because after you spend time with the SQL Server debugger you would think that something like that would never work
How to Backup Your Blogger Blog
If you have a blog hosted on Blog*Spot (Blogger) and you've upgraded to the new version, there's an easy way to backup your blog. The Google Operating System blog shows you just the way to do this. The link to the article is below
http://googlesystem.blogspot.com/2007/02/how-to-backup-blogger-blog.html
http://googlesystem.blogspot.com/2007/02/how-to-backup-blogger-blog.html
Friday, February 09, 2007
Use A Combination OF NULLIF and COALESCE TO Display A Custom Value
Let's you inherit a table with a column that can contain blanks, empty strings or NULL values. I am saying inherit because surely you would have a constraint on the column that wouldn't allow those values to begin with right? For all the values that are '',' ' or NULL you want to display 'N/A'.
What is the best way to do this? You can Use CASE and test for the values or you can use COALESCE with NULLIF which is much shorter. A lot of people don't know that you can stack these functions so that it makes your life so much easier.
Let's get started
CREATE TABLE Blah (SomeCol VARCHAR(33))
INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')
--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah
--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah
Output for both queries
-----------------------
N/A
N/A
N/A
A
B B
What is the best way to do this? You can Use CASE and test for the values or you can use COALESCE with NULLIF which is much shorter. A lot of people don't know that you can stack these functions so that it makes your life so much easier.
Let's get started
CREATE TABLE Blah (SomeCol VARCHAR(33))
INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')
--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah
--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah
Output for both queries
-----------------------
N/A
N/A
N/A
A
B B
Tuesday, February 06, 2007
SSIS Connectivity White Papers Available On Microsoft's Site
Bob Beauchemin's SSIS whitepapers are now available on Microsoft's website
Connectivity and SQL Server 2005 Integration Services
http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ConnectivitySSIS.doc
Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/ImplementingScaleOut.doc
Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/InternalsTroubleshootingScaleOut.doc
Enjoy reading them
Connectivity and SQL Server 2005 Integration Services
http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ConnectivitySSIS.doc
Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/ImplementingScaleOut.doc
Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/InternalsTroubleshootingScaleOut.doc
Enjoy reading them
Monday, February 05, 2007
What Happens When You Let a Three Year Old Use Your Computer For 2 Minutes?
What Happens When You Let a Three Year Old Use Your Computer For 2 Minutes?
This (I Spy a bunch of trouble)
Shipping Method: Standard Shipping
Shipping Preference: Group my items into as few shipments as possible
Subtotal of Items: $234.62
Shipping & Handling: $32.70
Promotion Applied: -$19.95
------
Total for this Order: $247.37
Shipping estimate for these items: February 7, 2007
Delivery estimate: February 12, 2007 - February 14, 2007 1 "I Spy A Penguin (Level 1) (I Spy)"
JEAN MARZOLLO; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy Santa Claus (Scholastic Reader, Level 1)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy A Balloon (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy Lightning In The Sky (level 1): I Spy Lightning In The Sky (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy A Dinosaur's Eye (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy A Dinosaur's Eye (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy A Pumpkin (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy A Pumpkin (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy: Year-round Challenger: Year-round Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy A School Bus (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy Little Animals (I Spy)"
Jean Marzollo; Board book; $6.99
Sold by: Amazon.com
1 "I Spy Fun House: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy School Days (I Spy)"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy Fun House: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy Extreme Challenger! A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy Funny Teeth (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy Gold Challenger (I Spy)"
Jean Marzollo; Hardcover; $11.58
Sold by: Amazon.com
1 "I Spy Spooky Night (I Spy)"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy: A Book Of Picture Riddles: A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy Christmas: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy Little Letters (I Spy)"
Jean Marzollo; Board book; $6.99
Sold by: Amazon.com
1 "I Spy Little Letters (I Spy)"
Jean Marzollo; Board book; $6.99
Sold by: Amazon.com
1 "I Spy: Mystery A Book Of Picture Riddles: Mystery A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy: Mystery A Book Of Picture Riddles: Mystery A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy Little Wheels (I Spy)"
Jean Marzollo; Board book; $6.99
Sold by: Amazon.com
1 "I Spy Little Book (I Spy)"
Jean Marzollo; Board book; $6.99
Sold by: Amazon.com
1 "Can You See What I See? Dream Machine"
Walter Wick; Hardcover; $11.58
Sold by: Amazon.com
And of course it already shipped, how come when I place an order it doesn't ship for 2 days.....
This (I Spy a bunch of trouble)
Shipping Method: Standard Shipping
Shipping Preference: Group my items into as few shipments as possible
Subtotal of Items: $234.62
Shipping & Handling: $32.70
Promotion Applied: -$19.95
------
Total for this Order: $247.37
Shipping estimate for these items: February 7, 2007
Delivery estimate: February 12, 2007 - February 14, 2007 1 "I Spy A Penguin (Level 1) (I Spy)"
JEAN MARZOLLO; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy Santa Claus (Scholastic Reader, Level 1)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy A Balloon (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy Lightning In The Sky (level 1): I Spy Lightning In The Sky (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy A Dinosaur's Eye (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy A Dinosaur's Eye (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy A Pumpkin (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy A Pumpkin (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy: Year-round Challenger: Year-round Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy A School Bus (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy Little Animals (I Spy)"
Jean Marzollo; Board book; $6.99
Sold by: Amazon.com
1 "I Spy Fun House: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy School Days (I Spy)"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy Fun House: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy Extreme Challenger! A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy Funny Teeth (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99
Sold by: Amazon.com
1 "I Spy Gold Challenger (I Spy)"
Jean Marzollo; Hardcover; $11.58
Sold by: Amazon.com
1 "I Spy Spooky Night (I Spy)"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy: A Book Of Picture Riddles: A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy Christmas: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16
Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy Little Letters (I Spy)"
Jean Marzollo; Board book; $6.99
Sold by: Amazon.com
1 "I Spy Little Letters (I Spy)"
Jean Marzollo; Board book; $6.99
Sold by: Amazon.com
1 "I Spy: Mystery A Book Of Picture Riddles: Mystery A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy: Mystery A Book Of Picture Riddles: Mystery A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95
Sold by: Amazon.com
1 "I Spy Little Wheels (I Spy)"
Jean Marzollo; Board book; $6.99
Sold by: Amazon.com
1 "I Spy Little Book (I Spy)"
Jean Marzollo; Board book; $6.99
Sold by: Amazon.com
1 "Can You See What I See? Dream Machine"
Walter Wick; Hardcover; $11.58
Sold by: Amazon.com
And of course it already shipped, how come when I place an order it doesn't ship for 2 days.....
More Than 10 Tips To Help Your Virtual PC Performance.
Sahil Malik has posted on his blog 10 Tips To Help Your Virtual PC Performance. If you use Virtual PC a lot to test service pack 2 for SQL Server for example then you want to check these tips out. The URL is below
http://blah.winsmarts.com/2007-2-10_tips_to_help_your_Virtual_PC_performance.aspx
http://blah.winsmarts.com/2007-2-10_tips_to_help_your_Virtual_PC_performance.aspx
Saturday, February 03, 2007
John Carpenter’s The Thing
I brought in John Carpenter’s The Thing
to work only to discover that 80% of my co-workers never heard of this movie. This movie is probably one of the best in its genre. If you like Alien(s) then you will love this movie. The story is about a bunch of researchers on Antarctica, they discover a spaceship and take a dog in (bad idea) I won’t spoil the story but let’s just say that a lot of people die. I watched this movie at least 10 times since its release in 1982. even nowadays the special effects are still amazing.IMDB lists this movie in the top 250 movies of all time with a rating of 8.0 and 29,153 votes. If you have problems picking out a movie at blockbuster with your significant other then consider this a favor because I know what it feels like spending 30 minutes in Blockbuster trying to agree on a movie. I even went home a couple of times without a movie because my wife and I couldn’t agree on a movie. So what are you waiting for it is Saturday afternoon (well it is in Princeton, NJ anyway)? Go to the movie rental store and watch this movie. Let me know what you thought of it after you watch it
If you are in doubt then just check out the comments on IMDB http://www.imdb.com/title/tt0084787/usercomments
And here is the trailer
http://srv12.movie-list.net/bendermac/thing_redux_h640_ml.mov
Thursday, February 01, 2007
Give Me One Good Reason Why You Would Store Documents In The Database?
Why would you ever store PDF, Doc or Excel files in the database? What is the point? I don't see any advantages only disadvantages
Let's say you have 8 webservers and 1 big SQL monster. If you store these documents in the DB and you get hit by these 8 webservers for documents all the time your DB is going to slow down. A much better way is to have the files on the webservers itself, sending 9MB pdf files over the network is just wrong.
Also if you store all these files in the DB your backups will take much longer.
Updating BLOBs is another pain in the neck; UPDATETEXT and WRITETEXT are not my favorite SQL commands.
So here is the question:
Would you store images/documents/spreadsheets in the Database and why?
Let's say you have 8 webservers and 1 big SQL monster. If you store these documents in the DB and you get hit by these 8 webservers for documents all the time your DB is going to slow down. A much better way is to have the files on the webservers itself, sending 9MB pdf files over the network is just wrong.
Also if you store all these files in the DB your backups will take much longer.
Updating BLOBs is another pain in the neck; UPDATETEXT and WRITETEXT are not my favorite SQL commands.
So here is the question:
Would you store images/documents/spreadsheets in the Database and why?
Tuesday, January 30, 2007
SQL Server 2005 High Availability Podcast On DotNet Rocks
.NET Rocks has made their latest podcast available, this one deals with SQL Server 2005 high availability
Allan Hirt discusses the details of providing high availability with SQL Server 2005, and the things developers need to know in order to make their applications compatible. He discusses clustering, transaction log shipping, mirroring, and more
The podcast is available in MP3, WMA,WMA L lo-Fi and IPOD (AAC) formats and the duration is one hour and 15 minutes (1:14:55)
Get the podcast here: http://dotnetrocks.com/default.aspx?showID=215
Allan Hirt discusses the details of providing high availability with SQL Server 2005, and the things developers need to know in order to make their applications compatible. He discusses clustering, transaction log shipping, mirroring, and more
The podcast is available in MP3, WMA,WMA L lo-Fi and IPOD (AAC) formats and the duration is one hour and 15 minutes (1:14:55)
Get the podcast here: http://dotnetrocks.com/default.aspx?showID=215
Monday, January 29, 2007
How To Restart A Remote Computer
Sometimes you have to login to your work PC from home over the VPN and after a while for some reason or another you want to restart your PC. How can you do that? You can't use the start menu because only the log off button is displayed
Well one way is to open a command window and executing shutdown -r
That will restart your computer
Here is the basic usage of the shutdown command
Usage: shutdown [-i -l -s -r -a] [-f] [-m \\computername] [-t xx] [-c "comment"] [-d up:xx:yy]
No args Display this message (same as -?)
-i Display GUI interface, must be the first option
-l Log off (cannot be used with -m option)
-s Shutdown the computer
-r Shutdown and restart the computer
-a Abort a system shutdown
-m \\computername Remote computer to shutdown/restart/abort
-t xx Set timeout for shutdown to xx seconds
-c "comment" Shutdown comment (maximum of 127 characters)
-f Forces running applications to close without warning
-d [u][p]:xx:yy The reason code for the shutdown
u is the user code
p is a planned shutdown code
xx is the major reason code (positive integer less than 256)
yy is the minor reason code (positive integer less than 65536)
Well one way is to open a command window and executing shutdown -r
That will restart your computer
Here is the basic usage of the shutdown command
Usage: shutdown [-i -l -s -r -a] [-f] [-m \\computername] [-t xx] [-c "comment"] [-d up:xx:yy]
No args Display this message (same as -?)
-i Display GUI interface, must be the first option
-l Log off (cannot be used with -m option)
-s Shutdown the computer
-r Shutdown and restart the computer
-a Abort a system shutdown
-m \\computername Remote computer to shutdown/restart/abort
-t xx Set timeout for shutdown to xx seconds
-c "comment" Shutdown comment (maximum of 127 characters)
-f Forces running applications to close without warning
-d [u][p]:xx:yy The reason code for the shutdown
u is the user code
p is a planned shutdown code
xx is the major reason code (positive integer less than 256)
yy is the minor reason code (positive integer less than 65536)
Saturday, January 27, 2007
Need Help With Picking Out An ASP.NET 2.0 Book
I decided to get back into some web development again, the last time I did this was in October 2001. I have developed in ASP, JSP and ColdFusion. Since I know some C# and have been using that on and off for the last 2 years with windows forms I decided to use ASP.NET 2.0. Another factor is that it is also a corporate standard at work.
So for someone like me which book would you recommend? Or maybe you have read an ASP.NET book that is just awesome either way let me know.
Don't worry I will never spend less than 51% of my time with SQL ;-)
So for someone like me which book would you recommend? Or maybe you have read an ASP.NET book that is just awesome either way let me know.
Don't worry I will never spend less than 51% of my time with SQL ;-)
Subscribe to:
Posts (Atom)