Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB
ÈÈÃűêÇ©£º c c# c++ asp asp.net linux php jsp java vb Python Ruby mysql sql access Sqlite sqlserver delphi javascript Oracle ajax wap mssql html css flash flex dreamweaver xml
 ×îÐÂÎÄÕ : mssql

ÓÃ×÷ҵʵÏÖ×Ô¶¯±¸·ÝMSSQLÊý¾Ý¿âµ½Ô¶³Ì·þÎñÆ÷

--´Ë´úÂëʵÏÖSQLÊý¾Ý¿âÔ¶³Ì±¸·Ý£¬·Åµ½×÷ÒµÀïÃæÖ´ÐпÉÒÔ×Ô¶¯±¸·ÝÊý¾Ý¿â¡¢×Ô¶¯É¾³ý@keepNDaysÌìÇ°±¸·Ý¡£
--´Ë´úÂ뽫±¾µØËùÓеÄÓû§Êý¾Ý¿â±¸·Ýµ½¹²ÏíĿ¼¡°\\backupServerIp\ShareName\Êý¾Ý¿â±¸·Ý¡±Ï¡£
--²¢É¾³ýÌìÇ°µÄ±¸·ÝÎļþ¡£Òª±¸·Ý³É¹¦±ØÐëÄܹ»¶Ô¹²ÏíĿ¼ÓвÙ×÷ȨÏÞ£¡

sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

--´´½¨Ó³Éä
execmaster..xp_cmdshell 'net use T: \\backupServerIp\ShareName "password" /user:uonun',NO_OUTPUT
GO

declare@keepNDays int,@s nvarchar(max),@del nvarchar(max)
select @keepNDays = 30,@backupSql='',@delSql=''

