Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Share tech news, updates, or what's on your mind.

Sign up to Post

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
4
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Good Article and very well presented. Gets my YES vote :)
0
 

Expert Comment

by:Abrienne Jonethan
Comment Utility
Very Good and Informative Article,
I have also gone through other blog as there are other ways to reset a Lost SA Password in Microsoft SQL Server.
Apart from recovering from manual way you can also use free software like SQL Password Recovery
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 4
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
4
 
LVL 10

Expert Comment

by:Jason clark
Comment Utility
Thanks for sharing this helpful article. But in my scenario i didn't have any recent backup of my deleted records also the recovery mode is set to simple. Then I tried SysTools SQL Log Analyzer and successfully recovered deleted records via SQL Transaction log file. it's really helpful if you don't have any backup of your database.
1
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and will then append that to the file name.
0
 

Expert Comment

by:Shaad Parihar
Comment Utility
The above code is for one database.
If i have multiple databases and i want to use a single batch file.how i add multiple database in the code.
0
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
4
 
LVL 7

Expert Comment

by:Yashwant Vishwakarma
Comment Utility
Nice & informative article Vitor !!!
Voted as Good Article :)
0
 
LVL 51

Author Comment

by:Vitor Montalvão
Comment Utility
Thank you Yashwant
0
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
8
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Voted Yes.
0
 
LVL 7

Expert Comment

by:Yashwant Vishwakarma
Comment Utility
Nice Explanation !!!
0
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
2
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Nicely done.  Voted Yes.
0
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within REPLACE functions). Over time this can be quite unreadable. It can also become a maintenance chore where this is not a single one-off effort.

This happened in a recent question here on EE. I'd like to share the solution that came out of that thread. I have written a function called udf_CleanDataFn. It can be invoke within any query as follows:
 
SELECT dbo.udf_CleanDataFn(YourColumn, @Options)

