Re: Returning a closed recordset...

Try
SELECT * FROM MyDatabase..MyTable WHERE...

You can also set the Initial Catalog on the Connection to switch databases
or in ADO.NET change it in code and let ADO.NET handle it.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


"Griff" <Howling@xxxxxxxx> wrote in message
news:%23hBEg7FwFHA.2664@xxxxxxxxxxxxxxxxxxxxxxx
>I have created a connection object to my sqlserver using the following
>connection string:
>
> "Provider=SQLOLEDB;Password=*******;Persist Security Info=True;User
> ID=**;Data Source=" & sServerName & ;Initial Catalog=" & sDatabase
>
> This SQLServer has many databases on it, all with identical schema.
>
> I create some SQL that I wish to use against all the databases in turn:
>
> sSQL = "USE " & db & " SELECT * from mytable where ...."
>
> db is a variable that names the specific database that I wish to query.
>
> If I trap this SQL and throw it in QueryAnalyzer (pointing to the wrong
> database) then it correctly brings back the data from the right database
> (i.e. it understands the "use db " prefix)
>
> However, when I run this in my code, it simply returns a recordset whose
> state is closed (= 0). If I "corrupt" the SQL, it will happily tell me
> that an object (such as a table name) does not exist.
>
> The VB code (actually, it's VBScript since it's within a classic ASP page)
> that I use to get the recordset is at the end of this post, and for the
> rest of the program this happily returns open recordsets (with the same
> connection object)
>
> So....any ideas why a recordset whose state = 0 is being returned?
>
> Thanks
>
> Griff
>
> --------------
>
> private function getRecordset(byVal oCN, byVal sSQL)
> Dim oRS
>
> set oRS = CreateObject("ADODB.Recordset")
> with oRs
> .CursorLocation = adUseClient
> .LockType = adLockBatchOptimistic
> .CursorType = adOpenStatic
> .ActiveConnection = oCN
>
> .Source = sSQL
>
> .Open
>
> .ActiveConnection = Nothing
> end with
>
> set getRecordset = oRS
> end function
>
>


.

Relevant Pages

  • Re: Great Restaurant in Miami - next time you are in South Florida
    ... Zakariya, in front of databases ... cosmetic and immediate, fixs in connection with it, noticing ...
    (sci.crypt)
  • Re: IIS6 Dynamic pages execusion problem
    ... I'm sure that connection objects to databases are closed probably and the ... "Rany M. Sabry" wrote in message ... > I have a number of Web Servers running IIS6 hosted on Windows 2003 ...
    (microsoft.public.inetserver.iis)
  • Re: Switching ODBC Oracle databases
    ... Also is there something in the connection string I am using that causes ODBC ... "Lynn Trapp" wrote: ... databases in the 10th and then you would only have to create the single ODBC ...
    (microsoft.public.access.externaldata)
  • Re: Setting NO COUNT default on a connection only(not the entire datab
    ... In the case of ADO.NET I suggest use of the SqlClient namespace--not OleDb. ... Hitchhiker's Guide to Visual Studio and SQL Server ... ON for a specific SQL Server connection? ... We have some databases that will need to be consolidated onto a sinlge ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Changing a DataAdapter connection
    ... Assign new connection to adapter's command instances ... > I have several servers hosting SQL databases. ... > I have a ComboBox that let me chose the server, ...
    (microsoft.public.dotnet.general)