select
   @backupSql=@backupSql+
        char(13)+'DBCC SHRINKDATABASE(N'''+Name+''', 10, TRUNCATEONLY)'+    --ÊÕËõÊý¾Ý¿â
        char(13)+'backup database  '+quotename(Name)+'  to disk =''T:\Êý¾Ý¿â±¸·Ý\'+Name+'_'+convert(varchar(8),getdate(),112)+'.bak''  with init',    --±¸·ÝÊý¾Ý¿ ......

php 5.3 Á¬½ÓmssqlÊý¾Ý¿â

×î½üÓиöС¶«Î÷Òª²é¿´mssqlÊý¾Ý¿âÊÇÓÃphpʵÏֵģ¬ÒÔÇ°ÎÒÓÃphp5.2ʱ¸Ð¾õͦ¼òµ¥µÄËùÒÔÏëphp5.3Ò²Ó¦¸ÃºÜ¼òµ¥µÄ
ΪʲôҪÓÃphp5.3ÄØÒòΪÎÒÏëÓÃsqlite3.0µÄ°¡£¬ÒòΪphp5.2µÄ²»Ö§³Ösqlite3.0µÄ°¡£¬ËùÒÔÎÒÌØÒâÈ¥ÏÂÁË5.3ÁËÏÂÔØ»ØÀ´Á˲ŷ¢ÏÖ5.3ÀïûÓÐmssqlµÄdllÀ©Õ¹ÁË£¬ÓôÃÆ°¡£¬²»¹ÜÕâô¶àÏÈÓÃÆðÄÇsqlite3.0ÔÙ˵ÁË
sqlite3.0µÄ²¿·Ö³ÌÐò»ù±¾Ð´ÍêÁË£¬ÏÖÔÚÒª¿ªÊ¼Á¬½ÓmssqlÊý¾Ý¿âÁË£¬ÎÒÏëûÓÐmssqlÄǾÍÓÃodbc°É£¬¿¿Á¬odbcÒ²ÕÒ²»µ½°¡£¬Ö»ÓÐpdo_odbcµÄ£¬Ã»Óа취ÁËÄǾÍÓÃpdo_odbcÁË£¬ÔõôÁ¬½Ó»¹Ã»ÓÐÓùýÄØ£¬Ö±½Ó²éphpµÄÊֲᣬ¸ù¾ÝÊÖ²áÊǵÄʵÀýÁ¬½ÓһϷ¢ÏÖÓÐÎÊÌâ°¡
¾¹È»ËµÃ»ÓÐodbcÇý¶¯°¡£¬Ôõô»ØÊ°¡£¬¸ãÁËÒ»»áûÓÐÕûÃ÷°×£¬ËãÁËÖ±½ÓÓÃ΢Èí¶ÔphpÀ©Õ¹Á½¼ÒsqlserverµÄ°É£¬ÊÇSQLServerDriverForPHP11ÏÂÔØÁË°²×°Ò»Ï£¬Æäʵ¾ÍÊÇ×Ô½âѹµÄѹËõ°ü°¡£¬¸ù¾Ý×Ô¼º¶ÔÓ¦µÄphp°æ±¾µÄsqlserverdriverµÄdll¿½±´µ½extĿ¼ÏÂÈ»ºóÔÚphp.iniÖмÓһϠextension=php_sqlsrv_53_nts_vc9.dll ¼ÓÔØʧ°Ü£¬ÓôÃÆ°¡£¬ºóÀ´¿´ÁËÎÒµÄphp°æÊÇphp-5.3.2-Win32-VC9-x86.zip²»ÊÇnts£¨Õâ¸öºÃÏñÊÇ·ÇḬ̈߳²È«°É£©ÎҾͻ»Á˸öextension=php_sqlsrv_53_ts_vc9.dll ûÓб¨´íÁË£¬¿´À´Ó¦¸Ã¿ÉÒÔÁË°É£¬¸ù¾ÝÕâÏÂÔØÇý¶¯Îĵµ¿´¿´ÔõôÁ¬½ÓÊý¾Ý¿â ......

php 5.3 Á¬½ÓmssqlÊý¾Ý¿â

×î½üÓиöС¶«Î÷Òª²é¿´mssqlÊý¾Ý¿âÊÇÓÃphpʵÏֵģ¬ÒÔÇ°ÎÒÓÃphp5.2ʱ¸Ð¾õͦ¼òµ¥µÄËùÒÔÏëphp5.3Ò²Ó¦¸ÃºÜ¼òµ¥µÄ
ΪʲôҪÓÃphp5.3ÄØÒòΪÎÒÏëÓÃsqlite3.0µÄ°¡£¬ÒòΪphp5.2µÄ²»Ö§³Ösqlite3.0µÄ°¡£¬ËùÒÔÎÒÌØÒâÈ¥ÏÂÁË5.3ÁËÏÂÔØ»ØÀ´Á˲ŷ¢ÏÖ5.3ÀïûÓÐmssqlµÄdllÀ©Õ¹ÁË£¬ÓôÃÆ°¡£¬²»¹ÜÕâô¶àÏÈÓÃÆðÄÇsqlite3.0ÔÙ˵ÁË
sqlite3.0µÄ²¿·Ö³ÌÐò»ù±¾Ð´ÍêÁË£¬ÏÖÔÚÒª¿ªÊ¼Á¬½ÓmssqlÊý¾Ý¿âÁË£¬ÎÒÏëûÓÐmssqlÄǾÍÓÃodbc°É£¬¿¿Á¬odbcÒ²ÕÒ²»µ½°¡£¬Ö»ÓÐpdo_odbcµÄ£¬Ã»Óа취ÁËÄǾÍÓÃpdo_odbcÁË£¬ÔõôÁ¬½Ó»¹Ã»ÓÐÓùýÄØ£¬Ö±½Ó²éphpµÄÊֲᣬ¸ù¾ÝÊÖ²áÊǵÄʵÀýÁ¬½ÓһϷ¢ÏÖÓÐÎÊÌâ°¡
¾¹È»ËµÃ»ÓÐodbcÇý¶¯°¡£¬Ôõô»ØÊ°¡£¬¸ãÁËÒ»»áûÓÐÕûÃ÷°×£¬ËãÁËÖ±½ÓÓÃ΢Èí¶ÔphpÀ©Õ¹Á½¼ÒsqlserverµÄ°É£¬ÊÇSQLServerDriverForPHP11ÏÂÔØÁË°²×°Ò»Ï£¬Æäʵ¾ÍÊÇ×Ô½âѹµÄѹËõ°ü°¡£¬¸ù¾Ý×Ô¼º¶ÔÓ¦µÄphp°æ±¾µÄsqlserverdriverµÄdll¿½±´µ½extĿ¼ÏÂÈ»ºóÔÚphp.iniÖмÓһϠextension=php_sqlsrv_53_nts_vc9.dll ¼ÓÔØʧ°Ü£¬ÓôÃÆ°¡£¬ºóÀ´¿´ÁËÎÒµÄphp°æÊÇphp-5.3.2-Win32-VC9-x86.zip²»ÊÇnts£¨Õâ¸öºÃÏñÊÇ·ÇḬ̈߳²È«°É£©ÎҾͻ»Á˸öextension=php_sqlsrv_53_ts_vc9.dll ûÓб¨´íÁË£¬¿´À´Ó¦¸Ã¿ÉÒÔÁË°É£¬¸ù¾ÝÕâÏÂÔØÇý¶¯Îĵµ¿´¿´ÔõôÁ¬½ÓÊý¾Ý¿â ......

MSSQLÊý¾Ý¿â²Ù×÷ʹÓÃÓï¾ä


1.°´ÐÕÊϱʻ­ÅÅÐò: 
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 
2.·ÖÒ³SQLÓï¾ä 
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from ±íÃû As tab) As t where rownum between ÆðʼλÖà And ½áÊøλÖà
3.»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§±í 
select * from sysobjects where xtype='U' and category=0 
4.»ñȡijһ¸ö±íµÄËùÓÐ×ֶΠ
select name from syscolumns where id=object_id('±íÃû') 
5.²é¿´Óëijһ¸ö±íÏà¹ØµÄÊÓͼ¡¢´æ´¢¹ý³Ì¡¢º¯Êý 
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%±íÃû%' 
6.²é¿´µ±Ç°Êý¾Ý¿âÖÐËùÓд洢¹ý³Ì 
select name as ´æ´¢¹ý³ÌÃû³Æ from sysobjects where xtype='P' 
7.²éѯÓû§´´½¨µÄËùÓÐÊý¾Ý¿â 
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa') 
»òÕß 
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01 
8.²éѯijһ¸ö±íµÄ×ֶκÍÊý¾ ......

php µ÷ÓÃmssql´æ´¢¹ý³ÌʵÀýÓ¦ÓÃ

php½Ì³Ì µ÷ÓÃmssql´æ´¢¹ý³ÌʵÀýÓ¦ÓÃ
ÓÃmssql_initÓï¾äÓÃÓÚ³õʼ»¯´æ´¢¹ý³Ì£¬¶øºóµ÷ÓÃmssql_bindÓï¾äÖ¸¶¨´æ´¢¹ý³Ì²ÎÊý£¬×îºóµ÷ÓÃmssql_executeÖ´Ðд洢¹ý³Ì¡£
*/
//Á¬½ÓmssqlÊý¾Ý¿â½Ì³Ì·þÎñÆ÷
 $link = mssql_connect("127.0.0.1", "sa", "sa") or die("Can't connect sql server");
    mssql_select_db("frrc", $link) or die("Select database failure");
//³õʼ»¯²¢¼ÓÔØ´æ´¢¹ý³Ì Bigdatabase
     $stmt = mssql_init("Bigdatabase", $link) or die("initialize stored procedure failure");  
  mssql_bind($stmt, "@pagesize", $pagesize, SQLINT4);
  mssql_bind($stmt, "@start", $start, SQLINT4);
  @$ms_result = mssql_execute($stmt, false);
  mssql_query("DUMP TRANSACTION tempdb WITH NO_LOG");
  
  do{
   while( $ms_rs = mssql_fetch_object($ms_result) )
   {
    //½øÐÐÊý¾Ý´¦Àí
   }
  }
// msssql  ´æ´¢¹ý³Ì BigdatabaseÈçÏ ......

php µ÷ÓÃmssql´æ´¢¹ý³ÌʵÀýÓ¦ÓÃ

php½Ì³Ì µ÷ÓÃmssql´æ´¢¹ý³ÌʵÀýÓ¦ÓÃ
ÓÃmssql_initÓï¾äÓÃÓÚ³õʼ»¯´æ´¢¹ý³Ì£¬¶øºóµ÷ÓÃmssql_bindÓï¾äÖ¸¶¨´æ´¢¹ý³Ì²ÎÊý£¬×îºóµ÷ÓÃmssql_executeÖ´Ðд洢¹ý³Ì¡£
*/
//Á¬½ÓmssqlÊý¾Ý¿â½Ì³Ì·þÎñÆ÷
 $link = mssql_connect("127.0.0.1", "sa", "sa") or die("Can't connect sql server");
    mssql_select_db("frrc", $link) or die("Select database failure");
//³õʼ»¯²¢¼ÓÔØ´æ´¢¹ý³Ì Bigdatabase
     $stmt = mssql_init("Bigdatabase", $link) or die("initialize stored procedure failure");  
  mssql_bind($stmt, "@pagesize", $pagesize, SQLINT4);
  mssql_bind($stmt, "@start", $start, SQLINT4);
  @$ms_result = mssql_execute($stmt, false);
  mssql_query("DUMP TRANSACTION tempdb WITH NO_LOG");
  
  do{
   while( $ms_rs = mssql_fetch_object($ms_result) )
   {
    //½øÐÐÊý¾Ý´¦Àí
   }
  }
// msssql  ´æ´¢¹ý³Ì BigdatabaseÈçÏ ......

ÆôÓÃMSSQL·Ö²¼Ê½ÊÂÎñµÄ³£¼ûÎÊÌâ

ÈçºÎ´´½¨Á´½Ó·þÎñÆ÷
IF  EXISTS (SELECT srv.name from sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'Á´½Ó·þÎñÆ÷Ãû')
EXEC master.dbo.sp_dropserver @server=N'Á´½Ó·þÎñÆ÷Ãû'', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver
 @server = N'Á´½Ó·þÎñÆ÷Ãû'', @srvproduct=N'SQLNCLI', @provider=N'SQLNCLI', @datasrc=N'Á´½Ó·þÎñÆ÷IPµØÖ·'
 
EXEC master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname=N'Á´½Ó·þÎñÆ÷Ãû'',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='Á´½Ó·þÎñÆ÷ÃÜÂë'
Æô¶¯DTC·þÎñ
·þÎñÃû³Æ£ºDistributed Transaction Coordinator
ÃüÁîÐÐÆô¶¯£ºnet start MSDTC
ÉèÖ÷ÃÎÊȨÏÞ
windows2003&xp
²Î¿¼£ºhttp://support.microsoft.com/kb/817064/zh-cn
windows2008
²Î¿¼£ºhttp://technet.microsoft.com/zh-cn/library/cc753510(WS.10).aspx ......

ÆôÓÃMSSQL·Ö²¼Ê½ÊÂÎñµÄ³£¼ûÎÊÌâ

ÈçºÎ´´½¨Á´½Ó·þÎñÆ÷
IF  EXISTS (SELECT srv.name from sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'Á´½Ó·þÎñÆ÷Ãû')
EXEC master.dbo.sp_dropserver @server=N'Á´½Ó·þÎñÆ÷Ãû'', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver
 @server = N'Á´½Ó·þÎñÆ÷Ãû'', @srvproduct=N'SQLNCLI', @provider=N'SQLNCLI', @datasrc=N'Á´½Ó·þÎñÆ÷IPµØÖ·'
 
EXEC master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname=N'Á´½Ó·þÎñÆ÷Ãû'',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='Á´½Ó·þÎñÆ÷ÃÜÂë'
Æô¶¯DTC·þÎñ
·þÎñÃû³Æ£ºDistributed Transaction Coordinator
ÃüÁîÐÐÆô¶¯£ºnet start MSDTC
ÉèÖ÷ÃÎÊȨÏÞ
windows2003&xp
²Î¿¼£ºhttp://support.microsoft.com/kb/817064/zh-cn
windows2008
²Î¿¼£ºhttp://technet.microsoft.com/en-us/library/cc753620(WS.10).aspx ......
×ܼǼÊý:86; ×ÜÒ³Êý:15; ÿҳ6 Ìõ; Ê×Ò³ ÉÏÒ»Ò³[2] [3] [4] [5] [6] [7] [8] [9] [10]  ÏÂÒ»Ò³ βҳ
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