Copying Procedures In SQL From One Server To Another Using VBA In Excel
Sep 25, 2007Is there any sample code or help on trying to copy/replace views or prodecure in SQL or Oracle? Im using VBA in Excel...
View 4 RepliesIs there any sample code or help on trying to copy/replace views or prodecure in SQL or Oracle? Im using VBA in Excel...
View 4 RepliesHi,In SQL 2000 if I wanted to take a complete copy of another running sqldatabase all did was create a new database locally and right-click itand select import and point to another database and click copyeverything (stored procedures as well) and it did it for. I can't seemto find the same functionality in SQL 2005. You can copy tables andviews but not the whole database. Is there another way of doing this?Our SQL database is hosted externaly and they recommend using theimport/export feature to do it. Does anyone know I can copy everything(such stored procedures, data table relations...etc)TanksMA.
View 1 Replies View RelatedThis question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
Hi all, is there a way I can simply copy the stored procedures in one DB, and "paste" them over into another DB that is identical? Thanks!
View 2 Replies View RelatedI am trying to make a copy of my live MSSQL 2005 Database to my local Developer Edition install.
To copy the whole database, I am using MS Database Publishing Wizard. I am able to copy the tables and data without problem, but after generating the sql file and running the query against the local db to copy everything, I get errors for the stored procedures.
The stored procedures name is preceded by a login, for instance:
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [matthews].[proc_allitemswithtaxologykeylist]
( @list varchar(7777) )
AS (sql code below)
I have created the user "matthews" on the local instance and given the account DBO role membership in the database. What else do I need to do?
Is there a better way to synch live databases with a local install, perhaps from within management studio itself? I am more experienced with enterprise manager, but 2005 won't let you connect with it.
Thank you for any help.
Hi, Could someone please advise me of how to copy stored procedures from one database to another?
Many thanks,
James
Hello,
When I transfer/copy tables from one database to another (on the same SQL Server), everything is fine. When I try to tranfer/copy Stored Procedures, I get the error message:
"Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server."
I am using the DTS import/export wizard to do this.
Thanks,
Bruce
I have several stored procedures, created in a development environment,that I need to move to a 'QA' environment, and then in turn, to variousproduction environments.When I move these stored procedures, I would like to encrypt them,using the 'WITH ENCRYPTION' clause.My question is, how do I copy these stored procedures from developmentto their target SQL server environment in an encrypted state?Up until now, we have been moving them by generating an SQL script andthen executing that script on the target server. I have tried thisusing a script with 'WITH ENCRYPTION' specified within it, but itdoesn't appear to work when I try and execute that script on the targetserver.Any advice would be greatly appreciated.Nick.
View 1 Replies View RelatedHow do i copy Stored Procedures from one SQL Express database to another?
View 8 Replies View RelatedI am experiencing unexpected behaviour when copying an Excel file.
An Excel file from TemplateDir to WorkingDir.
Excel file is then populated in WorkingDir, ok Copy File task copies Excel File from WorkingDir to FinalDir, however non populated version of file is copied.
However, if I stop SSIS and and execute Copy Task only, the populated version of file is copied.
It seems SSIS has a kind of lock on Excel file in WorkingDir.
how lock can be released to enable Copy of populated file.
Hello
Is it possible to run some SQL Stored Procedures (on our server) via Excel on my workstation and pull the results back into Excel?
I have several Stored Procedures which are sitting on the server, and I need my colleagues to be able to access and run them easily, and this is the only way that I can think of doing that!
Thanks,
Jon
Trying to upload excel in server where excel is not installed. BIDs was there in the server, when i am trying to craete Excel source I am not able.what the workround for this.. How to upload excel without excel installed on the server.
View 4 Replies View RelatedI was trying to find the fastest way to COPY a 50G DB from our production server to our test server. I was testing this on our test server and had a simple question.
Attach/Detach is out since I can only move not copy a DB using this method.
export/import, bcp and DTS take too long and are not indented for this anyways.
so, I was left with Backup and restore. so, I tried that on two different test servers. Took very long. so, I decided to be brave and try the following.
1. Take DB1 that's running on server1 OFFLINE
2. Manually copy all datafiles and logfiles from Server1 to server2.
3. Attach DB1 on server2.
It came up great. No complaints whatsoever. So basically, it's the same as attach/detach but instead of detaching the DB I took the Db offline and copied the files over. So my question...
Is this supported?
Any chance of corruption on either of the servers?
If a DB is offline, is copying datafiles and logfiles supported (I am bit worried if I might corrupt data in production).
Do people use this method to COPY databases or do they stick with BACKUP and RESTORE?
PS: Are there any other ways to do this?
Thanks so much.
Just installed Sql Server Express, trying to figure out how to copy a database. I'm pretty new to databases in general.
Let's say I've created a new database called test1. Now, I want to create an identical copy (data not important, I just need the table structures, keys, constraints, etc.) called test2. Not sure how I would go about doing that. I see that import/export functionality is disabled in the Express version, if that's what I would have needed. I tried copying the mdf file manually and calling "attach" - that failed. Any suggestions? Thanks!
Hi,
I have right now sql server 2000 instance installed on a server (serverone) which is accessed on all nodes through an ODBC connection.
Now we have upgraded our server to windows server 2005 which will be on other machine servertwo. I have right now installed sql server 2005 in servertwo. Wish to copy the database from serverone to servertwo. Once it is copied and everything is working fine. I want to uninstal sql server 2000 from serverone.
What is the best way to do this? I am trying different things like tried running code Advisor on serverone which is not recognising the sql server 2005 instance of servertwo. It's giving message as since the sql server is installed on default settings it can't be accessed remotely. I see that SQL server 2000 database is accessible on all nodes including servertwo. Why is it that servertwo sql server is no where accessible?
What it is that I am missing?
Kindly help me.
Thanks, regards.
Shobha
Hi, I'm trying to copy one SQL Server Database into another SQL Server DB within the same server. I learnt a bit about this copying, that it copies only the Database structure, the Tabels, constraints view stored procedures etc etc. Is there a tutorial where I can get clear instructions to do this. I just need the table structure without any data to be copied into this. Have even tried exploring creating and executing DTS packages but cudn't get much help with this. Any help wud be really appreciated.
Thanks
Hi!
Not sure if this is the right place to post. I need to copy a website that I created on a CD. It is using SQL Server as the database and VB, ASP as front end. However, the problem is that I need to copy it in such a way so that it can be viewed from the CD itseld without needing MSDE or SQL Server. Need help.
Thanks.
I have succeeded in reattaching the db to the new server, but the old logins do not work, as has been experienced by many people.
What follows was is a solution from an earlier post (last year) on this topic. I tried running the queries from Master in on the new server, and got the following error:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'sysusers' with unique index 'sysusers'.
The statement has been terminated.
The sql queries I am using are:
insert sysxlogins(srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language)select srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language
from "oldServerName".master.dbo.sysxlogins
where name not in ('DISTRIBUTOR_ADMIN', 'REPL_PUBLISHER', 'REPL_SUBSCRIBER', 'TRACEUSER', 'SA')
AND
insert sysusers(uid, status, name, sid, roles, createdate, updatedate, altuid, password)
select uid, status, name, sid, roles, createdate, updatedate, altuid, password
from "oldServerName".master.dbo.sysusers
where name not in ('public', 'INFORMATION_SCHEMA', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader','db_datawriter', 'db_denydatareader', 'db_denydatawriter', 'guest', 'dbo')
The old server is set up as a linked server on the new server.
This suggests that the information is all in the new server. Any tips? TIA D. Lewis
Sages,
I have been trying to do this using both DTS and BACKUP/RESTORE. With the former my logins and users get transferred but no one can login. It's as if they are not linked to each other.
With the latter there are no logins to link to the db users. In Sybase we used to bcp the master..syslogins table. Does this work with MSSQL 7.0? Any other suggestions? TIA.
Hi,
Does anyone know how to copy a database from one server to another? I want to backup a production database and restore it to a different server (a test server) as a test.
I used a sample from the MCDBA study book and all I got was two copies of Northwind on the same server. Any idea what I did wrong? Seems like you should be able to backup on one server and restore on another. Here's the code I used:
BACKUP DATABASE Northwind
TO DISK = 'Sd-appsmssql7DataNwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'Sd-appsmssql7DataNwind.bak'
RESTORE DATABASE Northwind2
FROM DISK = 'Sd-appsmssql7DataNwind.bak'
WITH MOVE 'Northwind' TO 'Sd-sqlmssql7DataNorthwind.mdf',
MOVE 'NorthwindLog1' TO 'Sd-sqlmssql7DataNorthwindLog1.ldf'
GO
Thanks for your help.
I have 5 DTS packages that I need to copy to another server. Can someone
please tell me how to do this.
I am a 6.5 DBA and now trying to adjust to 7.0. Big Change.
Thanks for any help.
Dianne
Hello -
I was curious if there is any way to "copy" a SQL Job, alert etc. from one server to another. I want to test jobs on a non production server before putting them out on our live Server. I understand you can do the whole Master server thing, but this temp server is often rebuilt for whatever we need.
Any insights would be greatly appreciated...
Thanks in Advance.
Joe
My production server has 8 databases connected to SAN where all the data resides.
This also has the system databases. There are two internal drives where I store the log files
We will be buying additional internal drives for the server and want to move all data to these drives
and free up SAN for other use.
Do I need to start building this server from scratch or is there an easy way to do this.
I can use sp_attach_db and sp_detach_db too but I prefer backup and restore
Is this the best option:
backup all the databases
uninstall sql server (since system databases are on SAN)
Reinstall sql server
Restore eveything on these new drives
Any ideas will be greatly appreciated
I need to copy records in a table on one server to a similar table on a diferent server. The table definitions are the same but not the data.
I have in mind to use the Transaction Coordinator and the copy should be done within a stored procedure.
I`ve create a table which has 10 fields...Now I have to create 12 more tables which has same fields...
How can i duplicate or copy a table ?
Hi All,
I need to take a copy of a database present in a SQL server to other SQl server using VB.net code.
I can make it out with wizards but i need the query to execute it thru vb.net.
Thanks
Aravind....
I have a 100m row table that I need to come from one database to another database in SQL SERVER.
The bulkcopy feature in DTS is nice -- however is there a stored procedure or external software that will be able to do this outside of DTS.
Right now I am doing a
SELECT *
INTO
(table name)
FROM (table name)
and on a 100m row table it is taking around 52 hours. Not acceptable.
We recently got a new SQL Server 2000. I'm not really a SQL/Network admin but I was tasked to migrate some of our databases in the SQLSVR7 to SQLSVR2K.
I tried using DTS EXPORT but getting errors. Is there a better way to do this?
Any info would be appreciated.
Hi
I am new to SQL Server. I am required to make a copy of a live database, or bring the database down and then make a copy of it.
I need to create a webpage to query the database, but another scheduling program also connects to the database, so that shceduling can be performed
Any help
I want to copy a database from one server to another. I'm happy abouthow to do this but also want to copy a number of DTS packages, jobs andalerts that relate to this database. Is there any way that I can copythem or will I need to create them again on the new server.Many ThanksLaurence Breeze
View 2 Replies View RelatedIm running SQL Server 2005 Express with SQL Server Management studio installed on my laptop. After updating my database, how do I copy the entire contents to the main server in the office, which is running windows server 2003 and SQL Server 2000 Developer Edition.
And is it possible to do it all from my laptop?
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Any inputs will be much appreciated.
Thanks,
MShah
Hi I am running my sql 2000 database server and I just want to copy that database and paste or put on another server which has also run same version of sql 2000 database. I tried to copy and paste the ldf & mdf extensioned files which are located in "C:Program FilesMicrosoft SQL ServerMSSQLData" into my 2 server, but when I open up the enterprise manager, I cant see the new database. So this way dont work, I tried to do something with export and import wizard but it just creates another database and copies the data in that database in the same server. I want to copy that data and put onto another system, please guide me. I'm confused, Thanks a lot
View 5 Replies View Related