Parameters passing in oledb provider for microsoft directory services in Reporting services

• seshu67

  Old Hand

  Points: 325

  More actions

  June 2, 2008 at 10:21 pm

  #187338

  Hi,

  I am working in reporting services.my task is to get active directory data in reporting services through query wise.here I am using oledb provider and Datasource as "oledb provider for Microsoft directory services" and connecting to activedirectory .when i am running following query in Datatab

  "select cn from 'LDAP://151.8.18.190' where samaccountname='Administrator' " its working fine.

  but under samaccountname instead of actual value if i pass parameter

  that is like this

  "select cn from 'LDAP://151.8.18.190' where samaccountname=@username" its generating following error.

  An error occurred while executing the query.

  The ICommandWithParameters interface is not supported by the 'ADsDSOObject' provider. Command parameters are unsupported with the current provider. (Microsoft Report Designer).

  how to resolve this.

  Any help will be greatly appreciated.

  Thanks,

  Seshu.

• Dave Balsillie

  SSCertifiable

  Points: 5589

  More actions

  June 4, 2008 at 7:19 am

  #824150

  Basically, this providor doesn't accept parameters.

  Try this:

  [Code]

  "select cn from 'LDAP://151.8.18.190' where samaccountname='" + @username + "'"

  [/Code]

  The difference here, is passing the select statement as a string, where @username has been concatinated into the string.

  Good luck and let us know how it goes.

• seshu67

  Old Hand

  Points: 325

  More actions

  June 5, 2008 at 7:04 am

  #824769

  hi Dave,

  I saw ur reply. I tried same query which u have given me in "oledb provider for microsoft directory services".but it is showing same error.

  any way first i ll thank u for replying me.

  I resolved this issue by creating linked server in sql server and passing query as a string.the query i am giving below.

  DECLARE @variable1 VARCHAR(8000)

  DECLARE @variable2 VARCHAR(8000)

  DECLARE @variable3 VARCHAR(8000)

  DECLARE @variable4 VARCHAR(8000)

  DECLARE @variable5 VARCHAR(8000)

  DECLARE @variable6 VARCHAR(8000)

  DECLARE @STR VARCHAR(max)

  SET @variable1 = 'Select * from openquery(ADSI,'

  SET @variable2 = '''select cn from '

  SET @variable3 = '''''LDAP://151.8.18.190'''''

  SET @variable4 = 'where samaccountname='

  SET @variable5 = '''''' + @username + ''''''

  SET @variable6 = '''' + ')'

  SET @STR=@variable1+@variable2+@variable3+@variable4+@variable5+@variable6

  EXEC(@str)

  In reporting services i am using "microsoft sql server" as provider and directly running above query means its working fine.

  ok thanx a lot.

  Regards,

  Seshu