June 1, 2015 at 6:05 am
Hi All,
i am trying to run this procedure and it keeps coming up with the follwing error.
Msg 105, Level 15, State 1, Procedure vw_, Line 5
Unclosed quotation mark after the character string '16)) as tbl'.
Msg 102, Level 15, State 1, Procedure vw_, Line 5
Incorrect syntax near '16)) as tbl'.
I have created the procedure succesfully but i cant seem to run it.
Please help.
This is the procedure script
alter Procedure [dbo].[Usp_GetPivotTable]
As
begin
Declare @PivotCols Varchar(2000);
DECLARE @QUERY NVARCHAR(MAX) ;
--------------------
Set @PivotCols='';
---------extract data ---------------
with External_Data
as(
Select
a.testID,
a.RealID,
a.RealSequenceID,
a.RealDateTime,
a.RealSourceDesc,
ROW_NUMBER() OVER(partition by testID ORDER BY a.RealDateTime asc) AS Row,
CAST(a.testID AS VARCHAR)+'_'+CAST(a.RealSequenceID AS VARCHAR) External_StartID
--into#External_Referrals
FROM [dbo].[MainSourceTable]a
where
IntRefSrc = 0
)
--------------------------------------
Select @PivotCols=(Select Distinct '['+ cast([row] as varchar) +'],' from External_Data for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
--Dynemic Query To Run Pivot
end
select @query=N'Select testID,
RealDateTime,
row
From External_Data as tmp
pivot(max(RealDateTime) for [row] in ('+@PivotCols+')) as tbl';
execute ('create view DBO.vw_ AS ' + @query)
June 1, 2015 at 6:16 am
akinwandeb (6/1/2015)
Hi All,i am trying to run this procedure and it keeps coming up with the follwing error.
Msg 105, Level 15, State 1, Procedure vw_, Line 5
Unclosed quotation mark after the character string '16)) as tbl'.
Msg 102, Level 15, State 1, Procedure vw_, Line 5
Incorrect syntax near '16)) as tbl'.
I have created the procedure succesfully but i cant seem to run it.
Please help.
This is the procedure script
alter Procedure [dbo].[Usp_GetPivotTable]
As
begin
Declare @PivotCols Varchar(2000);
DECLARE @QUERY NVARCHAR(MAX) ;
--------------------
Set @PivotCols='';
---------extract data ---------------
with External_Data
as(
Select
a.testID,
a.RealID,
a.RealSequenceID,
a.RealDateTime,
a.RealSourceDesc,
ROW_NUMBER() OVER(partition by testID ORDER BY a.RealDateTime asc) AS Row,
CAST(a.testID AS VARCHAR)+'_'+CAST(a.RealSequenceID AS VARCHAR) External_StartID
--into#External_Referrals
FROM [dbo].[MainSourceTable]a
where
IntRefSrc = 0
)
--------------------------------------
Select @PivotCols=(Select Distinct '['+ cast([row] as varchar) +'],' from External_Data for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
--Dynemic Query To Run Pivot
end
select @query=N'Select testID,
RealDateTime,
row
From External_Data as tmp
pivot(max(RealDateTime) for [row] in ('+@PivotCols+')) as tbl';
execute ('create view DBO.vw_ AS ' + @query)
Try doing a PRINT @query instead of executing it. That'll show you what your variable contains so you can see where the problem is.
Edit: Once you're done with development, you'll want to remark the PRINT statement or remove it from your procedure.
June 1, 2015 at 6:54 am
Hi Ed,
Thanks for the tip. I have updated the script using Print, which showed me there was a missing ] at the end of my script afterthe last @PivotCols+ which i have now included.
here is the line of the script i updated.
pivot(max(ReferralDateTime) for [row] in ('+@PivotCols+'])) as tbl';
But it comes up with another error
Msg 102, Level 15, State 1, Procedure vw_, Line 5
Incorrect syntax near ']'.
Please advice
June 1, 2015 at 7:10 am
Take a look at what you're trying to execute. Can you execute your built-up statement in SSMS outside of the procedure? You have another error in your dynamic SQL that you have to find and fix.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply