I have an XML file that I receive from a vendor and would like to report
from this data every morning. Can anyone send me a link where I can do
this? I do not have a schema at this time but I think I can create one if
needed.
Thanks
Scottsee the thread
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/d8d798424508e0e8/c4ceb1e126528133?lnk=raot#c4ceb1e126528133
I think your solution will also depend on
[1] how do you receive the XML file from the vendor?
[2] how do you like the report every morning? (schedule,
auto-delivery?)
Scott M wrote:
> I have an XML file that I receive from a vendor and would like to report
> from this data every morning. Can anyone send me a link where I can do
> this? I do not have a schema at this time but I think I can create one if
> needed.
> Thanks
> Scott|||Will this also work in RS 2000?
<siva.jasthi@.gmail.com> wrote in message
news:1134501012.316584.125040@.g44g2000cwa.googlegroups.com...
> see the thread
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/d8d798424508e0e8/c4ceb1e126528133?lnk=raot#c4ceb1e126528133
>
> I think your solution will also depend on
> [1] how do you receive the XML file from the vendor?
> [2] how do you like the report every morning? (schedule,
> auto-delivery?)
>
>
> Scott M wrote:
>> I have an XML file that I receive from a vendor and would like to report
>> from this data every morning. Can anyone send me a link where I can do
>> this? I do not have a schema at this time but I think I can create one
>> if
>> needed.
>> Thanks
>> Scott
>|||Hi Scott,
Unfortunately, it will not work in SQL Server 2000 Reporting Services.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Is there an online tutorial for how to create a data extension for that?
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:YUstdbIAGHA.832@.TK2MSFTNGXA02.phx.gbl...
> Hi Scott,
> Unfortunately, it will not work in SQL Server 2000 Reporting Services.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Scott,
Thanks for the respond.
I believe the following articles might help
Build an XML Data Extension for SQL Server Reporting Services
http://www.devx.com/dbzone/Article/21214
Tutorial: Using XML Data in a Report
http://msdn2.microsoft.com/en-us/library/ms345334(en-US,SQL.90).aspx
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I am not understanding from the MSDN article in this post where do you put
the XML file to connect to for your database.
"Michael Cheng [MSFT]" wrote:
> Hi Scott,
> Thanks for the respond.
> I believe the following articles might help
> Build an XML Data Extension for SQL Server Reporting Services
> http://www.devx.com/dbzone/Article/21214
> Tutorial: Using XML Data in a Report
> http://msdn2.microsoft.com/en-us/library/ms345334(en-US,SQL.90).aspx
> Hope this helps.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||That first article looks like it might be what I need. I'll let you guys
know.
Thanks VERY much!!
Scott
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:ktNF%23pVAGHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Scott,
> Thanks for the respond.
> I believe the following articles might help
> Build an XML Data Extension for SQL Server Reporting Services
> http://www.devx.com/dbzone/Article/21214
> Tutorial: Using XML Data in a Report
> http://msdn2.microsoft.com/en-us/library/ms345334(en-US,SQL.90).aspx
> Hope this helps.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Scott,
You are welcome :) If you have any questions or concerns next time, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label datasource. Show all posts
Showing posts with label datasource. Show all posts
Friday, March 30, 2012
Monday, March 12, 2012
Query to get the all the users from ADS datasource
Please help me to get the all the user from ADS, i searched a lot and found we can get that using linked server,
i ran the following query to add the linked server,
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
The query ran succesfully and the 'ADSI' has been added as linked server
I got a query from net to get the users from ADS
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')
I am not able to understand the query above and what i need to give to get my ADS users
please help me.
i tried from my side after refering these links ... go through this...
http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
http://support.microsoft.com/kb/299410
http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx
Madhu
|||Madhu,
I am having the same problem as posted above, but despite trying your suggestions, it still doesnt work.
The code used:
Code Block
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'
SELECT * FROM OPENQUERY( ADSI,
'SELECT name, adsPath
FROM ''LDAP://DC=myCompany,DC=lan''
WHERE objectCategory = ''Person'' AND objectClass= ''user''')
PS: the domain is myCompany.lan
We use windows authentication mode. I dont belong to the administrators' group (which is systems administrator's stuff here).
May it be something related to the linked server's security? I've tried "be made using the login's current security context" and "be made without a security context" (we never know 
Or would it be related to AD reading permissions?
|||Well, a clue:
Digging around, I've found this script at http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx:
Code Block
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT Name FROM 'LDAP://dc=myCompany,dc=lan' " & _
"WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop
It works like charm. Whatever it may be, is not related to permissions. An interesting thing: in Brendan Tompkins' blog http://adsdsoobject.codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx, he quotes this link: http://www.dbforums.com/archive/index.php/t-958399.html, which mentions certain windows registry keys I havent found in both my server and desktops: there is no entry called "provider", under the mentioned path. Is there anything missing here?
Thanks in advance!
|||More news:
Indeed, the query runs smoothly... using SSMS on the server machine. But not in any workstation.
As I said, I can run that very vb script quoted above from my workstation, but not the query in SSMS. Why is it so?
No clues?!
|||Do you get any error when you are not expecting correct results?
If it doesn't work with server name you might try with IP Address instead.
|||Thanks for your reply, Satya.
I didnt understand your first question...
Actually, the query below doesnt work when I try to run from my workstation (that same "An error occurred while preparing the query..." error). I've tried using IP as you suggested:
select * from openquery
(ADSI,'SELECT name
FROM ''LDAP://172.23.0.21''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
Something weird: it doesnt work while running from SSMS right on the server, either! I get this error:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".
Query to get the all the users from ADS datasource
Please help me to get the all the user from ADS, i searched a lot and found we can get that using linked server,
i ran the following query to add the linked server,
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
The query ran succesfully and the 'ADSI' has been added as linked server
I got a query from net to get the users from ADS
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')
I am not able to understand the query above and what i need to give to get my ADS users
please help me.
i tried from my side after refering these links ... go through this...
http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
http://support.microsoft.com/kb/299410
http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx
Madhu
|||Madhu,
I am having the same problem as posted above, but despite trying your suggestions, it still doesnt work.
The code used:
Code Block
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'
SELECT * FROM OPENQUERY( ADSI,
'SELECT name, adsPath
FROM ''LDAP://DC=myCompany,DC=lan''
WHERE objectCategory = ''Person'' AND objectClass= ''user''')
PS: the domain is myCompany.lan
We use windows authentication mode. I dont belong to the administrators' group (which is systems administrator's stuff here).
May it be something related to the linked server's security? I've tried "be made using the login's current security context" and "be made without a security context" (we never know
Or would it be related to AD reading permissions?
|||Well, a clue:
Digging around, I've found this script at http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx:
Code Block
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT Name FROM 'LDAP://dc=myCompany,dc=lan' " & _
"WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop
It works like charm. Whatever it may be, is not related to permissions. An interesting thing: in Brendan Tompkins' blog http://adsdsoobject.codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx, he quotes this link: http://www.dbforums.com/archive/index.php/t-958399.html, which mentions certain windows registry keys I havent found in both my server and desktops: there is no entry called "provider", under the mentioned path. Is there anything missing here?
Thanks in advance!
|||More news:
Indeed, the query runs smoothly... using SSMS on the server machine. But not in any workstation.
As I said, I can run that very vb script quoted above from my workstation, but not the query in SSMS. Why is it so?
No clues?!
|||Do you get any error when you are not expecting correct results?
If it doesn't work with server name you might try with IP Address instead.
|||Thanks for your reply, Satya.
I didnt understand your first question...
Actually, the query below doesnt work when I try to run from my workstation (that same "An error occurred while preparing the query..." error). I've tried using IP as you suggested:
select * from openquery
(ADSI,'SELECT name
FROM ''LDAP://172.23.0.21''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
Something weird: it doesnt work while running from SSMS right on the server, either! I get this error:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".
Subscribe to:
Posts (Atom)