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')
Go to Top of Page

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')


Go to Top of Page

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]
Go to Top of Page
   

- Advertisement -