]]>綺懼鐨凷QL鍜孲QL SERVER 涓嶢CCESS銆丒XCEL鐨勬暟鎹鍏ュ鍑鴻漿鎹?/title>http://m.shnenglu.com/lilac/archive/2008/01/13/41075.html鏉庝簹鏉庝簹Sat, 12 Jan 2008 16:21:00 GMThttp://m.shnenglu.com/lilac/archive/2008/01/13/41075.htmlhttp://m.shnenglu.com/lilac/comments/41075.htmlhttp://m.shnenglu.com/lilac/archive/2008/01/13/41075.html#Feedback0http://m.shnenglu.com/lilac/comments/commentRss/41075.htmlhttp://m.shnenglu.com/lilac/services/trackbacks/41075.html闃呰鍏ㄦ枃
]]>澶囦喚鏈嶅姟鍣ㄧSQL SERVER鏁版嵁搴撹嚦鏈湴鐩綍http://m.shnenglu.com/lilac/archive/2008/01/13/41074.html鏉庝簹鏉庝簹Sat, 12 Jan 2008 16:18:00 GMThttp://m.shnenglu.com/lilac/archive/2008/01/13/41074.htmlhttp://m.shnenglu.com/lilac/comments/41074.htmlhttp://m.shnenglu.com/lilac/archive/2008/01/13/41074.html#Feedback0http://m.shnenglu.com/lilac/comments/commentRss/41074.htmlhttp://m.shnenglu.com/lilac/services/trackbacks/41074.html/**//*鏁告摎搴倷浠藉瓨鍎查亷紼?/span>*/ /**//*鏀寔寰炴湇鍕欏櫒鍌欎喚鏁告摎鑷蟲湰鍦版鍣ㄤ笂*/ -- CREATEPROCEDURE up_dbbackup ( @backup_db_nameVARCHAR(128), @filenameVARCHAR(128), /**//*鍌欎喚璺緫+鏂囦歡鍚?/span>*/ @flagVARCHAR(60) OUTPUT ) AS SET NOCOUNT ON DECLARE@sqlNVARCHAR(4000),@parNVARCHAR(1000) IFNOTEXISTS( SELECT*FROM master..sysdatabases WHERE name=@backup_db_name ) BEGIN SELECT@flag='鏁告摎搴?/span>'+@backup_db_name+'涓嶅瓨鍦?' RETURN END ELSE BEGIN IFRIGHT(@filename,1)<>'\'ANDCHARINDEX('\',@filename)<>0 BEGIN /**//*瀹氱京妯欒獙*/ DECLARE@lFlagINT EXECUTE@lFlag=master..xp_cmdshell 'DIR Z:' IF@lFlag<>0 BEGIN /**//* 鍦ㄥ鎴舵192.168.2.45涓婂緩绔嬩竴鍊嬪畬鍏ㄥ叡浜洰閷刣b,鎸囧畾涓鍊嬬敤鎴跺悕鍜屽瘑紕?Win98鐢ㄦ埗涓嶉渶瑕佺敤鎴跺悕鍜屽瘑紕?/span>*/ EXECUTE@lFlag=master..xp_cmdshell 'NET USE Z: \\192.168.2.45\db$ test /user:ca.atc\test' IF@lFlag<>0 BEGIN SELECT@flag='鏈嶅嫏鍣ㄥ壍寤虹洰閷勫け鏁?' RETURN END END SELECT@par='@filename VARCHAR(1000)' SELECT@sql='BACKUP DATABASE '+@backup_db_name+' TO DISK=@filename WITH INIT' EXECUTE sp_executesql @sql,@par,@filename EXECUTE master..xp_cmdshell 'NET USE Z: /DELETE' SELECT@flag='鏁告摎搴?/span>'+@backup_db_name+'鍌欎喚鎴愬姛!' RETURN END ELSE BEGIN SELECT@flag='鏁告摎搴倷浠借礬寰戦尟瑾?' RETURN END END **浠ヤ笂閬庣▼鍦⊿QL SERVER涓嬫脯瑭﹂氶亷
]]>sql瀹炵幇鍒嗘鍔熻兘鐨勫嚱鏁?鍘繪帀閲嶅璁板綍錛岃幏鍙栭噸澶嶈褰?/title>http://m.shnenglu.com/lilac/archive/2008/01/13/41073.html鏉庝簹鏉庝簹Sat, 12 Jan 2008 16:12:00 GMThttp://m.shnenglu.com/lilac/archive/2008/01/13/41073.htmlhttp://m.shnenglu.com/lilac/comments/41073.htmlhttp://m.shnenglu.com/lilac/archive/2008/01/13/41073.html#Feedback0http://m.shnenglu.com/lilac/comments/commentRss/41073.htmlhttp://m.shnenglu.com/lilac/services/trackbacks/41073.html
sql瀹炵幇鍒嗘鍔熻兘鐨勫嚱鏁?/a>
--綆鍗曞垎孌佃鍙?/font>
declare @b varchar(20)
select @b='a-b-c-d-e'--澶勭悊
declare @sql varchar(4000)
select @sql= 'select '''+replace(@b,'-',''' union all select ''') ''''exec(@sql)
--瀹炵幇 split 鍒嗘鍔熻兘鐨勫嚱鏁?/font>
createfunction f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
asbegindeclare @i intset @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begininsert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
endif @SourceSql<>'\'insert @temp values(@SourceSql)
returnend--鐢ㄦ硶:
select * from dbo.f_split('A:B:C:D:E',':')
--杈撳嚭
a
1 A
2 B
3 C
4 D
5 E
--瀹炵幇綆鍗曟ā緋婃煡鎵劇殑榪囩▼
create procedure splitter
@strvarchar(1024)
asdeclare @s varchar(1024)
declare @i intset @s='select message from T_QQMsgLog where 1=1'set @i=1
]]>綆鍗曞瘑鐮佸姞瀵嗚繃紼?/title>http://m.shnenglu.com/lilac/archive/2008/01/13/41071.html鏉庝簹鏉庝簹Sat, 12 Jan 2008 16:11:00 GMThttp://m.shnenglu.com/lilac/archive/2008/01/13/41071.htmlhttp://m.shnenglu.com/lilac/comments/41071.htmlhttp://m.shnenglu.com/lilac/archive/2008/01/13/41071.html#Feedback0http://m.shnenglu.com/lilac/comments/commentRss/41071.htmlhttp://m.shnenglu.com/lilac/services/trackbacks/41071.html--鏂板鎴栦慨鏀瑰瘑鐮?br>createproc modifypwd @uid varchar(16),@pwd varchar(16) as declare @encode varbinary(255) set @encode=convert(varbinary(255),pwdencrypt(@pwd)) ifexists(select uid from [user] where uid=@uid) update [user] set pwd=@encode where uid=@uid else insertinto [user](uid,pwd) values(@uid,@encode) go
--鍒ゆ柇瀵嗙爜鏄惁姝g‘ createproc checkid @uid varchar(16),@pwd varchar(16) as declare @encode varbinary(255) select @encode=pwd from [user] where uid=@uid if pwdcompare(@pwd,@encode,0)='1' select'鐧誨綍鎴愬姛' else select'鐢ㄦ埛鍚嶆垨瀵嗙爜閿? GO
--榪斿洖闅忔満瀛楃涓茬殑榪囩▼ createproc randomchar @times int,@result varchar(255) out as declare @k int declare @r int declare @i int declare @s varchar(255) set @i=0 set @s='' while @i<@times begin set @k=rand()*61 if @k<26 set @r=@k+97 elseif @k>51 set @r=@k-4 else set @r=@k 39 set @s=@s char(@r) set @i=@i 1 end set @result=@s --璋冪敤 declare @s varchar(255) exec randomchar 20,@s out
declare @sql varchar(4000) set @sql = 'select 濮撳悕' select @sql = @sql + ',sum(case 瀛︾ when ''' 瀛︾ ''' then 鎴愮嘩 end) as ' 瀛︾ from (selectdistinct 瀛︾ from CJ) as a set @sql = @sql ' from cj groupby 濮撳悕' exec(@sql)
鍒涘緩涓涓悎騫剁殑鍑芥暟 createfunction fmerg(@idint) returnsvarchar(8000) as begin declare @strvarchar(8000) set @str='' select @str=@str','cast(pid asvarchar) from 琛ˋ whereid=@id set @str=right(@str,len(@str)-1) return(@str) end go
--璋冪敤鑷畾涔夊嚱鏁板緱鍒扮粨鏋?br>selectdistinctid,dbo.fmerg(id) from 琛ˋ