Full-text search example

if object_id(N'[dbo].[FTSearch]',N'U') is not null

   drop table [dbo].[FTSearch]

go

CREATE TABLE [dbo].[FTSearch]

(PK INT NOT NULL IDENTITY(1,1) CONSTRAINT fs_primarykey PRIMARY KEY, def varchar(max), [type] varchar(max), name varchar(max))

go

insert into [dbo].[FTSearch] ([type], name, def)

      SELECT

      obj.type_desc, -- [Object Type],

      obj.name,      -- [Object Name],      

      com.definition  -- [Text]

   FROM sys.sql_modules com

   JOIN sys.objects obj ON obj.object_id = com.object_id and SCHEMA_NAME(schema_id) <> 'sys' AND is_ms_shipped = 0

   ORDER BY obj.name

go

IF EXISTS ( SELECT 1 FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[FTSearch]') ) 

   DROP FULLTEXT INDEX ON #FTSearch

go

IF EXISTS ( SELECT 1 FROM sysfulltextcatalogs ftc WHERE ftc.name = N'TestFTSearch' ) 

   DROP FULLTEXT CATALOG [TestFTSearch]

go

CREATE FULLTEXT CATALOG [TestFTSearch] WITH ACCENT_SENSITIVITY = ON AS DEFAULT AUTHORIZATION [dbo]

go

CREATE FULLTEXT INDEX ON [dbo].[FTSearch]([def]) KEY INDEX fs_primarykey ON ([TestFTSearch]) WITH (CHANGE_TRACKING AUTO)

go

ALTER FULLTEXT INDEX ON [dbo].[FTSearch] ENABLE

go

WHILE FulltextCatalogProperty('TestFTSearch','PopulateStatus') <> 0   

BEGIN  

   WAITFOR DELAY '00:00:05' 

END 

SELECT *

FROM [dbo].[FTSearch]

WHERE CONTAINS([def], 'CheckPOBuilderViewsSp')

Fetch data from another DB server (sql server)

way 1:

exec   sp_addlinkedserver     'srv_lnk','','SQLOLEDB','cnshdnfeng1'   
exec   sp_addlinkedsrvlogin   'srv_lnk','false',null,'sa','sa'  
go

select * from   srv_lnk.SunSystemsData.dbo.ANL_DIR

exec   sp_dropserver   'srv_lnk','droplogins'

way 2:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

select * from openrowset('SQLOLEDB','cnshdnfeng1';'sa';'sa', SunSystemsData.dbo.ANL_DIR)

Common schema clause

-- Add UDDT: AU_EdiDateOffsetHoursType

IF NOT EXISTS (SELECT 1 FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id 

   WHERE st.name = N'AU_EdiDateOffsetHoursType' AND ss.name = N'dbo')

   CREATE TYPE [dbo].[AU_EdiDateOffsetHoursType] FROM smallint NULL

GO

--Create Table: AU_co_contract_line_mst

IF OBJECT_ID(N'[dbo].[AU_co_contract_line_mst]', N'U') IS NULL

CREATE TABLE [dbo].[AU_co_contract_line_mst](

      [site_ref] [dbo].[SiteType] NOT NULL

         CONSTRAINT [DF_AU_co_contract_line_mst_site_ref] DEFAULT (RTRIM(CONVERT([nvarchar](8),context_info(),0)))

    , [contract_id] [dbo].[AU_ContractIDType] NOT NULL

    , [co_num] [dbo].[CoNumType] NOT NULL 

    , [cust_item] [dbo].[CustItemType] NULL

    , [CreatedBy] [dbo].[UsernameType] NOT NULL

        CONSTRAINT [DF_AU_co_contract_line_mst_CreatedBy]  DEFAULT (SUSER_SNAME())

    , [UpdatedBy] [dbo].[UsernameType] NOT NULL 

        CONSTRAINT [DF_AU_co_contract_line_mst_UpdatedBy]  DEFAULT (SUSER_SNAME())

    , [CreateDate] [dbo].[CurrentDateType] NOT NULL 

        CONSTRAINT [DF_AU_co_contract_line_mst_CreateDate]  DEFAULT (GETDATE())

    , [RecordDate] [dbo].[CurrentDateType] NOT NULL 

        CONSTRAINT [DF_AU_co_contract_line_mst_RecordDate]  DEFAULT (GETDATE())

    , [RowPointer] [dbo].[RowPointerType] NOT NULL 

        CONSTRAINT [DF_AU_co_contract_line_mst_RowPointer]  DEFAULT (NEWID())

    , [NoteExistsFlag] [dbo].[FlagNyType] NOT NULL 

        CONSTRAINT [DF_AU_co_contract_line_mst_NoteExistsFlag]  DEFAULT ((0)) 

        CONSTRAINT [CK_AU_co_contract_line_mst_NoteExistsFlag] CHECK ([NoteExistsFlag] IN (0,1))

    , [InWorkflow] [dbo].[FlagNyType] NOT NULL 

        CONSTRAINT [DF_AU_co_contract_line_mst_InWorkflow]  DEFAULT ((0))

        CONSTRAINT [CK_AU_co_contract_line_mst_InWorkflow] CHECK ([InWorkflow] IN (0,1))

    , CONSTRAINT [PK_AU_co_contract_line_mst] PRIMARY KEY CLUSTERED 

       (

           [contract_id] ASC,

           [co_num] ASC,

           [co_line] ASC,

           [site_ref]

       )

    , CONSTRAINT [IX_AU_co_contract_line_mst_RowPointer] UNIQUE NONCLUSTERED 

      ( 

         [RowPointer]

        ,[site_ref]

      )

   )   

GO

-- Add column with constraints

IF OBJECTPROPERTY(OBJECT_ID(N'[dbo].[so_parms]'), N'IsUserTable') = 1

   AND NOT EXISTS (SELECT 1 FROM [sys].[columns]

      WHERE [object_id] = OBJECT_ID(N'[dbo].[so_parms]')

      AND [name] = N'stat_code')

   ALTER TABLE [dbo].[so_parms] ADD

      [stat_code] [dbo].[FSStatCodeType] NOT NULL

         CONSTRAINT [DF_so_parms_stat_code]  DEFAULT (1)

         CONSTRAINT [CK_so_parms_stat_code] CHECK ([pick_list_printed] IN (0, 1))

         CONSTRAINT [FK_so_parms_stat_code] FOREIGN KEY ([stat_code]) 

            REFERENCES [dbo].[fs_stat_code]([stat_code]) NOT FOR REPLICATION

GO

IF COL_LENGTH('dbo.CRMMobileDeviceIdo', 'IsReadOnly')  IS NULL

   ALTER TABLE [dbo].[CRMMobileDeviceIdo] ADD [IsReadOnly] [dbo].[ListYesNoType] NOT NULL DEFAULT 0;

GO

-- Add FK between AU_co_contract_line_prc_mst and AU_co_contract_line_mst

IF OBJECTPROPERTY(OBJECT_ID(N'[dbo].[AU_co_contract_line_prc_mst]'), N'IsUserTable') = 1

   AND NOT EXISTS (SELECT 1 FROM [sys].[objects]

   WHERE [OBJECT_ID] = OBJECT_ID(N'FK_AU_co_contract_line_prc_mst_contract_id_co_num_co_line_site_ref'))

   ALTER TABLE [dbo].[AU_co_contract_line_prc_mst] WITH NOCHECK 

   ADD CONSTRAINT [FK_AU_co_contract_line_prc_mst_contract_id_co_num_co_line_site_ref]

   FOREIGN KEY (

           [contract_id],

           [co_num],

           [co_line],

           [site_ref]

   ) REFERENCES [dbo].[AU_co_contract_line_mst](

           [contract_id],

           [co_num],

           [co_line],

           [site_ref]

   ) NOT FOR REPLICATION

GO

-- Add Check Constraint

IF  EXISTS (SELECT 1 FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_arpmtd_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[arpmtd]'))

ALTER TABLE [dbo].[arpmtd] DROP CONSTRAINT [CK_arpmtd_type]

GO

ALTER TABLE [dbo].[arpmtd] WITH CHECK ADD  CONSTRAINT [CK_arpmtd_type] CHECK  (([type]='S' OR ([type]='D' OR ([type]='A' OR ([type]='W' OR [type]='C')))))

GO

ALTER TABLE [dbo].[arpmtd] CHECK CONSTRAINT [CK_arpmtd_type]

GO

-- Remove existed FK

IF EXISTS (SELECT 1 

           FROM sys.foreign_keys 

           WHERE OBJECT_ID = OBJECT_ID(N'fs_parmsFk57')

           AND   parent_OBJECT_ID = OBJECT_ID(N'[dbo].[fs_parms]')

)

BEGIN

   ALTER TABLE [dbo].[fs_parms] DROP CONSTRAINT [fs_parmsFk57]

END

GO

-- Remove Existed column

IF EXISTS (SELECT 1 FROM  sys.columns c 

           INNER JOIN  sys.objects t ON (c.[OBJECT_ID] = t.[OBJECT_ID])

           WHERE t.[OBJECT_ID] = OBJECT_ID(N'[dbo].[fs_parms]')

           AND   c.[name] = N'parts_sro_template')

BEGIN 

   ALTER TABLE [dbo].[fs_parms] DROP COLUMN parts_sro_template

END

GO

-- Create Stored Procedure

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE id = object_id(N'MilestoneOperationCheckSp') 

AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

   DROP PROCEDURE MilestoneOperationCheckSp

GO

CREATE PROCEDURE MilestoneOperationCheckSp (  

   @PSroNum            FSSRONumType

 , @Infobar            Infobar      = NULL OUTPUT

) AS  

  

DECLARE 

   @Severity INT  

SET @Severity = 0

RETURN @Severity 

-- Create Function

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SarbCalSp]') AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1)

   DROP FUNCTION [dbo].[SarbCalSp]

GO

CREATE FUNCTION dbo.SarbCalSp (

  @PFutureDate DateType

, @PNewDate    DateType

)

RETURNS SMALLINT

AS

BEGIN

   RETURN (month(@PNewDate) - month(@PFutureDate)) + (year(@PNewDate) - year(@PFutureDate)) * 12

END

-- Create Trigger

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UserNamesAppDel]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)

