Friday, April 28, 2006

Sometimes you deal with vendors, customers or agencies and you have to do a file exchangeOf course the format of these files is always dictated by these people so what do you do when a CityStateZip column contains values like “Long Island City, NY 10013” or like “Princeton,NJ 08536 “? But in your Database it is normalized of course, and you have 3 columns. You will have to use a combination of LEFT, LTRIM, SUBSTRING, REPLACE and RIGHT. I am taking into account that there could be spaces in the column or even spaces in the name (New York City) So let’s get started and see what we get

Tuesday, April 25, 2006

SQL Server 2005 SP1 Books Online (April 2006) is available for downloadDownload an updated version of Books Online for Microsoft SQL Server 2005. Books Online is the primary documentation for SQL Server 2005. The April 2006 update to Books Online contains new material and fixes to documentation problems reported by customers after SQL Server 2005 was released. Refer to "New and Updated Books Online Topics" for a list of topics that are new or updated in this version. Topics with significant updates have a Change History table at the bottom of the topic that summarizes the changes. Beginning with the April 2006 update, SQL Server 2005 Books Online reflects product upgrades included in SQL Server 2005 Service Pack 1 (SP1).

Monday, April 24, 2006

One of the biggest complaints of people who moved from SQL server 2000 to SQL Server 2005 is the inability to script multiple objects. Well that feature is still available only it’s kind of hiddenClick the Jobs or Stored Procedures folder in SQL Server Management Studio, and then hit the F7 key; this will bring up the Summary pane. Highlight all the Jobs or Stored Procedures that you want to script using a combination of Shift and Ctrl keys, then right click, Script Job/Stored Procedure as..., and then choose where to save this script to. An image of how to script Stored Procedures is below

Wednesday, April 19, 2006

Microsoft Corp. today announced the availability of Microsoft® SQL Server™ 2005 Service Pack 1 (SP1), the product’s first major milestone since the launch of SQL Server 2005 only a few months ago. The release follows the remarkable reception, momentum and feedback offered by customers and partners in 92 countries. This has informed and accelerated Microsoft’s multiyear vision of Your Data, Any Place, Any Time. Microsoft SQL Server 2005 SP1 includes production-ready Database Mirroring functionality and the new SQL Server Management Studio Express, along with additional updates to SQL Server 2005 Express Edition targeted at users such as independent software vendors (ISVs) wanting to take advantage of greater functionality.

SP1 delivers production-ready Database Mirroring functionality for continuous availability. This complements the existing Always On Technologies in SQL Server 2005 such as failover clustering, database snapshots, snapshot isolation and log shipping. Since launch, Database Mirroring has been extensively tested by Microsoft and its thriving customer community to help ensure that it provides the high availability necessary for the largest customer deployments. Today more than 20 SQL Server customers have deployed Database Mirroring into production

SP1 extends business insight to smaller-scale customers and ISVs using the free SQL Server 2005 Express Edition, which includes SQL Server Reporting Services (SSRS) functionality, Full Text Search and the newly released SQL Server Management Studio Express. This optional set of capabilities is already receiving positive feedback from Microsoft’s ISV partners

SP1 also advances dynamic applications with the new SQL Server Management Studio Express, a simplified graphical management environment for SQL Server Express Edition. This tool builds on Microsoft’s commitment to extending the breadth of the SQL Server family to support all the data storage needs of Microsoft’s customers. It also complements the recently announced SQL Server Everywhere Edition, a lightweight and rich subset of capabilities found in other SQL Server editions, targeted for application embedded storage on clients. SQL Server Everywhere Edition is targeted to be available as a Community Technology Preview (CTP) this summer and released by year end.

For end-to-end business insight, SP1 extends SSRS to support enterprise reporting on SAP NetWeaver Business Intelligence with two new components in SP1: a Microsoft .NET Data provider for SAP NetWeaver Business Intelligence and a new MDX Query Designer. This new functionality extends the benefits of enterprise reporting with SQL Server by enabling SAP customers to easily create and manage reports on information inside any SAP BW data warehouse

