DTS - using multiple data sources


How do I use a view or query that references two different data sources ie 1 Oracle (APPPCT_PROJECT_TYPE ) and 1 table within my Sql. (ProjectTypes)

I want to use a query that looks and the data in the Oracle table compares it against my current table to determine if there are differenes and if so update current table with the differences in the data.

This is the query I would have used in Access: Help me to convert it use using DTS.



INSERT INTO ProjectTypes ( ProjectTypeCode, ProjectTypeDescrip, ProjectCatagory, ProjectClass, CapitalProjectNum, DateCreated, UserCreated, DateModified, UserModified )



SELECT APPPCT_PROJECT_TYPE.ID, APPPCT_PROJECT_TYPE.DESCR, APPPCT_PROJECT_TYPE.PROJ_DESCR_CATEGORY, APPPCT_PROJECT_TYPE.PROJ_CLASS, APPPCT_PROJECT_TYPE.CPN, Now() AS Expr1, "import" AS Expr2, Now() AS Expr3, "import" AS Expr4



FROM APPPCT_PROJECT_TYPE LEFT JOIN ProjectTypesON APPPCT_PROJECT_TYPE.ID = ProjectTypes.ProjectTypeCode



WHERE (((ProjectTypes.ProjectTypeCode) Is Null));



Any assistance in this mater is greatly appreciated.



Sincerely,

Karen

Posted On: Tuesday 20th of November 2012 04:26:33 AM Total Views:  414
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Error Accessing MSAccess database on network drive

On a network drive accessed by all users,I have created a directory with all security permissions to . Copied the MSAccess database to this directory and granted full control to Everyone.From a personal web server on my machine , I am trying to access a MSAccess database on a network drive and get the following error.I also created a folder on my C: drive.When I change the database path/namt to clannedactivitypact.mdb everything works fine.But when I try to access the database on the network drive, I get the following errorError Type:Microsoft JET Database Engine (0x80004005)The Microsoft Jet database engine cannot open the file 'gperationssupportplannedactivitypact.mdb'. It is already opened exclusively by another user, or you need permission to view its data./html/html/Jon/jon_entry.asp, line 79Browser Type:Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) Page:GET /html/html/Jon/jon_entry.asp Would appreciate some help with thi
VIEWS ON THIS POST

454

Posted on:

Tuesday 20th November 2012
View Replies!

How do you create an upload on a form so that the file gets sent to the database?

, I have a form written in JScript and ASP and I need to create an upload area for files of any type, ...etc. The data in the form currently gets sent to a SQL database. How does the uploaded file get to the sql database I read somewhere that the field type is "image" The other challenge is that an email gets sent to a person with a link to view the web based form with all the data. But how is this person going to view the uploaded file Ideally I'd like for them to receive the file as an attachment. Is this possible Please help! Thank you, Barnes
VIEWS ON THIS POST

463

Posted on:

Tuesday 20th November 2012
View Replies!

Script to insert 1000 rows in a database table

Please guide me as to how to write a script to insert 1000 rows in a table automatically for SQL Server. the table has structure like Table1(id int,name varchar, date datetime,currency money) Thanx in advance.
VIEWS ON THIS POST

258

Posted on:

Tuesday 20th November 2012
View Replies!

Help with my connection to SQL Security database!!!

