I've thought about this a bit more and relised that it can be done using
dynamic SQL, but I would like to avoid this if possible. What are the other
options?
"WCL" <WCL@.nospam.nospam> wrote in message
news:usRAcZGIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Is it possible to have query result to have a column per row of a table?
>
> e.g.
> Employees table
> ID (identity)
> FirstName
> Ref
>
> containing
> ID, FirstName
> 1 Tom
> 2 Dick
> 3 Harry
>
>
> Project table
> ID (identity)
> Name
>
> containing
> ID, Name
> 1 Client A
> 2 Client B
> 3 Client C
>
> Timesheets
> ID (identity)
> Employees_ID
> Project_ID
> Hours
>
> containing 6 rows
> ID
> Employee_ID
> Project_ID
> Hours
> 1
> 1
> 1
> 5
> 2
> 1
> 2
> 15
> 3
> 2
> 1
> 2
> 4
> 2
> 2
> 4
> 5
> 3
> 1
> 8
> 6
> 3
> 2
> 8
>
> NB - No records for client C
>
> I can get three columns (name, client, hours) with nine rows no problem,
> but how do I get 3 rows with a column per Client, like this:?
>
> Name
> Client A
> Client B
> Client C
> Tom
> 5
> 15
> 0 or NULL
> Dick
> 2
> 4
> 0 or NULL
> Harry
> 8
> 8
> 0 or NULL
>
>
>No, it is not really useful from a SQL standpoint to do this, so it is not a
part of SQL (columns should contain like things, not multiple different
things.)
I think you can do it using several UNION ALLs:
SELECT client, cast(client as varchar(30)), 1 as sorting
FROM table
UNION ALL
SELECT client,cast(value1 as varchar(30)), 2 as sorting
FROM table
order by client, sorting --maybe something different than key
I would suggest using the client tool to format data like this.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"WCL" <WCL@.nospam.nospam> wrote in message
news:u4q6ipGIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> I've thought about this a bit more and relised that it can be done using
> dynamic SQL, but I would like to avoid this if possible. What are the
> other options?
> "WCL" <WCL@.nospam.nospam> wrote in message
> news:usRAcZGIGHA.1312@.TK2MSFTNGP09.phx.gbl...
>
Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts
Monday, March 26, 2012
query with a column per row of a linked table
Is it possible to have query result to have a column per row of a table?
e.g.
Employees table
ID (identity)
FirstName
Ref
containing
ID, FirstName
1 Tom
2 Dick
3 Harry
Project table
ID (identity)
Name
containing
ID, Name
1 Client A
2 Client B
3 Client C
Timesheets
ID (identity)
Employees_ID
Project_ID
Hours
containing 6 rows
ID
Employee_ID
Project_ID
Hours
1
1
1
5
2
1
2
15
3
2
1
2
4
2
2
4
5
3
1
8
6
3
2
8
NB - No records for client C
I can get three columns (name, client, hours) with nine rows no problem, but
how do I get 3 rows with a column per Client, like this:?
Name
Client A
Client B
Client C
Tom
5
15
0 or NULL
Dick
2
4
0 or NULL
Harry
8
8
0 or NULLWCL wrote:
> Is it possible to have query result to have a column per row of a table?
This is a common problem, known as "crosstab query" (hint: Google that).
First, you will want to read this:
http://www.stephenforte.net/owdasbl...>
15d6d813eeb8
This is harder to do when the number of ouput columns isn't static. I am
not aware of any ways to do that without dynamic SQL on SQL Server 2000 and
below. SQL Server 2005 provides PIVOT functionality -- which I have yet to
play with myself, but believe does exactly that.
Chris Priede|||"Chris Priede" <priede@.panix.com> wrote in message
news:%235a8RMHIGHA.676@.TK2MSFTNGP10.phx.gbl...
> WCL wrote:
> This is a common problem, known as "crosstab query" (hint: Google that).
> First, you will want to read this:
>
http://www.stephenforte.net/owdasbl...>
15d6d813eeb8
I do not think this is the CASE at all:)
> This is harder to do when the number of ouput columns isn't static. I am
> not aware of any ways to do that without dynamic SQL on SQL Server 2000
and
> below. SQL Server 2005 provides PIVOT functionality -- which I have yet
to
> play with myself, but believe does exactly that.
You be confusing your 'belief' with your 'wish':)
An alternative may be found @.
www.rac4sql.net|||Hi,
05ponyGT wrote:
> I do not think this is the CASE at all:)
> You be confusing your 'belief' with your 'wish':)
> An alternative may be found @.
> www.rac4sql.net
The absence of any technical insight to accompany your assertions led me to
Google your posting name. Of the 11 results returned, 11 are pushing this
particular product.
In addition, I couldn't help but notice that both the Rac "F.A.Q." and "What
can Rac do" section of documentation suffer from multiple instances of
incorrect usage of "your" vs. "you're", as well as other grammatical
sloppyness. It may help your advertising efforts to fix those first. :)
Chris Priede|||"Chris Priede" <priede@.panix.com> wrote in message
news:O7kZP3HIGHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi,
> 05ponyGT wrote:
> The absence of any technical insight to accompany your assertions led me
to
> Google your posting name. Of the 11 results returned, 11 are pushing this
> particular product.
That is extremely thin!
I'm aware my reply may have been a bit ponderous but just what
didn't you understand?Talk about insight:)
> In addition, I couldn't help but notice that both the Rac "F.A.Q." and
"What
> can Rac do" section of documentation suffer from multiple instances of
> incorrect usage of "your" vs. "you're", as well as other grammatical
> sloppyness. It may help your advertising efforts to fix those first. :)
You are right.Content always comes in second:)|||> http://www.stephenforte.net/owdasbl...
5-15d6d813eeb8
vey usefull link , then I googled for 'crosstab query.
Found some info on a 'dynamic cross tab', I'll give it a go and post with
the results.
"Chris Priede" <priede@.panix.com> wrote in message
news:%235a8RMHIGHA.676@.TK2MSFTNGP10.phx.gbl...
> WCL wrote:
> This is a common problem, known as "crosstab query" (hint: Google that).
> First, you will want to read this:
> http://www.stephenforte.net/owdasbl...
5-15d6d813eeb8
> This is harder to do when the number of ouput columns isn't static. I am
> not aware of any ways to do that without dynamic SQL on SQL Server 2000
> and below. SQL Server 2005 provides PIVOT functionality -- which I have
> yet to play with myself, but believe does exactly that.
>
> --
> Chris Priede
>
e.g.
Employees table
ID (identity)
FirstName
Ref
containing
ID, FirstName
1 Tom
2 Dick
3 Harry
Project table
ID (identity)
Name
containing
ID, Name
1 Client A
2 Client B
3 Client C
Timesheets
ID (identity)
Employees_ID
Project_ID
Hours
containing 6 rows
ID
Employee_ID
Project_ID
Hours
1
1
1
5
2
1
2
15
3
2
1
2
4
2
2
4
5
3
1
8
6
3
2
8
NB - No records for client C
I can get three columns (name, client, hours) with nine rows no problem, but
how do I get 3 rows with a column per Client, like this:?
Name
Client A
Client B
Client C
Tom
5
15
0 or NULL
Dick
2
4
0 or NULL
Harry
8
8
0 or NULLWCL wrote:
> Is it possible to have query result to have a column per row of a table?
This is a common problem, known as "crosstab query" (hint: Google that).
First, you will want to read this:
http://www.stephenforte.net/owdasbl...>
15d6d813eeb8
This is harder to do when the number of ouput columns isn't static. I am
not aware of any ways to do that without dynamic SQL on SQL Server 2000 and
below. SQL Server 2005 provides PIVOT functionality -- which I have yet to
play with myself, but believe does exactly that.
Chris Priede|||"Chris Priede" <priede@.panix.com> wrote in message
news:%235a8RMHIGHA.676@.TK2MSFTNGP10.phx.gbl...
> WCL wrote:
> This is a common problem, known as "crosstab query" (hint: Google that).
> First, you will want to read this:
>
http://www.stephenforte.net/owdasbl...>
15d6d813eeb8
I do not think this is the CASE at all:)
> This is harder to do when the number of ouput columns isn't static. I am
> not aware of any ways to do that without dynamic SQL on SQL Server 2000
and
> below. SQL Server 2005 provides PIVOT functionality -- which I have yet
to
> play with myself, but believe does exactly that.
You be confusing your 'belief' with your 'wish':)
An alternative may be found @.
www.rac4sql.net|||Hi,
05ponyGT wrote:
> I do not think this is the CASE at all:)
> You be confusing your 'belief' with your 'wish':)
> An alternative may be found @.
> www.rac4sql.net
The absence of any technical insight to accompany your assertions led me to
Google your posting name. Of the 11 results returned, 11 are pushing this
particular product.
In addition, I couldn't help but notice that both the Rac "F.A.Q." and "What
can Rac do" section of documentation suffer from multiple instances of
incorrect usage of "your" vs. "you're", as well as other grammatical
sloppyness. It may help your advertising efforts to fix those first. :)
Chris Priede|||"Chris Priede" <priede@.panix.com> wrote in message
news:O7kZP3HIGHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi,
> 05ponyGT wrote:
> The absence of any technical insight to accompany your assertions led me
to
> Google your posting name. Of the 11 results returned, 11 are pushing this
> particular product.
That is extremely thin!
I'm aware my reply may have been a bit ponderous but just what
didn't you understand?Talk about insight:)
> In addition, I couldn't help but notice that both the Rac "F.A.Q." and
"What
> can Rac do" section of documentation suffer from multiple instances of
> incorrect usage of "your" vs. "you're", as well as other grammatical
> sloppyness. It may help your advertising efforts to fix those first. :)
You are right.Content always comes in second:)|||> http://www.stephenforte.net/owdasbl...
5-15d6d813eeb8
vey usefull link , then I googled for 'crosstab query.
Found some info on a 'dynamic cross tab', I'll give it a go and post with
the results.
"Chris Priede" <priede@.panix.com> wrote in message
news:%235a8RMHIGHA.676@.TK2MSFTNGP10.phx.gbl...
> WCL wrote:
> This is a common problem, known as "crosstab query" (hint: Google that).
> First, you will want to read this:
> http://www.stephenforte.net/owdasbl...
5-15d6d813eeb8
> This is harder to do when the number of ouput columns isn't static. I am
> not aware of any ways to do that without dynamic SQL on SQL Server 2000
> and below. SQL Server 2005 provides PIVOT functionality -- which I have
> yet to play with myself, but believe does exactly that.
>
> --
> Chris Priede
>
Query whith Linked Server
Hi gruoup
I have to make a query from SQL Server tables joined with Visual FoxPro
xbase tables. I created a Linked Server with Microrosft OLE DB Provider for
Visual FoxPro provider, whth VFPOLEDB.1 string provider. Everything runs ok
within a Business Intelligence Development Studio environment, but when I
implement my report in SQL Reporting Server this one reports an error
because it can't create the object VFPOLEDB. I proved any combination of
Linked Server security tab page without to solve the problem.
Any help about this error I'll thank very much.I want to add some coments to clarify the problem.
The report runs at a right way from Internet Explorer 7 browser when I
generate it in a machine at which runs the Report Server, but I have
problems when I run it under Mozilla Fire Fox at local server machine or it
fails too from any browser at any intranet machine.
Thanks.
<tiempotecnologia@.newsgroup.nospam> escribió en el mensaje
news:Ou2%23o5kPHHA.1380@.TK2MSFTNGP05.phx.gbl...
> Hi gruoup
> I have to make a query from SQL Server tables joined with Visual FoxPro
> xbase tables. I created a Linked Server with Microrosft OLE DB Provider
> for Visual FoxPro provider, whth VFPOLEDB.1 string provider. Everything
> runs ok within a Business Intelligence Development Studio environment, but
> when I implement my report in SQL Reporting Server this one reports an
> error because it can't create the object VFPOLEDB. I proved any
> combination of Linked Server security tab page without to solve the
> problem.
> Any help about this error I'll thank very much.
>|||Hello Tiem
My understanding of this issue is that: You have a linked server in the sql
server and you wants to use it in the reporting services.
I would like to know what's the credential you supplied in the datasource
for the sql server.
If you use a sa account, did this report could be accessed?
Sincerely,
Wei Lu
Microsoft Online Community 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 Wei, thanks for your time!
I configured the database with RS Configuration tool under SQL credentials
using sa account and the report and its queries run correctly within
Business Intelligence Dev. Studio, i.e. the report preview, the execute of
query in data tab page, etc, everything runs ok, but from another intranet
machine, where I must authenticate with the account under run RS web
services, Report Manager opens the initial parameters view, I supply them
and then an error occurs because VFPOLEDB data provider object can`t create.
The physical Visual FoxPro tables are stored in an intranet machine within a
shared folder with read permission for anyone. Today I made a test moving
this tables to RS server local folder and I created a linked server ponting
to it but the same resulted. I tested with Domain\Useraccount credentials
for RS database too and the same resulted.
I'm waiting for help. Thanks in advance.
Arturo Carrión
artcarrion@.yahoo.com.ar
at Tiempo Hard SA
Mendoza, Argentina
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:POsb5hrPHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Tiem
> My understanding of this issue is that: You have a linked server in the
> sql
> server and you wants to use it in the reporting services.
> I would like to know what's the credential you supplied in the datasource
> for the sql server.
> If you use a sa account, did this report could be accessed?
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Arturo,
When I mentioned credentials, I mean the credentials you use in the report.
You could connect to the report manager, find the report and then provide
the credential information.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello, Wei
When you mentioned credentials, did you refer to ones to access SQL Server
or the Web Services ?
If I've a good understanding and you refered to SQL Server credentials, when
I generate a report I define its data source providing a connect string with
RS server name and initial catalog, but I don't define credentials. These
ones are defined at RS database configuration, not at a single report.
Please, correct me if I'm wrong.
I think that the problem seems to be related with some VFPOLEDB Provider
Security settings, do you believe it ?
Thank you very much.
Arturo Carrión
.Net/SQL Server Developer at
Tiempo Hard SA
artcarrion@.yahoo.com.ar
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Wei.
I'm so sorry!. Definitively I'm wrong. When I define a data source I must
provide the server and credentials. I defined them as sa account and its
password, test button has no problem.
Sincerely yours,
Arturo Carrión
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Great Wei !
I solve the problem following this 3 steps:
1. SQL Server Windows Service, Browser and RS all must run under domain
account or net service account.
2. VFPOLEDB Provider Setting "Allow Inprocess" must be checked.
3. DataSource at each report must have sql credentials (your suggestion), in
our case, sa account.
Thank you very much for your help.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hello Arturo,
Thanks for the update and glad to hear you resolved this issue.
You need to pass the credential in the datasource of the report so other
client could use this credential to connect to the linked server.
Otherwise, they will access denied.
If you have any questions, please feel free to let me know.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sql
I have to make a query from SQL Server tables joined with Visual FoxPro
xbase tables. I created a Linked Server with Microrosft OLE DB Provider for
Visual FoxPro provider, whth VFPOLEDB.1 string provider. Everything runs ok
within a Business Intelligence Development Studio environment, but when I
implement my report in SQL Reporting Server this one reports an error
because it can't create the object VFPOLEDB. I proved any combination of
Linked Server security tab page without to solve the problem.
Any help about this error I'll thank very much.I want to add some coments to clarify the problem.
The report runs at a right way from Internet Explorer 7 browser when I
generate it in a machine at which runs the Report Server, but I have
problems when I run it under Mozilla Fire Fox at local server machine or it
fails too from any browser at any intranet machine.
Thanks.
<tiempotecnologia@.newsgroup.nospam> escribió en el mensaje
news:Ou2%23o5kPHHA.1380@.TK2MSFTNGP05.phx.gbl...
> Hi gruoup
> I have to make a query from SQL Server tables joined with Visual FoxPro
> xbase tables. I created a Linked Server with Microrosft OLE DB Provider
> for Visual FoxPro provider, whth VFPOLEDB.1 string provider. Everything
> runs ok within a Business Intelligence Development Studio environment, but
> when I implement my report in SQL Reporting Server this one reports an
> error because it can't create the object VFPOLEDB. I proved any
> combination of Linked Server security tab page without to solve the
> problem.
> Any help about this error I'll thank very much.
>|||Hello Tiem
My understanding of this issue is that: You have a linked server in the sql
server and you wants to use it in the reporting services.
I would like to know what's the credential you supplied in the datasource
for the sql server.
If you use a sa account, did this report could be accessed?
Sincerely,
Wei Lu
Microsoft Online Community 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 Wei, thanks for your time!
I configured the database with RS Configuration tool under SQL credentials
using sa account and the report and its queries run correctly within
Business Intelligence Dev. Studio, i.e. the report preview, the execute of
query in data tab page, etc, everything runs ok, but from another intranet
machine, where I must authenticate with the account under run RS web
services, Report Manager opens the initial parameters view, I supply them
and then an error occurs because VFPOLEDB data provider object can`t create.
The physical Visual FoxPro tables are stored in an intranet machine within a
shared folder with read permission for anyone. Today I made a test moving
this tables to RS server local folder and I created a linked server ponting
to it but the same resulted. I tested with Domain\Useraccount credentials
for RS database too and the same resulted.
I'm waiting for help. Thanks in advance.
Arturo Carrión
artcarrion@.yahoo.com.ar
at Tiempo Hard SA
Mendoza, Argentina
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:POsb5hrPHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Tiem
> My understanding of this issue is that: You have a linked server in the
> sql
> server and you wants to use it in the reporting services.
> I would like to know what's the credential you supplied in the datasource
> for the sql server.
> If you use a sa account, did this report could be accessed?
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Arturo,
When I mentioned credentials, I mean the credentials you use in the report.
You could connect to the report manager, find the report and then provide
the credential information.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello, Wei
When you mentioned credentials, did you refer to ones to access SQL Server
or the Web Services ?
If I've a good understanding and you refered to SQL Server credentials, when
I generate a report I define its data source providing a connect string with
RS server name and initial catalog, but I don't define credentials. These
ones are defined at RS database configuration, not at a single report.
Please, correct me if I'm wrong.
I think that the problem seems to be related with some VFPOLEDB Provider
Security settings, do you believe it ?
Thank you very much.
Arturo Carrión
.Net/SQL Server Developer at
Tiempo Hard SA
artcarrion@.yahoo.com.ar
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Wei.
I'm so sorry!. Definitively I'm wrong. When I define a data source I must
provide the server and credentials. I defined them as sa account and its
password, test button has no problem.
Sincerely yours,
Arturo Carrión
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Great Wei !
I solve the problem following this 3 steps:
1. SQL Server Windows Service, Browser and RS all must run under domain
account or net service account.
2. VFPOLEDB Provider Setting "Allow Inprocess" must be checked.
3. DataSource at each report must have sql credentials (your suggestion), in
our case, sa account.
Thank you very much for your help.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hello Arturo,
Thanks for the update and glad to hear you resolved this issue.
You need to pass the credential in the datasource of the report so other
client could use this credential to connect to the linked server.
Otherwise, they will access denied.
If you have any questions, please feel free to let me know.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sql
Friday, March 23, 2012
Query users in a Security Group with LDAP
I have a linked server set up and working correctly. I can create a query to get all the users from active directory with something like this:
SELECT [name], [samaccountname] from OpenQuery( ADSI,
'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')
Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?
Thanks much.If that can't be done, is there anyway to check if a user is a member of a group or not through a linked server?sql
SELECT [name], [samaccountname] from OpenQuery( ADSI,
'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')
Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?
Thanks much.If that can't be done, is there anyway to check if a user is a member of a group or not through a linked server?sql
Tuesday, March 20, 2012
Query to Oracle via linked server hangs
Basic description:
We have developed a solution that sends data from SQL Server to an Oracle server as the result of a stored procedure called by a job that runs every minute. While this development worked fine in our test environment, after moving it to production it ran successfully the first minute, but the second minute the stored procedure hung, and the process could not be killed. In order to stop the process I had to stop both the SQL Agent and the MSDTC services.
Our SQL Server box:
SQL Server 2000 Standard Edition SP4
Windows 2003 Server R2 SP1
Our Oracle box:
Test: Oracle 9.2.0.6
Production: Oracle 9.2.0.4
To setup the SQL box, I did the following:
1) Install Oracle Client Tools version 10.2.0.1
2) Restart Server
3) Modify the registry as follows:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI] "OracleXaLib"="oraclient10.dll" "OracleSqlLib"="orasql10.dll" "OracleOciLib"="oci.dll"
4) Modified the PATH variable so that all references to SQL Server appear in front of Oracle path references
5) Added the linked server via sp_addlinkedserver '<tns name>','Oracle','MSDAORA','<tns name>'
6) Added linked server logins via sp_addlinkedsrvlogin '<tns name>','False','<SQL user>','<Oracle User name>','<password on oracle>'
7) Changed the registry for MSDTC to match this:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC]
"AllowOnlySecureRpcCalls"=dword:00000000
"FallbackToUnsecureRPCIfNecessary"=dword:00000001
"TurnOffRpcSecurity"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\Security]
"NetworkDtcAccess"=dword:00000001
"NetworkDtcAccessAdmin"=dword:00000001
"NetworkDtcAccessClients"=dword:00000001
"NetworkDtcAccessTransactions"=dword:00000001
"NetworkDtcAccessTip"=dword:00000001
"XaTransactions"=dword:00000001
"DomainControllerState"=dword:00000000
"AccountName"="NT Authority\\NetworkService"
"NetworkDtcAccessOutbound"=dword:00000001
"NetworkDtcAccessInbound"=dword:00000001
8) Stopped and restarted services in the following order:
1) MSDTC Stop
2) SQL Server Stop
3) MSDTC Start
4) SQL Server Start
The stored procedure:
In a single transaction, the stored procedure compares a production table against a logging table. If a record exists in the production table that is not in the logging table, a record is inserted into logging table, and a record sent to Oracle via an INSERT INTO OPENQUERY('INSTANCE','SELECT Column1, column2, column3,... FROM SCHEMA.TABLE')
SELECT column1,column2,column FROM SQLTable
This stored procedure has worked just fine for us in test, to either the test or production Oracle boxes, but it now fails, and hangs, in production to either the test or production Oracle boxes.
Additionally, I can run the following query via Query Analyzer from our test box to both the test Oracle and production Oracle and it runs successfully (this is NOT used in our stored procedure code, but is presented here as an indication that I think there is something wrong with the settings on our production SQL box):
SELECT * FROM OPENQUERY('INSTANCE','SELECT * FROM SCHEMA.TABLE')
When I run this same query via Query Analyzer on our Production SQL box, to either the test Oracle or production Oracle, it hangs, and I have to kill the process, and restart the MSDTC service.
Other queries that hang are:
SELECT * FROM SERVER..SCHEMA.TABLE
Additionally, I noticed that when I used this method to kill the process I would see errors like the following in the Application Event Log on the SQL box:
The XA Transaction Manager attempted to perform recovery with the XA resource manager. The XA resource manager reported that recovery was unsuccessful. DSN = MTxOCI.Dll.
Since I figured this was an aborted transaction still residing in the MSDTC log file, I would stop the MSDTC service, delete the MSDTC log file, reset the MSDTC log, and then restart the MSDTC service in order to prevent this error from occurring.
Not ALL queries from the production SQL box to production and test Oracle boxes fail. I can get results returned for this query:
SELECT COLUMN1, COLUMN2 FROM SERVER..SCHEMA.TABLE
I've been scouring the internet for about a week now, and I've run out of ideas on what to check on the production SQL box. Any suggestions would be greatly appreciated.
Tim
Dismayed by the lack of comment on my problem, I did some additional research into what is happening on my machines. I setup the Sysinternals utility ProcMon to capture file access when I run the query, both on Production and on Test. The biggest different I noticed was that, on Production where the queries are hanging, there's ALOT of activity by DLLHOST.exe opening oracle dlls. No such activity occurs on Test. In fact on Test, DLLHOST.exe isn't running at all.So, I did some searching on Oracle's metalink, and Note 333327.1 (which points to KB 833388) suggests that DLLHOST.exe indicates that I've somehow configured my Oracle Provider for OLEDB to run Out of Process, and suggests modifying the registry key for the Oracle provider, HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers\OraOLEDB.Oracle\ and set AllowInProcess=1
A couple things about this puzzle me. First, the Providers folder in the registry on my Test box (again, where the queries work fine) does not contain any entries whatsoever. On Production, I've got a bunch of folders in the Providers key, but none for OraOLEDB.Oracle. And why would it even matter, since I've configured my linked server to use MSDAORA? The MSDAORA key contains AllowInProcess=1 on Production.
Am I looking down the wrong path? Any suggestions?
|||
Did some more work on this today, and answered some of my own questions, but not the most important one.
I was able to find my Providers key on Test. Since we have multiple SQL Server instances on Test, it was under an instance folder at HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\Providers\
I added keys for \OraOLEDB.Oracle\ to both Production and Test, and made sure to set AllowInProcess=1 in the registry. I restarted SQL Server on both Production and Test. The queries, and subsequently the development, both worked fine in Test. On Production, I ran one of my trouble queries, SELECT * FROM LinkServer..Schema.Table, against the test Oracle instance, and I got results!
But when I ran it a second time, the query hung.
I tried the same test against the production Oracle instance. The query ran successfully the first time, but now all subsequent running of the query just hangs.
Back to the drawing board.
|||Worked on this again yesterday. I coordinated with our Oracle guy to see if he could watch what was happening on his end. As we tested, I saw that my SELECT * queries actually could complete today! Sure, they took over a minute, when from test they took less than a second, but it's progress, of a sort.I setup ProcMon again to catch file and registry key activity again on the Production SQL Server box that has the long running queries. I verified that the linked queries to Oracle are now running In Process (no longer using DLLHOST.exe). But, more interesting, I spotted a number of Buffer Overflows occurring. I thought I was on to something, but then I saw this blog entry:
http://blogs.technet.com/markrussinovich/archive/2005/05/17/buffer-overflows.aspx
So, it may be nothing, and I'm back to the drawing board again.
|||
A resolution! The network/infrastructure folks looked at the problem after I asked them to check out the NIC on the server with the Production SQL, and it turns out that the ports on the Cisco router that the server was plugged into was configured in such a manner that was resulting in lots of dropped packets and packet collisions. After changing the Cisco router setting for those ports, my queries run as expected from my SQL Server production box.
I learned alot during this problem, though....
Query to Oracle via linked server hangs
Basic description:
We have developed a solution that sends data from SQL Server to an Oracle server as the result of a stored procedure called by a job that runs every minute. While this development worked fine in our test environment, after moving it to production it ran successfully the first minute, but the second minute the stored procedure hung, and the process could not be killed. In order to stop the process I had to stop both the SQL Agent and the MSDTC services.
Our SQL Server box:
SQL Server 2000 Standard Edition SP4
Windows 2003 Server R2 SP1
Our Oracle box:
Test: Oracle 9.2.0.6
Production: Oracle 9.2.0.4
To setup the SQL box, I did the following:
1) Install Oracle Client Tools version 10.2.0.1
2) Restart Server
3) Modify the registry as follows:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI] "OracleXaLib"="oraclient10.dll" "OracleSqlLib"="orasql10.dll" "OracleOciLib"="oci.dll"
4) Modified the PATH variable so that all references to SQL Server appear in front of Oracle path references
5) Added the linked server via sp_addlinkedserver '<tns name>','Oracle','MSDAORA','<tns name>'
6) Added linked server logins via sp_addlinkedsrvlogin '<tns name>','False','<SQL user>','<Oracle User name>','<password on oracle>'
7) Changed the registry for MSDTC to match this:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC]
"AllowOnlySecureRpcCalls"=dword:00000000
"FallbackToUnsecureRPCIfNecessary"=dword:00000001
"TurnOffRpcSecurity"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\Security]
"NetworkDtcAccess"=dword:00000001
"NetworkDtcAccessAdmin"=dword:00000001
"NetworkDtcAccessClients"=dword:00000001
"NetworkDtcAccessTransactions"=dword:00000001
"NetworkDtcAccessTip"=dword:00000001
"XaTransactions"=dword:00000001
"DomainControllerState"=dword:00000000
"AccountName"="NT Authority\\NetworkService"
"NetworkDtcAccessOutbound"=dword:00000001
"NetworkDtcAccessInbound"=dword:00000001
8) Stopped and restarted services in the following order:
1) MSDTC Stop
2) SQL Server Stop
3) MSDTC Start
4) SQL Server Start
The stored procedure:
In a single transaction, the stored procedure compares a production table against a logging table. If a record exists in the production table that is not in the logging table, a record is inserted into logging table, and a record sent to Oracle via an INSERT INTO OPENQUERY('INSTANCE','SELECT Column1, column2, column3,... FROM SCHEMA.TABLE')
SELECT column1,column2,column FROM SQLTable
This stored procedure has worked just fine for us in test, to either the test or production Oracle boxes, but it now fails, and hangs, in production to either the test or production Oracle boxes.
Additionally, I can run the following query via Query Analyzer from our test box to both the test Oracle and production Oracle and it runs successfully (this is NOT used in our stored procedure code, but is presented here as an indication that I think there is something wrong with the settings on our production SQL box):
SELECT * FROM OPENQUERY('INSTANCE','SELECT * FROM SCHEMA.TABLE')
When I run this same query via Query Analyzer on our Production SQL box, to either the test Oracle or production Oracle, it hangs, and I have to kill the process, and restart the MSDTC service.
Other queries that hang are:
SELECT * FROM SERVER..SCHEMA.TABLE
Additionally, I noticed that when I used this method to kill the process I would see errors like the following in the Application Event Log on the SQL box:
The XA Transaction Manager attempted to perform recovery with the XA resource manager. The XA resource manager reported that recovery was unsuccessful. DSN = MTxOCI.Dll.
Since I figured this was an aborted transaction still residing in the MSDTC log file, I would stop the MSDTC service, delete the MSDTC log file, reset the MSDTC log, and then restart the MSDTC service in order to prevent this error from occurring.
Not ALL queries from the production SQL box to production and test Oracle boxes fail. I can get results returned for this query:
SELECT COLUMN1, COLUMN2 FROM SERVER..SCHEMA.TABLE
I've been scouring the internet for about a week now, and I've run out of ideas on what to check on the production SQL box. Any suggestions would be greatly appreciated.
Tim
Dismayed by the lack of comment on my problem, I did some additional research into what is happening on my machines. I setup the Sysinternals utility ProcMon to capture file access when I run the query, both on Production and on Test. The biggest different I noticed was that, on Production where the queries are hanging, there's ALOT of activity by DLLHOST.exe opening oracle dlls. No such activity occurs on Test. In fact on Test, DLLHOST.exe isn't running at all.So, I did some searching on Oracle's metalink, and Note 333327.1 (which points to KB 833388) suggests that DLLHOST.exe indicates that I've somehow configured my Oracle Provider for OLEDB to run Out of Process, and suggests modifying the registry key for the Oracle provider, HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers\OraOLEDB.Oracle\ and set AllowInProcess=1
A couple things about this puzzle me. First, the Providers folder in the registry on my Test box (again, where the queries work fine) does not contain any entries whatsoever. On Production, I've got a bunch of folders in the Providers key, but none for OraOLEDB.Oracle. And why would it even matter, since I've configured my linked server to use MSDAORA? The MSDAORA key contains AllowInProcess=1 on Production.
Am I looking down the wrong path? Any suggestions?
|||
Did some more work on this today, and answered some of my own questions, but not the most important one.
I was able to find my Providers key on Test. Since we have multiple SQL Server instances on Test, it was under an instance folder at HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\Providers\
I added keys for \OraOLEDB.Oracle\ to both Production and Test, and made sure to set AllowInProcess=1 in the registry. I restarted SQL Server on both Production and Test. The queries, and subsequently the development, both worked fine in Test. On Production, I ran one of my trouble queries, SELECT * FROM LinkServer..Schema.Table, against the test Oracle instance, and I got results!
But when I ran it a second time, the query hung.
I tried the same test against the production Oracle instance. The query ran successfully the first time, but now all subsequent running of the query just hangs.
Back to the drawing board.
|||Worked on this again yesterday. I coordinated with our Oracle guy to see if he could watch what was happening on his end. As we tested, I saw that my SELECT * queries actually could complete today! Sure, they took over a minute, when from test they took less than a second, but it's progress, of a sort.I setup ProcMon again to catch file and registry key activity again on the Production SQL Server box that has the long running queries. I verified that the linked queries to Oracle are now running In Process (no longer using DLLHOST.exe). But, more interesting, I spotted a number of Buffer Overflows occurring. I thought I was on to something, but then I saw this blog entry:
http://blogs.technet.com/markrussinovich/archive/2005/05/17/buffer-overflows.aspx
So, it may be nothing, and I'm back to the drawing board again.
|||
A resolution! The network/infrastructure folks looked at the problem after I asked them to check out the NIC on the server with the Production SQL, and it turns out that the ports on the Cisco router that the server was plugged into was configured in such a manner that was resulting in lots of dropped packets and packet collisions. After changing the Cisco router setting for those ports, my queries run as expected from my SQL Server production box.
I learned alot during this problem, though....
Query to only display information from one table where the foreign key doesnt exist in the
I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.
For example. If I had two tables with a one to many relationship.
A [Computer] has one or more [Hard Drives].
I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist.
This seems simple but I'm drawing a blank here.
SELECT * FROM Computer where ComputerId NOT IN (SELECT ComputerId FROM [Hard Drives])
query to obtain linked server properties
Im trying to find the table/column that stores the linked server security
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers already
Hi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers already
Hi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj
query to obtain linked server properties
Im trying to find the table/column that stores the linked server security
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers alreadyHi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers alreadyHi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj
query to obtain linked server properties
Im trying to find the table/column that stores the linked server security
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers alreadyHi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers alreadyHi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj
query to Linksed server very slow!
Hello,
i have created an RPC from my SQL server which queries a database of a linked server (remote server).
the query result is very very slow. (it is a mis-size query, with many JOINs on tables with many entries). Let's say it takes about two minutes to get about 3000 results.
The query uses five tables in the database of the linked server, runs a few (let's about 5-8) JOIN clauses and selects the entries. except for two tables (out of 8), each table has about 1000-2000 entries. the two have about 40,000 entries.
Is this normal?!
Is there anyway i can optimize my query?
i also tested my query and asked for only 100 results as opposed to all of the 3000. there was only 2-3 second difference in getting the results back, which indicates that it is not the remote connection but the query itself which is slow.
any help would be greatly appreciated!!How about the speed when you connect to your linked server directly and test your query? You can use SQL Server Management Studio to issue the same query to the linked server directly. SQL Server 2005 Database Tuning Advisor can help to you to optimize the query.
Query to linked server:Oracle; problems with ANSI_NULLS;ANSI_WARNINGS
When I perform a query on a linked Oracle server in the Query analyser I
have no
prboblem' to perform this query.
However, when I create this query in a stored procedure I get a compilation
error
when saving this procedure. (Not when compiling; it has no errors)
have no
prboblem' to perform this query.
However, when I create this query in a stored procedure I get a compilation
error
when saving this procedure. (Not when compiling; it has no errors)
Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
to be set for the connection. This ensures consistent query semantics.
Enable these options and then reissue your query.
When I create a dynamic SQL statement then I can save this stored procedure
when I run the stored procedure this same error happens.
What do I have to do.
Arno de Jong, The Netherlands"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bq9lqp$qag$1@.reader11.wxs.nl...
> When I perform a query on a linked Oracle server in the Query analyser I
> have no
> prboblem' to perform this query.
> However, when I create this query in a stored procedure I get a
compilation
> error
> when saving this procedure. (Not when compiling; it has no errors)
> Server: Msg 7405, Level 16, State 1, Line 1
> Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
> to be set for the connection. This ensures consistent query semantics.
> Enable these options and then reissue your query.
> When I create a dynamic SQL statement then I can save this stored
procedure
> when I run the stored procedure this same error happens.
> What do I have to do.
> Arno de Jong, The Netherlands
Have you tried putting SET ANSI_NULLS ON and SET ANSI_WARNINGS ON at the
start of your stored procedure, ie. in the procedure code itself? QA sets
these on automatically, but other connections may not.
Simon
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".
Query to get Linked List kind of data from the Table
hi Experts,
I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of
i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.
Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate
i appreciate if somebody can help me think in some line to give me some idea.
/soni
This is a common problem, what you will find is that you actually have a tree structure where 42217 is the root of the tree, 65702 is a branch, and 77637 is a leaf. Have a search around Google for Celko's "nested set" which has a solution which should help you here.
Query to get Linked List kind of data from the Table
hi Experts,
I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of
i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.
Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate
i appreciate if somebody can help me think in some line to give me some idea.
/soni
Assuming you are using 2005 you can use a common table expression (CTE) and use the following syntax:
DECLARE @.ID int
SET @.ID = 77637
;WITH Dupes (ID, Duplicate_of, State) AS
(
SELECT
ID, Duplicate_of, State
FROM
dbo.test
WHERE
ID = @.ID
UNION ALL
SELECT
T.ID, T.Duplicate_of, T.State
FROM
dbo.test T
INNER JOIN Dupes D ON D.Duplicate_of = T.ID
)
SELECT *
FROM Dupes
|||hi Weaver,
Many Thanks for looking at my problem.
i forgot to mention i use SQL Server 2000 as of now. cannot upgrade to SQL Server 2005. :(
/Soni
|||You can use this function to return the root of the tree (assuming you wanted to do this one at a time :)
set nocount on
drop table issue
drop function issue$getRoot
go
create table issue
(
issueId int primary key,
duplicateOf int references issue(issueId)
)
insert into issue
select 1, NULL
insert into issue
select 2, 1
insert into issue
select 3, 2
insert into issue
select 4, NULL
insert into issue
select 5, 4
go
create function issue$getRoot
(
@.issueId int
)
returns int
as
begin
while(1=1)
begin
select @.issueId = issue.issueId
from issue
join issue as dup
on dup.duplicateOf = issue.issueId
where dup.issueId = @.issueId
if @.@.rowcount = 0
break
end
return @.issueId
end
go
select dbo.issue$getRoot (5)
|||hi Louis
Thanks a million for yr expert logic. its working!!!. :)
/Soni
Saturday, February 25, 2012
query text file as linked server in v. 7.0?
Hi,
Can one query a text file as a linked server in sql 7.0? If so, anyone know which driver/provider string I would use?
thxOriginally posted by manster
Hi,
Can one query a text file as a linked server in sql 7.0? If so, anyone know which driver/provider string I would use?
thx
Did you check out sp_addlimked server? There's a section on text files...
BOL
H. Use the Microsoft OLE DB Provider for Jet to access a text file
This example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is 'Text'.
The data source is the full pathname of the directory that contains the text files. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. For more information about creating a schema.ini file, refer to Jet Database Engine documentation.
--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\data\distqry',
NULL,
'Text'
GO
Can one query a text file as a linked server in sql 7.0? If so, anyone know which driver/provider string I would use?
thxOriginally posted by manster
Hi,
Can one query a text file as a linked server in sql 7.0? If so, anyone know which driver/provider string I would use?
thx
Did you check out sp_addlimked server? There's a section on text files...
BOL
H. Use the Microsoft OLE DB Provider for Jet to access a text file
This example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is 'Text'.
The data source is the full pathname of the directory that contains the text files. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. For more information about creating a schema.ini file, refer to Jet Database Engine documentation.
--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\data\distqry',
NULL,
'Text'
GO
Subscribe to:
Posts (Atom)