ASP连接sql server实例解析

1、首先确定自己的iis没有问题

2、其次确定自己sqlserver没有问题

然后在iis的目录wwwroot里，建立一个文件 名为testSqlServer.asp，编写代码如下即可

<% 'ole db连接 set cnn1 = Server.CreateObject("ADODB.Connection") '连接串需要注意sqlserver的实例名，是否是默认，非默认必须要写出来 cnn1.Open "provider=sqloledb;data source=./sqlexpress;initial catalog=master;user id=sa;password=000000;" sql = "select * from master..spt_values" set rs= Server.CreateObject("ADODB.RecordSet") rs.Open sql,cnn1 ,1,1 '获得连接信息与查询结果行数 Response.write("连接1："&cnn1.ConnectionString&"<br/> spt_values行数："&rs.recordcount&"<br/><br/>") '循环结果输出 do While not rs.eof Response.write(rs(0)&","&rs(1)&","&rs(2)&","&rs(3)&","&rs(4)&"<br/>") rs.MoveNext Loop rs.close() cnn1.close() %> <% 'odbc连接 set cnn2 = Server.CreateObject("ADODB.Connection") cnn2.Open "Driver={SQL Server};Server=./sqlexpress;Database=master;UID=sa;PWD=000000;" sql = "select * from master..spt_values" set rs= Server.CreateObject("ADODB.RecordSet") rs.Open sql,cnn2 ,1,1 Response.write("连接2："&cnn2.ConnectionString&"<br/> spt_values行数："&rs.recordcount&"<br/>") do While not rs.eof Response.write(rs(0)&","&rs(1)&","&rs(2)&","&rs(3)&","&rs(4)&"<br/>") rs.MoveNext Loop rs.close() cnn2.close() %>

在ie地址输入http://localhost/testsqlserver.asp，即可看到如下结果

连接1：Provider=SQLOLEDB.1;Password=000000;User ID=sa;Initial Catalog=master;Data Source=./sqlexpress;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PC-201003062254;Use Encryption for Data=False;Tag with column collation when possible=False spt_values行数：2346 rpc,1,A ,, pub,2,A ,, sub,4,A ,, dist,8,A ,, dpub,16,A ,, rpc out,64,A ,, data access,128,A ,, collation compatible,256,A ,, system,512,A ,, use remote collation,1024,A ,, lazy schema validation,2048,A ,, 。。。。。。。。。。。。。。。 serial writes,32,V ,, read only,4096,V ,0,1 deferred,8192,V ,0,1 连接2：Provider=MSDASQL.1;Extended Properties="DRIVER=SQL Server;SERVER=./sqlexpress;UID=sa;PWD=000000;APP=Microsoft? Windows? Operating System;WSID=PC-201003062254;DATABASE=master" spt_values行数：2346 rpc,1,A ,, pub,2,A ,, sub,4,A ,, dist,8,A ,, dpub,16,A ,, rpc out,64,A ,, data access,128,A ,, collation compatible,256,A ,, system,512,A ,, use remote collation,1024,A ,, lazy schema validation,2048,A ,, 。。。。。

很容易吧，你也去试试吧，其实编码都是很简单的事情。

补充，调用存储过程

<% 'ole db连接 set Conn= Server.CreateObject("ADODB.Connection") '连接串需要注意sqlserver的实例名，是否是默认，非默认必须要写出来 Conn.Open "provider=sqloledb;data source=192.168.1.101;initial catalog=master;user id=sa;password=000000;" %> <% Const adInteger= 3 Const adVarChar= 200 Const adParamInput= 1 Const adParamOutput= 2 Const adCmdSPStoredProc = 4 Set adoComm = CreateObject("ADODB.Command") With adoComm .ActiveConnection = Conn .CommandType = adCmdSPStoredProc .Prepared = true .CommandText = "sp_checklogin" .Parameters.append .CreateParameter("@userid",adVarChar,1,6,"NO001") '参数名，参数类型，输入输出类型，长度，值 .Parameters.append .CreateParameter("@flag",adInteger,2) '返回参数,返回总记录数,数据长度可以省略. .execute End With flag = adoComm(1) Set adoComm = Nothing response.write "执行本程序后，存储过程返回值Flag："&flag&"<br>" if flag=0 then response.write "提示：成功登录！（该帐户今天第一次登录）" else response.write "提示：该帐号已有人登录，不可再登录" end if %> <% sql = "select * from master..spt_values" set rs= Server.CreateObject("ADODB.RecordSet") rs.Open sql,Conn ,1,1 '获得连接信息与查询结果行数 Response.write("连接1："&Conn.ConnectionString&"<br/> spt_values行数："&rs.recordcount&"<br/><br/>") '循环结果输出 do While not rs.eof Response.write(rs(0)&","&rs(1)&","&rs(2)&","&rs(3)&","&rs(4)&"<br/>") rs.MoveNext Loop rs.close() 'Conn.close() %>

SQL存储过程如下：
CREATE procedure sp_checklogin
@userid varchar(6) , -- 操作帐号
@flag int out -- 输出参数 0:没有登录 1:已经登录
as
declare @sql nvarchar(100)
IF object_id('tempdb.dbo.##'+@userid) is null
begin
set @sql='create table ##'+@userid+'( userid varchar(6))'
exec(@sql);
set @flag = 0
end
Else
set @flag = 1
GO