DROP TRIGGER [dbo].[UserNamesAppDel]

GO

CREATE TRIGGER dbo.UserNamesAppDel

ON UserNames

FOR DELETE

AS

-- Skip trigger operations as required.

IF dbo.SkipBaseTrigger() = 1

   RETURN

DECLARE sssFSUsernamesDelCrs CURSOR LOCAL STATIC

FOR SELECT 

  dd.RowPointer

, dd.username

FROM deleted AS dd

OPEN sssFSUsernamesDelCrs

WHILE @Severity = 0

BEGIN -- cursor loop

   FETCH sssFSUsernamesDelCrs INTO

     @RowPointer

   , @Username

   IF @@FETCH_STATUS = -1

      BREAK

END -- End of cursor loop

CLOSE sssFSUsernamesDelCrs

DEALLOCATE sssFSUsernamesDelCrs

IF @Severity = 0

BEGIN

  DELETE user_local

  FROM

    deleted dd

   ,user_local ul

  WHERE ul.UserId = dd.UserId

  SELECT @Severity = @@ERROR

END

/* return error result */

IF @Severity <> 0

BEGIN

    EXEC RaiseErrorSp @Infobar, @Severity, 3

 

    EXEC @Severity = RollbackTransactionSp

       @Severity

 

    IF @Severity != 0

    BEGIN

       ROLLBACK TRANSACTION

       RETURN

    END

