473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

1
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.OpenConnection("", , , "ODBC;DATABASE=databaseNew;DSN=LegalperfecTSQL ;")
Set lvDats = cns.Database
Set lvWrkd = CreateWorkspace("", "sa", "bulemia22", dbUseODBC)
Set cnd = lvWrkd.OpenConnection("", , , "ODBC;DATABASE=databaseold;DSN=LegalperfecTSQL ;")
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
Nov 5 '08 #1
1 3001
debasisdas
8,127 Expert 4TB
database objects are not like filesystem objects , you cant copy and paste them as files in windows.
Nov 5 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: sbh | last post by:
I need to copy data from a table on one Oracle server to another. Scenario: Need to create a stored procedure in server a, database aa that will copy data from server b, database bb, table bbb to...
7
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
11
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the...
4
by: Little PussyCat | last post by:
Hello, I have had a request, one of our tables is used as a report and I have been asked that all fieldnames for months have dashes in them, like Jan-05 instead of Jan05 and so on... Now what...
10
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I...
5
by: M Wells | last post by:
Hi All, Is it possible to use form criteria in a query / view in an Access 2003 ADP with SQL Server as the backend? ie something like: select * from mytable where recid = forms!! I'm...
8
by: David | last post by:
Hi, Could someone please xplain how to add a field to an existing SQL table in VB.Net I have added the field in the Server Explorer and it shows up when I reload the program but I cannot...
4
by: JIM.H. | last post by:
Hello, I am trying to write the data I got from a web service to my table in SQL Server I need to append the dataset wsDS to the dataset ds and do update. PVS.myWS.Loader load = new...
1
by: jshunter | last post by:
I've got a weird one here. I'm running a DTS package on SQL Server 2005. It copies a bunch of stored procedures. I renamed them on the originating server and ran the DTS again. The came over...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.