Hi pmclinn-ga,
A very important part of your question is the SQL Server
database in which the data is to be copied. Since you have not
provided much information on this database, there are certain
assumptions that I am making about it in order to answer your
question. If any of my assumptions are erroneous, I will gladly
rectify my code based on the new information you provide.
From your question, I understand that you have a SQL Server
database available, which contains a Table named 'ExchangeGal' with
the following fields.
> IDNumber -Primary Key (int) : Since you have not specified what
data goes in here, I assume that this field is Autogenerated i.e., SQL
Server will automatically provide a value for this field whenever I
add a new record to the database.
> cn - varchar
> adspath -varchar
> UID -varchar
As you know, to open this database, I need certain info such as the
database name, its location etc. to put in the connection string. Here
is the connection string that I am using :
"Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=myDatabase;" & _
"User ID=myUsername;" & _
"Password=myPassword"
This assumes that the Sql server is on the local machine, the database
is named myDatabase, and we use the username and password. If you need
to change the connection string to suit your settings, you can check
out the available options at :
- ADO Connections : OLE DB Provider for SQL Server
( http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer
)
Here's the Modified code :
======= BEGIN CODE ======
' The connection string used to open the database
' You will need to modify it to suit your environment
' for this code to work
Const DB_CONNECT_STRING = "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=myDatabase;" & _
"User ID=myUsername;" & _
"Password=myPassword"
Function Main()
' Get data to be written to database
set oConn = CreateObject("ADODB.Connection")
set oCommand = CreateObject("ADODB.Command")
set oRS = CreateObject("ADODB.Recordset")
oConn.Provider = "ADsDSOObject"
oConn.Open "Ads Provider"
set oCommand.ActiveConnection = oConn 'set the active connection
' A filter of (objectClass=person) will return mailboxes,
distribution lists, and custom recipients
strQry =" <LDAP://ENTMAEXCH02/o=Continental/ou=Northeast/cn=Recipients>;(&(objectClass=Person));cn,adspath,UID;subtree"
oCommand.CommandText = strQry
oCommand.Properties("Page Size") = 99
'paged query used to avoid Exchange LDAP server limits
set oRS = oCommand.Execute
oRS.MoveFirst
' Open connection to SQL Server database
Set dbCon = CreateObject("ADODB.Connection")
Set rstDB = CreateObject("ADODB.Recordset")
dbCon.Open = DB_CONNECT_STRING
'Open table ExchangeGal
rstDB.Open "ExchangeGal", dbCon
' Move to the last rec in the Table. Just In case...
rstDB.MoveLast
'Now we loop through oRS, adding its rows to the database
While Not oRS.Eof
' Call AddNew to create a new row
rstDB.AddNew
' Note that I do not set IDNumber
' since I assume it is Auto generated
' You can easily set it here if it is not
rstDB("cn") = oRS("cn")
rstDB("adspath") = oRS("adspath")
rstDB("UID") = oRS("uid")
rstDB.Update
'Move to the next record
oRS.MoveNext
Wend
'Cleanup
rstDB.Close
rstDB = Nothing
Main = DTSTaskExecResult_Success
End Function
=========== END CODE ==============
Please try out this code and inform me if you face any problems. Note
that you will have to modify the connection string before the code
will work.
Hope this helps.
If you need any clarifications, just ask!
Regards,
Theta-ga
=====================================
RELATED LINKS :
- VBScript Interfaces in SQL Server 2000
by Alok Mehta and Daniel Williams
( http://msdn.microsoft.com/msdnmag/issues/02/08/VBScriptandSQLServer2000/default.aspx
)
- Show me the Autonumber!
by Manohar Kamath
( http://www.kamath.com/tutorials/tut004_autonum.asp )
- The ABCs of ADO and ASP
( http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5909&pg=1
)
========================================
Google Search Terms Used :
msdn vbscript ado "sql server" database addnew
vbscript open table with recordset
vbscript create "sql server" database |
Request for Answer Clarification by
pmclinn-ga
on
23 Jan 2003 14:41 PST
I'll run this code tomorrow and give it a whirl. I think the only
problem I can forsee is that there may be an issue with null records
poping up and ldap connection seems to have trouble with that.
After I run it I'll contact you with any errors I encounter, if any.
|
Clarification of Answer by
theta-ga
on
24 Jan 2003 09:01 PST
Hi pmclinn-ga,
Sure, take your time. Remember to modify the connection string to
reflect your settings.
Best of luck!
Regards,
Theta-ga
|
Request for Answer Clarification by
pmclinn-ga
on
24 Jan 2003 10:50 PST
Error Code:0
Error Source= ADODB
ERROR: CURRENT RECORDSETSET DOES NOT SUPPORT UPDATING. THIS may be a
limitation of the provider, or of the selected lock type.
I also had to pull out this code:
' Move to the last rec in the Table. Just In case...
rstDB.MoveLast because it said the recordset could not write
backwards?
error on Line 44
Current Recordset does not not support updating. This may be a
limitation of the provider, or of the selected locktype.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
' The connection string used to open the database
' You will need to modify it to suit your environment
' for this code to work
Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Password=PASS;Persist
Security Info=True;User ID=saV;Initial Catalog=320DATABASE;Data
Source=plantop"
Function Main()
' Get data to be written to database
set oConn = CreateObject("ADODB.Connection")
set oCommand = CreateObject("ADODB.Command")
set oRS = CreateObject("ADODB.Recordset")
oConn.Provider = "ADsDSOObject"
oConn.Open "Ads Provider"
set oCommand.ActiveConnection = oConn
'set the active connection
' A filter of (objectClass=person) will return mailboxes,
distribution lists, and custom recipients
strQry =" <LDAP://ENTMAEXCH02/o=Continental/ou=Northeast/cn=Recipients>;(&(objectClass=Person));cn,adspath,UID;subtree"
oCommand.CommandText = strQry
oCommand.Properties("Page Size") = 99
'paged query used to avoid Exchange LDAP server limits
set oRS = oCommand.Execute
oRS.MoveFirst
' Open connection to SQL Server database
Set dbCon = CreateObject("ADODB.Connection")
Set rstDB = CreateObject("ADODB.Recordset")
dbCon.Open = DB_CONNECT_STRING
'Open table ExchangeGal
rstDB.Open "ExchangeGal", dbCon
'Now we loop through oRS, adding its rows to the database
While Not oRS.Eof
' Call AddNew to create a new row
rstDB.AddNew
' Note that I do not set IDNumber
' since I assume it is Auto generated
' You can easily set it here if it is not
rstDB("cn") = oRS("cn")
rstDB("adspath") = oRS("adspath")
rstDB("uid") = oRS("uid")
rstDB.Update
'Move to the next record
oRS.MoveNext
Wend
'Cleanup
rstDB.Close
rstDB = Nothing
Main = DTSTaskExecResult_Success
End Function
|
Request for Answer Clarification by
pmclinn-ga
on
24 Jan 2003 13:02 PST
This was the orignial error:
' Move to the last rec in the Table. Just In case...
rstDB.MoveLast
This code generates this error:
Error Code: 0
Error Source = Microsoft OLE DB Provider for SQL Server
Error Description: Rowset does not support fetching backward
Error on Line 40
Rowset does not support fetching backwards
|
Request for Answer Clarification by
pmclinn-ga
on
24 Jan 2003 13:21 PST
I just changed this line and things are working:
rstDB.Open "ExchangeGal", dbCon
to rstDB.Open "ExchangeGal", dbCon,2,3
Thanks!
|
Clarification of Answer by
theta-ga
on
25 Jan 2003 00:58 PST
Hi pmclinn-ga,
Great to know that you were able to get the code working. I was
unfortunately unable to test the code since I have neither Exchange
nor (as of now) VB installed. But, having done extensive db coding
with ADO, I was fairly sure that the errors, if any, would be minor.
:-)
Glad to be of assistance.
Regards,
Theta-ga
:)
|
,
0 Comments
)