Category Archives: Database

How to: change SQL server instance name

Posted on by
Reply

SQL server typically gets its instance name from underlining machine name when the SQL server was first installed. If the machine name has been changed, the SQL server instance name will be in-sync with network name. It could cause lots of wired behavior. If the following two command give you different name, then you better change your instance name.
sp_helpserver
select @@servername

To change instance name run this:

sp_dropserver 'old_name'
go
sp_addserver 'new_name','local'
go

Then restart the SQL server service.

A simple Sql cursor sample

Posted on by
Reply


USE FencoDW
GO

DECLARE @ID INT, @PR varchar(50)
DECLARE @getID CURSOR
SET @getID = CURSOR FOR
SELECT [Load Number], [Primary Reference(s)]
FROM Accounting.TransplaceLoadInfo

OPEN @getID
FETCH NEXT
FROM @getID INTO @ID, @PR
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ID
PRINT @PR

INSERT INTO dbo.T1 ([Load Number], [Primary Reference(s)])
SELECT @ID, [Val] from dbo.ParseCSV(@PR, ',')

FETCH NEXT
FROM @getID INTO @ID, @PR
END
CLOSE @getID
DEALLOCATE @getID
GO

SQL Server parse cvs

Posted on by
Reply

to parse a cvs string and return a table with “Val” as the column name.
=====

ALTER FUNCTION [dbo].[ParseCSV] (
@param varchar(8000),
@Delimeter varchar(10)
)
RETURNS @tblRes TABLE
(
-- Columns returned by the function
Val varchar(100)
)

AS begin

DECLARE @Val varchar(100)
DECLARE @StartPos int, @Length int

WHILE LEN(@param) > 0
BEGIN

SET @StartPos = CHARINDEX(@Delimeter, @param)
IF @StartPos < 0 SET @StartPos = 0 SET @Length = LEN(@param) - @StartPos - 1 IF @Length < 0 SET @Length = 0 IF @StartPos > 0
BEGIN
SET @Val = SUBSTRING(@param, 1, @StartPos - 1)
SET @param = SUBSTRING(@param, @StartPos + 1, LEN(@param) - @StartPos)
END
ELSE
BEGIN
set @param = ltrim(rtrim(@param))
SET @Val = @param
SET @param = ''
END
set @Val = Ltrim(Rtrim(@Val))
INSERT @tblRes (Val) VALUES(@Val)
END

--Show all records in the @tblRes table
--SELECT * FROM @tblRes

RETURN
end