Copying Objects
Mar 5, 2003Is it possible to copy tables/indexes/data from one db to another (on the same server) while specifying a new owner & w/o logging?
View 11 Replies
ADVERTISEMENT
DTS Error When Copying Objects
Jan 4, 2005I am getting the following error when executing a Copy SQL Server Objects Task. If it helps these objects are User Defined functions and also this had worked in the past it is only after changing the destination server to one that is offsite, has a different OS then the source and also runs as a DC. We are running SQL 2000 Server Standard with Spk 3a on both boxes.
Step 'DTSStep_DTSTransferObjectsTask_6' failed
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 42S02)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetRightsAbbreviations'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetRights'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetTerritoryAbbreviations'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetTerritories'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetShow'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetTvEpisodes'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetTvSegments'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GetTvSegmentsString'.
Step Error code: 800400D0
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131
Copying Server Objects
Sep 30, 2005I 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 RelatedCopying Encrypted Objects ?
Jan 26, 2007Hi,
I would like to copy a function from one sql 2005 database to another, but the function is encrypted so cannot use the script to window commands etc... Is there a way of copying encrypted objects from one sql 2005 db to another? I don't really care to know the contents of the function.
Any help appreciated.
James.
Copying Database Objects And Data From Oracle 8 To SQL 2005
May 16, 2007Does anyone know how to copying database objects and data from Oracle 8 to SQL 2005 ?
View 1 Replies View RelatedBug?? Transfer SQL Server Objects Task - Error When Copying Views
Nov 20, 2006I'm trying to copy data over using a Transfer SQL Server Objects Task however I get problems trying to copy over views. I get the similar error (pre sp1) when using import/export wizard and copying over views. Now with SP1 the views just don't transfer. I'm transferring from SQL 2005 to SQL 2005. The error I get is:
Error: 0xC002F363 at Transfer Data (NEW), Transfer SQL Server Objects Task: Table "vw_XXXX" does not exist at the source. Task failed: Transfer Data (NEW)
I know the view exists at the source because:
A) I can select it in the Views List Collection within the Transfer SQL Server Objects Task
B) I'm using the view on the source
Somehow the views are not transferring. Anyone have any ideas?
Putting Names Of Objects To Control-flow Loop Creating Objects
Dec 27, 2006please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies View RelatedSQL Server 2012 :: Select Data From XML - Objects Within Objects?
Nov 20, 2013passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
Copying DBs
Jul 8, 2004What is the Syntax for a statement that can copy one MS SQL DB to another Database (on a different server). Or is this even possible?
View 3 Replies View RelatedNeed Help In Copying Sps
Jul 31, 2007Dear friends
i want to copy all stored procedures in one drives. if i do manually it will take whole day. i have to change udd length in all sps.so please anybody give sugessions
Copying XML File Into SQL
Aug 17, 2006Hi, I have been having fun and games for well over a week now trying to get an xml file copied into an sql file, but still have no joy. I and using the bulk copy to do this and think I am close to solving it but just need a final push in the write direction.
In green below is the full source code, I think that the trouble is with the try statement part here:
Try
bulk.WriteToServer(xd)
This comes up with the error :
Unable to cast object of type 'System.Xml.XmlDocument' to type 'System.Data.IDataReader'
Basically how do I get the XML data into a reader (or IDataReader) format so that the writetoserver command can interpret it
I would be so greatful if someone could help resolve this it is becoming increasingly more frustrating
Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim objXML
Dim objXSL
' destination table
Dim connectionString As String = ConfigurationManager.ConnectionStrings("Database1ConnectionString").ConnectionString
Dim myNewConnection As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(connectionString)
myNewConnection.Open()
Dim productreader As XmlReader
Dim bulk As Data.SqlClient.SqlBulkCopy = New Data.SqlClient.SqlBulkCopy(myNewConnection)
bulk.DestinationTableName = "Product"
Dim productcount As Integer = 0
Dim settings As New System.Xml.XmlReaderSettings()
settings.IgnoreWhitespace = True
settings.IgnoreComments = True
Dim xs As String = (Server.MapPath("~/App_Data/XMLfile.xml"))
Using reader As XmlReader = XmlReader.Create(xs, settings)
While (reader.Read())
If (reader.NodeType = XmlNodeType.Element And "product" = reader.LocalName) Then
productcount += 1
End If
End While
End Using
Response.Write(String.Format("found {0} products!", productcount))
Using bulk
bulk.DestinationTableName = "Product"
Dim xd As New System.Xml.XmlDocument()
xd.Load("C:Documents and SettingsSimonMy DocumentsVisual Studio 2005WebSitesWebSite1App_Dataxmlfile.xml")
Dim xr As New System.Xml.XmlTextReader(Server.MapPath("~/App_Data/xmlfile.xml"))
bulk.ColumnMappings.Add("TDProductId", "TDProductId") ' map First to first_name
bulk.ColumnMappings.Add("name", "name") ' map Last to last_name
bulk.ColumnMappings.Add("description", "description") ' map Date to first_sale
bulk.ColumnMappings.Add("imageUrl", "imageUrl") ' map Amount to sale_amount
bulk.ColumnMappings.Add("productUrl", "productUrl") ' map UserID from Session to user_id
bulk.ColumnMappings.Add("price", "price")
bulk.ColumnMappings.Add("currency", "currency")
bulk.DestinationTableName = "Affilaite_Product_new"
Try
bulk.WriteToServer(xd)
Catch ex As Exception
Response.Write(ex.Message)
Finally
myNewConnection.Close()
bulk.Close()
xd = Nothing
End Try
End Using
End Sub
Tables Copying
Dec 24, 2007
i have sql local database in the application . I want to copy the table from one local database to another. here the detination table is already created with
one field which is incremental and other field is image and some other fields are text. any solutions on how to do it
Copying A Database
Mar 27, 2004Hi all,
I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks!
Vik!
Copying The Database
Aug 18, 2004Hi,
I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.
Thanks in advance,
Uday
Help-for Copying Database
Oct 11, 2004hi,
i m new 4 sql server.
i create one app. in asp.net.
i create one database in sql server.
now,
i want to make setup for my app.
so,
i want copy database and put on c:/..../wwwroot/app folder.
how can i do this?
one more things,
i create database using enterprise manager.
i want to see my database file.
where it is stored?
somebody help me.
it's urgent.
thanks in advance.
Copying From One Column To Another
Dec 22, 2004It's all in the same table
How do I copy something from one column to another, but limiting it to only the first "x" characters?
so if i wanted to copy just 'ABC' instead of 'ABCDEFG' how could i do that?
thanks in advance
Copying Data From PRD To TST
Apr 28, 2006I've got two DBs in the same SQL instance. They are named TST and PRD. I am using 2.0 so there are many ASP generated tables also. Every once in a while I want to refresh data from PRD to TST. But I don't want to copy the data from ASP tables.What is the easiest way to do so?
View 6 Replies View RelatedDB Question: Copying ID
Jun 10, 2006Hi,
I'm trying to insert one value (an order) into the table Order and (via a for-loop) all the products in that order in the table Product, hence, one order can have multiple products (and must have at least one). I have an automatically increased value for the OrderID as the primary key for Order, and I have a foreign key named OrderID in the Product table. So far, I _think_ everything's logically correct.
However, I don't understand how to retrieve the OrderID to be able to insert it in the Product table upon insertion. I guess this is done all the time, but the only solution I can think of is to make a new SQL Command, asking for the just created OrderID to use it in the SQL Command for the products' for-loop. I'm sure that's a bad idea. :-)
Can I use relationships or so to make this automatically updated (that is, to have the Product table "check for" the OrderID and insert the OrderID upon insertion of the Product row(s))?
I hope this is clear to you. Thanks in advance for all help!
Pettrer
Copying Logins
May 6, 2002Hello List,
Folks, I need a help here. We are running our production database on SQL Server 7.0. We are in a process of switching the production database server to another server hardware. I was wondering, How can we copy the Logins/Passwords from my old server to the new one. Well this is very important to have all the old logins and passwords on the new server to have anybody login to it. I know, Doing Export will copy the logins. But would it allow the same old password to be on the new server?
Please help me here.
DTS Not Copying All Records From SQL
Jul 20, 2001I have a SQL Table called Consumers that contains 495,037 records.
The record size is about 350 bytes and it has one field that is a varchar (255). I am using DTS to copy this file from SQL (7.0) to Access (97). Last week this file had about 216,000 records and the DTS Process worked okay. Today, it has 495,037 records and the DTS Process appears to lose some records (about 26,000) during the copy. In the DTS Package the SQL query is just a straight SELECT statement with no WHERE condition.
During the DTS Process the SQL Consumers Table is being copied to and Access Table. The error I am getting during the DTS Process is "Error at Destination for Row # 468608".
When I open the Access Table it shows 468,608 as the actual number of rows in the Access Table and shows ConsumerID 675820 (primary key, identity field) for this particular record.
When I link to the SQL Table via Access the Record # 468,608 shows ConsumerID 643852 and the last record shows Record # 495,037 and ConsumerID 675820.
This tells me that some records are not being copied over during the DTS Process. The last record on the SQL Table is on the Access Table. So there are probably 26,429 (495,037 on SQL Table minus 468,608 on Access Table) that are not on the Access Table.
The DTS Select Query does not have a Where Condition. Do you think some records are being lost because of the size of the SQL Table?
There is an Advance Tab on the DTS Properties Page; there are some
options like 'Insert Commit Size', Fetch Buffer Size', 'Use Fast Load', 'Keep NULLS', and 'Check Constraints' that I
need to take a further look at. Have you used any of these options and do you think any particular one may help
my problem?
There is also and 'Exception File Name' option. I will give this a try next week to see if it will write the problem
records to disk.
Thanks for your help, Kevin
Copying VIEW From One Db To Another
Aug 23, 2001I have been trying to copy a VIEW from one database to another, but SQL 7 doesn't let me. I use the DTS wizard and it looks like it happns but then it doesn't appear, and I have tried to refresh. Can someone please tell me what I am doing wrong?
TIA,
Bruce David
Copying DTS Package
Sep 24, 2001Hello,
This may seem like an easy question, but I have a DTS package that I need to copy to another SQL server. Is this done easily? Can it be done (i think so?).
Thanks in advance,
Jim
Copying DAT Files In 6.5
Mar 30, 2000I have an 8 GB SQL 6.5 database that I need to move to another server. I know I can create the devices and the database on the new server exactly as they were created on the old server then replace the new DAT files with the old (after shuting down SQL Server). The issue I am running into is that the sort order and character sets on the two servers are different. Will this have an effect if I copy the DAT files? Am I better off using BCP? I'd hate to use BCP because of the time that it will take to move the files.
Thanks for you help
DTS Moving/copying
Jul 23, 1999Does anyone know how to move or copy DTS Package?
That I need to do:
I would have to move database from one server to the other
and last thing I want to do is recreate DTS packages from
scrach.
I could not find any way of transfering DTS packages.
Any help greatly appreciated
Copying A Table From One Db To Another
Mar 5, 2001I have 2 databases on the same sql server. Both have the same tables. I need to copy the contents of tableA from the source database to TableA on the destination database. Can I do this using a SQL script or some kind of script, and if so how and what would the syntax be.
please reply via e-mail also...res2100@yahoo.com
Copying Tables
Dec 22, 2004hi,
I have a database called marketing in it i have a table called products and right now there are five products in the table with product_id as 8003,8004,8005,8006,8007 i want to create the same table in the database but my product_id should start from 1 and i only want three products from the old table to be copied into the new table any idea how to make this happen.
thanks,
belord
Copying A Row From One Table To Another
Mar 18, 2005Hi, I'm pretty new to SQL so am still learning the syntax etc.
I have 2 tables, one called Railways and another called Sheet1$ (an import from Excel).
In Railways, I have 5 Fields:
ID
First_Name
Last_Name
File
Full_Name
At the moment, all are populated with data (300 records) but Full_Name is showing values of <NULL>.
I want to populate Full_Name with the Data in Sheet1$ which has the field Full_Name (which is populated with the data I need)
What is the query I need to run to get SQL to copy the data from Sheet1$ (Full_Name) into Railways (Full_Name) - replacing all of the <NULL> values?
I've tried using the import wizard but without success.
Thanks in advance.
Copying SQL Diagrams
Feb 26, 2002Is there a way to export a database diagram? No preference as to the format of the exported file.
Thanks!
Copying DTS Packages
Jun 11, 2001Is there a way to copy saved DTS packages from one server to another?
Thanks!
Ellen
Copying SQL, Mdf File / Please Help
Nov 27, 2004I have a SQL db on my server in internet
the server and my DB is online
I want to copy mdf file to my computer via FTP but I get an error
that file is in use and I can`t copy it
I wanted to know is there any way that i copy information in that MDF fle
to another DB on my computer
Its very important please help me
thanks all
COPYING A DB From One Server To Another
Feb 15, 2005I 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.
About Copying Table..
Jan 8, 2008just wondering how can i script out so that i can copy a table from a database but not the data itself..
.
Polling Changes On A Db, And Copying Changes To Another Db
Apr 3, 2008Hi all, I'm in the process of cleaning up a very VERY poorly implemented system. One of the problems I'm tackling consists of :
1 - Database A
2 - Database B (different schema than A)
3 - A task which regularly polls A, checks for updated/new records and inserts them (with some transformation) into B
As you can imagine, the overhead of the polling is killing the system. What I want to know is :
What is the best way for A to notify B whenever there are changes to a particular table on A ?
I'm running 2000 but we will most probably upgrade to 2005.
thanks in advance to everybody
Omar