I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.edu
What account is SQL Server running under? The query is run in it's context.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>
|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Thursday, March 29, 2012
Distributed Query
I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>
Distributed Query
I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>>I would like to perform Distributed Queries on Active Directory via views
>>in SQL Server. I have created a linked server by executing:
>> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
>> 'ADSDSOObject', 'adsdatasource'
>> However whenever I run even the simplest query:
>> SELECT *FROM OPENQUERY( ADSI,
>> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
>> I get the following error:
>> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
>> that the user did not have the permission to perform the operation.
>> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
>> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates
>> that the user did not have the permission to perform the operation.].
>> I am logged into the server as an Administrator and logging into query
>> analyzer as sa - so I should have all required permissions.
>> Any ideas? Any help or direction is really appreciated - Thanks in
>> advance,
>> J. Shane Kunkle
>> jkunkle@.vt.edu
>>
>
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>>I would like to perform Distributed Queries on Active Directory via views
>>in SQL Server. I have created a linked server by executing:
>> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
>> 'ADSDSOObject', 'adsdatasource'
>> However whenever I run even the simplest query:
>> SELECT *FROM OPENQUERY( ADSI,
>> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
>> I get the following error:
>> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
>> that the user did not have the permission to perform the operation.
>> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
>> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates
>> that the user did not have the permission to perform the operation.].
>> I am logged into the server as an Administrator and logging into query
>> analyzer as sa - so I should have all required permissions.
>> Any ideas? Any help or direction is really appreciated - Thanks in
>> advance,
>> J. Shane Kunkle
>> jkunkle@.vt.edu
>>
>
distributed queries in win 2003 and win 2000
Hi there,
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhiSaradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhiSaradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison
distributed queries in win 2003 and win 2000
Hi there,
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhi
Saradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhi
Saradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison
distributed queries in win 2003 and win 2000
Hi there,
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhiSaradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhiSaradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison
Distributed queries
Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";J et OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MD W";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
Massimo
After other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work
sql
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";J et OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MD W";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
Massimo
After other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work
sql
Distributed Queries
We are using MS SQL Server 2000 service pack 4.
On our production servers the following scenerio works, our LAN
department recently rebuilt one of our development servers and we are
having problems.
On our central development server I create a temp table CREATE TABLE
#Temp(dur_code char(1) NOT NULL, tmc_code char(1) NOT NULL)
-- Inserting into the temp table on our central server from our DEV2
server works when called this way:
INSERT #temp
SELECT dur_code, tmc_code
FROM dev1.sms.dbo.term_cds
--This dynamic statement works from the central server to dev2 server
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
EXEC (@.vcCmd)
-- Inserting to the same temp table on the central server from the same
DEV2 server does not work when it is called dynamically.
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
INSERT #temp EXEC (@.vcCmd)
I am running this code through SQL Query Analyzer. This code works on
our central server when we call our other development server remotely
(and on all of our production servers). The Distributed Transaction
Coordinator is running on both servers.
Any ideas on what we have done wrong?Karen (karen.jones@.tusd1.org) writes:
> -- Inserting to the same temp table on the central server from the same
> DEV2 server does not work when it is called dynamically.
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> INSERT #temp EXEC (@.vcCmd)
What "does not work" mean? If there is an error message, please post it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||CLARIFICATION:
We are inserting into a table on our central server and making a
dynamic call to the dev2 server.
Karen wrote:
> We are using MS SQL Server 2000 service pack 4.
> On our production servers the following scenerio works, our LAN
> department recently rebuilt one of our development servers and we are
> having problems.
> On our central development server I create a temp table CREATE TABLE
> #Temp(dur_code char(1) NOT NULL, tmc_code char(1) NOT NULL)
> -- Inserting into the temp table on our central server from our DEV2
> server works when called this way:
> INSERT #temp
> SELECT dur_code, tmc_code
> FROM dev1.sms.dbo.term_cds
>
> --This dynamic statement works from the central server to dev2 server
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> EXEC (@.vcCmd)
>
> -- Inserting to the same temp table on the central server from the same
> DEV2 server does not work when it is called dynamically.
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> INSERT #temp EXEC (@.vcCmd)
> I am running this code through SQL Query Analyzer. This code works on
> our central server when we call our other development server remotely
> (and on all of our production servers). The Distributed Transaction
> Coordinator is running on both servers.
> Any ideas on what we have done wrong?|||CLARIFICATION:
There is no error... it just hangs and never returns. It will not even
let you cancel the query, you have to end Query Analyzer through task
manager.
Erland Sommarskog wrote:
> Karen (karen.jones@.tusd1.org) writes:
> What "does not work" mean? If there is an error message, please post it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Karen,
Please help us out in clarifying a few points.
1. DEV1 = "Central Server"
2. DEV2 = "Development Server"
3. You have a temp table #Temp created on DEV1
4. INSERTS into #Temp succeed when executed on DEV1
5. INSERTS into #Temp FAIL when executed on DEV2
6. DEV2 does NOT have a #Temp table
#Temp tables are accessible only to the connection that created them. It is
logical that #Temp would be accessible and the INSERT would succeed when
executed from DEV1. And also that the INSERT would FAIL when you attempt to
insert into DEV1's #Temp table from code executed on DEV2.
Perhaps you need to investigate using a 'global' ##Temp table. (2 pound
signs) This form of ##Temp table is accessible by all connections and stays
in scope as long as there is any single connection open.
If I have properly understood the issue, let me know. Also you might check
in BOL about #Temp / ##Temp tables and scope.
Regards,
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Karen" <karen.jones@.tusd1.org> wrote in message
news:1151102931.892749.87140@.p79g2000cwp.googlegroups.com...
> CLARIFICATION:
> We are inserting into a table on our central server and making a
> dynamic call to the dev2 server.
>
> Karen wrote:
>|||I am running the query on the central server through Query Analyzer.
Lets call that DEV1. The data is being added to the temp table on DEV1
from a table on DEV2.
A: The temp table exists on DEV1 and I am executing the statement on
DEV1
INSERT #temp EXEC ('SELECT x, y FROM DEV2.sms.dbo.table')
The above code works.
B:Using the same scenerio (A:) I execute the following code
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT x, y, FROM DEV2.sms.dbo.table'
INSERT #temp EXEC(@.vcCmd)
The above code does not work.
The same code as above (example B:) works on our production servers but
does not work on one (1) of our development servers, all other
development servers can run this code just fine.
Recently our LAN staff rebuilt the DEV2 server and this is when the
problem started.
Since example A: works we know it has nothing to do with the linked
server setup
and the DTC is running.
Arnie Rowland wrote:
> Karen,
> Please help us out in clarifying a few points.
> 1. DEV1 = "Central Server"
> 2. DEV2 = "Development Server"
> 3. You have a temp table #Temp created on DEV1
> 4. INSERTS into #Temp succeed when executed on DEV1
> 5. INSERTS into #Temp FAIL when executed on DEV2
> 6. DEV2 does NOT have a #Temp table
> #Temp tables are accessible only to the connection that created them. It i
s
> logical that #Temp would be accessible and the INSERT would succeed when
> executed from DEV1. And also that the INSERT would FAIL when you attempt t
o
> insert into DEV1's #Temp table from code executed on DEV2.
> Perhaps you need to investigate using a 'global' ##Temp table. (2 pound
> signs) This form of ##Temp table is accessible by all connections and stay
s
> in scope as long as there is any single connection open.
> If I have properly understood the issue, let me know. Also you might check
> in BOL about #Temp / ##Temp tables and scope.
> Regards,
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Karen" <karen.jones@.tusd1.org> wrote in message
> news:1151102931.892749.87140@.p79g2000cwp.googlegroups.com...|||Karen wrote:
> I am running the query on the central server through Query Analyzer.
> Lets call that DEV1. The data is being added to the temp table on DEV1
> from a table on DEV2.
> A: The temp table exists on DEV1 and I am executing the statement on
> DEV1
> INSERT #temp EXEC ('SELECT x, y FROM DEV2.sms.dbo.table')
> The above code works.
> B:Using the same scenerio (A:) I execute the following code
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT x, y, FROM DEV2.sms.dbo.table'
> INSERT #temp EXEC(@.vcCmd)
> The above code does not work.
> The same code as above (example B:) works on our production servers but
> does not work on one (1) of our development servers, all other
> development servers can run this code just fine.
> Recently our LAN staff rebuilt the DEV2 server and this is when the
> problem started.
> Since example A: works we know it has nothing to do with the linked
> server setup
> and the DTC is running.
>
Have you looked at sysprocesses on each server while this statement is
executing? Look there for any blocking, and also see what waittype is
specified for the spid that is running your statement.
Also look at perfmon on each machine, look at CPU and Average Disk Queue
length to see if either machine is CPU or I/O bound.|||No one is using these servers except myself. No other processes are
running on either server. The table I am accessing has 10 rows. I
believe this is a configuration problem.
Tracy McKibben wrote:
> Karen wrote:
> Have you looked at sysprocesses on each server while this statement is
> executing? Look there for any blocking, and also see what waittype is
> specified for the spid that is running your statement.
> Also look at perfmon on each machine, look at CPU and Average Disk Queue
> length to see if either machine is CPU or I/O bound.|||Karen (karen.jones@.tusd1.org) writes:
> CLARIFICATION:
> There is no error... it just hangs and never returns. It will not even
> let you cancel the query, you have to end Query Analyzer through task
> manager.
Ah, this behaviour. I've have one such situation at work as well. Alas,
I don't have a solution, as I've never cared about getting it fixed.
The only difference to your description is that for me it suffices to
to:
CREATE TABLE #dates (d datetime)
go
INSERT #dates (d)
EXEC ('SELECT * FROM NEMO.sec_93_stb.dbo.dates')
I don't have to put the SQL command in a variable. (And that should not
matter.)
What I have observed is that if I look in sysprocesses on NEMO, the
machice I connect to, there is an entry for a process with host name
JAMIE4K, the machine I connect from. So my conclusion is that the problem
occurs when NEMO tries to connect back to JAMIE4K, but never get a
response. As I've said, I have not dug into this, but our sy
min people
are very fond of running firewalls about everywhere, and they also lock
down the service accounts for SQL Server.
It is also worth noting that NEMO, the machine I connect to, runs
Windows 2003, whereas JAMIE4K where I connect from runs Windows 2000.
I guess you will have to talk with your LAN people, and ask them to
review what they did with the network.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Karen wrote:
> No one is using these servers except myself. No other processes are
> running on either server. The table I am accessing has 10 rows. I
> believe this is a configuration problem.
>
It very well could be a configuration problem, and looking at
sysprocesses to see what waittype, if any, the process is reporting, you
might well be able to figure out what is misconfigured. Perfmon might
also reveal some important clues. Suggestion is there, use it as you wish.
On our production servers the following scenerio works, our LAN
department recently rebuilt one of our development servers and we are
having problems.
On our central development server I create a temp table CREATE TABLE
#Temp(dur_code char(1) NOT NULL, tmc_code char(1) NOT NULL)
-- Inserting into the temp table on our central server from our DEV2
server works when called this way:
INSERT #temp
SELECT dur_code, tmc_code
FROM dev1.sms.dbo.term_cds
--This dynamic statement works from the central server to dev2 server
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
EXEC (@.vcCmd)
-- Inserting to the same temp table on the central server from the same
DEV2 server does not work when it is called dynamically.
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
INSERT #temp EXEC (@.vcCmd)
I am running this code through SQL Query Analyzer. This code works on
our central server when we call our other development server remotely
(and on all of our production servers). The Distributed Transaction
Coordinator is running on both servers.
Any ideas on what we have done wrong?Karen (karen.jones@.tusd1.org) writes:
> -- Inserting to the same temp table on the central server from the same
> DEV2 server does not work when it is called dynamically.
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> INSERT #temp EXEC (@.vcCmd)
What "does not work" mean? If there is an error message, please post it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||CLARIFICATION:
We are inserting into a table on our central server and making a
dynamic call to the dev2 server.
Karen wrote:
> We are using MS SQL Server 2000 service pack 4.
> On our production servers the following scenerio works, our LAN
> department recently rebuilt one of our development servers and we are
> having problems.
> On our central development server I create a temp table CREATE TABLE
> #Temp(dur_code char(1) NOT NULL, tmc_code char(1) NOT NULL)
> -- Inserting into the temp table on our central server from our DEV2
> server works when called this way:
> INSERT #temp
> SELECT dur_code, tmc_code
> FROM dev1.sms.dbo.term_cds
>
> --This dynamic statement works from the central server to dev2 server
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> EXEC (@.vcCmd)
>
> -- Inserting to the same temp table on the central server from the same
> DEV2 server does not work when it is called dynamically.
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> INSERT #temp EXEC (@.vcCmd)
> I am running this code through SQL Query Analyzer. This code works on
> our central server when we call our other development server remotely
> (and on all of our production servers). The Distributed Transaction
> Coordinator is running on both servers.
> Any ideas on what we have done wrong?|||CLARIFICATION:
There is no error... it just hangs and never returns. It will not even
let you cancel the query, you have to end Query Analyzer through task
manager.
Erland Sommarskog wrote:
> Karen (karen.jones@.tusd1.org) writes:
> What "does not work" mean? If there is an error message, please post it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Karen,
Please help us out in clarifying a few points.
1. DEV1 = "Central Server"
2. DEV2 = "Development Server"
3. You have a temp table #Temp created on DEV1
4. INSERTS into #Temp succeed when executed on DEV1
5. INSERTS into #Temp FAIL when executed on DEV2
6. DEV2 does NOT have a #Temp table
#Temp tables are accessible only to the connection that created them. It is
logical that #Temp would be accessible and the INSERT would succeed when
executed from DEV1. And also that the INSERT would FAIL when you attempt to
insert into DEV1's #Temp table from code executed on DEV2.
Perhaps you need to investigate using a 'global' ##Temp table. (2 pound
signs) This form of ##Temp table is accessible by all connections and stays
in scope as long as there is any single connection open.
If I have properly understood the issue, let me know. Also you might check
in BOL about #Temp / ##Temp tables and scope.
Regards,
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Karen" <karen.jones@.tusd1.org> wrote in message
news:1151102931.892749.87140@.p79g2000cwp.googlegroups.com...
> CLARIFICATION:
> We are inserting into a table on our central server and making a
> dynamic call to the dev2 server.
>
> Karen wrote:
>|||I am running the query on the central server through Query Analyzer.
Lets call that DEV1. The data is being added to the temp table on DEV1
from a table on DEV2.
A: The temp table exists on DEV1 and I am executing the statement on
DEV1
INSERT #temp EXEC ('SELECT x, y FROM DEV2.sms.dbo.table')
The above code works.
B:Using the same scenerio (A:) I execute the following code
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT x, y, FROM DEV2.sms.dbo.table'
INSERT #temp EXEC(@.vcCmd)
The above code does not work.
The same code as above (example B:) works on our production servers but
does not work on one (1) of our development servers, all other
development servers can run this code just fine.
Recently our LAN staff rebuilt the DEV2 server and this is when the
problem started.
Since example A: works we know it has nothing to do with the linked
server setup
and the DTC is running.
Arnie Rowland wrote:
> Karen,
> Please help us out in clarifying a few points.
> 1. DEV1 = "Central Server"
> 2. DEV2 = "Development Server"
> 3. You have a temp table #Temp created on DEV1
> 4. INSERTS into #Temp succeed when executed on DEV1
> 5. INSERTS into #Temp FAIL when executed on DEV2
> 6. DEV2 does NOT have a #Temp table
> #Temp tables are accessible only to the connection that created them. It i
s
> logical that #Temp would be accessible and the INSERT would succeed when
> executed from DEV1. And also that the INSERT would FAIL when you attempt t
o
> insert into DEV1's #Temp table from code executed on DEV2.
> Perhaps you need to investigate using a 'global' ##Temp table. (2 pound
> signs) This form of ##Temp table is accessible by all connections and stay
s
> in scope as long as there is any single connection open.
> If I have properly understood the issue, let me know. Also you might check
> in BOL about #Temp / ##Temp tables and scope.
> Regards,
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Karen" <karen.jones@.tusd1.org> wrote in message
> news:1151102931.892749.87140@.p79g2000cwp.googlegroups.com...|||Karen wrote:
> I am running the query on the central server through Query Analyzer.
> Lets call that DEV1. The data is being added to the temp table on DEV1
> from a table on DEV2.
> A: The temp table exists on DEV1 and I am executing the statement on
> DEV1
> INSERT #temp EXEC ('SELECT x, y FROM DEV2.sms.dbo.table')
> The above code works.
> B:Using the same scenerio (A:) I execute the following code
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT x, y, FROM DEV2.sms.dbo.table'
> INSERT #temp EXEC(@.vcCmd)
> The above code does not work.
> The same code as above (example B:) works on our production servers but
> does not work on one (1) of our development servers, all other
> development servers can run this code just fine.
> Recently our LAN staff rebuilt the DEV2 server and this is when the
> problem started.
> Since example A: works we know it has nothing to do with the linked
> server setup
> and the DTC is running.
>
Have you looked at sysprocesses on each server while this statement is
executing? Look there for any blocking, and also see what waittype is
specified for the spid that is running your statement.
Also look at perfmon on each machine, look at CPU and Average Disk Queue
length to see if either machine is CPU or I/O bound.|||No one is using these servers except myself. No other processes are
running on either server. The table I am accessing has 10 rows. I
believe this is a configuration problem.
Tracy McKibben wrote:
> Karen wrote:
> Have you looked at sysprocesses on each server while this statement is
> executing? Look there for any blocking, and also see what waittype is
> specified for the spid that is running your statement.
> Also look at perfmon on each machine, look at CPU and Average Disk Queue
> length to see if either machine is CPU or I/O bound.|||Karen (karen.jones@.tusd1.org) writes:
> CLARIFICATION:
> There is no error... it just hangs and never returns. It will not even
> let you cancel the query, you have to end Query Analyzer through task
> manager.
Ah, this behaviour. I've have one such situation at work as well. Alas,
I don't have a solution, as I've never cared about getting it fixed.
The only difference to your description is that for me it suffices to
to:
CREATE TABLE #dates (d datetime)
go
INSERT #dates (d)
EXEC ('SELECT * FROM NEMO.sec_93_stb.dbo.dates')
I don't have to put the SQL command in a variable. (And that should not
matter.)
What I have observed is that if I look in sysprocesses on NEMO, the
machice I connect to, there is an entry for a process with host name
JAMIE4K, the machine I connect from. So my conclusion is that the problem
occurs when NEMO tries to connect back to JAMIE4K, but never get a
response. As I've said, I have not dug into this, but our sy
min peopleare very fond of running firewalls about everywhere, and they also lock
down the service accounts for SQL Server.
It is also worth noting that NEMO, the machine I connect to, runs
Windows 2003, whereas JAMIE4K where I connect from runs Windows 2000.
I guess you will have to talk with your LAN people, and ask them to
review what they did with the network.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Karen wrote:
> No one is using these servers except myself. No other processes are
> running on either server. The table I am accessing has 10 rows. I
> believe this is a configuration problem.
>
It very well could be a configuration problem, and looking at
sysprocesses to see what waittype, if any, the process is reporting, you
might well be able to figure out what is misconfigured. Perfmon might
also reveal some important clues. Suggestion is there, use it as you wish.
Distributed queries
Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";Jet OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MDW";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
MassimoAfter other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";Jet OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MDW";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
MassimoAfter other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work
Distributed queries
Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";Jet OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MDW";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
MassimoAfter other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";Jet OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MDW";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
MassimoAfter other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work
distributed queries
Hi all,
Do you know that can we use 'distributed queries' in reporting services to
create rdl report or use linked server as datasource.
please advise if any direction, many thanks.
e.g.
SELECT s.sales_amount
FROM access_server...region AS r,
oracle_server..sales_owner.sale AS s,
sql_server.customer_db.dbo.customer AS c
where r.region_id=s.region_id
and s.customer_id=c.customer_id
and r.region_name='Southwest'
and c.customer_name='ABC Steel'Any legal queries can be used. To do something like this you need to switch
from graphical mode to non-graphical mode. There is a button to the right of
the ... that accomplishes this. Just hover your mouse over the buttons to
find the right one.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> Do you know that can we use 'distributed queries' in reporting services to
> create rdl report or use linked server as datasource.
> please advise if any direction, many thanks.
> e.g.
> SELECT s.sales_amount
> FROM access_server...region AS r,
> oracle_server..sales_owner.sale AS s,
> sql_server.customer_db.dbo.customer AS c
> where r.region_id=s.region_id
> and s.customer_id=c.customer_id
> and r.region_name='Southwest'
> and c.customer_name='ABC Steel'
>|||Hi Bruce,
I am not sure I undeestand the step(s) to generate the report. do you know
any example or document from microsoft.
Any way, thanks a lot for your help.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Any legal queries can be used. To do something like this you need to
> switch from graphical mode to non-graphical mode. There is a button to the
> right of the ... that accomplishes this. Just hover your mouse over the
> buttons to find the right one.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>|||Your problem is that RS graphical editor does not support this sort of
thing.
What you need to do is two steps. First, you cannot use the Report Wizard.
You need to create an empty report. Right mouse click on Reports, Add -> New
Item. Select Report. You get a blank report.
Second, Click on the dataset tab. Add a new dataset (drop down
combobox),Switch to non-graphical mode. Put in your SQL code.
Go to the dataset tab in the report designer, then do as I mentioned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:O1xpvIyFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Hi Bruce,
> I am not sure I undeestand the step(s) to generate the report. do you know
> any example or document from microsoft.
> Any way, thanks a lot for your help.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Any legal queries can be used. To do something like this you need to
>> switch from graphical mode to non-graphical mode. There is a button to
>> the right of the ... that accomplishes this. Just hover your mouse over
>> the buttons to find the right one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
>> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>>
>|||Ho Bruce,
Thanks for your direction, i have tried it, it real works ;-)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eNLHfnyFIHA.4140@.TK2MSFTNGP03.phx.gbl...
> Your problem is that RS graphical editor does not support this sort of
> thing.
> What you need to do is two steps. First, you cannot use the Report Wizard.
> You need to create an empty report. Right mouse click on Reports, Add ->
> New Item. Select Report. You get a blank report.
> Second, Click on the dataset tab. Add a new dataset (drop down
> combobox),Switch to non-graphical mode. Put in your SQL code.
> Go to the dataset tab in the report designer, then do as I mentioned.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:O1xpvIyFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Hi Bruce,
>> I am not sure I undeestand the step(s) to generate the report. do you
>> know any example or document from microsoft.
>> Any way, thanks a lot for your help.
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Any legal queries can be used. To do something like this you need to
>> switch from graphical mode to non-graphical mode. There is a button to
>> the right of the ... that accomplishes this. Just hover your mouse over
>> the buttons to find the right one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
>> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>>
>>
>
Do you know that can we use 'distributed queries' in reporting services to
create rdl report or use linked server as datasource.
please advise if any direction, many thanks.
e.g.
SELECT s.sales_amount
FROM access_server...region AS r,
oracle_server..sales_owner.sale AS s,
sql_server.customer_db.dbo.customer AS c
where r.region_id=s.region_id
and s.customer_id=c.customer_id
and r.region_name='Southwest'
and c.customer_name='ABC Steel'Any legal queries can be used. To do something like this you need to switch
from graphical mode to non-graphical mode. There is a button to the right of
the ... that accomplishes this. Just hover your mouse over the buttons to
find the right one.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> Do you know that can we use 'distributed queries' in reporting services to
> create rdl report or use linked server as datasource.
> please advise if any direction, many thanks.
> e.g.
> SELECT s.sales_amount
> FROM access_server...region AS r,
> oracle_server..sales_owner.sale AS s,
> sql_server.customer_db.dbo.customer AS c
> where r.region_id=s.region_id
> and s.customer_id=c.customer_id
> and r.region_name='Southwest'
> and c.customer_name='ABC Steel'
>|||Hi Bruce,
I am not sure I undeestand the step(s) to generate the report. do you know
any example or document from microsoft.
Any way, thanks a lot for your help.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Any legal queries can be used. To do something like this you need to
> switch from graphical mode to non-graphical mode. There is a button to the
> right of the ... that accomplishes this. Just hover your mouse over the
> buttons to find the right one.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>|||Your problem is that RS graphical editor does not support this sort of
thing.
What you need to do is two steps. First, you cannot use the Report Wizard.
You need to create an empty report. Right mouse click on Reports, Add -> New
Item. Select Report. You get a blank report.
Second, Click on the dataset tab. Add a new dataset (drop down
combobox),Switch to non-graphical mode. Put in your SQL code.
Go to the dataset tab in the report designer, then do as I mentioned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:O1xpvIyFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Hi Bruce,
> I am not sure I undeestand the step(s) to generate the report. do you know
> any example or document from microsoft.
> Any way, thanks a lot for your help.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Any legal queries can be used. To do something like this you need to
>> switch from graphical mode to non-graphical mode. There is a button to
>> the right of the ... that accomplishes this. Just hover your mouse over
>> the buttons to find the right one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
>> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>>
>|||Ho Bruce,
Thanks for your direction, i have tried it, it real works ;-)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eNLHfnyFIHA.4140@.TK2MSFTNGP03.phx.gbl...
> Your problem is that RS graphical editor does not support this sort of
> thing.
> What you need to do is two steps. First, you cannot use the Report Wizard.
> You need to create an empty report. Right mouse click on Reports, Add ->
> New Item. Select Report. You get a blank report.
> Second, Click on the dataset tab. Add a new dataset (drop down
> combobox),Switch to non-graphical mode. Put in your SQL code.
> Go to the dataset tab in the report designer, then do as I mentioned.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:O1xpvIyFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Hi Bruce,
>> I am not sure I undeestand the step(s) to generate the report. do you
>> know any example or document from microsoft.
>> Any way, thanks a lot for your help.
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Any legal queries can be used. To do something like this you need to
>> switch from graphical mode to non-graphical mode. There is a button to
>> the right of the ... that accomplishes this. Just hover your mouse over
>> the buttons to find the right one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
>> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>>
>>
>
Sunday, March 11, 2012
Displaying member name instead of key in query
In the tools that I have used for getting generated MDX queries (Query Analyzer, Proclarity, Excel) the query always seems to show the key value instead of the member name.
Is it possible or is there a query tool that will generate queries with the member name instead?
See example:
[Organization].&[152551]
Should read
[Organization].[By Division]
thanks,
Andrew
Set "MemberNamesUnique" to True for a particular attribute. That should do it|||
Thanks! Didn't even have to reprocess!
cheers,
Andrew
Sunday, February 19, 2012
display of dynamically generated queries
I wonder, if there is a way to display the dynamically generated
sqlstatement in a report.
I'm doing a lot with dynamically created queries and sometimes it'd be
very handy, if i could see the finally generated sqlstatement. I tried
the profiler, which works, and I want it nicer !
Thanks for any suggestions
ChrisThe closest you can get today, is to add a textbox to the report and
copy&paste the expression you use for the query into the textbox. Therefore,
the textbox will evaluate to the same value as the query.
We have already a tracking item for this (exposing the commandtext in the
ReportObjectModel) and it will be evaluated for inclusion in a future
release. Thanks for the suggestion.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"chrisM" <cmueller@.mcomt.de> wrote in message
news:545ffea8.0409250224.614445eb@.posting.google.com...
> I wonder, if there is a way to display the dynamically generated
> sqlstatement in a report.
> I'm doing a lot with dynamically created queries and sometimes it'd be
> very handy, if i could see the finally generated sqlstatement. I tried
> the profiler, which works, and I want it nicer !
> Thanks for any suggestions
> Chris
sqlstatement in a report.
I'm doing a lot with dynamically created queries and sometimes it'd be
very handy, if i could see the finally generated sqlstatement. I tried
the profiler, which works, and I want it nicer !
Thanks for any suggestions
ChrisThe closest you can get today, is to add a textbox to the report and
copy&paste the expression you use for the query into the textbox. Therefore,
the textbox will evaluate to the same value as the query.
We have already a tracking item for this (exposing the commandtext in the
ReportObjectModel) and it will be evaluated for inclusion in a future
release. Thanks for the suggestion.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"chrisM" <cmueller@.mcomt.de> wrote in message
news:545ffea8.0409250224.614445eb@.posting.google.com...
> I wonder, if there is a way to display the dynamically generated
> sqlstatement in a report.
> I'm doing a lot with dynamically created queries and sometimes it'd be
> very handy, if i could see the finally generated sqlstatement. I tried
> the profiler, which works, and I want it nicer !
> Thanks for any suggestions
> Chris
Subscribe to:
Posts (Atom)