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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737892

  More actions

  December 18, 2001 at 8:36 am

  #421216

  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

  December 18, 2001 at 8:51 am

  #421219

  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

  December 18, 2001 at 11:39 am

  #421254

  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: 737892

  More actions

  December 18, 2001 at 3:07 pm

  #421257

  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