Update the taskname as 'projectname' + '_' + 'workname' for any projectid.
projectname and workname coming from the projectid and workid in the task record
so the task table becomes
1,project1_work1,1,1
2,project1_work2,1,2
3,project2_work3,2,3
I can get all the records doing this
SELECT p.projectname+ ' ' + w.workname AS 'NEWNAME'
FROM task t
JOIN work w
ON t.workid = w.workid
JOIN project p
ON t.projectid = p.projectid
WHERE projectid = 2
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 am trying to join two tables on multiple fields. But the nulls aren't considered a match so they aren't included in the results set.
Select A.Lot, A.Block, A.Plan, B.Key from A join B on A.Lot=B.Lot A.Block=B.Block A.Plan=B.Plan
In the data, there can be an instance where Block is null in both tables so it "matches" but not in SQL. How do I get the "matched" nulls to be returned as well?
Hello,I need to do a seach in multiple columns for a certain word. With SQLI have to use one specific column right?I.E. select * from DB where Column1 like '%search%'That works.But what if I want to seach multiple columns in the table for thesearch word?You can't do this:select * from DB where Column1, Column2, Column3 like '%search%'Is there a way to do this?Thanks,Tmuld.
Dear all,Our application needs a bit of database redundancy.Our application only accesses database for reading purposes.We want to have two databases in separate machines. Incase one databaseserver dies the application should automatically extract data from theother server.Is there any JDBC driver available so that it can detect failure in themain database server and then tries to extract data from the standbydatabase server?Kind regards--Posted via http://dbforums.com
I have a single .asp page that opens a connection and then sequentiallyopens and closes 14 recordsets from stored procedures to obtain variousproduct information before closing the connection.Is it common practice to do something like this? Or is opening 14recordsets going to become a real problem when the page goes live and startsgetting high web traffic?Thank you in advance for any information you might provide.Dave
I need to update information for a user and if the user is classified as a primary (@blnPrimary) then I need to update information for all users within his agency (AgencyUniqueId). The issue is that the second UPDATE to "cdds_User_Profile" always returns a rowcount of 0 (should be 1) even though the values for "@Original_AgencyUniqueId" and "@Original_UserId" are correct. This is just a snippet of the whole procedure. I'm trying to implement similar logic in other parts of the procedure and I'm observing the same behavior there as well. Any help anyone can provide is greatly appreciated. </p><pre>/*** Update User Profile ***/UPDATE [cdds_User_Profile] SET [FirstName] = @FirstName, [LastName] = @LastName, [Title] = @Title, [Phone] = @Phone, [AcctType] = @AcctType, [AcctStatus] = @AcctStatus, [LastUpdatedDate] = GETDATE() WHERE ([FirstName] = @Original_FirstName AND [LastName] = @Original_LastName AND [Title]=@Original_Title AND [Phone]=@Original_Phone AND [AcctType]=@Original_AcctType AND [AcctStatus]= @Original_AcctStatus AND [AgencyUniqueId] = @Original_AgencyUniqueIdAND [UserId] = @Original_UserId);IF @@ROWCOUNT = 0BEGINSET @err_message = 'Data has been edited by another user since you began viewing this information.'RAISERROR (@err_message,11, 1)ROLLBACK TRANSACTIONRETURNEND IF @@ERROR <> 0 BEGINROLLBACK TRANSACTIONRETURN ENDIF @blnPrimary = 1 BEGIN IF LOWER(@AcctStatus) <> LOWER(@AgencyAcctStatus)/*** Update Users Acct. Status ***//* update all users in same agency profile */UPDATE [cdds_User_Profile] SET [AcctStatus] = @AcctStatus,[LastUpdatedDate] = GETDATE() WHERE ([AgencyUniqueId] = @Original_AgencyUniqueIdAND [UserId] = @Original_UserId); IF @@ROWCOUNT = 0BEGINSET @err_message = 'Data for this agency has been edited by another user since you began viewing this information.'RAISERROR (@err_message,11, 1)ROLLBACK TRANSACTIONRETURNENDIF @@ERROR <> 0 BEGINROLLBACK TRANSACTIONRETURN ENDEND</pre><pre>
Hey everyone, I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model. The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me. Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the Table Property. I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time. Jeremy
Is it possible to generate mulitple reports from parameter? what I want is for example: I have a report with a sales orders table, it has 2 input parameter fields; two dates to make up a date range. If the date range is between yesterday and today, I will get two reports (two reports with two different total page number); one is for yesterday and one is for today.
I did some research on resetting the total page, but their solution doesnt work on my special case (very complicate..). A similiar work out also apperciate.
I have a DLL that acts as an interface between the application and the database. So you create a new instance to the database via:
MyDatabaseClass db = new MyDatabaseClass()
Each instance of the class creates a new connection to the database.
So if I were to have the following, then 4 connections would be made.
MyDatabaseClass db1 = new MyDatabaseClass() MyDatabaseClass db2 = new MyDatabaseClass() MyDatabaseClass db3 = new MyDatabaseClass() MyDatabaseClass db4 = new MyDatabaseClass()
My question is...What if instead of creating new connections upon each instantiation, I re-use the same connection through a "ConnectionManager" class. In this way, the above code will only create 1 connection instead of 4.
What do you guys think about each approach? Obviously the 2nd approach makes sense for non-web applications, but what about websites? Would that just cause that single connection to be overloaded??
I have several ResultSet Querys/Statements within my page. An exampleof the code looks like this:ResultSet rs1 = stmt1.executeQuery("SELECT right('' + '$' + convert(varchar,SUM(ActivePrim),1), 15) AS 'ActivePrim',right(' ' + '$' + convert(varchar,SUM(KGAP),1), 15) AS'KGAP', right(' ' + '$' +convert(varchar,SUM(PrimaryRepo),1), 15) AS 'PrimaryRepo', right('' + '$' + convert(varchar,SUM(WeeklyTotal),1), 15) AS'WeeklyTotal' FROM Intranet..InsuranceStats WHERE EmployeeName ='Jamie' and Date BETWEEN '01/01/04' and '01/31/04'");What would be the correct way to retrieve each result set? Icurrently have it as the example below. But this doesn't allow foreach result set to be displayed separately.<td valign=top><b>Active Primary:</b><%= ActivePrim %></td>Any help would be greatly appreciated.Catherine
How to assign multiple values to emp1 variable separated by comma Ex: If ajay karthik vijay are employee names in emp table then emp1= ajay,karthik, vijay set emp1= (select employeename from emp) returns error error: subquery returns morethan one value
We shall be taking a bunch of 7.0 instances and moving/upgrading to a SQL 2000 cluster server. I was thinking of creating new named instances on the 2000 cluster and upgrading each 7.0 server to it's respective named instance. Also thought of using the 2000 copy database wizard; I was told this didn't always work. Anyone hear of problems with this? Thanks
I'm sure there is probably a very easy solution that I am just not seeing or can't Google...
I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas?
hello, I have several tables that have guids as their primary keys and the tables are related as follows: Table1 - primary key = ServiceNo (Guid), Filter Key = CampaignNo Table2 - primary key = CostBasisNo (Guid), Foreign Key = ServiceNo (from Table1) Table3 - primary key = UserId, Foreign Key = ServiceNo (from table1) Table4 - primary key = SourceServiceNo (Foreign Key from Table1), MemberServiceNo(Foreign Key from Table1) what I need to do is copy all records from Table1 where CampaignNo = @CampaignNo and insert them into table1, this I can do easily but I will generate a new ServiceNo for each one and associated a new CampaigNo which is fine. The problem comes in that I need to also copy the contents of Table2 = Table3 for all ServiceNos that have been copied from Table1 but insert the new Guid that will have been created when copying the rows in Table1 This is further compounded when I need to do the same to Table4 but this time I need to insert the newid's for SourceServiceNo and the related MemberServiceNo which all would have changed. I haven't the first clue where to start with this task, do I need to use temporary tables, cursors? any help gratefully received, even if it's a pointer to the most efficient approach. regards
I'm fairly new to the SSIS world, and I've recently ported a bunch of dts packages over to SSIS. I'm an ASP.NET developer so I'm very familiar with the capabilities that configuration files give you, and I attempted to set up my solution as follows:
All of my "Data Sources" are at the project level, and added (with the same name) to each package. I wanted to have a single config file that had all of the project-level settings (i.e. connection strings, data file paths, etc). I then have a config for each package with the package level settings - i.e. variables, etc.
The problem becomes that all packages do not use all data sources. This results in an error when I try to open up a package for editing, it complains that it doesn't have a reference to data source XYZ that it is seeing in the configuration file.
Is there any way that I can get around this? If I have a password to a database change, I don't want to have to look through every config file and change it in multiple places.
I would like to update Table 1 with the data from Table 2. Here is my problem..Lets say that I have two records in Table 2 that have the same policyNumber but two different NewEmpNames, it only takes the first. In other words, a single policynumber can be moved to a New EmpName and then again later on to another NewEmpName adn even again if need be
I am trying to update a field within one table with the values from another table. With the criteria that another field in each table are equal. What is the correct way to do this. My syntax is all wrong.
I have table1 which has many unique ID numbers and table2 that has many records for each ID. some of the ID numbers in table1 have changed and I have created a translation table (table3) that links the old and new ID numbers.
What I need to do is some sort of update sql statement that updates all the records in table2 changing all the oldID numbers to the new ones using the translation table.
Table1 and table2 are not linked...can anyone help me with the sql statement
example
Table 1 IDNUM NAME 12345 Joe 12346 Mary 12347 David
Good day to all, I am new here so i hope i am doing things correctly.
The Company i work for make coils of shaped wire and work a 6 - 6 shift pattern
I have a database that is updated from a data collection source (MS Access) at 06:00 every morning. This seems to be working ok, my problem is that most coils fit nicely into the 6 - 6 shift pattern, but some now and again drift over into the next shift. I have written a crystal report that picks up this data. at the moment the coils are put in the database as: [Coil Start Time], [Coil Finish Time], [Coil Start Weight], [Coil Finish Weight], etc.
I have written (been helped to write) a SQL statement that will do the following:
Step 1: If the Coil Finish time is greater than the shift end time, then set the shit end time to be coil end time and zero start and finish wheight. Step 2: The original Coil record is duplicated and Coil Start time set to start time of shift, all other data left alone.
Example of code:
-->>
SELECT [Batch Name], [Batch Start], [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift], [Operator ID], [Works Order No] FROM dbo.tblCoilData WHERE (DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch End]) < 18) OR ((DATEPART(hour, [Batch Start]) < 6 OR DATEPART(hour, [Batch Start]) >= 18) AND (DATEPART(hour, [Batch End]) < 6 OR DATEPART(hour, [Batch End]) >= 18)) UNION ALL SELECT [Batch Name], [Batch Start], DATEADD(hour, 17, DATEADD(minute, 59, CONVERT(char(10), [Batch End], 101))), 0, 0, [Product], [Shift], [Operator ID], [Works Order No] FROM dbo.tblCoilData WHERE DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch Start]) < 18 AND (DATEPART(hour, [Batch End]) < 6 OR DATEPART(hour, [Batch End]) >= 18) UNION ALL SELECT [Batch Name], DATEADD(hour, 18, CONVERT(char(10), [Batch Start], 101)), [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift], [Operator ID], [Works Order No] FROM dbo.tblCoilData WHERE DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch Start]) < 18 AND (DATEPART(hour, [Batch End]) < 6 OR DATEPART(hour, [Batch End]) >= 18) UNION ALL SELECT [Batch Name], [Batch Start], DATEADD(hour, 5, DATEADD(minute, 59, CONVERT(char(10), [Batch End], 101))), 0, 0, [Product], [Shift], [Operator ID], [Works Order No] FROM dbo.tblCoilData WHERE (DATEPART(hour, [Batch Start]) < 6 OR DATEPART(hour, [Batch Start]) >= 18) AND DATEPART(hour, [Batch End]) >= 6 AND DATEPART(hour, [Batch End]) < 18 UNION ALL SELECT [Batch Name], DATEADD(hour, 6, CONVERT(char(10), [Batch Start], 101)), [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift], [Operator ID], [Works Order No] FROM dbo.tblCoilData WHERE (DATEPART(hour, [Batch Start]) < 6 OR DATEPART(hour, [Batch Start]) >= 18) AND DATEPART(hour, [Batch End]) >= 6 AND DATEPART(hour, [Batch End]) < 18
<<--
I have 2 options now
option 1: Leave this as a SQL View and report from this
option 2: Insert updated records to the tblCoilData table so that the data in the table is permanent
I would prefer option 2 but am a bit of a nugget when it comes to writing update / insert statements, Could someone please help me with this
I have one table of new records (tableA) that may already exist intableB. I want to insert these records into tableB with insert if theydon't already exist, or update any existing ones with new data if theydo already exist. A column (Action) in tableA already tells me whetherthis is an INSERT, UPDATE, or DELETE. I'm able to derive that I can doan insert withselect * into tableB from tableA where Action = 'INSERT'....and I think I can handle the delete.But I'm stuck on the update. How do I do the update? An ordinaryUPDATE statement just won't do unless I use a cursor to cycle throughthe recordset. I want to avoid a cursor.
How to use db-lib to update/insert database records without using SQLlanguage. I want to change the value of the data individually withoutplugging in the new values in the SQL language then execute it. Theperfect situation for me is loop through the retrieved records thenedit the values individually until EOF.Thanks,Carlo
I'm fairly basic in SQL and I was wondering if it was possible to update the id field of a certain table in all records of that table. And to update the links to those ids in other tables.
