RE: [SQLDownUnder] Script in SQL2005

[Kristen Harvey]

 Never mind.  I've got it.  Never try this on a Friday afternoon :)  In
saying that I've migrated a database that has a different owner than dbo
and instead of keeping it as the owner it has made a schema.  How do I
bulk change a schema?

Thanks
Kristen


-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Kristen
Harvey
Sent: Friday, 24 November 2006 6:22 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: [SQLDownUnder] Script in SQL2005

Hi Guys,

Guess what?  I've moved this to Production and it doesn't work.  The
only difference is the server name.  Why would this be?

------------------------------------------------------------------------
------------------------------------

RE: [SQLDownUnder] Script in SQL2005

------------------------------------------------------------------------
--------

From: Kristen Harvey
Subject: RE: [SQLDownUnder] Script in SQL2005
Date: Mon, 27 Mar 2006 17:57:04 -0800 

------------------------------------------------------------------------
--------

I had a look at the linked servers, and I have two, ADSI and
INFRA_ADlookup.  I found what the problem was.  ADSI had a Product name
of Active Directory Services 2.5 which is what I use in SQL2k, but it
seems 2k5 doesn't like it.  I deleted ADSI from the linked servers and
re-created with the product name Active Directory Service Interfaces and
it worked fine.
 
Thanks very much for your help Greg.
 
Thanks
Kristen



------------------------------------------------------------------------
--------
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Greg Wood
Sent: Tuesday, 28 March 2006 11:15 AM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] Script in SQL2005


 

Good to hear!

 

INFRA_ADLookup is just the DisplayName and reference name I used in that
example.

 

go have a look under linked servers for them.

 

 

Greg

 

 



 


------------------------------------------------------------------------
--------
Subject: RE: [SQLDownUnder] Script in SQL2005
Date: Tue, 28 Mar 2006 11:07:31 +1000
From: Kristen.Harvey@xxxxxxxxxxxxxxxxxxxxxx
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx


Greg,  I got it working.  My original script had the AD server name in
it, I took it out and it worked.  One question though, previously I used
OPENQUERY (ADSI, and in your query you have INFRA_ADlookup.  What is
INFRA_ADlookup?  I looked it up on msdn and got no result back, same on
Google.
 
Thanks
Kristen



------------------------------------------------------------------------
--------
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Greg Wood
Sent: Tuesday, 28 March 2006 10:42 AM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] Script in SQL2005


make sure in the second OPENQUERY  that i sent. Change - 

dc=Addomain,dc=net

 

to be your domain that you login to, this can be found by going to a
command prompt typing set and looking at USERDNSDOMAIN

 

say the domain was 

greg.wood.net

 

change it to

dc=greg,dc=wood,dc=net

 

 

Greg

 

 

 


 


------------------------------------------------------------------------
--------
Subject: RE: [SQLDownUnder] Script in SQL2005
Date: Tue, 28 Mar 2006 10:28:37 +1000
From: Kristen.Harvey@xxxxxxxxxxxxxxxxxxxxxx
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx


Hi Greg,
 
All the SQL services run as a domain user on both servers.  I can run
your first script fine, but the second comes up with the same error.  I
must be doing something simple wrong.
 
Thanks
Kristen
 



------------------------------------------------------------------------
--------
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Greg Wood
Sent: Tuesday, 28 March 2006 8:24 AM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] Script in SQL2005


Hi Kristen, 

 

Make sure your SQL Agent account is running as a domain user, as Active
Directory will need to authenticate.

 

Here is a script I use that works on both SQL 2k and 2k5

 

sp_addlinkedserver 'INFRA_ADlookup', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
go

--through enterprise manager go and set the user to connect to Ad in the
box at the bottom - 


SELECT     *
FROM         OPENQUERY(INFRA_ADlookup, 
                      'SELECT
sAMAccountName,name,whenCreated,homeDirectory,scriptPath,displayName,tel
ephonenumber,department FROM
''LDAP://ou=users,dc=Addomain,dc=net'' WHERE objectClass= ''user''')

 

Greg






------------------------------------------------------------------------
--------


> Subject: RE: [SQLDownUnder] Script in SQL2005
> Date: Mon, 27 Mar 2006 13:54:58 +1100
> From: greg.low@xxxxxxxxxxx
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> 
> Hi Kristen,
> 
> Are you running it on the same system (is 2k5 on the same system as
the
> 2k)? If not, are you sure the ADSI OLEDB provider is installed? 
> 
> Regards,
> 
> Greg
> 
> 
> Dr Greg Low
> Readify - Senior Consultant
> M: +61 419 201 410
> 
> 
> -----Original Message-----
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
> [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Kristen 
> Harvey
> Sent: Monday, 27 March 2006 12:13 PM
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: [SQLDownUnder] Script in SQL2005
> 
> Hi All,
> 
> I'm having a bit of trouble with a script.  The script below works
fine
> on SQL2000, but when I run it on SQL 2005 I get the below error.
> 
> SELECT *
>   INTO TMP_AD
>   FROM OpenQuery(
> ADSI,'<LDAP://Server/OU=User
> Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));
> sAMAccountName, givenName, sn, company, title, mail, department,
> adspath;subtree')
> 
> Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing the query "<LDAP://Server/OU=User 
> Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));
> sAMAccountName, givenName, sn, company, title, mail, department, 
> adspath;subtree" for execution against OLE DB provider "ADSDSOObject"
> for linked server "ADSI". 
> 
> I run this first on both scripts
> 
> EXEC sp_addlinkedserver
> 'ADSI',
> 'Active Directory Services 2.5',
> 'ADSDSOObject',
> 'adsdatasource'
>  
> EXEC sp_addlinkedsrvlogin
> 'ADSI',
> 'false',
> NULL,
> NULL,
> NULL

Regards
Kristen Harvey
System Administrator - DBA
Technologies
Collection House Ltd
Ph: 07 3100 1211
Mb: 0419 995 127
Fax: 07 3831 1244

------------------------------------------------------------------------
---
This email is private and confidential and may contain legally
privileged and/or commercially sensitive information.

If this communication is not intended for you and you are not an
authorised recipient of this email, you are prohibited from dealing with
or relying on the email or any file attachments.  This prohibition
includes reading, printing, copying, re-transmitting, disseminating,
storing or in any other way dealing or acting in reliance on the
information.  If you have received this email in error, we request you
contact the sender immediately by return email and then destroy any
electronic or paper copy of this message.

Reasonable steps have been taken to ensure the accuracy and the
integrity of this communication but the sender and his/her employer
accepts no liability for materials transmitted or for results of any
actions taken or not on the basis of the information in this
communication.  Privilege in the contents of the email or any file
attachments is not waived if the email is inadvertently sent to the
wrong address.  This email has been scanned through virus scanning
software, but no guarantee is given that this email and any file
attachments are virus free.
------------------------------------------------------------------------
---


to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed
by http://www.readify.net




to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net