Link server and temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Link server and temp table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-29 : 08:12:43

Earl writes "How can I get the results of a link server to a temp table if my query for the link server is dynamic. Sample code below. I need a dynamic query so that the link server is not fix on a single company database. My problem is that when I ran the sprocs, there are no records on the temp table.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[GSCLink]
(
@LinkCompany nvarchar(50),
@Page int,
@RecsPerPage int
)
AS

SET NOCOUNT ON

--Create temp table
CREATE TABLE #TempTable
(
ID int IDENTITY,
Name nvarchar(80),
AccountID int,
Active bit
)

--dynamic sql
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT a.Name, a.AccountID, a.Active
FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a
LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b
ON a.AccountID = b.oaAccountID
WHERE oaAccountID IS NULL
ORDER BY Name ASC'

INSERT INTO #TempTable values(@Name, @AccountID, @Active)

EXEC sp_executesql @sql

--Find out the first and last record
DECLARE @FirstRec int
DECLARE @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

--Return the set of paged records, plus an indication of more records or not
SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @LastRec) AS MoreRecords
FROM #TempTable
WHERE ID > @FirstRec AND ID < @LastRec"

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2006-11-29 : 12:26:06

dont use a linked server

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-29 : 13:45:52

Change this

--dynamic sql
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT a.Name, a.AccountID, a.Active
FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a
LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b
ON a.AccountID = b.oaAccountID
WHERE oaAccountID IS NULL
ORDER BY Name ASC'

INSERT INTO #TempTable values(@Name, @AccountID, @Active)

EXEC sp_executesql @sql

to this

--dynamic sql
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO #TempTable values(Name, AccountID, Active)
SELECT a.Name, a.AccountID, a.Active
FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a
LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b
ON a.AccountID = b.oaAccountID
WHERE oaAccountID IS NULL
ORDER BY Name ASC'

EXEC sp_executesql @sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources