May 12, 2009 at 6:24 am
I've two databases that contain data I'dd like to combine.
In Query 1 I've select information about contracts from database1 (Contract).
select
srelatie.naamas Relatie,
srelatie.debiteurnummer as Debnr,
ckop.contractnummeras Contractnummer,
ssoort.omschrijving as Omschrijving,
DATEADD(day, ckop.ingangsdatum, CONVERT(DATETIME, '1800-12-28 00:00:00', 102)) as Ingangsdatum,
ckop.looptijdas Looptijd,
DATEADD(day, ckop.vervaldatum, CONVERT(DATETIME, '1800-12-28 00:00:00', 102)) as Vervaldatum,
cvrijval.jaarbedragas Bedrag,
cvrijval.maandas Vrijval_Maand,
cvrijval.jaaras Vrijval_Jaar
from
ckopwith (nolock)
inner join srelatie with (nolock) on ckop.idsrelatie=srelatie.idsrelatie
inner join cvrijval with (nolock) on ckop.idckop=cvrijval.idckop
inner join ssoortwith (nolock) on ckop.idssoort=ssoort.idssoort
where
ckop.onbepaald='1'
and ckop.actief='1'
and year(DATEADD(day, ckop.vervaldatum, CONVERT(DATETIME, '1800-12-28 00:00:00', 102))) <= year(getdate())
and cvrijval.jaar=year(getdate())
and cvrijval.maand=month(getdate())
group by
srelatie.naam, srelatie.debiteurnummer, ckop.contractnummer, ssoort.omschrijving, ckop.ingangsdatum, ckop.looptijd, ckop.vervaldatum,cvrijval.jaarbedrag, cvrijval.maandbedrag, cvrijval.maand, cvrijval.jaar
order by
Vervaldatum, Relatie desc, Bedrag, Contractnummer
[/code]
This information should be combined with the corresponding Reseller and Accountmanager, found in database 2 (TOPdesk)
[code]
select
vestiging.naamas Klantnaam,
vestiging.debiteurennummer as Debnr,
actiedoor.ref_aanhefinformeel as Accountmanager,
vrijeopzoekvelden.naamas Reseller
from
vestigingwith (nolock)
left join vestigingaccountmanagerwith (nolock) on vestiging.unid=vestigingaccountmanager.vestigingidleft join actiedoor on vestigingaccountmanager.accountmanagerid=actiedoor.unid
left join vrijeopzoekveldenwith (nolock) on vestiging.vrijeopzoek2=vrijeopzoekvelden.unid
[/code]
The unique field in both databases is "Debnr", so I think it should be possible.
Can someone help me?
May 12, 2009 at 6:29 am
you'll need to create a linked server on one of the databases (where you want the query running) to the other one
So if the linked server was created on CONTRACT and pointed to TOPDesk2 then data can be combined from both:
Select *
from contract.table1
inner join TOPDesk2.[DatabaseName].dbo.table2
on [join clauses]
May 13, 2009 at 4:45 am
Ok... I've created the linked server.
But when I've query both of my databases, I get the error:
The multi-part identifier "[am1-sqls03a\sqls03a].Topdesk.dbo.vestiging.debiteurennummer" could not be bound.
Query:
select *
from
srelatie
inner join [am1-sqls03a\sqls03a].Topdesk.dbo.vestiging
on srelatie.debiteurnummer=[am1-sqls03a\sqls03a].Topdesk.dbo.vestiging.debiteurennummer
I've tried several options, but no luck.
May 13, 2009 at 4:58 am
htilburgs (5/13/2009)
Ok... I've created the linked server.But when I've query both of my databases, I get the error:
The multi-part identifier "[am1-sqls03a\sqls03a].Topdesk.dbo.vestiging.debiteurennummer" could not be bound.
you need to alias it
select *
from
srelatie
inner join [am1-sqls03a\sqls03a].Topdesk.dbo.vestiging AliasTableName
on srelatie.debiteurnummer= AliasTableName.debiteurennummer
you can replace AliasTableName with any name you like
May 13, 2009 at 5:05 am
When I use Alias, I've get the next error:
Cannot resolve the collation conflict between "Latin1_General_CI_AI_KS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I've checked the collation of the both Tables, but both are SQL_Latin1_General_CP1_CI_AS
select srelatie.naam,srelatie.adres
from
srelatie
inner join [am1-sqls03a\sqls03a].Topdesk.dbo.vestiging TDName
on srelatie.debiteurnummer=TDName.debiteurennummer
May 13, 2009 at 5:09 am
i'm not sure about why its getting that collation.
You can specify the collation of a column though
select MyColumn collate [collation name] as MyColumnAlias
from MyTable
The same thing works in joins
from TableA A
inner Join TableB B
on A.col1 collate [collation name] = B.col2 collate [collation name]
replace [collation name] with your collation name
May 13, 2009 at 5:19 am
YIPPIE!
When I use the next code, it works! Thanks!
select srelatie.naam,srelatie.adres
from
srelatie
inner join [am1-sqls03a\sqls03a].Topdesk.dbo.vestiging TDName
on srelatie.debiteurnummer COLLATE DATABASE_DEFAULT =TDName.debiteurennummer COLLATE DATABASE_DEFAULT
The collation of the Column "debiteurennummer" is not the same as the database.
That's why I use "COLLATE DATABASE_DEFAULT"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply