SqlServer 连接字串不使用数据库名 实现跨库查询

此次运用需要在同一服务器下，在一个库中SQL查询一组数据，作为参数提交到一个另一个库的函数中进行计算，以前没玩过，问了下度娘，未得到可用结果，遂根据查到的零星信息猜想，取消连接字串里面的库名称试试能不能同时操作多个库，经测试，顺利一次通过运行成功！详见以下代码（以ASP为例）：

    Set conn = Server.CreateObject("ADODB.Connection")    SET Rs = Server.CreateObject("ADODB.Recordset")    connstr= "Provider=SQLoledb;data source=.;UID=sa;PWD=youpassword;"  '不使用Initial Catalog=DBname"              conn.Open connstr    sql="SELECT * FROM [DBname].[dbo].[TableName] where StationCode ='条件' " rs.Open sql, conn ,1If Rs.eof And Rs.bof ThenResponse.write  "查询 数据错误！" SID=""elseResponse.write "SO2:" & rs.Fields("SO2").value & "
"end ifCloseDB 2sql="DECLARE @SO2_data decimal(20,10),@NO2_data decimal(20,10),@PM10_data decimal(20,10),@CO_data decimal(20,10),@O3_1h_data decimal(20,10),@O3_8h_data decimal(20,10),@PM25_data decimal(20,10),@des varchar(100),@PrimaryPollutant varchar(30),@AQI varchar(10);SELECT @CO_data=convert(decimal(20,10),[CO_24h]),@NO2_data=convert(decimal(20,10),[NO2_24h]),@O3_1h_data=convert(decimal(20,10),[O3_24h]),@O3_8h_data=convert(decimal(20,10),[O3_8h]),@PM10_data=convert(decimal(20,10),[PM10_24h]),@PM25_data=convert(decimal(20,10),[PM2_5_24h]),@SO2_data=convert(decimal(20,10),[SO2_24h])  FROM [ProvincePublishV2].[dbo].[AQIDataPublishLive] where StationCode ='1004A';set @des='PrimaryPollutant';SELECT @PrimaryPollutant=[EnvDataProvince].[dbo].[fn_AQI_Publish] (@SO2_data,@NO2_data,@PM10_data,@CO_data,@O3_1h_data,@O3_8h_data,@PM25_data,@des);set @des='AQI';SELECT @AQI=[EnvDataProvince].[dbo].[fn_AQI_Publish] (@SO2_data,@NO2_data,@PM10_data,@CO_data,@O3_1h_data,@O3_8h_data,@PM25_data,@des);SELECT @PrimaryPollutant as 'PrimaryPollutant' ,@AQI as 'AQI';"rs.Open sql, conn ,1If Rs.eof And Rs.bof ThenResponse.write  "查询AQI错误！" SID=""elseResponse.write "PrimaryPollutant:" & rs.Fields("PrimaryPollutant").value & ", AQI:" &  rs.Fields("AQI").valueend ifCloseDB 1'----附关闭数据连接子程序--------------------------------Public Sub CloseDB(allClose)    '1 ,AllClose & Nothing    '0 ,AllClose    '2 ,RS Close    '3 ,RS Close & Nothing    On Error Resume Next    If allClose = 1 Then        If Not RS Is Nothing Then            If RS.State = 1 Then RS.Close            Set RS = Nothing '----------------        End If        If Not conn Is Nothing Then            If conn.State = 1 Then conn.Close            Set conn = Nothing '---------------------        End If    ElseIf allClose = 0 Then        If Not RS Is Nothing Then            If RS.State = 1 Then RS.Close        End If        If Not conn Is Nothing Then            If conn.State = 1 Then conn.Close        End If    ElseIf allClose = 2 Then        If Not RS Is Nothing Then            If RS.State = 1 Then RS.Close        End If    ElseIf allClose = 3 Then        If Not RS Is Nothing Then            If RS.State = 1 Then RS.Close            Set RS = Nothing '--------------        End If    End If    'Response.write "function1 CONN closeed!"End Sub

里面的 DBname 即为正常连接时需要写在 Initial Catalog= 的库名，这里不写库名的话，在SQL语句中就必须带上库名加上[dbo] 标识再加上表名称来定位到数据表，切记！

即  [DBname].[dbo].[TableName] 

这样，更换了  DBname  后 就可以实现在多个库之间自由查询了。

此记.