Distributed Query?

Hi,
I am trying to do a distributed query from within my SQL 7 database, the
server has been linked inot mine and I have done a stored procedure which is
on the linked server which works perfectly, however when I try and put it
onto mine I can;t seem to get it right. Here is my code, would just need
some help on where I would place the linked server name and database name
within the code - any help on this would be most appreciated.
SELECT dbo.LPI.UPRN as UPRN,
isnull(convert(varchar(5),dbo.LPI.PAON_START_NUM),'') + ' ' +
isnull(convert(varchar(5),dbo.LPI.PAON_START_SUFFIX),'') + ' ' + case when
paon_end_num = 0 then '' else cast(paon_end_num as varchar(20)) end + ' ' +
isnull(convert(varchar(30), dbo.STREET.DESCRIPTION),'') as ADD1,
dbo.LPI.SAON_TEXT as ADD2, dbo.LU_LOCALITY.LOC, dbo.LU_COUNTY.COUNTY,
dbo.LU_POSTCODE.POSTCODE
FROM dbo.LPI INNER JOIN
dbo.LU_POSTCODE ON dbo.LPI.POSTCODE_REF = dbo.LU_POSTCODE.POSTCODE_REF INNER JOIN
dbo.STREET ON dbo.LPI.LPI_USRN = dbo.STREET.USRN INNER
JOIN
dbo.LU_LOCALITY ON dbo.STREET.LOC_REF = dbo.LU_LOCALITY.LOC_REF INNER JOIN
dbo.LU_COUNTY ON dbo.STREET.COUNTY_REF = dbo.LU_COUNTY.COUNTY_REF
WHERE POSTCODE = @.P_CODE
Thank you
DamonRefer to the 3(4) declaration of objects:
Servername.Databasename.Ownername.Ojectname (sample: Server01...Customers),
you don´t need to name all the names, if they are unique.
So you could use a alias to Name the objects
SELECT Username from
Users U1 join
Server02.Test..Users U2
where U1.Name = 'Test' and U2.name='Clean the House'
HTH, Jens Süßmeyer.
"Damon" <damon@.nospam.co.uk> schrieb im Newsbeitrag
news:GY1xb.268$Bh1.223@.newsfep1-gui.server.ntli.net...
> Hi,
> I am trying to do a distributed query from within my SQL 7 database, the
> server has been linked inot mine and I have done a stored procedure which
is
> on the linked server which works perfectly, however when I try and put it
> onto mine I can;t seem to get it right. Here is my code, would just need
> some help on where I would place the linked server name and database name
> within the code - any help on this would be most appreciated.
> SELECT dbo.LPI.UPRN as UPRN,
> isnull(convert(varchar(5),dbo.LPI.PAON_START_NUM),'') + ' ' +
> isnull(convert(varchar(5),dbo.LPI.PAON_START_SUFFIX),'') + ' ' + case when
> paon_end_num = 0 then '' else cast(paon_end_num as varchar(20)) end + ' '
+
> isnull(convert(varchar(30), dbo.STREET.DESCRIPTION),'') as ADD1,
> dbo.LPI.SAON_TEXT as ADD2, dbo.LU_LOCALITY.LOC,
dbo.LU_COUNTY.COUNTY,
> dbo.LU_POSTCODE.POSTCODE
> FROM dbo.LPI INNER JOIN
> dbo.LU_POSTCODE ON dbo.LPI.POSTCODE_REF => dbo.LU_POSTCODE.POSTCODE_REF INNER JOIN
> dbo.STREET ON dbo.LPI.LPI_USRN = dbo.STREET.USRN
INNER
> JOIN
> dbo.LU_LOCALITY ON dbo.STREET.LOC_REF => dbo.LU_LOCALITY.LOC_REF INNER JOIN
> dbo.LU_COUNTY ON dbo.STREET.COUNTY_REF => dbo.LU_COUNTY.COUNTY_REF
> WHERE POSTCODE = @.P_CODE
>
> Thank you
> Damon
>
>|||Thanks for the reply but not exactly sure what you are getting @..
Where do I need to place the server name and database names? I stick them
after the 'from' and before the dbo.LPI, dbo.LU_POSTCODE, dbo.STREET,
dbo.LU_LOCALITY and dbo.LU_COUNTY but then it comes up with a whole load of
errors?
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:OoeHpMCtDHA.1756@.TK2MSFTNGP09.phx.gbl...
> Refer to the 3(4) declaration of objects:
> Servername.Databasename.Ownername.Ojectname (sample:
Server01...Customers),
> you don´t need to name all the names, if they are unique.
> So you could use a alias to Name the objects
> SELECT Username from
> Users U1 join
> Server02.Test..Users U2
> where U1.Name = 'Test' and U2.name='Clean the House'
> HTH, Jens Süßmeyer.
> "Damon" <damon@.nospam.co.uk> schrieb im Newsbeitrag
> news:GY1xb.268$Bh1.223@.newsfep1-gui.server.ntli.net...
> > Hi,
> >
> > I am trying to do a distributed query from within my SQL 7 database, the
> > server has been linked inot mine and I have done a stored procedure
which
> is
> > on the linked server which works perfectly, however when I try and put
it
> > onto mine I can;t seem to get it right. Here is my code, would just
need
> > some help on where I would place the linked server name and database
name
> > within the code - any help on this would be most appreciated.
> >
> > SELECT dbo.LPI.UPRN as UPRN,
> > isnull(convert(varchar(5),dbo.LPI.PAON_START_NUM),'') + ' ' +
> > isnull(convert(varchar(5),dbo.LPI.PAON_START_SUFFIX),'') + ' ' + case
when
> > paon_end_num = 0 then '' else cast(paon_end_num as varchar(20)) end + '
'
> +
> > isnull(convert(varchar(30), dbo.STREET.DESCRIPTION),'') as ADD1,
> > dbo.LPI.SAON_TEXT as ADD2, dbo.LU_LOCALITY.LOC,
> dbo.LU_COUNTY.COUNTY,
> > dbo.LU_POSTCODE.POSTCODE
> > FROM dbo.LPI INNER JOIN
> > dbo.LU_POSTCODE ON dbo.LPI.POSTCODE_REF => > dbo.LU_POSTCODE.POSTCODE_REF INNER JOIN
> > dbo.STREET ON dbo.LPI.LPI_USRN = dbo.STREET.USRN
> INNER
> > JOIN
> > dbo.LU_LOCALITY ON dbo.STREET.LOC_REF => > dbo.LU_LOCALITY.LOC_REF INNER JOIN
> > dbo.LU_COUNTY ON dbo.STREET.COUNTY_REF => > dbo.LU_COUNTY.COUNTY_REF
> > WHERE POSTCODE = @.P_CODE
> >
> >
> > Thank you
> >
> > Damon
> >
> >
> >
>|||4 Point Declaratio
Servername.DatabaseName.OwnerName.Objectname
If there are errors please post them,or post the whole SQL Statement.
HTH, Jens Süßmeyer.
"Damon" <damon@.nospam.co.uk> schrieb im Newsbeitrag
news:My2xb.290$Eb.349801@.newsfep2-gui.server.ntli.net...
> Thanks for the reply but not exactly sure what you are getting @..
> Where do I need to place the server name and database names? I stick them
> after the 'from' and before the dbo.LPI, dbo.LU_POSTCODE, dbo.STREET,
> dbo.LU_LOCALITY and dbo.LU_COUNTY but then it comes up with a whole load
of
> errors?
>
> "Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
> news:OoeHpMCtDHA.1756@.TK2MSFTNGP09.phx.gbl...
> > Refer to the 3(4) declaration of objects:
> >
> > Servername.Databasename.Ownername.Ojectname (sample:
> Server01...Customers),
> > you don´t need to name all the names, if they are unique.
> >
> > So you could use a alias to Name the objects
> >
> > SELECT Username from
> > Users U1 join
> > Server02.Test..Users U2
> > where U1.Name = 'Test' and U2.name='Clean the House'
> >
> > HTH, Jens Süßmeyer.
> >
> > "Damon" <damon@.nospam.co.uk> schrieb im Newsbeitrag
> > news:GY1xb.268$Bh1.223@.newsfep1-gui.server.ntli.net...
> > > Hi,
> > >
> > > I am trying to do a distributed query from within my SQL 7 database,
the
> > > server has been linked inot mine and I have done a stored procedure
> which
> > is
> > > on the linked server which works perfectly, however when I try and put
> it
> > > onto mine I can;t seem to get it right. Here is my code, would just
> need
> > > some help on where I would place the linked server name and database
> name
> > > within the code - any help on this would be most appreciated.
> > >
> > > SELECT dbo.LPI.UPRN as UPRN,
> > > isnull(convert(varchar(5),dbo.LPI.PAON_START_NUM),'') + ' ' +
> > > isnull(convert(varchar(5),dbo.LPI.PAON_START_SUFFIX),'') + ' ' + case
> when
> > > paon_end_num = 0 then '' else cast(paon_end_num as varchar(20)) end +
'
> '
> > +
> > > isnull(convert(varchar(30), dbo.STREET.DESCRIPTION),'') as ADD1,
> > > dbo.LPI.SAON_TEXT as ADD2, dbo.LU_LOCALITY.LOC,
> > dbo.LU_COUNTY.COUNTY,
> > > dbo.LU_POSTCODE.POSTCODE
> > > FROM dbo.LPI INNER JOIN
> > > dbo.LU_POSTCODE ON dbo.LPI.POSTCODE_REF => > > dbo.LU_POSTCODE.POSTCODE_REF INNER JOIN
> > > dbo.STREET ON dbo.LPI.LPI_USRN = dbo.STREET.USRN
> > INNER
> > > JOIN
> > > dbo.LU_LOCALITY ON dbo.STREET.LOC_REF => > > dbo.LU_LOCALITY.LOC_REF INNER JOIN
> > > dbo.LU_COUNTY ON dbo.STREET.COUNTY_REF => > > dbo.LU_COUNTY.COUNTY_REF
> > > WHERE POSTCODE = @.P_CODE
> > >
> > >
> > > Thank you
> > >
> > > Damon
> > >
> > >
> > >
> >
> >
>|||Here is the whole sql statement which is coming up with errors.
SELECT dbo.LPI.UPRN as UPRN,
isnull(convert(varchar(5),dbo.LPI.PAON_START_NUM),'') + '' +
isnull(convert(varchar(5),dbo.LPI.PAON_START_SUFFIX),'') + ' ' + case when
paon_end_num = 0 then '' else cast(paon_end_num as varchar(20)) end + ' ' +
isnull(convert(varchar(30), dbo.STREET.DESCRIPTION),'') as ADD1,
dbo.LPI.SAON_TEXT as ADD2, dbo.LU_LOCALITY.LOC as DISTRICT,
dbo.LU_COUNTY.COUNTY as CITY, dbo.LU_POSTCODE.POSTCODE as POSTCODE
FROM cccgaz1.bs7666_ccc.dbo.LPI INNER JOIN
cccgaz1.bs7666_ccc.dbo.LU_POSTCODE ON
dbo.LPI.POSTCODE_REF = dbo.LU_POSTCODE.POSTCODE_REF INNER JOIN
cccgaz1.bs7666_ccc.dbo.STREET ON dbo.LPI.LPI_USRN =dbo.STREET.USRN INNER JOIN
cccgaz1.bs7666_ccc.dbo.LU_LOCALITY ON
dbo.STREET.LOC_REF = dbo.LU_LOCALITY.LOC_REF INNER JOIN
cccgaz1.bs7666_ccc.dbo.LU_COUNTY ON
dbo.STREET.COUNTY_REF = dbo.LU_COUNTY.COUNTY_REF
WHERE POSTCODE = @.P_CODE
Thanks
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:Oeu5QhCtDHA.1756@.TK2MSFTNGP09.phx.gbl...
> 4 Point Declaratio
> Servername.DatabaseName.OwnerName.Objectname
> If there are errors please post them,or post the whole SQL Statement.
> HTH, Jens Süßmeyer.
>
> "Damon" <damon@.nospam.co.uk> schrieb im Newsbeitrag
> news:My2xb.290$Eb.349801@.newsfep2-gui.server.ntli.net...
> > Thanks for the reply but not exactly sure what you are getting @..
> >
> > Where do I need to place the server name and database names? I stick
them
> > after the 'from' and before the dbo.LPI, dbo.LU_POSTCODE, dbo.STREET,
> > dbo.LU_LOCALITY and dbo.LU_COUNTY but then it comes up with a whole load
> of
> > errors?
> >
> >
> > "Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
> > news:OoeHpMCtDHA.1756@.TK2MSFTNGP09.phx.gbl...
> > > Refer to the 3(4) declaration of objects:
> > >
> > > Servername.Databasename.Ownername.Ojectname (sample:
> > Server01...Customers),
> > > you don´t need to name all the names, if they are unique.
> > >
> > > So you could use a alias to Name the objects
> > >
> > > SELECT Username from
> > > Users U1 join
> > > Server02.Test..Users U2
> > > where U1.Name = 'Test' and U2.name='Clean the House'
> > >
> > > HTH, Jens Süßmeyer.
> > >
> > > "Damon" <damon@.nospam.co.uk> schrieb im Newsbeitrag
> > > news:GY1xb.268$Bh1.223@.newsfep1-gui.server.ntli.net...
> > > > Hi,
> > > >
> > > > I am trying to do a distributed query from within my SQL 7 database,
> the
> > > > server has been linked inot mine and I have done a stored procedure
> > which
> > > is
> > > > on the linked server which works perfectly, however when I try and
put
> > it
> > > > onto mine I can;t seem to get it right. Here is my code, would just
> > need
> > > > some help on where I would place the linked server name and database
> > name
> > > > within the code - any help on this would be most appreciated.
> > > >
> > > > SELECT dbo.LPI.UPRN as UPRN,
> > > > isnull(convert(varchar(5),dbo.LPI.PAON_START_NUM),'') + ' ' +
> > > > isnull(convert(varchar(5),dbo.LPI.PAON_START_SUFFIX),'') + ' ' +
case
> > when
> > > > paon_end_num = 0 then '' else cast(paon_end_num as varchar(20)) end
+
> '
> > '
> > > +
> > > > isnull(convert(varchar(30), dbo.STREET.DESCRIPTION),'') as ADD1,
> > > > dbo.LPI.SAON_TEXT as ADD2, dbo.LU_LOCALITY.LOC,
> > > dbo.LU_COUNTY.COUNTY,
> > > > dbo.LU_POSTCODE.POSTCODE
> > > > FROM dbo.LPI INNER JOIN
> > > > dbo.LU_POSTCODE ON dbo.LPI.POSTCODE_REF => > > > dbo.LU_POSTCODE.POSTCODE_REF INNER JOIN
> > > > dbo.STREET ON dbo.LPI.LPI_USRN =dbo.STREET.USRN
> > > INNER
> > > > JOIN
> > > > dbo.LU_LOCALITY ON dbo.STREET.LOC_REF => > > > dbo.LU_LOCALITY.LOC_REF INNER JOIN
> > > > dbo.LU_COUNTY ON dbo.STREET.COUNTY_REF => > > > dbo.LU_COUNTY.COUNTY_REF
> > > > WHERE POSTCODE = @.P_CODE
> > > >
> > > >
> > > > Thank you
> > > >
> > > > Damon
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>