@Options is a varchar(255) field, which can accept any of the following "options":
 
  • ltrim - This will left-trim the value in YourColumn.
  • rtrim - This will right-trim the value in YourColumn.
  • trim - This will both left- and right-trim the value.
  • nocomma - This will remove any and all comma's.
  • nospace - This will remove any and all spaces.
  • nopunc - This will remove all standard punctuations (!@#$% etc)
  • nonum - This will remove all numbers (0-9).
  • noalpha - This will remove all alpha characters (A-Z and a-z).
  • alphaonly - This will remove everything except alpha characters.
  • numonly - This will remove everything except numbers.

Following are a few working examples that will demonstrate the different results . This first example takes out only the commas:
 
SELECT dbo.udf_CleanDataFn('This !  is *  a  13,21  @  test', 'nocomma')
Result:
1
 

Expert Comment

by:Nick Sawe
Comment Utility
ok thanks, i will give it a go. thanks for sharing this by the way !
0
 
LVL 20

Author Comment

by:dsacker
Comment Utility
You're welcome. If it proves helpful, please click the "Helpful" button. :)
0
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
 

The problem is that RFC822 date formats do not always fit within SQL Server's valid date formats. The CONVERT/CAST functions within SQL Server will only convert a limited variety of date formats. When trying to convert RFC822 date formats, some of the RFC822 accepted formats will result in the well-known T-SQL error:
 
Msg 241, Level 16, State 1, Line 1 
Conversion failed when converting date and/or time from character string.

According to the RFC822: Standard for ARPA Internet Text Messages, as long as a date adheres to the constraints in section 5 of the RFC822 protocols, it is a valid RFC822 date. Many RFC822 Dates fail to convert into a T-SQL datetime format.

For instance, the following is a valid RFC822 date:
 
Wed, 27 Sep 2006 21:49:19 GMT

This format (and a number of other RFC822 formats) will not convert to a datetime type. In addition SQL Server does not inherently recognize nor convert time zone codes. (See footnote 1 at the end of this article for a list of valid RFC822 date formats.)

I have written a function called …
1
 
LVL 20

Author Comment

by:dsacker
Comment Utility
Appreciate that. And thanks to Eric. He gave me some good pointers along the way.
0
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
I ran the code blocks and the result was excellent.  Nice job.
0
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties screen, Security tab:
SQLAuthenticationMode.PNGBy default Windows Authentication mode is selected since is the one recommended by Microsoft and also the one that is more secure because it uses Windows Security tokens so no passwords will be passed through the network when connecting to the SQL Server instance. It is also the one that is more easy to use since the user doesn't need to remember one more login name and password and it is more easy for maintenance since it will be managed by the Active Directory (AD) so it will respect the user AD policies.
The creation of a Windows authentication login is as simple as providing the domain and user name as the Login name:
DomainLogin.PNGThe same can me performed using the following T-SQL command:
CREATE LOGIN [MyDomain\DomainUserName] FROM WINDOWS

Open in new window

In Windows authentication mode the sa login is disabled so ensure that someone else has the sysadmin role.


SQL Server logins are managed inside the SQL Server engine. The passwords are stored in the SQL Server master database and when connecting to the SQL Server instance the user credential is passing through the network. So, when creating a SQL Server authentication login a login name and password need to be provided:
MSSQLLogin.PNGThe same can me performed using the following T-SQL command:


Open in new window

6
 
LVL 51

Author Comment

by:Vitor Montalvão
Comment Utility
Submitted
0
by Mark Wills

Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simply new to SQL Server then there are a few concepts we can share to make your life easier.

Based on the frequency of questions we see in Experts-Exchange, I thought a 'light' discussion about the Transaction Log would be a good place to start.

Microsoft's SQL Server is a great database. Generally, it can look after itself, but there are one or two aspects that will need your attention. You can interactively access, configure, manage, administer, and develop your databases using SQL Server Management Studio (SSMS). With SSMS, the database administrator (i.e., you) can administer any of the components that we will be discussing.  Now, this article is not an introduction into SSMS, but there are a couple of tools you will need to know. For managing your database, in SSMS, use the Object explorer to right click on your database and then Properties for a range of settings and information, or Tasks to shrink or backup files. Also in Object Explorer is the Management group, expand that for Maintenance plans.

A SQL Database is made up of three types of files, the two main files are primary data file with the extension of MDF and the transaction log file with the extension of LDF. The third is an optional secondary data file and …
26
 
LVL 51

Author Comment

by:Mark Wills
Comment Utility
It is a little pedantic, but if it is the difference between understanding a very import differentiator, then it is an important addition.

I am thinking that a seperate paragraph is required. then again, our comments are not "private", so the thread does provide that clarity.

The more important factor is, if running a "FULL" recovery model, then you really have to do transaction log backups, otherwise your transaction logs will continue to grow. So, your transaction log really must be managed by doing those transaction log backups. It is not just for recoverability, backups are an important part of database health. Of course running a "FULL" recovery model is the reason in the first place to give you the highest possible mode for recoverability. Bit of a catch-22 in some regards.
0
 
LVL 7

Expert Comment

by:Yashwant Vishwakarma
Comment Utility
Nice share!!! Voted Yes :-)
Waiting for more from you in future.....
0
Optimize your web performance
LVL 1
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based developed using Microsoft technologies – classic ASP, ASP.Net, VB.net and SQL Server 2005.

Basic requirements:
The key aspects to be considered while moving to multi-tenant data architecture for the application are given below.
Firstly, data isolation should be maintained between different tenants. This is the basic concept of multi-tenancy where data of one tenant should not be available to other tenants.
Secondly, we need to consider some data statistics in the application. Data is inserted into primary tables every 20 seconds and there could be around 100,000 rows per day in each of these tables. In future, there should be provision for data to be added to these tables every 4 seconds. There are around 20-25 tables per tenant and around 5 common tables.
Finally, one of the most important requirements is that changes should be done with no impact to infrastructure as it would mean additional costs.