I am trying to connect to the Security table to authenticate users. It works well on my box when Im logged in but it isnt working when I have another user try to connect or when I try to connect remotely. I get a SQL error on the aspx page (System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON') but I have already removed the anonymous login for IIS and I have the connection string set to SSPI (.ConnectionString = "data source=servername;initial catalog=Security;Connection TimeOut=30;integrated security=SSPI)with the web.configs identity set to impersonate=true />. Do you have any other ideas or do you know of another way I can authenticate users and get a return value\t\t
VIEWS ON THIS POST

333

Posted on:

Tuesday 20th November 2012
View Replies!

relocating datafile in sql server

hi could any one please tell me how to relocate datafile in sql server TIA Anand
VIEWS ON THIS POST

350

Posted on:

Wednesday 21st November 2012
View Replies!

website triggered by a database

dear everybody,i have a project about a website that is triggered by a database. it has a log-in page using SQL server. mynstructor also required that there be a "session" so that it does no goes directly to the HTML. i also would like to know about a HTML which connects to a database.i really don't know where to start.please help me ASAP
VIEWS ON THIS POST

225

Posted on:

Wednesday 21st November 2012
View Replies!

Error Openning database.

everybody...My web application works fine, but when some different people try to open the databse, I get the following error...Microsoft JET Database Engine error '80004005' Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt. /fotografia/despliegafotoeng.asp, line 310 The line of code where the error appears is when I try to open the database.The application works fine, but when I put a link to my application in a public forum (when two or more people are trying to open the database, it crashes.)I'm using Access 97 database.I have contacted with support area , and they tell me that this error happens when 2 or more people are trying to open the database at the same specific time, but the problem is that the error remains until the server is restarted.Is there any solution\tplease help.Roberto Ayala.
VIEWS ON THIS POST

193

Posted on:

Wednesday 21st November 2012
View Replies!

Need help to export data to EXcel

iam developed a module. but i want to take data backup every week related to that module. for that i want to generate one button. by clicking that button i want to import tat data in to EXCEL file. any one can help me and save me from tis problem.
VIEWS ON THIS POST

389

Posted on:

Monday 26th November 2012
View Replies!

App data

hi,' I m fresher IN MVC Framework I have install sql server 2005 and, visual studio..2008. I m using a mvc 1.0frame work in visual studio 2008, I m trying to connect to data base.from app data,using sql server data base..templete(database.mdf),but does not connect some exception is there.. { connection to sql server files(*mdf) sql server express 2005 to function properly.please verify the installtion pf componentvor download from url//go.microsoft.com/flink/link=49251}, Ihave download express sp3.but is not working second one exception.. {Error 2 The type or namespace name 'LoginDBContext' could not be found (are you missing a using directive or an assembly reference) D:\Login\Login\Login\Controllers\LoginController.c s 12 9 Login } plz help it..
VIEWS ON THIS POST

248

Posted on:

Monday 26th November 2012
View Replies!

Free sqldata recovery software

Hi members thanks for the good work i need help on getting a free tool for recovering sqlserver 2000 data from an mdf file . Provide me with links to it iam completely stuck please help me
VIEWS ON THIS POST

192

Posted on:

Monday 26th November 2012
View Replies!

ODBC connection to two databases

Does anyone know if it is possible to connect to multiple databases in one connection. I have an application where i can only select one connectionstring or ODBC.
VIEWS ON THIS POST

236

Posted on:

Monday 26th November 2012
View Replies!

Transferring SQL Server database to host server

, I have created a web site with SQL server 2005 express database using visual studio 2008 c# asp.net. I have tested the application on my local host and its all working fine. However I moved it over to the host server by simply copying all .mdf and .ldf files using ftp. I then tested it there and I get the error below: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Is there anything I was suppose to do to the database before publishing it Would be very grateful for any ideas.
VIEWS ON THIS POST

254

Posted on:

Monday 26th November 2012
View Replies!

Insert data into 2 tables in a single shot

Hi , In SQL Server, Can we load data into two table from a single table in a single shot something like we do it as below: select * into from IS there any way to achieve this
VIEWS ON THIS POST

270

Posted on:

Monday 26th November 2012
View Replies!

Calculating datediff on without using weekends, etc.

I use the following query to gather info for a report ... as you can see I calculate the amount of time bewteen open and closed (days, hours, minutes) and open and onsite the same way. Which, btw, works perfectly. [highlight=SQL] select t.ticketnumber as [Ticket #], c.fName+' '+c.lName as Contact, t.openeddate+' '+t.openedtime as [Date Open],t.closeddate+' '+t.closedtime as [Date Closed], case when datepart(yyyy,closeddate) '1900' then cast(datediff(mi, openeddate+openedtime, closeddate+closedtime)/60/24 as varchar(10))+' day(s) '+ cast(datediff(mi, openeddate+openedtime, closeddate+closedtime)/60%24 as varchar(5))+' hr(s) '+ cast(datediff(mi, openeddate+openedtime, closeddate+closedtime)%60 as varchar(5))+' min(s) ' else '' end as [Time to Close], t.problem as [Problem], t.resolution as [Resolution], t.strOnsite as [Onsite Date], case when t.strOnsite is not null then cast(datediff(mi, openeddate+openedtime, strOnsite)/60/24 as varchar(10))+' day(s) '+ cast(datediff(mi, openeddate+openedtime, strOnsite)/60%24 as varchar(5))+' hr(s) '+ cast(datediff(mi, openeddate+openedtime, strOnsite)%60 as varchar(5))+' min(s) ' else '' end as [Time to Onsite] from ticket_master t, contact_master c where c.company = 4 and t.contactID = c.id and openeddate >= dateadd(mm,-1,getdate()) and openeddate < dateadd(dd,-1,getdate()) or c.company = 4 and t.contactID = c.id and closeddate > dateadd(mm,-1,getdate()) and closeddate < dateadd(dd,-1,getdate()) order by t.openeddate, t.openedtime [/highlight] Now they only want to count Mondays through Fridays (Saturday/Sunday) should not be used in the calculation, nor should anything outsite of 8:00AM t0 5:00pm ... Here's my question, can this be done I have a function that will count business days, and I guess I can then convert that to hours ... but how will I get the number of days, hours, minutes ... based on 5 days a week and 8 hours a day as opposed to 7 days a week and 24 hours a day.
VIEWS ON THIS POST

160

Posted on:

Monday 26th November 2012
View Replies!

Comparing two colums in different tables using a join to find non match?

and gals, I have two tables and need to delete some strays in two related tables. The column names are idProduct and Product_id. I wrote an asp script to import and reverse an import except I had forgotten to reverse one of the two tables. So consiquently there are 151 stray records that I need to find. How would I write a join, or not to find these strays and delete them Thank you so much in advance. cbadchris
VIEWS ON THIS POST

166

Posted on:

Monday 26th November 2012
View Replies!

How to output part of feild using sql

Hi all. i got a huge list of mp3 that they are in diffrent folders. I put all the songs infor in mysql database in a table called files. I want a query db that output foldernames for me .The folder name is part of feiled called filename in files table. The value of filename is like this stored in db : http://localhost/wimpy5recursive/mp3s/album1/song1.mp3 http://localhost/wimpy5recursive/mp3s/album2/song4.mp3 where the bold part is the foldername. i want to print folder name.Note:mp3s is the root folder for mp3s.The whole point is to list all folders name and link to page that holds songs inside those folders. Furthermore, i want a query all feilds info for songs in each given folder.What i mean is that for each folder it lists all songs info iside that folder.I want to be able to tell the query what folder i want to list its song
VIEWS ON THIS POST

146

Posted on:

Monday 26th November 2012
View Replies!

using SQLserver db dynamically

I would like to create program that load SQLserver database file from a mobile disk (eg. flash disk), the db should be flexible to move to any other computer and SHOULD NOT stay in the harddisk i've been thinkin of solution: 1. i should do automatically attach of db to the sqlserver everytime i load the program 2. auto detach the db when finish using the program 3. and the file will be in .mdf instead of a backup file (since backup file need restoration and restoration will restore file to the harddisk ) is my solution correct or is there any better solution
VIEWS ON THIS POST

212

Posted on:

Monday 26th November 2012
View Replies!

Running SQLserver general Function using scripts

2 functions i need to perform by using scripts : clear connection (in detach db option) and setting server authentication mode (either windows only or windows and sqlserver) how do i run these functions in scripts or should i just call the stored procedures in master db\t which sp should i call to perform these functions , wif the parameter (pls give example) thx
VIEWS ON THIS POST

204

Posted on:

Monday 26th November 2012
View Replies!

View over multiple databases

i have 2 databases (on 1 instance of sql server) i would like a view in database A bases on data in database B 1) is this possible 2) if yes, how do i do this 3) how are permissions and user involved
VIEWS ON THIS POST

