May 10, 2018 at 7:13 am
Hello!
There's a procedure
declare @name nvarchar(100)
declare @sql nvarchar(max)
DECLARE @test-2 AS DATETIME
SET @test-2 = dateadd(dd,-28,getdate())
DECLARE @theDate varchar(22)
SET @theDate = concat(convert(varchar,dateadd(dd,-28,getdate()),112),'000000.Z0')
set @name = 'Ivanov'
set @sql=
'SELECT sAMAccountName as IdentityFrom
FROM OPENQUERY (
ADSI,
''SELECT sAMAccountName
FROM ''''LDAP://1.1.1.1/CN=Users,DC=v-domain,DC=com''''
WHERE
objectClass = ''''user''''
and objectCategory = ''''person''''
and ''''userAccountControl:1.2.840.113556.1.4.803:''''=2
and whenChanged > ''''' + @theDate + ''''' <---- 5!!!!! Quotation marks!!!!!
'')'
exec dbo.sp_executeSQL @sql
@theDate is a dynamic value.
The resulting table I plan to use as a view. @Declare is forbidden in the views.
Would someone please help to modify the procedure in order to it to became a view?
May 10, 2018 at 7:35 am
Here is a simplified version
DECLARE @theDate varchar(22)
SET @theDate = concat(convert(varchar,dateadd(dd,-28,getdate()),112),'000000.Z0')
SELECT *,
@theDate
FROM OPENQUERY(ADSI,
'
SELECT sAMAccountName
FROM ''LDAP://10.10.10.10/CN=Users,DC=v-domain,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND whenChanged > '' 20180505000000.Z0 ''
AND ''userAccountControl:1.2.840.113556.1.4.803:''=2'
)
AS derivedtbl_1
so, instead of AND whenChanged > '' 20180505000000.Z0 '' I would like to use @theDate variable. But no matter how I frame it in quotes I get an error message http://joxi.ru/DmBgD8GTwd8672
May 10, 2018 at 9:00 am
nkaretnikov - Thursday, May 10, 2018 7:35 AMHere is a simplified version
DECLARE @theDate varchar(22)
SET @theDate = concat(convert(varchar,dateadd(dd,-28,getdate()),112),'000000.Z0')
SELECT *,
@theDate
FROM OPENQUERY(ADSI,
'
SELECT sAMAccountName
FROM ''LDAP://10.10.10.10/CN=Users,DC=v-domain,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND whenChanged > '' 20180505000000.Z0 ''
AND ''userAccountControl:1.2.840.113556.1.4.803:''=2'
)
AS derivedtbl_1
so, instead of AND whenChanged > '' 20180505000000.Z0 '' I would like to use @theDate variable. But no matter how I frame it in quotes I get an error message http://joxi.ru/DmBgD8GTwd8672
Nope. OPENQUERY won't work that way. You will need to use dynamic SQL to accomplish what you are attempting. Also, pretty sure you datetime representation won't work either. Never seen the format you are proposing.
May 10, 2018 at 9:21 am
Give this a try:
DECLARE @theDate varchar(22)
, @SQLCmd NVARCHAR(MAX);
SET @theDate = CONVERT(VARCHAR(22),CAST(CAST(dateadd(dd,-28,getdate()) AS DATE) AS DATETIME2),126)
SET @SQLCmd = N'
SELECT
*,
''' + @theDate + '''
FROM
OPENQUERY(ADSI,''
SELECT
sAMAccountName
FROM
''LDAP://10.10.10.10/CN=Users,DC=v-domain,DC=com''
WHERE
objectCategory = ''Person''
AND objectClass = ''USER''
AND whenChanged > ''' + @theDate + '''
AND ''userAccountControl:1.2.840.113556.1.4.803:''=2''
)
AS derivedtbl_1
';
PRINT @SQLCmd;
-- EXECUTE (@SQLCmd);
May 10, 2018 at 11:08 am
Lynn Pettis - Thursday, May 10, 2018 9:00 AMnkaretnikov - Thursday, May 10, 2018 7:35 AMHere is a simplified version
DECLARE @theDate varchar(22)
SET @theDate = concat(convert(varchar,dateadd(dd,-28,getdate()),112),'000000.Z0')
SELECT *,
@theDate
FROM OPENQUERY(ADSI,
'
SELECT sAMAccountName
FROM ''LDAP://10.10.10.10/CN=Users,DC=v-domain,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND whenChanged > '' 20180505000000.Z0 ''
AND ''userAccountControl:1.2.840.113556.1.4.803:''=2'
)
AS derivedtbl_1
so, instead of AND whenChanged > '' 20180505000000.Z0 '' I would like to use @theDate variable. But no matter how I frame it in quotes I get an error message http://joxi.ru/DmBgD8GTwd8672Nope. OPENQUERY won't work that way. You will need to use dynamic SQL to accomplish what you are attempting. Also, pretty sure you datetime representation won't work either. Never seen the format you are proposing.
Funny thing. When I simply execute that code there is no error message, it only happens during the save operation ))
May 10, 2018 at 11:11 am
nkaretnikov - Thursday, May 10, 2018 11:08 AMLynn Pettis - Thursday, May 10, 2018 9:00 AMnkaretnikov - Thursday, May 10, 2018 7:35 AMHere is a simplified version
DECLARE @theDate varchar(22)
SET @theDate = concat(convert(varchar,dateadd(dd,-28,getdate()),112),'000000.Z0')
SELECT *,
@theDate
FROM OPENQUERY(ADSI,
'
SELECT sAMAccountName
FROM ''LDAP://10.10.10.10/CN=Users,DC=v-domain,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND whenChanged > '' 20180505000000.Z0 ''
AND ''userAccountControl:1.2.840.113556.1.4.803:''=2'
)
AS derivedtbl_1
so, instead of AND whenChanged > '' 20180505000000.Z0 '' I would like to use @theDate variable. But no matter how I frame it in quotes I get an error message http://joxi.ru/DmBgD8GTwd8672Nope. OPENQUERY won't work that way. You will need to use dynamic SQL to accomplish what you are attempting. Also, pretty sure you datetime representation won't work either. Never seen the format you are proposing.
Funny thing. When I simply execute that code there is no error message, it only happens during the save operation ))
Not sure what you mean, it only happens during the save operation.
May 10, 2018 at 11:12 am
Lynn Pettis - Thursday, May 10, 2018 9:21 AMGive this a try:
DECLARE @theDate varchar(22)
, @SQLCmd NVARCHAR(MAX);SET @theDate = CONVERT(VARCHAR(22),CAST(CAST(dateadd(dd,-28,getdate()) AS DATE) AS DATETIME2),126)
SET @SQLCmd = N'
SELECT
*,
''' + @theDate + '''
FROM
OPENQUERY(ADSI,''
SELECT
sAMAccountName
FROM
''LDAP://10.10.10.10/CN=Users,DC=v-domain,DC=com''
WHERE
objectCategory = ''Person''
AND objectClass = ''USER''
AND whenChanged > ''' + @theDate + '''
AND ''userAccountControl:1.2.840.113556.1.4.803:''=2''
)
AS derivedtbl_1
';PRINT @SQLCmd;
-- EXECUTE (@SQLCmd);
Lynn, thanks! I mentioned that with execute I can compose a correct script.
My intended was to use dynamic date within a view, so, the OPENQUERY code should not be a variable but a script without quote. Is that really impossible and I should stuck with a non elegant solution - running a execute command?
And on the date - this format is necessary for LDAP filtering. And it works, trust me 🙂
May 10, 2018 at 11:15 am
nkaretnikov - Thursday, May 10, 2018 11:12 AMLynn Pettis - Thursday, May 10, 2018 9:21 AMGive this a try:
DECLARE @theDate varchar(22)
, @SQLCmd NVARCHAR(MAX);SET @theDate = CONVERT(VARCHAR(22),CAST(CAST(dateadd(dd,-28,getdate()) AS DATE) AS DATETIME2),126)
SET @SQLCmd = N'
SELECT
*,
''' + @theDate + '''
FROM
OPENQUERY(ADSI,''
SELECT
sAMAccountName
FROM
''LDAP://10.10.10.10/CN=Users,DC=v-domain,DC=com''
WHERE
objectCategory = ''Person''
AND objectClass = ''USER''
AND whenChanged > ''' + @theDate + '''
AND ''userAccountControl:1.2.840.113556.1.4.803:''=2''
)
AS derivedtbl_1
';PRINT @SQLCmd;
-- EXECUTE (@SQLCmd);Lynn, thanks! I mentioned that with execute I can compose a correct script.
My intended was to use dynamic date within a view, so, the OPENQUERY code should not be a variable but a script without quote. Is that really impossible and I should stuck with a non elegant solution - running a execute command?
And on the date - this format is necessary for LDAP filtering. And it works, trust me 🙂
https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-2017
May 10, 2018 at 11:33 am
Lynn Pettis - Thursday, May 10, 2018 11:11 AMNot sure what you mean, it only happens during the save operation.
May 10, 2018 at 11:35 am
nkaretnikov - Thursday, May 10, 2018 11:33 AMI mean, adding the script into a design view of MS SQL Management Studio and execution of it gives the necessary result just fine.
http://joxi.ru/p27Z7axTo390Gm - SQL query
http://joxi.ru/v29Rl31c3dX3jA - the same query in a view design
http://joxi.ru/n2YL5g1UoGwopm - and this is what happens when I hit "Save"Lynn Pettis - Thursday, May 10, 2018 11:11 AMNot sure what you mean, it only happens during the save operation.
Sorry, not clicking on the links you posted. If you want, upload screen shots.
May 10, 2018 at 11:36 am
Also, read the Microsoft documentation using the link I had posted (that is a link I would click on).
May 10, 2018 at 11:37 am
Lynn Pettis - Thursday, May 10, 2018 11:15 AM
I guess, you're saying "this is impossible" 🙂
May 10, 2018 at 11:38 am
nkaretnikov - Thursday, May 10, 2018 11:37 AMLynn Pettis - Thursday, May 10, 2018 11:15 AMI guess, you're saying "this is impossible" 🙂
Yes, at least for what you are trying to accomplish. I have had to use dynamic SQL to get around that limitation in OPENQUERY.
May 10, 2018 at 11:46 am
Lynn Pettis - Thursday, May 10, 2018 11:38 AMYes, at least for what you are trying to accomplish. I have had to use dynamic SQL to get around that limitation in OPENQUERY.
joxy.ru it's just another cloud base screenshot service. It is like joxy.ru is reported as a suspicious site by your browser? By that is little of importance.
I'll proceed with sql execute, no need to charge SQL with constant update of dynamic date, anyway.
May 14, 2018 at 3:03 pm
nkaretnikov - Thursday, May 10, 2018 11:46 AMLynn Pettis - Thursday, May 10, 2018 11:38 AMYes, at least for what you are trying to accomplish. I have had to use dynamic SQL to get around that limitation in OPENQUERY.
joxy.ru it's just another cloud base screenshot service. It is like joxy.ru is reported as a suspicious site by your browser? By that is little of importance.
I'll proceed with sql execute, no need to charge SQL with constant update of dynamic date, anyway.
Bottom line is that dynamic SQL is required in order to do what you want, and that can NOT be part of a view. Views don't allow dynamic SQL, and neither do functions. Only stored procedures and ad-hoc queries (run in SSMS) can do so.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply