sql文档中心

Use 数据库
DECLARE @ProcName varchar(50)
Create Table #tmpName(Content varchar(2000))
Create Table #tmp(ProcName varchar(2000),Content1 varchar(8000))
--定义一个游标
DECLARE SearchProc CURSOR FOR
--查询数据库中存储过程的名称，尽量去除系统PROC，可以根据crdate时间字段来寻找非系统PROC
select name from sysobjects where type='P' and name not like 'dt_%'
OPEN SearchProc
FETCH NEXT from SearchProc
INTO @ProcName
WHILE @@FETCH_STATUS >=0
BEGIN
    Print @ProcName
    Insert Into #tmpName(Content) Exec sp_helptext @ProcName
    Insert Into #tmp(ProcName,Content1) select @ProcName,#tmpName.Content from #tmpName
    --填充完就清空一下临时表
    Truncate table #tmpName
    FETCH NEXT from SearchProc
    INTO @ProcName
END
CLOSE SearchProc
DEALLOCATE SearchProc
GO
select ProcName from #tmp where Content1 like '%查找内容%' group by ProcName
select ProcName,Content1 ......

1.使用Management Studio Express，用“Windows身份验证”登录，选中SQL服务器名，右击鼠标选择属性，在服务器属性选项页面，选择“安全性”，将服务器身份验证由 “Windows身份验证”改为“SQL Server和Windows身份验证”，单击确定。
2.使用SQL Server 2005外围应用配置器，选择“服务和连接的外围应用配置器”，选SQL Express-->Database Engine-->服务，先停止，再启动，重启SQL Server服务器。
3.再回到Management Studio Express，选择“安全性”-->登录名-->sa，右击鼠标，在登录属性-sa页面，选择状态，将登录由“禁用”改为“启用”（此时可设置sa登录密码或取消登录密码，在常规选项中），单击确定。
......

--1. 创建表，添加测试数据
CREATE TABLE tb(id int, [value] varchar(10))
INSERT tb SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
--SELECT * from tb
/**//*
id value
----------- ----------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
(5 row(s) affected)
*/
--2 在SQL2000只能用自定义函数实现
----2.1 创建合并函数fn_strSum，根据id合并value值
GO
CREATE FUNCTION dbo.fn_strSum(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SET @values = ''
SELECT @values = @values + ',' + value from tb WHERE id=@id
RETURN STUFF(@values, 1, 1, '')
END
GO
-- 调用函数
SELECT id, VALUE = dbo.fn_strSum(id) from tb GROUP BY id
DROP FUNCTION dbo.fn_strSum
----2.2 创建合并函数fn_strSum2，根据id合并value值
GO
CREATE FUNCTION dbo.fn_strSum2(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SELECT @values = isnull(@values + ',', '') + value from t ......

1. Q. What is a join?
   A. Join is a process of retrieve pieces of data from different sets (tables) and returns them to the user or program as one joined collection of data.
2. Q. Can a table have more than one foreign key defined?
   A. A table can have any number of foreign keys defined. It can have only
       one primary key defined.
3. Q. List all the possible values that can be stored in a BOOLEAN data field.
   A. There are only two values that can be stored in a BOOLEAN data field:
         -1(true) and 0(false).
4. Q. What is a stored procedure?
    A. A procedure is a group of PL/SQL statements that can be called by
        a name. Procedures do not return values they perform tasks.
5. Q. What is Normalization?
    A. The process of table design is called normalization.
6. Q. Write ......

你的SQL Server 2000必须打上SP3的补,然后安装Microsoft的JDBC驱动,将以下三个jar包复制到WebContent\WEB-INF\lib
msbase.jar
mssqlserver.jar
msutil.jar
下面是测试代码，没有报错数据库就能正常连接
public class DbcTest {
/**
* @param args
*/
public static void main(String[] args) {
String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String url = "jdbc:microsoft:sqlserver://58.154.128.22:1433;DatabaseName=JavaDaoDataBase";
String user = "sa";
String password = "sa";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
} catch (SQLException e) {
System.out.println(e.toString());
}
System.out.println("数据库操作成功，恭喜你");
}
}
......

在运行窗口输入regedit，打开注册表编辑器，在HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Session Manager中找到PendingFileRenameOperations，删除该键值，关闭注册表编辑器。重新安装SQL Server 2000,此时,就可以正常进入期待的安装界面了。 ......

sql查询所有存储过程内容利用游标和临时表

解决 sql server 2005 express中sa不能登录

实现多行合并一行的SQL [MSSQL2005]写法

实现多行合并一行的SQL [MSSQL2005]写法

简单的SQL面试题

Java 连接 SQL Server 2000示例代码

Java 连接 SQL Server 2000示例代码

SQL Server挂起