Multi-tenant data architecture:
With SQL Server (as this is the database being used), the approaches to implement multi-tenancy are Separate Database, Shared Database Separate Schema and Share Database Shared Schema (Refer to Multi-Tenant Data Architecture from MSDN
2
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case.

Let us try to examine for ourselves with an example.

To get started, use the following script, which I ran on my sever to create a table OrderTest in database TempDB and, additionally, to insert 4 rows into the table.
USE TempDB 
GO
CREATE TABLE OrderTest(N1 INT NOT NULL, C1 VARCHAR(100) NOT NULL)
GO 
INSERT INTO OrderTest(N1, C1) VALUES(1, 'First Insert') 
GO 
INSERT INTO OrderTest(N1, C1) VALUES(10, 'Second Insert') 
GO 
INSERT INTO OrderTest(N1, C1) VALUES(2, 'Third Insert') 
GO 
INSERT INTO OrderTest(N1, C1) VALUES(4, 'Fourth Insert') 
GO 

Open in new window


We are ready for the test.  Let us select data from the table.
USE TempDB 
GO
SELECT * FROM OrderTest 
GO

Open in new window

TestOutput1The results are indeed retrieved in the order in which it is inserted.  Convinced?

Now I am going to add a primary key to the table.
USE TempDB 
GO
ALTER TABLE OrderTest ADD PRIMARY KEY(N1)
GO 

Open in new window


With the primary key in place, let us once again select data from the table using the previous select statement.
USE TempDB 
GO
SELECT * FROM OrderTest 
GO

Open in new window

TestOutput2The rows are NOT returned in the order in which it is inserted!!  Surprising??

What made the difference?  

In the first case, the SQL Server query optimizer decided to do a table scan while it opted for a Clustered Index Scan in the latter case.   See the following execution plans for an illustration.
 ExecutionPlan1 ExecutionPlan2
The verdict.

SQL server does not guarantee any order of output (unless you explicitly append an ORDER BY clause to the query to order your output in a desired way added).  Further, it is the SQL Server query optimizer that decides how the data should be retrieved.  Hence never assume that data will be returned in the order in which it is inserted.
4
INTRODUCTION:

While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a single stored procedure that periodically and automatically takes a snapshot of all your objects and archives them. At any point in time, you can see the history for a given database object, and it makes it trivial to restore a previous version of an altered or dropped stored procedure.

This is accomplished using a stored procedure (provided in full at the end of the article) that's created in the master database, and then scheduled to run at whatever interval you choose - anywhere from once/minute to once/week, or anywhere in between.

This Stored Procedure goes through the following steps:

1

If the Master.dbo.coSourceControl table (used to store the history) doesn't exist, it creates it

2

For each database on the server (so new databases are added automatically), it performs steps 3-5:

3

It grabs the text contents of all the user objects (not flagged as "IsMsShipped")

4

Compares the contents of each to the last known copy (if there is one)

5

If the object is new or has changed, add a new copy to the source control table in master

6

At the end, it will output the number of objects updated across all databases
The history is kept in a …
2

0. Introduction

When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database.
Problems start when you try to combine that with some "user input" passed using parameters, usually in conjunction with stored procedures.

For simplifying I won't refer to a Stored Procedure in the below examples, instead use some script that could run "on it's own".

So, consider this rough setup:
A SQL 2005+ instance/database named SQL_DB
Some (remote) Oracle (or whatever type) database named REMOTE_DB
A linked server configured on the MS SQL 2005+ instance to that remote db, named LINKED_DB
On that (remote) database, a table named TEST.ACCOUNT, with a couple of colums with all possible data types
I assume that the reader knows how to setup a Linked Server, otherwise you go to Creating Linked Servers


1. Usual techniques

There are several techniques to query the remote table, for example, to get the balance from the account 12345:
SELECT balance FROM LINKED_DB..TEST.ACCOUNT WHERE id = 12345

Open in new window

or, with the parameter method:
declare @id int 
set @id = 12345
SELECT balance FROM LINKED_DB..TEST.ACCOUNT WHERE id = @id

Open in new window

Syntax note: LINKED_DB..TEST.ACCOUNT is the so-called four-parts name, build as: <linked_server_name>.<database_name>.<schema_name>.<table_name>.
For some linked server drivers or providers, the database_name is not needed or even not supported, and the schema_name might not be needed either, so if the linked server is configured to connect with TEST login, you might even write:
SELECT balance FROM LINKED_DB...TEST_ACCOUNT

Open in new window

4
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Some eventually useful information in this context is the feature WITH RESULT SETS for the EXECUTE statement:
http://technet.microsoft.com/en-us/library/ms188332.aspx
0
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What if it has an identity column?  I've taken a few steps to make things easier for me.

First of all, this is a work in progress.  There's bound to be little bugs that I haven't worked out yet because I literally just wrote it 10 minutes ago when asked to do this.  But as updates to this happen, I will post post new iterations of "SQL Insert Generator".

Secondly, this is for SQL 2005 and later only.  Trying to do this working within the limitations of the varchar() and nvarchar() data types in SQL 2000 would have been too much of a pain in the butt.  But if you are working with fairly narrow tables then it should work in SQL 2000.  Just change the nvarchar(MAX) variables to nvarchar(4000).

IF object_id('dbo.up_GenerateInserts') IS NULL
     EXEC ('create procedure dbo.up_GenerateInserts as set nocount on')
GO

ALTER PROCEDURE dbo.up_GenerateInserts
      @TableSchema       sysname
     ,@TableName         sysname
     ,@IDField           sysname
     ,@IDMin             SQL_VARIANT = NULL
     ,@IDMax             SQL_VARIANT = NULL
     ,@IncludeIdent      BIT = 0
     ,@IncludeIfNot      BIT = 1
AS
SET NOCOUNT ON

DECLARE 
      @InsertSQL         NVARCHAR(MAX)
     ,@SelectSQL         NVARCHAR(MAX)
     ,@IFNotSQL     

Open in new window

2
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Brandon,

Thanks for sharing, this is brilliant!  I wish I had had something like this available the few times I had had to script out the complete recreation of a table, including the values :)

