ASP connection SQL Server Instance resolution

1. First determine your own IIS no problem

2, second to determine their own SQL Server no problem

Then in the IIS folder Wwwroot, create a file named Testsqlserver.asp, write code such as the following can be

<% ' OLE DB connection set cnn1 = Server.CreateObject ("ADODB. Connection ") ' connection strings need to be aware of SQL Server instance name, whether it is default, non-default must be written out 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, "Get connection information with query result rows Response.Write (" Connection 1: "&cnn1. connectionstring& "<br/> spt_values lines:" &rs.recordcount& "<br/><br/>") ' Loop result output do While Not Rs.eofResponse.write (RS (0) & "," &rs (1) & "," &rs (2) & "," &rs (3) & "," &rs (4) & " <br/> ") Rs. Movenextlooprs.close () cnn1.close ()%><% ' ODBC connection 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,1response.write ("Connection 2:" &cnn2. connectionstring& "<br/> spt_values lines:" &rs.recordcount& "&LT;br/> ") do and Not Rs.eofResponse.write (RS (0) &", "&rs (1) &", "&rs (2) &", "&rs (3) &", " &rs (4) & "<br/>") Rs. Movenextlooprs.close () cnn2.close ()%>

In the IE address input http://localhost/testsqlserver.asp, you can see such as the following results

Connection 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=falsespt_values lines: 2346rpc,1,a,, Pub,2,a,, Sub,4,a,, Dist,8,a,, dpub,16,a,, RPC ou T,64,a,, Data access,128,a,, collation Compatible,256,a,, System,512,a,, use remote Collation,1024,a,, lazy schema Valida Tion,2048,a,,。。。。。。。。。。。。。。。 Serial writes,32,v,, read only,4096,v, 0,1deferred,8192,v, 0,1 connection 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 lines: 2346rpc,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 Validati On,2048,a,,。。。。。

Very easy, you have to try it, in fact, the coding is very simple things.

Add, call stored procedure

<% ' OLE DB connection set conn= Server.CreateObject ("ADODB. Connection ") ' connection string need to be aware of SQL Server instance name, is default, non-default must be written out Conn.Open" Provider=sqloledb;data source=192.168.1.101;initial Catalog=master;user id=sa;password=000000; " %><%const adinteger= 3 Const advarchar=-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") ' parameter name, parameter type, input and output type, length, value. Parameters.Append. CreateParameter ("@flag", adinteger,2) ' Returns the number of total records, the data length can be omitted. Execute End withflag = Adocomm (1) Set Adocomm = Nothingresponse.write "After running this program, the stored procedure returns the value flag:" &flag& "<br>" if Flag=0 Then Response.Write "Prompt: Login successfully! (The account is logged on for the first time today) "Elseresponse.write" prompt: The account has been logged in, no more login "End If%><%sql =" SELECT * FROM Master. Spt_values "Set rs= Server.CreateObject (" ADODB. REcordset ") Rs. Open sql,conn, "Get connection information and query result rows Response.Write (" Connection 1: "&Conn.ConnectionString&" <br/> spt_values line count: "& amp;rs.recordcount& "<br/><br/>") ' Loop result output do and not Rs.eof Response.Write (RS (0) & "," &rs (1) & "," &rs (2) & "," &rs (3) & "," &rs (4) & "<br/>") Rs. MoveNext Loop rs.close () ' Conn.close ()%>

The SQL storage steps are as follows:
CREATE procedure Sp_checklogin
@userid varchar (6),--Operation account
@flag int out--output parameter 0: No login 1: Already logged in
As
declare @sql nvarchar (100)
IF object_id (' tempdb.dbo.## ' [email protected]) is null
Begin
Set @sql = ' CREATE TABLE # # ' [email protected]+ ' (userid varchar (6)) '
EXEC (@sql);
Set @flag = 0
End
Else
Set @flag = 1
GO

ASP connection SQL Server Instance resolution