]]>_֦的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导{?/title>http://www.shnenglu.com/lilac/archive/2008/01/13/41075.html李亚李亚Sat, 12 Jan 2008 16:21:00 GMThttp://www.shnenglu.com/lilac/archive/2008/01/13/41075.htmlhttp://www.shnenglu.com/lilac/comments/41075.htmlhttp://www.shnenglu.com/lilac/archive/2008/01/13/41075.html#Feedback0http://www.shnenglu.com/lilac/comments/commentRss/41075.htmlhttp://www.shnenglu.com/lilac/services/trackbacks/41075.html阅读全文
]]>备䆾服务器端SQL SERVER数据库至本地目录http://www.shnenglu.com/lilac/archive/2008/01/13/41074.html李亚李亚Sat, 12 Jan 2008 16:18:00 GMThttp://www.shnenglu.com/lilac/archive/2008/01/13/41074.htmlhttp://www.shnenglu.com/lilac/comments/41074.htmlhttp://www.shnenglu.com/lilac/archive/2008/01/13/41074.html#Feedback0http://www.shnenglu.com/lilac/comments/commentRss/41074.htmlhttp://www.shnenglu.com/lilac/services/trackbacks/41074.html/**//*數據庫備份存儲過E?/span>*/ /**//*支持從服務器備䆾數據x地機器上*/ -- CREATEPROCEDURE up_dbbackup ( @backup_db_nameVARCHAR(128), @filenameVARCHAR(128), /**//*備䆾路徑+文g?/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上徏立一個完全共享目錄db,指定一個用戶名和密?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 **以上過程在SQL SERVER下測試通過
]]>sql实现分段功能的函?L重复记录Q获取重复记?/title>http://www.shnenglu.com/lilac/archive/2008/01/13/41073.html李亚李亚Sat, 12 Jan 2008 16:12:00 GMThttp://www.shnenglu.com/lilac/archive/2008/01/13/41073.htmlhttp://www.shnenglu.com/lilac/comments/41073.htmlhttp://www.shnenglu.com/lilac/archive/2008/01/13/41073.html#Feedback0http://www.shnenglu.com/lilac/comments/commentRss/41073.htmlhttp://www.shnenglu.com/lilac/services/trackbacks/41073.html
sql实现分段功能的函?/a>
--单分D语?/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
--实现单模p查扄q程
create procedure splitter
@strvarchar(1024)
asdeclare @s varchar(1024)
declare @i intset @s='select message from T_QQMsgLog where 1=1'set @i=1
]]>单密码加密过E?/title>http://www.shnenglu.com/lilac/archive/2008/01/13/41071.html李亚李亚Sat, 12 Jan 2008 16:11:00 GMThttp://www.shnenglu.com/lilac/archive/2008/01/13/41071.htmlhttp://www.shnenglu.com/lilac/comments/41071.htmlhttp://www.shnenglu.com/lilac/archive/2008/01/13/41071.html#Feedback0http://www.shnenglu.com/lilac/comments/commentRss/41071.htmlhttp://www.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
--判断密码是否正确 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'd成功' else select'用户名或密码? GO
--q回随机字符串的q程 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 成W end) as ' 学科 from (selectdistinct 学科 from CJ) as a set @sql = @sql ' from cj groupby 姓名' exec(@sql)
创徏一个合q的函数 createfunction fmerg(@idint) returnsvarchar(8000) as begin declare @strvarchar(8000) set @str='' select @str=@str','cast(pid asvarchar) from 表A whereid=@id set @str=right(@str,len(@str)-1) return(@str) end go
--调用自定义函数得到结?br>selectdistinctid,dbo.fmerg(id) from 表A