Patrick
0
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data.

I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from the XML document. I have used XPath queries and functions to do that in combination with dynamic code. The names of the variables are in Spanish so maybe you will suffer a little to understand the code so I will try to explain.

Because in a given XML we don't know how many elements and attributes we have or what their names are, I use the XPath local-name function to find out the name of the nodes and use the modify function to delete the current empty element or the current empty attribute. I also use dynamic code in SQL so the database engine allows me to execute the XPath sentences even if I don't know the names of the elements. The code is a little bit complex because I have used recursion to pass through the entire xml document.

The following stored procedure deletes a given empty attribute from a given element in a given XML. Note that the XML must be passed in xml type.

An empty attribute means: <Node emptyattribute=""/>

 
/******************************* spXMLDeleteEmptyAttribute *************************************************/
if exists (select * from sysobjects where id = object_id('dbo.spXMLDeleteEmptyAttribute') and type = 'P') drop procedure dbo.spXMLDeleteEmptyAttribute
GO             
CREATE 

Open in new window

1
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task design flaw duplicates have been created.

All essential business data should always be protected with constraints such as a primary key or a unique index.

In my case, one database table with the IIS logs had over a 100,000,000 rows without duplicates!  The usual solutions -- to create a temporary or duplicate table, a while loop or a dreaded resource consuming cursor -- are not always practical.

So in SQL 2005 I've found a very useful function that allowed me to delete duplicates with one query!

First off you have to determine if there are duplicates.  For these examples I'm going to create the following table:
CREATE TABLE [dbo].[Duplicates]([DuplicatesNonNull] [varchar](50) NOT NULL) ON [PRIMARY]

Open in new window


