connecting to different databases

[shanuragu]

Hi

How can I connect more than one databases in SQL server??


shara

[simflex]

Here is an example of how you can do that.
Although this example selects data from one db and inserts into another db but the concept is sound.
hope it helps!

Pulling data from one database and inserting into another database.

'connection settings file
Dim ConDB
Dim connStr
Set ConDB = Server.CreateObject("ADODB.Connection")
'Connection to the 1st DSN.
connStr = "DSN=DSN1Name;uid=login;pwd=password"
ConDB.mode = 3
ConDB.ConnectionTimeout = 1
ConDB.CommandTimeout = 1
ConDB.Open(connStr)
CONST adOpenStatic = 3
CONST adLockOptimistic = 3


'we need to do a select statement to retrieve to primary key for the one's dept.
Dim thissql
thissql = "SELECT * FROM dbo.db1.employee where dbo.db1.employee.empID = "&empid&""
set deptSet = ConDB.Execute(thissql)
If Not deptSet.Eof then
empid= deptSet("empid")
'You need to pull all data out that you want to insert into the next database.
First_Name = deptSet("firstname")
Last_Name = deptSet("lastname")
homephone = deptSet("homephone")
Work_Phone = deptSet("workphone")
etc.
etc.
End If
Set thissql = Nothing
set deptSet = Nothing
Set connStr = Nothing

' Check first to make sure the empid pulled info.
If empid <> "" Then
'Connection to the 2nd DSN.
connStr = "DSN=DSN2Name;uid=login;pwd=password"
ConDB.execute = "INSERT INTO training " & _
"(fName, lName,PhoneHome,PhoneWork,Age,Address,City,State,Zip,DeptID,DriverGender,EmailAdd) " & _
" VALUES " & _
" ('"&First_Name&"', " & _
" '"&Last_Name&"', " & _
" "&homephone&", " & _
" '"&work_Phone&"'," & _
etc, etc.

Set ConDB = Nothing
Set connStr = Nothing
ConDB.Close

End If

[magus]

additional:

Dim conn1
Conn1 = Server.CreateObject("ADODB.Connection")
Conn1.open "DSN1", "username", "password"

Dim conn2
Conn2 = Server.CreateObject("ADODB.Connection")
Conn2.open "DSN2", "username", "password"

Then you can run database operation (SELECT, INSERT, EDIT) from Database1 to Database2 vice versa

[shanuragu]

Thanks simflex & magus for ur help

shara

[joelo]

I am trying to move all records from one database(dbinput) to another database (dboutput)and leave database(dbinput) blank....both databases are indentical and consist of 10 tables in each database

can the above example be used to achieve what I am trying to do..... A little bit of light a long way.

Thanx.

[magus]

Of course you can...as long as you know how to query that operation...

[simflex]

joelo, are you leaving database (dbinput) blank for future use?
If no, why not use the copy database utility to either copy the entire database (dbinput) to dboutput?
And if you are planning on using it for the future, then use dts to move all data from dbinput to dboutput.
You have a lot of options.
This whole thing could be done on the backend with the need for front end programming.
However, as Magus said, it can be done.
Try to formulate your code and let us know if you have issues accomplishing this.

The above of course depends on what kind of database you are using anyway.

[joelo]

I attached here a copy of my script

I don't know if I am making any sense

Yes, I am leaving database (dbinput) blank for future use by user


Please Help me out


Thanks

[magus]

I've made a simple code for this and it's working in my computer:

<%

Dim xDb_Conn_Str
Dim Conn
Dim strsql

if Date()= DateSerial(Year(Date),Month(Date)+1,0) Then


xDb_Conn_Str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("dboutput\Databaseoutput.mdb") & ";"
set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str1

strsql = "INSERT INTO bsldatabase SELECT * FROM [" & server.mappath("dbtemp\Databasetemp.mdb") &"].bsldatabase "

Conn.execute(strsql)

strsql = "INSERT INTO devicebsldevice SELECT * FROM [" & server.mappath("dbtemp\Databasetemp.mdb") & "].devicebsldevice"

Conn.execute(strsql)

---and continue for the rest
%>

[joelo]

Thank You very Much for 'Ur quick response.....Very Helpful

Attached a copy of my corrected script....Please kindly check if my delete query and all is ok.