How did I miss this? SQL Server MVP Itzik Ben-Gan has published his latest book: Inside Microsoft SQL Server 2005: T-SQL Querying. For all of you who read SQL Server magazine you probably know Itzik from his great SQL tips and puzzles articles.Take a detailed look at the internal architecture of T-SQL—and unveil the power of set-based querying—with comprehensive reference and advice from the experts. Database developers and administrators get best practices, sample databases, and code to master the intricacies of the programming language—solving complex problems with real-world solutions.

Discover how to:

•Understand logical and physical query processing•Apply a methodology to optimize query tuning•Solve relational division problems•Use CTEs and ranking functions to simplify and optimize solutions•Aggregate data with various techniques, including tiebreakers, pivoting, histograms, and grouping factors•Use the TOP option in a query to modify data•Query specialized data structures with recursive logic, materialized path, or nested sets solutions•PLUS—Improve your logic and get to the heart of querying problems with logic puzzles

Tuesday, April 18, 2006

This is a question that came up yesterday in the Getting started with SQL Server MSDN forum (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=353250&SiteID=1)A person wanted to use a MID function in SQL Server, There is no MID function in SQL Server but there are at least 4 ways to implement what the person tried to accomplishBasically if the data looked like this

aaa-bbbbbppppp-bbbbbzzzz-xxxxx

Then we need to grab everything up until the minus signSo let's get started

Monday, April 17, 2006

You want to add a new user with read and write access and also the ability to execute all user defined functions but you don't want to make the user a db_owner. The code below will do a GRANT EXECUTE/SELECT for all the user defined functions in the DB If the user defined function is a table-valued function then you need to grant select permissions otherwise you need to grant execute permissions

Right now this code prints the GRANT EXECUTE/SELECT statements, change the PRINT to EXEC if you want it to be done automatically

--Grab all the functions for the current DB SELECT IDENTITY(INT,1,1) AS ID,SPECIFIC_NAME,DATA_TYPEINTO #FunctionListFROM INFORMATION_SCHEMA.ROUTINES --Only ProcsWHEREOBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0AND ROUTINE_TYPE='FUNCTION'ORDER BY SPECIFIC_NAME

DECLARE@Loopid INT,@MaxId INT,@UserName VARCHAR(50)

--This is the user that will get the execute/select permissionsSELECT @UserName = 'SomeUser'

Thursday, April 13, 2006

You want to add a new user with read and write access and also the ability to execute all stored procedures but you don't want to make the user a db_owner.The code below will do a GRANT EXECUTE for all the procedures in the DBThis line will skip those dt_ procedures that are in every databaseWHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0

Right now this code prints the GRANT EXECUTE statements, change the PRINT to EXEC if you want it to be done automatically

--Grab all the procedures for the current DB SELECT IDENTITY(INT,1,1) AS ID,SPECIFIC_NAMEINTO #ProcedurelistFROM INFORMATION_SCHEMA.ROUTINES --Only ProcsWHEREOBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0 AND ROUTINE_TYPE='PROCEDURE'ORDER BY SPECIFIC_NAME

DECLARE@Loopid INT,@MaxId INT,@UserName VARCHAR(50)

--This is the user that will get the execute permissionsSELECT @UserName = 'SomeUser'

Wednesday, April 12, 2006

Not everyone uses Visual Source Safe, CVS or Subversion to keep track of proc changes/deletions and/or additionsOf course you could use Red-Gate SQL Compare (I do) But let's say you don't have any of these tools and are using SQL Server 2005, what else can you do?In SQL Server 2000 you can useselect ROUTINE_DEFINITION,SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES to get the body of the stored procedure, the caveat is that this will only return 4000 characters.Another way is to use the sp_helptext procedureIn SQL server 2005 this is much easier. There is a new function in town: OBJECT_DEFINITION()

OBJECT_DEFINITION() does return the whole body of a stored procedureBelow is some code (very simple) that will give you an idea of how you could use OBJECT_DEFINITION() to keep track of changesYou will have to setup a job that runs once a day and stores the definition of all the procedures in a tableThen you can do a self join on that table to find added, deleted and changed proceduresYou can run the code below in 1 shot if you want