Next, I'll populate it with duplicates:
INSERT INTO Duplicates (DuplicatesNonNull) VALUES ('Duplicate1')
INSERT INTO Duplicates (DuplicatesNonNull) VALUES ('Duplicate2')
INSERT INTO Duplicates (DuplicatesNonNull) VALUES ('Duplicate3')
INSERT INTO Duplicates (DuplicatesNonNull) VALUES ('Unique1')
INSERT INTO Duplicates (DuplicatesNonNull) VALUES ('Duplicate2')
INSERT INTO Duplicates (DuplicatesNonNull) VALUES ('Duplicate1')
INSERT INTO Duplicates (DuplicatesNonNull) VALUES 

Open in new window

4
 
LVL 51

Author Comment

by:Ted Bouskill
Comment Utility
@cyberwiki: I don't mind constructive discussion,  portions of your comment was rude and yes I am going to report it to the moderators.

NOTE: In your examples you have a GUID which is unique.  In that case I wouldn't use CHECKSUM() which means I think you are missing my point.  I'm not saying the CHECKSUM() is a final solution and yes it can be abused (like anything can).

Check out my test of your example when I used them as strings (which is likely in a log) along with some other examples which is in the spirit of friendly discussion.
SELECT CHECKSUM('3DB7D309-A8F4-47C4-BA90-0CB458B44CB0', 'avb', 'BCZ', 'ckz')
UNION ALL
SELECT CHECKSUM('EFE7F2C5-19F9-42B9-9C16-21BED41E882B', 'AAE', 'BRQ', 'CAW')

Results: 
1711681669
390310668

SELECT CHECKSUM(CAST('3DB7D309-A8F4-47C4-BA90-0CB458B44CB0' AS uniqueidentifier), 'avb', 'BCZ', 'ckz')
UNION ALL
SELECT CHECKSUM(CAST('EFE7F2C5-19F9-42B9-9C16-21BED41E882B' AS uniqueidentifier), 'AAE', 'BRQ', 'CAW')

Results:
107127020
107127020

SELECT BINARY_CHECKSUM(CAST('3DB7D309-A8F4-47C4-BA90-0CB458B44CB0' AS uniqueidentifier), 'avb', 'BCZ', 'ckz')
UNION ALL
SELECT BINARY_CHECKSUM(CAST('EFE7F2C5-19F9-42B9-9C16-21BED41E882B' AS uniqueidentifier), 'AAE', 'BRQ', 'CAW')

Results:
1025885929
1023845076

Open in new window

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
@tedbilly

I believe that ciberwiki is simply trying to point out the limitations in using CHECKSUM based approach for dealing with duplicates tuples and pretty much most his comments are perfectly valid.  

Knowing these limitations does not say it is a bad solution, it simply completes it.  

No bad intent in that.

Regards...
0
Introduction
This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred during an installation I eventually completed.

The problem
All the settings for the installation were made and the installation started. The entire installation progresses correctly except at the very end it gives the following error:

error

The installation was blocked with an error that wasn't clear. Clicking the retry-button did't help so "Cancel" is was the only option. The installation rolled back and I'd lost about a 45 minutes of your time monitoring a failed installation.

Let's review the configuration of the cluster node that had this problem. The node had the following hardware.
Note: Not every item of hardware is listed only those that could affect the installation.

DL 580 G5
34 GB memory
4 x Intel Xeon E7450 (6 core) CPU
Several logical disks
Both nodes for the cluster have the same hardware specifications.

I started puzzling over what could be the problem. Did I use the wrong settings for the installation? The log files didn't give me any direction as to where the problem could be; they only showed that the installation stopped at starting the services.

In a desperate attempt to fix things, I tried to do the installation again but the same error occurred at the same point.

The solution
0
I am showing a way to read/import the excel data in table using SQL server 2005...

Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name".

Now to import this Excel data into the table, we will use the OpenRowSet method

OpenRowset contains all the information that is required to connect to remote data source.
It can also be used (alternatively) to access table from linked server

select * 
from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\temp\book1.xls', 'SELECT * FROM [Sheet1$]')

Open in new window


