May 14, 2008 at 11:19 am
CREATE TABLE #XYZ(
NTUser varchar(20),
FullNTName varchar(50),
FirstName varchar(20),
MiddleName varchar(20),
LastName varchar(40),
Rolename varchar(30))
DECLARE @NAME AS varchar(1000)
DECLARE @ADDRESS AS varchar(1000)
Declare CUR_C Cursor
For
Select Rolename
From DCJ_SecurityRole
Open CUR_C
Fetch Next From CUR_C
into @NAME
While @@fetch_status =0
Begin
IF @NAME not in('All','PUBLIC')
Begin
SET @ADDRESS = 'cn='+'''' + @NAME +''''+',OU=Groups, OU=AJP,DC=XYZ,DC=com'
INSERT INTO #UserDetail
EXEC ('
SELECT SAMAccountName as NTUSER,name as FULLNTNAME,givenname as FIRSTNAME,
initials as MIDDLENAME,sn as LASTNAME,''' + @NAME + ''' as Rolename FROM OPENQUERY
(ADSI, ''SELECT sAMAccountName,name,givenname,initials,sn FROM
''''LDAP://DC=XYZ,DC=com''''
WHERE
MemberOf=''''' + @ADDRESS +''''' '')
')
END
Fetch Next From CUR_C
into @NAME
END
CLOSE CUR_C
DEALLOCATE CUR_C
When I executes this code I am getting some syntax error like
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'APJPRD',where APJPRD is one of the role,
Can anyone suggest what dynamic sql part has error??
May 14, 2008 at 11:27 am
Replace the EXEC with a PRINT and have a look at what the constructed SQL statement is. You should be able to spot syntax errors fairly easily that way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2008 at 11:33 am
Thanks for that but I already did that and I can see this error
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'PRINT'
May 14, 2008 at 11:34 am
GilaMonster (5/14/2008)
Replace the EXEC with a PRINT and have a look at what the constructed SQL statement is. You should be able to spot syntax errors fairly easily that way.
And if you're having a slow day and you can't see it that way...Copy the output into a new SSMS query window and try to execute it. The parser will point you to where it thinks you have a problem.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 11:46 am
Did you comment the insert out as well?
This parses. It won't run, cause I don't have the tables, but it does parse.
CREATE TABLE #XYZ(
NTUser varchar(20),
FullNTName varchar(50),
FirstName varchar(20),
MiddleName varchar(20),
LastName varchar(40),
Rolename varchar(30))
DECLARE @NAME AS varchar(1000)
DECLARE @ADDRESS AS varchar(1000)
Declare CUR_C Cursor
For
Select Rolename
From DCJ_SecurityRole
Open CUR_C
Fetch Next From CUR_C
into @NAME
While @@fetch_status =0
Begin
IF @NAME not in('All','PUBLIC')
Begin
SET @ADDRESS = 'cn='+'''' + @NAME +''''+',OU=Groups, OU=AJP,DC=XYZ,DC=com'
-- INSERT INTO #UserDetail -- commented out cause you can't insert the results of a print...
PRINT '
SELECT SAMAccountName as NTUSER,name as FULLNTNAME,givenname as FIRSTNAME,
initials as MIDDLENAME,sn as LASTNAME,''' + @NAME + ''' as Rolename FROM OPENQUERY
(ADSI, ''SELECT sAMAccountName,name,givenname,initials,sn FROM
''''LDAP://DC=XYZ,DC=com''''
WHERE
MemberOf=''''' + @ADDRESS +''''' '')
'
END
Fetch Next From CUR_C
into @NAME
END
CLOSE CUR_C
DEALLOCATE CUR_C
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2008 at 11:53 am
--I know it parses successfully but when I execute I am geting this rror
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'APJPRD'.
I have 16 roles like this in my table I am geting 16 lines of errors like above
What could be the reason???
May 15, 2008 at 12:09 am
What does the print for the dynamic SQL return? Or does that also return an error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2008 at 9:37 am
When I'm stuck like this, I usually go crazy with commenting out code until I get to something that does work.
So in this case, I'd recommend commenting out everything except the initial SET, then try to PRINT that and see if it works (like GilaMonster suggested). Then, if that does work, I'd say create your entire SQL string (SELECT SAMAccountName...) into another variable, then try to PRINT that.
I think it'd be helpful for us to see an example @ADDRESS value to play with.
May 19, 2008 at 8:42 am
The value created by the SQL engine forms an invalid string in the neighborhood of "APJPRD". Which of your @variables reads the value "APJPRD"? The four single quotes leave one quote around the value. If the value "APJPRD", being read from the table, should be unquoted, then adjust the number of single quotes.
Thanks, Ralph Luman
May 20, 2008 at 4:39 am
Uhmmmmm,
I may be just a lil daft here, but you are creating temp table #XYZ, and inserting into #UserDetail.
With everyone on commenting out your lines of code and adding piece by piece.
Indentation may also help you a little.
~PD
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply