July 30, 2009
A Few Tips For Taking a SQL Server Database Offline
In SQL Server Management Studio (SSMS), you can right-click on a database, select "Tasks" and take the database offline.
If the database seems to "hang" in the transition for more than a minute after you do this (you may be sweating bullets by this point), try the following two things:
- Make sure you are not profiling the database.
- Restart SSMS.
These simple things may save you a lot of stress!
Posted by Bill Simpkins at 5:29 PM | Comments (0) | TrackBack
January 14, 2009
CHARINDEX ?
CHARINDEX. Yeah. That's right. It is one of my favorite SQL functions. I rarely see it used and most people I know don't even know about it. Consequently, I must blab about it a little.
CHARINDEX returns the index of a successful string match.
For example:
print charindex('cat','my cat is king')
Returns: 4
The searches can also be case sensitive:
print charindex('king','my cat is KING!' COLLATE Latin1_General_CS_AS)
returns: 0
Here is a little function I use from time to time. It grabs the text in between two characters:
Create Function [dbo].[fnBetweenCharacters](@text ntext, @startChar nchar(1), @endChar nchar(1))
RETURNS nvarchar(4000)
AS
BEGIN
declare @startIndex int
declare @endIndex int
set @startIndex = charindex(@startChar, @text, 1)
set @endIndex = charindex(@endChar, @text, @startIndex+1)
return substring(@text,@startIndex+1, @endIndex - @startIndex-1)
END
It can be used like this:
print [dbo].[fnBetweenCharacters](N'asdkljasd@I need this text*k;lk', '@', '*')
returns: I need this text
Pass it on!
Posted by Bill Simpkins at 5:32 PM | Comments (1) | TrackBack
October 7, 2008
Passing An Array Parameter To SQL Server Stored Procedures
SQL Server (2000 & 2005) does not support array parameters for stored procedures. As a workaround, an array of values can be passed into SQL Server as a delimited string.
There are many articles on the web on how to do this, but this is my preferred method because the conversion of the delimited string into table values is done in a reusable function and the function itself can be placed inside a select query and act as a table.
First, we need to convert a delimited string to a table of values. This can be done through the following table-valued function:
CREATE Function [dbo].[fnSplit](@text text, @delimitor nchar(1))
RETURNS
@table TABLE
(
[Index] int Identity(0,1),
[SplitText] varchar(10)
)
AS
BEGIN
declare @current varchar(10)
declare @endIndex int
declare @textlength int
declare @startIndex int
set @startIndex = 1
if(@text is not null)
begin
set @textLength = datalength(@text)
while(1=1)
begin
set @endIndex = charindex(@delimitor, @text, @startIndex)
if(@endIndex != 0)
begin
set @current = substring(@text,@startIndex, @endIndex - @StartIndex)
Insert Into @table ([SplitText]) values(@current)
set @startIndex = @endIndex + 1
end
else
begin
set @current = substring(@text, @startIndex, datalength(@text)-@startIndex+1)
Insert Into @table ([SplitText]) values(@current)
break
end
end
end
return
END
To use this function, simply treat it as a table in the query:
select SplitText
from dbo.fnSplit('a,b,c',',')
Returns:
a
b
c
The complete process goes like this:
1. Convert the array of values to a delimited string.
2. Pass this string to the stored procedure.
3. Use the above fnSplit function to convert the string to a table of values which can be used in queries.
Posted by Bill Simpkins at 4:10 PM | Comments (4) | TrackBack