Last week I created a blog post name OPENROWSET And Excel Problems
I forgot to add one other example, and this is when you have an excel file open and you are trying to open it with OPENROWSET.
If you do this you will get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Just keep this in mind
Monday, March 20, 2006
Friday, March 17, 2006
SQL Server 2005 Service Pack 1 - Community Technology Preview (CTP)
The Community Technology Preview (CTP) version of Microsoft SQL Server 2005 Service Pack 1 is now available. You can use these packages to upgrade any of the following SQL Server 2005 editions:
Enterprise
Enterprise Evaluation
Developer
Standard
Workgroup
To upgrade SQL Server 2005 Express Edition, obtain the SP1 CTP version of Express Edition.
These packages have been made available for general testing purposes only. Do not deploy the CTP software in production
Click here to go to the download page
Enterprise
Enterprise Evaluation
Developer
Standard
Workgroup
To upgrade SQL Server 2005 Express Edition, obtain the SP1 CTP version of Express Edition.
These packages have been made available for general testing purposes only. Do not deploy the CTP software in production
Click here to go to the download page
Thursday, March 16, 2006
OPENROWSET And Excel Problems
Create an excel sheet
In the first 2 rows put some data, save the excel sheet as testing.xls on the c drive
Execute the command below
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')
You will see 1 row since the first row will be used for header names
If you want 2 rows you need to add HDR=No like this
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;HDR=NO','SELECT * FROM [Sheet1$]')
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=SS:\testing.xls','SELECT * FROM [Sheet1$]')
The path can't be found (SS) you will get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing2.xls','SELECT * FROM [Sheet1$]')
When you spell the filename (testing2) wrong you get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet11$]')
When you spell the sheet name (Sheet11) wrong you get this error
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Select * from [Sheet11$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=Select * from [P2 2003 DJIA updates$]'].
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls','SELECT * FROM [Sheet1$]')
When you type the wrong path (D) but the path exists, then you get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
So hopefully the next time you get an error you can quickly figure out if it's the file name, sheet name or path name that is wrong
In the first 2 rows put some data, save the excel sheet as testing.xls on the c drive
Execute the command below
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')
You will see 1 row since the first row will be used for header names
If you want 2 rows you need to add HDR=No like this
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;HDR=NO','SELECT * FROM [Sheet1$]')
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=SS:\testing.xls','SELECT * FROM [Sheet1$]')
The path can't be found (SS) you will get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing2.xls','SELECT * FROM [Sheet1$]')
When you spell the filename (testing2) wrong you get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet11$]')
When you spell the sheet name (Sheet11) wrong you get this error
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Select * from [Sheet11$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=Select * from [P2 2003 DJIA updates$]'].
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls','SELECT * FROM [Sheet1$]')
When you type the wrong path (D) but the path exists, then you get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
So hopefully the next time you get an error you can quickly figure out if it's the file name, sheet name or path name that is wrong
Tuesday, March 14, 2006
Find Out If A Table Has An Identity Column
Here are 2 ways to find out if a table has an identity column
The first way is using the COLUMNPROPERTY function and the second way is using the OBJECTPROPERTY function
USE northwind
GO
DECLARE @tableName VARCHAR(50)
SELECT @tableName = 'orders'
--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO
DECLARE @intObjectID INT
SELECT @intObjectID =OBJECT_ID('orders')
--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@intObjectID, 'TableHasIdentity'),0) AS HasIdentity
The first way is using the COLUMNPROPERTY function and the second way is using the OBJECTPROPERTY function
USE northwind
GO
DECLARE @tableName VARCHAR(50)
SELECT @tableName = 'orders'
--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO
DECLARE @intObjectID INT
SELECT @intObjectID =OBJECT_ID('orders')
--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@intObjectID, 'TableHasIdentity'),0) AS HasIdentity
Monday, March 13, 2006
Search All Stored Procedures That Contain Specific Text
You have changed the column name from titleauthor to t_author in your table and you have 200 stored procedures
The problem is that you don't remember which of these 200 procedures use that column
You run sp_depends 'authors' but like we all know this procedure is not always accurate
So instead of running sp_depends we can take a look at the INFORMATION_SCHEMA.ROUTINES ANSI SQL Schema view
Let's search for all procedures that have the text titleauthor in them
USE pubs
GO
SELECT SPECIFIC_NAME,*
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%titleauthor%'
The problem is that you don't remember which of these 200 procedures use that column
You run sp_depends 'authors' but like we all know this procedure is not always accurate
So instead of running sp_depends we can take a look at the INFORMATION_SCHEMA.ROUTINES ANSI SQL Schema view
Let's search for all procedures that have the text titleauthor in them
USE pubs
GO
SELECT SPECIFIC_NAME,*
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%titleauthor%'
Thursday, March 09, 2006
Create Stored Procedures With Optional Parameters
Sometimes you want to create stored procedures in which all the parameters are not required or will be used. You can create optional parameters and give them a value in the procedure. There are two ways to call procedures; you can pass parameters to stored procedures by name or by position. To pass parameters by name you would do something like this
EXEC prTestOptionalParameters @intID =1
To pass parameters by position you would do something like this
EXEC prTestOptionalParameters 1
Just keep in mind that if you pass by position that you don’t mix up the position because that makes for fun debugging time. Another problem with optional parameters is that if you call the procedure by using position for parameters then those parameters have to be at the end of the procedure. Let’s take a look at how this works
--Create the procedure with optional parameters at the end
CREATE PROCEDURE prTestOptionalParameters
@intID INT,
@chvName VARCHAR(80) = NULL
AS
SET NOCOUNT ON
IF @chvName IS NULL
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is null' AS message
END
ELSE
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is not null' AS message
END
SET NOCOUNT OFF
--Run the proc without the optional parameter
EXEC prTestOptionalParameters 1
--Run the proc with the second parameter
EXEC prTestOptionalParameters 1,'abc'
--Run the proc without the optional parameter and use name parameters
EXEC prTestOptionalParameters @intID =1
--Run the proc with a null value for the optional parameter
EXEC prTestOptionalParameters 1 ,null
--Let's switch the parameters around
CREATE PROCEDURE prTestOptionalParameters2
@chvName VARCHAR(80) = NULL,
@intID INT
AS
SET NOCOUNT ON
IF @chvName IS NULL
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is null' AS message
END
ELSE
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is not null' AS message
END
SET NOCOUNT OFF
--Run the proc without the optional parameter
EXEC prTestOptionalParameters2 1
You will get this error message
Server: Msg 201, Level 16, State 4, Procedure prTestOptionalParameters2, Line 0Procedure 'prTestOptionalParameters2' expects parameter '@intID', which was not supplied.
--Run the procedure with the named parameter
EXEC prTestOptionalParameters2 @intID =1
EXEC prTestOptionalParameters @intID =1
To pass parameters by position you would do something like this
EXEC prTestOptionalParameters 1
Just keep in mind that if you pass by position that you don’t mix up the position because that makes for fun debugging time. Another problem with optional parameters is that if you call the procedure by using position for parameters then those parameters have to be at the end of the procedure. Let’s take a look at how this works
--Create the procedure with optional parameters at the end
CREATE PROCEDURE prTestOptionalParameters
@intID INT,
@chvName VARCHAR(80) = NULL
AS
SET NOCOUNT ON
IF @chvName IS NULL
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is null' AS message
END
ELSE
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is not null' AS message
END
SET NOCOUNT OFF
--Run the proc without the optional parameter
EXEC prTestOptionalParameters 1
--Run the proc with the second parameter
EXEC prTestOptionalParameters 1,'abc'
--Run the proc without the optional parameter and use name parameters
EXEC prTestOptionalParameters @intID =1
--Run the proc with a null value for the optional parameter
EXEC prTestOptionalParameters 1 ,null
--Let's switch the parameters around
CREATE PROCEDURE prTestOptionalParameters2
@chvName VARCHAR(80) = NULL,
@intID INT
AS
SET NOCOUNT ON
IF @chvName IS NULL
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is null' AS message
END
ELSE
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is not null' AS message
END
SET NOCOUNT OFF
--Run the proc without the optional parameter
EXEC prTestOptionalParameters2 1
You will get this error message
Server: Msg 201, Level 16, State 4, Procedure prTestOptionalParameters2, Line 0Procedure 'prTestOptionalParameters2' expects parameter '@intID', which was not supplied.
--Run the procedure with the named parameter
EXEC prTestOptionalParameters2 @intID =1
Wednesday, March 08, 2006
Create Procedures That Run At SQL Server Startup
Let's say you have a table and you want to make sure that you clear it every time SQL Server is restarted
What would be the easiest way to accomplish that? Well you can create a procedure and have it execute every time the SQL Server is restarted
The procedure has to be created in the master database, after it is created you have to use sp_procoption to have the procedure execute when SQL Server starts up
--Let's create our procedure
USE master
GO
CREATE PROCEDURE prStartUp
AS
SELECT GETDATE()
--You would do something real here
--like deleting the data
GO
--Make the procedure execute when the server starts up
sp_procoption prStartUp,startup,'on'
--This will return the proc name since we enabled the ExecIsStartup property
SELECT name FROM sysobjects
WHERE xtype = 'p'
AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1
--disable the execution of the proc on start up
sp_procoption prStartUp,startup,'off'
--Let's check again, no rows should be returned now
SELECT name FROM sysobjects
WHERE xtype = 'p'
AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1
What would be the easiest way to accomplish that? Well you can create a procedure and have it execute every time the SQL Server is restarted
The procedure has to be created in the master database, after it is created you have to use sp_procoption to have the procedure execute when SQL Server starts up
--Let's create our procedure
USE master
GO
CREATE PROCEDURE prStartUp
AS
SELECT GETDATE()
--You would do something real here
--like deleting the data
GO
--Make the procedure execute when the server starts up
sp_procoption prStartUp,startup,'on'
--This will return the proc name since we enabled the ExecIsStartup property
SELECT name FROM sysobjects
WHERE xtype = 'p'
AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1
--disable the execution of the proc on start up
sp_procoption prStartUp,startup,'off'
--Let's check again, no rows should be returned now
SELECT name FROM sysobjects
WHERE xtype = 'p'
AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1
Monday, March 06, 2006
Returning Grouped Random Results
Let's say you have data and you want to return this random but grouped, what do I mean by that? For example you have the following data
sony 4
sony 6
toshiba 1
toshiba 3
mitsubishi 2
mitsubishi 5
When you rerun the query you want the order to be different but the companies have to be grouped together. So the next result could be something like this
toshiba 1
toshiba 3
sony 4
sony 6
mitsubishi 2
mitsubishi 5
To order a result set randomly every single time is easy you just have to add ORDER BY NEWID() and it will be different every single time
To order randomly and keeping it 'ordered/grouped' by company is a little bit trickier
Below are 2 ways to accomplish that. The first way is with a sub query the second is by using RAND and CHECKSUM
If you run the two queries at the same time and look at the execution plan you will notice that the query cost for the query with the sub query is 66.75% (relative to the batch) and for the query with RAND and CHECKSUM it's only 33.25%
For those of you who don't know how to see the execution plan, this is how you do that
CTRL + K (you can also select Query-->Show Execution Plan from the menu bar) then highlight both queries and press F5. Between Grids and Messages you will see a new tab named Execution Plan, click on that tab and you will see a graphical representation of the execution plan
CREATE TABLE #Testcompanies (
Name VARCHAR(50),
ID INT)
INSERT INTO #Testcompanies
SELECT 'toshiba' ,1
UNION ALL
SELECT 'mitsubishi', 2
UNION ALL
SELECT 'toshiba', 3
UNION ALL
SELECT 'sony', 4
UNION ALL
SELECT 'mitsubishi', 5
UNION ALL
SELECT 'sony', 6
--Query using a sub query and NEWID()
SELECT T.*
FROM #Testcompanies T
JOIN (SELECT DISTINCT TOP 100 PERCENT Name,
NEWID() AS GroupedOrder
FROM #Testcompanies
GROUP BY Name
ORDER BY NEWID()) Z
ON T.Name = Z.Name
ORDER BY Z.GroupedOrder
--Query using RAND()
DECLARE @R FLOAT
SET @R = RAND()
SELECT TOP 100 PERCENT *
FROM #TESTCOMPANIES
ORDER BY RAND(@R * CHECKSUM(NAME))
sony 4
sony 6
toshiba 1
toshiba 3
mitsubishi 2
mitsubishi 5
When you rerun the query you want the order to be different but the companies have to be grouped together. So the next result could be something like this
toshiba 1
toshiba 3
sony 4
sony 6
mitsubishi 2
mitsubishi 5
To order a result set randomly every single time is easy you just have to add ORDER BY NEWID() and it will be different every single time
To order randomly and keeping it 'ordered/grouped' by company is a little bit trickier
Below are 2 ways to accomplish that. The first way is with a sub query the second is by using RAND and CHECKSUM
If you run the two queries at the same time and look at the execution plan you will notice that the query cost for the query with the sub query is 66.75% (relative to the batch) and for the query with RAND and CHECKSUM it's only 33.25%
For those of you who don't know how to see the execution plan, this is how you do that
CTRL + K (you can also select Query-->Show Execution Plan from the menu bar) then highlight both queries and press F5. Between Grids and Messages you will see a new tab named Execution Plan, click on that tab and you will see a graphical representation of the execution plan
CREATE TABLE #Testcompanies (
Name VARCHAR(50),
ID INT)
INSERT INTO #Testcompanies
SELECT 'toshiba' ,1
UNION ALL
SELECT 'mitsubishi', 2
UNION ALL
SELECT 'toshiba', 3
UNION ALL
SELECT 'sony', 4
UNION ALL
SELECT 'mitsubishi', 5
UNION ALL
SELECT 'sony', 6
--Query using a sub query and NEWID()
SELECT T.*
FROM #Testcompanies T
JOIN (SELECT DISTINCT TOP 100 PERCENT Name,
NEWID() AS GroupedOrder
FROM #Testcompanies
GROUP BY Name
ORDER BY NEWID()) Z
ON T.Name = Z.Name
ORDER BY Z.GroupedOrder
--Query using RAND()
DECLARE @R FLOAT
SET @R = RAND()
SELECT TOP 100 PERCENT *
FROM #TESTCOMPANIES
ORDER BY RAND(@R * CHECKSUM(NAME))
Conversation With Database Legend Jim Gray
Channel 9 has an interesting video with Turing award winner Jim Gray. From the website: "This episode features Jim Gray. He is a "Technical Fellow" in the Scaleable Servers Research Group (Sky Server, Terra Server) and manager of Microsoft's Bay Area Research Center (BARC). Jim has been called a "giant" in the fields of database and transaction processing computer systems. In 1998, Jim was awarded the ACM’s prestigious A.M. Turing Award. Before joining Microsoft, Jim worked at Digital Equipment Corp (DEC)., Tandem Computers Inc., IBM Corp. and AT&T and he is the editor of the “Performance Handbook for Database and Transaction Processing Systems,” and co-author of “Transaction Processing: Concepts and Techniques.” In this interview, Jim is joined by former colleague from DEC and partner on the Terra Server project, Researcher, Tom Barclay."
Pro SQL Server 2005 Assemblies
Pro SQL Server 2005 Assemblies provides a detailed and example-driven tutorial on how to build and use .NET assemblies. The authors focus on building assemblies in C#, but also provide the equivalent VB .NET code in the supplied code download.Assemblies are not a complete replacement for T-SQL stored procedures and triggers; rather, they’re enhancements, to be used at the right place and right time. This book examines the ins and outs of assemblies—when they should and should not be used, what you can do with them, and how you can get the most out of them.
Table Of Contents
CHAPTER 1 Introducing Assemblies
CHAPTER 2 Writing a Simple SQL Assembly
CHAPTER 3 The SQL Server NET Programming Model
CHAPTER 4 CLR Stored Procedures
CHAPTER 5 User-Defined Functions
CHAPTER 6 User-Defined Types
CHAPTER 7 User-Defined Aggregates
CHAPTER 8 CLR Triggers
CHAPTER 9 Error Handling and Debugging Strategies
CHAPTER 10 Security
CHAPTER 11 Integrating Assemblies with Other Technologies
INDEX
Sample Chapter: The SQL Server NET Programming Model
Amazon link here
Sunday, March 05, 2006
SQL Server Cursors Not Always Evil
By know you probably heard a thousand times not to use cursors but to use a set based solution instead. Cursors are bad, almost everything that can be written in a cursor can be rewritten to use a set based approach etc etc etc. Well SQL Server MVP Adam Machanic has a great post on his blog that will show you that a cusrsor sometimes is actually faster than a set based solution. Read the post Running sums, redux
A day later Adam created another entry on his blog, this time using SQLCLR code to rewrite the cursor code and improving the execution time by 25%. You can read that entry here (Running sums yet again: SQLCLR saves the day!)
So as you can see cursors do have a place in SQL Server and are sometimes a better option
A day later Adam created another entry on his blog, this time using SQLCLR code to rewrite the cursor code and improving the execution time by 25%. You can read that entry here (Running sums yet again: SQLCLR saves the day!)
So as you can see cursors do have a place in SQL Server and are sometimes a better option
Thursday, March 02, 2006
Top 5 SQL Posts Of All Time
Below are the top 5 posts according to Google Analytics for all time!!
SQL Server 2005 Free E-Learning
Fun With SQL Server Update Triggers
Feature Pack for Microsoft SQL Server 2005
Find all Primary and Foreign Keys In A Database
SQL Query Optimizations
I will update this post once a month and might make it 10, don't know yet
SQL Server 2005 Free E-Learning
Fun With SQL Server Update Triggers
Feature Pack for Microsoft SQL Server 2005
Find all Primary and Foreign Keys In A Database
SQL Query Optimizations
I will update this post once a month and might make it 10, don't know yet
Top 5 SQL Server Posts for February 2006
Below are the top 5 posts according to Google Analytics for the month of February
SQL Query Optimizations
Fun With SQL Server Update Triggers
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Find all Primary and Foreign Keys In A Database
SQL Server 2005 Free E-Learning
SQL Query Optimizations
Fun With SQL Server Update Triggers
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Find all Primary and Foreign Keys In A Database
SQL Server 2005 Free E-Learning
Top SQL Server Google Searches For February 2006
These are the top SQL Searches on this site for the month of February. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...
HOW TO IMPROVE SQL SERVER Query PERFORMANCE
sql server tips tricks
HOW INCREASE THE SPEED OF SQL QUERY
hOW TO FINE TUNE SQL Query
sql code automation
bulk insert
deadlock
triggers
return multiple rows t-sql
CmdExec sql backup filename parameter
backup filename
how to backup a sql server db
SQl server books
HOW TO IMPROVE SQL SERVER Query PERFORMANCE
sql server tips tricks
HOW INCREASE THE SPEED OF SQL QUERY
hOW TO FINE TUNE SQL Query
sql code automation
bulk insert
deadlock
triggers
return multiple rows t-sql
CmdExec sql backup filename parameter
backup filename
how to backup a sql server db
SQl server books
Tuesday, February 28, 2006
Find All tables That Contain A Certain Column
This question was posted today on the tek-tips site and also in the microsoft.public.sqlserver.programming newsgroup
The question was how to find all tables that contain a certain column
For example return all tables that have the column OrderID in the Northwind database
You can get all that information from the INFORMATION_SCHEMA.COLUMNS system view, however that view returns tables as well as views
You have to join with INFORMATION_SCHEMA.TABLES and that view contains a column named TABLE_TYPE that you can use to filter on the type (BASE TABLE or VIEW)
I have only selected a couple of columns from the views, use * to see them all
USE Northwind
GO
SELECT c.TABLE_NAME,
TABLE_TYPE,
COLUMN_NAME,
ORDINAL_POSITION,
IS_NULLABLE,
DATA_TYPE,
NUMERIC_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE COLUMN_NAME ='orderid'
ORDER BY TABLE_TYPE ,c.TABLE_NAME
The question was how to find all tables that contain a certain column
For example return all tables that have the column OrderID in the Northwind database
You can get all that information from the INFORMATION_SCHEMA.COLUMNS system view, however that view returns tables as well as views
You have to join with INFORMATION_SCHEMA.TABLES and that view contains a column named TABLE_TYPE that you can use to filter on the type (BASE TABLE or VIEW)
I have only selected a couple of columns from the views, use * to see them all
USE Northwind
GO
SELECT c.TABLE_NAME,
TABLE_TYPE,
COLUMN_NAME,
ORDINAL_POSITION,
IS_NULLABLE,
DATA_TYPE,
NUMERIC_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE COLUMN_NAME ='orderid'
ORDER BY TABLE_TYPE ,c.TABLE_NAME
Monday, February 27, 2006
SQL Server Upgrade Your Skills To 2005 Webcasts
Are you planning new database development projects this year? Do you need to increase the scale, performance, security, or continuous availability of your existing systems to meet the demands of the business? Microsoft SQL Server 2005 is here, bringing significant enhancements in performance, availability, and security and the most powerful and flexible set of DBA productivity tools we have ever delivered. Our subject matter experts walk through all of the major features and enhancements built into SQL Server 2005 and demonstrate how you can bring these benefits to your organization. Topics covered include SQL Server 2005 management tools, security enhancements, high availability features, replication, and scalability. Also, tune in weekly through June 2006 as Microsoft MVP and Regional Director Kimberly Tripp and her colleagues present an 11-part series on building robust, recoverable, and reliable SQL Server 2005 systems.
Live Webcasts
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 1 of 11): A Fast-Paced Feature Overview and Series Introduction (Level 200)
Friday, March 10, 2006 9:30 A.M.–11:00 A.M. Pacific Time
This first webcast of the SQL Server 2005 for the IT Professional series presents the new features designed to help improve administration, management, and operations. This webcast provides an overview of new features, how they fit together, and best practices for each. Building a robust, recoverable, and reliable system requires knowledge, best practices, and finding the right tool for the job. Join this series to learn how to build the foundation for success.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 2 of 11): Security (Level 200)
Friday, March 17, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Securing SQL Server system requires a variety of defenses. Microsoft SQL Server 2005 provides numerous mechanisms to reduce the attackable surface area, from features that are configured "off by default" to more complex features that can encrypt data to defend against rogue file copies of the database. Understanding how to deploy the wide selection of development and administrative options securely can be complex. In this second webcast of the SQL Server 2005 for the IT Professional series, learn about best practices for sorting through the options to optimally secure SQL Server 2005. Additionally, this webcast addresses security issues such as protecting and recovering data as it relates to encryption key escrow and recovery.
TechNet Webcast: SQL Server for the IT Professional (Part 3 of 11): Understanding Installation Options and Initial Configuration (Level 200)
Friday, March 24, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Graphical installation requires you to continually click next through the options with the idea that you can always change the settings later. We believe there is a much better way. Make the right decisions at installation to ensure a more secure and properly configured environment. In this third webcast of the SQL Server 2005 for the IT Professional series, we present Service Account options, Authentication Mode, secure sockets layer (SSL) security (without installing SSL), password requirements, and installation directory structures. We explain code page and sort order selection for new and upgraded installations. The session wraps up with the changes necessary immediately after installation: setting the system administrator (SA) password and using the SQL Surface Area Configuration tool and Configuration Manager.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 4 of 11): Upgrade Considerations and Migration Paths (Level 200)
Friday, March 31, 2006 9:30 A.M.–11:00 A.M. Pacific Time
In this fourth webcast of the SQL Server 2005 for the IT Professional series, we help you prepare your migration path to minimize potential failures. In the first half of this session learn how to use the Upgrade Advisor and what to look for in your existing databases. The second half helps you to understand your options and explains the Copy Database Wizard, Detach/Attach, and Backup/Restore features. Join us to create a careful and easy test plan today so that your migration is easy tomorrow!
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 5 of 11): Effective Use of the New Management Tools (Level 200)
Friday, April 7, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Microsoft SQL Server 2005 introduces a new set of tools that are a radical departure from previous versions. If you want to avoid a steep learning curve attend this fifth webcast of the SQL Server 2005 for the IT Professional series, presented by Kimberly Tripp, to discover the new features designed to make the database administration and database development teams more effective and productive. Understanding the impact of the Microsoft Visual Studio shell—and where SQL Server Management Studio (SSMS) diverges from the standard shell—can help you to use SSMS more effectively. The primary focus of this webcast is on solutions, projects, customizing the query tool, and source control integration.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 6 of 11): New Application Design Patterns for Scalability and Availability and the Operational Implications of Service Broker (Level 200)
Friday, April 14, 2006 9:30 A.M.–11:00 A.M. Pacific Time
SQL Service Broker is an integral part of the database in Microsoft SQL Server 2005. Understanding why developers may choose to use this is one part of the picture, but some internal SQL Server features use it as well. In this sixth webcast of the SQL Server 2005 for the IT Professional series, we present what new data objects comprise Service Broker, what new SQL statements you need to know about, and how you manage it, secure it, and monitor it, along with information about when it is appropriate to consider a Broker-based application. Join us to learn about dynamic management views and profiler support, in addition to how you can troubleshoot operational problems. The webcast also explains the internal functions run by Service Broker and how they work.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 7 of 11): Technologies and Features to Improve Availability (Level 200)
Friday, April 21, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Find the right technology for the job in this seventh webcast of the SQL Server 2005 for the IT Professional series. Join us to learn which technologies provide the right solution for a specific problem, as well as the pros and cons of each technology. Designing a system to protect you against the faults most likely to occur is the first and most important strategy, but finding the right combination to minimize both downtime and data loss is critical. This webcast covers all of the “Availability” technologies at a glance: remote mirroring, failover clustering, database mirroring, log hipping, replication, redundant array of independent disks (RAID), partial database availability, piecemeal online restore, database snapshots, snapshot isolation, and online operations.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 8 of 11): Implementing Database Mirroring in SQL Server 2005 (Part 1 of 2) (Level 200)
Friday, April 28, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Database mirroring was released for testing when Microsoft SQL Server 2005 shipped as a product. As we approach the first service pack, get prepared for the release of database mirroring for general production use and understand the barriers of what database mirroring will protect against. Attend this first part in the eighth webcast of the SQL Server 2005 for the IT Professional series to obtain better insight for when database mirroring should be implemented as well as what to expect moving forward in service pack 1 (SP1).
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 9 of 11): Implementing Database Mirroring (Part 2 of 2) (Level 200)
Friday, May 5, 2006 9:30 A.M.–11:00 A.M. Pacific Time
After learning about what database mirroring provides—as well as the changes to expect in service pack 1(SP1)—attend this second part in the ninth webcast of the SQL Server 2005 for the IT Professional series to walk through the entire process of implementation, failover, and monitoring. Join us for this slide-free session; it’s all about the demos.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 10 of 11): Recovering from Human Error (Level 200)
Friday, May 12, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Microsoft SQL Server 2005 provides a variety of new features to help deal with the possibility of human error with preventative techniques, investigative techniques, and recovery techniques. Join this tenth webcast of the SQL Server 2005 for the IT Professional series as we look at data definition language (DDL) triggers, event notifications, partial database availability, partial database restores, and database snapshots.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 11 of 11): Best Practices in Building Robust, Recoverable, and Reliable Systems (Level 200)
Friday, May 19, 2006 9:30 A.M.–11:00 A.M. Pacific Time
In this eleventh and last webcast of the SQL Server 2005 for the IT Professional series, Kimberly Tripp reviews key areas discussed throughout the series to ensure that all areas have been covered. This summary session should keep you thinking, give you more resources to review and use, and point to a few new areas to consider. Building a robust, recoverable, and reliable system takes a myriad of options. Join us to learn what the options are and how you can start making the most of them.
Live Webcasts
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 1 of 11): A Fast-Paced Feature Overview and Series Introduction (Level 200)
Friday, March 10, 2006 9:30 A.M.–11:00 A.M. Pacific Time
This first webcast of the SQL Server 2005 for the IT Professional series presents the new features designed to help improve administration, management, and operations. This webcast provides an overview of new features, how they fit together, and best practices for each. Building a robust, recoverable, and reliable system requires knowledge, best practices, and finding the right tool for the job. Join this series to learn how to build the foundation for success.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 2 of 11): Security (Level 200)
Friday, March 17, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Securing SQL Server system requires a variety of defenses. Microsoft SQL Server 2005 provides numerous mechanisms to reduce the attackable surface area, from features that are configured "off by default" to more complex features that can encrypt data to defend against rogue file copies of the database. Understanding how to deploy the wide selection of development and administrative options securely can be complex. In this second webcast of the SQL Server 2005 for the IT Professional series, learn about best practices for sorting through the options to optimally secure SQL Server 2005. Additionally, this webcast addresses security issues such as protecting and recovering data as it relates to encryption key escrow and recovery.
TechNet Webcast: SQL Server for the IT Professional (Part 3 of 11): Understanding Installation Options and Initial Configuration (Level 200)
Friday, March 24, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Graphical installation requires you to continually click next through the options with the idea that you can always change the settings later. We believe there is a much better way. Make the right decisions at installation to ensure a more secure and properly configured environment. In this third webcast of the SQL Server 2005 for the IT Professional series, we present Service Account options, Authentication Mode, secure sockets layer (SSL) security (without installing SSL), password requirements, and installation directory structures. We explain code page and sort order selection for new and upgraded installations. The session wraps up with the changes necessary immediately after installation: setting the system administrator (SA) password and using the SQL Surface Area Configuration tool and Configuration Manager.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 4 of 11): Upgrade Considerations and Migration Paths (Level 200)
Friday, March 31, 2006 9:30 A.M.–11:00 A.M. Pacific Time
In this fourth webcast of the SQL Server 2005 for the IT Professional series, we help you prepare your migration path to minimize potential failures. In the first half of this session learn how to use the Upgrade Advisor and what to look for in your existing databases. The second half helps you to understand your options and explains the Copy Database Wizard, Detach/Attach, and Backup/Restore features. Join us to create a careful and easy test plan today so that your migration is easy tomorrow!
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 5 of 11): Effective Use of the New Management Tools (Level 200)
Friday, April 7, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Microsoft SQL Server 2005 introduces a new set of tools that are a radical departure from previous versions. If you want to avoid a steep learning curve attend this fifth webcast of the SQL Server 2005 for the IT Professional series, presented by Kimberly Tripp, to discover the new features designed to make the database administration and database development teams more effective and productive. Understanding the impact of the Microsoft Visual Studio shell—and where SQL Server Management Studio (SSMS) diverges from the standard shell—can help you to use SSMS more effectively. The primary focus of this webcast is on solutions, projects, customizing the query tool, and source control integration.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 6 of 11): New Application Design Patterns for Scalability and Availability and the Operational Implications of Service Broker (Level 200)
Friday, April 14, 2006 9:30 A.M.–11:00 A.M. Pacific Time
SQL Service Broker is an integral part of the database in Microsoft SQL Server 2005. Understanding why developers may choose to use this is one part of the picture, but some internal SQL Server features use it as well. In this sixth webcast of the SQL Server 2005 for the IT Professional series, we present what new data objects comprise Service Broker, what new SQL statements you need to know about, and how you manage it, secure it, and monitor it, along with information about when it is appropriate to consider a Broker-based application. Join us to learn about dynamic management views and profiler support, in addition to how you can troubleshoot operational problems. The webcast also explains the internal functions run by Service Broker and how they work.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 7 of 11): Technologies and Features to Improve Availability (Level 200)
Friday, April 21, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Find the right technology for the job in this seventh webcast of the SQL Server 2005 for the IT Professional series. Join us to learn which technologies provide the right solution for a specific problem, as well as the pros and cons of each technology. Designing a system to protect you against the faults most likely to occur is the first and most important strategy, but finding the right combination to minimize both downtime and data loss is critical. This webcast covers all of the “Availability” technologies at a glance: remote mirroring, failover clustering, database mirroring, log hipping, replication, redundant array of independent disks (RAID), partial database availability, piecemeal online restore, database snapshots, snapshot isolation, and online operations.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 8 of 11): Implementing Database Mirroring in SQL Server 2005 (Part 1 of 2) (Level 200)
Friday, April 28, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Database mirroring was released for testing when Microsoft SQL Server 2005 shipped as a product. As we approach the first service pack, get prepared for the release of database mirroring for general production use and understand the barriers of what database mirroring will protect against. Attend this first part in the eighth webcast of the SQL Server 2005 for the IT Professional series to obtain better insight for when database mirroring should be implemented as well as what to expect moving forward in service pack 1 (SP1).
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 9 of 11): Implementing Database Mirroring (Part 2 of 2) (Level 200)
Friday, May 5, 2006 9:30 A.M.–11:00 A.M. Pacific Time
After learning about what database mirroring provides—as well as the changes to expect in service pack 1(SP1)—attend this second part in the ninth webcast of the SQL Server 2005 for the IT Professional series to walk through the entire process of implementation, failover, and monitoring. Join us for this slide-free session; it’s all about the demos.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 10 of 11): Recovering from Human Error (Level 200)
Friday, May 12, 2006 9:30 A.M.–11:00 A.M. Pacific Time
Microsoft SQL Server 2005 provides a variety of new features to help deal with the possibility of human error with preventative techniques, investigative techniques, and recovery techniques. Join this tenth webcast of the SQL Server 2005 for the IT Professional series as we look at data definition language (DDL) triggers, event notifications, partial database availability, partial database restores, and database snapshots.
TechNet Webcast: SQL Server 2005 for the IT Professional (Part 11 of 11): Best Practices in Building Robust, Recoverable, and Reliable Systems (Level 200)
Friday, May 19, 2006 9:30 A.M.–11:00 A.M. Pacific Time
In this eleventh and last webcast of the SQL Server 2005 for the IT Professional series, Kimberly Tripp reviews key areas discussed throughout the series to ensure that all areas have been covered. This summary session should keep you thinking, give you more resources to review and use, and point to a few new areas to consider. Building a robust, recoverable, and reliable system takes a myriad of options. Join us to learn what the options are and how you can start making the most of them.
Thursday, February 23, 2006
Three Way To List All Databases On Your Server
Sometimes you want to print out a document with all the databases that exists on one server so that you can compare this against another server for example
Listed below are three ways to accomplish this by using a system table, a system view and an undocumented stored procedure
First Way
Use the INFORMATION_SCHEMA.SCHEMATA system view
SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN('master','msdb','tempdb','model')
ORDER BY CATALOG_NAME
Second Way
Use the sysdatabases system table in the master database
SELECT name FROM master..sysdatabases
WHERE name NOT IN('master','msdb','tempdb','model')
ORDER BY Name
Third way
Use the undocumented sp_MSForEachDB procedure
CREATE TABLE #AllDB (Name VARCHAR(100))
INSERT INTO #AllDB
EXEC sp_MSForEachDB 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
SELECT ''?'' '
SELECT * FROM #AllDB
Listed below are three ways to accomplish this by using a system table, a system view and an undocumented stored procedure
First Way
Use the INFORMATION_SCHEMA.SCHEMATA system view
SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN('master','msdb','tempdb','model')
ORDER BY CATALOG_NAME
Second Way
Use the sysdatabases system table in the master database
SELECT name FROM master..sysdatabases
WHERE name NOT IN('master','msdb','tempdb','model')
ORDER BY Name
Third way
Use the undocumented sp_MSForEachDB procedure
CREATE TABLE #AllDB (Name VARCHAR(100))
INSERT INTO #AllDB
EXEC sp_MSForEachDB 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
SELECT ''?'' '
SELECT * FROM #AllDB
Tuesday, February 21, 2006
Convert Float Stored As Varchar To Numeric
An interesting problem came up today in the microsoft SQL Server DTS newsgroup
A person tried to convert a varchar value to numeric and got the following message "Error converting data type varchar to numeric."
The problem is that the value stored as varchar is a float and then SQL Server is not able to convert this to a numeric datatype
So first you have to convert to float and then to numeric
DECLARE @Value VARCHAR(50)
SELECT @Value = '5.9499999999999997E-2'
--Error converting data type varchar to numeric.
SELECT CONVERT(NUMERIC(21,20),@Value)
--This works after converting to float first
SELECT CONVERT(NUMERIC(21,20),CONVERT(FLOAT,@Value))
--No problems here since the value in the varchar field can be converted to numeric
DECLARE @Value2 VARCHAR(50)
SELECT @Value2 = '5.9499999999999997'
A person tried to convert a varchar value to numeric and got the following message "Error converting data type varchar to numeric."
The problem is that the value stored as varchar is a float and then SQL Server is not able to convert this to a numeric datatype
So first you have to convert to float and then to numeric
DECLARE @Value VARCHAR(50)
SELECT @Value = '5.9499999999999997E-2'
--Error converting data type varchar to numeric.
SELECT CONVERT(NUMERIC(21,20),@Value)
--This works after converting to float first
SELECT CONVERT(NUMERIC(21,20),CONVERT(FLOAT,@Value))
--No problems here since the value in the varchar field can be converted to numeric
DECLARE @Value2 VARCHAR(50)
SELECT @Value2 = '5.9499999999999997'
SQL Server And Business Intelligence Podcast
Channel 9 on MSDN has added another SQL server podcast yesterday, this one is about SQL Server And Business Intelligence
From their page: "Business Intelligence… some people think that is an oxymoron but my friend it is most certainly better than business ignorance which unfortunately is how many business operate. In the last few years most companies have been amassing data at a phenomenal rate but what does that data tell you? Well listen up because my guest today Andrew Brust is going tell you how you can use the tools in SQL Server 2005 to make sense of this treasure trove of competitive information because business intelligence is always better than business stupidity."
The podcast is 32 minutes and 58 seconds and you can get it here
Enjoy the show
From their page: "Business Intelligence… some people think that is an oxymoron but my friend it is most certainly better than business ignorance which unfortunately is how many business operate. In the last few years most companies have been amassing data at a phenomenal rate but what does that data tell you? Well listen up because my guest today Andrew Brust is going tell you how you can use the tools in SQL Server 2005 to make sense of this treasure trove of competitive information because business intelligence is always better than business stupidity."
The podcast is 32 minutes and 58 seconds and you can get it here
Enjoy the show
Monday, February 20, 2006
XML In SQL Server 2005 Podcast
XML in SQL Server 2005Channel9 at MSDN.com has a great SQL Server 2005 and XML podcast
From the page: "How much code have you seen that is devoted to simply shredding XML and storing it in a database? What if you didn't need to do that anymore? What if you could store XML in all of it's unstructured hierarchical goodness and just leave it at that? Well my friend, you are in luck because on this episode Shankar Pal of the SQL Server team is going to tell you all about the details of the great new XML support in SQL Server 2005."
Get the podcast here
Enjoy the show
Friday, February 17, 2006
Sort A SQL Server Table With CASE Or CHARINDEX
Let's say you have a table with states in your database, you only have 3 values NY, ME and SC.
You want to order the result like this: first NY followed by SC and ME last.
You can do that in two different ways
One: use a case statement in your order by
Two: use Charindex in your order by
Let's see how that works
CREATE TABLE #TEST (
STATE CHAR(2))
INSERT INTO #TEST
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC'
-- order by using CASE
--Order by using CHARINDEX
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'NY-SC-ME')
--or without NY since CHARINDEX will return 0 for NY and it will be first
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-ME')
--the problem is of course if you have more values and you only want to have NY and SC showing up first and second
--let's insert 2 more rows
INSERT INTO #TEST
SELECT 'IL'
UNION ALL
SELECT 'CA'
-- Now the CHARINDEX Order doesn't work
-- the trick is to make it Descending and switch the states around
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-NY') DESC
or this way
--Order by using CHARINDEX DESC
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'ME-SC-NY') DESC
You want to order the result like this: first NY followed by SC and ME last.
You can do that in two different ways
One: use a case statement in your order by
Two: use Charindex in your order by
Let's see how that works
CREATE TABLE #TEST (
STATE CHAR(2))
INSERT INTO #TEST
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC'
-- order by using CASE
SELECT *
FROM #TEST
ORDER BY CASE STATE
WHEN 'NY' THEN 1
WHEN 'SC' THEN 2
ELSE 3
END
--Order by using CHARINDEX
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'NY-SC-ME')
--or without NY since CHARINDEX will return 0 for NY and it will be first
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-ME')
--the problem is of course if you have more values and you only want to have NY and SC showing up first and second
--let's insert 2 more rows
INSERT INTO #TEST
SELECT 'IL'
UNION ALL
SELECT 'CA'
-- Now the CHARINDEX Order doesn't work
-- the trick is to make it Descending and switch the states around
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-NY') DESC
or this way
--Order by using CHARINDEX DESC
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'ME-SC-NY') DESC
Tuesday, February 14, 2006
User Definded Functions And GETDATE() (nondeterministic built-in functions )
I have read on Louis Davidson's blog a post about UDF and being able to use GETDATE() in a UDF in SQL server 2005. I wasn't aware of that so I would like to bring it to your attention
In SQL server 2005 you are able to use the following nondeterministic built-in functions in Transact-SQL user-defined functions:
CURRENT_TIMESTAMP
GET_TRANSMISSION_STATUS
GETDATE
GETUTCDATE
@@PACK_SENT
@@PACK_RECEIVED
@@MAX_CONNECTIONS
@@PACKET_ERRORS
@@CONNECTIONS
@@TIMETICKS
@@CPU_BUSY
@@TOTAL_ERRORS
@@DBTS
@@TOTAL_READ
@@IDLE
@@TOTAL_WRITE
@@IO_BUSY
So let's get started and test it out, I will create 2 UDF's one that uses GETDATE() and one that uses DATEADD() and GETDATE()
CREATE FUNCTION fnNonDeterministic ()
RETURNS DATETIME
AS
BEGIN
RETURN GETDATE()
END
GO
CREATE FUNCTION fnNonDeterministicAdd (@Days int)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(d,@Days,GETDATE())
END
GO
--Let's see what the ouput is
SELECT dbo.fnNonDeterministic() AS DateReturned,
dbo.fnNonDeterministicAdd(2) AS DateAddReturned
In SQL server 2005 you are able to use the following nondeterministic built-in functions in Transact-SQL user-defined functions:
CURRENT_TIMESTAMP
GET_TRANSMISSION_STATUS
GETDATE
GETUTCDATE
@@PACK_SENT
@@PACK_RECEIVED
@@MAX_CONNECTIONS
@@PACKET_ERRORS
@@CONNECTIONS
@@TIMETICKS
@@CPU_BUSY
@@TOTAL_ERRORS
@@DBTS
@@TOTAL_READ
@@IDLE
@@TOTAL_WRITE
@@IO_BUSY
So let's get started and test it out, I will create 2 UDF's one that uses GETDATE() and one that uses DATEADD() and GETDATE()
CREATE FUNCTION fnNonDeterministic ()
RETURNS DATETIME
AS
BEGIN
RETURN GETDATE()
END
GO
CREATE FUNCTION fnNonDeterministicAdd (@Days int)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(d,@Days,GETDATE())
END
GO
--Let's see what the ouput is
SELECT dbo.fnNonDeterministic() AS DateReturned,
dbo.fnNonDeterministicAdd(2) AS DateAddReturned
Monday, February 13, 2006
Use CHARINDEX, LEN and SUBSTRING To Find Data Between Characters
Let's say you get data in the following format aaaa/bbbb/cccc/dddd/eeee
What you need from this file is just the data between the first and the last forward slash (in thid case bbbb)
If there is no slash in the file then grab all the data, if there is only 1 slash then grab everything after that slash
This was actually one of the questions that I have answered on tek-tips so yes data like this does exist.
How do you attack such a problem?
First you have to find the first slash, you do that by using CHARINDEX, CHARINDEX will return the position where the first slash is located, then you use CHARINDEX again with SUBSTRING to find the Second slash, finally you use SUBSTRING with the LEFT function and the difference between the 2 CHARINDEX calls as the length
I used a replace function at the beginning to check if there is more than 1 slash or not and then a case statement later on depending on the outcome of that replace function
Let’s see how I did it
--Create some test data
CREATE TABLE #test (ValueField VARCHAR(50))
INSERT INTO #test
SELECT ' /aaaa' UNION ALL
SELECT ' /yyyy/bbbb ' UNION ALL
SELECT 'zzzzz/c/defgh ' UNION ALL
SELECT 'VVVVV'
--This is our delimiter
DECLARE @Delimiter CHAR(1)
SET @Delimiter = '/'
--Show the whole field and the extracted value
SELECT ValueField AS Originalvalue,
CASE WHEN LEN(ValueField) -LEN(REPLACE(ValueField,@Delimiter,'')) > 1
THEN LEFT(SUBSTRING(ValueField,CHARINDEX(@Delimiter,ValueField)+1 ,LEN(ValueField)),CHARINDEX(@Delimiter,SUBSTRING(ValueField,CHARINDEX(@Delimiter,ValueField)+1 ,LEN(ValueField)))-1)
ELSE SUBSTRING(ValueField,CHARINDEX(@Delimiter,ValueField)+1 ,LEN(ValueField))END AS ExtractedValue
FROM #test
What you need from this file is just the data between the first and the last forward slash (in thid case bbbb)
If there is no slash in the file then grab all the data, if there is only 1 slash then grab everything after that slash
This was actually one of the questions that I have answered on tek-tips so yes data like this does exist.
How do you attack such a problem?
First you have to find the first slash, you do that by using CHARINDEX, CHARINDEX will return the position where the first slash is located, then you use CHARINDEX again with SUBSTRING to find the Second slash, finally you use SUBSTRING with the LEFT function and the difference between the 2 CHARINDEX calls as the length
I used a replace function at the beginning to check if there is more than 1 slash or not and then a case statement later on depending on the outcome of that replace function
Let’s see how I did it
--Create some test data
CREATE TABLE #test (ValueField VARCHAR(50))
INSERT INTO #test
SELECT ' /aaaa' UNION ALL
SELECT ' /yyyy/bbbb ' UNION ALL
SELECT 'zzzzz/c/defgh ' UNION ALL
SELECT 'VVVVV'
--This is our delimiter
DECLARE @Delimiter CHAR(1)
SET @Delimiter = '/'
--Show the whole field and the extracted value
SELECT ValueField AS Originalvalue,
CASE WHEN LEN(ValueField) -LEN(REPLACE(ValueField,@Delimiter,'')) > 1
THEN LEFT(SUBSTRING(ValueField,CHARINDEX(@Delimiter,ValueField)+1 ,LEN(ValueField)),CHARINDEX(@Delimiter,SUBSTRING(ValueField,CHARINDEX(@Delimiter,ValueField)+1 ,LEN(ValueField)))-1)
ELSE SUBSTRING(ValueField,CHARINDEX(@Delimiter,ValueField)+1 ,LEN(ValueField))END AS ExtractedValue
FROM #test
Friday, February 10, 2006
How To Check For A Certain Time With SQL
Sometimes you have jobs and you don't know how long they will run
Let's just assume here that the job has 2 steps
You run step 1 and it takes x hours, you only want to run step 2 if it's before 5:45 AM because some other job will start at 6:00 AM and you don't want those 2 jobs hitting the same table at the same time (a real world scenario might be different of course, this is just a simplified version)
I hardcoded 05:45:00.000 here, you can of course store this in a parameter or even in a table so that you only have to change it in one place
--Get today's date
DECLARE @CurrentDateTime DATETIME
SET @CurrentDateTime = GETDATE()
-- create a 2nd parameter and append the time that you need to check for
DECLARE @TimeToChek DATETIME
SELECT @TimeToChek = CONVERT(VARCHAR,@CurrentDateTime,101) + ' 05:45:00.000'
IF (@CurrentDateTime <= @TimeToChek)
-- It is before 5:45 AM
BEGIN
SELECT 'before 5:45am' OutputStatus,
@CurrentDateTime AS CurrentTime,
@TimeToChek AS TimeToCheck
END
ELSE
-- it is before 5:45 AM
BEGIN
SELECT 'Not before 5:45am' OutputStatus,
@CurrentDateTime AS CurrentTime,
@TimeToChek AS TimeToCheck
END
Let's just assume here that the job has 2 steps
You run step 1 and it takes x hours, you only want to run step 2 if it's before 5:45 AM because some other job will start at 6:00 AM and you don't want those 2 jobs hitting the same table at the same time (a real world scenario might be different of course, this is just a simplified version)
I hardcoded 05:45:00.000 here, you can of course store this in a parameter or even in a table so that you only have to change it in one place
--Get today's date
DECLARE @CurrentDateTime DATETIME
SET @CurrentDateTime = GETDATE()
-- create a 2nd parameter and append the time that you need to check for
DECLARE @TimeToChek DATETIME
SELECT @TimeToChek = CONVERT(VARCHAR,@CurrentDateTime,101) + ' 05:45:00.000'
IF (@CurrentDateTime <= @TimeToChek)
-- It is before 5:45 AM
BEGIN
SELECT 'before 5:45am' OutputStatus,
@CurrentDateTime AS CurrentTime,
@TimeToChek AS TimeToCheck
END
ELSE
-- it is before 5:45 AM
BEGIN
SELECT 'Not before 5:45am' OutputStatus,
@CurrentDateTime AS CurrentTime,
@TimeToChek AS TimeToCheck
END
Thursday, February 09, 2006
Beginning SQL Server 2005 For Developers: From Novice To Professional
Apress has published their latest SQl server 2005 book: Beginning SQL Server 2005 For Developers: From Novice To ProfessionalSQL Server 2005 will increase your programming options, productivity, analysis, and database management. If you have some basic knowledge of relational databases and want to start a career as a developer using SQL Server, then this book is your ideal first step. It explains the core jobs and roles for developing a database in both SQL Server 2000 and 2005.
This book features practical steps to help you overcome issues you’re likely to encounter. You’ll learn to use SQL for querying, inserting, updating, and deleting data. You’ll also learn how to back up and restore databases for basic administration in SQL Server. Further, you’ll cover how to build a complete database, from the fundamentals of relational database design to table and index creation.
Additionally, you’ll start to program in T-SQL, SQL Server’s implementation (and extension) of the SQL programming language, and you’ll come away with effective programming techniques using stored procedures and triggers. The book also includes a CD that contains an evaluation edition of SQL Server 2005 so you can start building database applications right away.
This book is 536 pages and below is the table of contents
CHAPTER 1 SQL Server 2005 Overview and Installation
CHAPTER 2 SQL Server Management Studio
CHAPTER 3 Database Design and Creation
CHAPTER 4 Security
CHAPTER 5 Defining Tables
CHAPTER 6 Creating Indexes and Database Diagramming
CHAPTER 7 Database Backups, Recovery, and Maintenance
CHAPTER 8 Working with the Data
CHAPTER 9 Building a View
CHAPTER 10 Stored Procedures
CHAPTER 11 T-SQL Essentials
CHAPTER 12 Advanced T-SQL
CHAPTER 13 Triggers
CHAPTER 14 SQL Server 2005 Reporting Services
APPENDIX Glossary of Terms
INDEX
Download chapter 4 (security) here
If you want to puchase this book the Amazon link is here
Check If Temporary Table Exists
How do you check if a temp table exists?
You can use IF OBJECT_ID('tempdb..#temp') IS NOT NULL
Let's see how it works
--Create table
USE Norhtwind
GO
CREATE TABLE #temp(id INT)
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
--Another way to check with an undocumented optional second parameter
IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
--Don't do this because this checks the local DB and will return does not exist
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
--unless you do something like this
USE tempdb
GO
--Now it exists again
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
--let's go back to Norhtwind again
USE Norhtwind
GO
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
now open a new window from Query Analyzer (CTRL + N) and run this code again
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
It doesn't exist and that is correct since it's a local temp table not a global temp table
Well let's test that statement
--create a global temp table
CREATE TABLE ##temp(id INT) --Notice the 2 pound signs, that's how you create a global variable
--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT '##temp does not exist!'
END
It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)
--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT '##temp does not exist!'
END
And yes this time it does exist since it's a global table
You can use IF OBJECT_ID('tempdb..#temp') IS NOT NULL
Let's see how it works
--Create table
USE Norhtwind
GO
CREATE TABLE #temp(id INT)
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
--Another way to check with an undocumented optional second parameter
IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
--Don't do this because this checks the local DB and will return does not exist
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
--unless you do something like this
USE tempdb
GO
--Now it exists again
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
--let's go back to Norhtwind again
USE Norhtwind
GO
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
now open a new window from Query Analyzer (CTRL + N) and run this code again
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
It doesn't exist and that is correct since it's a local temp table not a global temp table
Well let's test that statement
--create a global temp table
CREATE TABLE ##temp(id INT) --Notice the 2 pound signs, that's how you create a global variable
--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT '##temp does not exist!'
END
It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)
--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT '##temp does not exist!'
END
And yes this time it does exist since it's a global table
Wednesday, February 08, 2006
SQL:Format Data From Numberformat To Timeformat
Sometimes you have data that's in a number format and you need to show it in a time format. Instead of 2.25 you need to show 02:15
In that case you can use a combination of CONVERT and DATEADD
Run the examples below to see how it works
DECLARE @a DECIMAL(6,2)
SELECT @a = 3.00
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted
SELECT @a = 3.15
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted
SELECT @a = 3.25
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted
SELECT @a = 3.75
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted
In that case you can use a combination of CONVERT and DATEADD
Run the examples below to see how it works
DECLARE @a DECIMAL(6,2)
SELECT @a = 3.00
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted
SELECT @a = 3.15
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted
SELECT @a = 3.25
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted
SELECT @a = 3.75
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted
Friday, February 03, 2006
SQL Server Related Webcasts For February 2006
Microsoft has scheduled 18 new new webcasts for February, covering such topics as SQL Server 2005 Essentials: SQL/CLR, Reporting Services Basics, Using .NET Procedural Code in SQL Server 2005m Using Analysis Services, Introduction to Data Mining and more. The whole list with their start dates is listed below. Enjoy
MSDN Webcast: SQL Server 2005 Essentials: SQL/CLR (Part 1 of 14) (Level 200)
Friday, February 03, 200610:00 AM Pacific Time As the first of a 14-part series, this webcast covers what it means to be a Microsoft .NET runtime host. Topics include how Microsoft SQL Server 2005 works with the .NET 2.0 application programming interfaces (APIs) to make loading and running code safe, reliable, and scalable.
MSDN Webcast: SQL Master Class: Reporting Services Basics (Level 200)
Tuesday, February 07, 20069:00 AM Pacific Time Microsoft SQL Server Reporting Services was released in 2004 to provide developers with an easy way to extract and share information captured in a relational database. In this webcast, we explore how to create basic reports, link them to together, and provide end users with a way to get the information they need.
MSDN Architecture Webcast: Build Reliable Application Platforms with SQL Server 2005 (Level 200)
Tuesday, February 07, 20061:00 PM Pacific Time Join us to discover the many advantages of using SQL Server 2005 for application building.
MSDN Webcast: SQL Server 2005 Essentials: Using .NET Procedural Code in SQL Server 2005 (Part 2 of 14) (Level 200)
Friday, February 10, 200610:00 AM Pacific TimeThis second webcast of a 14-part series covers in detail how to write procedures, functions, and triggers using a managed language. Additionally, we discuss when to use managed code and when to use Transact-SQL.
TechNet Webcast: Introduction to SQL Server 2005 Reporting Services (Level 200)
Monday, February 13, 20069:30 AM Pacific TimeAttend this webcast to learn about the new capabilities in SQL Server 2005 Reporting Services.
TechNet Webcast: Introduction to SQL Server 2005 Analysis Services (Level 200)
Tuesday, February 14, 20069:30 AM Pacific Time This webcast shows how you can use SQL Server Analysis Services to build a highly scalable business intelligence infrastructure that supports the analytics your business needs to improve overall performance.
TechNet Webcast: Introduction to Data Mining with SQL Server 2005 (Level 200)
Thursday, February 16, 20069:30 AM Pacific TimeThis webcast introduces you to the possibilities of data mining and predictive analytics.
MSDN Webcast: SQL Server 2005 Essentials: The SqlServer Data Provider (Part 3 of 14) (Level 200)
Friday, February 17, 200610:00 AM Pacific TimeThis third webcast of a 14-part series covers the new provider, System.Data.SqlServer, and discusses how connections, variable mappings, and other functions built into Transact-SQL are handled when a Microsoft .NET language is used.
TechNet Webcast: Advanced SQL Server 2005 Reporting Services (Level 300)
Monday, February 20, 20069:30 AM Pacific Time Topics in this webcast include: how to use the built-in expression language, report parameterization (data driven, multi-valued, and hierarchical), supporting multiple data sources (including relational, multi-dimensional, and XML), and making reports interactive.
TechNet Webcast: Security in SQL Server 2005 (Level 200)
Monday, February 20, 200611:00 AM Pacific TimeThis webcast highlights security concepts that are new to Microsoft SQL Server 2005, such as encryption and user-schema separation, and looks at how SQL Server 2005 breaks security down into several distinct areas.
MSDN Webcast: Using ADO.NET with Your Lotus Notes Applications (Level 400)
Tuesday, February 21, 20068:00 AM Pacific TimeADO.NET is the standard method for Microsoft Visual Studio .NET applications to interact with data sources such as Microsoft SQL Server, the IBM DB2 Universal Database, and Oracle.
TechNet Webcast: A Technical Overview of High Availability Features in SQL Server 2005 (Level 200)
Friday, February 24, 20068:00 AM Pacific TimeThere are many barriers to achieving high availability in a database system, and only some of them can be addressed by database management systems technology. This webcast examines some of the features of Microsoft SQL Server 2005—the latest release of the SQL Server product—that can help you overcome some of these barriers.
MSDN Webcast: SQL Server 2005 Essentials (Part 4 of 14): User-Defined Types and User-Defined Aggregates (Level 200)
Friday, February 24, 200610:00 AM Pacific TimeIn this fourth module of the 14-part series, learn about using .NET objects and how you can use them inside the database.
TechNet Webcast: Introduction to SQL Server 2005 Report Builder (Level 200)
Monday, February 27, 20069:30 AM Pacific Time Learn how Microsoft SQL Server 2005 Reporting Services is meeting the demand for end-user, ad hoc reporting.
TechNet Webcast: Introduction to SQL Server 2005 Integration Services (Level 200)
Monday, February 27, 200611:30 AM Pacific Time In this webcast, we introduce you to the enterprise-class capabilities of Integration Services including dedicated management functionality, collaborative development environment, and high scalability and reliability. You also learn how you can take advantage of SQL Server 2005 Integration Services to implement data quality, data profiling, and custom transforms in your organization.
MSDN Webcast: SQL Master Class: Using Report Builder (Level 200)
Tuesday, February 28, 20069:00 AM Pacific Time Find out about the new Report Builder in Microsoft SQL Server 2005 that allows end users to customize reports to their liking.
TechNet Webcast: Advanced SQL Server 2005 Integration Services (Level 300)
Tuesday, February 28, 200611:30 AM Pacific Time This webcast presents the key architectural enhancements in SSIS that makes it extremely scalable.
MSDN Webcast: SQL Server 2005 Essentials: SQL/CLR (Part 1 of 14) (Level 200)
Friday, February 03, 200610:00 AM Pacific Time As the first of a 14-part series, this webcast covers what it means to be a Microsoft .NET runtime host. Topics include how Microsoft SQL Server 2005 works with the .NET 2.0 application programming interfaces (APIs) to make loading and running code safe, reliable, and scalable.
MSDN Webcast: SQL Master Class: Reporting Services Basics (Level 200)
Tuesday, February 07, 20069:00 AM Pacific Time Microsoft SQL Server Reporting Services was released in 2004 to provide developers with an easy way to extract and share information captured in a relational database. In this webcast, we explore how to create basic reports, link them to together, and provide end users with a way to get the information they need.
MSDN Architecture Webcast: Build Reliable Application Platforms with SQL Server 2005 (Level 200)
Tuesday, February 07, 20061:00 PM Pacific Time Join us to discover the many advantages of using SQL Server 2005 for application building.
MSDN Webcast: SQL Server 2005 Essentials: Using .NET Procedural Code in SQL Server 2005 (Part 2 of 14) (Level 200)
Friday, February 10, 200610:00 AM Pacific TimeThis second webcast of a 14-part series covers in detail how to write procedures, functions, and triggers using a managed language. Additionally, we discuss when to use managed code and when to use Transact-SQL.
TechNet Webcast: Introduction to SQL Server 2005 Reporting Services (Level 200)
Monday, February 13, 20069:30 AM Pacific TimeAttend this webcast to learn about the new capabilities in SQL Server 2005 Reporting Services.
TechNet Webcast: Introduction to SQL Server 2005 Analysis Services (Level 200)
Tuesday, February 14, 20069:30 AM Pacific Time This webcast shows how you can use SQL Server Analysis Services to build a highly scalable business intelligence infrastructure that supports the analytics your business needs to improve overall performance.
TechNet Webcast: Introduction to Data Mining with SQL Server 2005 (Level 200)
Thursday, February 16, 20069:30 AM Pacific TimeThis webcast introduces you to the possibilities of data mining and predictive analytics.
MSDN Webcast: SQL Server 2005 Essentials: The SqlServer Data Provider (Part 3 of 14) (Level 200)
Friday, February 17, 200610:00 AM Pacific TimeThis third webcast of a 14-part series covers the new provider, System.Data.SqlServer, and discusses how connections, variable mappings, and other functions built into Transact-SQL are handled when a Microsoft .NET language is used.
TechNet Webcast: Advanced SQL Server 2005 Reporting Services (Level 300)
Monday, February 20, 20069:30 AM Pacific Time Topics in this webcast include: how to use the built-in expression language, report parameterization (data driven, multi-valued, and hierarchical), supporting multiple data sources (including relational, multi-dimensional, and XML), and making reports interactive.
TechNet Webcast: Security in SQL Server 2005 (Level 200)
Monday, February 20, 200611:00 AM Pacific TimeThis webcast highlights security concepts that are new to Microsoft SQL Server 2005, such as encryption and user-schema separation, and looks at how SQL Server 2005 breaks security down into several distinct areas.
MSDN Webcast: Using ADO.NET with Your Lotus Notes Applications (Level 400)
Tuesday, February 21, 20068:00 AM Pacific TimeADO.NET is the standard method for Microsoft Visual Studio .NET applications to interact with data sources such as Microsoft SQL Server, the IBM DB2 Universal Database, and Oracle.
TechNet Webcast: A Technical Overview of High Availability Features in SQL Server 2005 (Level 200)
Friday, February 24, 20068:00 AM Pacific TimeThere are many barriers to achieving high availability in a database system, and only some of them can be addressed by database management systems technology. This webcast examines some of the features of Microsoft SQL Server 2005—the latest release of the SQL Server product—that can help you overcome some of these barriers.
MSDN Webcast: SQL Server 2005 Essentials (Part 4 of 14): User-Defined Types and User-Defined Aggregates (Level 200)
Friday, February 24, 200610:00 AM Pacific TimeIn this fourth module of the 14-part series, learn about using .NET objects and how you can use them inside the database.
TechNet Webcast: Introduction to SQL Server 2005 Report Builder (Level 200)
Monday, February 27, 20069:30 AM Pacific Time Learn how Microsoft SQL Server 2005 Reporting Services is meeting the demand for end-user, ad hoc reporting.
TechNet Webcast: Introduction to SQL Server 2005 Integration Services (Level 200)
Monday, February 27, 200611:30 AM Pacific Time In this webcast, we introduce you to the enterprise-class capabilities of Integration Services including dedicated management functionality, collaborative development environment, and high scalability and reliability. You also learn how you can take advantage of SQL Server 2005 Integration Services to implement data quality, data profiling, and custom transforms in your organization.
MSDN Webcast: SQL Master Class: Using Report Builder (Level 200)
Tuesday, February 28, 20069:00 AM Pacific Time Find out about the new Report Builder in Microsoft SQL Server 2005 that allows end users to customize reports to their liking.
TechNet Webcast: Advanced SQL Server 2005 Integration Services (Level 300)
Tuesday, February 28, 200611:30 AM Pacific Time This webcast presents the key architectural enhancements in SSIS that makes it extremely scalable.
Thursday, February 02, 2006
Varchar To Varbinary And Vice Versa
I have received an email from someone in Taiwan who looked at the contact me blog entry and wanted to know how I ‘encrypted’ my email address like that. Well there really is not much to it, you just convert the email string to varbinary. Then you convert it back to varchar to get the string back
For example if my email address is abc@qwerty.NotCom then you would do
SELECT CONVERT(VARBINARY(17),'abc@qwerty.NotCom')
This would return 0x616263407177657274792E4E6F74436F6D
Then you do
SELECT CONVERT(VARCHAR(17),0x616263407177657274792E4E6F74436F6D)
to see the string again
Of course you will have to increase the size of the varchar and the varbinary field if it exceeds 17 bytes
For example if my email address is abc@qwerty.NotCom then you would do
SELECT CONVERT(VARBINARY(17),'abc@qwerty.NotCom')
This would return 0x616263407177657274792E4E6F74436F6D
Then you do
SELECT CONVERT(VARCHAR(17),0x616263407177657274792E4E6F74436F6D)
to see the string again
Of course you will have to increase the size of the varchar and the varbinary field if it exceeds 17 bytes
Top 5 SQL Server Posts for January 2006
Below are the top 5 posts according to Google Analytics for the month of January
SQL Server 2005 Free E-Learning
Feature Pack for Microsoft SQL Server 2005
Fast Date Ranges Without Loops In SQL Server 2000
Find all Primary and Foreign Keys In A Database
SQL Server 2005 Free E-Learning
Feature Pack for Microsoft SQL Server 2005
Fast Date Ranges Without Loops In SQL Server 2000
Find all Primary and Foreign Keys In A Database
Top SQL Server Google Searches For January 2006
These are the top SQL Searches on this site for the month of December. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...
MS SQL zero fill
"Trouble Checker" "SQL Server"
sql server management studio
auto increment
SQL books
"dts step by step"
SSPI
sp_removedbreplication
sql server
SQl server books
triggers
sql login
sql security
I decided to blog about some of these searches, I see a lot of searches for books nowadays and also for login problems. I did create 2 posts about the login problems so that's covered for now. I will feature more books from now on
MS SQL zero fill
"Trouble Checker" "SQL Server"
sql server management studio
auto increment
SQL books
"dts step by step"
SSPI
sp_removedbreplication
sql server
SQl server books
triggers
sql login
sql security
I decided to blog about some of these searches, I see a lot of searches for books nowadays and also for login problems. I did create 2 posts about the login problems so that's covered for now. I will feature more books from now on
SQL Server Site Geo Map For January 2006
These are the countries where the bulk of the visitors for this blog are coming from. As you can see most of the visitors are from the US and Europe, Asia is in third place. When I look at the maps day by day I also see some visitors from Africa and the Middle East but you need more than one visitor per city in order for it to show up in the monthly map.
Wednesday, February 01, 2006
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
I am seeing a lot of searches on this blog or reaching this blog from MSN/Google with this search “SQL 2005 Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.” So this is what causes the problem: your SQL Server has been setup with windows authentication only. In order to make it mixed mode authentication right click on the server name in enterprise manager select properties and click on the security tab. Select SQL server and Windows Authentication mode(see picture…) and that will fix it The cool thing about SQL server 2005 is that you can script this out so that you can run the code on multiple servers instead of going to all the servers and clicking etc. Just click on script and code like the one below will be generatedUSE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO
For the SQL server 2000 version go here
Monday, January 30, 2006
NULL Trouble In SQL Server Land
I am seeing a lot of searches for SQL + Nulls from this site so I decided to blog about it
Before I start I would like to point out that all the code will behave this way if ANSI_NULLS is set to on ,not to off
CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)
CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)
--We get back value 1 here
SELECT * FROM testjoin WHERE ID IN(SELECT ID FROM testnulls)
--Nothing is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls)
--Value 3 is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls WHERE ID IS NOT NULL)
--value 3 is returned
SELECT * FROM testjoin j
WHERE NOT EXISTS (SELECT n.ID
FROM testnulls n
WHERE n.ID = j.ID)
--value 3 is returned
SELECT j.* FROM testjoin j
LEFT OUTER JOIN testnulls n ON n.ID = j.ID
WHERE n.ID IS NULL
--a count of 3 is returned
SELECT COUNT(*) FROM testnulls
-- a count of 2 is returned, the count ignores NULL values
SELECT COUNT(id) FROM testnulls
--By using coalesce the count is also 3
SELECT COUNT(COALESCE(id,0)) FROM testnulls
--all 3 rows are returned
SELECT * FROM testnulls
-- 1 row is returned
SELECT * FROM testnulls
WHERE ID = 1
-- only 1 row is returned the row with the NULL value is ignored
SELECT * FROM testnulls
WHERE ID <> 1
-- Now both rows that are not 1 are returned
SELECT * FROM testnulls
WHERE ID <>1
OR ID IS NULL
-- Now both rows that are not 1 are returned also
SELECT * FROM testnulls
WHERE COALESCE(ID,0) <> 1
Some more NULL Fun
You can''t compare NULL with anything
Since both values are unknown even a comparison with another NULL is unknown
DECLARE @v INT
DECLARE @v2 INT
SELECT @v =NULL,@v2 = NULL
IF @v = @v2
SELECT 'yes'
ELSE
SELECT 'No'
Be carefull with forgetting to initialize parameters while building string
DECLARE @SQL VARCHAR(500)
DECLARE @Id INT
SELECT @SQL = 'SELECT * FROM testnulls
WHERE ID =' + CONVERT(VARCHAR,@Id)
SELECT @SQL
Before I start I would like to point out that all the code will behave this way if ANSI_NULLS is set to on ,not to off
CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)
CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)
--We get back value 1 here
SELECT * FROM testjoin WHERE ID IN(SELECT ID FROM testnulls)
--Nothing is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls)
--Value 3 is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls WHERE ID IS NOT NULL)
--value 3 is returned
SELECT * FROM testjoin j
WHERE NOT EXISTS (SELECT n.ID
FROM testnulls n
WHERE n.ID = j.ID)
--value 3 is returned
SELECT j.* FROM testjoin j
LEFT OUTER JOIN testnulls n ON n.ID = j.ID
WHERE n.ID IS NULL
--a count of 3 is returned
SELECT COUNT(*) FROM testnulls
-- a count of 2 is returned, the count ignores NULL values
SELECT COUNT(id) FROM testnulls
--By using coalesce the count is also 3
SELECT COUNT(COALESCE(id,0)) FROM testnulls
--all 3 rows are returned
SELECT * FROM testnulls
-- 1 row is returned
SELECT * FROM testnulls
WHERE ID = 1
-- only 1 row is returned the row with the NULL value is ignored
SELECT * FROM testnulls
WHERE ID <> 1
-- Now both rows that are not 1 are returned
SELECT * FROM testnulls
WHERE ID <>1
OR ID IS NULL
-- Now both rows that are not 1 are returned also
SELECT * FROM testnulls
WHERE COALESCE(ID,0) <> 1
Some more NULL Fun
You can''t compare NULL with anything
Since both values are unknown even a comparison with another NULL is unknown
DECLARE @v INT
DECLARE @v2 INT
SELECT @v =NULL,@v2 = NULL
IF @v = @v2
SELECT 'yes'
ELSE
SELECT 'No'
Be carefull with forgetting to initialize parameters while building string
DECLARE @SQL VARCHAR(500)
DECLARE @Id INT
SELECT @SQL = 'SELECT * FROM testnulls
WHERE ID =' + CONVERT(VARCHAR,@Id)
SELECT @SQL
Professional SQL Server 2005 Integration Services
Professional SQL Server 2005 Integration Services has just been published by WROX, a co-worker already ordered it and I skimmed though it during lunch and ordered my own copy. I already own SQL server 2000 DTS and was very satisfied with that book. I have included 2 links to 2 sample chapters as well as a link to Amazon, so far there are 2 reviews on Amazon and both are 5 stars. The book itself is 720 pages and below is the table of contents
Foreword.
Preface.
Acknowledegements.
Author Bios.
Introduction.
1. Services.
2. Importing and Exporting Data.
3. SSIS Fundamentals.
4. Data Flow Elements and Transforms.
5. Creating Your First Package.
6. Advanced Tasks and Transforms.
7. Scripting in DTS.
8. Accessing Heterogenous Data.
9. Reliability and Scalability.
10. Understanding the Integration Services Engine.
11. Applying the Integration Services Engine.
12. DTS 2000 Migration and Metadata Management.
13. Error and Event Handling.
14. Programming and Extending SSIS.
15. Adding a User Interface to Components.
16. External Management and WMI.
17. Using SSIS with External Applications.
18. SSIS Software Development Life Cycle.
19. Case Study: Typical Relational ETL.
Index.
Chapter 1 Welcome to SQL Server Integration Services sample chapter is available here
Chapter 5 Creating an End-to-End Package is available from Brian Knight's blog
The Amazon link is here
Friday, January 27, 2006
Format SQL Server Decimal Data
Yesterday someone posted a question in the SQL server
microsoft.public.sqlserver.programming news group about formatting decimal data. The person did not want trailing zeros or a decimal point if the amount was whole
The trick to do this is convert the decimal point to a space, rtrim it and then convert the space to a decimal. When you do that the trailing decimal point will vanish. The same applies for trailing zeros of course
output
.25000
.50000
1.00000
1.50000
2.00000
3.00000
desired output
0.25
0.5
1
1.5
2
3
--first create our test data table
CREATE TABLE #testdecimals(testdata DECIMAL(20,5))
INSERT INTO #testdecimals
SELECT 0.2500 UNION ALL
SELECT 0.5000 UNION ALL
SELECT 1.0000 UNION ALL
SELECT 1.5000 UNION ALL
SELECT 2.0000 UNION ALL
SELECT 3.0000
-- regular select
SELECT testdata
FROM #testdecimals
--formatted select
SELECT REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(CONVERT(varchar,testdata),'0',' ')),' ','0'),'.',' ')),' ','.')
FROM #testdecimals
microsoft.public.sqlserver.programming news group about formatting decimal data. The person did not want trailing zeros or a decimal point if the amount was whole
The trick to do this is convert the decimal point to a space, rtrim it and then convert the space to a decimal. When you do that the trailing decimal point will vanish. The same applies for trailing zeros of course
output
.25000
.50000
1.00000
1.50000
2.00000
3.00000
desired output
0.25
0.5
1
1.5
2
3
--first create our test data table
CREATE TABLE #testdecimals(testdata DECIMAL(20,5))
INSERT INTO #testdecimals
SELECT 0.2500 UNION ALL
SELECT 0.5000 UNION ALL
SELECT 1.0000 UNION ALL
SELECT 1.5000 UNION ALL
SELECT 2.0000 UNION ALL
SELECT 3.0000
-- regular select
SELECT testdata
FROM #testdecimals
--formatted select
SELECT REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(CONVERT(varchar,testdata),'0',' ')),' ','0'),'.',' ')),' ','.')
FROM #testdecimals
Use XP_CMDSHELL To Get All File Names With Size In A Directory
To get all the files with their filesize in a directory use xp_cmdshell with a temporary table
After the temporary table is populated you will be able to sort the result set any way you like
CREATE TABLE #tempList (Files VARCHAR(500))
INSERT INTO #tempList
EXEC MASTER..XP_CMDSHELL 'dir c:\ '
--delete all directories
DELETE #tempList WHERE Files LIKE '%<dir>%'
--delete all informational messages
DELETE #tempList WHERE Files LIKE ' %'
--delete the null values
DELETE #tempList WHERE Files IS NULL
--get rid of dateinfo
UPDATE #tempList SET files =RIGHT(files,(LEN(files)-20))
--get rid of leading spaces
UPDATE #tempList SET files =LTRIM(files)
--split data into size and filename
SELECT LEFT(files,PATINDEX('% %',files)) AS Size,
RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FileName
FROM #tempList
After the temporary table is populated you will be able to sort the result set any way you like
CREATE TABLE #tempList (Files VARCHAR(500))
INSERT INTO #tempList
EXEC MASTER..XP_CMDSHELL 'dir c:\ '
--delete all directories
DELETE #tempList WHERE Files LIKE '%<dir>%'
--delete all informational messages
DELETE #tempList WHERE Files LIKE ' %'
--delete the null values
DELETE #tempList WHERE Files IS NULL
--get rid of dateinfo
UPDATE #tempList SET files =RIGHT(files,(LEN(files)-20))
--get rid of leading spaces
UPDATE #tempList SET files =LTRIM(files)
--split data into size and filename
SELECT LEFT(files,PATINDEX('% %',files)) AS Size,
RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FileName
FROM #tempList
Wednesday, January 25, 2006
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2000
I am seeing a ton of searches on this blog or reaching this blog from MSN/Google with this search “Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.” So this is what causes the problem: your SQL Server has been setup with windows authentication only. In order to make it mixed mode authentication right click on the server name in enterprise manager select properties and click on the security tab. Select SQL server and Windows (see picture…) and that will fix itI will monitor the searches and write more stuff about things that people are searching for.
For the SQL server 2005 version go here
Subscribe to:
Posts (Atom)