[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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

0
Fordraiders
Asked:
Fordraiders
  • 10
  • 7
1 Solution
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
FordraidersAuthor Commented:
PERFECT Thanks

Whay is it normal ?  just curious...


0
 
FordraidersAuthor Commented:
Thanks for staying with me on this !!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now