Variables inside linked server queries... stumped!

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 2008 Forums

Transact-SQL (2008)

Variables inside linked server queries... stumped!

Author

Topic

ACNASQL
Starting Member

2 Posts

Posted - 2014-04-11 : 09:57:54

Hello folks,

Was hoping you could help me on this one. I'm using SQL Server Mgmt Studio (2008, R2). Trying to create a linked server query for a stored procedure.... However, I am running into the following error: "Msg 199, Level 15, State 1, Line 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable."

I've pasted my code below (hope the format translates!)
I don't see how I'm assigning values to the variable inside my select statement here - it's being done outside of it? I'm stumped!

Thanks in advance....

Code:

declare @CurrMo Varchar(30)
declare @CurrYe Varchar(30)
DECLARE @TSQL varchar(8000)

set @CurrMo = Month(dateadd(day,-1,getdate()))
set @CurrYe = Year(dateadd(day,-1,getdate())) 

INSERT INTO [Srvr2].[dbo].[tbl_Rent_Uti2]
([Year], 
[Month], 
[Utilperiod],
[PL],
[BL], 
[Store],
[Region],
[UnitsOR],
[FleetOR],
[UnitsTotal],
[NBVTotal],
[FleetTotal])

SELECT @TSQL =

('Select Year, Month as MONTH
	, ''1mth'' as UtilPeriod
	, tbl_Rental_UtilBase.PL
	, tbl_Rental_UtilBase.BL
	, tbl_Rental_UtilBase.SubChannel Store
	, tbl_Rental_UtilBase.Region

	, Util.UnitsTotal
	, Util.NBVTotal
	, Util.FleetTotal 
FROM dbo.tbl_Rental_UtilBase 

LEFT OUTER JOIN

(SELECT * FROM openquery([TODBC],

''SELECT left(PDTE,4) as Year, (substring(PDTE,5,2)+0) as Month, 
	PCNA as PL, 

	PBUSL as BL, 

	PLOCD as STORE, 


(count(PSN)/30)  UnitsTotal, (sum(PNBV)/30)  NBVTotal, (sum(PPCST)/30)  FLEETTotal 

FROM IPOSUT

Where left(PDTE,4) = '''''+@CurrYe+''''' and (substring(PDTE,5,2)+0) = '''''+@CurrMo+'''''

GROUP BY 
	left(PDTE,4),
	(substring(PDTE,5,2)+0),
	PCNA,
	PBUSL,
	PLOCD 
			''))Util 
			ON dbo.tbl_Rental_UtilBase.PL = Util.PL 
			AND dbo.tbl_Rental_UtilBase.BL = Util.BL 
			AND dbo.tbl_Rental_UtilBase.SubChannel = Util.Store')

EXEC (@TSQL)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-11 : 14:24:25

Remove the ( and ) on the @TSQL value assignment statement:

SELECT @TSQL =

('Select Year, Month as MONTH
, ''1mth'' as UtilPeriod
, tbl_Rental_UtilBase.PL
, tbl_Rental_UtilBase.BL
, tbl_Rental_UtilBase.SubChannel Store
, tbl_Rental_UtilBase.Region

, Util.UnitsTotal
, Util.NBVTotal
, Util.FleetTotal
FROM dbo.tbl_Rental_UtilBase

LEFT OUTER JOIN

(SELECT * FROM openquery([TODBC],

''SELECT left(PDTE,4) as Year, (substring(PDTE,5,2)+0) as Month,
PCNA as PL,

PBUSL as BL,

PLOCD as STORE,

(count(PSN)/30) UnitsTotal, (sum(PNBV)/30) NBVTotal, (sum(PPCST)/30) FLEETTotal

FROM IPOSUT

Where left(PDTE,4) = '''''+@CurrYe+''''' and (substring(PDTE,5,2)+0) = '''''+@CurrMo+'''''

GROUP BY
left(PDTE,4),
(substring(PDTE,5,2)+0),
PCNA,
PBUSL,
PLOCD
''))Util
ON dbo.tbl_Rental_UtilBase.PL = Util.PL
AND dbo.tbl_Rental_UtilBase.BL = Util.BL
AND dbo.tbl_Rental_UtilBase.SubChannel = Util.Store')

ACNASQL
Starting Member

2 Posts

Posted - 2014-04-11 : 14:40:06

Thanks for the suggestion, Scott! Unfortunately, I get the same error?

quote:
Originally posted by ScottPletcher

Remove the ( and ) on the @TSQL value assignment statement:

SELECT @TSQL =

('Select Year, Month as MONTH
, ''1mth'' as UtilPeriod
, tbl_Rental_UtilBase.PL
, tbl_Rental_UtilBase.BL
, tbl_Rental_UtilBase.SubChannel Store
, tbl_Rental_UtilBase.Region

, Util.UnitsTotal
, Util.NBVTotal
, Util.FleetTotal
FROM dbo.tbl_Rental_UtilBase

LEFT OUTER JOIN

(SELECT * FROM openquery([TODBC],

''SELECT left(PDTE,4) as Year, (substring(PDTE,5,2)+0) as Month,
PCNA as PL,

PBUSL as BL,

PLOCD as STORE,

(count(PSN)/30) UnitsTotal, (sum(PNBV)/30) NBVTotal, (sum(PPCST)/30) FLEETTotal

FROM IPOSUT

Where left(PDTE,4) = '''''+@CurrYe+''''' and (substring(PDTE,5,2)+0) = '''''+@CurrMo+'''''

GROUP BY
left(PDTE,4),
(substring(PDTE,5,2)+0),
PCNA,
PBUSL,
PLOCD
''))Util
ON dbo.tbl_Rental_UtilBase.PL = Util.PL
AND dbo.tbl_Rental_UtilBase.BL = Util.BL
AND dbo.tbl_Rental_UtilBase.SubChannel = Util.Store')

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-11 : 17:21:50

[code]

declare @CurrMo Varchar(30)
declare @CurrYe Varchar(30)
DECLARE @TSQL varchar(8000)

set @CurrMo = Month(dateadd(day,-1,getdate()))
set @CurrYe = Year(dateadd(day,-1,getdate()))

SELECT @TSQL = '
INSERT INTO [Srvr2].[dbo].[tbl_Rent_Uti2]
([Year],
[Month],
[Utilperiod],
[PL],
[BL],
[Store],
[Region],
[UnitsOR],
[FleetOR],
[UnitsTotal],
[NBVTotal],
[FleetTotal])

Select Year, Month as MONTH
, ''1mth'' as UtilPeriod
, tbl_Rental_UtilBase.PL
, tbl_Rental_UtilBase.BL
, tbl_Rental_UtilBase.SubChannel Store
, tbl_Rental_UtilBase.Region

, Util.UnitsTotal
, Util.NBVTotal
, Util.FleetTotal
FROM dbo.tbl_Rental_UtilBase

LEFT OUTER JOIN

(SELECT * FROM openquery([TODBC],

''SELECT left(PDTE,4) as Year, (substring(PDTE,5,2)+0) as Month,
PCNA as PL,

PBUSL as BL,

PLOCD as STORE,

(count(PSN)/30) UnitsTotal, (sum(PNBV)/30) NBVTotal, (sum(PPCST)/30) FLEETTotal

FROM IPOSUT

Where left(PDTE,4) = '''''+@CurrYe+''''' and (substring(PDTE,5,2)+0) = '''''+@CurrMo+'''''

GROUP BY
left(PDTE,4),
(substring(PDTE,5,2)+0),
PCNA,
PBUSL,
PLOCD
''))Util
ON dbo.tbl_Rental_UtilBase.PL = Util.PL
AND dbo.tbl_Rental_UtilBase.BL = Util.BL
AND dbo.tbl_Rental_UtilBase.SubChannel = Util.Store'

PRINT @TSQL
EXEC (@TSQL)

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources