Need some help with DTS

I need to set up an SQL datbase table to automaticaly update its data from another database (outside of the SQL server). I am not extreemly familiar with SQL server and DTS, so im not sure how to go about this.

What i need to do is;

1. select all records in the destination database that no longer have matches in the source database and set a column in the destination databvase (one not in the source table) to a value, such as "not active". So if the record is deleted in the source database, its only marked as deleted in the destination database.

2. Update columns in the destination database with changes made to the source.

3. Add any new records to the destination database that have been added to the source database.

4. Have this run once a day.

I have done little research and the parts 2 and 3 dont seem like it would be hard to figure out, but the first part I cant seem to find anything on how do get it accomplished.

I am working in SQL Server 7. Any help for this noob to DTS would be appreciate, especialy articles on how to accomplish set 1.Sounds like a case for replication database but don't know if it works on linked database.
Think you can do it with Views, maybe called by DTS

First link source database so you can use it's Alias in views (SourceServer)
Do this in Security\linked servers in SQL Server Enterprice Manager

Make VIEW1 in destination dbase:

SELECT DestinationTable.Record_Id
FROM DestinationTable LEFT JOIN SourceServer.SourceDatabase.Dbo.SourceTable AS ST ON DestinationTable.Record_Id = ST.Record_Id
WHERE (((ST.Record_Id ) Is Null));

Make VIEW2 that uses results from VIEW1:

UPDATE DestinationTable INNER JOIN VIEW1 ON DestinationTable.Record_Id = VIEW1.Record_Id SET DestinationTable.RemarkColumn = "deleted"|||Tried making that View1 lke you said, but its returning all rows in the destination table, not jus the ones that dont have matches in the source table|||Make sure the join is LEFT JOIN. Because of criteria in source (IS NULL) it should only return the ones you want.

Use CAPITAL (IS NULL) in criteria

good luck|||I got that working, thanks.

New problem. SQL server will not let me save a view with an update command in it.

I was thinking i could just use the SQL statement and have it to run once a day. Is there a way to schedule SQL statements to run peridicaly in DTS?sql

Need help writing search query

I am not very familiar with the syntax of MS SQL and I am trying to write a stored procedure which would do a search and return matching records.
This is what I need to achieve:
for instance I create a form with 4 text fields
- First Name
- Last Name
- Employee ID
- Date

I am interested in writing a stored procedure that would run a select query based on the input in the text fields
e.g.
- if the user enters First Name and the Last Name (leaving Employee ID and Date fields blank) query should be something like
select * from Employee where FirstName like @.FirstName and LastName like @.LastName
- or if the user enters only the Employee ID stored procedure should run a query similar to
select * from Employee where EmployeeID like @.EmployeeIDselect *
from Employee
where (FirstName like @.FirstName and LastName like @.LastName)
or
(EmployeeID like @.EmployeeID)|||Thanks for the your time blindman .. that was absolutely brilliant, shows you know your SQL :cool:
This query would sure work for the example I posted, but I was just wondering if I can give user more flexibility and allow him to enter lets say [partial first name (some string with wildcard characters) and partial ID] or [partial first name, last name and ID] or some such wierd combination. The query should adapt to the input and return result accordingly.
What is the best way to go about doing this, again thanks for any help I can get.
Thank you.
Have a great day.|||Yes.

You will need to add more complexity to your WHERE clause to accomplish the logic you want.

You will also need to use the LIKE operator if you want to allow wildcards.

You will also need to expect this query not to run very fast, if you include lots of logical operators and LIKE comparisons in your criteria...|||Since this is in a stored procedure you might use dynamic SQL to create the query on the fly and then execute it.

eg.

create x @.empid int, ...

declare @.sql varchar(200)

set @.sql = 'select * from Employee where '

if empid is not null set @.sql = @.sql & 'EmployeeID =' & @.empid

... etc etc

exec (@.sql)

...|||Thanks ejustuss and blindman, you guys were a tremendous help.
Since I am not very comfortable writing stored procedures this was my solution to the problem .. nothing ingenious but hey as long it works thats all I care about :D.
So I wrote this insanely strict stored procedure which would except user to input all the parameters (first name, last name, Employee ID, Date ... everything) and wrote a query something similar to this:
select * from Employee where FirstName like @.FirstName and LastName like @.LastName and EmployeeID like @.EmployeeID and ....
Since my stored procedure is not giving any flexibility to the user, I added flexibility on the client side in the web form code by replacing all fields left blank by the user with wildcard '%'. So if a user wanted to search by employee's first name, he would just enter the first name leaving other field blanks. These blank fields will be replaced with % and passed to the stored procedure.|||Not sure how effecient this is :

SELECT * FROM Employee
WHERE EmployeeID LIKE ISNULL('%' + @.EmployeeID + '%', EmployeeID)
AND FirstName LIKE ISNULL('%' + @.FirstName + '%', FirstName)
AND LAstName LIKE ISNULL('%' + @.LastName + '%', LastName)
AND DATEDIFF(Day, ISNULL(@.Date, Date), Date) = 0;|||Hey afx thanks for posting your version of the solution. I am sorry but I really did not understand the code you posted. Though your input was definetely useful since using ISNULL is by far a way better more efficient option :cool:
This is how I would use ISNULL

SELECT * FROM Employee
WHERE EmployeeID LIKE ISNULL( @.EmployeeID, '%')
AND FirstName LIKE ISNULL( @.FirstName, '%')
AND LAstName LIKE ISNULL( @.LastName, '%')

Thank you again for the input and effort :) :)|||Hi all,

I need help on building query statement. below is my table called inventory.

idx fabidx coloridx qty isReserved
1 1 1 15 Y
2 1 2 20
3 1 1 10
4 1 1 25 Y
5 1 2 23 Y
6 1 3 26
This is the output that i'm expecting.
fabidx coloridx qty isReserved
1 1 50 2
1 2 43 1
1 3 26 0
I need to get all distinct fabidx and coloridx, i need to get the sum of the distinct fabidx and coloridx, and i need to get the count of "Y" in the isReserved column of the distinct fabidx and coloridx.

Please help. Thanks.|||A simple aggregation query. Look up aggregation function in Books Online, and then post this as a new thread if you still need help.