Sql server error msg 7321

sql server error msg 7321

The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery...: Do one or more. techyaz.com › sql-server › troubleshooting › server-msg-7321-level-16-sta. Server: Msg 7321, Level 16, State 2, Line 1. An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'. sql server error msg 7321

Sql server error msg 7321 - remarkable

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-04-03 : 13:38:16
I am getting this error when querying the Active Directory

Msg 7321, Level 16, State 2, Line 27
An error occurred while preparing the query "SELECT DisplayName, title,mail
FROM 'LDAP://CBC-03/DC=jhcohn,DC=com'
WHERE objectClass = 'Person'
" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

I am not the DOmain Admin, but the lady that is gets the same error.

Here is the Code that linked the server:

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'cbc-03.jhcohn.com' --AKA the full computer name of the AD server

Here is the query code:

SELECT * FROM OpenQuery
(
ADSI,
'SELECT DisplayName, title,mail
FROM ''LDAP://CBC-03/DC=jhcohn,DC=com''
WHERE objectClass = ''Person''
')

Any ideas, thanks in advance

Bryan Holmstrom

Msg 7321, Level 16, State 2, Line 68
An error occurred while preparing the query "

Solution:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/083229e7-58f2-44cf-ae5d-c9acf366818c/sql-2005-ldap-query-error-msg-7321-level-16?forum=transactsql

It normally succeedes test connection generally but when you run query using this linked server to pull data from AD(Active Directory) server it will throw above issue. so issue will be between db server of linked server and AD server.

we need to make sure logins should be same on both AD server and db server
of linked server created.

LDAP means Lightweight Directory Access Protocol


First execute your query:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

'ADSDSOObject'

, 'adsdatasource'

GO

Like daw21 explain before follow the detail steps:

Second step go to the SqlServer management Studio and edit manually the Linkserver name ADSI:

go to Server Objects/Linked Servers/  and right click on ADSI  select the option Properties and then go to Security.

Select the option :         Be made using this security context:

and there fill the user  and Password about the valid user with permission into Active Directory, not forget before the domain\user.



(or)

First you have to make sure that Ad Hoc Distributed Queries is enabled ...

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Second you have to make sure that you specify a security context that has access to AD INCLUDING the DOMAIN
Third you should put a row limit (e.g. SELECT TOP 100) in to avoid the issue where you hit the 1000 row limit so
SELECT



TOP 100 *

FROM



OpenQuery(ADSI, 'SELECT sAMAccountName FROM ''LDAP://DC=core,DC=acllab,DC=net'' WHERE objectCategory=''user'' ')

Dear community,

 

I am trying to make a select on the field 'Date' in the table UVT_DatesOfVersionsBOPos using the Microsoft SQL Server Management Studio (select * from openquery ([PROALPHA], 'select Date from PAVAR.PUB.UVT_DatesOfVersionsBOPos')

 

Unfortunately I get the following error message:

 

The OLE DB provider "MSDASQL" for the connection server "PROALPHA" has the message "[DataDirect] [ODBC Progress OpenEdge Wire Protocol driver] [OPENEDGE] Syntax error in SQL statement at or about" Date from PAVAR.PUB.UVT_DatesOfVersionsB "( 10713) ".

Msg 7321, Level 16, State 2, Line 1

Failed to prepare the select date from PAVAR.PUB.UVT_DatesOfVersionsBOPos query to run for the OLE DB provider "MSDASQL" for the linked server "PROALPHA".

 

I assume that SQL interprets the Date field as the Date function and therefore displays an error.

 

Does anyone of you know the correct syntax so I can get the field?

 

I thank you in advance for your support

 

Best regards

 

Marcel

 

 

Linked Server Error Between SQL Server and Oracle

Jevon

I have used Linked Servers to hook up to an Oracle 9i database in SQL
Server 2000. I can see all the Tables and Views when I look in the
linked server section. I can also run the following SQL statement :

Select *
from Orcldb..SYSTEM.CrossTabSaveSites

and get a return result. The problem I realize is that some of the
tables are going to be quite larger and I can't wait for SQL Server to
download all the data and parse it itself, so I tried to use a
passthrough query like so :

SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROM
SYSTEM.CrossTabSaveSites')

Unfortunately this gives me the error message :

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00942: table or view does not
exist
]

There is also the issue of speed. It takes about 15 seconds to come
back to me to tell me that the table/view doesn't exist, but sometimes
it comes back immediately. Is this something with the user
permissions in Oracle? (Don't really know much about Oracle) It just
seems like a very long time to wait just to let me know that the item
doesn't exist.

Any help would be greatly appreciated,

-Jevon Thurlow

Jul 20 '05

Simon Hayes


"Jevon" <je**********@hotmail.com> wrote in message
news:34**************************@posting.google.c om...

I have used Linked Servers to hook up to an Oracle 9i database in SQL
Server 2000. I can see all the Tables and Views when I look in the
linked server section. I can also run the following SQL statement :

Select *
from Orcldb..SYSTEM.CrossTabSaveSites

and get a return result. The problem I realize is that some of the
tables are going to be quite larger and I can't wait for SQL Server to
download all the data and parse it itself, so I tried to use a
passthrough query like so :

SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROM
SYSTEM.CrossTabSaveSites')

Unfortunately this gives me the error message :

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00942: table or view does not
exist
]

There is also the issue of speed. It takes about 15 seconds to come
back to me to tell me that the table/view doesn't exist, but sometimes
it comes back immediately. Is this something with the user
permissions in Oracle? (Don't really know much about Oracle) It just
seems like a very long time to wait just to let me know that the item
doesn't exist.

Any help would be greatly appreciated,

-Jevon Thurlow


I can't tell you anything about the performance issue, but the ORA-00942
error is probably because Oracle is case-sensitive, and object names are
usually in upper case:

http://support.microsoft.com/default...b;EN-US;240340
http://support.microsoft.com/default...b;EN-US;280106

Simon

Jul 20 '05

This discussion thread is closed

Start new discussion

Replies have been disabled for this discussion.

Read these next...

Hello,

I am trying to use openquery with two ou. However, I am getting following error.

Below is my script:

 select displayName,DEPARTMENT,mail,physicalDeliveryOfficeName,telephoneNumber
             from OpenQuery ( ad,  
  'SELECT
  employeeid, sAMAccountName,instanceType,objectCategory, mail
  ,displayName, DistinguishedName
  ,department,isDeleted,name
  ,logoncount
  ,createTimeStamp,modifyTimeStamp
  ,lastLogonTimestamp, lastlogon
  ,UserAccountControl
  ,physicalDeliveryOfficeName
  ,telephoneNumber
  FROM ''LDAP://OU=Faculty,OU=Staff,DC=domain,DC=com''  


  WHERE objectClass = ''User''  
  ')

Here is the error message that I am getting:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT
  employeeid, sAMAccountName,instanceType,objectCategory, mail
  ,displayName, DistinguishedName
  ,department,isDeleted,name
  ,logoncount
  ,createTimeStamp,modifyTimeStamp
  ,lastLogonTimestamp, lastlogon
  ,UserAccountControl
  ,physicalDeliveryOfficeName
  ,telephoneNumber
  FROM 'LDAP://OU=Faculty,OU=Staff,DC=domain,DC=com'  


  WHERE objectClass = 'User'  
  " for execution against OLE DB provider "ADSDSOObject" for linked server "ad".

Please help me out to solve this error.

Thanks in advance

  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    I have not been able to use 'OU=xxxx' in any of my ADSI queries.

    I tried your ADSI query on my system and it works if I use the following:

    Text

    FROM ''LDAP://DC=domain,DC=com''  

    Make sure that 'domain' and 'com' are actually the proper domain prefix and suffix for your AD system.
    For example if your AD system domain is mycompany.org, then you should use 'mycompany' in place of 'domain' and 'org' in place of 'com'.

    Text

    FROM ''LDAP://DC=mycompany,DC=org''  

    Also note I am using two sets of single quotes here within the string (since we are embedding this within another string); do NOT use double quotes.

    You also need to have server 'ad' setup as a linked server with the following linked server properties (or similar):
    Provider: OLE DB Provider for Microsoft Directory Services
    Product Name: Active Directory Services 2.5

    There is also a limit on the number of rows that can be returned by this type of query.  I have found it to be about 1000 rows or so.
    So if your AD system has more than 1000 users in it, the query will likely timeout and fail.
    If you keep the number of returned rows below 500, you should not have a problem.

    Something like the following:

    Text

    FROM ''LDAP://DC=mycompany,DC=org'' where objectClass = ''User'' and name<''a'' FROM ''LDAP://DC=mycompany,DC=org'' where objectClass = ''User'' and name>=''a'' and name<''c''
    Was this post helpful?thumb_upthumb_down
  • You can also add a TOP() clause to limit the number of rows returned:

    SQL

    SELECTTOP(100)<columnlist>FROM<table>
    Was this post helpful?thumb_upthumb_down
  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    To get around the row limit problem, I simply combined them in a VIEW using UNION ALL, where each query would get a section of the sorted alphabet.

    First one has <'a', second has >='a' and <'c', third has >='c' and <'e', and so on.

    Takes a minute or two to do the whole view query.
    For efficiency, I just do this a few times a day and insert the results into an actual SQL table for subsequent fast retrieval.
    Mine has about 4000 users in it, not too big.

    Was this post helpful?thumb_upthumb_down
  • I have around 250 emaployees and adjunct faculty

  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    RV16 wrote:

    I have around 250 emaployees and adjunct faculty

    Ok.  Did you try any of my suggestions to get your query to work?

  • I tried but getting same error:

    select displayName,DEPARTMENT,mail,physicalDeliveryOfficeName,telephoneNumber

                 from OpenQuery ( adsi,  

      'SELECT

      employeeid, sAMAccountName,instanceType,objectCategory, mail

      ,displayName, DistinguishedName

      ,department,isDeleted,name

      ,logoncount

      ,createTimeStamp,modifyTimeStamp

      ,lastLogonTimestamp, lastlogon

      ,UserAccountControl

      ,physicalDeliveryOfficeName

      ,telephoneNumber

      FROM ''LDAP://OU=Employees,DC=domain,DC=com''  

      WHERE objectClass = ''User''

      UNION

       SELECT

      employeeid, sAMAccountName,instanceType,objectCategory, mail

      ,displayName, DistinguishedName

      ,department,isDeleted,name

      ,logoncount

      ,createTimeStamp,modifyTimeStamp

      ,lastLogonTimestamp, lastlogon

      ,UserAccountControl

      ,physicalDeliveryOfficeName

      ,telephoneNumber

      FROM ''LDAP://OU=Faculty,DC=domain,DC=com''  

      ')


  • I also try through view but still getting the same error:

    CREATE VIEW [vADUsers1] AS
    SELECT displayName,DEPARTMENT,title,mail,physicalDeliveryOfficeName,telephoneNumber
    FROM OPENQUERY(adsi,
      'SELECT
      employeeid, sAMAccountName,instanceType,objectCategory, mail
      ,displayName, DistinguishedName
      ,department,isDeleted,name
      ,logoncount
      ,createTimeStamp,modifyTimeStamp
      ,lastLogonTimestamp, lastlogon
      ,UserAccountControl
      ,physicalDeliveryOfficeName
      ,telephoneNumber
      ,title
      FROM ''LDAP://OU=Employees,DC=domain,DC=edu''
      WHERE objectClass = ''User''
    UNION ALL

    SELECT
      employeeid, sAMAccountName,instanceType,objectCategory, mail
      ,displayName, DistinguishedName
      ,department,isDeleted,name
      ,logoncount
      ,createTimeStamp,modifyTimeStamp
      ,lastLogonTimestamp, lastlogon
      ,UserAccountControl
      ,physicalDeliveryOfficeName
      ,telephoneNumber
      ,title
      FROM ''LDAP://OU=Faculty,DC=domain,DC=edu''
      WHERE objectClass = ''User''

      ');


  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    1) Try removing the OU sections.

    ''LDAP://DC=domain,DC=com''  

    2) Is your domain really 'domain' and suffix 'com' ?  You are changing these to your actual domain name and suffix, correct?

    3) Your linked server name is 'adsi', correct?  Previously you had 'ad'.  This linked server name has to match whatever is entered in your SQL Server Linked Server list for your ADSI server.

  • But I only want employee and adjunct faculty information. 

    When I remove OU,I am getting following error:

    Msg 7330, Level 16, State 2, Line 1
    Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "adsi".

  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    You should get a working query first before trying to change it otherwise you will never get anywhere.
    You can modify your query results by using 'where' conditions instead of changing the LDAP string.

    So let's not worry about the employee and adjunct stuff now, and try to get the query working.
    I can replicate the same error message you get (Msg 7330) if the number of returned rows from ADSI is over 900 (SQL Server 2008).

    Please put in one more condition where we restrict the names to those less than 'b' :

    Text

    FROM ''LDAP://DC=domain,DC=edu'' where objectClass = ''User'' and name<''b''
    Was this post helpful?thumb_upthumb_down
  • Thanks Alec6638. I appreciated for your help.

     I have used above script and used as a view. And now I am trying to join that view with another table in other script.

    I am getting result. However, the execution is very slow. Any ideas to solve this?

    create view ViewActiveDir

     As

     select *

                 from OpenQuery ( adsi,  

      'SELECT

      employeeid, sAMAccountName,instanceType,objectCategory, mail

      ,displayName, DistinguishedName

      ,department,isDeleted,name

      ,logoncount

      ,createTimeStamp,modifyTimeStamp

      ,lastLogonTimestamp, lastlogon

      ,UserAccountControl

      ,title

      ,telephoneNumber

      ,physicalDeliveryOfficeName

      FROM ''LDAP://OU=Employees,DC=domain,DC=edu''  

      WHERE objectClass = ''User''  

      ')

      union all

      select *

                 from OpenQuery ( adsi,  

      'SELECT

      employeeid, sAMAccountName,instanceType,objectCategory, mail

      ,displayName, DistinguishedName

      ,department,isDeleted,name

      ,logoncount

      ,createTimeStamp,modifyTimeStamp

      ,lastLogonTimestamp, lastlogon

      ,UserAccountControl

      ,title

      ,telephoneNumber

      ,physicalDeliveryOfficeName

      FROM ''LDAP://OU=Adjunct Faculty,DC=domain,DC=edu''  

      WHERE objectClass = ''User''  

      ')

    select

    distinct ('<img src=http://my.domain.com/JICSPhotos/'+nm.LAST_NAME+','+nm.FIRST_NAME+'-'+convert(varchar(8),nm.ID_NUM... width=100 hspace="10">') as 'PICTURE ID',

    nm.FIRST_NAME,nm.LAST_NAME,nm.EMAIL_ADDRESS,ad.department,ad.telephoneNumber,ad.physicalDeliveryOfficeName,

    nm.TITLE

           from NAME_MASTER nm

    Join viewActiveDir ad on nm.ID_NUM=ad.employeeid

    join TW_GRP_MEMBERSHIP gm on gm.ID_NUM=nm.ID_NUM

    join EMPL_MAST em on em.ID_NUM=nm.ID_NUM

    join IND_POS_HIST iph on iph.ID_NUM=em.ID_NUM

    and em.ACT_INACT_STS='A'

    and nm.id_num<>'4300458'

    and (em.GRP_CDE not in ('WKST','WKSTF','WKSTS'))

    and (gm.GROUP_ID like 'staff' or gm.GROUP_ID like'faculty')

    Thank you


  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    Queries from Active Directory via LDAP ADSI will always be slower than SQL queries from native SQL tables.

    How often do you need updated data from Active Directory ?  Every day?

    You can try doing the following on a schedule that meets your needs (hourly, daily, other):

    SQL

    IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.TABLESWHERETABLE_NAME='ViewActiveDirTable')DROPTABLEViewActiveDirTable;SELECT*INTOViewActiveDirTableFROMViewActiveDir;

    And in your last query where you do the JOINS, replace ViewActiveDir with ViewActiveDirTable.

  • Thanks for the solution. 

    Could I use SSIS in this case? How?

    Please give me some example.

    Thank you

    Ripa

  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    checkBest Answer

    No need for SSIS here, just schedule the SQL statements as a job within SQL Server Agent.


    Was this post helpful?thumb_upthumb_down
  • Thank you very much. It solved my whole issue. I appreciated.

  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    RV16 wrote:

    Thank you very much. It solved my whole issue. I appreciated.

    It is always good to hear that a solution has been reached!

    Was this post helpful?thumb_upthumb_down
  • Spark! Pro series - 1st September 2022

    Spark! Pro series - 1st September 2022

    Spiceworks Originals

    Today in History: Wreck of the Titanic Found Seventy-three years after it sank to the North Atlantic ocean floor, a joint U.S.-French expedition locates the wreck of the RMS Titanic. The sunken liner was about 400 miles east of Newfoundland in the ...

  • SpiceQuest September (2022) -- Of Bacon and Books... and Bacon

    SpiceQuest September (2022) -- Of Bacon and Books... and Bacon

    Spiceworks Originals

    Welcome to another SpiceQuest! In this series, we call out current holidays and give you the chance to earn the monthly SpiceQuest badge! (Each task can be done at any time. They don't have to be completed on a certain holiday.) This month w...

  • Art Imitates Life: DALL·E 2 AI Generates Art Featuring IT Professionals

    Art Imitates Life: DALL·E 2 AI Generates Art Featuring IT Professionals

    Water Cooler

    In my last post about computer generated art, I tested artificial intelligence's ability to create orange T-Rex art in the style of many famous artists, using OpenAI's DALL·E 2 AI.Quick recap of how DALL·E 2 works. Users can request images of anything by ...

  • Snap! Azure outage, malware in JWST images, floppy disks, Voyager 1, & more

    Snap! Azure outage, malware in JWST images, floppy disks, Voyager 1, & more

    Spiceworks Originals

    Your daily dose of tech news, in brief. While the month may be ending, we're halfway through the week. Let's jump into today's Snap! and see some of the things going on in the world. You need to hear this. Microsoft Azure outage knocks Ubuntu...

  • Monitor our Weblogs

    Monitor our Weblogs

    Security

    Hi,I would like to ask for your recommendations on how I can capture the websites our end users access during office hours.our current firewall doesnt support it as it only logs HTTP connections and we all know that almost all of the websites now are usin...

I have three Microsoft SQL Servers 2005 Enterprise Edition.
We installed IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider version 10.124.107.200 on all of them.
We created the linked server PS_DWH using this provider to perform queries agains a DB2 database.
On one of those servers, I'm having issues to run the following query:

select * from openquery(ps_dwh,'select substring(abalph,2,4) from proddta.f0101')

It raises the following error:

OLE DB provider "IBMDA400" for linked server "ps_dwh" returned message "SQL0104: Token ,2 was not valid. Valid tokens: , FROM.
Cause . . . . . : A syntax error was detected at token ,2. Token ,2 is not a valid token. A partial list of valid tokens is , FROM. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token ,2. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query " 
select substring(abalph,2,4) from proddta.f0101
" for execution against OLE DB provider "IBMDA400" for linked server "ps_dwh".

If I add spaces among the substring function's parameters, the query works:

select * from openquery(ps_dwh,'select substring(abalph, 2, 4) from proddta.f0101')

Does anybody know why?

Remembering that it works on the other two SQL Servers, it doesn't matter whether the spaces exist or not. Do I need to set something?

Thanks,

Felipe

Msg 7321 An error occurred while preparing the query "query-text-here" for execution against OLE DB provider "MSOLAP" for linked server "OLAP_SERVER_NAME_DEV"

I have two instances of SQL 2005 on the same box one DEV and one QA - both are at version 9.00.3233.00, on Windows Server 2003 Standard SP2, sql server error msg 7321. The OLAP server is 64bit 2005 Enterprise on Windows Server 2003

The following query works only on the DEV instance.

SELECT *

FROM OPENQUERY

(

OLAP_SERVER_NAME_DEV

'SELECT FLATTENED PREDICT([Modified Related Items WebP_ID1].[v Assoc Seq Line Items], INCLUDE_STATISTICS, 4) AS WEBP_ID

FROM [Modified Related Items WebP_ID1]

NATURAL PREDICTION JOIN

(

SELECT

(

SELECT 4361542 AS [Web P ID]

) AS [v Assoc Seq Line Items]

) AS t '

)

WHERE [WEBP_ID.$ADJUSTEDPROBABILITY] > .1

Here's the error it throws on the QA instance:

Msg 7321 An error occurred while preparing the query "query-text-here" for execution against OLE DB provider "MSOLAP" for linked server "OLAP_SERVER_NAME_DEV"

Here's the script that I used to create the linked server on both instances:

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver

@server = N'OLAP_SERVER_NAME_DEV',

@srvproduct=N'Analysis Services 2005',

@provider=N'MSOLAP',

@datasrc=N'OLAP_SERVER_NAME',

@catalog=N'RelatedItems'

GO

EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER_NAME_DEV', @optname=N'rpc', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER_NAME_DEV', @optname=N'rpc out', @optvalue=N'true'

GO

EXEC master.dbo.sp_MSset_oledb_prop N'MSOLAP', N'AllowInProcess', 1

GO

Here are the steps taken to resolve so far:

1) Performed Windows Update

2) Reinstalled Microsoft OLE DB Provider for Analysis Services 9.0 from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Anyone thoughts on this?

Applications come and go, but data lasts forever!

Msg 7321, Level 16, State 2, Line 68
An error occurred while preparing the query "

Solution:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/083229e7-58f2-44cf-ae5d-c9acf366818c/sql-2005-ldap-query-error-msg-7321-level-16?forum=transactsql

It normally succeedes test connection generally but when you run query using this linked server to pull data from AD(Active Directory) server it will throw above issue. so issue will be between db server of linked server and AD server.

we need to make sure logins should be same on both AD server and db server
of linked server created.

LDAP means Lightweight Directory Directx error 2005530518 Protocol


First execute your query:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

'ADSDSOObject'

'adsdatasource'

GO

Like daw21 explain before follow the detail steps:

Second step go to the SqlServer management Studio and edit manually the Linkserver name ADSI:

go to Server Objects/Linked Servers/  and right click on ADSI  select the option Properties and then go to Security.

Select the option :         Be made using this security context:

and there fill the user  and Password about the valid user with permission into Active Directory, not forget before the domain\user.



(or)

First you have to make sure that Ad Hoc Distributed Queries is enabled .

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Second you have to make sure that you specify a security context that has access to AD INCLUDING the DOMAIN
Third you should put a row limit (e.g. SELECT TOP 100) in to avoid the issue where you hit the 1000 row limit so
SELECT



TOP 100 *

FROM



OpenQuery(ADSI, 'SELECT sAMAccountName FROM ''LDAP://DC=core,DC=acllab,DC=net'' WHERE objectCategory=''user'' ')

SQL SERVER – Msg 7399, Level 16 – The OLE DB Provider “ADsDSOObject” for Linked Server “AD” Reported an Error

SQL SERVER - Msg 7399, Level 16 - The OLE DB Provider "ADsDSOObject" for Linked Server "AD" Reported an Error errorspy One of my blog reader told that they are having trouble with linked server to AD. SQL SERVER – How to Query Active Directory Data Using ADSI / LDAP Linked Server. Let us learn about error related to OLE DB Provider.

They said that they are trying to use a linked server to run an LDAP query on an AD in another domain. Below us the query which works fine in one domain

SELECT * FROM OPENQUERY (ADSI, 'SELECT * FROM ''LDAP://OU=Power,OU=Non Fileshare,OU=Domain Local Groups,OU=CA Groups,DC= CA,DC=ad '' ') AD

They told that they can successfully look up items in the first domain, but when you run the same LDAP query for the second domain in a separate forest they get the following error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “ADsDSOObject” for linked server “ADSI” reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query “SELECT *
FROM ‘LDAP://OU=Power,OU=Non Fileshare,OU=Domain Local Groups,OU=CA Groups,DC= CA,DC=ad’
” against OLE DB provider “ADsDSOObject” for linked server “ADSI”.

WORKAROUND/SOLUTION

While looking at their query and my blog, I found that the sql server error msg 7321 Server was missing the domain controller name. We modified the query according to below syntax

“LDAP://DomainControllerName.com/OU=OrganizationalUnit,DC=DOMAIN,DC=NAME”

As soon as we kept the domain controller name, it worked.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Linked Server Error Between SQL Server and Oracle

Jevon

I have used Linked Servers to hook up to an Oracle 9i database in SQL
Server 2000. I can see all the Tables and Views when I look in the
linked server section. I can also run the following SQL statement :

Select *
from Orcldb.SYSTEM.CrossTabSaveSites

and get a return result. The problem I realize is that some of the
tables are going to be quite larger and I can't wait for SQL Server to
download all the data and parse it itself, so I tried to use a
passthrough query like so :

SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROM
SYSTEM.CrossTabSaveSites')

Unfortunately this gives me the error message :

Server: Msg 7321, sql server error msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00942: table or error 09 nikon d70 does not
exist
]

There is also the issue of speed. It takes about 15 seconds to come
back to me to tell me that the table/view doesn't exist, but sometimes
it comes back immediately. Is this something with the user
permissions in Oracle? (Don't really know much about Oracle) It just
sql server error msg 7321 like a very long time to wait just to let me know that the item
doesn't exist.

Any help would windows 7 error log files greatly appreciated,

-Jevon Thurlow

Jul 20 '05

Simon Hayes


"Jevon" <je**********@hotmail.com> wrote in message
news:34**************************@posting.google.c om.

I have used Linked Servers to hook up to an Oracle 9i database in SQL
Server 2000. I can see all the Tables and Views when I look in the
linked server section. I can also run the following SQL statement :

Select *
from Orcldb.SYSTEM.CrossTabSaveSites

and get a return result. The problem I realize is that some of the
tables are going to be quite larger and I can't wait for SQL Server to
download all the data and parse it itself, so I tried to use a
passthrough query like so :

SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROM
SYSTEM.CrossTabSaveSites')

Unfortunately this gives me the error message :

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00942: table or view does not
exist
]

There is also the issue of speed. It takes about 15 seconds to come
back to me to tell me that the table/view doesn't exist, but sometimes
it comes back immediately. Is this something with the user
permissions in Oracle? (Don't really know much about Oracle) It just
seems like a very long time to wait just to let me know that the item
doesn't exist.

Any help would be greatly appreciated,

-Jevon Thurlow


I can't tell you anything about the performance issue, but the ORA-00942
error is probably because Oracle is case-sensitive, and object names are
usually in upper case:

http://support.microsoft.com/default.b;EN-US;240340
http://support.microsoft.com/default.b;EN-US;280106

Simon

Jul 20 '05 error 79 04 power off sql server error msg 7321

This discussion thread is closed

Start new discussion

Replies have been disabled for this discussion.

network error 5120 nokia these next.

Hello,

I am trying to use openquery with two ou. However, I am getting following error.

Below is my script:

 select displayName,DEPARTMENT,mail,physicalDeliveryOfficeName,telephoneNumber
             from OpenQuery ( ad,  
  'SELECT
  employeeid, sAMAccountName,instanceType,objectCategory, mail
  ,displayName, sql server error msg 7321, DistinguishedName
  ,department,isDeleted,name
  ,logoncount
  ,createTimeStamp,modifyTimeStamp
  ,lastLogonTimestamp, lastlogon
  ,UserAccountControl
  ,physicalDeliveryOfficeName
  ,telephoneNumber
  FROM ''LDAP://OU=Faculty,OU=Staff,DC=domain,DC=com''  


  WHERE objectClass = ''User''  
  ')

Here is the error message that I am getting:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT
  employeeid, sAMAccountName,instanceType,objectCategory, mail
  ,displayName, DistinguishedName
  ,department,isDeleted,name
  ,logoncount
  ,createTimeStamp,modifyTimeStamp
  ,lastLogonTimestamp, lastlogon
  ,UserAccountControl
  ,physicalDeliveryOfficeName
  ,telephoneNumber
  FROM 'LDAP://OU=Faculty,OU=Staff,DC=domain,DC=com'  


  WHERE objectClass = 'User'  
  " for execution against OLE DB provider "ADSDSOObject" for linked server "ad".

Please help me out to solve this error.

Thanks in advance

  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    I have not been able to use 'OU=xxxx' in any of my ADSI queries.

    I tried your ADSI query on my system and it works if I use the following:

    Text

    FROM ''LDAP://DC=domain,DC=com''  

    Make sure that 'domain' and 'com' are actually the proper domain prefix and suffix for your AD system.
    For example if your AD system domain is mycompany.org, then you should use 'mycompany' in place of 'domain' and 'org' in place of 'com'.

    Text

    FROM ''LDAP://DC=mycompany,DC=org''   syntax error unexpected t_variable expecting t_function note I am using two sets of single quotes here within the string (since we are embedding this within another string); do NOT use double quotes.

    You also need asus error beep codes have server 'ad' setup as a linked server with the following linked server properties (or similar):
    Provider: OLE DB Provider for Microsoft Directory Services
    Product Name: Active Directory Services 2.5

    There is also a limit on the number of rows that can be returned by this type of query.  I have found it to be about 1000 rows or so.
    So if your AD system has more than 1000 users in it, the query will likely timeout and fail.
    If you keep the number of returned sql server error msg 7321 below 500, you should not have a problem.

    Something like the following:

    Text

    FROM ''LDAP://DC=mycompany,DC=org'' where objectClass = ''User'' and name<''a'' FROM ''LDAP://DC=mycompany,DC=org'' where objectClass = ''User'' and name>=''a'' and name<''c''
    Was this post helpful?thumb_upthumb_down
  • You can also add a TOP() clause to limit the number of rows returned:

    SQL

    SELECTTOP(100)<columnlist>FROM<table>
    Was this post helpful?thumb_upthumb_down
  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    To get around the row limit problem, I simply combined them sql server error msg 7321 a VIEW using UNION ALL, where each query would get a section of pcad error 4105 sorted alphabet.

    First one has <'a', sql server error msg 7321, second has >='a' and <'c', third has >='c' and <'e', and so on.

    Takes a minute or two to do the whole view query.
    For efficiency, I just do this a few times a day and insert the results into an actual SQL table for subsequent fast retrieval.
    Mine has about 4000 users in it, not too big.

    Was this post helpful?thumb_upthumb_down
  • I have around 250 emaployees and adjunct faculty

  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    RV16 wrote:

    I have around 250 emaployees and adjunct faculty

    Ok.  Did you try any of my suggestions to get your query to work?

  • I tried but getting same error:

    select displayName,DEPARTMENT,mail,physicalDeliveryOfficeName,telephoneNumber

                 from OpenQuery ( adsi,  

      'SELECT

      employeeid, sAMAccountName,instanceType,objectCategory, mail

      ,displayName, DistinguishedName

      ,department,isDeleted,name

      ,logoncount

      ,createTimeStamp,modifyTimeStamp

      ,lastLogonTimestamp, lastlogon

      ,UserAccountControl

      ,physicalDeliveryOfficeName

      ,telephoneNumber

      FROM ''LDAP://OU=Employees,DC=domain,DC=com''  

      WHERE objectClass = ''User''

      UNION

       SELECT

      employeeid, sql server error msg 7321, sAMAccountName,instanceType,objectCategory, mail

      ,displayName, DistinguishedName

      ,department,isDeleted,name

      ,logoncount

      ,createTimeStamp,modifyTimeStamp

      ,lastLogonTimestamp, lastlogon

      ,UserAccountControl

      ,physicalDeliveryOfficeName

      ,telephoneNumber

      FROM ''LDAP://OU=Faculty,DC=domain,DC=com''  

      ')


  • I also try through view but still getting the same error:

    CREATE VIEW [vADUsers1] AS
    SELECT displayName,DEPARTMENT,title,mail,physicalDeliveryOfficeName,telephoneNumber
    FROM OPENQUERY(adsi,
      'SELECT
      employeeid, sAMAccountName,instanceType,objectCategory, mail
      ,displayName, sql server error msg 7321, DistinguishedName
      ,department,isDeleted,name
      ,logoncount
      ,createTimeStamp,modifyTimeStamp
      ,lastLogonTimestamp, lastlogon
      ,UserAccountControl
      ,physicalDeliveryOfficeName
      ,telephoneNumber
      ,title
      FROM ''LDAP://OU=Employees,DC=domain,DC=edu''
      WHERE objectClass = ''User''
    UNION ALL

    SELECT
      employeeid, sAMAccountName,instanceType,objectCategory, mail
      ,displayName, DistinguishedName
      ,department,isDeleted,name
      ,logoncount
      ,createTimeStamp,modifyTimeStamp
      ,lastLogonTimestamp, lastlogon
      ,UserAccountControl
      ,physicalDeliveryOfficeName
      ,telephoneNumber
      ,title
      FROM ''LDAP://OU=Faculty,DC=domain,DC=edu''
      WHERE objectClass = ''User''

      ');


  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account sql server error msg 7321 to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    1) Try removing the OU sections.

    ''LDAP://DC=domain,DC=com''  

    2) Is your domain really 'domain' and suffix 'com' ?  You are changing these to your actual domain name and suffix, correct?

    3) Your linked server name is 'adsi', correct?  Previously you had 'ad'.  This linked server name has to match whatever is entered in error - 1270040 SQL Server Linked Server list for your ADSI server.

  • But I only want employee and adjunct faculty information. 

    When I remove OU,I am getting following error:

    Msg 7330, Level 16, State 2, Line 1
    Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "adsi".

  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    You should get a working query first before trying to change it otherwise you will never get anywhere.
    You can modify your query results by using 'where' conditions instead of changing the LDAP string.

    So let's not worry about the employee and adjunct stuff now, and try to get the query working.
    I can replicate the same error message you get (Msg 7330) if the number of returned rows from ADSI is over 900 (SQL Server 2008).

    Please put in one more condition where we restrict the names to those less than 'b' :

    Text

    FROM ''LDAP://DC=domain,DC=edu'' where objectClass = ''User'' and name<''b''
    Was this post helpful?thumb_upthumb_down
  • Thanks Alec6638. I appreciated for your help.

     I have used above script and used as a view. And now I am trying to join that view with another table in other script.

    I am getting result. However, the execution is very slow. Any ideas to solve this?

    create view ViewActiveDir

     As

     select *

                 from OpenQuery ( adsi,  

      'SELECT

      employeeid, sAMAccountName,instanceType,objectCategory, mail

      ,displayName, DistinguishedName

      ,department,isDeleted,name

      ,logoncount

      ,createTimeStamp,modifyTimeStamp

      ,lastLogonTimestamp, lastlogon

      ,UserAccountControl

      ,title

      ,telephoneNumber

      ,physicalDeliveryOfficeName

      FROM sql server error msg 7321  

      WHERE objectClass = ''User''  

      ')

      union all

      select *

                 from OpenQuery ( adsi,  

      'SELECT

      employeeid, sAMAccountName,instanceType,objectCategory, mail

      ,displayName, DistinguishedName

      ,department,isDeleted,name

      ,logoncount

      ,createTimeStamp,modifyTimeStamp

      ,lastLogonTimestamp, lastlogon

      ,UserAccountControl

      ,title

      ,telephoneNumber

      ,physicalDeliveryOfficeName

      FROM ''LDAP://OU=Adjunct Faculty,DC=domain,DC=edu''  

      WHERE objectClass = ''User''  

      ')

    select

    distinct ('<img src=http://my.domain.com/JICSPhotos/'+nm.LAST_NAME+','+nm.FIRST_NAME+'-'+convert(varchar(8),nm.ID_NUM. width=100 hspace="10">') as 'PICTURE ID',

    nm.FIRST_NAME,nm.LAST_NAME,nm.EMAIL_ADDRESS,ad.department,ad.telephoneNumber,ad.physicalDeliveryOfficeName,

    nm.TITLE

           from NAME_MASTER nm

    Join viewActiveDir ad on nm.ID_NUM=ad.employeeid

    join TW_GRP_MEMBERSHIP gm on gm.ID_NUM=nm.ID_NUM

    join EMPL_MAST em on em.ID_NUM=nm.ID_NUM

    join IND_POS_HIST iph on iph.ID_NUM=em.ID_NUM

    and em.ACT_INACT_STS='A'

    and nm.id_num<>'4300458'

    and (em.GRP_CDE not in ('WKST','WKSTF','WKSTS'))

    and (gm.GROUP_ID like 'staff' or gm.GROUP_ID like'faculty')

    Thank you


  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you sql server error msg 7321 a professional, sql server error msg 7321.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    Queries from Active Directory via LDAP ADSI will always be slower than SQL queries from native SQL tables.

    How often do you need updated data from Active Directory ?  Every day?

    You can try error_reportinge_all ini_setdisplay_errors, on the following on a schedule that meets your needs (hourly, daily, other):

    SQL

    IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.TABLESWHERETABLE_NAME='ViewActiveDirTable')DROPTABLEViewActiveDirTable;SELECT*INTOViewActiveDirTableFROMViewActiveDir;

    And in your last query where you do the JOINS, replace ViewActiveDir with ViewActiveDirTable.

  • Thanks for the solution. 

    Could I use SSIS in this case? How?

    Please give me some example.

    Thank you

    Ripa

  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    checkBest Answer

    No need for SSIS here, just schedule the Sql server error msg 7321 statements as a job within SQL Server Agent.


    Was this post helpful?thumb_upthumb_down
  • Thank you very much. It solved my whole issue. I appreciated.

  • Alec6638
    Alec6638This person is a Verified Professional

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 127 Best Answers
    • thumb_up 307 Helpful Votes

    RV16 wrote:

    Thank you very much. It solved my whole issue. I appreciated.

    It is always good to hear that a solution has been reached!

    Was this post helpful?thumb_upthumb_down
  • Spark! Pro series - 1st September 2022

    Spark! Pro series - 1st September 2022

    Spiceworks Originals

    Today in History: Wreck of the Titanic Found Seventy-three years after it sank to the North Atlantic ocean floor, a joint U.S.-French expedition locates the wreck of sql server error msg 7321 RMS Titanic. The sunken liner was about 400 miles east of Newfoundland in the .

  • SpiceQuest September (2022) -- Of Bacon and Books. and Bacon

    SpiceQuest September (2022) -- Of Bacon and Books. and Bacon

    Spiceworks Originals

    Welcome to another SpiceQuest! In this series, we call out current holidays and give you the chance to earn the monthly SpiceQuest badge! (Each task can be done at any time. They don't have to be completed on a certain holiday.) This month w.

  • Art Imitates Life: DALL·E 2 AI Generates Art Featuring IT Professionals

    Art Imitates Life: DALL·E 2 AI Generates Art Featuring IT Professionals

    Water Cooler

    In my last post about computer generated art, sql server error msg 7321, I tested artificial intelligence's ability to create orange T-Rex art in the style of many famous artists, using OpenAI's DALL·E 2 AI.Quick recap of how DALL·E 2 works. Users can request images of anything by .

  • Snap! Azure outage, malware in JWST images, floppy disks, <b>sql server error msg 7321</b>, Voyager 1, & more

    Snap! Azure outage, malware in JWST images, floppy disks, Voyager 1, & more

    Spiceworks Originals

    Your daily dose of tech news, in brief. While sql server error msg 7321 month may be ending, we're halfway through the week. Let's jump into today's Snap! and see some of the things going on in the world. You need to hear this. Microsoft Azure outage knocks Ubuntu.

  • Monitor our Weblogs

    Monitor our Weblogs

    Security

    Hi,I would like to ask for your recommendations on how I can capture the websites our end users access during office hours.our current firewall doesnt support it as it only logs HTTP connections and we all know that almost all of the websites now are usin.

watch the video

Résoudre les problèmes de connexion dans SQL Server