I have an SQL web site linked to may SQL Server. I am using the IP address t
o
do this.
Then I tried to make an ODBC to link VB6.0 to update some tables in the
linked server, but it always give me an error message saying that the server
doesn't exist or i do not have permitions. I am using the same permitions I
used to create the linked server, so i do not know what else i can do.
can any body help me?Why aren't you simply connecting directly to the server instead of through S
QL
Server's linked server?
If you want to connect to a single source but have data from multiple source
s,
then create stored proces and views that query the linked server. Granted, t
his
will be slower than querying the linked server directly.
HTH
Thomas
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:0C040BE5-A598-489C-9EC9-F681841EE641@.microsoft.com...
>I have an SQL web site linked to may SQL Server. I am using the IP address
to
> do this.
> Then I tried to make an ODBC to link VB6.0 to update some tables in the
> linked server, but it always give me an error message saying that the serv
er
> doesn't exist or i do not have permitions. I am using the same permitions
I
> used to create the linked server, so i do not know what else i can do.
> can any body help me?|||Of course, why didn't I think about it before?
Thanks a lot!
"Thomas" wrote:
> Why aren't you simply connecting directly to the server instead of through
SQL
> Server's linked server?
> If you want to connect to a single source but have data from multiple sour
ces,
> then create stored proces and views that query the linked server. Granted,
this
> will be slower than querying the linked server directly.
>
> HTH
>
> Thomas
>
> "Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in messag
e
> news:0C040BE5-A598-489C-9EC9-F681841EE641@.microsoft.com...
>
>sqlsql
Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts
Thursday, March 22, 2012
Thursday, March 8, 2012
Conditional Join
Hello,
I have the following problem...
I am musing ADO.NET with SQL Server. I have a table that stores events
that are linked to different tables.. for example, lets suppose that I
have the following tables : Airplanes, Cars, Trains. Each table is
different, however, events produced by any of these are stored on a
single Event Table with the following fields:
EventID, OwnerTable, OwnerID, EventTime, Description.
Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
unique ID within the given table. I now want to create a SELECT
statement that shows all events and joins the particular row to the
correct record based on the OwnerTable and OwnerID to show common fields
among all. For example, assume that all three tables have a COLOR
field, the resulting query should produce the following:
Event Table
Event ID: 1
OwnerTable: Airplanes
OwnerID: 1
Color : Red ( This is the COLOR field on the Airplanes table with
AirplaneID = 1)
Event Table
Event ID: 2
OwnerTable: Trains
OwnerID: 1
Color : Blue ( This is the COLOR field on the Trains table with
TrainID = 1)
Thanks,
Jeronimo BertranHi J,
As per the info you have porvided I have created tables and inserted the
data.
The query you require is at the end.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000),
OwnerID Int, EventTime DateTime, Description Varchar(1000))
go
Insert Into plane values ('Blue')
go
Insert Into plane values ('Blue1')
go
Insert Into plane values ('Blue2')
go
Insert Into car values ('Red')
go
Insert Into car values ('Red1')
go
Insert Into car values ('Red2')
go
Insert Into train values ('Yellow')
go
Insert Into train values ('Yellow1')
go
Insert Into train values ('Yellow2')
go
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
go
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
go
--This is your query
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem
Thanks,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:
> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Hi Jeronimo Bertran,
As per the info you have provided, I have created table and populated them
with data. The query you are looking for is at end. I am giving you all the
scripts so that there is no misinterpertation.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000), OwnerID Int, EventTime DateTime, Description Varchar(1000))
GO
Insert Into plane values ('Blue')
GO
Insert Into plane values ('Blue1')
GO
Insert Into plane values ('Blue2')
GO
Insert Into car values ('Red')
GO
Insert Into car values ('Red1')
GO
Insert Into car values ('Red2')
GO
Insert Into train values ('Yellow')
GO
Insert Into train values ('Yellow1')
GO
Insert Into train values ('Yellow2')
GO
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
GO
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
GO
--This is the query you are lookin for
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem.
Regard ,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:
> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Jeronimo
SELECT <column lists> FROM Airplanes A JOIN Events E
ON A.EventId=E.EventId AND A.OwnerID=E.OwnerID
If it does not help please post DDL+ sample data + expected result
"Jeronimo Bertran" <jeronimo.bertran@.newsgroup.nospam> wrote in message
news:eexdtw7FFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran|||Try this:
SELECT E.eventid, E.ownertable, E.ownerid,
COALESCE(A.color, T.color) AS color
FROM Events AS E
LEFT JOIN AirPlanes AS A
ON E.ownerid = A.airplaneid
AND E.ownertable = 'Airplanes'
LEFT JOIN Trains AS T
ON E.ownerid = T.trainid
AND E.ownertable = 'Trains'
Alternatively, why not put those common columns in a single table
across all the types of transport and in the separate tables just have
columns that are specific to that subtype - that would greatly simplify
this type of query.
If Event is some kind of system-maintained audit trail then its maybe
reasonable to hold the applicable table name in there but otherwise I
would suggest that the table name is probably a poor way to identify
the entity. Don't you have identifying codes for the types "Airplanes",
"Trains", etc? In general its best not to mix data and metadata in a
table - doing so suggests that there may be something missing from the
data model.
David Portas
SQL Server MVP
--|||Thanks David,
The COALESCE will do the job. Yes I do have identifying codes for the
different types whcih I can use instead of the table name.|||Thanks Vishal,
Especially for being so thorough. Yor implementation solved my problem.
I have the following problem...
I am musing ADO.NET with SQL Server. I have a table that stores events
that are linked to different tables.. for example, lets suppose that I
have the following tables : Airplanes, Cars, Trains. Each table is
different, however, events produced by any of these are stored on a
single Event Table with the following fields:
EventID, OwnerTable, OwnerID, EventTime, Description.
Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
unique ID within the given table. I now want to create a SELECT
statement that shows all events and joins the particular row to the
correct record based on the OwnerTable and OwnerID to show common fields
among all. For example, assume that all three tables have a COLOR
field, the resulting query should produce the following:
Event Table
Event ID: 1
OwnerTable: Airplanes
OwnerID: 1
Color : Red ( This is the COLOR field on the Airplanes table with
AirplaneID = 1)
Event Table
Event ID: 2
OwnerTable: Trains
OwnerID: 1
Color : Blue ( This is the COLOR field on the Trains table with
TrainID = 1)
Thanks,
Jeronimo BertranHi J,
As per the info you have porvided I have created tables and inserted the
data.
The query you require is at the end.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000),
OwnerID Int, EventTime DateTime, Description Varchar(1000))
go
Insert Into plane values ('Blue')
go
Insert Into plane values ('Blue1')
go
Insert Into plane values ('Blue2')
go
Insert Into car values ('Red')
go
Insert Into car values ('Red1')
go
Insert Into car values ('Red2')
go
Insert Into train values ('Yellow')
go
Insert Into train values ('Yellow1')
go
Insert Into train values ('Yellow2')
go
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
go
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
go
--This is your query
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem
Thanks,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:
> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Hi Jeronimo Bertran,
As per the info you have provided, I have created table and populated them
with data. The query you are looking for is at end. I am giving you all the
scripts so that there is no misinterpertation.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000), OwnerID Int, EventTime DateTime, Description Varchar(1000))
GO
Insert Into plane values ('Blue')
GO
Insert Into plane values ('Blue1')
GO
Insert Into plane values ('Blue2')
GO
Insert Into car values ('Red')
GO
Insert Into car values ('Red1')
GO
Insert Into car values ('Red2')
GO
Insert Into train values ('Yellow')
GO
Insert Into train values ('Yellow1')
GO
Insert Into train values ('Yellow2')
GO
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
GO
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
GO
--This is the query you are lookin for
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem.
Regard ,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:
> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Jeronimo
SELECT <column lists> FROM Airplanes A JOIN Events E
ON A.EventId=E.EventId AND A.OwnerID=E.OwnerID
If it does not help please post DDL+ sample data + expected result
"Jeronimo Bertran" <jeronimo.bertran@.newsgroup.nospam> wrote in message
news:eexdtw7FFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran|||Try this:
SELECT E.eventid, E.ownertable, E.ownerid,
COALESCE(A.color, T.color) AS color
FROM Events AS E
LEFT JOIN AirPlanes AS A
ON E.ownerid = A.airplaneid
AND E.ownertable = 'Airplanes'
LEFT JOIN Trains AS T
ON E.ownerid = T.trainid
AND E.ownertable = 'Trains'
Alternatively, why not put those common columns in a single table
across all the types of transport and in the separate tables just have
columns that are specific to that subtype - that would greatly simplify
this type of query.
If Event is some kind of system-maintained audit trail then its maybe
reasonable to hold the applicable table name in there but otherwise I
would suggest that the table name is probably a poor way to identify
the entity. Don't you have identifying codes for the types "Airplanes",
"Trains", etc? In general its best not to mix data and metadata in a
table - doing so suggests that there may be something missing from the
data model.
David Portas
SQL Server MVP
--|||Thanks David,
The COALESCE will do the job. Yes I do have identifying codes for the
different types whcih I can use instead of the table name.|||Thanks Vishal,
Especially for being so thorough. Yor implementation solved my problem.
Subscribe to:
Posts (Atom)