[simflex]

Your delete statement will be something like:


strSQL = "DELETE * FROM bsldatabase "
rst.Open strSQL, Conn

This assumes that you are using access database.
If you are using sql server, then remove the *

[simflex]

I forgot to mention that you will be deleting objects in a database, not the database itself since you need for future use.

[joelo]

Please I am Trying to exclude peimary key field from being inserted..... Is there any thing I am doing wrong here, please correct me


strsql = "INSERT INTO bsldatabase [(ID, FACILITYCOMPONENTID, COMPONENTDESCRIPTION, DEVICEID, ISSTACKARRESTORINSTALLED, ISFLAMEARRESTORINSTALLED, DIDALARMOPERATEATWONDERWAREANDSHUTDOWNLOCALPANEL, TESTDATE, NEXTDUETESTDATE, MONTHYEAR, FACILITY, AREA, TECHNICIANSNAME, COMMENTS)]" &_
"VALUES [(ID, FACILITYCOMPONENTID, COMPONENTDESCRIPTION, DEVICEID, ISSTACKARRESTORINSTALLED, ISFLAMEARRESTORINSTALLED, DIDALARMOPERATEATWONDERWAREANDSHUTDOWNLOCALPANEL, TESTDATE, NEXTDUETESTDATE, MONTHYEAR, FACILITY, AREA, TECHNICIANSNAME, COMMENTS)]" &_
"SELECT [(ID, FACILITYCOMPONENTID, COMPONENTDESCRIPTION, DEVICEID, ISSTACKARRESTORINSTALLED, ISFLAMEARRESTORINSTALLED, DIDALARMOPERATEATWONDERWAREANDSHUTDOWNLOCALPANEL, TESTDATE, NEXTDUETESTDATE, MONTHYEAR, FACILITY, AREA, TECHNICIANSNAME, COMMENTS)]" &_
"from ["& server.mappath("dbtemp\Databasetemp.mdb") & "].bsldatabase"
conn.execute(strsql)

[simflex]

Is your primary key called ID?
If yes, then depending on your data definition, you may need to remove it from your insert statement.

In other words, you should make your primary key autonumber (identity if you are using sql server).
This way, you won't have to include it in your insert statement.
All you do is just insert other fields.
So, I suggest looking at your data structure and see if your primary key is an auto increment number.
If it is not, then you will have to do something like:
insert into yourtable(ID, ....other fields)
select ID+1, and values from theOthertable.

The other problem with your code, is if you are selecting from one database to insert into another, then your code should look something like this:

strsql = "INSERT INTO bsldatabase [(ID, " & _
" FACILITYCOMPONENTID, " & _
" COMPONENTDESCRIPTION, " & _
" DEVICEID, " & _
" ISSTACKARRESTORINSTALLED, " & _
" ISFLAMEARRESTORINSTALLED, " & _ " DIDALARMOPERATEATWONDERWAREANDSHUTDOWNLOCALPANEL, " & _
" TESTDATE, " & _
" NEXTDUETESTDATE, " & _
" MONTHYEAR," & _
" FACILITY, " & _
" AREA, " & _
" TECHNICIANSNAME, " & _
" COMMENTS)]" &_
"SELECT [(ID, " & _
"FACILITYCOMPONENTID," & _
"COMPONENTDESCRIPTION," & _
"DEVICEID, " & _
"ISSTACKARRESTORINSTALLED, " & _
"ISFLAMEARRESTORINSTALLED, " & _
"DIDALARMOPERATEATWONDERWAREANDSHUTDOWNLOCALPANEL," & _
"TESTDATE, NEXTDUETESTDATE," & _
"MONTHYEAR, " & _
"FACILITY, " & _
"AREA, &_

[joelo]

My ID is an autonumber Field and I am using Access 2000
I am still getting an error