USE masterGO--Let's Create a New DatabaseCREATE DATABASE TestProcCodeGO

Monday, April 10, 2006

Today someone posted a question on the Tek-Tips Forums web siteThe queston was how not to insert rows with a NULL value in a certain columnThe answer is of course INSERT INTO Table2 SELECT * FROM table WHERE column IS NOT NULLThis person replied that rows where the column is null are still being insertedI turned out that there was data that had NULL character values 'NULL'When you run SELECT NULL,'NULL' in Query Analyzer or SQL Server Managment Studio this looks identical so it's very easy to think that there is something else going on

Friday, April 07, 2006

Paul Flessner Senior Vice President of Microsoft Corporation has posted a SQL Server 2005 update hereAm I the only one who thinks that the name "SQL Server Everywhere Edition" is very close to "Sybase SQL Anywhere"?I can see the jokes already "Not only does SQL server have the same codebase (not true) the name is also ripped off"

This is what Paul has to say about SQL Server Everywhere Edition "This new offering for storage on clients of all types will provide a lightweight, compact, but rich subset of the capabilities found in other SQL Server editions. Beyond having rich local data management capabilities, SQL Server Everywhere Edition will also include support for seamlessly synchronizing with other SQL Server editions and provides features that promote building rich client applications that operate effectively in today’s increasingly “occasionally connected” environment. SQL Server Everywhere Edition also shares a common programming model with the other SQL Server editions, enabling developers to transfer skills and knowledge quickly and easily. We expect to ship the first CTP of SQL Server Everywhere Edition this summer, with the goal of final release before the end of this calendar year."

Wednesday, April 05, 2006

As promised here is a left join blog post. What is a left (outer) join? This is what Books On Line has as the left join description “The Left Outer Join logical operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.”So let’s see how that works

--Return all the people without an orderSELECT c.*,o.*FROM #Customer cLEFT JOIN #Order o ON c.CustomerID =o.CustomerIDWHERE o.CustomerID IS NULL

--Left Join, CustomerID 2 is not returned because the condition is in the WHERE clause--The WHERE clause is applied to the #Order table instead of the #Customer tableSELECT c.*,o.*FROM #Customer cLEFT JOIN #Order o ON c.CustomerID =o.CustomerIDWHERE o.CustomerID < 3

--Left Join, CustomerID 2 is returned (as well as 3) because the condition is in the AND clauseSELECT c.*,o.*FROM #Customer cLEFT JOIN #Order o ON c.CustomerID =o.CustomerIDAND o.CustomerID < color="#009900">

It's always interesting to see what people are searching for, I left the money laundry one in this list because I thought it was kind of amusingI always like to look at this list so that I know what people are interested in and I can write a little thing about itSo tomorrow I will cover LEFT JOIN

Addison Wesley Professional has published their latest SQL Server 2005 book "SQL Server 2005 Distilled"

Book Description

Need to get your arms around Microsoft SQL Server 2005 fast, without getting buried in the details? Need to make fundamental decisions about deploying, using, or administering Microsoft’s latest enterprise database?

Need to understand what’s new in SQL Server 2005, and how it fits with your existing IT and business infrastructure? SQL Server 2005 Distilled delivers the answers you need–quickly, clearly, and objectively.

Architectural overview: how SQL Server 2005’s features work together and what it means to you Security management, policies, and permissions: gaining tighter control over your data SQL Server Management Studio: Microsoft’s new, unified tool suite for authoring, management, and operations Availability enhancements: online restoration, improved replication, shorter maintenance/recovery windows, and more Scalability improvements, including a practical explanation of SQL Server 2005’s complex table partitioning feature Data access enhancements, from ADO.NET 2.0 to XML SQL Server 2005’s built-in .NET CLR: how to use it, when to use it, and when to stay with T-SQL Business Intelligence Development Studio: leveraging major improvements in reporting and analytics Visual Studio integration: improving efficiency throughout the coding and debugging process Simple code examples demonstrating SQL Server 2005’s most significant new features

I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you"