1

I have used stored procedure with parameters to generate the result set for my report in reporting services 2012 but the result returned is wrong. I traced the command generated and here it is:

EXEC sp_executesql N'EXECUTE [Controls&Compliance].[dbo].[GetAccountsDetails_2]'
    ,N'@Region nvarchar(4000),@Market nvarchar(4000),@SiteID nvarchar(4000),@ServerClass nvarchar(4000),@InstanceName nvarchar(4000),@LoginName nvarchar(8)'
    ,@Region = NULL
    ,@Market = NULL
    ,@SiteID = NULL
    ,@ServerClass = NULL
    ,@InstanceName = NULL
    ,@LoginName = N'1C_admin'

This command generate thousand of rows.

The strange thing is that if I execute the code outside the sp_executesql it return the correct result (1 row):

EXECUTE [Controls&Compliance].[dbo].[GetAccountsDetails_2] @Region = NULL
    ,@Market = NULL
    ,@SiteID = NULL
    ,@ServerClass = NULL
    ,@InstanceName = NULL
    ,@LoginName = N'1C_admin'

I have read an article about problem generated by the wrong parameter order, but this is not the case. I also checked for the parameter data type and all are the same type.

Could someone help to understand why this behaviour and how to avoid it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SQLCH
  • 21
  • 1

2 Answers2

0

That's the first time I have ever seen an ampersand in a database name!

Anyway, back to the question.

  1. Is this in production or visual studio?
  2. Is there any caching taking place?
  3. Populate your dataset using a stored procedure - there is no excuse for using sp_executesql in this instance!
  4. If after all that the issue is still occurring (I bet it won't be) write out your parameters to a table within the SP.
BIDeveloper
  • 2,628
  • 4
  • 36
  • 51
0
  1. It is a pre-prod system. I am using report builder
  2. No cache - server restart gave the same issue
  3. The sp_executesql is generated by reporting services - I have no control on this

I have found the solution by the way. It seems that is related to the way the report was created or interpreted by reporting services. I created the reference to the stored procedure manually creating all the parameter and this way was giving the problem. If you select the stored procedure through the guy interface instead it is sending the right sp_executesql code to SQL server. It was quite painful and still not clear the reason by the way but now works.

SQLCH
  • 21
  • 1