This will read all the data from the excel sheet 1. If it had a named worksheet, then we would need to use the actual name.

But this is an Ad hoc query statement, and so, might not be permitted to be executed by the SQL server 2005 (but enabled by default in SQL Server 2000).

So, we will need to go into SQL Server configuration and enable the Ad Hoc queries. Firstly, it is an advanced options, so we will need to enable that first, and then we can deal with enabling the adhoc query.

To configure the SQL Server for these advanced statements, execute the following T-SQL in a query window:
sp_configure 'show advanced options', 1
GO

reconfigure
GO

Open in new window


This will result in a confirmation of the configuration changes :
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

And now we have enabled the advanced options, we can now focus on the ad hoc query option. To do so, execute the following T-SQL in a query window:
sp_configure 'Ad Hoc Distributed Queries', 1
GO

reconfigure
GO

Open in new window


This will result in a confirmation of the configuration changes :
1
Visualize your virtual and backup environments
LVL 1
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Ever wondered the date a "day" first occurred?

Realize I am not talking about the beginning of time?

Use SQL Server 2005?

Then this article is for you!

In this SQL Server 2005 tip you will see how to determine the date on which a requested day of the week occurred in any month of any year.  A typical scenario would be in a calendaring system that allows you to establish meetings on the first Monday of every month.  Consequently, as this function has evolved over time, its ability now goes beyond just the first occurrence and can return the second, third, ..., or even last occurrence of a given weekday.

Since a month can start on any day of the week, having an ordinal weekday calculation is not a trivial task.  For example: the first Monday in August 2009 fell on the 3rd of August; the first Tuesday for September 2009, September 1st; the last Tuesday of September 2008, September 30th.

With that in mind, let's begin going through the sections...

What you need; what you get.
Validate input and default values just in case.
It's all in the date.
The User Defined Function Code.

1. What you need; what you get.


Given a year and month, our function will get a specific weekday occurrence using its integer day of week value (each locale may use a different @@datefirst value, but, typically for the US, Sunday is 1; Monday, 2; …
9
 
LVL 18

Expert Comment

by:Rajar Ahmed
Comment Utility
Thanks mwvisa1 . great article.
soon it may come in handy for me..!
0
 
LVL 60

Author Comment

by:Kevin Cross
Comment Utility
Glad to hear it, meeran03!
Thank you for the vote also as well as others I had not yet acknowledged. I am just happy to have helped in some way.

Best regards,
Kevin
0
Moving Database files by Offline method
-Prabhakaran
This document will show you how to move the database files without detaching the databases as we do in SQL 2000.
This method will be applicable for only SQL 2005\SQL 2008 databases

To move files, we have to specify the current logical name of the file and the new file path, which includes the new file name.
I would like to tell important point in this method we can move only once file at a time in this manner.
To move data or log files to new location, follow these steps

1.      Take a sp_helpfile output of the database, which will provide the logical file name of the data and log files.
sp_help
2.      Set  the database you want to work with offline by doing below
 ALTER DATABASE dbadb SET OFFLINE 

Open in new window

offline
3.      Move one file at a time to the new location
In Windows Explorer physically move the file to the new location, and then tell the SQL Server Database where that file now lives.
 
  ALTER DATABASE dbadb
 MODIFY FILE(NAME=dbadb,FILENAME='E:\Data\MSSQL\dbadb.mdf')
 GO 

Open in new window

modify fileYou can move only one file at a time, So if you want to move the more than one file like data and log files, first move the file using the step3 described above then repeat the same step for other files of the database.
 
4.      Set back the database to online
 
 ALTER DATABASE dbadb SET ONLINE   

Open in new window


onlineBefore bringing the database to offline there should be no user connections to database either you can do this by killing all the connections or by issueing the following statement
 
 ALTER DATABASE dbadb SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

Open in new window



 
  ALTER DATABASE dbadb SET MULTI_USER WITH ROLLBACK IMMEDIATE 

Open in new window


   
5.      Verify the move by checking sp_helpfile output of the database again and you can observe the new path.
verify
 
0
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005.

The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.
 
Recently, I was involved in a discussion as to what some of these views really do, and thought I might share some of that discussion in a relatively light hearted way. Considerably more light hearted than the aforementioned discussion...

Now, despite being able to find a lot of discussions about these views, these two in particular can cause confusion. Mainly due to the terminology used in naming these views. Notably, the words "index" and "usage" are a little ambiguous. You would think that those names imply what one would reasonably expect to get if using them, and, when you might use them. Think again.

At first glance they give information which doesn't quite make sense when compared side by side. One view says "this" many reads and the other says "that". One view has an index entry for a table and the other doesn't. Both have entries for tables that don't have indexes. So, what is going on ? Why are they so different ? Which happens to be the wrong question, the fact that the information doesn't match is a very important differentiator. Question should be "How can I take advantage of those difference".

The differences in the information from those views actually tell us important pieces of the index puzzle. Collectively, that information is very …
9
by Mark Wills

PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively easy to do, other times it can be a challenge (and that is why we are here).

Let's have a quick look at the PIVOT function...

SELECT
          <display_column_list>

FROM

          (SELECT <source_columns> as Column_Source
                         ,<column_to_be_aggregated> as Column_Value
                         ,<column_with_new_column_names> as Column_List
           FROM <datasource> ) as DataSource

PIVOT
          (<aggregate_function>(Column_Value)  FOR  Column_List  IN
          ([<new_column_1_heading>],[<new_column_2_heading>],...,[<new_column_N_heading>]) ) PivotTable

ORDER BY <column_number_or_name>;

That looks pretty straight forward, except for one or two small details:

1) First up, we need to know the <display_column_list>
    easy enough, just do a Select * instead and problem solved (except that it does control display sequence)

2) Secondly, we need to know and hard code the new column headings as in :
     ([<new_column_1_heading>],[<new_column_2_heading>],...,[<new_column_N_heading>])

And that last point is often the big challenge. Not so bad if we are doing something static like "months in a year", just list out those months in sequence, and make sure you can cast the…
32
 
LVL 51

Author Comment

by:Mark Wills
Comment Utility
Yeah, should be...

Would add in another definition for the totals (similar to @columns).

But then we can no longer simply cheat with "select * from" we would have to add in that new computed column, and probably a parameter to decide when / if to use.

If you are feeling adventurous, there is a slightly more involved Article that should be able to do that for you : http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html

Cheers,
Mark
0
 

Expert Comment

by:Wil999
Comment Utility
Thanks voor the quick reply Mark, i will look in to the article!
0
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3.

When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed by the update of the database services. After a lot of searching I found the solution to my problem.
Because I spend a lot of time figuring out why my installation failed I decided to write this article so other people won't have to spend so much time and frustration installing the service pack.
This article also covers the problem for Service Pack 2. I haven't tested Service Pack 1 with this method.

What exactly goes wrong during the update?
When I looked closely at the installation I saw that on a certain moment the setup program start some actions for services and databases. After watching the databases being created and deleted I noticed that something went wrong at the "mssqlresource" database.
The setup program tries to overwrite the "mssqlresource" database but for some reason fails what results in a rollback of that part of the installation.
 

1. Create Backups

As for any DBA you should always backup your databases before you start the installation of updates or complete service packs. In my case I didn't have many databases on my server because it was a fresh installation. I created backups of all the present databases and I copied the "master" and the "mssqlresource" to a different location.
The …
0
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):

   . Check for publisher's certificate revocation
   . Check for server certificate revocation

Close your internet browser and launch SQL Server Management Studio. Should load faster now.

Cheers
1
 
LVL 10

Expert Comment

by:oxyoo
Comment Utility
Two more things to improve startup speed slightly...

1. Start the application with the -nosplash attribute

2. Tools / Options / Environment / General
    Set the "At Startup" option to "Open empty environment"

0

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.