sql server 2008图形界面中的问题

2010-05-03 03:05:14 212 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

sql server 2008 创建用户这两天用到了 sql server 2008 ，主要是建数据库，建表和创建用户。在 “Windows 身份验证” 下，创建了数据库和用户，然后用 SQL Server 身份验证登录，却提示错误 18452，找了一下资料改法如下：[无法连接到服务器服务器：消息18452，级别16，状态1 [Microsoft][ODBC SQL Server Drive 阅读详情

以前一直玩2000的，今天装了2008，请问下，2000中表右键有个“返回所有行”，在2008版中哪里有看所有行？
另外2008版的作业，怎么在面板中全部导出t-sql脚本？

...全文

SQL server 2008 安装重启问题安装SQL sever 2008是总是提示需要重新启动才能继续安装，第一次安装时重新启动之后确实能继续安装了，但是今天再次安装的时候却安装不了。重启之后还是不行，提示如下图：本着不讲究是发现的源动力的想法，百度了一下，终于找到了解决的办法，下面以图片配解说的方式展示出来。 1. 首先windows + R 运行里边输入regedit ,选择确定，进入阅读详情

solidvacuum

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

htl258_Tony 2010-05-03

打赏
举报

--不好找，帮你改了一个批量生成作业的过程，你试试：

create proc Generate_Job_Script_for2008

as

set nocount on

declare @jobname nvarchar(1000)

declare @tb table (id int identity(1,1),sqltext varchar(4000))

declare @job_category_name varchar(1000)

declare @job_id varchar(100)

declare @changeline varchar(10)

declare @enabled int,

    @notify_level_eventlog int,

    @notify_level_email int,

    @notify_level_netsend int ,

    @notify_level_page int,

    @delete_level int,

    @description varchar(1000),

    @category_name varchar(1000),

    @owner_login_name varchar(1000)



declare c cursor for 

    select name from msdb.dbo.sysjobs

open c

fetch c into @jobname

while @@FETCH_STATUS=0

begin

    set @changeline = ','+char(13)

    select @job_category_name = replace(b.name,'''',''''''),@job_id = a.job_id

    from msdb.dbo.sysjobs a join msdb.dbo.syscategories b on a.category_id = b.category_id where a.name =@jobname



    insert into @tb(sqltext)

    select 'USE [msdb]' union all

    select 'GO' union all

    select '/****** 对象: Job ['+@jobname+']    脚本日期: '+convert(varchar(10),getdate(),101)+' '+convert(varchar(8),getdate(),108)+' ******/' union all

    select 'BEGIN TRANSACTION' union all

    select 'DECLARE @ReturnCode INT' union all

    select 'SELECT @ReturnCode = 0' union all

    select '/****** 对象: JobCategory ['+@job_category_name+']    脚本日期: '+convert(varchar(10),getdate(),101)+' '+convert(varchar(8),getdate(),108)+' ******/' union all

    select 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'''+@job_category_name+''' AND category_class=1)' union all

    select 'BEGIN' union all

    select 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N'''+@job_category_name+'''' union all

    select 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' union all

    select '' union all

    select 'END'



    select

    @enabled                                 = enabled,

    @notify_level_eventlog         =notify_level_eventlog,

    @notify_level_email                 =notify_level_email,

    @notify_level_netsend         =notify_level_netsend,

    @notify_level_page                 =notify_level_page,

    @delete_level                         =delete_level,

    @description                         =description,

    @category_name                         =@job_category_name,

    @owner_login_name                 =suser_sname(owner_sid)

    from msdb.dbo.sysjobs

    where job_id =@job_id



    insert into @tb(sqltext)

    select 'DECLARE @jobId BINARY(16)' union all

    select 'EXEC @ReturnCode = msdb.dbo.sp_add_job ' union all

    select '                 @job_name=N'''+@jobname+''', ' union all

    select '                 @enabled='+isnull(convert(varchar(10),@enabled),'')+', ' union all

    select '                 @notify_level_eventlog='+isnull(convert(varchar(10),@notify_level_eventlog),'')+', ' union all

    select '                 @notify_level_email='+isnull(convert(varchar(10),@notify_level_email),'')+', ' union all

    select '                 @notify_level_netsend='+isnull(convert(varchar(10),@notify_level_netsend),'')+', ' union all

    select '                 @notify_level_page='+isnull(convert(varchar(10),@notify_level_page),'')+', ' union all

    select '                 @delete_level='+isnull(convert(varchar(10),@delete_level),'')+', ' union all

    select '                 @description=N'''+isnull(convert(varchar(1000),replace(@description,'''','''''')),'')+''', ' union all

    select '                 @category_name=N'''+isnull(convert(varchar(1000),replace(@category_name,'''','''''')),'')+''', ' union all

    select '                 @owner_login_name=N'''+isnull(convert(varchar(100),replace(@owner_login_name,'''','''''')),'')+''', @job_id = @jobId OUTPUT' union all

    select 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'



    if exists( select 1 from msdb.dbo.sysjobsteps where job_id =@job_id)

    begin

    insert into @tb(sqltext)

    select c1 from (

    select 1 as id,step_id,

    '/****** 对象: Step ['+convert(varchar(10),step_id )+']    脚本日期: '+convert(varchar(10),getdate(),101)+' '+convert(varchar(8),getdate(),108)+' ******/'+char(13)+

    'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep '+replace(@changeline,',','') +

    '                 @job_id = @JobID'+@changeline+

    '                 @step_name = N'''+isnull(replace(step_name,'''','''''') ,'')+''''+@changeline+

    '                 @step_id = '+isnull(convert(varchar(10),step_id ),'') + @changeline+

    '                 @cmdexec_success_code = '+isnull(convert(varchar(10),cmdexec_success_code ),'')+ @changeline+

    '                 @on_success_action = '+isnull(convert(varchar(10),on_success_action ),'') + @changeline+

    '                 @on_success_step_id = '+isnull(convert(varchar(10),on_success_step_id ),'') + @changeline+

    '                 @on_fail_action = '+isnull(convert(varchar(10),on_fail_action ),'') + @changeline+

    '                 @on_fail_step_id = '+isnull(convert(varchar(10),on_fail_step_id ),'') + @changeline+

    '                 @retry_attempts = '+isnull(convert(varchar(10),retry_attempts ),'') + @changeline+

    '                 @retry_interval = '+isnull(convert(varchar(10),retry_interval ),'') + @changeline+

    '                 @os_run_priority = '+isnull(convert(varchar(10),os_run_priority ),'') + @changeline+

    '                 @subsystem = N'''+isnull(convert(varchar(100),replace(subsystem,'''','''''') ),'') +''''+ @changeline+

    '                 @command = N'''+isnull(convert(varchar(8000),replace(command,'''','''''') ),'') +''''+ @changeline+

    '                 @database_name = N'''+isnull(convert(varchar(100),replace(database_name,'''','''''') ),'') +''''+ @changeline+

    '                 @flags = '+isnull(convert(varchar(10),flags ),'') + replace(@changeline,',','') as c1



    from msdb.dbo.sysjobsteps

    where job_id =@job_id



    union all



    select 2 as id,step_id, ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' as c1



    from msdb.dbo.sysjobsteps

    where job_id =@job_id

    ) abc

    order by step_id,id



    end



    insert into @tb(sqltext)

    select ' EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = '+isnull(convert(varchar(100),start_step_id),'')+' '

    from msdb.dbo.sysjobs

    where job_id =@job_id union all

    select '' union all

    select ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback '





    insert into @tb(sqltext)

    select ''



    if exists (select 1 from msdb.dbo.sysjobschedules where job_id =@job_id)

    begin

    --         insert into @tb(sqltext)

    --         select ' -- 添加作业调度'



    insert into @tb(sqltext)

    select c1 from (

    select 1 as id,a.schedule_id,

    'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule '+replace(@changeline,',','') +

    '                 @job_id = @JobID'+@changeline+

    '                 @name = N'''+isnull(replace(name,'''','''''') ,'')+''''+@changeline+

    '                 @enabled = '+isnull(convert(varchar(10),enabled ),'') + @changeline+

    '                 @freq_type = '+isnull(convert(varchar(10),freq_type ),'')+ @changeline+

    '                 @freq_interval = '+isnull(convert(varchar(10),freq_interval ),'') + @changeline+

    '                 @freq_subday_type = '+isnull(convert(varchar(10),freq_subday_type ),'') + @changeline+

    '                 @freq_subday_interval = '+isnull(convert(varchar(10),freq_subday_interval ),'') + @changeline+

    '                 @freq_relative_interval = '+isnull(convert(varchar(10),freq_relative_interval ),'') + @changeline+

    '                 @freq_recurrence_factor = '+isnull(convert(varchar(10),freq_recurrence_factor ),'') + @changeline+

    '                 @active_start_date = '+isnull(convert(varchar(100),active_start_date ),'') + @changeline+

    '                 @active_end_date = '+isnull(convert(varchar(100),active_end_date ),'') + @changeline+

    '                 @active_start_time = '+isnull(convert(varchar(100),active_start_time ),'') + @changeline+

    '                 @active_end_time = '+isnull(convert(varchar(100),active_end_time ),'') + replace(@changeline,',','')

    as c1

    from msdb.dbo.sysschedules a join msdb.dbo.sysjobschedules b

    on a.schedule_id = b.schedule_id

    where b.job_id =@job_id



    union all



    select 2 as id,schedule_id,' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' as c1

    from msdb.dbo.sysjobschedules

    where job_id =@job_id

    ) aa

    order by schedule_id,id



    end



    insert into @tb(sqltext)

    --select ' -- 添加目标服务器' union all

    select

    ' EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'''+case when a.server_id = 0 then '(local)' else b.srvname end +''' '

    from msdb.dbo.sysjobservers a join master.dbo.sysservers b

    on a.server_id = b.srvid

    where job_id =@job_id

    union all

    select ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback '



    insert into @tb(sqltext)

    select 'COMMIT TRANSACTION' union all

    select 'GOTO EndSave' union all

    select 'QuitWithRollback:' union all

    select '    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION' union all

    select 'EndSave:'

    fetch c into @jobname

end

close c

deallocate c

select sqltext from @tb

GO

--原脚本参考：http://hi.baidu.com/jixj/blog/item/5b5f07ec0212abd3b21cb131.html

--调用：

exec Generate_Job_Script_for2008

solidvacuum 2010-05-03

打赏
举报

[Quote=引用 5 楼 htl258 的回复:]
引用 3 楼 solidvacuum 的回复:
引用楼主 solidvacuum 的回复:
以前一直玩2000的，今天装了2008，请问下，2000中表右键有个“返回所有行”，在2008版中哪里有看所有行？
另外2008版的作业，怎么在面板中全部导出t-sql脚本？

怎么在面板中全部导出t-sql脚本？
导出数据的脚本参考楼上。导出结构脚本的方法你右键数据库-->任务-->生成脚……
[/Quote]

我是说导出“作业”的脚本啊，不是说数据库的！！

htl258_Tony 2010-05-03

打赏
举报

[Quote=引用 3 楼 solidvacuum 的回复:]
引用楼主 solidvacuum 的回复:
以前一直玩2000的，今天装了2008，请问下，2000中表右键有个“返回所有行”，在2008版中哪里有看所有行？
另外2008版的作业，怎么在面板中全部导出t-sql脚本？

怎么在面板中全部导出t-sql脚本？
[/Quote]导出数据的脚本参考楼上。导出结构脚本的方法你右键数据库-->任务-->生成脚本

喜-喜 2010-05-03

打赏
举报

可以导出数据库框架模式脚本，不能直接导出数据的 insert 脚本！

要导出数据的 insert 脚本，需依靠第三方存储过程，用下面的代码可以实现！

CREATE PROCEDURE dbo.UspOutputData 

@tablename sysname 

AS 

declare @column varchar(1000) 

declare @columndata varchar(1000) 

declare @sql varchar(4000) 

declare @xtype tinyint 

declare @name sysname 

declare @objectId int 

declare @objectname sysname 

declare @ident int 



set nocount on 

set @objectId=object_id(@tablename) 



if @objectId is null -- 判断对象是否存在 

begin 

print 'The object not exists' 

return 

end 

set @objectname=rtrim(object_name(@objectId)) 



if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密 

begin 

print 'object not in current database' 

return 

end 



if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判断对象是否是table 

begin 

print 'The object is not table' 

return 

end 



select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80 



if @ident is not null 

print 'SET IDENTITY_INSERT '+@TableName+' ON' 



declare syscolumns_cursor cursor



for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid 



open syscolumns_cursor 

set @column='' 

set @columndata='' 

fetch next from syscolumns_cursor into @name,@xtype 



while @@fetch_status < >-1 

begin 

if @@fetch_status < >-2 

begin 

if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理 



begin 

set @column=@column+case when len(@column)=0 then'' else ','end+@name 



set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','

end 



+case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char 

when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar 

when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime 

when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime 

when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier 

else @name end 



end 



end 



fetch next from syscolumns_cursor into @name,@xtype 



end 



close syscolumns_cursor 

deallocate syscolumns_cursor 



set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename 



print '--'+@sql 

exec(@sql) 



if @ident is not null 

print 'SET IDENTITY_INSERT '+@TableName+' OFF' 



GO



使用方法：



exec UspOutputData 你的表名



然后将运行后的结果存成.sql，加上用SQL Server生成的数据库脚本就可以了

另参考：如何为SQL Server表数据生成insert脚本

solidvacuum 2010-05-03

打赏
举报

[Quote=引用楼主 solidvacuum 的回复:]
以前一直玩2000的，今天装了2008，请问下，2000中表右键有个“返回所有行”，在2008版中哪里有看所有行？
另外2008版的作业，怎么在面板中全部导出t-sql脚本？
[/Quote]

怎么在面板中全部导出t-sql脚本？

bancxc 2010-05-03

打赏
举报

Select * from 表名

htl258_Tony 2010-05-03

打赏
举报

右键，选择前1000行，再在代码那里把TOP 1000 去掉，然后执行即可，就是这么麻烦。

最好用语句操作。

sql server 2008图形界面中的问题

相关推荐