END

T-SQL read/write windows file system

IF OBJECT_ID('dbo.Tool_File_WriteAllTextSp') IS NOT NULL

    DROP PROCEDURE [dbo].[Tool_File_WriteAllTextSp]

GO

CREATE PROCEDURE [dbo].[Tool_File_WriteAllTextSp]

(

   @FileFullName NVARCHAR(1000)

 , @FileContent  NVARCHAR(MAX)

) AS

DECLARE

   @Object INT

 , @rc INT

 , @FileID INT

EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @Object OUTPUT

EXEC @rc = sp_OAMethod @Object, 'OpenTextFile', @FileID OUTPUT, @FileFullName, 2, 1

SET @FileContent = REPLACE(REPLACE(REPLACE(@FileContent, '&', '&'), '<', '<'), '>', '>')

EXEC @rc = sp_OAMethod @FileID, 'WriteLine', NULL, @FileContent

EXEC @rc = dbo.sp_OADestroy @FileID

EXEC @rc = dbo.sp_OADestroy @Object, 'SaveFile', NULL, @FileContent, @FileFullName, 0

EXEC sp_OADestroy @FileID

EXEC sp_OADestroy @Object

GO

IF OBJECT_ID('dbo.Tool_File_ReadAllTextSp') IS NOT NULL

    DROP PROCEDURE [dbo].[Tool_File_ReadAllTextSp]

