ASP connection SQL Server Instance resolution

Source: Internet
Author: User
Tags odbc connection

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.