April 11, 2014 at 10:05 am
Hi everyone,
Was hoping you could help me on this one. Posted this on another forum, but no luck so far.
I'm using SQL Server Mgmt Studio 2008, R2. I'm 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, in the hopes that someone can provide a bit of insight for me.
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)
April 11, 2014 at 10:39 am
You're doing an insert with a variable, via the Select @TSQL statement. Why can't you strip out @TSQL, and modify your statement a little bit to just use a select?
April 11, 2014 at 10:40 am
You do assignment, here:
SELECT @TSQL =
--Vadim R.
April 11, 2014 at 11:05 am
JoshDBGuy,
I have found that OPENQUERY does not allow variables - the only workaround is to embed it in a @TSQL and then execute. I know for a fact that this has been done before (variables within @TSQL), so there should be some way to get it done?
That being said, if anyone knows of a way to skip the @TSQL, I'm more than open to suggestions.
JoshDBGuy (4/11/2014)
You're doing an insert with a variable, via the Select @TSQL statement. Why can't you strip out @TSQL, and modify your statement a little bit to just use a select?
April 11, 2014 at 11:25 am
If the server is linked, instead of doing an openquery you can use select .... from [server].[db].[schema].[table_name], i.e.
select * from server01.main_database.dbo.table
April 11, 2014 at 11:28 am
You need to include the whole thing (including INSERT INTO part) into the dynamic code.
--Vadim R.
April 11, 2014 at 11:30 am
This is also another option, it just seems a little weird to do it like this though.
April 11, 2014 at 11:33 am
I should mention that the linked server is not in SQL - it's an AS400 database.
April 11, 2014 at 11:37 am
The error you are getting is because this is syntactically invalid:
INSERT INTO TableA
SELECT @TSQL = 'select * from TableB'
--Vadim R.
April 11, 2014 at 11:43 am
JoshDBGuy (4/11/2014)
This is also another option, it just seems a little weird to do it like this though.
Ah, ok, try rVadams recommendation.
April 11, 2014 at 11:44 am
rVadim (4/11/2014)
You need to include the whole thing (including INSERT INTO part) into the dynamic code.
rVadim, I'm trying to follow this suggestion, but I can't get things to work. Also, I'm not sure if your second post about invalid syntax invalidates this first suggestion.
Would you mind elaborating? I am fairly new to SQL, so please forgive my slowness to understand.
Thanks again
April 11, 2014 at 12:06 pm
edited for double post, sorry!
April 11, 2014 at 12:10 pm
Back to basics. Change Exec(@Tsql) to PRINT @Tsql. Examine the output. Too many delimiters around your variables perhaps.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 11, 2014 at 12:40 pm
Your dynamic code should look like this:
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'
EXEC (@TSQL)
If you get errors do what Chris said - replace EXEC with PRINT, run it, copy results in the new query window, and examine.
--Vadim R.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply