Hello Guys,
I am having hard time printing "No data to print" when there are no records returned by the report. Rightnow report prints without data but with all column headings. I would appreciate hearing from you soon.
Thanks
Dilemma
:wave:Hi,
In all headers suppress events write the formula as below
if isnull({DatabaseFeild}) =True then True
Place a new header in the header showing the value "No data to Print"
Now in the suppress event of this header write the formula as
if isnull({DatabaseFeild}) =false then True
Madhivanan|||Hello Madhi,
Thanks for the reply. I appreciate your help.
I am just wondering how do u write code header supress event. Do u have to write some code in your eg. VB or PB's event or in crystal report designer itself ? Unfortunately I am using PowreBuilder (pb) for the development.
Thanks
Dilemma|||Hi,
You can use this code in the crystal report itself.
Right click on header and select format section. A pop up menu will appear. There will be a check box labelled as Suppress. In parallel to that a button labelled X-2 is there. Click that button and write the coding I have mentioned. You have to this process for each header. To display "No data to Print", just add a text object and write it as "No data to Print" and in the suppress option write the second formula.
Madhivanan
Showing posts with label returned. Show all posts
Showing posts with label returned. Show all posts
Monday, March 26, 2012
no data returned when using a stylesheet
Executing http queries works fine. But when I try to use a xls stylesheet in the query, I get headings but no data. It must be something in the stylesheet but I cant identify the problem. Any help would be appreciated.
This code is based on examples from P151 in John Griffins book XML and SQL Server 2000 using the Northwind database.
IIS and sql server are running on the same machine (bne20dbm07). Profiler shows that the data is being retrieved.
Using Windows server 2000, SQL 2000 sp3a, all hotfixes applied, MSXML 3.0 sp4
Queries are being run for a Windows XP machine via IE 6 sp1 plus hotfixes.
httpQuery
http://bne20dbm07/Nwind?sql=select+T...AUTO&root=ROOT
Results
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT><Orders OrderID="10248" EmployeeId="5" Shipname="Vins et alcools Chevalier" /><Orders OrderID="10249" EmployeeId="6" Shipname="Toms Spezialit?ten" /><Orders OrderID="10250" EmployeeId="4" Shipname="Hanari Carnes" /><Orders OrderID="10251" Employe
eId="3" Shipname="Victuailles en stock" /></ROOT>
stylesheet query
order.xsl
<?xml version='1.0'?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform/1.0"><xsl:output media-type="text/html"/><xsl:template match="/"><HTML><BODY><TABLE width='400' border='1'><TR><TD><B>Order ID</B></TD><TD><B>Ship Name</B></TD></TR><xsl:
apply-templates/></TABLE></BODY></HTML></xsl:template><xsl:template match="Orders"><TR><TD><xsl:value-of select="@.OrderID"/></TD><TD><xsl:value-of select="@.Shipname"/></TD></TR></xsl:template></xsl:stylesheet>
http://bne20dbm07/Nwind?sql=select+T...xsl&root=ROOT
Results (Only the heading text in the table is displayed)
Order ID Ship Name
The only problem I could find is with the stylesheet namespace declaration -
I had to change it to <xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> (i.e. with a
version attribute)
Other than that it worked OK for me from both the local PC and a remote
client.
You don't list SQLXML 3.0 sp2 as being installed - it *should* still work
without it, but I haven't tested it.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"David R" <david.roseneder@.dcs.qld.gov.au> wrote in message
news:0C44F895-7C60-4842-B601-D09197A70727@.microsoft.com...
Executing http queries works fine. But when I try to use a xls stylesheet in
the query, I get headings but no data. It must be something in the
stylesheet but I cant identify the problem. Any help would be appreciated.
This code is based on examples from P151 in John Griffins book XML and SQL
Server 2000 using the Northwind database.
IIS and sql server are running on the same machine (bne20dbm07). Profiler
shows that the data is being retrieved.
Using Windows server 2000, SQL 2000 sp3a, all hotfixes applied, MSXML 3.0
sp4
Queries are being run for a Windows XP machine via IE 6 sp1 plus hotfixes.
httpQuery
http://bne20dbm07/Nwind?sql=select+T...AUTO&root=ROOT
Results
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT><Orders OrderID="10248" EmployeeId="5" Shipname="Vins et alcools
Chevalier" /><Orders OrderID="10249" EmployeeId="6" Shipname="Toms
Spezialitten" /><Orders OrderID="10250" EmployeeId="4" Shipname="Hanari
Carnes" /><Orders OrderID="10251" EmployeeId="3" Shipname="Victuailles en
stock" /></ROOT>
stylesheet query
order.xsl
<?xml version='1.0'?><xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform/1.0"><xsl:output
media-type="text/html"/><xsl:template match="/"><HTML><BODY><TABLE
width='400' border='1'><TR><TD><B>Order ID</B></TD><TD><B>Ship
Name</B></TD></TR><xsl:apply-templates/></TABLE></BODY></HTML></xsl:template
><xsl:template match="Orders"><TR><TD><xsl:value-of
select="@.OrderID"/></TD><TD><xsl:value-of
select="@.Shipname"/></TD></TR></xsl:template></xsl:stylesheet>
http://bne20dbm07/Nwind?sql=select+T...xsl&root=ROOT
Results (Only the heading text in the table is displayed)
Order ID Ship Name
|||Graeme
Thanks, adding the namespace corrected the problem. Data is now returned.
Thanks again
David Roseneder
This code is based on examples from P151 in John Griffins book XML and SQL Server 2000 using the Northwind database.
IIS and sql server are running on the same machine (bne20dbm07). Profiler shows that the data is being retrieved.
Using Windows server 2000, SQL 2000 sp3a, all hotfixes applied, MSXML 3.0 sp4
Queries are being run for a Windows XP machine via IE 6 sp1 plus hotfixes.
httpQuery
http://bne20dbm07/Nwind?sql=select+T...AUTO&root=ROOT
Results
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT><Orders OrderID="10248" EmployeeId="5" Shipname="Vins et alcools Chevalier" /><Orders OrderID="10249" EmployeeId="6" Shipname="Toms Spezialit?ten" /><Orders OrderID="10250" EmployeeId="4" Shipname="Hanari Carnes" /><Orders OrderID="10251" Employe
eId="3" Shipname="Victuailles en stock" /></ROOT>
stylesheet query
order.xsl
<?xml version='1.0'?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform/1.0"><xsl:output media-type="text/html"/><xsl:template match="/"><HTML><BODY><TABLE width='400' border='1'><TR><TD><B>Order ID</B></TD><TD><B>Ship Name</B></TD></TR><xsl:
apply-templates/></TABLE></BODY></HTML></xsl:template><xsl:template match="Orders"><TR><TD><xsl:value-of select="@.OrderID"/></TD><TD><xsl:value-of select="@.Shipname"/></TD></TR></xsl:template></xsl:stylesheet>
http://bne20dbm07/Nwind?sql=select+T...xsl&root=ROOT
Results (Only the heading text in the table is displayed)
Order ID Ship Name
The only problem I could find is with the stylesheet namespace declaration -
I had to change it to <xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> (i.e. with a
version attribute)
Other than that it worked OK for me from both the local PC and a remote
client.
You don't list SQLXML 3.0 sp2 as being installed - it *should* still work
without it, but I haven't tested it.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"David R" <david.roseneder@.dcs.qld.gov.au> wrote in message
news:0C44F895-7C60-4842-B601-D09197A70727@.microsoft.com...
Executing http queries works fine. But when I try to use a xls stylesheet in
the query, I get headings but no data. It must be something in the
stylesheet but I cant identify the problem. Any help would be appreciated.
This code is based on examples from P151 in John Griffins book XML and SQL
Server 2000 using the Northwind database.
IIS and sql server are running on the same machine (bne20dbm07). Profiler
shows that the data is being retrieved.
Using Windows server 2000, SQL 2000 sp3a, all hotfixes applied, MSXML 3.0
sp4
Queries are being run for a Windows XP machine via IE 6 sp1 plus hotfixes.
httpQuery
http://bne20dbm07/Nwind?sql=select+T...AUTO&root=ROOT
Results
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT><Orders OrderID="10248" EmployeeId="5" Shipname="Vins et alcools
Chevalier" /><Orders OrderID="10249" EmployeeId="6" Shipname="Toms
Spezialitten" /><Orders OrderID="10250" EmployeeId="4" Shipname="Hanari
Carnes" /><Orders OrderID="10251" EmployeeId="3" Shipname="Victuailles en
stock" /></ROOT>
stylesheet query
order.xsl
<?xml version='1.0'?><xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform/1.0"><xsl:output
media-type="text/html"/><xsl:template match="/"><HTML><BODY><TABLE
width='400' border='1'><TR><TD><B>Order ID</B></TD><TD><B>Ship
Name</B></TD></TR><xsl:apply-templates/></TABLE></BODY></HTML></xsl:template
><xsl:template match="Orders"><TR><TD><xsl:value-of
select="@.OrderID"/></TD><TD><xsl:value-of
select="@.Shipname"/></TD></TR></xsl:template></xsl:stylesheet>
http://bne20dbm07/Nwind?sql=select+T...xsl&root=ROOT
Results (Only the heading text in the table is displayed)
Order ID Ship Name
|||Graeme
Thanks, adding the namespace corrected the problem. Data is now returned.
Thanks again
David Roseneder
No Data returned from OPENQUERY to Active Directory
I am trying to query our active directory for user information, but even
though I get the query to work I am getting no data back. I have a linked
server to the active directory from SQL Server 2000 and I am running the
query in the Query Analyzer.
My query is as follows:
select * from openquery(adsi, 'select name, homephone from
''ldap://DC=mydomain,DC=com'' where objectclass = ''User'' ')
This query will complete without error, but no data is returned. I have
seen one other question of this nature here, but I saw no final resolution.
What is wrong?
Alan
Message posted via http://www.sqlmonster.com
Bruce,
thanks for the response, but I tried what you suggested and still the query
returned no data. It completed without error just no data.
Thanks,
Alan
Message posted via http://www.sqlmonster.com
|||Alan, not sure why youre getting no data back then.
You're sure of the domain name? there's no special
security on your AD to prevent you from accessing? The
linked sever is setup correctly? like so..?
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
go
Are you sure the data sits in the User ObjectClass?
Might be somewhere else, check it.
There was a max 1000 row limit by default and we had to
have that kicked up to get more then 1000 rows returned.
These links talk about that...
http://support.novell.com/cgi-
bin/search/searchtid.cgi?/10081596.htm
http://www.ldapadministrator.com/for...topic.php?t=14
>--Original Message--
>Bruce,
>thanks for the response, but I tried what you suggested
and still the query
>returned no data. It completed without error just no
data.
>Thanks,
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>
|||Bruce,
I ran an export of the data in the user objects using ldifde and got back
exactly what I expected, but the same query in SQL Server Query Analyzer
comes back empty.
I guess it could be something with the link, but it was set up according to
the instructions I found on the web. The link was created in Enterprise
manager, but using the same settings that you specified in the
sp_addlinkedserver statement. It could also be a user security issue, but
I have tried logining in as the admin, SQL Sever admin and myself with no
data back.
Still playing around with it.
Alan
Message posted via http://www.sqlmonster.com
|||Finally, thanks Bruce. I was reading your posting and I was finally able to
pull data.
I'm trying to pull just users that are a member of a certain group, how can
I do that?
This is what I have but not working; however, I was able to used your query
just to get a start and that was a big help.
SELECT *
FROM OPENQUERY( ADSI,
'SELECT cn
FROM ''LDAP://internal/OU=Account
Executives,DC=internal,DC=homequest,DC=com''
WHERE objectClass = ''users''')
GO
"Bruce de Freitas" wrote:
> Alan, not sure if it matters, but I usually do the
> ADIS/LDAP calls more like this format... Check the
> Linked Server also, for correct setup and security. Bruce
>
> SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
> ((objectClass=User));distinguishedname,homePhone')
> where distinguishedname like '%a%'
>
> information, but even
> back. I have a linked
> I am running the
> from
> = ''User'' ')
> returned. I have
> no final resolution.
>
|||Sonya, this query would list all the GROUPs.
SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
(&(objectCategory=GROUP)); distinguishedname,CN')
You'd then have to join all the Users to what groups they are in, if
that is what youre trying to do... Bruce
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Thank you, after playing with it for awhile I was able to get it to work.
Do you know if there is a way to extract users' contact folder from the
server-level?
"Bruce de Freitas" wrote:
> Sonya, this query would list all the GROUPs.
> SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
> (&(objectCategory=GROUP)); distinguishedname,CN')
>
> You'd then have to join all the Users to what groups they are in, if
> that is what youre trying to do... Bruce
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
though I get the query to work I am getting no data back. I have a linked
server to the active directory from SQL Server 2000 and I am running the
query in the Query Analyzer.
My query is as follows:
select * from openquery(adsi, 'select name, homephone from
''ldap://DC=mydomain,DC=com'' where objectclass = ''User'' ')
This query will complete without error, but no data is returned. I have
seen one other question of this nature here, but I saw no final resolution.
What is wrong?
Alan
Message posted via http://www.sqlmonster.com
Bruce,
thanks for the response, but I tried what you suggested and still the query
returned no data. It completed without error just no data.
Thanks,
Alan
Message posted via http://www.sqlmonster.com
|||Alan, not sure why youre getting no data back then.
You're sure of the domain name? there's no special
security on your AD to prevent you from accessing? The
linked sever is setup correctly? like so..?
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
go
Are you sure the data sits in the User ObjectClass?
Might be somewhere else, check it.
There was a max 1000 row limit by default and we had to
have that kicked up to get more then 1000 rows returned.
These links talk about that...
http://support.novell.com/cgi-
bin/search/searchtid.cgi?/10081596.htm
http://www.ldapadministrator.com/for...topic.php?t=14
>--Original Message--
>Bruce,
>thanks for the response, but I tried what you suggested
and still the query
>returned no data. It completed without error just no
data.
>Thanks,
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>
|||Bruce,
I ran an export of the data in the user objects using ldifde and got back
exactly what I expected, but the same query in SQL Server Query Analyzer
comes back empty.
I guess it could be something with the link, but it was set up according to
the instructions I found on the web. The link was created in Enterprise
manager, but using the same settings that you specified in the
sp_addlinkedserver statement. It could also be a user security issue, but
I have tried logining in as the admin, SQL Sever admin and myself with no
data back.
Still playing around with it.
Alan
Message posted via http://www.sqlmonster.com
|||Finally, thanks Bruce. I was reading your posting and I was finally able to
pull data.
I'm trying to pull just users that are a member of a certain group, how can
I do that?
This is what I have but not working; however, I was able to used your query
just to get a start and that was a big help.
SELECT *
FROM OPENQUERY( ADSI,
'SELECT cn
FROM ''LDAP://internal/OU=Account
Executives,DC=internal,DC=homequest,DC=com''
WHERE objectClass = ''users''')
GO
"Bruce de Freitas" wrote:
> Alan, not sure if it matters, but I usually do the
> ADIS/LDAP calls more like this format... Check the
> Linked Server also, for correct setup and security. Bruce
>
> SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
> ((objectClass=User));distinguishedname,homePhone')
> where distinguishedname like '%a%'
>
> information, but even
> back. I have a linked
> I am running the
> from
> = ''User'' ')
> returned. I have
> no final resolution.
>
|||Sonya, this query would list all the GROUPs.
SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
(&(objectCategory=GROUP)); distinguishedname,CN')
You'd then have to join all the Users to what groups they are in, if
that is what youre trying to do... Bruce
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Thank you, after playing with it for awhile I was able to get it to work.
Do you know if there is a way to extract users' contact folder from the
server-level?
"Bruce de Freitas" wrote:
> Sonya, this query would list all the GROUPs.
> SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
> (&(objectCategory=GROUP)); distinguishedname,CN')
>
> You'd then have to join all the Users to what groups they are in, if
> that is what youre trying to do... Bruce
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
No Data returned from OPENQUERY to Active Directory
I am trying to query our active directory for user information, but even
though I get the query to work I am getting no data back. I have a linked
server to the active directory from SQL Server 2000 and I am running the
query in the Query Analyzer.
My query is as follows:
select * from openquery(adsi, 'select name, homephone from
''ldap://DC=mydomain,DC=com'' where objectclass = ''User'' ')
This query will complete without error, but no data is returned. I have
seen one other question of this nature here, but I saw no final resolution.
What is wrong?
Alan
--
Message posted via http://www.sqlmonster.comAlan, not sure if it matters, but I usually do the
ADIS/LDAP calls more like this format... Check the
Linked Server also, for correct setup and security. Bruce
SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
((objectClass=User));distinguishedname,homePhone')
where distinguishedname like '%a%'
>--Original Message--
>I am trying to query our active directory for user
information, but even
>though I get the query to work I am getting no data
back. I have a linked
>server to the active directory from SQL Server 2000 and
I am running the
>query in the Query Analyzer.
>My query is as follows:
>select * from openquery(adsi, 'select name, homephone
from
>''ldap://DC=mydomain,DC=com'' where objectclass
= ''User'' ')
>This query will complete without error, but no data is
returned. I have
>seen one other question of this nature here, but I saw
no final resolution.
>What is wrong?
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>|||Bruce,
thanks for the response, but I tried what you suggested and still the query
returned no data. It completed without error just no data.
Thanks,
Alan
--
Message posted via http://www.sqlmonster.com|||Alan, not sure why youre getting no data back then.
You're sure of the domain name? there's no special
security on your AD to prevent you from accessing? The
linked sever is setup correctly? like so..'
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
go
Are you sure the data sits in the User ObjectClass?
Might be somewhere else, check it.
There was a max 1000 row limit by default and we had to
have that kicked up to get more then 1000 rows returned.
These links talk about that...
http://support.novell.com/cgi-
bin/search/searchtid.cgi?/10081596.htm
http://www.ldapadministrator.com/forum/viewtopic.php?t=14
>--Original Message--
>Bruce,
>thanks for the response, but I tried what you suggested
and still the query
>returned no data. It completed without error just no
data.
>Thanks,
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>|||Bruce,
I ran an export of the data in the user objects using ldifde and got back
exactly what I expected, but the same query in SQL Server Query Analyzer
comes back empty.
I guess it could be something with the link, but it was set up according to
the instructions I found on the web. The link was created in Enterprise
manager, but using the same settings that you specified in the
sp_addlinkedserver statement. It could also be a user security issue, but
I have tried logining in as the admin, SQL Sever admin and myself with no
data back.
Still playing around with it.
Alan
--
Message posted via http://www.sqlmonster.com|||Finally, thanks Bruce. I was reading your posting and I was finally able to
pull data.
I'm trying to pull just users that are a member of a certain group, how can
I do that?
This is what I have but not working; however, I was able to used your query
just to get a start and that was a big help.
SELECT *
FROM OPENQUERY( ADSI,
'SELECT cn
FROM ''LDAP://internal/OU=Account
Executives,DC=internal,DC=homequest,DC=com''
WHERE objectClass = ''users''')
GO
"Bruce de Freitas" wrote:
> Alan, not sure if it matters, but I usually do the
> ADIS/LDAP calls more like this format... Check the
> Linked Server also, for correct setup and security. Bruce
>
> SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
> ((objectClass=User));distinguishedname,homePhone')
> where distinguishedname like '%a%'
>
> >--Original Message--
> >I am trying to query our active directory for user
> information, but even
> >though I get the query to work I am getting no data
> back. I have a linked
> >server to the active directory from SQL Server 2000 and
> I am running the
> >query in the Query Analyzer.
> >
> >My query is as follows:
> >
> >select * from openquery(adsi, 'select name, homephone
> from
> >''ldap://DC=mydomain,DC=com'' where objectclass
> = ''User'' ')
> >
> >This query will complete without error, but no data is
> returned. I have
> >seen one other question of this nature here, but I saw
> no final resolution.
> >What is wrong?
> >
> >Alan
> >
> >--
> >Message posted via http://www.sqlmonster.com
> >.
> >
>|||Thank you, after playing with it for awhile I was able to get it to work.
Do you know if there is a way to extract users' contact folder from the
server-level?
"Bruce de Freitas" wrote:
> Sonya, this query would list all the GROUPs.
> SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
> (&(objectCategory=GROUP)); distinguishedname,CN')
>
> You'd then have to join all the Users to what groups they are in, if
> that is what youre trying to do... Bruce
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>sql
though I get the query to work I am getting no data back. I have a linked
server to the active directory from SQL Server 2000 and I am running the
query in the Query Analyzer.
My query is as follows:
select * from openquery(adsi, 'select name, homephone from
''ldap://DC=mydomain,DC=com'' where objectclass = ''User'' ')
This query will complete without error, but no data is returned. I have
seen one other question of this nature here, but I saw no final resolution.
What is wrong?
Alan
--
Message posted via http://www.sqlmonster.comAlan, not sure if it matters, but I usually do the
ADIS/LDAP calls more like this format... Check the
Linked Server also, for correct setup and security. Bruce
SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
((objectClass=User));distinguishedname,homePhone')
where distinguishedname like '%a%'
>--Original Message--
>I am trying to query our active directory for user
information, but even
>though I get the query to work I am getting no data
back. I have a linked
>server to the active directory from SQL Server 2000 and
I am running the
>query in the Query Analyzer.
>My query is as follows:
>select * from openquery(adsi, 'select name, homephone
from
>''ldap://DC=mydomain,DC=com'' where objectclass
= ''User'' ')
>This query will complete without error, but no data is
returned. I have
>seen one other question of this nature here, but I saw
no final resolution.
>What is wrong?
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>|||Bruce,
thanks for the response, but I tried what you suggested and still the query
returned no data. It completed without error just no data.
Thanks,
Alan
--
Message posted via http://www.sqlmonster.com|||Alan, not sure why youre getting no data back then.
You're sure of the domain name? there's no special
security on your AD to prevent you from accessing? The
linked sever is setup correctly? like so..'
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
go
Are you sure the data sits in the User ObjectClass?
Might be somewhere else, check it.
There was a max 1000 row limit by default and we had to
have that kicked up to get more then 1000 rows returned.
These links talk about that...
http://support.novell.com/cgi-
bin/search/searchtid.cgi?/10081596.htm
http://www.ldapadministrator.com/forum/viewtopic.php?t=14
>--Original Message--
>Bruce,
>thanks for the response, but I tried what you suggested
and still the query
>returned no data. It completed without error just no
data.
>Thanks,
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>|||Bruce,
I ran an export of the data in the user objects using ldifde and got back
exactly what I expected, but the same query in SQL Server Query Analyzer
comes back empty.
I guess it could be something with the link, but it was set up according to
the instructions I found on the web. The link was created in Enterprise
manager, but using the same settings that you specified in the
sp_addlinkedserver statement. It could also be a user security issue, but
I have tried logining in as the admin, SQL Sever admin and myself with no
data back.
Still playing around with it.
Alan
--
Message posted via http://www.sqlmonster.com|||Finally, thanks Bruce. I was reading your posting and I was finally able to
pull data.
I'm trying to pull just users that are a member of a certain group, how can
I do that?
This is what I have but not working; however, I was able to used your query
just to get a start and that was a big help.
SELECT *
FROM OPENQUERY( ADSI,
'SELECT cn
FROM ''LDAP://internal/OU=Account
Executives,DC=internal,DC=homequest,DC=com''
WHERE objectClass = ''users''')
GO
"Bruce de Freitas" wrote:
> Alan, not sure if it matters, but I usually do the
> ADIS/LDAP calls more like this format... Check the
> Linked Server also, for correct setup and security. Bruce
>
> SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
> ((objectClass=User));distinguishedname,homePhone')
> where distinguishedname like '%a%'
>
> >--Original Message--
> >I am trying to query our active directory for user
> information, but even
> >though I get the query to work I am getting no data
> back. I have a linked
> >server to the active directory from SQL Server 2000 and
> I am running the
> >query in the Query Analyzer.
> >
> >My query is as follows:
> >
> >select * from openquery(adsi, 'select name, homephone
> from
> >''ldap://DC=mydomain,DC=com'' where objectclass
> = ''User'' ')
> >
> >This query will complete without error, but no data is
> returned. I have
> >seen one other question of this nature here, but I saw
> no final resolution.
> >What is wrong?
> >
> >Alan
> >
> >--
> >Message posted via http://www.sqlmonster.com
> >.
> >
>|||Thank you, after playing with it for awhile I was able to get it to work.
Do you know if there is a way to extract users' contact folder from the
server-level?
"Bruce de Freitas" wrote:
> Sonya, this query would list all the GROUPs.
> SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
> (&(objectCategory=GROUP)); distinguishedname,CN')
>
> You'd then have to join all the Users to what groups they are in, if
> that is what youre trying to do... Bruce
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>sql
No Data returned from OPENQUERY to Active Directory
Alan, not sure if it matters, but I usually do the
ADIS/LDAP calls more like this format... Check the
Linked Server also, for correct setup and security. Bruce
SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
((objectClass=User));distinguishedname,h
omePhone')
where distinguishedname like '%a%'
>--Original Message--
>I am trying to query our active directory for user
information, but even
>though I get the query to work I am getting no data
back. I have a linked
>server to the active directory from SQL Server 2000 and
I am running the
>query in the Query Analyzer.
>My query is as follows:
>select * from openquery(adsi, 'select name, homephone
from
>''ldap://DC=mydomain,DC=com'' where objectclass
= ''User'' ')
>This query will complete without error, but no data is
returned. I have
>seen one other question of this nature here, but I saw
no final resolution.
>What is wrong?
>Alan
>--
>Message posted via http://www.droptable.com
>.
>Bruce,
thanks for the response, but I tried what you suggested and still the query
returned no data. It completed without error just no data.
Thanks,
Alan
Message posted via http://www.droptable.com|||Alan, not sure why youre getting no data back then.
You're sure of the domain name? there's no special
security on your AD to prevent you from accessing? The
linked sever is setup correctly? like so..'
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
go
Are you sure the data sits in the User ObjectClass?
Might be somewhere else, check it.
There was a max 1000 row limit by default and we had to
have that kicked up to get more then 1000 rows returned.
These links talk about that...
http://support.novell.com/cgi-
bin/search/searchtid.cgi?/10081596.htm
http://www.ldapadministrator.com/fo...wtopic.php?t=14
>--Original Message--
>Bruce,
>thanks for the response, but I tried what you suggested
and still the query
>returned no data. It completed without error just no
data.
>Thanks,
>Alan
>--
>Message posted via http://www.droptable.com
>.
>|||Bruce,
I ran an export of the data in the user objects using ldifde and got back
exactly what I expected, but the same query in SQL Server Query Analyzer
comes back empty.
I guess it could be something with the link, but it was set up according to
the instructions I found on the web. The link was created in Enterprise
manager, but using the same settings that you specified in the
sp_addlinkedserver statement. It could also be a user security issue, but
I have tried logining in as the admin, SQL Sever admin and myself with no
data back.
Still playing around with it.
Alan
Message posted via http://www.droptable.com|||Finally, thanks Bruce. I was reading your posting and I was finally able to
pull data.
I'm trying to pull just users that are a member of a certain group, how can
I do that?
This is what I have but not working; however, I was able to used your query
just to get a start and that was a big help.
SELECT *
FROM OPENQUERY( ADSI,
'SELECT cn
FROM ''LDAP://internal/OU=Account
Executives,DC=internal,DC=homequest,DC=c
om''
WHERE objectClass = ''users''')
GO
"Bruce de Freitas" wrote:
> Alan, not sure if it matters, but I usually do the
> ADIS/LDAP calls more like this format... Check the
> Linked Server also, for correct setup and security. Bruce
>
> SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
> ((objectClass=User));distinguishedname,h
omePhone')
> where distinguishedname like '%a%'
>
>
> information, but even
> back. I have a linked
> I am running the
> from
> = ''User'' ')
> returned. I have
> no final resolution.
>|||Sonya, this query would list all the GROUPs.
SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
(&(objectCategory=GROUP)); distinguishedname,CN')
You'd then have to join all the Users to what groups they are in, if
that is what youre trying to do... Bruce
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Thank you, after playing with it for awhile I was able to get it to work.
Do you know if there is a way to extract users' contact folder from the
server-level?
"Bruce de Freitas" wrote:
> Sonya, this query would list all the GROUPs.
> SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
> (&(objectCategory=GROUP)); distinguishedname,CN')
>
> You'd then have to join all the Users to what groups they are in, if
> that is what youre trying to do... Bruce
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
ADIS/LDAP calls more like this format... Check the
Linked Server also, for correct setup and security. Bruce
SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
((objectClass=User));distinguishedname,h
omePhone')
where distinguishedname like '%a%'
>--Original Message--
>I am trying to query our active directory for user
information, but even
>though I get the query to work I am getting no data
back. I have a linked
>server to the active directory from SQL Server 2000 and
I am running the
>query in the Query Analyzer.
>My query is as follows:
>select * from openquery(adsi, 'select name, homephone
from
>''ldap://DC=mydomain,DC=com'' where objectclass
= ''User'' ')
>This query will complete without error, but no data is
returned. I have
>seen one other question of this nature here, but I saw
no final resolution.
>What is wrong?
>Alan
>--
>Message posted via http://www.droptable.com
>.
>Bruce,
thanks for the response, but I tried what you suggested and still the query
returned no data. It completed without error just no data.
Thanks,
Alan
Message posted via http://www.droptable.com|||Alan, not sure why youre getting no data back then.
You're sure of the domain name? there's no special
security on your AD to prevent you from accessing? The
linked sever is setup correctly? like so..'
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
go
Are you sure the data sits in the User ObjectClass?
Might be somewhere else, check it.
There was a max 1000 row limit by default and we had to
have that kicked up to get more then 1000 rows returned.
These links talk about that...
http://support.novell.com/cgi-
bin/search/searchtid.cgi?/10081596.htm
http://www.ldapadministrator.com/fo...wtopic.php?t=14
>--Original Message--
>Bruce,
>thanks for the response, but I tried what you suggested
and still the query
>returned no data. It completed without error just no
data.
>Thanks,
>Alan
>--
>Message posted via http://www.droptable.com
>.
>|||Bruce,
I ran an export of the data in the user objects using ldifde and got back
exactly what I expected, but the same query in SQL Server Query Analyzer
comes back empty.
I guess it could be something with the link, but it was set up according to
the instructions I found on the web. The link was created in Enterprise
manager, but using the same settings that you specified in the
sp_addlinkedserver statement. It could also be a user security issue, but
I have tried logining in as the admin, SQL Sever admin and myself with no
data back.
Still playing around with it.
Alan
Message posted via http://www.droptable.com|||Finally, thanks Bruce. I was reading your posting and I was finally able to
pull data.
I'm trying to pull just users that are a member of a certain group, how can
I do that?
This is what I have but not working; however, I was able to used your query
just to get a start and that was a big help.
SELECT *
FROM OPENQUERY( ADSI,
'SELECT cn
FROM ''LDAP://internal/OU=Account
Executives,DC=internal,DC=homequest,DC=c
om''
WHERE objectClass = ''users''')
GO
"Bruce de Freitas" wrote:
> Alan, not sure if it matters, but I usually do the
> ADIS/LDAP calls more like this format... Check the
> Linked Server also, for correct setup and security. Bruce
>
> SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
> ((objectClass=User));distinguishedname,h
omePhone')
> where distinguishedname like '%a%'
>
>
> information, but even
> back. I have a linked
> I am running the
> from
> = ''User'' ')
> returned. I have
> no final resolution.
>|||Sonya, this query would list all the GROUPs.
SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
(&(objectCategory=GROUP)); distinguishedname,CN')
You'd then have to join all the Users to what groups they are in, if
that is what youre trying to do... Bruce
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Thank you, after playing with it for awhile I was able to get it to work.
Do you know if there is a way to extract users' contact folder from the
server-level?
"Bruce de Freitas" wrote:
> Sonya, this query would list all the GROUPs.
> SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
> (&(objectCategory=GROUP)); distinguishedname,CN')
>
> You'd then have to join all the Users to what groups they are in, if
> that is what youre trying to do... Bruce
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
No Data Returned for report
Hi,
In RS 2005, if a specific query does not return any rows, the table also is not displayed in the report.
Is there any way to get around this and show an empty table?
Manish
Hi,
select all the cells and in the properties make "borderstyle" - "Solid"
and select the table and make same as above in the properties.
you can see the empty table.
Amarnath
|||One option is to set the "NoRows" property of the table. You can also apply styles to that message.
-- Robert
|||This is correct but only if the table has a Header or Footer row.
If the dataset associated with a table returns no data, and there is no "NoRows" message specified for the table, AND if there is a header or footer, the table will be shown.
-chris
No data retuned
Hi,
Does anyone know of a way to show a user that there is no data returned into a report from a query, I am using ASP.NET with reportviewer and if the report does not return data I want to tell the user and not let them wait for no response. Is there a method or property within crystal that I can use?CrystalViewer control provides two functions called GetCurrentPageNumber() and ShowFirstPage(). Using these two functions you could move your cursor to page 1 and then try getting Getcurrentpagenumber or either call GetCurrentPageNumber which if returns 0 then there is no data else there is a data.
Thanks
Does anyone know of a way to show a user that there is no data returned into a report from a query, I am using ASP.NET with reportviewer and if the report does not return data I want to tell the user and not let them wait for no response. Is there a method or property within crystal that I can use?CrystalViewer control provides two functions called GetCurrentPageNumber() and ShowFirstPage(). Using these two functions you could move your cursor to page 1 and then try getting Getcurrentpagenumber or either call GetCurrentPageNumber which if returns 0 then there is no data else there is a data.
Thanks
Wednesday, March 7, 2012
Newby JOIN question
I'm attempting to use a JOIN for the first time and I'm getting about 94,000 rows returned when I should only be getting about 270. Something must not be unique (possible DSN) but I can't figure out why the join is not working. I know it must be difficult to determine the problem without actually seeing the tables but could somebody with a lot of SQL experience spot any glaring problems with this query? Thanks!
SELECT DS.DSNName AS DSN,
S.ServerName AS WebServer,
DBs.DBName,
A.ServerName AS SQLServer
FROM DSNs DS,
Servers S,
DBs,
DSNs LEFT OUTER JOIN Servers A
ON DSNs.SQLServerID = S.ServerID
WHERE DBs.Status = 1
AND DS.WebserverID = S.ServerID
AND DBs.DBID = DS.DBID
ORDER BY DSN
Hi reformatted, I got this:
SELECT DS.DSNName AS DSN,
S.ServerName AS WebServer,
DBs.DBName,
A.ServerName AS SQLServer
FROM DSNs DS,
INNER JOIN Servers S,
ON DS.WebserverID = S.ServerID
INNER JOIN DBs
ON DBs.DBID = DS.DBID
INNER JOIN DSNs
ON DSNs.SQLServerID = S.ServerID
LEFT OUTER JOIN Servers A
ON DSNs.SQLServerID = A.ServerID --I guess this was wrong as it was pointing to the S.Server not the A.Server, see if this helps.
WHERE DBs.Status = 1
AND DS.WebserverID = S.ServerID
AND DBs.DBID = DS.DBID
ORDER BY DSN
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for your reply. I removed a couple unnecessary commas and the query runs but does not pull back any data. I will play around with it. Please let me know if you have any additional suggestions.|||If I am not sure about a query, I always do a step.by-step approach, starting with the base query and adding more joins one by one controlling the expected results after each step. This is much easier than doing the whole query and then guessing by the results which JOIN was not the right one.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)