LDAP Dialect and concatenation?
-
Can anyone tell me what the problem is with the following code snippet:
IF @UserProfileID = ''
BEGIN
SET @UserProfileID =
(SELECT sAMAccountName
FROM OPENQUERY(ADSI,'SELECT sAMAccountName
FROM ''LDAP://corp.jpmp.chase.com''
WHERE objectCategory = ''Person''
AND objectClass = ''User"''
AND givenName = ''' + @IndividualFirstName +
''' AND sn = ''' + @IndividualLastName +
''' AND displayName <> ''*SMTP''')
END
This is part of a query to get the sAMAccountName from LDAP. When I try to run it, I get an error: Incorrect syntax near "+". Does LDAP SQL dialect not like concatenation? If I plug string literals into it in place of the @params, it works (i.e. givenName = ''John'' AND sn = ''Smith''), so the query itself is OK.
Any help very much appreciated.
-
You need to build SQL string first:
declare @SQL nvarchar (4000)
select @SQL = 'SELECT sAMAccountName
FROM ''LDAP://corp.jpmp.chase.com''
WHERE objectCategory = ''Person''
AND objectClass = ''User"''
AND givenName = ''' + @IndividualFirstName +
''' AND sn = ''' + @IndividualLastName +
''' AND displayName <> ''*SMTP'''
SELECT sAMAccountName
FROM OPENQUERY(ADSI, @SQL )
_____________
Code for TallyGenerator -
Thanks, but I had already tried this and it also throws an error - Incorrect syntax near '@SQL' on the line
SELECT sAMAccountName
FROM OPENQUERY(ADSI, @SQL )
I'll keep searching...
-
OK, here's the code in its entirety just in case there's another error somewhere. I don't think there is, though, because if I use literal values in place of the @parameters in the LDAP part of the query, it works fine.
DECLARE c_extract_rainmaker_contact_data CURSOR FOR
SELECT isnull(substring(UserProfileID,charindex('\',UserProfileID)+1,len(UserProfileID)),'')
,isnull(UserDisplayName,'')
,isnull(UserEmailAddress,'')
,isnull(UserClientID,'')
,ci.ClientID
,isnull(UserPhone,'')
,isnull(UserFax,'')
,isnull(UserClientName,'')
,isnull(IndividualFirstName,'')
,isnull(IndividualLastName,'')
,isnull(InvestranLogin,'')
,PortalFormUser
FROM dbo.tmp_RainMakerContacts rm
JOIN dbo.ClientInfo ci
ON ci.ClientName = rm.UserClientID
DECLARE @UserProfileID varchar(128)
,@UserDisplayName varchar(403)
,@UserEmailAddress varchar(200)
,@UserClientID varchar(255)
,@UserClientIDAlt int
,@UserPhone varchar(30)
,@UserFax varchar(30)
,@UserClientName varchar(350)
,@IndividualFirstName varchar(200)
,@IndividualLastName varchar(150)
,@InvestranLogin varchar(128)
,@PortalFormUser bit
OPEN c_extract_rainmaker_contact_data
FETCH NEXT FROM c_extract_rainmaker_contact_data INTO
@UserProfileID
,@UserDisplayName
,@UserEmailAddress
,@UserClientID
,@UserClientIDAlt
,@UserPhone
,@UserFax
,@UserClientName
,@IndividualFirstName
,@IndividualLastName
,@InvestranLogin
,@PortalFormUser
WHILE @@FETCH_STATUS = 0
BEGIN
IF @UserProfileID = ''
BEGIN
DECLARE @SQL nvarchar (4000)
SELECT @SQL = 'SELECT sAMAccountName
FROM ''LDAP://corp.jpmp.chase.com''
WHERE objectCategory = ''Person''
AND objectClass = ''User"''
AND givenName = ''' + @IndividualFirstName +
''' AND sn = ''' + @IndividualLastName +
''' AND displayName <> ''*SMTP'''
--THIS IS WHAT THROWS THE SYNTAX ERROR--
SET @UserProfileID = (SELECT sAMAccountName
FROM OPENQUERY(ADSI, @SQL ))
END
DECLARE @insert_sql nvarchar(2000)
IF NOT EXISTS (SELECT * FROM dbo.UserProfile WHERE UserProfileID = @UserProfileID)
BEGIN
SET @insert_sql = 'INSERT INTO dbo.UserProfile (' +
'UserProfileID, UserDisplayName, UserDefaultDraftLocation, UserDefaultSubmitLocation, UserSharepointPersonalDocLibrary, UserSharepointSubmitLocation, UserEmailAddress, UserClientID, UserClientIDAlternate, UserPhone, UserFax, UserClientName, InvestranContactID) ' +
'VALUES ("' + @UserProfileID + '", "' + @UserDisplayName + '", "\\sifile01\home\' + @UserProfileID + '", "\\sifile01\SubmittedeForms", NULL, NULL, "' + @UserEmailAddress + '", "' + @UserClientID + '", ' + cast(@UserClientIDAlt as nchar(10)) + ', "' + @UserPhone + '", "' + @UserFax + '", "' + @UserClientName + '", 1)'
EXEC sp_executesql @insert_sql
END
ELSE
BEGIN
UPDATE dbo.UserProfile
SET UserEmailAddress = @UserEmailAddress, UserClientID = @UserClientID, UserClientIDAlternate = cast(@UserClientIDAlt as nchar(10)), UserPhone = @UserPhone, UserFax = @UserFax, UserClientName = @UserClientName
WHERE UserProfileID = @UserProfileID
EXEC sp_executesql @insert_sql
END
FETCH NEXT FROM c_extract_rainmaker_contact_data INTO
@UserProfileID
,@UserDisplayName
,@UserEmailAddress
,@UserClientID
,@UserClientIDAlt
,@UserPhone
,@UserFax
,@UserClientName
,@IndividualFirstName
,@IndividualLastName
,@InvestranLogin
,@PortalFormUser
END
CLOSE c_extract_rainmaker_contact_data
DEALLOCATE c_extract_rainmaker_contact_data
Any help appreciated!
-
This blog http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx will throw some light on your concatenation question.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply