After adding linked server on Microsoft SQL 2008, it’s time to query data from it. So how to query data from linked server on Microsoft SQL 2008?

Just run below query…

SELECT * FROM OPENQUERY
(LinkedServerName,’SELECT * FROM master.sys.servers‘)
GO

LinkedServerName is the linked server that you want to query from. And replace the query statement with your own query. Then you are ready to go.

• 0 Comments

Want to query data from other database server like Oracle, Access, IBM DB2 or even Microsoft Excel Spreadsheet, ODBC data source and file system? All you need to do is to add them as linked server on Microsoft SQL 2008. And remote stored procedures can be executed too.

Run below queries to add linked server

EXEC master.dbo.sp_addlinkedserver @server = N’ServerName‘, @provider = N’SQLOLEDB’, @datasrc = N’DataSource‘, @srvproduct=”

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’ServerName‘,@useself=N’False’,@locallogin=NULL,@rmtuser=N’UserName‘,@rmtpassword=’Password‘

sp_addlinkedserver is used to create a linked server.
sp_addlinkedsrvlogin is used to create login on the linked server.
That’s all.

• 2 Comments

I want to insert a list of data that separated by comma like ‘1234,2345,3456′ into a SQL table. How do I do that? Well, it is not hard if you know the trick. Here are the codes to do it.

DECLARE @pos int, @curruntLocation char(20), @input varchar(2048)
SELECT @pos=0
SELECT @input = ‘1234,2345,3456′
SELECT @input = @input + ‘,’

CREATE TABLE #tempTable (temp varchar(100) )

WHILE CHARINDEX(‘,’,@input) > 0
BEGIN
SELECT @pos=CHARINDEX(‘,’,@input)
SELECT @curruntLocation = RTRIM(SUBSTRING(@input,1,@pos-1))
INSERT INTO #tempTable (temp) VALUES (@curruntLocation)
SELECT @input=SUBSTRING(@input,@pos+1,2048)
END

SELECT * FROM #tempTable

DROP TABLE #tempTable

Result – you will get 3 rows (1234,2345,3456) in #tempTable. The @input is your input variable. @curruntLocation is the value that needed to be inserted into SQL table. That’s it.

• 4 Comments

Hmm… I have been using SQL for quite some time. But I do not know how to compare 2 tables to find out the different records. For example, I have 2 tables which are table A and table B. Both of them have some similar records. But I want to filter out the different data only. How can I do so? I searched online and discovered the method to do so. It is located in Microsoft SQL Server 2005 Books Online. All we need to do is use EXCEPT function in SQL.

Query to compare 2 tables to find the different

SELECT * FROM TableA EXCEPT SELECT * FROM TableB;

Query to compare 2 tables to find the matches

SELECT * FROM TableA INTERSECT SELECT * FROM TableB;

Hope this will help you too.

• 0 Comments

How to delete / drop SQL table? The following code will guide you to drop the temporary SQL table. Basically, you just need to type in ‘DROP TABLE tableName‘.

IF OBJECT_ID(N’tempdb..#temptable’, N’U') IS NOT NULL
DROP TABLE #temptable;
GO

• 0 Comments