|
I doubt very much that a hosting provider will allow you to configure a linked server; this needs to be done by a system administrator of the SQL Server. You should email the tech support folks at your hosting provider and ask them directly.
An alternate solution would be to create a webservice on your hosted site which would process datachanges from your Oracle system. You could create a folder somwwhere on your server and write an XML file which represents the data change, then have a Windows service monitor that folder for new files and when it sees one, call the webservice to update the SQL database.
It is not a real-time solution, but it would be pretty close.
Good luck.
|
|
|
|
|
Thanks David........
I used Linked Server,and My system administrator tell me that we are not hosting this in public domain,we host it on local IIS and we buy public IP and connect local IP to public IP.
Is this possible?
Is this secure?
|
|
|
|
|
Ah ha. I see.
From my understanding if the SQL server and the Oracle Server are local, then the communication between the two should be pretty secure. I would ensure that the user accounts that you are using from your web server has the minimum privilege necessary to get the job done. You don't want to be connecting as "sa" incase your application gets hacked.
During your application development, make sure you use parameterized queries to limit the exposure to SQL injection.
|
|
|
|
|
Uma Shankar Patel wrote: Yes I tried this,
If you did, than why did you not mention that with your question? What else did you try?
Uma Shankar Patel wrote: does Hosting provide provides Linked server facility?
No. It's something that's built in to Sql Server, and it requires a database-connection to the remote database-server. The remote host will not allow remote connections, to prevent people from abusing them as a cheap alternative for databases.
You could fetch the latest info from the remote host itself; generate Xml using PHP locally on the MySql machine, read it from your code by fetching the webpage, and updating your database.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
If your hosting provider allows, you can create a DB Link to SQL Server from your Oracle database. Write triggers in the Oracle tables and update the SQL Server tables through the DB Link.
|
|
|
|
|
|
I have two tables "CM_Master" and "CM_Details" and the sample records are below, i want to make a report like under the result. How can i join two tables and create table heading like the shift names in CM_Master table.
CM_Master
ShiftID ShiftName
1 Day
2 Night
CM_Details
CM_ID Site_ID Level_ID ShiftID Number
1 1 1 1 5
2 1 2 1 4
3 1 1 2 2
4 2 2 2 8
Result
Site Level Day Night
1 1 5 2
1 2 4 0
2 2 0 8
|
|
|
|
|
Try the below approach
CREATE TABLE #CM_Master
(
ShiftID INT, ShiftName VARCHAR(50)
)
INSERT INTO #CM_Master
SELECT 1, 'Day' UNION
SELECT 2, 'Night'
CREATE TABLE #CM_Detail
(
CM_ID INT, Site_ID INT, Level_ID INT, ShiftID INT, Number INT
)
INSERT INTO #CM_Detail
SELECT 1, 1, 1, 1, 5 UNION
SELECT 2, 1, 2, 1, 4 UNION
SELECT 3, 1, 1, 2, 2 UNION
SELECT 4, 2, 2, 2, 8
SELECT DISTINCT CD.Site_ID, CD.Level_ID, ISNULL(A.Day,0) AS [Day], ISNULL(B.Night,0) AS Night
FROM #CM_Detail CD
LEFT JOIN
(
SELECT Site_ID, Level_ID,
SUM(Number) [Day]
FROM #CM_Detail
WHERE ShiftID = 1
GROUP BY Site_ID, Level_ID
) A ON CD.Site_ID = A.Site_ID AND CD.Level_ID = A.Level_ID
LEFT JOIN
(
SELECT Site_ID, Level_ID,
SUM(Number) AS [Night]
FROM #CM_Detail
WHERE ShiftID = 2
GROUP BY Site_ID, Level_ID
) B ON CD.Site_ID = B.Site_ID AND CD.Level_ID = B.Level_ID
and here is a generic approach where shift names are not hard coded.
DECLARE @PivotColumnHeader VARCHAR(MAX)
SELECT @PivotColumnHeader = COALESCE(@PivotColumnHeader + ', [' + ShiftName + ']', '[' + ShiftName + ']')
FROM #CM_Master
DECLARE @SQL VARCHAR(MAX)
SET @SQL = N'SELECT * FROM
(
SELECT Site_ID, Level_ID, ShiftName, ISNULL(Number,0) AS Number FROM #CM_Detail CD
INNER JOIN #CM_Master CM ON CD.ShiftID = CM.ShiftID
) P
PIVOT
(
SUM(Number) FOR ShiftName IN (' + @PivotColumnHeader + ')
) AS PivotTable'
EXECUTE (@SQL)
modified 28-Aug-12 4:05am.
|
|
|
|
|
Is it possible to make the sql dynamic, instead of hard code.
eg: Day, Night some time the master table have more records.
|
|
|
|
|
Robymon wrote: Is it possible to
Yes, that's possible. The correct response to the answer would have been "thank you".
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I have modified the solution to include the dynamic query.
|
|
|
|
|
Hi all, suppose I have an app that allows users to search for people based on location, degree held, skills, and experience. How would I join the following tables to achieve what I need:
Person Table
PersonID -----Pk
First Name
Last Name
City
State
Education Table
EducationID --PK
PersonID -----FK
Degree
Skills Table
SkillID ------PK
PersonID -----FK
Skill
JobHistory Table
HistoryID ----PK
PersonID -----FK
Experience
Thanks in advance for your help.
modified 26-Aug-12 19:17pm.
|
|
|
|
|
As your joins are many to one (History to Person) you are going to need a number of queries and/or views to get all the different results.
I would create view(s) that joined all the tables, using inner joins where where the FK is required and left joins where the FK is optional and then search that view.
Alternatively use joins via the PersonID FK.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
select p.* ,e.*,s.*,jh.*
from persons p
inner join Education e on e.PersonID = p.PersonID
inner join Skills s on s.PersonID = p.PersonID
inner join JobHistory jh on jh.PersonID = p.PersonID
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
This looks great but what about the conditions? For instance I want to find someone who meets the following conditions:
Education = Masters
Skills = Computer Networking
Experience = 6 years
Also I would like the search to match lower case spelling of Masters and Computer Networking, how are those 2 things I've just mentioned achieved. Thanks for your help.
|
|
|
|
|
Add a where clause with the conditions and user LOWER(Education) = 'masters'
You are screwed with the 6 Years, this should have been stored as a numeric and then you could use Duration > 6
You may also want to look into SOUNDEX filtering for the text fields
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Select TblA.*,TblB.*,TblC.*,TblD.* from persons TblA
inner join Education TblB on TblB.PersonID = TblA.PersonID
inner join Skills TblC on TblC.PersonID = TblA.PersonID
inner join JobHistory TblD on TblD.PersonID = TblA.PersonID
|
|
|
|
|
Hi all,
I have a table called Singer and another called Song in Access. The Singer table has a primary key of SingerID and the Song table has a primary key of SongID and a foreign key of SingerID. I'm trying to add data to the Song table and I'm getting the error, "You cannot add or change a record because a related record is required in table 'Singer'. Why am I getting that error? Any help will be greatly appreciated, thanks in advance for your help.
|
|
|
|
|
A Singer must exist before a Song can be sung by the singer.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
modified 25-Aug-12 12:48pm.
|
|
|
|
|
Hi Eddy, thanks for replying.
So you are saying that I will get the error message I mentioned if I try to add a song into the Song table before I add an singer for that song in the Singer table?
|
|
|
|
|
Yes. The song you try to enter does not have a (valid) SingerId. Create a singer, like "Fat Lady", give her number 1 and enter your song with her SingerId. You'll find that the song will be accepted.
If you want to "point" to other tables from your Song-table, then the data in the other table has to exist before the Foreign Key is entered.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
When I wrote something similar a few years back, I created Artist and Title tables and a PlayList table to allow many-to-many relationships between them because a song may be performed by multiple artists.
C:\Projects\KCDXlogger>dbt kcdx "describe Artist ; describe Title ; describe Playlist
DBT V6.0 -- Simple SQL interface Sir John E. Boucher 2003
describe Artist
Name Type1 Type2 Nullable Unique Read only
---- ------------- -------------------- -------- ------ ---------
Id System.Guid DBTYPE_GUID True False False
Name System.String DBTYPE_WVARCHAR(255) True False False
2 records affected.
describe Title
Name Type1 Type2 Nullable Unique Read only
---- ------------- -------------------- -------- ------ ---------
Id System.Guid DBTYPE_GUID True False False
Name System.String DBTYPE_WVARCHAR(255) True False False
2 records affected.
describe Playlist
Name Type1 Type2 Nullable Unique Read only
-------- --------------- -------------------- -------- ------ ---------
CreTim System.DateTime DBTYPE_DATE True False False
Text System.String DBTYPE_WVARCHAR(255) True False False
PlaTim System.DateTime DBTYPE_DATE True False False
ArtistId System.Guid DBTYPE_GUID True False False
TitleId System.Guid DBTYPE_GUID True False False
5 records affected.
|
|
|
|
|
Thank you all for replying. I have solved this problem. It is as someone had said, you can't create a song before you have a singer.
|
|
|
|
|
Yes, which is very limiting, no?
|
|
|
|
|
PIEBALDconsult wrote: Yes, which is very limiting, no?
Yes, it is. But that was not his original question, it was about referential integrity. So his statement can be rewritten as
you can't create a song before you have singers.
|
|
|
|