C# Á¬½ÓsqlÊý¾Ý¿â¶Ô access£¬excelµ¼Èëµ¼³ö²Ù×÷
Ò»¡¢SQL SERVER ºÍACCESSµÄÊý¾Ýµ¼Èëµ¼³ö
³£¹æµÄÊý¾Ýµ¼Èëµ¼³ö£º
ʹÓÃDTSÏòµ¼Ç¨ÒÆÄãµÄAccessÊý¾Ýµ½SQL Server£¬Äã¿ÉÒÔʹÓÃÕâЩ²½Öè:
¡¡¡¡¡ð1ÔÚSQL SERVERÆóÒµ¹ÜÀíÆ÷ÖеÄTools£¨¹¤¾ß£©²Ëµ¥ÉÏ£¬Ñ¡ÔñData Transformation
¡¡¡¡¡ð2Services£¨Êý¾Ýת»»·þÎñ£©£¬È»ºóÑ¡Ôñ czdImport Data£¨µ¼ÈëÊý¾Ý£©¡£
¡¡¡¡¡ð3ÔÚChoose a Data Source£¨Ñ¡ÔñÊý¾ÝÔ´£©¶Ô»°¿òÖÐÑ¡ÔñMicrosoft Access as the Source£¬È»ºó¼üÈëÄãµÄ.mdbÊý¾Ý¿â(.mdbÎļþÀ©Õ¹Ãû)µÄÎļþÃû»òͨ¹ýä¯ÀÀÑ°ÕÒ¸ÃÎļþ¡£
¡¡¡¡¡ð4ÔÚChoose a Destination£¨Ñ¡ÔñÄ¿±ê£©¶Ô»°¿òÖУ¬Ñ¡ÔñMicrosoft OLE¡¡DB Prov ider for SQL¡¡Server£¬Ñ¡ÔñÊý¾Ý¿â·þÎñÆ÷£¬È»ºóµ¥»÷±ØÒªµÄÑéÖ¤·½Ê½¡£
¡¡¡¡¡ð5ÔÚSpecify Table Copy£¨Ö¸¶¨±í¸ñ¸´ÖÆ£©»òQuery£¨²éѯ£©¶Ô»°¿òÖУ¬µ¥»÷Copy tables£¨¸´ÖƱí¸ñ£©¡£
¡ð6ÔÚSelect Source Tables£¨Ñ¡ÔñÔ´±í¸ñ£©¶Ô»°¿òÖУ¬µ¥»÷Select All£¨È«²¿Ñ¡¶¨£©¡£ÏÂÒ»²½£¬Íê³É¡£
Transact-SQLÓï¾ä½øÐе¼Èëµ¼³ö£º
1.ÔÚSQL SERVERÀï²éѯaccessÊý¾Ý:
SELECT * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=')...±íÃû
2.½«accessµ¼ÈëSQL server
ÔÚSQL SERVER ÀïÔËÐÐ:
SELECT *INTO newtable from OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...±íÃû
3.½«SQL SERVER±íÀïµÄÊý¾Ý²åÈëµ½Access±íÖÐ
ÔÚSQL SERVER ÀïÔËÐУº
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...±íÃû (ÁÐÃû1,ÁÐÃû2)
select ÁÐÃû1,ÁÐÃû2 from sql±í
ʵÀý£º
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\db.mdb';'admin';'', Test)
select id,name from Test
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', ±íÃû)
SELECT * from sqltablename
¶þ¡¢SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö
1¡¢ÔÚSQL SERVERÀï²éѯExcelÊý¾Ý:
SELECT * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ÏÂÃæÊǸö²éѯµÄʾÀý£¬Ëüͨ
Ïà¹ØÎĵµ£º
insert into mysql.user
SELECT '%', 'admin', PASSWORD('admin'), Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv, Reload_priv,
Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv,
Index_priv, Alter_priv, Show_db_priv, Super_priv, ......
C#
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/Config/User_yhlx_Jb.xml"));
DataView dv = ds.Tables[0].DefaultView;
//dv.RowFilter = "State=0";
this.DropDownList1.DataSource = dv;
this.DropDownList1.DataTextField = "text";
this ......
aspÁ¬½Óaccess,³öÏÖ0x80004005,δָ¶¨µÄ´íÎó
aspÀ´Á¬½ÓaccessÊý¾Ý¿â
±¾µØ²âÊÔ·¢ÏÖ,¾²Ì¬Ò³¿ÉÒÔ´ò¿ª.
ºÍÊý¾Ý¿â½¨Á¢Á´½ÓµÄ´úÂëconn.open connstr Ðгö´í,µ«ÊÇÒÔÇ°²âÊÔʪ¿ÉÒÔͨ¹ýµÄ.
ÔÚÍøÉÏÕÒÁËÕÒ½â¾ö°ì·¨,ÀýÈ磺 ¸øÓû§¼ÓȨÏÞ,¸ü¸ÄiisÉèÖÃ.µ«ÊÇ»¹ÊDz»ÐÐ.
¼ÌÐøÕÒ£¬ÕÒµ½Ò»ÆªÎÄÕ£¬ËµÊÇÒªÐÞ¸Äc:\windows\temp ȨÏ޵ġ£
......
SQL Server°²×°Îļþ¹ÒÆð:
ÔÚÖØа²×°Ö÷SQLServer2000ʱ»òÊÇ´ò²¹¶¡Ê±,×ÜÊÇ»áÓöµ½SQLServer°²×°Îļþ¹ÒÆð¡£
ÔÚÔËÐд°¿ÚÊäÈëregedit£¬´ò¿ª×¢²á±í±à¼Æ÷£¬ÔÚHKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Session ManagerÖÐÕÒµ½PendingFileRenameOperations£¬É¾³ý¸Ã¼üÖµ£¨Õâ¸ö¼üÖµÊÇ°²×°³ÌÐòÔݹÒÏîÄ¿£¬Ö»ÒªÕÒµ½¶ÔÓ¦µÄÓ¦Óó ......
³õ´ÎʹÓÃpb10µÄÅóÓÑÎÞ·¨Ë³ÀûÁ¬½Óµ½sql server£¬Ò»·½ÃæûÓÐÁËmssql serverרÓýӿÚÁË£¬ÁíÒ»·½ÃæÊý¾Ý¿â¹ÜÀíϵͳ(DBMS)»»³ÉÁË“OLE DB”£¬ÅäÖÃÆðÀ´Óе㲻˳ÊÖ£¡ÏÂÃæ¾ÍÀ´½éÉÜpb10ÖÐÓÃOLE DBÁ¬½Ósql 2000Êý¾Ý¿â¡£
ÔÚ°²×°pb10µÄʱºòÑ¡Ôñcustom/full·½Ê½°²×°£¨Ä¬Èϵݲװ·½Ê½²»»á°²×°ole db½Ó¿Ú£©£¬next£¡·Èç¹ûÄãûÓа ......