t-sql : Insert statement: using network server to sql server express 2005

sql server 2005 express
sql server 2003

How can I  use this query:  see below
Instead  of using access as the place to append the data, and just use sql server express to append the data.

It a huge table on the network  server..980,000 records...





dbo_WwgExtraChgo  = is on my sql server express 2005 machine.
DSN=Sql_2005_Express;Description=Main Database Connection;APP=Microsoft Office 2003;WSID=DXP1;DATABASE=WwgExtra;Trusted_Connection=Yes



dbo_Item =  network server... I have linked into access 2003 database. "BetaCrs.mdb"
DSN=BM_Only;Description=PRODUCT db;APP=Microsoft Office 2003;WSID=DXP1;Network=DBMSSOCN


Thanks
fordraiders



INSERT INTO dbo_WwgExtraChgo ( material_no, brand_no, condensed_mfr_model_no, country_of_origin, current_catalog_page_no, customer_ship_qty, item_gsa_compliant_flag, long_description, pricer_minimum_purchase_qty, product_noun, sales_status, short_description, uom_name, uom_qty, gsa_open_mkt_flag, green_material_flag )
SELECT dbo_Item.material_no, dbo_Item.brand_no, dbo_Item.condensed_mfr_model_no, dbo_Item.country_of_origin, dbo_Item.current_catalog_page_no, dbo_Item.customer_ship_qty, dbo_Item.item_gsa_compliant_flag, dbo_Item.long_description, dbo_Item.pricer_minimum_purchase_qty, dbo_Item.product_noun, dbo_Item.sales_status, dbo_Item.short_description, dbo_Item.uom_name, dbo_Item.uom_qty, dbo_Item.gsa_open_mkt_flag, dbo_Item.green_material_flag
FROM dbo_Item;

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need a linked server on either instance, pointing to the other instance, and then you can select from the remote instance:
insert into ... 
  select ...
   from linked_server_name.database.dbo.tablename

Open in new window

0
FordraidersAuthor Commented:
can you link in sql server express like you can in access 2003 ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just to clarify:
access is a GUI application.
sql server is a backend application.

anyhow, see here for an example how to configure a linked server:
http://www.quackit.com/sql_server/sql_server_2008/tutorial/linked_servers.cfm
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

FordraidersAuthor Commented:
ok...with what I have stated above for the network server:

Provider =  ?
"microsoft oledb provider for sql server"   <---- ?
product name = ?
"ITEM"   <--- THIS IS THR NAME OF THE TABLE I'm needing
data source = ?
DBMSSOCN  =  name of network server  <---- ?
provider string = ?
PRMTB004.cam.wwer.com
catalog = ?  optional
optional

0
FordraidersAuthor Commented:
the linked server has unserame and password ..

username =   Read_Only
password =  ReadOnly
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in a linked server, you don't specify a TABLE, you specify the full instance.

as from there, you query tables from that instance.
in ms access, you link each table individually.
0
FordraidersAuthor Commented:
OK, Here is what I,m getting:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

The OLE DB provider "SQLNCLI" for linked server "LINK_CHGO" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "LINK_CHGO".
OLE DB provider "SQLNCLI" for linked server "LINK_CHGO" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "LINK_CHGO" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4053&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

&Yes
&No
------------------------------
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Authentication failed.
>OLE DB provider "SQLNCLI" for linked server "LINK_CHGO" returned message "Invalid authorization specification".

on which instance (version) to do try to set up the linked server?
I presume on what you called sql server 2003, though there is no such version 2003 for sql server.
please clarify
0
FordraidersAuthor Commented:
sql server 2000 sorry...
0
FordraidersAuthor Commented:
here it is so far ?

link-sql-network.bmp
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show the screenshot of the linked server security tab?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
remove the login specifications from there, and use them in the security tab
0
FordraidersAuthor Commented:
They just told me sql server 2005..? checking on the remote connectivity other than DSN
0
FordraidersAuthor Commented:
ok got the hook up to work..  Do not see any tables ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that might be normal...
can you query from the linked server
select * from linked_server.database.dbo.tablename

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
PERFECT Thanks

Whay is it normal ?  just curious...


0
FordraidersAuthor Commented:
Thanks for staying with me on this !!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.