Hi Everyone
I have a question regarding a function I have which is licensed on a
particular server and
uses an ActiveX component to pull a list of names and addresses from a
Post Office Address File.
Its expensive to license the component on another server so I was
wondering if its possible to have a linked server
and open the function to return the data to another server?
Any help would be very much appreciated.
Mattie
Chester Zoo> Its expensive to license the component on another server so I was
> wondering if its possible to have a linked server
> and open the function to return the data to another server?
I haven't actually done this via a linked server but you ought to be able to
encapsulate the ActiveX requests in a stored procedure using the sp_OA*
procs. However, be sure to thoroughly review the vendor licensing agreement
to make sure this use is allowed. Many EULAs have provisions that prohibit
multiplexing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1192366692.494333.154910@.i38g2000prf.googlegroups.com...
> Hi Everyone
> I have a question regarding a function I have which is licensed on a
> particular server and
> uses an ActiveX component to pull a list of names and addresses from a
> Post Office Address File.
> Its expensive to license the component on another server so I was
> wondering if its possible to have a linked server
> and open the function to return the data to another server?
> Any help would be very much appreciated.
> Mattie
> Chester Zoo
>|||On 14 Oct, 16:40, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > Its expensive to license the component on another server so I was
> > wondering if its possible to have a linked server
> > and open the function to return the data to another server?
> I haven't actually done this via a linked server but you ought to be able to
> encapsulate the ActiveX requests in a stored procedure using the sp_OA*
> procs. However, be sure to thoroughly review the vendor licensing agreement
> to make sure this use is allowed. Many EULAs have provisions that prohibit
> multiplexing.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MattieG" <mattiegrif...@.hotmail.co.uk> wrote in message
> news:1192366692.494333.154910@.i38g2000prf.googlegroups.com...
>
> > Hi Everyone
> > I have a question regarding a function I have which is licensed on a
> > particular server and
> > uses an ActiveX component to pull a list of names and addresses from a
> > Post Office Address File.
> > Its expensive to license the component on another server so I was
> > wondering if its possible to have a linked server
> > and open the function to return the data to another server?
> > Any help would be very much appreciated.
> > Mattie
> > Chester Zoo- Hide quoted text -
> - Show quoted text -
Hi
Thanks for that.
The only other way is to install IIS on the SQL box and transfer the
system to that.
It should be ok as the IIS is not going external so security isnt much
of a problem.
Thanks
Mattie
Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
Active Directory User's Member Groups from SQL Server 2000
I can create a Linked Server for ADSI sucessfully but cannot retrieve group
membership for an indivdual user.
I've seen some examples of syntax but can't get it to work.
Does anyone have an example of some code I could use
LindaOLE DB provider for Directory Services does not support multi-valued
attributes such as: members and memberOf. sorry...
"Linda Lou" wrote:
> I can create a Linked Server for ADSI sucessfully but cannot retrieve grou
p
> membership for an indivdual user.
> I've seen some examples of syntax but can't get it to work.
> Does anyone have an example of some code I could use
> Linda
membership for an indivdual user.
I've seen some examples of syntax but can't get it to work.
Does anyone have an example of some code I could use
LindaOLE DB provider for Directory Services does not support multi-valued
attributes such as: members and memberOf. sorry...
"Linda Lou" wrote:
> I can create a Linked Server for ADSI sucessfully but cannot retrieve grou
p
> membership for an indivdual user.
> I've seen some examples of syntax but can't get it to work.
> Does anyone have an example of some code I could use
> Linda
Active Directory select
Hi all,
I 'm sorry my wrong english, please.
So i have a problem. I make in a linked server which represent my server 's active directory. I would like to write a sql query what show the users and when expire their password.
How can i write this query?
Thank you in anticipation!
Trying to translate this for you to a more readable question.
Thanks in advance.
-- I think this is what he wants --|||in my reply
http://www.msdner.com/forum/thread626320.html
this was a discussion at that link.
I 'm sorry my wrong english, please.
So i have a problem. I make in a linked server which represent my server 's active directory. I would like to write a sql query what show the users and when expire their password.
How can i write this query?
Thank you in anticipation!
Diccsy
Quote:
Originally Posted by diccsy
Hi all,
I 'm sorry my wrong english, please.
So i have a problem. I make in a linked server which represent my server 's active directory. I would like to write a sql query what show the users and when expire their password.
How can i write this query?
Thank you in anticipation!
Diccsy
Trying to translate this for you to a more readable question.
Quote:
Originally Posted by
I would like to know how to create a SQL Query that shows the users in my servers active directory and when their passwords expire.
Thanks in advance.
-- I think this is what he wants --|||in my reply
http://www.msdner.com/forum/thread626320.html
this was a discussion at that link.
Active Directory Linked Server Fails After Upgrade to SQL Server 2
We had an SQL Server 2000 instance running on Windows 2003. This was
querying a linked AD server running on Window 2000.
The query we run is as follows:
SELECT location FROM OpenQuery(python, 'SELECT location,title, displayName,
sAMAccountName, givenName, telephoneNumber,
facsimileTelephoneNumber,
sn, mail,department, physicalDeliveryOfficeName FROM
''LDAP://DC=mycompany,DC=com''
where sn<''k''
and objectClass = ''User''')
The SQL Server instance was upgraded to SQL Server 2005. No other changes
were made.
We now get an error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADSDSOObject" for linked server "python" reported an
error. The provider did not give any information about the error.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "ADSDSOObject" for
linked server "python".
Any suggestions?
--
Jonathan PalmerJonathan Palmer wrote:
> We had an SQL Server 2000 instance running on Windows 2003. This was
> querying a linked AD server running on Window 2000.
> The query we run is as follows:
> SELECT location FROM OpenQuery(python, 'SELECT location,title, displayName,
> sAMAccountName, givenName, telephoneNumber,
> facsimileTelephoneNumber,
> sn, mail,department, physicalDeliveryOfficeName FROM
> ''LDAP://DC=mycompany,DC=com''
> where sn<''k''
> and objectClass = ''User''')
> The SQL Server instance was upgraded to SQL Server 2005. No other changes
> were made.
> We now get an error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "ADSDSOObject" for linked server "python" reported an
> error. The provider did not give any information about the error.
> Msg 7350, Level 16, State 2, Line 1
> Cannot get the column information from OLE DB provider "ADSDSOObject" for
> linked server "python".
> Any suggestions?
>
Hi
I've just tried you statement on a SQL2005 server, and it works fine for
me. The only thing I've changed, is the part where you define your LDAP
server. Instead of ...''LDAP:/DC=nycompany, DC=com''... i've changed
it to ...''LDAP://DC-1''... where DC-1 is the name of my domain
controller. I don't know if that will solve your problem because I don't
think this should be any different from SQL2000 to SQL2005. Only
specifying the domain controller name has worked for me in SQL2000 as
well... actually I've never got it working with anything else...:-).
--
Regards
Steen Schlüter Persson
DBA|||The error message is the same if I use the FQDN for the server, the server
name alone or DC=.. etc approach.
If I type in the wrong FQDN, I do not get the error message about "cannot
get the column information"|||Jonathan Palmer wrote:
> The error message is the same if I use the FQDN for the server, the server
> name alone or DC=.. etc approach.
> If I type in the wrong FQDN, I do not get the error message about "cannot
> get the column information"
That's a bit strange. Have you tried to remove the linked server and
recreate it?
What happens if you use OPENROWSET like in my example below -Does that
make any difference?
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,'User ID=YourAdministratorAccount;Password=YourPassword;Your Linked
Servername Flag=0x11;Page Size=10000'
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://YourDomainCOntrollerName''
WHERE objectClass = ''organizationalPerson''
'
)
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||try recreate the linked server and rerun sp_addlinkedsrvlogin. SIDs might be
wrong after upgrade.
--
-oj
"Jonathan Palmer" <nospam@.nospamnospam.nospamnospam> wrote in message
news:92564888-4415-4C84-8475-5DA44BFE655C@.microsoft.com...
> The error message is the same if I use the FQDN for the server, the server
> name alone or DC=.. etc approach.
> If I type in the wrong FQDN, I do not get the error message about "cannot
> get the column information"|||If I use OpenRowSet, I do get results, e.g.
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,
''
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://MYSERVER''
WHERE objectClass = ''organizationalPerson''
')
I tried recreating the linked server and running the query:
SELECT * FROM OpenQuery(ADSI, 'SELECT mail
,SamAccountName FROM
''LDAP://myserver'
where
objectClass = ''User''')
This query returns no results if it is made without a security context and
rows if I enter a username and password. How can the first query run without
any loggin details and the second only run with loggin details?
--
Jonathan Palmer
"Steen Persson (DK)" wrote:
> That's a bit strange. Have you tried to remove the linked server and
> recreate it?
> What happens if you use OPENROWSET like in my example below -Does that
> make any difference?
>
> SELECT *
> FROM OPENROWSET(
> 'AdsDsoObject'
> ,'User ID=YourAdministratorAccount;Password=YourPassword;Your Linked
> Servername Flag=0x11;Page Size=10000'
> ,'SELECT mail
> ,ExtensionAttribute3
> ,SamAccountName
> FROM ''LDAP://YourDomainCOntrollerName''
> WHERE objectClass = ''organizationalPerson''
> '
> )
>
> --
> Regards
> Steen Schlüter Persson
> Databaseadministrator / Systemadministrator
>
querying a linked AD server running on Window 2000.
The query we run is as follows:
SELECT location FROM OpenQuery(python, 'SELECT location,title, displayName,
sAMAccountName, givenName, telephoneNumber,
facsimileTelephoneNumber,
sn, mail,department, physicalDeliveryOfficeName FROM
''LDAP://DC=mycompany,DC=com''
where sn<''k''
and objectClass = ''User''')
The SQL Server instance was upgraded to SQL Server 2005. No other changes
were made.
We now get an error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADSDSOObject" for linked server "python" reported an
error. The provider did not give any information about the error.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "ADSDSOObject" for
linked server "python".
Any suggestions?
--
Jonathan PalmerJonathan Palmer wrote:
> We had an SQL Server 2000 instance running on Windows 2003. This was
> querying a linked AD server running on Window 2000.
> The query we run is as follows:
> SELECT location FROM OpenQuery(python, 'SELECT location,title, displayName,
> sAMAccountName, givenName, telephoneNumber,
> facsimileTelephoneNumber,
> sn, mail,department, physicalDeliveryOfficeName FROM
> ''LDAP://DC=mycompany,DC=com''
> where sn<''k''
> and objectClass = ''User''')
> The SQL Server instance was upgraded to SQL Server 2005. No other changes
> were made.
> We now get an error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "ADSDSOObject" for linked server "python" reported an
> error. The provider did not give any information about the error.
> Msg 7350, Level 16, State 2, Line 1
> Cannot get the column information from OLE DB provider "ADSDSOObject" for
> linked server "python".
> Any suggestions?
>
Hi
I've just tried you statement on a SQL2005 server, and it works fine for
me. The only thing I've changed, is the part where you define your LDAP
server. Instead of ...''LDAP:/DC=nycompany, DC=com''... i've changed
it to ...''LDAP://DC-1''... where DC-1 is the name of my domain
controller. I don't know if that will solve your problem because I don't
think this should be any different from SQL2000 to SQL2005. Only
specifying the domain controller name has worked for me in SQL2000 as
well... actually I've never got it working with anything else...:-).
--
Regards
Steen Schlüter Persson
DBA|||The error message is the same if I use the FQDN for the server, the server
name alone or DC=.. etc approach.
If I type in the wrong FQDN, I do not get the error message about "cannot
get the column information"|||Jonathan Palmer wrote:
> The error message is the same if I use the FQDN for the server, the server
> name alone or DC=.. etc approach.
> If I type in the wrong FQDN, I do not get the error message about "cannot
> get the column information"
That's a bit strange. Have you tried to remove the linked server and
recreate it?
What happens if you use OPENROWSET like in my example below -Does that
make any difference?
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,'User ID=YourAdministratorAccount;Password=YourPassword;Your Linked
Servername Flag=0x11;Page Size=10000'
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://YourDomainCOntrollerName''
WHERE objectClass = ''organizationalPerson''
'
)
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||try recreate the linked server and rerun sp_addlinkedsrvlogin. SIDs might be
wrong after upgrade.
--
-oj
"Jonathan Palmer" <nospam@.nospamnospam.nospamnospam> wrote in message
news:92564888-4415-4C84-8475-5DA44BFE655C@.microsoft.com...
> The error message is the same if I use the FQDN for the server, the server
> name alone or DC=.. etc approach.
> If I type in the wrong FQDN, I do not get the error message about "cannot
> get the column information"|||If I use OpenRowSet, I do get results, e.g.
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,
''
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://MYSERVER''
WHERE objectClass = ''organizationalPerson''
')
I tried recreating the linked server and running the query:
SELECT * FROM OpenQuery(ADSI, 'SELECT mail
,SamAccountName FROM
''LDAP://myserver'
where
objectClass = ''User''')
This query returns no results if it is made without a security context and
rows if I enter a username and password. How can the first query run without
any loggin details and the second only run with loggin details?
--
Jonathan Palmer
"Steen Persson (DK)" wrote:
> That's a bit strange. Have you tried to remove the linked server and
> recreate it?
> What happens if you use OPENROWSET like in my example below -Does that
> make any difference?
>
> SELECT *
> FROM OPENROWSET(
> 'AdsDsoObject'
> ,'User ID=YourAdministratorAccount;Password=YourPassword;Your Linked
> Servername Flag=0x11;Page Size=10000'
> ,'SELECT mail
> ,ExtensionAttribute3
> ,SamAccountName
> FROM ''LDAP://YourDomainCOntrollerName''
> WHERE objectClass = ''organizationalPerson''
> '
> )
>
> --
> Regards
> Steen Schlüter Persson
> Databaseadministrator / Systemadministrator
>
Active directory linked server access!
I got a linked server to retrieve information from our Active Directory. Using a stored procedure that accesses the linked server I update a table. The procedure works!
But when I try to run it from a task, it does not work.
Does someone know, which permission has to be configured and where?
Depending on the impersonation defined on the linked server, the service account running SQL Server Agent needs to have access to the Active directory information. Normally this is done by using a domain account as the service account for SQL Server Agent.Jens K. Suessmeyer
http://www.sqlserver2005.de
Active Directory Groups Don't Work with Linked Server?
I mapped a login created with an Active Directory Group on server A to a login on server B through a linked server on server A and received a null login error when attempting to connect.
I changed the Active Directory Group login to an individual active directory login and the connection worked fine.
I saw someone post online somewhere that Active Directory Groups don't work with linked server by design--but I wanted to get confirmation on this. Can anyone confirm this, particularly someone from Microsoft?
This might be the dreaded "Double-hop" scenario. There's a great rundown by a Microsoft Protocols Engineer here:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
That will get you started on learning why this is happening.
Active Directory Groups Don't Work with Linked Server?
I mapped a login created with an Active Directory Group on server A to a login on server B through a linked server on server A and received a null login error when attempting to connect.
I changed the Active Directory Group login to an individual active directory login and the connection worked fine.
I saw someone post online somewhere that Active Directory Groups don't work with linked server by design--but I wanted to get confirmation on this. Can anyone confirm this, particularly someone from Microsoft?
This might be the dreaded "Double-hop" scenario. There's a great rundown by a Microsoft Protocols Engineer here:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
That will get you started on learning why this is happening.
Active Directory as linked Server in SQL
I want to create a view in SQL populated with users from our Active Directory. I have learnt that this can be done using linked server. I have tried using the following:
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
go
sp_addlinkedsrvlogin @.rmtsrvname = 'ADSI', @.useself = 'false', @.locallogin = 'sa', @.rmtuser = 'lok_applications', @.rmtpassword = '9dfFfG374GoiAo6yxxc8oZ'
SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=com"')
I keep getting this error no matter what I try:
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].
Any ideas why?
Hi,
From your descriptions, I understood that you meet the error [Prepare
returned 0x80040e14] when you are using linked server to Active Directory.
Have I understood you? If there is anything I misunderstood, plesae feel
free to let me know
Based on my knowledge, there should be some authority issue with this kind
of error. Would you please have a try on following steps?
1.
try to create a new linked server as follows
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
Will you get the same error?
2.
try to add SQL Server to Active Directory (Right click Server in SQL Server
Enterprise Manager -> properties -> Active Directory)
Could you make it successfully? If not, what kind of error do you meet?
3.
Checking Start-up account of SQL Server.
Step One: typing "services.msc" (without quotation marks) in Start -> Run
Step Two: right click 'MSSQLServer' or 'MSSQLServer$InstanceName',
according to the SQL Server you are using -> Properties -> Log On. If you
are using a Local System Account, change it to Domain Account
try to see whether you could successfully add it and make the query
Hope this helps and please feel free to post in the group if this solves
your problem or if you would like further help. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I have a very similar problem. I have double checked that:
First ran from a Member Server running SQL 2000 sp3
SQL Service is running as a domain user (even a domain admin)
SQL Server is registered in Active Directory
Created the linked server as suggested, and also tried creating using
enterprise manager
Tried various uses of sp_addlinkedsrvlogin as well as the security
dialog of the linked server in EM to make sure I have proper credentials
Tried running from the Domain Controller itself on SQL 7.0
updated MDAC to 2.8
When I run an OpenQuery in QA, I get a message like:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADsDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject'
ICommandPrepare::Prepare returned 0x80040e14
When I try to browse around in EM under the linked server, I get a
message dialog like:
Could not obtain a required interface from OLE DB provider
'ADSDSOObject'. OLE DB error trace[OLE/DB Provider 'ADSDSOObject'
IUnknown::QueryInterface returned 0x80004002: IDBSchemaRowset].
Any ideas? Perhaps I need to change the AD configuration, or I
overlooked something with the authentication?
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Bjork,
I have a nearly identical problem. Did you ever solve your problem, and
if so, do you have any suggestions?
thanks,
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
go
sp_addlinkedsrvlogin @.rmtsrvname = 'ADSI', @.useself = 'false', @.locallogin = 'sa', @.rmtuser = 'lok_applications', @.rmtpassword = '9dfFfG374GoiAo6yxxc8oZ'
SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=com"')
I keep getting this error no matter what I try:
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].
Any ideas why?
Hi,
From your descriptions, I understood that you meet the error [Prepare
returned 0x80040e14] when you are using linked server to Active Directory.
Have I understood you? If there is anything I misunderstood, plesae feel
free to let me know
Based on my knowledge, there should be some authority issue with this kind
of error. Would you please have a try on following steps?
1.
try to create a new linked server as follows
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
Will you get the same error?
2.
try to add SQL Server to Active Directory (Right click Server in SQL Server
Enterprise Manager -> properties -> Active Directory)
Could you make it successfully? If not, what kind of error do you meet?
3.
Checking Start-up account of SQL Server.
Step One: typing "services.msc" (without quotation marks) in Start -> Run
Step Two: right click 'MSSQLServer' or 'MSSQLServer$InstanceName',
according to the SQL Server you are using -> Properties -> Log On. If you
are using a Local System Account, change it to Domain Account
try to see whether you could successfully add it and make the query
Hope this helps and please feel free to post in the group if this solves
your problem or if you would like further help. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I have a very similar problem. I have double checked that:
First ran from a Member Server running SQL 2000 sp3
SQL Service is running as a domain user (even a domain admin)
SQL Server is registered in Active Directory
Created the linked server as suggested, and also tried creating using
enterprise manager
Tried various uses of sp_addlinkedsrvlogin as well as the security
dialog of the linked server in EM to make sure I have proper credentials
Tried running from the Domain Controller itself on SQL 7.0
updated MDAC to 2.8
When I run an OpenQuery in QA, I get a message like:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADsDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject'
ICommandPrepare::Prepare returned 0x80040e14
When I try to browse around in EM under the linked server, I get a
message dialog like:
Could not obtain a required interface from OLE DB provider
'ADSDSOObject'. OLE DB error trace[OLE/DB Provider 'ADSDSOObject'
IUnknown::QueryInterface returned 0x80004002: IDBSchemaRowset].
Any ideas? Perhaps I need to change the AD configuration, or I
overlooked something with the authentication?
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Bjork,
I have a nearly identical problem. Did you ever solve your problem, and
if so, do you have any suggestions?
thanks,
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Active Directory as linked Server in SQL
I want to create a view in SQL populated with users from our Active Directory. I have learnt that this can be done using linked server. I have tried using the following
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource
g
sp_addlinkedsrvlogin @.rmtsrvname = 'ADSI', @.useself = 'false', @.locallogin = 'sa', @.rmtuser = 'lok_applications', @.rmtpassword = '9dfFfG374GoiAo6yxxc8oZ'
SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=com"'
I keep getting this error no matter what I try
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].
Any ideas why?Hi,
From your descriptions, I understood that you meet the error [Prepare
returned 0x80040e14] when you are using linked server to Active Directory.
Have I understood you? If there is anything I misunderstood, plesae feel
free to let me know :)
Based on my knowledge, there should be some authority issue with this kind
of error. Would you please have a try on following steps?
1.
try to create a new linked server as follows
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
Will you get the same error?
2.
try to add SQL Server to Active Directory (Right click Server in SQL Server
Enterprise Manager -> properties -> Active Directory)
Could you make it successfully? If not, what kind of error do you meet?
3.
Checking Start-up account of SQL Server.
Step One: typing "services.msc" (without quotation marks) in Start -> Run
Step Two: right click 'MSSQLServer' or 'MSSQLServer$InstanceName',
according to the SQL Server you are using -> Properties -> Log On. If you
are using a Local System Account, change it to Domain Account
try to see whether you could successfully add it and make the query :)
Hope this helps and please feel free to post in the group if this solves
your problem or if you would like further help. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Bjork,
I have a nearly identical problem. Did you ever solve your problem, and
if so, do you have any suggestions?
thanks,
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource
g
sp_addlinkedsrvlogin @.rmtsrvname = 'ADSI', @.useself = 'false', @.locallogin = 'sa', @.rmtuser = 'lok_applications', @.rmtpassword = '9dfFfG374GoiAo6yxxc8oZ'
SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=com"'
I keep getting this error no matter what I try
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].
Any ideas why?Hi,
From your descriptions, I understood that you meet the error [Prepare
returned 0x80040e14] when you are using linked server to Active Directory.
Have I understood you? If there is anything I misunderstood, plesae feel
free to let me know :)
Based on my knowledge, there should be some authority issue with this kind
of error. Would you please have a try on following steps?
1.
try to create a new linked server as follows
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
Will you get the same error?
2.
try to add SQL Server to Active Directory (Right click Server in SQL Server
Enterprise Manager -> properties -> Active Directory)
Could you make it successfully? If not, what kind of error do you meet?
3.
Checking Start-up account of SQL Server.
Step One: typing "services.msc" (without quotation marks) in Start -> Run
Step Two: right click 'MSSQLServer' or 'MSSQLServer$InstanceName',
according to the SQL Server you are using -> Properties -> Log On. If you
are using a Local System Account, change it to Domain Account
try to see whether you could successfully add it and make the query :)
Hope this helps and please feel free to post in the group if this solves
your problem or if you would like further help. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Bjork,
I have a nearly identical problem. Did you ever solve your problem, and
if so, do you have any suggestions?
thanks,
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Active Directory as linked Server in SQL
I want to create a view in SQL populated with users from our Active Director
y. I have learnt that this can be done using linked server. I have tried usi
ng the following:
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject',
'adsdatasource'
go
sp_addlinkedsrvlogin @.rmtsrvname = 'ADSI', @.useself = 'false', @.locallog
in = 'sa', @.rmtuser = 'lok_applications', @.rmtpassword = '9dfFfG374GoiA
o6yxxc8oZ'
SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=com"')
I keep getting this error no matter what I try:
An error occurred while preparing a query for execution against OLE DB provi
der 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are returned 0x80040e14].
Any ideas why'Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi,
I have a very similar problem. I have double checked that:
First ran from a Member Server running SQL 2000 sp3
SQL Service is running as a domain user (even a domain admin)
SQL Server is registered in Active Directory
Created the linked server as suggested, and also tried creating using
enterprise manager
Tried various uses of sp_addlinkedsrvlogin as well as the security
dialog of the linked server in EM to make sure I have proper credentials
Tried running from the Domain Controller itself on SQL 7.0
updated MDAC to 2.8
When I run an OpenQuery in QA, I get a message like:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADsDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject'
ICommandPrepare::Prepare returned 0x80040e14
When I try to browse around in EM under the linked server, I get a
message dialog like:
Could not obtain a required interface from OLE DB provider
'ADSDSOObject'. OLE DB error trace[OLE/DB Provider 'ADSDSOObject'
IUnknown::QueryInterface returned 0x80004002: IDBSchemaRowset].
Any ideas? Perhaps I need to change the AD configuration, or I
overlooked something with the authentication?
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Bjork,
I have a nearly identical problem. Did you ever solve your problem, and
if so, do you have any suggestions?
thanks,
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
y. I have learnt that this can be done using linked server. I have tried usi
ng the following:
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject',
'adsdatasource'
go
sp_addlinkedsrvlogin @.rmtsrvname = 'ADSI', @.useself = 'false', @.locallog
in = 'sa', @.rmtuser = 'lok_applications', @.rmtpassword = '9dfFfG374GoiA
o6yxxc8oZ'
SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=com"')
I keep getting this error no matter what I try:
An error occurred while preparing a query for execution against OLE DB provi
der 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are returned 0x80040e14].
Any ideas why'Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi,
I have a very similar problem. I have double checked that:
First ran from a Member Server running SQL 2000 sp3
SQL Service is running as a domain user (even a domain admin)
SQL Server is registered in Active Directory
Created the linked server as suggested, and also tried creating using
enterprise manager
Tried various uses of sp_addlinkedsrvlogin as well as the security
dialog of the linked server in EM to make sure I have proper credentials
Tried running from the Domain Controller itself on SQL 7.0
updated MDAC to 2.8
When I run an OpenQuery in QA, I get a message like:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADsDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject'
ICommandPrepare::Prepare returned 0x80040e14
When I try to browse around in EM under the linked server, I get a
message dialog like:
Could not obtain a required interface from OLE DB provider
'ADSDSOObject'. OLE DB error trace[OLE/DB Provider 'ADSDSOObject'
IUnknown::QueryInterface returned 0x80004002: IDBSchemaRowset].
Any ideas? Perhaps I need to change the AD configuration, or I
overlooked something with the authentication?
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Bjork,
I have a nearly identical problem. Did you ever solve your problem, and
if so, do you have any suggestions?
thanks,
Dave
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Active Directory as Linked Server no data being returned
We're having problems establishing a Linked Server that points at
Active Directory in SQL Server 2000. We've executed
sp_addlinkedserver and have created a view of active directory users.
However, when querying the view, no data is returned. Are we maybe
not connecting correctly? We are not receiving any error messages.
Any help would be appreciated.
Here is how we created our view:
CREATE VIEW AD_USERS AS
SELECT * FROM OpenQuery(
ADSI,'<LDAP://DC=domain,DC=com>;(& (objectCategory=Person)(objectClass=user
))
;sn,
name, adspath,title;subtree')
(except domain is replaced with our domain name)
Thanx in advance for any help!!Try querying the same OU etc. from a command line tool to make sure that
the query is returning data.
The command line tool is a Windows tools called ldifde.
Example:
ldifde -d "CN=Users,DC=<domain>" -l servicePrincipalName -F
<NewoutputUsers>.txt This syntax creates a file named NewoutputUsers.txt
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We get data returned with the command line tool when
replacing <domain> in your example with our domain name.
What do we need to change in our OpenQuery statement to
get results with SQL Server?
to make sure that
servicePrincipalName -F
named NewoutputUsers.txt
confers no rights.
Directory:
CREATE VIEW AD_USERS AS
SELECT *
FROM OPENQUERY( ADSI,
'SELECT *
FROM ''LDAP://CN=users,DC=<domain>,DC=com''')
When I query the AD_USERS view using query analyzer on
a client workstation running Enterprise Manager, there
are errors produced. When I query the same view using
query analyzer on the SQL Server server, no errors are
produced. However, even though I am selecting * from the
view, the only field to come back is adsPath. When I try
to specify another field name in the query that creates
the view, I receive the following error:
Could not get the data of the row from the OLE DB
provider 'ADSDSOObject'. Could not convert the data value
due to reasons other than sign mismatch or overflow.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
IRowset::GetData returned 0x80040e21: Data status
returned from the provider: [COLUMN_NAME=cn
STATUS=DBSTATUS_E_CANTCONVERTVALUE]].
Is there something that needs to be changed in the query?
Help, please.
to make sure that
servicePrincipalName -F
named NewoutputUsers.txt
confers no rights.
Active Directory in SQL Server 2000. We've executed
sp_addlinkedserver and have created a view of active directory users.
However, when querying the view, no data is returned. Are we maybe
not connecting correctly? We are not receiving any error messages.
Any help would be appreciated.
Here is how we created our view:
CREATE VIEW AD_USERS AS
SELECT * FROM OpenQuery(
ADSI,'<LDAP://DC=domain,DC=com>;(& (objectCategory=Person)(objectClass=user
))
;sn,
name, adspath,title;subtree')
(except domain is replaced with our domain name)
Thanx in advance for any help!!Try querying the same OU etc. from a command line tool to make sure that
the query is returning data.
The command line tool is a Windows tools called ldifde.
Example:
ldifde -d "CN=Users,DC=<domain>" -l servicePrincipalName -F
<NewoutputUsers>.txt This syntax creates a file named NewoutputUsers.txt
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We get data returned with the command line tool when
replacing <domain> in your example with our domain name.
What do we need to change in our OpenQuery statement to
get results with SQL Server?
quote:
>--Original Message--
>Try querying the same OU etc. from a command line tool
to make sure that
quote:
>the query is returning data.
>The command line tool is a Windows tools called ldifde.
>Example:
>ldifde -d "CN=Users,DC=<domain>" -l
servicePrincipalName -F
quote:
> <NewoutputUsers>.txt This syntax creates a file
named NewoutputUsers.txt
quote:
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
quote:|||So, I tried to create a view to use for querying Active
>
>.
>
Directory:
CREATE VIEW AD_USERS AS
SELECT *
FROM OPENQUERY( ADSI,
'SELECT *
FROM ''LDAP://CN=users,DC=<domain>,DC=com''')
When I query the AD_USERS view using query analyzer on
a client workstation running Enterprise Manager, there
are errors produced. When I query the same view using
query analyzer on the SQL Server server, no errors are
produced. However, even though I am selecting * from the
view, the only field to come back is adsPath. When I try
to specify another field name in the query that creates
the view, I receive the following error:
Could not get the data of the row from the OLE DB
provider 'ADSDSOObject'. Could not convert the data value
due to reasons other than sign mismatch or overflow.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
IRowset::GetData returned 0x80040e21: Data status
returned from the provider: [COLUMN_NAME=cn
STATUS=DBSTATUS_E_CANTCONVERTVALUE]].
Is there something that needs to be changed in the query?
Help, please.
quote:
>--Original Message--
>Try querying the same OU etc. from a command line tool
to make sure that
quote:
>the query is returning data.
>The command line tool is a Windows tools called ldifde.
>Example:
>ldifde -d "CN=Users,DC=<domain>" -l
servicePrincipalName -F
quote:
> <NewoutputUsers>.txt This syntax creates a file
named NewoutputUsers.txt
quote:
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
quote:
>
>.
>
Thursday, March 8, 2012
Action jumping to wrong report
Hello all,
I have a set of master reports that have linked copies in different
directories based on which customer is logging in to view them. Many of
the fields in these reports have actions to jump to other reports. The
problem I'm running into is that the action is jumping to the report in
the master directory (which has open customer queries) instead of the
one in the customer directory which has a default.
Kind of confusing, so here's an example:
Master reports 1 and 2 in directory /master have a default value of %
for customer and don't prompt for that parameter
Linked reports 1 and 2 in directory /123 have a default value of 123
for customer and don't prompt for that parameter
Report 1 jumps to report 2 when they click on a certain field, but if
it jumps to the report in /master it will query data for all customers.
I want it to jump to the linked report in /123 so it will only query
data for that customer.
If I try to pass in the customer field it gives an error since that
field isn't prompted for. Obviously prompting for the field will defeat
the security. Is there any way to force it to jump to the linked report
in the current directory instead of the one in the directory being
linked from?
Thanks!
JonAfter playing around with the "jump to URL" option I answered my own
question.
=Globals!ReportServerUrl & "?" & Globals.ReportFolder &
"/Report+2&Customer=" Fields!customer.value
Just in case it helps anyone else with a similar question...
Thanks,
Jon
I have a set of master reports that have linked copies in different
directories based on which customer is logging in to view them. Many of
the fields in these reports have actions to jump to other reports. The
problem I'm running into is that the action is jumping to the report in
the master directory (which has open customer queries) instead of the
one in the customer directory which has a default.
Kind of confusing, so here's an example:
Master reports 1 and 2 in directory /master have a default value of %
for customer and don't prompt for that parameter
Linked reports 1 and 2 in directory /123 have a default value of 123
for customer and don't prompt for that parameter
Report 1 jumps to report 2 when they click on a certain field, but if
it jumps to the report in /master it will query data for all customers.
I want it to jump to the linked report in /123 so it will only query
data for that customer.
If I try to pass in the customer field it gives an error since that
field isn't prompted for. Obviously prompting for the field will defeat
the security. Is there any way to force it to jump to the linked report
in the current directory instead of the one in the directory being
linked from?
Thanks!
JonAfter playing around with the "jump to URL" option I answered my own
question.
=Globals!ReportServerUrl & "?" & Globals.ReportFolder &
"/Report+2&Customer=" Fields!customer.value
Just in case it helps anyone else with a similar question...
Thanks,
Jon
Friday, February 24, 2012
accesssing mirrored databases via SQLNCLI linked server?
Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance? It is quit possible that the connection to the primary take long to fail before making second connection to the secondary. The default timeout value is 15 seconds for a connection. You can try extend the timeout value or you can upgrade to SP1 which has better retry logic that can deal with timeout better.
Say I have a mirrored database on two servers: PRIMARY and SECONDARY.
I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.
This is what I'm using:
EXEC master.dbo.sp_addlinkedserver
@.server = N'MIRROR',
@.srvproduct=N'',
@.provider=N'SQLNCLI',
@.provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'
select count (*) from mirror.pubs.dbo.authors
and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
As far as I can tell, it doesn't try to contact SECONDARY at all. It seems like SQL Server is ignoring the FailoverPartner attribute.
If I switch PRIMARY and SECONDARY in the connection string (ie @.provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.
Any ideas?
Piers.
I was able to reproduce this problem and we're investigating further. Will let you know the results.
|||great! I'd love to know if there's a workaround or patch available.|||
From the documentation at: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dataacc9/html/71b15712-7972-4465-9274-e0ddc271eedc.htm
You must explicitly specify the database to be used by a connection if you want to use this feature in a DSN, connection string, or connection property/attribute. SQL Native Client will not attempt to failover to the partner database if this is not done.
Mirroring is a feature of the database. Applications that use multiple databases might not be able to exploit this feature.
In addition, server names are case insensitive, but database names are case sensitive. You should therefore make sure that you use the same casing in DSNs and connection strings.
So basically you should specify the Database name to which to connect to either in the connection string or through @.catalog=<Database Name> parameter to sp_addlinkedserver. I tried doing that and the repro started working.
Thanks
Waseem
accesssing mirrored databases via SQLNCLI linked server?
Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance?
Say I have a mirrored database on two servers: PRIMARY and SECONDARY.
I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.
This is what I'm using:
EXEC master.dbo.sp_addlinkedserver
@.server = N'MIRROR',
@.srvproduct=N'',
@.provider=N'SQLNCLI',
@.provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'
select count (*) from mirror.pubs.dbo.authors
and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
As far as I can tell, it doesn't try to contact SECONDARY at all. If I switch PRIMARY and SECONDARY in the connection string (ie @.provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.
Any ideas?
Piers.
It is quit possible that the connection to the primary take long to fail before making second connection to the secondary. The default timeout value is 15 seconds for a connection. You can try extend the timeout value or you can upgrade to SP1 which has better retry logic that can deal with timeout better.|||All 3 servers are running 9.0.2047sp1/x64 on WS2k3sp1/x64.
I've also tried extending the connection timeout with
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'connect timeout', @.optvalue=N'120'
but I still get the same error after ~20 seconds (regardless of the value I specify). I've looked at the network traffic and it never seems to attempt to establish a connection to the secondary.
here's the entire script I'm using:
EXEC master.dbo.sp_dropserver @.server= N'MIRROR'
EXEC master.dbo.sp_addlinkedserver
@.server = N'MIRROR',
@.srvproduct=N'',
@.provider=N'SQLOLEDB',
@.provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'
GOEXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'collation compatible', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'dist', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'pub', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'rpc', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'rpc out', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'sub', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'connect timeout', @.optvalue=N'1000'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'lazy schema validation', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'use remote collation', @.optvalue=N'true'select top 10 * from MIRROR.pubs.dbo.authors
What am I missing?
Piers.
Accessing Users & Members in Active Directory via Linked Server
Hi,
I'm trying to extract all the users and their membership to groups, and the membership of groups to groups from active directory though a link to server.
I can get the users. I can get the groups.... individually.
I can't get the info of what user is a member of or who are members of a group.
Anyone know how to do this or am I going to have to right a vb app? (Anyone already got the code...)
I want to load this data into tables for reporting in my Data Warehouse.
Cheers
Chris
Take a look at the following API:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netmgmt/netmgmt/netgroupgetusers.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netmgmt/netmgmt/netlocalgroupgetmembers.asp
Follow the links from the bottom of the articles for other related API.
Hope this helps.
Laurentiu
Accessing UDF on a linked server
Hi all-
I need to invoke a UDF on a linked server. Is this possible?
eg:
I need to execute the following query from Server1
select *
from Server2.Database.owner.fnGetTree() AS Server2Tree
inner join Database.owner.fnGetTree() AS Server1Tree
on Server1Tree.NodeId = Server2Tree.NodeId
Thanks for your timeNever mind. I got it. I could do this using OPNEQUERY
I need to invoke a UDF on a linked server. Is this possible?
eg:
I need to execute the following query from Server1
select *
from Server2.Database.owner.fnGetTree() AS Server2Tree
inner join Database.owner.fnGetTree() AS Server1Tree
on Server1Tree.NodeId = Server2Tree.NodeId
Thanks for your timeNever mind. I got it. I could do this using OPNEQUERY
accessing to an oracle 10 linked server
Hello,
i try to make some select queries using a linked oracle 10 server but for a
few tables, only the first row is returned...
i haved used "Microsoft OLE provider for OCBC driver" when declaring my
linked server and my odbc link is using the lastest Oracle odbc driver
(10.1.3.1).
Using another odbc driver (microsoft odbc for oracle) instead of oracle one
make it works great on query analyser but when using it on my programs (who
are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
returning me "Impossible to start other transactions for this session".
Any clue?
Thx
Not sure but you'd definitely want to try using the OLE DB
provider instead. You add extra overhead when using the OLE
DB provider for ODBC. The OLE DB providers generally provide
more functionality as well.
-Sue
On Tue, 15 Mar 2005 07:23:03 -0800, Cdric Prin-Derre
<Cdric Prin-Derre@.discussions.microsoft.com> wrote:
>Hello,
>i try to make some select queries using a linked oracle 10 server but for a
>few tables, only the first row is returned...
>i haved used "Microsoft OLE provider for OCBC driver" when declaring my
>linked server and my odbc link is using the lastest Oracle odbc driver
>(10.1.3.1).
>Using another odbc driver (microsoft odbc for oracle) instead of oracle one
>make it works great on query analyser but when using it on my programs (who
>are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
>returning me "Impossible to start other transactions for this session".
>
>Any clue?
>Thx
i try to make some select queries using a linked oracle 10 server but for a
few tables, only the first row is returned...
i haved used "Microsoft OLE provider for OCBC driver" when declaring my
linked server and my odbc link is using the lastest Oracle odbc driver
(10.1.3.1).
Using another odbc driver (microsoft odbc for oracle) instead of oracle one
make it works great on query analyser but when using it on my programs (who
are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
returning me "Impossible to start other transactions for this session".
Any clue?
Thx
Not sure but you'd definitely want to try using the OLE DB
provider instead. You add extra overhead when using the OLE
DB provider for ODBC. The OLE DB providers generally provide
more functionality as well.
-Sue
On Tue, 15 Mar 2005 07:23:03 -0800, Cdric Prin-Derre
<Cdric Prin-Derre@.discussions.microsoft.com> wrote:
>Hello,
>i try to make some select queries using a linked oracle 10 server but for a
>few tables, only the first row is returned...
>i haved used "Microsoft OLE provider for OCBC driver" when declaring my
>linked server and my odbc link is using the lastest Oracle odbc driver
>(10.1.3.1).
>Using another odbc driver (microsoft odbc for oracle) instead of oracle one
>make it works great on query analyser but when using it on my programs (who
>are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
>returning me "Impossible to start other transactions for this session".
>
>Any clue?
>Thx
accessing to an oracle 10 linked server
Hello,
i try to make some select queries using a linked oracle 10 server but for a
few tables, only the first row is returned...
i haved used "Microsoft OLE provider for OCBC driver" when declaring my
linked server and my odbc link is using the lastest Oracle odbc driver
(10.1.3.1).
Using another odbc driver (microsoft odbc for oracle) instead of oracle one
make it works great on query analyser but when using it on my programs (who
are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
returning me "Impossible to start other transactions for this session".
Any clue?
ThxNot sure but you'd definitely want to try using the OLE DB
provider instead. You add extra overhead when using the OLE
DB provider for ODBC. The OLE DB providers generally provide
more functionality as well.
-Sue
On Tue, 15 Mar 2005 07:23:03 -0800, Cdric Prin-Derre
<Cdric Prin-Derre@.discussions.microsoft.com> wrote:
>Hello,
>i try to make some select queries using a linked oracle 10 server but for a
>few tables, only the first row is returned...
>i haved used "Microsoft OLE provider for OCBC driver" when declaring my
>linked server and my odbc link is using the lastest Oracle odbc driver
>(10.1.3.1).
>Using another odbc driver (microsoft odbc for oracle) instead of oracle one
>make it works great on query analyser but when using it on my programs (who
>are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
>returning me "Impossible to start other transactions for this session".
>
>Any clue?
>Thx
i try to make some select queries using a linked oracle 10 server but for a
few tables, only the first row is returned...
i haved used "Microsoft OLE provider for OCBC driver" when declaring my
linked server and my odbc link is using the lastest Oracle odbc driver
(10.1.3.1).
Using another odbc driver (microsoft odbc for oracle) instead of oracle one
make it works great on query analyser but when using it on my programs (who
are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
returning me "Impossible to start other transactions for this session".
Any clue?
ThxNot sure but you'd definitely want to try using the OLE DB
provider instead. You add extra overhead when using the OLE
DB provider for ODBC. The OLE DB providers generally provide
more functionality as well.
-Sue
On Tue, 15 Mar 2005 07:23:03 -0800, Cdric Prin-Derre
<Cdric Prin-Derre@.discussions.microsoft.com> wrote:
>Hello,
>i try to make some select queries using a linked oracle 10 server but for a
>few tables, only the first row is returned...
>i haved used "Microsoft OLE provider for OCBC driver" when declaring my
>linked server and my odbc link is using the lastest Oracle odbc driver
>(10.1.3.1).
>Using another odbc driver (microsoft odbc for oracle) instead of oracle one
>make it works great on query analyser but when using it on my programs (who
>are connecting to SQLSERVER 2000 using odbc SQLSERVER), linked request fail
>returning me "Impossible to start other transactions for this session".
>
>Any clue?
>Thx
Sunday, February 19, 2012
Accessing System Views via linked servers
Is there anyway to access system views on/from a linked server?
I have unsuccessfully tried various permutations of
select *
from [MDEDATAWTD\ss2005].master.[information_schema.colums]
ThanksYou are making the table and column name appear as one object. This works fine for me:
SELECT *
FROM [MyServer].master.information_schema.columns
2005 also. High priveledge linked account.
HTH|||You are making the table and column name appear as one object.Not very helpful huh?
I mean with this:
...[information_schema.colums]|||Thanks.
It is not working for me even though I can access the data bases directly. I am going to try a different tack. This approach is chewing too much time.|||Ok - but if you do decide to stick with it check that your linked server maps to an account with the correct privledges. Remember - your domain account could be locally mapped to a remote SQL account that has insufficient rights.
I suppose we'd need to know the error too.
Anyway - just a thought :)|||Thanks for the additional insight.
I also discovered that I really didn't want the system tables from a "linked server" but the system views from a database on the "linked server". Either way I could not get it to work. As I said the benefit gained by procedurally scripting the change looks to be longer than just manually doing the change.
FYI: The error was of the "object does not exist" variety.|||Awww, c'mon Bartron...don't give up now! :) You'll need the linked server experience somewhere down the line!
It's very common for the "object does not exist" error to be due to permissions issues as previously pointed out.
I have unsuccessfully tried various permutations of
select *
from [MDEDATAWTD\ss2005].master.[information_schema.colums]
ThanksYou are making the table and column name appear as one object. This works fine for me:
SELECT *
FROM [MyServer].master.information_schema.columns
2005 also. High priveledge linked account.
HTH|||You are making the table and column name appear as one object.Not very helpful huh?
I mean with this:
...[information_schema.colums]|||Thanks.
It is not working for me even though I can access the data bases directly. I am going to try a different tack. This approach is chewing too much time.|||Ok - but if you do decide to stick with it check that your linked server maps to an account with the correct privledges. Remember - your domain account could be locally mapped to a remote SQL account that has insufficient rights.
I suppose we'd need to know the error too.
Anyway - just a thought :)|||Thanks for the additional insight.
I also discovered that I really didn't want the system tables from a "linked server" but the system views from a database on the "linked server". Either way I could not get it to work. As I said the benefit gained by procedurally scripting the change looks to be longer than just manually doing the change.
FYI: The error was of the "object does not exist" variety.|||Awww, c'mon Bartron...don't give up now! :) You'll need the linked server experience somewhere down the line!
It's very common for the "object does not exist" error to be due to permissions issues as previously pointed out.
Monday, February 13, 2012
Accessing SQL data from an ACCESS app...
I have just imported into SQL server all of my Access 2000 tables. In my
front end Access app, I now am using linked tables to access the data in the
SQL server db.
The speed isn't the greatest.
Is there a better connection type I can use in Access - other than ODBC that
provides better speed?
Thanks,
Brad"Brad Pears" <donotreply@.notreal.com> wrote in message
news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> I have just imported into SQL server all of my Access 2000 tables. In my
> front end Access app, I now am using linked tables to access the data in
the
> SQL server db.
> The speed isn't the greatest.
> Is there a better connection type I can use in Access - other than ODBC
that
> provides better speed?
> Thanks,
> Brad
The speed problem isn't ODBC - it's your code. You've only just begun the
journey of porting an Access app to SQL Server. For instance, you open a
recordset in code on two large tables using a JOIN. You only want to add one
record. But Access will fetch all records from both tables across the
network (locking them in the process), do the JOIN, move to the end, and
insert the field, then send it all back. If you turn on ODBC tracing and
excute a query, you'll see what I'm talking about (this will make it
*really* crawl - don't do it for long!). You need to do a lot of optimizing
and change many of your queries to SQL Pass-Throgh. I recommend you get a
good book - my favorite is Microsoft Access Developer's Guide to SQL Server
by Chipman and Baron, SAMS Publishing. Good luck!|||What is the difference /benefits of a pass-through query and any other SQL
query? I have never used a pass through query before..
Thanks,
Brad
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> the
> that
> The speed problem isn't ODBC - it's your code. You've only just begun the
> journey of porting an Access app to SQL Server. For instance, you open a
> recordset in code on two large tables using a JOIN. You only want to add
one
> record. But Access will fetch all records from both tables across the
> network (locking them in the process), do the JOIN, move to the end, and
> insert the field, then send it all back. If you turn on ODBC tracing and
> excute a query, you'll see what I'm talking about (this will make it
> *really* crawl - don't do it for long!). You need to do a lot of
optimizing
> and change many of your queries to SQL Pass-Throgh. I recommend you get a
> good book - my favorite is Microsoft Access Developer's Guide to SQL
Server
> by Chipman and Baron, SAMS Publishing. Good luck!
>|||A pass-through query passes the SQL statement to the server so that the
entire statement gets processed there, rather than returning lots of raw
data so that Access can process the query at the client computer. I'm
pretty sure you can find more information in the Access or SQL Server help.
I recently had surprisingly good results after upsizing an Access 2000
database to SQL Server, using only ODBC linked tables to SQL server 2000.
My users can now use the application at an acceptable speed from remote
high-speed VPN connected locations. Access Front-End/Back-end could never
have done that.
One thing I've done a lot of in Access is to write VBA functions to use in
Query criteria so I could fill in parameters in code without using SendKeys
to fill in parameter prompts. I use this method for Forms and Reports, and
I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
driver broke-down my queries so they were sent to SQL server with literal
parameters, and I got back only the records I was looking for. You can use
SQL Server Profiler to see the SQL statements that get sent to your server
from Access - that can give you a lot of insight into what's going on in you
app.
I'm pretty sure filters still get applied locally, so you don't want to rely
on those to be your initial data filters on large recordsets.
Another way to bring improved performance would be to use and Access data
project file (.ADP), which provides a more true Client/Server application.
I thought I was going to have to go that route so that my application would
work for remote high-speed VPN users, but my app worked so well with ODBC
linked tables that I didn't have to go to the work of largely re-doing my
application.
Another book set I would recommend is "Access Developer's Handbook Set" by
Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> one
> optimizing
> Server
>|||You really should only be calling stored procedures from pass-through
queries. In addition, you shouldn't have linked tables at all if you want a
really scalable enterprise application. Use the VBA recordset and
passthrough queries calling stored procedures. Let the database server
perform the database work most efficiently using stored procedures.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
my[vbcol=seagreen]
in[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
> one
> optimizing
a[vbcol=seagreen]
> Server
>|||So, you are saying do not use linked tables at all. Could you give me a
snippet of Access code that opens an SQL Server Db and calls a stored
procedure to do something simple such as select * from a table?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
> a
>|||That's great information...
When you are referring to writing an Access query that uses a VBA function
for it's criteria to fill in the parameters (I've never even used SendKeys
to fill in parameter prompts before), are you referring to replacing things
like "[Enter Customer Name]" as a row criteria with a function such as
GetCustName()' where function GetCustName() would display a screen where
the user enters the customers name they are looking for and then you set
GetCustName = txtCustName?
Also what is the ADP you referred to? Is this a reference you add in to the
project? Never even heard of that one!!
Thanks,
Brad
That sounds interesting and is likely something I'd want to do...
"Dynamo" <noone@.nowhere.com> wrote in message
news:uPsFHJPuEHA.3152@.TK2MSFTNGP14.phx.gbl...
> A pass-through query passes the SQL statement to the server so that the
> entire statement gets processed there, rather than returning lots of raw
> data so that Access can process the query at the client computer. I'm
> pretty sure you can find more information in the Access or SQL Server
help.
> I recently had surprisingly good results after upsizing an Access 2000
> database to SQL Server, using only ODBC linked tables to SQL server 2000.
> My users can now use the application at an acceptable speed from remote
> high-speed VPN connected locations. Access Front-End/Back-end could never
> have done that.
> One thing I've done a lot of in Access is to write VBA functions to use in
> Query criteria so I could fill in parameters in code without using
SendKeys
> to fill in parameter prompts. I use this method for Forms and Reports,
and
> I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
> driver broke-down my queries so they were sent to SQL server with literal
> parameters, and I got back only the records I was looking for. You can
use
> SQL Server Profiler to see the SQL statements that get sent to your server
> from Access - that can give you a lot of insight into what's going on in
you
> app.
> I'm pretty sure filters still get applied locally, so you don't want to
rely
> on those to be your initial data filters on large recordsets.
> Another way to bring improved performance would be to use and Access data
> project file (.ADP), which provides a more true Client/Server application.
> I thought I was going to have to go that route so that my application
would
> work for remote high-speed VPN users, but my app worked so well with ODBC
> linked tables that I didn't have to go to the work of largely re-doing my
> application.
> Another book set I would recommend is "Access Developer's Handbook Set" by
> Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
a[vbcol=seagreen]
>|||Can you also give me a simple example (and code) of a passthrough SQL query
calling a stored procedure at the SQL Database level?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
> a
>|||"Brad Pears" <donotreply@.notreal.com> wrote in message
news:eHmgt0RvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Can you also give me a simple example (and code) of a passthrough SQL
query
> calling a stored procedure at the SQL Database level?
> Thanks,
> Brad
I have a sub called SQLExecute that I use for Pass-Throughs. Here it is:
Public Sub SQLExecute(SQL As String, Optional rs As Variant)
'This function creates a SQL Pass Through query that optionaly returns
records
On Error GoTo Error_SQLExecute
Dim qdf As QueryDef
Dim errAny As error
Set qdf = DBEngine(0)(0).CreateQueryDef("")
qdf.Connect = gstrODBC
qdf.ODBCTimeout = 0
qdf.SQL = SQL
If IsMissing(rs) Then
qdf.ReturnsRecords = False
qdf.Execute
Else
qdf.ReturnsRecords = True
qdf.MaxRecords = 2147483647
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
End If
Exit_SQLExecute:
Set qdf = Nothing
Exit Sub
Error_SQLExecute:
For Each errAny In DBEngine.Errors
msgbox "Error " & errAny.Number & " from " & errAny.source & " = " &
errAny.Description, vbCritical, "Error " & errAny.Number & " - SQLExecute"
Next
Resume Exit_SQLExecute
End Sub
In this sub, what makes it a Pass-Trhough is setting the QueryDef's Connect
property. In my case, the Connect string is set on app open to a global
variable called "gstrODBC". To get a value to sote in gstrODBC, open the
Debug window in your Access app (Ctrl-G) and type this followed by the Enter
key:
?CurrentDb.TableDefs("any_linked_table_name").Connect
The returned string should be used as the Connect property for the query
def. For the SQL parameter to SQLExecute, use any valid SQL statement or
query, including stored procedures with parameters. The one caveat is it
must be SQL syntax that the *server* understands, *not* Access's SQL syntax.
The error handler will return messages from the SQL Server if there is a
syntax error, etc.
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
want[vbcol=seagreen]
> a
> SQL
> In
> data
begun[vbcol=seagreen]
open[vbcol=seagreen]
> a
> add
the[vbcol=seagreen]
> and
> and
> get
>
front end Access app, I now am using linked tables to access the data in the
SQL server db.
The speed isn't the greatest.
Is there a better connection type I can use in Access - other than ODBC that
provides better speed?
Thanks,
Brad"Brad Pears" <donotreply@.notreal.com> wrote in message
news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> I have just imported into SQL server all of my Access 2000 tables. In my
> front end Access app, I now am using linked tables to access the data in
the
> SQL server db.
> The speed isn't the greatest.
> Is there a better connection type I can use in Access - other than ODBC
that
> provides better speed?
> Thanks,
> Brad
The speed problem isn't ODBC - it's your code. You've only just begun the
journey of porting an Access app to SQL Server. For instance, you open a
recordset in code on two large tables using a JOIN. You only want to add one
record. But Access will fetch all records from both tables across the
network (locking them in the process), do the JOIN, move to the end, and
insert the field, then send it all back. If you turn on ODBC tracing and
excute a query, you'll see what I'm talking about (this will make it
*really* crawl - don't do it for long!). You need to do a lot of optimizing
and change many of your queries to SQL Pass-Throgh. I recommend you get a
good book - my favorite is Microsoft Access Developer's Guide to SQL Server
by Chipman and Baron, SAMS Publishing. Good luck!|||What is the difference /benefits of a pass-through query and any other SQL
query? I have never used a pass through query before..
Thanks,
Brad
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> the
> that
> The speed problem isn't ODBC - it's your code. You've only just begun the
> journey of porting an Access app to SQL Server. For instance, you open a
> recordset in code on two large tables using a JOIN. You only want to add
one
> record. But Access will fetch all records from both tables across the
> network (locking them in the process), do the JOIN, move to the end, and
> insert the field, then send it all back. If you turn on ODBC tracing and
> excute a query, you'll see what I'm talking about (this will make it
> *really* crawl - don't do it for long!). You need to do a lot of
optimizing
> and change many of your queries to SQL Pass-Throgh. I recommend you get a
> good book - my favorite is Microsoft Access Developer's Guide to SQL
Server
> by Chipman and Baron, SAMS Publishing. Good luck!
>|||A pass-through query passes the SQL statement to the server so that the
entire statement gets processed there, rather than returning lots of raw
data so that Access can process the query at the client computer. I'm
pretty sure you can find more information in the Access or SQL Server help.
I recently had surprisingly good results after upsizing an Access 2000
database to SQL Server, using only ODBC linked tables to SQL server 2000.
My users can now use the application at an acceptable speed from remote
high-speed VPN connected locations. Access Front-End/Back-end could never
have done that.
One thing I've done a lot of in Access is to write VBA functions to use in
Query criteria so I could fill in parameters in code without using SendKeys
to fill in parameter prompts. I use this method for Forms and Reports, and
I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
driver broke-down my queries so they were sent to SQL server with literal
parameters, and I got back only the records I was looking for. You can use
SQL Server Profiler to see the SQL statements that get sent to your server
from Access - that can give you a lot of insight into what's going on in you
app.
I'm pretty sure filters still get applied locally, so you don't want to rely
on those to be your initial data filters on large recordsets.
Another way to bring improved performance would be to use and Access data
project file (.ADP), which provides a more true Client/Server application.
I thought I was going to have to go that route so that my application would
work for remote high-speed VPN users, but my app worked so well with ODBC
linked tables that I didn't have to go to the work of largely re-doing my
application.
Another book set I would recommend is "Access Developer's Handbook Set" by
Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> one
> optimizing
> Server
>|||You really should only be calling stored procedures from pass-through
queries. In addition, you shouldn't have linked tables at all if you want a
really scalable enterprise application. Use the VBA recordset and
passthrough queries calling stored procedures. Let the database server
perform the database work most efficiently using stored procedures.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
my[vbcol=seagreen]
in[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
> one
> optimizing
a[vbcol=seagreen]
> Server
>|||So, you are saying do not use linked tables at all. Could you give me a
snippet of Access code that opens an SQL Server Db and calls a stored
procedure to do something simple such as select * from a table?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
> a
>|||That's great information...
When you are referring to writing an Access query that uses a VBA function
for it's criteria to fill in the parameters (I've never even used SendKeys
to fill in parameter prompts before), are you referring to replacing things
like "[Enter Customer Name]" as a row criteria with a function such as
GetCustName()' where function GetCustName() would display a screen where
the user enters the customers name they are looking for and then you set
GetCustName = txtCustName?
Also what is the ADP you referred to? Is this a reference you add in to the
project? Never even heard of that one!!
Thanks,
Brad
That sounds interesting and is likely something I'd want to do...
"Dynamo" <noone@.nowhere.com> wrote in message
news:uPsFHJPuEHA.3152@.TK2MSFTNGP14.phx.gbl...
> A pass-through query passes the SQL statement to the server so that the
> entire statement gets processed there, rather than returning lots of raw
> data so that Access can process the query at the client computer. I'm
> pretty sure you can find more information in the Access or SQL Server
help.
> I recently had surprisingly good results after upsizing an Access 2000
> database to SQL Server, using only ODBC linked tables to SQL server 2000.
> My users can now use the application at an acceptable speed from remote
> high-speed VPN connected locations. Access Front-End/Back-end could never
> have done that.
> One thing I've done a lot of in Access is to write VBA functions to use in
> Query criteria so I could fill in parameters in code without using
SendKeys
> to fill in parameter prompts. I use this method for Forms and Reports,
and
> I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
> driver broke-down my queries so they were sent to SQL server with literal
> parameters, and I got back only the records I was looking for. You can
use
> SQL Server Profiler to see the SQL statements that get sent to your server
> from Access - that can give you a lot of insight into what's going on in
you
> app.
> I'm pretty sure filters still get applied locally, so you don't want to
rely
> on those to be your initial data filters on large recordsets.
> Another way to bring improved performance would be to use and Access data
> project file (.ADP), which provides a more true Client/Server application.
> I thought I was going to have to go that route so that my application
would
> work for remote high-speed VPN users, but my app worked so well with ODBC
> linked tables that I didn't have to go to the work of largely re-doing my
> application.
> Another book set I would recommend is "Access Developer's Handbook Set" by
> Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
a[vbcol=seagreen]
>|||Can you also give me a simple example (and code) of a passthrough SQL query
calling a stored procedure at the SQL Database level?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
> a
>|||"Brad Pears" <donotreply@.notreal.com> wrote in message
news:eHmgt0RvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Can you also give me a simple example (and code) of a passthrough SQL
query
> calling a stored procedure at the SQL Database level?
> Thanks,
> Brad
I have a sub called SQLExecute that I use for Pass-Throughs. Here it is:
Public Sub SQLExecute(SQL As String, Optional rs As Variant)
'This function creates a SQL Pass Through query that optionaly returns
records
On Error GoTo Error_SQLExecute
Dim qdf As QueryDef
Dim errAny As error
Set qdf = DBEngine(0)(0).CreateQueryDef("")
qdf.Connect = gstrODBC
qdf.ODBCTimeout = 0
qdf.SQL = SQL
If IsMissing(rs) Then
qdf.ReturnsRecords = False
qdf.Execute
Else
qdf.ReturnsRecords = True
qdf.MaxRecords = 2147483647
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
End If
Exit_SQLExecute:
Set qdf = Nothing
Exit Sub
Error_SQLExecute:
For Each errAny In DBEngine.Errors
msgbox "Error " & errAny.Number & " from " & errAny.source & " = " &
errAny.Description, vbCritical, "Error " & errAny.Number & " - SQLExecute"
Next
Resume Exit_SQLExecute
End Sub
In this sub, what makes it a Pass-Trhough is setting the QueryDef's Connect
property. In my case, the Connect string is set on app open to a global
variable called "gstrODBC". To get a value to sote in gstrODBC, open the
Debug window in your Access app (Ctrl-G) and type this followed by the Enter
key:
?CurrentDb.TableDefs("any_linked_table_name").Connect
The returned string should be used as the Connect property for the query
def. For the SQL parameter to SQLExecute, use any valid SQL statement or
query, including stored procedures with parameters. The one caveat is it
must be SQL syntax that the *server* understands, *not* Access's SQL syntax.
The error handler will return messages from the SQL Server if there is a
syntax error, etc.
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
want[vbcol=seagreen]
> a
> SQL
> In
> data
begun[vbcol=seagreen]
open[vbcol=seagreen]
> a
> add
the[vbcol=seagreen]
> and
> and
> get
>
Subscribe to:
Posts (Atom)