TSQL -insert script in MSQL database

Question

TSQL -insert script in MSQL database

luissantos

SSCertifiable

Points: 7391
More actions
December 18, 2001 at 2:55 am

#158054

Thank you very much for your useful help
I need to import in my SQL database external data from access table.
I give you my script :
use demo2original
insert st
(ref,design,codigo,familia,stock,pv1,ivaincl,tabiva,pcusto,usr3,usr4,usr1,usr2,ststamp)
select ref,DESIGN,codigo,familia,stock,pv1,ivaincl,tabiva,pcusto,usr3,usr4,usr1,usr2,(select ref as ststamp) from OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:\a_prgroh\sqlfrontoffice97.mdb';'Admin';'',kasse) AS K
where NOT EXISTS(select ref from st WHERE k.REF = ST.REF)AND(K.DESIGN IS NOT NULL)
When i run this script my SQL query analiser return the following error:
Server: Msg 446, Level 16, State 9, Line 2
Cannot resolve collation conflict for equal to operation.
If i use just :
select ref,DESIGN,codigo,familia,stock,pv1,ivaincl,tabiva,pcusto,usr3,usr4,usr1,usr2,(select ref as ststamp) from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\a_prgroh\sqlfrontoffice97.mdb';'Admin';'',kasse) AS K
Sql return correct information about my external mdb table KASSE
I need some help because i can´t resolve this error,I don´t now if it´s possible doing this kind of procedure.
Thank you for your help and congratulation for this exellent site.
Luis Santos

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Thanks for the feedback. It appears that your Access database uses a different collation (kind of like language) than SQL.

Create a temp table and insert the data, then move it from the temp table to your real table.

Steve Jones

steve@dkranch.net

nigelrivett SSCertifiable Points: 5472 More actions · Answer 2

You could convert the collation in the compare but you would have to find out what was expected and where it was being actioned.

the syntax is something like

= (fld collate collationname)

Cursors never.
DTS - only when needed and never to control.

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Ok i think its a good solution, but i need the TSQL script for creating my temp table.

Could you send me this?

Best regards

Luis Santos

Thanks for the feedback. It appears that your Access database uses a different collation (kind of like language) than SQL.

Create a temp table and insert the data, then move it from the temp table to your real table.

Steve Jones

steve@dkranch.net

[/quote]

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

temp tables are created using the same create table syntax, but prefixing the name with #

CREATE TABLE #MyTemp ( ref int, design int, ...)

insert #MyTemp

(ref,design,codigo,familia,stock,pv1,ivaincl,tabiva,pcusto,usr3,usr4,usr1,usr2,ststamp)

select ref,DESIGN,codigo,familia,stock,pv1,ivaincl,tabiva,pcusto,usr3,usr4,usr1,usr2,(select ref as ststamp) from OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:\a_prgroh\sqlfrontoffice97.mdb';'Admin';'',kasse) AS K

Then join the temp table with your st table. When you finish, drop the table in drop table #MyTest

Steve Jones

steve@dkranch.net