CODEWITHSUNDEEP
sql-servert-sql
Ben
Ben

Reputation: 609

INSERTING INTO table on Linked Server

I have this

IF @publishdr = 1 
BEGIN
TRUNCATE TABLE [LINKEDSERVERNAME].[Website-LIVE].[dbo].[VehicleData]
INSERT INTO [LINKEDSERVERNAME].[Website-LIVE].[dbo].[VehicleData]
(VehicleRef, Type, CapId, Maintenance, 
CH_2YR_SOURCE, CH_3YR_SOURCE, CH_4YR_SOURCE, PCH_2YR_SOURCE, PCH_3YR_SOURCE, PCH_4YR_SOURCE,
Manufacturer, Model, ShortModText, Derivative, ShortDerText, ManufacturerUrl, ModelUrl, ShortModTextUrl, DerivativeUrl, ShortDerTextUrl, VehicleUrl,
Category, LoCatCode, Fuel, Transmission, Doors, DriveTrain, Seats, Mpg, BodyType, BodyTypeId, 
CO2, EngineSize, InsuranceGroup, BHP, ManufacturerRRP,
VanCategory, VanMaxPayload, VanMaxLoadLength, VanMaxLoadWidthBetweenArches, VanMaxLoadWidth, VanMaxLoadHeight,
Co2TaxMonthly, Co2Tax40Monthly, IsInStock, AdhocVehicleRef,
InfoJson)
SELECT VehicleRef, Type, CapId, Maintenance, 
CH_2YR_SOURCE, CH_3YR_SOURCE, CH_4YR_SOURCE, PCH_2YR_SOURCE, PCH_3YR_SOURCE, PCH_4YR_SOURCE,
Manufacturer, Model, ShortModText, Derivative, ShortDerText, ManufacturerUrl, ModelUrl, ShortModTextUrl, DerivativeUrl, ShortDerTextUrl, VehicleUrl,
Category, LoCatCode, Fuel, Transmission, Doors, DriveTrain, Seats, Mpg, BodyType, BodyTypeId, 
CO2, EngineSize, InsuranceGroup, BHP, ManufacturerRRP,
VanCategory, VanMaxPayload, VanMaxLoadLength, VanMaxLoadWidthBetweenArches, VanMaxLoadWidth, VanMaxLoadHeight,
Co2TaxMonthly, Co2Tax40Monthly, IsInStock, AdhocVehicleRef,
InfoJson
FROM #VehicleData
END

I get this error:

The object name 'LINKEDSERVERNAME.Website-LIVE.dbo.VehicleData' contains more than the maximum number of prefixes. The maximum is 2.

I thought that was the correct syntax to specify tables on a linked server.

Thanks

Upvotes: 0

Views: 44

Answers (1)

squillman
squillman

Reputation: 13641

The error might be due to the fact that you are trying to TRUNCATE TABLE across the linked server, which is not supported.

Try this instead:

EXEC [LINKEDSERVERNAME].[Website-LIVE].[sys].[sp_executesql] N'TRUNCATE TABLE dbo.VehicleData';

Upvotes: 2

Related Questions