Brian notepad: Sql Server

Showing posts with label Sql Server. Show all posts

Sep 6, 2012

SQL Server Error: 5132 ,Error: 17207 when attach DB

SQL Server Error: 5132 , Error: 17207, Severity: 16
Loss SQL Server 2005 .LDF file , when try to attach MDF/NDF facing problem.
Step 0) Copy MDF/LDF to another location for backup purpose.
Step 1) Create DB with same DB/MDF/NDF/LDF Name
Step 2) Stop SQL Service
Step 3) Replace new DB MDF/NDF file with old MDF/NDF file
Step 4) Start SQL Server
Step 5) SQL Server 2005

ALTER DATABASE YourDB SET EMERGENCY

select * from sysdatabases

ALTER DATABASE YourDB SET SINGLE_USER

DBCC CHECKDB(YourDB, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE SFC SET MULTI_USER

CheckDB complained about the missing log then went and rebuilt the log

#Remark - SQL Server 2000 update system tables

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE;
-- do some modification to system tables

-- Ex.Put the DB in suspect mode:

-- update sysdatabases set status = 32768 where name ='YourDB'
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE;

Feb 22, 2011

SQL Server using decode / instr function

1. "charindex('Taiwan',description)" can be act as instr.
2. "case WHEN charindex('Taiwan',description) > 0 then 'Taiwan Local' else 'Other contury' end as location" can be act as decode.
3. full sql is like :
select mac,description,case WHEN charindex('Taiwan',description) > 0 then 'Taiwan Local' else 'Other contury' end as location from tmpdata

Jan 9, 2009

sql server 2000(publisher) vs sql server 2005(subscriber) with difference COLLATE

1. SQL server 2000 as publisher and distributor role .
2. Add sql server 2005 as subscriber(cannot browser database , just type the database name into destination database name in EM) in sql sql server EM.
3. Create tables in sql server 2005 with same COLLATE column specify in sql server 2000.
4. Add publication article with no create object option.

solve problem:
無法大量載入，大量資料流不正確地指定為已排序。
(來源: 192.168.1.1 (資料來源); 錯誤號碼: 4819)
---------------------------------------------------------------------------------------------------------------
函數順序錯誤
(來源: 192.168.1.1 (資料來源); 錯誤號碼: S1010)

Jun 11, 2008

SQL Server Replication Data Type & Oracle Data Type

Oracle Data Type Definitions

1. The following table lists the Oracle data type definitions.

Oracle data type	Definition
CHAR	<=2000
DATE	Jan 1, 4712 B.C. to Dec 31, 4712 A.D.
DECIMAL	Same as Number
FLOAT	Same as Number
INTEGER	Same as Number
LONG	<=2GB
LONG RAW	Raw data; Same as Long
LONG VARCHAR	Same as Long
NUMBER	1.0E-130 to 9.99..E125
SMALLINT	Same as Number
RAW	Raw Binary Data <=255 bytes
ROWID	Unique Value
VARCHAR2	<=4000 bytes
VARCHAR	Same as Varchar2
BLOB	Binary Large Object <=4GB
COB	Char Large Object <=4GB
NCLOB	Same as Clob (for multibyte)
BFILE	Pointer to binary operating file

2. The following table maps data types for replication to Oracle Subscribers.

SQL Server 2000 data type	Oracle data type
bigint	NUMBER
binary	LONG RAW NOT NULL
bit	NUMBER (1, 0)
char	VARCHAR2 (900) NOT NULL
datetime	DATE
decimal	NUMBER (255, 3) NOT NULL
float	FLOAT NOT NULL
image	LONG RAW
int	NUMBER (255, 3) NOT NULL
money	NUMBER (255, 3) NOT NULL
nchar	VARCHAR2 (2000) NOT NULL
ntext	LONG
numeric	NUMBER (255, 3) NOT NULL
nvarchar	VARCHAR2 (2000) NOT NULL
real	FLOAT NOT NULL
smallint	NUMBER (255, 3) NOT NULL
smalldatetime	DATE NOT NULL
smallmoney	NUMBER (255, 3) NOT NULL
sql_variant	LONG
sysname	CHAR(255)
text	LONG
timestamp	RAW (255)
tinyint	NUMBER (255, 3) NOT NULL

3. The following table shows the data type mappings that are used when data is replicated to a Subscriber running Oracle.

SQL Server 2005 Data Type	Oracle Data Type
BIGINT	NUMBER(19,0)
BINARY(1-2000)	RAW(1-2000)
BINARY(2001-8000)	BLOB
BIT	NUMBER(1)
CHAR(1-2000)	CHAR(1-2000)
CHAR(2001-4000)	VARCHAR2(2001-4000)
CHAR(4001-8000)	CLOB
DATETIME	DATE
DECIMAL(1-38, 0-38)	NUMBER(1-38, 0-38)
DOUBLE PRECISION	FLOAT
FLOAT	FLOAT
IMAGE	BLOB
INT	NUMBER(10,0)
MONEY	NUMBER(19,4)
NCHAR(1-1000)	CHAR(1-1000)
NCHAR(1001-4000)	NCLOB
NTEXT	NCLOB
NUMERIC(1-38, 0-38)	NUMBER(1-38, 0-38)
NVARCHAR(1-1000)	VARCHAR2(1-2000)
NVARCHAR(1001-4000)	NCLOB
NVARCHAR(MAX)	NCLOB
REAL	REAL
SMALLDATETIME	DATE
SMALLINT	NUMBER(5,0)
SMALLMONEY	NUMBER(10,4)
SQL_VARIANT	N/A
SYSNAME	VARCHAR2(128)
TEXT	CLOB
TIMESTAMP	RAW(8)
TINYINT	NUMBER(3,0)
UNIQUEIDENTIFIER	CHAR(38)
VARBINARY(1-2000)	RAW(1-2000)
VARBINARY(2001-8000)	BLOB
VARCHAR(1-4000)	VARCHAR2(1-4000)
VARCHAR(4001-8000)	CLOB
VARBINARY(MAX)	BLOB
VARCHAR(MAX)	CLOB
XML	NCLOB

Apr 17, 2008

SQL server 四捨五入

SQL Server 於小數點進行四捨五入時,若欄位是 Float 型態,用 Round 函數,則有時會有無限循環的小數, 必須轉換欄位形態才行, 如下語法可進行驗證~
select round(convert(numeric(5,2),10)/convert(numeric(5,2),3),2)

Mar 25, 2008

sql server 18483 error

18483 時發生錯誤: 不可能會連線到伺服器 ' < > 執行個體 \ < 伺服器 > ' 因為 ' distributor_admin ' 並未定義為遠端登入伺服器上

SELECT @@SERVERNAME, SERVERPROPERTY('ServerName') 確認

sp_dropserver 'oldservername' , delete old name
,after that sp_addserver 'actualservername','local' followed by a restart of the SQL service.

Mar 20, 2008

MimeSweeper sql script

select name from sysdatabases where dbid > 6
go

sp_changedbowner 'sa'
go
sp_changeobjectowner 'Archive.MAILINFO','dbo'
go
sp_changeobjectowner 'Archive.MAILTO','dbo'
go
sp_dropuser 'Archive'
go
sp_changedbowner 'Archive'

CREATE VIEW MAILINFO as
select * from archive20080310..MAILINFO
union all
select * from Archive20080229..MAILINFO
union all
select * from Archive20080218..MAILINFO
union all
select * from Archive20080130..MAILINFO
union all
select * from Archive20080117..MAILINFO
union all
select * from Archive20080116..MAILINFO
union all
select * from Archive20071221..MAILINFO
union all
select * from Archive20071212..MAILINFO
union all
select * from Archive20071205..MAILINFO
union all
select * from Archive20071122..MAILINFO
union all
select * from archive20071113..MAILINFO
union all
select * from archive20071101..MAILINFO
union all
select * from archive20071101..MAILINFO
union all
select * from archive20071024..MAILINFO
union all
select * from archive20071012..MAILINFO
union all
select * from archive20070927..MAILINFO
union all
select * from Archive20070912..MAILINFO
union all
select * from archive20070901..MAILINFO
union all
select * from archive20070831..MAILINFO
union all
select * from archive20070802..MAILINFO
union all
select * from archive20070718..MAILINFO
union all
select * from archive20070627..MAILINFO
GO

CREATE VIEW MAILTO as
select * from archive20080310..MAILTO
union all
select * from Archive20080229..MAILTO
union all
select * from Archive20080218..MAILTO
union all
select * from Archive20080130..MAILTO
union all
select * from Archive20080117..MAILTO
union all
select * from Archive20080116..MAILTO
union all
select * from Archive20071221..MAILTO
union all
select * from Archive20071212..MAILTO
union all
select * from Archive20071205..MAILTO
union all
select * from Archive20071122..MAILTO
union all
select * from archive20071113..MAILTO
union all
select * from archive20071101..MAILTO
union all
select * from archive20071101..MAILTO
union all
select * from archive20071024..MAILTO
union all
select * from archive20071012..MAILTO
union all
select * from archive20070927..MAILTO
union all
select * from Archive20070912..MAILTO
union all
select * from archive20070901..MAILTO
union all
select * from archive20070831..MAILTO
union all
select * from archive20070802..MAILTO
union all
select * from archive20070718..MAILTO
union all
select * from archive20070627..MAILTO

Feb 1, 2008

SQL server replicaion alert and restart fail jobs , vbscript

Are you a sql server dba ?
Your continue replication job failed lots of days and nobody tell you until wrong data cause business loss ? Using following script to help get alert and automatic restart jobs.
'1. Connect to sql server and get replication status
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.LoginSecure = True 'Windows authentication
oServer.Connect ""
Set queryResults = oServer.Replication.Distributor.EnumDistributionAgentViews

'2.
'Get distribution agent name
queryResults.ColumnName(j) = "name"
agent_name = queryResults.GetColumnString(i,j)

'3. Get distribution agent status
queryResults.ColumnName(j) = "status"
queryResults.GetColumnLong(i, j) = 6

'*** Replication Task Status Constants

Constant	Value	Description
SQLDMOTask_Failed	6	At least one job failed to execute.
SQLDMOTask_Idle	4	All jobs are scheduled and idle.
SQLDMOTask_Pending	0	All jobs are waiting to start.
SQLDMOTask_Retry	5	At least one job is attempting to execute after a previous failure.
SQLDMOTask_Running	3	At least one job is executing.
SQLDMOTask_Starting	1	One or more jobs are starting.
SQLDMOTask_Succeeded	2	All jobs executed successfully.

'4. When agent status become failed , restart it .
Set oJob = oServer.JobServer.Jobs(agent_name)
oJob.Start

Jan 30, 2008

Eliminate sql server table duplicate rows

Eliminate sql server table duplicate rows

Scenario : company_list table content with duplicate rows , only want to keep one row .
Now , create table temp1 , column definition same with company_lis , then

1. SQL Server 2005 , right click upon table ,

2. on table column , right click and choose “Indexes/Keys”

3. choose Add , “Is Unique” -> Yes

4. Then "Ignore Duplicate Keys" -> Yes

5. Save the table , then issue the command insert into temp1 select * from company_list . You will get data with no duplication rows in temp1 table, now ,

SQL Server replication to Oracle DB

1. SQL EM -> tools -> Replication -> Configure publishing ,Subscribers,Distribution

2. Subscribers tab , click new , choose OLE DB data source

3. Now , pick your linked server which is oracle DB source

4. transactional replicaion

5.

6. Now , Oracle subscriber appeared in SQL EM subscribers list.