Does 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.
I have two servers, one is 2005 standard, the other is 2005 express. I'm using express to record test results and it will be keep recording non-stop. So, what I want to do is I want to transfer data from express to standard using SSIS package, and as soon as data gets transferred the data to no longer exists in express.
I have a job running the "copy" package every minute, but I don't know how to delete the data in express. Does anyone know how to help me out pleaseeeee..?
Ok. So we just moved over one DB from our original server to another server. The DB copied over fine.
Our next step is trying to see if there is a way to move accounts. Is that possible?
We have about 40 accounts (using SQL authentication) on the original server. Is there a way to move or copy those accounts to the new SQL server that is holding that same DB?
Haven't found anything in the Enterprise manager, so im thinking it will be something to do in the SQL analyzer.
My lack of knowledge in SQL is not helping me much here.
When you copy a sequence container between dtsx you obtain a different size at destination. Any way to avoid this? I'd like to see that task with the same size.
Hi I have a (possibly) common position where half of our IT department is SQL 2005 and the rest is SQL2000. For myself, having to work in a SQL2000 environment and needing data from a SQL2005 Cluster I came up with this solution. Also, to alert me when the process starts and completes since it is part of a scheduled process, I have it do a RAISERROR with logging to record entry and exit times. In addition, this runs out a series of PRINT statements that lets the operator know what table is currently being worked on.
Of course, any suggestions for speeding this up would be helpful!
What I have found that works for getting data (albeit slow) from SQL 2005 down to SQL 2000 is to script a fairly simple copy process -
-- it is actually pretty easy to follow if you just read the code......
-- first, we find all the views which are present, so they can be ignored when copying the raw data
CREATE TABLE #VIEWS (TABLE_NAME NVARCHAR (255) )
INSERT #VIEWS SELECT TABLE_NAME FROM OPENDATASOURCE( 'SQLOLEDB', Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' ) .SQL_SIS_SYSTEM.INFORMATION_SCHEMA.VIEWS
-- now, we get the actual tables with data CREATE TABLE #TEMP (TBL_SCHEMA VARCHAR (12), TBL_NAME VARCHAR (255), RECCNT INT )
INSERT #TEMP SELECT TABLE_SCHEMA , TABLE_NAME FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' ) .SQL_SIS_SYSTEM.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM #VIEWS)
-- then, we start copying tables over - now we tag the ones which are populated with at least 1 row. -- the first cursor loop gets all table names, the second will find row counts of source tables. -- this segment uses (ugh) cursor to loop through and gather all of the data. This cursor is at the table name level - not -- processing anything, and is used only to find tables which have a rowcount > 0 -- believe it or not, the cursors run pretty darn quickly since they arnet doing any calculations - just finding -- tables with row counts > 0
SET @QUOT = CHAR(39) SET @LBRAKT = '[' SET @RBRAKT = ']' SET @IUT = 'IsUserTable' SET @ODBC_CMD ='(' + @QUOT + 'SQLOLEDB' + @QUOT + ',' + @QUOT + 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT + ').SQL_SIS_SYSTEM.dbo.' PRINT 'BEGIN TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) ) DECLARE GETEM CURSOR FOR SELECT TBL_SCHEMA, TBL_NAME FROM #TEMP OPEN GETEM FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME
WHILE @@FETCH_STATUS = 0 BEGIN
SET @SQL = 'UPDATE #TEMP SET RECCNT = ' + '(SELECT COUNT(*) FROM OPENDATASOURCE(' + @QUOT + 'SQLOLEDB'+ @QUOT + ',' + @QUOT +''Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT + ').SQL_SIS_SYSTEM.' + @TBL_SCHEMA + '.' + @TBL_NAME +')' + ' WHERE TBL_NAME = ' + @QUOT + @TBL_NAME + @QUOT EXEC (@SQL) FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME END CLOSE GETEM DEALLOCATE GETEM
I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
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
Hi, 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
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
Hi 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!
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.
I'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?
Hi, 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
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?
I 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.
I 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?
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?).
I 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.
I 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.
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.
I 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
I 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?
Hi 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.
I am assuming there is some functionality to copy and paste a table in sql server..however I am not seeing where I can do this what I want to do is this:
create table1 as select * from table 2;
Please let me know how I can create a new table as a replica of an existing table. Thanks so much!
I'd like a really simple way of making a replica of a table. The thing is i'd like the table name to be a variable. The following code doesn't work, any ideas??
Thanks in advance,
Alph
CREATE Procedure Test
@vMonth as varchar(3)
As
SELECT tbl_Targets.* INTO @vmonth FROM tbl_Targets; GO