274

Posted on:

Monday 26th November 2012
View Replies!

Trouble using fn_get_sql to capture table changes

I created an trigger (on insert and update) on a table that uses the new fn_get_sql function that comes with SQL Server 2000 SP3. I use it to record who performed what transaction and when they did it. It looks like this: -------------------------------- CREATE TRIGGER Update_Last_Modified ON [dbo].[MYTABLENAME] FOR UPDATE, INSERT AS BEGIN SET NOCOUNT ON DBCC TRACEON (2861) DECLARE @Qry nvarchar(4000) DECLARE @handle binary(20) SELECT @handle = sql_handle FROM master..sysprocesses WHERE spid = @@SPID SET @QRY = (SELECT CONVERT(nvarchar(4000), [text]) FROM ::fn_get_sql(@handle)) UPDATE MYTABLENAME SET DATE_LAST_MODIFIED = GETDATE(), LAST_COMMAND = @QRY, LAST_USER = SYSTEM_USER FROM inserted WHERE MYTABLENAME.UID= Inserted.UID END ---------------------------------- It was previously coded to use DBCC INPUTBUFFER, and it worked fine, but I was limited to the first 255 characters of the command, which prevented me from seeing the critical parts, like the where clause! When I modified the trigger to use fn_get_sql, all I ever see is the entire text of the create trigger command. Maybe I should use an entirely different approach. I'm open to ideas.
VIEWS ON THIS POST

198

Posted on:

Monday 26th November 2012
View Replies!