January 11, 2018 at 3:39 am
Dear all,
I have a report. This report has 3 parameters (Choose Database , Choose Start Date and Choose endDate)
Choose Database is a parameter where you can select from a combox one or multiple database names (it returns one or more database names, according to what you have selected)
IF I choose just one database, the report works properly, but if I choose multiple databases (two or more DB names), it throughs the below error:
Incorrect syntax near ','.
The error is in the dataset, which is a Stored Procedure.
IF I execute the SP directly from Management studio with one value (one DB name), it works and if I choose to execute it with multiple it also works.
Below are two examples of this SP being called directly from Management Studio for both, one database name and multiple databases:
One database:
exec uspReturnSPPerformance 'dwhstaging', '01.01.2018', '10.01.2018'
Two databases:
exec uspReturnSPPerformance 'dwhstaging; dwhCalculation', '01.01.2018', '10.01.2018'
So, what I am expecting is that my stored procedure, when executed as dataset receives the same format as above when database parameters have multiple values chosen: DB1; DB2
Can you please help me understand what am I doing wrong?
Below you can find the Dataset Stored Procedure:
alter PROCEDURE uspReturnSPPerformance
(@databases VARCHAR(2000), @startdate VARCHAR(10), @enddate VARCHAR(10))
AS
DECLARE @StrSQL VARCHAR(2000), @DB VARCHAR(1000)
SET @databases = '''' + REPLACE(REPLACE(@databases, ';',''','''),' ','') + ''''
BEGIN
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME ='#ReturnSPNames')
BEGIN
DROP TABLE #ReturnSPNames
END
CREATE TABLE #ReturnSPNames
(
NumberExecutions BIGINT,
StoredProcedure VARCHAR(200),
DataChanges BIGINT,
TExecutionTimeSec INT,
DatabaseName VARCHAR(200)
)
EXEC('DECLARE UserDBs CURSOR FOR
SELECT
[d].[name]
FROM
[sys].[databases] AS [d]
WHERE
[d].[name] in (' +@databases+ ')
ORDER BY
[d].[name] ')
OPEN [UserDBs]
FETCH NEXT FROM [UserDBs] INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @StrSQL = ('
INSERT INTO #ReturnSPNames
SELECT TOP 10
COUNT(*) NumberExecutions,
execsp.schemaname + '' - '' + execsp.SPname StoredProcedure,
(SUM(rll.rowsinserted) + SUM(rll.rowsdeleted) + SUM(rll.rowsupdated)) DataChanges,
AVG(DATEDIFF(SECOND, execsp.[StartDateTime], execsp.[EndDateTime])) TExecutiontimeSec,
databasename
FROM
' + @DB + ' .ETL.vwRowLoadLog rll
INNER JOIN
' + @DB + '.Logging.SPExecutions execsp
ON
execsp.ExecutionID=rll.ExecutionID
AND
CONVERT( date, execsp.StartDateTime,103) >= CONVERT( date, ''' + @StartDate + ''' , 103)
AND
CONVERT (date, execsp.enddatetime,103) <= CONVERT(date, ''' + @EndDate + ''', 103)
GROUP BY
databasename, execsp.schemaname + '' - '' + execsp.SPname
ORDER BY texecutiontimesec DESC
')
EXEC (@StrSQL)
FETCH NEXT FROM [UserDBs] INTO @DB
END
CLOSE [UserDBs];
DEALLOCATE [UserDBs];
SELECT TOP 10
NumberExecutions, DatabaseName + ' - ' + StoredProcedure StoredProcedure, DataChanges, TExecutionTimeSec
FROM
#ReturnSPNames
ORDER BY
texecutiontimesec DESC
IF EXISTS (SELECT * FROM sys.objects WHERE NAME ='#ReturnSPNames')
BEGIN
DROP tABLE #ReturnSPNames
END
END
January 11, 2018 at 3:50 am
I think the reasoning is you that you say that it works fine in SSMS, but you're not running it as it would be in SSRS. You EXEC is:exec uspReturnSPPerformance 'dwhstaging; dwhCalculation', '01.01.2018', '10.01.2018'
However, SSRS, would provided a comma delimited string. Thus:exec uspReturnSPPerformance 'dwhstaging,dwhCalculation', '20180101', '20181001' ;
I suspect the latter doesn't work for you?
If so, consider replacing the commas with semi colons, or otherwise, use the a splitter function (such as delimitedsplit8k) to extract the multiple database entries.
P.s. try not to put so many line breaks between each line of your code. That is impossible/very difficult to read.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 11, 2018 at 3:53 am
Check out this link: https://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply