Category Archive » SQL

Adding linked servers in SQL farm

Handy lines for adding linked servers in your SQL farm:

   exec sp_addlinkedserver @server = ‘SQLSERVERNAME’, @srvproduct = N”, @provider=N’SQLNCLI’, @datasrc = ‘SERVERNAME’

   exec sp_addlinkedsrvlogin @rmtsrvname = ‘SQLSERVERNAME’, @useself = ‘false’, @locallogin = ‘login_svc’, @rmtuser = ‘user_svc’, @rmtpassword = ‘********’

 

SQL function to split string

Here’s a handy user defined function to split string into comma separated line:

/****** Object: UserDefinedFunction [dbo].[ufnSplitString] Script Date: 19/11/2015 11:18:20 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[ufnSplitString](

@String varchar(max),

@Delimiter char(1),

@Column int = 1

)

Read More

How to connect to SQL data source from Infopath 2010 browser form

Infopath has the ability connect to an external data source for data. The problem is there’s not a lot of information available on the net in guiding you to do this. You will most likely bump into problem when publishing the form to run on browser, to your production server where there’s multi-tier architecture (i.e. having multiple servers).

Read More

How to check if Constraint exist

A very useful statement to check if a constraint exists before do something to it. Note, we can never edit a constraint before deleting it and re-create.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=’dbo’
AND CONSTRAINT_NAME=’FK_37_Page_FK’ AND TABLE_NAME=’PageContent’