What Every Developer Should Know About Database Scalability

Check out this SlideShare Presentation:

What Every Developer Should Know About Database Scalability

View more presentations from jbellis.

SP_FIND for SQL SERVER

CREATE PROC dbo.sp_find(@LikeSearchstr VARCHAR(255))
  AS
  /*
  Created by:    Abhishek Joshi
  Created Date:  Oct 27 2010
 
  Usage: To find a object stored proc or views that contain specific sttring.
 
  Example
  Exec dbo.sp_find 'Orders'
 
  */
  BEGIN
 DECLARE @cmd VARCHAR(MAX)

 SET @cmd='
 SELECT Name,OBJECT_DEFINITION(OBJECT_ID) as Text_Definition
 FROM sys.objects
 WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'+@LikeSearchstr+'%'''

EXEC(@CMD)
  END
 

Combining Like Searche with in search

while writing SP for Reports

1. MultiValue Parameter with Exact search Criteria:

There are conditions where you have have to pass parameters which are
multivalues usually string of comma separated values like @ProductID='Ram, Krina,
Hanuman'.

When we have to do exact search on these parameters we can directly use a
where clause like

p.ProductID in (Select _ID from dbo.fnSplitString(@ProductID,','))

where fnSplitString is splits values of parameter into table. exact search
can be performed. (code at the bottom)

2. Single Value Parameter with like Search.

In this situation we pass string parameter with a single value and
pass value like @Name='Ram'
the where clause will be

st.swName like (@Name+'%')

and all the names starting with Ram will be returned

3. Multi Value Parameter with like Search.

Now searching on this option how to do this. in nSQL



Code for fnSplitString

CREATE FUNCTION [dbo].[fnSplitString]
( @InputString varchar(8000), @SplitChar char(1) )

RETURNS @MyTbl TABLE(_ID varchar(4000))ASBEGIN

declare @LENInputString as intdeclare @x as int
declare @SingleChar as varchar(4000)

set @LENInputString = len(@InputString)
set @x = 0
while (select @x) < @LENInputString
begin set @SingleChar = SUBSTRING(@InputString, @x, 1)
if( select @SingleChar ) <> @SplitChar AND (select @InputString) <> ' '
begin
declare @y as int
set @y = @x+1
while (select @y) < @LENInputString+1
AND (select SUBSTRING(@InputString, @y, 1)) <> @SplitChar
begin
set @SingleChar = @SingleChar + SUBSTRING(@InputString, @y, 1)
set @y = @y+1
end
set @x=@y
end

if( select @SingleChar ) <> @SplitChar AND (select @InputString) <> ' '
begin
set @SingleChar = ltrim(rtrim(@SingleChar))
INSERT INTO @MyTbl (_ID) VALUES (@SingleChar)
end
set @x = @x+1
end
return
end

Index and length must refer to a location within the string. Parameter name: length (mscorlib)

While importing Data from Excel 2007 (a .xlsx file)

I came across following error

Index and length must refer to a location within the string.
Parameter name: length (mscorlib)

I was able to read meta data of excel but could not read the Data.

I was able to read data by copying over the same data to new Excel sheet and reading from there....This show me that excel's Internal database got infected in some manor.

while I am trying to find a better solution above work arround can be used.

Xcopy

xcopy "C:\Documents and Settings\t_AbhishekJ\My Documents\Citirx\CreateDDL" "v:\My Documents\SQLScripts" /s /y


Options
/A - Copies only files that have been set as archive files (using the ATTRIB command). The copied files will still be marked as archive files in BOTH the source and destination files.

/D:(date) - Copies only those files in the source directory that have been changed on or LATER than the specified date.

/S - Copies all files in the current directory and in any subdirectory within it.

/E - Copies subdirectories, even if they are empty. If you use this option, you must use the /S option also.

/M - Copies the same marked archive files as specified in the /A option; however, the files on the destination disk are no longer marked as archive files. This switch checks the archive attribute of a file: if the file`s archive attribute is set to off (-A), the file will not be copied.

/P - Does not copy the file until you respond to a prompt that asks you to confirm that you want the file copied. However, if you are using the /E option, you will not be prompted before an empty subdirectory is copied.

/V - Each file copied is verified to be sure the file stored on the destination disk is identical to the original on the source disk.

/W - Copies only after you respond to a prompt to begin copying (if this option is omitted, the copying begins as soon as you enter the XCOPY command and press the Enter key).

/Y - Causes XCOPY to replace existing files without a confirmation prompt.

/-Y - Causes XCOPY to give you a confirmation prompt before replacing an existing file.

Clearing log on Database with Replications

Run
BACKUP LOG pubs WITH TRUNCATE_ONLY

you may have to disable or Drop your Repliation. before running above command.

You can check details here.

http://support.microsoft.com/kb/272318

fn_Parse

the code is

CREATE FUNCTION fn_parse (@str varchar(255), @delim char(1), @token tinyint)
RETURNS varchar(255)
AS
BEGIN
DECLARE @start tinyint
, @end tinyint
, @loopcnt tinyint

set @end = 0
set @loopcnt = 0
set @delim = substring(@delim, 1, 1)

-- loop to specific token
while (@loopcnt < @token) begin
set @start = @end + 1
set @loopcnt = @loopcnt + 1
set @end = charindex(@delim, @str+@delim, @start)
if @end = 0 break
end

if @end = 0
set @str = null
else
set @str = substring(@str, @start, @end-@start)

RETURN @str


source:http://www.sqlservercentral.com/scripts/Miscellaneous/30334/
END