GO

CREATE PROCEDURE [dbo].[Tool_File_ReadAllTextSp]

(

   @FileFullName NVARCHAR(1000)

 , @FileContent  NVARCHAR(MAX) OUTPUT

) AS

DECLARE

   @Object INT

 , @rc INT

 , @FileID INT

 , @strLine NVARCHAR(4000) = ''

 , @blnEndOfFile INT = 0

SET @FileContent = ''

EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @Object OUTPUT

EXEC @rc = sp_OAMethod @Object, 'OpenTextFile', @FileID OUTPUT, @FileFullName, 1

EXEC sp_OAMethod @FileID, 'AtEndOfStream', @blnEndOfFile OUTPUT

WHILE @blnEndOfFile = 0

BEGIN

   EXEC sp_OAMethod @FileID, 'ReadLine', @strLine OUTPUT

   SET @FileContent = @FileContent + ISNULL(@strLine, '') + CHAR(13)

   EXEC sp_OAMethod @FileID, 'AtEndOfStream', @blnEndOfFile OUTPUT

END

EXEC sp_OADestroy @FileID

EXEC sp_OADestroy @Object

GO

-- Test Code Here

EXEC Tool_File_WriteAllTextSp

'\\USCOVWSL901TS2\Shared\Test.txt',

'File Content Text Here

First Line

Second Line

...

Last Line.'

DECLARE @Content nvarchar(1000)

EXEC Tool_File_ReadAllTextSp '\\USCOVWSL901TS2\Shared\abc.txt', @Content OUTPUT

SELECT @Content

Debug Message in Sql Server Function

Add below scripts into Function(Need relative permission) then it would send the debug message to specified file.

 -- Enable advanced options to be changed.

EXEC SP_CONFIGURE 'show advanced options', 1

GO

RECONFIGURE

GO

-- Enable xp_cmdshell option.

EXEC SP_CONFIGURE N'xp_cmdshell', 1

GO

RECONFIGURE

GO

declare @Result1 decimal (38, 10) = 12.1

declare @a varchar(500) = 'echo "' + cast(@Result1 as varchar(100)) + '" >> \\cnshdbqin01\Test\debuginfo.txt'

exec xp_cmdshell 'net use \\cnshdbqin01\Test "Winter_02" /USER:infor\bqin'

exec xp_cmdshell @a

-- Disable xp_cmdshell option. 

EXEC SP_CONFIGURE 'xp_cmdshell', 0

GO

RECONFIGURE

GO

-- Disable advanced options to be changed.

EXEC SP_CONFIGURE 'show advanced options', 0

GO

RECONFIGURE

GO

Export assembly from database into dll file

/*

    Summary: 

    Use Ole Automation Procedures to export assemble to local dll file. Configure file system      permissions for Database Engine Access when need (Missed here). Then send the file to      another work computer from database server by xp_cmdshell.

*/

-- Turn on Ole Automation Procedures & xp_cmdshell. 

sp_configure 'show advanced options', 1;  

GO  

RECONFIGURE;  

GO  

sp_configure 'Ole Automation Procedures', 1; 

GO 

EXEC SP_CONFIGURE N'xp_cmdshell', 1

GO  

RECONFIGURE;  

GO 

DECLARE @IMG_PATH VARBINARY(MAX)

DECLARE @ObjectToken INT

DECLARE @intObject INT

DECLARE @intResult INT

DECLARE @strSource VARCHAR(255)

DECLARE @strDescription VARCHAR(255)

DECLARE @Directory VARCHAR(255)

DECLARE @DirDirectory VARCHAR(255)

DECLARE @MkDirDirectory VARCHAR(255)

DECLARE @File VARCHAR(255)

DECLARE @FilePath VARCHAR(255)

DECLARE @Xcopy VARCHAR(255)

SET @Directory = 'C:\Temp\Test\'

SET @DirDirectory = 'dir ' + @Directory

SET @File = 'MaterialExt.dll'

EXEC @intResult = master..xp_cmdshell @DirDirectory, no_output

IF @intResult <> 0

BEGIN

   SET @MkDirDirectory = 'mkdir ' + @Directory

   EXEC @intResult = master..xp_cmdshell @MkDirDirectory, no_output

   IF @intResult <> 0

   BEGIN

      PRINT 'Failed to create directory'

  GOTO Exit_Point

   END

END

--SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65536

SELECT @IMG_PATH = assemblyImage FROM [dbo].[ObjCustomAssembly] WHERE assemblyname = 'MaterialExt'

EXEC  @intResult = sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT

IF @intResult <> 0

BEGIN

   PRINT 'Failed to create object'

   GOTO Handle_Error

END

EXEC sp_OASetProperty @ObjectToken, 'Type', 1

EXEC @intResult = sp_OAMethod @ObjectToken, 'Open'

IF @intResult <> 0

BEGIN

   PRINT 'Failed to open stream'

   GOTO Handle_Error

END

EXEC @intResult = sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH

IF @intResult <> 0

BEGIN

   PRINT 'Failed to write to stream'

   GOTO Handle_Error

END

SET @FilePath = @Directory + @File

EXEC @intResult = sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FilePath, 2

IF @intResult <> 0

BEGIN

   PRINT 'Failed to write to file'

   GOTO Handle_Error

END

EXEC sp_OAMethod @ObjectToken, 'Close'

EXEC sp_OADestroy @ObjectToken

Handle_Error:

BEGIN

   EXEC @intResult = sp_OAGetErrorInfo @intObject, @strSource OUT, @strDescription OUT

   PRINT @strSource + ' - ' + @StrDescription

END

exec @intResult = xp_cmdshell 'net use \\cnshdbqin01\Test "Summer_01" /USER:infor\bqin', no_output

IF @intResult <> 0

BEGIN

   PRINT 'Failed to net use'

   GOTO Exit_Point

END

SET @Xcopy = 'xcopy /Y ' + @FilePath + ' \\cnshdbqin01\Test\'

EXEC @intResult = master..xp_cmdshell @Xcopy, no_output

IF @intResult <> 0

BEGIN

   PRINT 'Failed to xcopy file'

   GOTO Exit_Point

END

Exit_Point:

-- Turn off Ole Automation Procedures & xp_cmdshell. 

EXEC SP_CONFIGURE 'Ole Automation Procedures', 0

GO

-- Disable xp_cmdshell option. 

EXEC SP_CONFIGURE 'xp_cmdshell', 0

GO

RECONFIGURE

GO

-- Disable advanced options to be changed.

EXEC SP_CONFIGURE 'show advanced options', 0

GO

RECONFIGURE

GO