Monitoring Inserted Data And Comparing Against Selected Data
I made ahuge load script in SQL Server 2000 as i load data from many
tables(select some of each one collumns) into one single table and i
want to test the loaded data against the selected data to make sure
that the loaded data is the same the selected data
is there a code or tool to make this test or monitoring ?? please
urgent ....
View Complete Forum Thread with Replies
Related Forum Messages:
Trigger Problem, Comparing Deleted/inserted Not Working :(
Hello all, I have I trigger where I want to insert all _changed_ rows from the INSERTED table into a table called tempProducts. If I put this query inside my trigger, I selects exactly the rows I want: rows changed Code SnippetSELECT * FROM INSERTED EXCEPT SELECT * FROM DELETED I the current trigger I have Code SnippetINSERT INTO dbo.TempProducts (LBTyp, CountryOfOrigin) SELECT LBTyp, CountryOfOrigin FROM INSERTED but this inserts ALL updated rows, not only the changed ones. So I thought hey, I´ll just combine the two querys and the problem will be solved, like so: Code Snippet INSERT INTO dbo.TempProducts (LBTyp, CountryOfOrigin) SELECT LBTyp, CountryOfOrigin FROM (SELECT * FROM INSERTED EXCEPT SELECT * FROM DELETED) as Temp But for some reason, this won´t work! Why is this? What am I doing wrong?
View Replies !
Bring A Data That Not Already Selected...
Hi I have a problem that seems to be stupid… but still I cant solve it so please if you can help ASAP I'll appreciated. Its 2 tables "Courses" and "Facilitators" The Course table contain: Course_Id and Course_Name The Facilitators table contain: Facili_Id and Facili_Name The two tables have (many to many) relation between them. So I split them into a 3rd table called "Trans" which contain the PK from each Table That will be : "Trans_Course_Id" and "Trans_Facili_Id" I put some data on each table… at least 3 records and related some of them in the "Trans" table Now im looking for an SQL command that brings me All the "Facili_ID" and "Facili_Name" for a specific course. And only those who is not already selected by the same course? Like if I have the data in the "Courses" tableid: 1 and Name: VB Id:2 and Name: C# And in "Facilitators" table: Id:1 and Name: Adam Id:2 And Name: George Id:3 and Name: Sam Now in the relation table "Trans" Course_Id:1 and Facili_Id:1 Course_Id:2 and Facili_Id:1 Course_Id:2 and Facili_Id:3 Now I want the SQL Commands that brings me the he "Facili_ID" and "Facili_Name" For Course_id "For example" and should not be selected by the same course… That would be: Id:2 And Name: George Id:3 and Name: Sam And the same for eash time I pass the course_id for the command Thank you.
View Replies !
Yearly Data From Selected Range
Hi all, I have the Following Query It is working fine if the first month is 01 and Second month is 12 but i want like this if the user selects 200103 to 200203 i want the value of composite should be added from 200103 to 200203 in one row and other row from 200203 to 200303. like this if the user selects from 200004 to 200304 i have to get 3 rows.. that is Yearly data..so how can i modify the following query or any new query. select CONVERT(CHAR(4),period,112), sum(composite) from cdh_price_gap where CONVERT(CHAR(6),period,112) between '200101' and '200312' group by CONVERT(CHAR(4),period,112) order by CONVERT(CHAR(4),period,112) Thanks
View Replies !
Importing Selected Csv Data Into SQL Tables
Hi, I hope you can help me. I've got a csv that I need to import. It's about 74 columns wide and 2500 rows long. What I need to do is select the first 3 columns from the columns from the csv and put them into one table and then put every 7 columns (plus the 1st column as the primary key) into a different table. I've not explained that very well, basically it's like this... A B C D E F G H I J K L M N O P 1 2 3 4 5 Table 1 Data cs# A1 fe B1 cl C1 Table 2 cs# A1 desc D1 date E1 pay F1 amount G1 vat H1 total I1 cReq J1 I'm not really sure were to start. I want to import the data nightly into the tables. I've tried to use the import wizard but I can't seem to select the data I want and exclude the data I don't. Steve
View Replies !
How WCF Can Get New Inserted SQL Data ?
dear all, I have a WCF service which is host in a console application for the time beeing. This service provide methods for retriving history data when request. So far so good. My WCF service need also to know when a particular table (ALARMS tabel ) gets updated with DELETE, INSERT, OR UPDATE. This because my client application (WinForm) need to refresh a datagrid binding to that ALARMS table. In other words my WCF servcie would send a callback event to my client when it as been notice for a change.. But my problem is how my WCF service can be notify from an update in my SQL table ? I have tried SQLDependency class, but I give up was not working properly...and hard to know the xact context you are runing on. Was thinking also of having a timer whcih pool every 1s by calling my tabel store procedure and verifiy is somerow ha changed.... What to do, how to do, what is the best way and thred safe method Thnaks for help and advise regards serge
View Replies !
Monitoring Data Load
I have a user that is loading data via an Access load procedure to a table that actually is a SQL Server 2005 table but is linked to the Access database. He is saying the load is extremly slow. How can I monitor what it is doing on the SQL Server side? Thanks! duckman2007 Have a great day!
View Replies !
Ntext Data Type Cannot Be Selected As DISTINCT
Hello, When I am using DISTINCT in the SELECT clause for a table having a column of datatype "ntext" in SQL 2000 as well SQL 2005, it fails giving the error as "ntext data type cannot be selected as DISTINCT". This is coz "The text, ntext, or image data type cannot be selected as DISTINCT." But I am able to insert into a temporary table having a column whose datatype is nvarchar) using this SELECT clause in SQL 2000. Please advice me how this happens. If I am not worng, implicit conversion has happened. But this insert fails raising the same error in SQL 2005. Please help me to find the difference thanks in advance!
View Replies !
Getting Inserted Data In Trigger
I am using SQL Server 2000.I want to create an after insert trigger on one of my tables, but I have forgotten how I reference the inserted data to do some business logic on it. Can someone please help. Thanks Jag
View Replies !
INSERT INTO - Data Is Not Inserted
hi thereCreated sproc - it stops dead in the first lineWhy ????Thanks in advanceCREATE PROCEDURE [dbo].[test] ASinsert into timesheet.dbo.table1 (RE_Code, PR_Code, AC_Code, WE_Date,SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,WWL_Number, CN_Number)SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_NumberFROM dbo.WWL_TimeSheetsWHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIME,'1999-12-03 00:00:00', 102))GO
View Replies !
Access Inserted Data
i have a oledb destination in my data flow pointing to table ABC and an error output if the insert failed..follow the error output, i have a lookup on table ABC which doesn't seem to work..is it possible to access new data in table ABC follow the error output? thanks
View Replies !
Prevent Data Being Inserted Twice
I have a table with 3 columns: ID, Status, DateTime. I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted. If the procedure was to be run a second time in hour window I do not want any Status to be inserted. Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00. Does anyone know if there is anyway I can gaurd against this?
View Replies !
Comparing SQL Data Against XLS
Hello, I am looking for some "best practice" information on how to properly setup a comparison of SQL data -vs- an Excel spreadsheet. I've never performed this task before, and will likely have to do more in the future. How can I define the range of cells to be used from the xls? There are over 1000 rows in the xls, so a Select * from dbo.table where dbo.column = 'x' just isn't practical. Any information/direction that's out there is most appreciated! Many thanks
View Replies !
Monitoring Data File Size
I am looking to automate monitoring space used for each file in eachdatabase on a SQL Server 2000. Does anybody have any SQL Scripts to dothis or to find the space used?
View Replies !
A Field Or Property With The Name 'Jan' Was Not Found On The Selected Data Source.
I have this Stored Procedure: Create PROCEDURE ListEventas If MONTH(GetDate()) <= 6 Begin SELECT EventTitle, EventDuration, (CASE WHEN MONTH(StartDate) = 1 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jan', (CASE WHEN MONTH(StartDate) = 2 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Feb', (CASE WHEN MONTH(StartDate) = 3 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Mar', (CASE WHEN MONTH(StartDate) = 4 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Apr', (CASE WHEN MONTH(StartDate) = 5 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'May', (CASE WHEN MONTH(StartDate) = 6 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jun'FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)WHERE YEAR(StartDate) = Year(GetDate())group by EventTitle, EventDuration,StartDate,EndDateEnd Else BeginSELECT EventTitle, EventDuration,(CASE WHEN MONTH(StartDate) = 7 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jul', (CASE WHEN MONTH(StartDate) = 8 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Aug', (CASE WHEN MONTH(StartDate) = 9 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Sep', (CASE WHEN MONTH(StartDate) = 10 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Oct', (CASE WHEN MONTH(StartDate) = 11 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Nov', (CASE WHEN MONTH(StartDate) = 12 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Dec'FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)WHERE YEAR(StartDate) = Year(GetDate())group by EventTitle, EventDuration,StartDate,EndDateEnd When I execute it in the SQLExpress, the result returned as expected. But when I bind to Gridview I got this error: A field or property with the name 'Jan' was not found on the selected data source. How do I solve this? I am using drag and drop SQLDatasource to call the Stored Proc and VB.net is the language. Thanks
View Replies !
Copy Selected Column Data From Table To Another During Upgrade Of App
Hi, I need to write a script that will be called during the database upgrade of my application. This is part of reorg of the tables. The script has to get data for say 4 columns from table A and insert it into another table B. Table B has identity insert column and remaining 4 columns matching the ones to be copied. The data is dependent on user database, hence number of records needs to be copied might be different. Also the columns can have null values. I tried using bcp Command as follows.. bcp "select colA,colB,colC,colD from A" queryout "c: empA.dat" -t" " -r"" -c I'm able to get the dat file, but not the format file. Can anyone tell me how to get it using query file with -c option. Also if there is better option to copy data, kindly let me know. This is very critical. Appreciate your help. Thanks, Ramya.
View Replies !
Stored Procedure To Delete Data From Selected Tables
Hi guys I have a database with 50 tables in it. I want to write a stored procedure which will loop through all the table names and delete data from the tables which match a certain criteria i.e. if the tablename begins with "tbl_my". I want to call this procedure from a DTS Package which will then populate data from another Database (ORACLE) into these tables. Can I do all this within one SP? Any help will be appreciated. Thanks
View Replies !
The Text Data Type Cannot Be Selected As DISTINCT Because It Is Not Comparable.
I'm having a problem building a query for an application I'm writing. Basically, there are a bunch of records in a table, and some of them may have the same identifier code. I only want one of each identifier code. The table looks like this: Memo --------- Msg_Identifier varchar(60) Memo_ID int (pk) Subject varchar(64) Message text Priority int Deleted int My query: SELECT DISTINCT Msg_Identifier, Memo_ID, Subject, Message, Priority FROM Memo WHERE Deleted = '0' When I run this query, I get "The text data type cannot be selected as DISTINCT because it is not comparable." I only want Msg_Identifier to be the distinct field, and it's obviously not set up as text. I've used SELECT DISTINCT in the past but I don't ever recall hitting an issue like this. What's the best way to do it?
View Replies !
How To Get The ID Of An Inserted Data In A Stored Procedure
hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure. for example: alter procedure [dbo].[AddDetails] ( @IndustryName nvarchar(50), @CompanyName nvarchar(50), @PlantName nvarchar(50), @Address nvarchar(100), @Createdby int, @CreatedOn datetime ) as begin insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn) insert into Company(Company_Name,Company_Address,Created_by,Created_On,Ind_ID_FK) OUTPUT inserted.Cmp_ID_PK values(@CompanyName,@Address,@Createdby,@CreatedOn) insert into Plant(Plant_Name,Created_by,Creadted_On,Ind_ID_FK,Cmp_ID_FK)values(@PlantName,@Createdby,@CreatedOn,@intReturnValueInd,@intReturnValueComp) end Here iam getting the output ID of the inserted data as OUTPUT inserted.Ind_ID_PK and later i want to insert this to the company table into Ind_ID_FK field.how can i do this. Please help me, i need the solution soon.
View Replies !
Converting Data To Be Inserted Into A Database
Hi,I am using web matrix, and I am trying to insert a data into a MSDE database. I have used webmatrix to generate the update code, and it is executed when a button is pressed on the web page. but when the code is executed I get the error:Syntax error converting the varchar value 'txtAmountSold.text' to a column of data type int.So I added the following code to try to convert the data, but i am still getting the same error, with txtAmountSold.text replaced with "test"dim test as integer test = Convert.ToInt32(txtAmountSold.text)Here is the whole of the function I am using:Function AddItemToStock() As Integer dim test as integer test = Convert.ToInt32(txtAmountSold.text) Dim connectionString As String = "server='(local)Matrix'; trusted_connection=true; database='HawkinsComputers'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _ "], [Description], [image], [OnOffer], [OfferPr"& _ "ice], [OfferDescription], [AmountInStock], [AmountOnOrder], [AmountSold]) VALUES ('CatList.SelectedItem.text', 'txtType.text', 'txtname.text', 'txtmanufacturer.text'"& _ ", convert(money,'txtPrice.text'), 'txtWeight.text', 'txtDescription.text', 'txtimage.text', 'txtOnOffer"& _ ".text', convert(money,'txtOfferPrice.text'), 'txtOfferDescrip"& _ "tion.text', 'txtAmountInStock.text', 'txtAmountOnOrder.text', 'test')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try Return rowsAffected End FunctionAny help in solving this problem would be greatly appreciated, as I am really stuck for where to go next.
View Replies !
Check Inserted Data In A SQL Database
Hi im having problems as im new to ASP.NET C# i have created a button to add details into a SQL database but i want to check the details before i insert the new values from the textboxes can anyone help....... this is what i have to insert into the database........i just want some help to compare the user name eg... if user name exists a message will appear telling the user to change a different user name Thanks private void Button1_Click(object sender, System.EventArgs e) { string connectionString = "server='(local)'; trusted_connection=true; database='tester'"; //System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString); System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString); conn.Open(); string commandString = "INSERT INTO Users (UserName, Password) " + "Values(@UserName, @Password)"; //SqlCommand dbCommand = new SqlCommand (commandString, dbconn); System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand(); //System.Data.SqlClient.SqlCommand myCmd = new System.Data.SqlClient.SqlCommand(queryString, conn); dbCommand.CommandText = commandString; dbCommand.Connection = conn; SqlParameter unParam = new SqlParameter ("@UserName", SqlDbType.NVarChar, 60); unParam.Value = txtUser.Text; dbCommand.Parameters.Add(unParam); SqlParameter paParam = new SqlParameter ("@Password", SqlDbType.NVarChar, 60); paParam.Value = txtPassword.Text; dbCommand.Parameters.Add(paParam); dbCommand.ExecuteNonQuery(); conn.Close(); Response.Redirect ("WebForm1.aspx"); }
View Replies !
Comparing Two Sets Of Data
I have the following situation. One set of data has 274 rows (set2)and anther has 264 (set1). Both data sets are similar in structure aswell as values for both of them were extracts from the same parenttable. Hope the info would substitute DDL. I need to find the "gap"rows between these two sets.Attempted to run a query likeselect count(*)from set2where not exists(select *from set1)did not yield what I desired. What else to try?TIA.
View Replies !
Comparing Data Between Two Tables...
I would like to compare data across two tables. I have partinformation in a table. I get a new set of information periodically.I would like to compare my new info to my old info. I recognize thatdoing a compare of every attribute of every part will take FOREVER. Isthere some way I can do a "diff" based on the columns that I careabout?Thanks!--gloria
View Replies !
Comparing Database Data
Can anyone tell me how a program like this might work:http://www.red-gate.com/products/SQ...mpare/index.htmI want to backup databases into a central repository but I only wantthe records that have changed for that day. This program seems to do itefficiently. Does Sql Server, Oracle, etc offer any sort of built inway of doing this via metadata or a similar mechanism?I want to be able to mirror databases in such a way that I dont need tohave the admin password for them. I don't want to alter the databaseschema in any way.The only way I can think off the top of my head is to assign MD5checksum values to rows and then compare the checksums in the masterand copy, however, to do it efficiently, you'd have to save the MD5values as you go, which would involve altering the schema on the mastertable.
View Replies !
Comparing Data In Two Different Databases
Hi Is there a way of comparing the data contained in two separate databases (on the same server)? I have two databases: DevDB and DB and all I want to verify is that the data in both are *exactly* the same. Is there a nifty function to do this in SQL Server? Thanks Sara
View Replies !
Comparing Data - Urgent
Hello, I need to do a query to compare the same field in two different databases. How can I go about doing that? Can you use an if not exists and how does that work? I also need to compare two numbers in two different databases and then convert that number to a decimal. For example I need to compare 22940 in one column and 22942 in a different column and insert the value of 2.1 in a third column. How can that be done? Thanks in advance, Anita
View Replies !
Comparing Data - SQL Issues
Hi I am relatively new to SQL. Basically i am trying to compare data that doesn't match properly. Firstly one set of data has spaces, secondly the first part of the data is missing. After researching I am going to remove the spaces with the replace and only query the final 8 number - this would dow hat I want but I cannot get it to work. Here is my code. Code: update aleads as a, leads as l set a.lid = l.lid where replace(right(a.htel,8), ' ', '') = replace(right(l.htel,8), ' ', ''); Of course this didn't work but I do not know why - or how to actually achieve this.
View Replies !
Comparing Data Structures
Hello, i am currently in a project involving standardization of SCADA. For this we have two different oracle servers. Is there any possiblility to compare the 2 data structures. THe main reason to compare the data structure is to get a picture on which one is better for us. Since i´m a control engineer, i don´t have any idea about DBMS. I need an effective solution to find the most efficient DB by comparing the existing ones. For me efficiency means, i would prefer a server which requires less maintenance. So how feasible it is, if i use a SQL query tool or a compare tool. I need to know whether i can first of all get a solution using such tools.Tthe DB has all the information related to the process and parameters. As i mentioned earlier i want to figure out the dissimilarities in either DB and want to make an analysis with this solution. I would be happy if someone could help me on this by suggesting some tools or books. THankyou,
View Replies !
Comparing Data In 2 Tables
I need to compare two tables in two different databases and get data that is only in one table. Table 1 in DB1 and Table A in DB_A I need all distinct IDs in Table 1 in DB1 that are NOT in Table A in DB_A and copy those IDs into Table A. Is the sql below correct in obtaining data? Can I add the copy part also to this? select ID from DB1.dbo.Table1 aaa where not exists ( select empID from DB_A.dbo.TableA bbb where aaa.ID = bbb.empID)
View Replies !
Sql Query To Retrieve Comments Based On Selected Month And Data
hi, i am generating report for my project. my table goes like ID VASID VASSID JanMail JanVisit JanPhone JanComment FebMail FebVisit FebPhone FebComment................. DecPhone 1 25 4 True False False Mail me False True False Visit me My report has 3 check boxes for Mail,Visit,Phone respectively and a listbox of 12 months january-december respectively.. once user check on Mail and select a month suppose January in my report,the report should generate only January Comments. here user can select any number of months.if user selects january,february and march, report should generate respective months Comments. Please help me in writing SQL Query to get the comments....... if any one know the solution for the above problem,Please help me Thanks & Regards,
View Replies !
How To Write Selected Table Data To A Text File Daily?
I want to check a table to see what rows have been updated today, then write to a text file some data from the selected rows; then I want to automate this (DTS package? TSQL stored procedure job?) to run every night at midnight. Is the DTS Wizard the best way, that's what I did, but have not confirmed that it is writing a new text file every day, and does each new version write over the old one?
View Replies !
How Do I Extract Data From Selected Rows From My Excel Source File?
The columns in my excel source contain data of different types with the column name being a string and the data in those columns being integers. Is there any way to only extract numeric data , in short I want column names to be omitted. Also the data is distributed unevenly , beggining at various rows in each column. Thanking in advance :)
View Replies !
Import Excel Data From User-selected Source File
Is it possible to import data from an Excel spreadsheet using OPENROWSET or OPENDATASOURCE without having to explicitly define the filepath of the source file? Currently, I have this piece of code within a sproc: INSERT INTO [dbo].[ProductionRequirementDetail] ([ProductionRequirementHeaderID], [SKU], [Quantity]) SELECT @ProductionRequirementHeaderID, [SKU], [LAMPS] FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=C:WeeklySchedule.xls', 'SELECT * FROM [Master$C5:Q65536]') AS XL LEFT JOIN [dbo].[PartMaster] ON (RIGHT([XL].[CODE], 7) = [PartMaster].[SKU]) WHERE [SKU] IS NOT NULL AND [CODE] IS NOT NULL AND [LAMPS] IS NOT NULL AND [LAMPS] > 0 AND [LampTypeID] = @LampTypeID I would like to remove the hardcoded reference 'Database=C:WeeklySchedule.xls' and replace it with a parameter for the filepath. Is this possible? This is in SQL Server 2000. Also, if there is a way to do this with DTS I'd be open to doing it that way too.
View Replies !
Clipping Data Inserted Into NText Field
I'm having a major problem. I'm executing an sql statement to insert data into an nText field. It's clipping the text though. I haven't seen any patterns yet. Example "Welcome to the show.", it would save "Welcome to t". And it's not all the time. Please Help. Thanks for your time, Randy
View Replies !
Deleting Data By Comparing To Another Table
I have an entry form allowing customers to enter up to 15 skus (productid) at a time, so they can make a multiple order, instead of enteringone sku, then submitting it, then returing to the form to submit thesecond one, and so forth.From time to time, the sku they enter will be wrong, or discontiued, soit will not submit an order.Therefore, when they are done submitting their 15 skus through the orderform, I want a list showing them all of those skus that came back blank,or were not found in the database.I'm doing this by creating two tables. A shopping cart, which holds allthe skus that were returned, and a holding table, that holds all theskus that were submitted. I want to then delete all the skus in theholding page that match the skus in teh cart (because they are goodskus) which will then leave the unmatched skus in the holding table.I'll then scroll out the contents of the holding table, to show them theskus that were not found in the database.(confused yet?)So what I want to do is have some sql that will delete from the holdingtable where the sku = the sku in the cart. I've tried writing this, butit dosn't work.I tiried this delete from holding_table where sku = cart.skuI was hoping this would work, but it dosn't. Is there a way for me to dothis?Thanks!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Comparing Multilingual Data--Urgent
Hi, In a table i have a column named 'Name'. It can have multi lingual data. I have desinged a query in which i have a statement as Select Col1 FROm TABLE1 WHERE NAME IN (@NAme) But for MultiLingual names is not compared. I have declared that column as NVARCHAR.
View Replies !
Comparing Data And Integrity Between Two Tables
Hi everyone, I have recently converted my DTS packages to SSIS and deployed them to the new server. I have the 2000 and 2005 server running concurrently, all that is left for me to do is compare the the tables generated by the DTS and SSIS packages to see if they are the same. How do I go about comparing the tables, which are from two different servers using SQL server 2005? Thank you inadvance:) Comparing data and integrity between two tables
View Replies !
Comparing Data In Tables/views.
I am looking for an efficient mechanism to compare data between 2 tables/views. Rationale: I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.
View Replies !
SSIS Data Flow Execution Monitoring And Logging
Has anyone come up/determined a generic way to capture and log indicative information within a data flow in SSIS - e.g., a number of rows selected from the source, transformed, rejected, loaded, various timestamps around these events, etc.? I am trying to avoid having to build a custom solution for each of the packages that I will have (of which there will be dozens). Ideally, I'd like to have some sort of a generic component (such as a custom transformation) that will hide the implementation details and provide a generic interface to the package. It is not too difficult to achieve something similar on the control flow level, but once you get into data flows things get complicated. Any ideas will be greatly appreciated.
View Replies !
Incorrect Data Is Inserted Into The SQL Table From OLEDB Command
I have an OLEDB command in a package that inserts the data into two tables. When I run the package, the data is getting inserted as divided by 100 of original data for derived columns. For example: Say col1 is my input column from flat file with value 1000. I am dividing it by 100 in Derived column and then inserting into the table. So the value 100 should be inserted into the table. But it is not so, the value 1 is getting inserted. Two Tables involved here have referential integrity constraints between them. SQL Script for the operation I am doing looks like the one just below INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable --AutoIncremented column INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,?,?,'act') END But this script did not work in OLEDB commandL So I wrote the below script for which I am facing the problem mentioned INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @Cost AS money SET @Cost=? DECLARE @MarkDownDollars AS money SET @MarkDownDollars=? DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,@Cost,@MarkDownDollars,'act') END
View Replies !
Distributed Data Synchronization - Comparing Timestamp
Hello, I am designing a distributed application where a central SQL Server 2005 database will need to be synchronized with remote SQLExpress databases via a WebService. Data can be edited at the cental db (by our connected applications) or on the local SQLExpress dbs running on the users machines (by this disconnected application). Now, how can I use the timestamp column to determine the most recent update. The most recent update to me is not the user that most recently invoked the syncing WebService, but the most recent time when the change was made to the data locally vs the change time at the central server. Because a user could make a change on his laptop on Monday but not synchronize it till Friday. I dont want the Mon data to overwrite data of Tues-Fri being overwritten simply based on assumption that a late sync is infact the most recent change. The initial data will be downloaded to the local SQLExpress db via the WebService so the timestamp data in the table will come along with it. When a user modifies data in this disconnected SQLExpress db, can that be compared to modifications on the central db using timestamps to determine if the users data being synced is older or newer than the data on the server? I understand timestamps are incremental values, but are they still sensitive to the users timezone since the central server will be a diff timezone then the user.
View Replies !
Comparing Data In Two Tables To Find Missing Records
I have two tables of book information. One that has descriptions of thebook in it, and the isbn, and the other that has the book title,inventory data, prices, the isbn.Because of some techncal constraints I won't get into now, I can'tcombine them both into one table. No problem. Things are going fine aslong as there is a description in the one table to corrispond to theisbn and other data in the other table.However, about half of the products are not yet entered into thedescrition table. I'd like to run a sql query that pulls up all theisbns that don't exist in the other. In other words, I'd like to get aquery that tells me exactly which isbns do not yet have descrition datain them. I know there is some sql that says to search from one filewhere the number does not exist in the other, but it slips my mind. Cansomeone help me on this please?Thank you!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Comparing Data In Two Consecutive Rows From A Single Table
I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.
View Replies !
|
TRACKER
