Is it possible to execute big SQL query in Hibernate?

Activex-SQL script to port information from Exchange Gal to SQL 2000 (1/2 done)

• I have the following script[below] that is tested and works. I'm looking to take the results of the following query [cn,adspath,UID] and dump them into a SQL Server Table. I need this script modified so that it takes this data it recieves and dumps it into a table with the following structure: Table Name: ExchangeGal IDNumber -Primary Key (int) cn - varchar adspath -varchar UID -varchar [I need the script to loop through all records and dump them into the aforemention table. In the example below I used a messagebox to validate the pull, that may be removed] '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() 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 'count = oRS.RecordCount - 1 set test = oRS.fields("uid") msgbox test Main = DTSTaskExecResult_Success End Function

• Answer:

  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