Copy table, view or stored procedure from one SQL SERVER 2005db to another using VB6

[Weltzy]

Hi,
I am new to SQL SERVER 2005 and want to do the following using VB6:


Set lvWrks = CreateWorkspace ("", "sa", "bulemia22" , dbUseODBC)
Set cns = lvWrks.OpenConn ection("", , , "ODBC;DATABASE= databaseNew;DSN =LegalperfecTSQ L;")
Set lvDats = cns.Database
Set lvWrkd = CreateWorkspace ("", "sa", "bulemia22" , dbUseODBC)
Set cnd = lvWrkd.OpenConn ection("", , , "ODBC;DATABASE= databaseold;DSN =LegalperfecTSQ L;")
Set lvDatd = cnd.Database

Both databases are using the same SQL SERVER.

I need to remove tables, views and stored procedures in databaseold and replace them with same from databasenew.

I use:
S = “DROP TABLE TableName”

lvDatd Execute s

And then want to use a stored procedure named fncCopyTable:

(ALTER [dbo].[fncCopyTable]
(@tablename varchar(25))
BEGIN
Exec (‘insert * into databaseold.’ + @tablename + ‘from ‘ + @tablename)
END

I do this:

S = “fncCopyTable” & “ “ & “tablename”

lvDats.Execute s


And I get the standard 3146 –odbc failed.

How can I do this? I need to be able to also replace views and stored procedures. I only replace anything if they are present in databasenew.
Regards,
Weltzy

[debasisdas]

database objects are not like filesystem objects , you cant copy and paste them as files in windows.