VB6 and SQL Server, Pulling records

[sunnydreams]

Microsoft SQL Server 2000

I am very new to programming. Can u pls assist.

I have the records in a database( Table 1)(Available on QA Server).
Now I want to write code, to pull those data from Table1 to a different database(Table 2)(Available on Development Server) and Table 2 (which is the records that are pulled) should run every month.ie.it has to be rerun.

Can anyone of u Pls help me with this code by providing syntax?
If u need any more details pls let me know.
Thx in advance.

So far, I have this code:

Dim conn As New ADODB.Connection
Set conn.ConnectionString= "ODBC; Table1 & ";UID=" &
UID & ";PWD=" & PWD
conn.Open

and I do not understand how to rerun this code and whether the above code will work?

[szlamany]

Seems like you need to create a LINKED SERVER - I did my first one last month - had some help from someone on the forum - search for LINKED SERVER and you might get an idea...

Good luck!

[Mackster]

Hi,
If your question is that you have two databases in SQL Server, the first one is A and the Second one is B. The first database has table1 and the second database has table2. you want to transfer records from A.Table1 to B.Table2.

If you want to achieve this using SQL Server i.e. right an simple SQL to copy the records then yes you will need to link the servers as said by szlamany.

If you want to use just VB6 then

     Dim ConnectionString1 as String
     Dim ConnectionString2 as String
     Dim dbcon1 as New ADODB.Connection
     Dim dbcon2 as New ADODB.Connection
     Dim rsTable1 as New ADODB.Recordset
     Dim rsTable2 as New ADODB.Recordset 
 
     ConnectionString1 =   "Provider=SQLOLEDB.1;Data Source=Server1;Initial Catalog=A;UID=sa;password="
     ConnectionString2 =   "Provider=SQLOLEDB.1;Data Source=Server2;Initial Catalog=B;UID=sa;password="
     
     With dbcon1
          .ConnectionString = ConnectionString1
          .open
     End with

     With dbcon2
          .ConnectionString = ConnectionString2
          .open
     End with

    With rsTable1
        .open "Select * from Table1",dbcon1,0,1
        Do Until .EOF 
             rsTable2.Open "table2",dbcon2,1,2
             rsTable2.addnew
             rsTable2.Fields("C1") = .Fields("C1")
             .................................
             rsTable2.update
             rsTable2.Close   
           .Movenext
        Loop
        .close

The above code assumes that SQL Server 1 is called Server1 and has the database called A with table table1 and SQL Server 2 is Called Server2 and has the database called B with Table table2.

It also assumes that the Columns in each table are called C1, C2 and so on.
This way you have opened two connections, with two recordsets and transferred the values from one to the other. Please note I have just done this as an exmaple which adds the records from Table1 to Table2.

hope this helps

[kaffenils]

Here is the thread szlamany mentioned.
http://www.vbforums.com/showthread.p...=linked+server

Add a linked server as described in this thread. Don't used the example posted here, as it will take too much time copying one and one record.
Create a stored procedure on the server that you want to pull data to that does the copy and execute this procedure from you vb code.

The SQL syntax could look something like this, and will copy only records that doesn't exist:

insert into CopyToTable
select s.* from CopyFromServer.database.dbo.CopyFromTable s
left join CopyToTable d on s.PkCol=d.PkCol
where d.PkCol is null

Remember that the Distributed Transaction Coordinator service must run in order for this to work (I think).