strsql = "INSERT INTO bsldatabase [(FACILITYCOMPONENTID, " & _
" COMPONENTDESCRIPTION, " & _
" DEVICEID, " & _
" ISSTACKARRESTORINSTALLED, " & _
" ISFLAMEARRESTORINSTALLED, " & _
" DIDALARMOPERATEATWONDERWAREANDSHUTDOWNLOCALPANEL, " & _
" TESTDATE, " & _
" NEXTDUETESTDATE, " & _
" MONTHYEAR," & _
" FACILITY, " & _
" AREA, " & _
" TECHNICIANSNAME, " & _
" COMMENTS)]" &_
"SELECT [(FACILITYCOMPONENTID," & _
"COMPONENTDESCRIPTION," & _
"DEVICEID, " & _
"ISSTACKARRESTORINSTALLED, " & _
"ISFLAMEARRESTORINSTALLED, " & _
"DIDALARMOPERATEATWONDERWAREANDSHUTDOWNLOCALPANEL," & _
"TESTDATE, NEXTDUETESTDATE," & _
"MONTHYEAR, " & _
"FACILITY, " & _
"AREA,"&_
" TECHNICIANSNAME, " & _
" COMMENTS)]" &_
"from ["& server.mappath("dbtemp\Databasetemp.mdb") & "].bsldatabase"
conn.execute(strsql)


Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/compliance/move3.asp, line 48

Line 48 is:
conn.execute(strsql)

[simflex]

try changing this:
conn.execute(strsql)

To this

conn.execute strsql

[joelo]

I Still get the same Error

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/compliance/move3.asp, line 48

Line 48 is:
conn.execute(strsql)

[simflex]

ok, do a couple of this for me.
One, see this tag [ ],
remove them and put them on individual fields like so:
[ESTDATE] " & _
" [EXTDUETESTDATE] " & _
" [ONTHYEAR]" &

etc and let me know what happens. some of the fields you are using may be reserved words.

[joelo]

I have Tried it ...still same error


Attached a copy of my script

[simflex]

can you include a zipped copy of your db, just one since they have the same structure ( I hope) and I will see what is wrong as I am getting ready to leave.

[joelo]

There U go....!!!!

[simflex]

I got a chance to look at your code.
The problem with the insert statement is brackets ().
Whenever you do an insert statement, and you have to list the fields, always use a ().
Insert into (fields....)
select ....
Here is the modification.
I have tested it and it works for me.
It selects from dbTemp and inserts into dbout.

I did not work the rest of the code as I believe this will help you resolve the rest.
One other thing I must tell you is to use delete * from yourtable when performing a delete statement.
I am attaching the next code

[joelo]

Hey Men, ....You 're the MAN

Works like a charm...Thank You Very Much for 'ur Help

I don't to bother You....Is there any way that I can set up this script to run automatically on it's own at the end of every Month?

Please any help will Do.

Thanks in Advance

[simflex]

you're welcome!
:)

Yes, you can set it up to run every month.
It takes two steps.
One, use global.asa file and set up your asp file.
Then use windows task scheduler (windows --> accessories > system tools, scheduled tasks) and schedule it it to run however you want.

[joelo]

Please Is there chance that you can show how to do it as have never done this B/4.....Please Please Please....!!!

[joelo]

Please Take a look at what I 've done and see if it ok

see attachment global.asa


Thanx in Advance

[joelo]

Please Someone Anybody.....Help me...!!!

[simflex]

sorry, I have been gone for awhile.
The only thing I see wrong is the the typo.
It should be application.unlock, not unloack.
Change that and set it up to run.
What you need to do right now is follow the following instructions and set up your global.asa to run your code monthly or however you want it.

For Windows nt, 2000 or xp, follow these instructions:

In order for the web server to know to look for this file you need to set up your directory as an application in IIS. This is done by navigating to the appropriate web site or directory in Internet Services Manager and right clicking on it and selecting "Properties" from the pop up menu. Then select the "Directory" or "Home Directory" tab and on the bottom half of that window you will see the "Application Settings" section. If it's not already, you need to make the directory an application by pressing the "Create" button. Click "Ok" to save the setting and exit.

In PWS (Personal Web Server) on Windows 95/98 this is done differently. You need to go to the Personal Web Manager and then click on "Advanced" in the bottom left. In the directory tree you should see an entry named <Home>. Select it and click the "Edit Properties..." button. In the resulting dialog box ensure that all three checkboxes are checked (Read, Execute, and Scripts). Click "Ok" to save the setting and exit. This will enable processing of the global.asa in PWS.

You may set it up to run in 1 day so you can be sure it is working.
If it is working, then set it up to run monthly.
If it isn't, then let us know.
Good luck!! ;)