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 am working on a sql server SP to delete records based on inactive customer ID.
There is a table A which contains the cust-ID and the status(e.g A-active,i-inactive). My SP is fetching all the inactive cust-id records from table A and deleting the entries from other tables(around 20 tables) based on the cust-id.
I am trying to do the data set-up to test this SP but have some queries:
My approach is to copy all the impacted tables(big size) into another database for testing and tried implementing the below logic :
1)select all(inactive customers) records from table A and store it in a temp table 2) use the below sql query to copy the records
insert into DB2.<tablename> where cust-id in (select * from temp table)
I am getting error while creating the temporary table (SQL version 2008).
1)create temporary table <tablename> - invalid keyword temporary 2) create table #<tablename> - the table is getting created but is not getting deleted thereby if I run it the next time it says table already exists. This doesn't seems to be the property of temp table.
Both the above syntax are not working.
Also, will temporary table be a good way (performance wise) or can I use the below sysntax for all the 20 impacted tables :
insert into DB2.<tablename> where cust-id in (select cust-id from table A where cust-status = 'I')
will it degrade the performance . The records to be copied for testing is around few hundreds for each table.
Before I start asking for favours I think it only polite that I introduce myself. My name is Doug I'm a EC&I engineer from the UK.
I'm developing a recipe managment system with SQL Server 2005 at the database system. I have a table which contains recipe parameters for an industrial process. I would like to generate a stored procedure which will copy either all or just some of the fields from one record in a recipe table to another record in the same table while retaining the unique recipe id. For example, CREATE PROCEDURE sp_CopyRecipes @RecipeNumberCopyFrom int @RecipeNumberCopyTo int AS SELECT Param1,Param2,Param3 FROM tblRecipe WHERE RecipeID = @RecipeNumberCopyFrom (And Copy to) SELECT Param1,Param2,Param3 FROM tblRecipe WHERE RecipeID = @RecipeNumberCopyTo
there are 45 recipe paramters. There is a requirement to copy all paramters or just a selection. I written a procedure in the VB.NET front end which works well, but I'd prefer it to be done on the DB server. Thanks in advance Hope I can contribute in the future
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
declare @table table ( ParentID INT, ChildID INT, Value float ) INSERT INTO @table SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario: -------------- I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank FROM RPL ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for? 2) CTE doesn't work on SQL 2000. I need a query that does. 3) I cannot modify the database itself in the process.
;WITH cte AS ( SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number() OVER(PARTITION BY [LogProcessName] ORDER BY [LogDate] DESC) AS RN FROM [LOGTABLE] WHERE [LogTypeGUID] IN ( SELECT LogTypeGUID FROM LOGTYPE WHERE LogTypeName = 'Process Status Start' OR LogTypeName = 'Process Status End' ) ) SELECT * FROM cte WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6 ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
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
I tried to port 10000 records using DTS. After porting of 9900 records I got an error and comes out without any result. But I want to keep the records which has been ported till the error occured. Plz help me.
Hi, I have had this problem for a while and have not been able solve it.
What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.
then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.
The table name is Adm_disc_Match_tbl
Basically what i have 4 fields. Index_key = which is the patient common link (text) ur_episode = this wil change for each Hospital (text) Admission_datetime = patient admission date and time (datetime) Discharge_datetime = patient discharge date and time (datetime)
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 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.
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 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.
