Code works differently based on the provider

Hello

The following code does not function if I use SQLOLEDB if I omit the provide and default to ODBC OLE DB it works correctly. I am assume I am coding something wrong for a SQLOLEDB provide. Any help is greatly appricated.

VB Code

Public Function SqlExecuteResult(xSQL As String, sServer As String, sDatabase As String, sUserName As String, sPassword As String, sCaller As String, Optional bLog As Boolean = False) As Object

Dim oDB As Object
Dim oRS As Object

Set oDB = CreateObject("adodb.connection")
Set oRS = CreateObject("adodb.recordset")

oDB.open "driver={SQL Server};provider=sqloledb;server=" & sServer & ";database=" & sDatabase & ";uid=" & sUserName & ";pwd=" & sPassword & ";"
oRS.CursorLocation = adUseClient
oRS.CursorType = adOpenStatic
Set oRS.ActiveConnection = oDB
oRS.open xSQL
Set oRS.ActiveConnection = Nothing
Set SqlExecuteResult = oRS
oDB.Close
Set oDB = Nothing

End Function

Private Sub Form_Load()
Dim rs As Object
Set rs = SqlExecuteResult("exec NextEntry 'SentMessages'", "surecomp-bob", "pmsureus33", "sa", "", "")
MsgBox rs.fields(0)
End Sub

SQL proceedure

CREATE PROCEDURE NextEntry @.CounterName Varchar(20) AS

begin
declare @.counter int
select @.counter = counter from counters where countername = @.counterName
select @.counter = @.counter + 1
update counters set counter = @.counter where countername = @.countername
select counter from counters where countername = @.counterName
End
GO

Thanks

Bob Jenkin

Are you having both "driver=" and "provider=sqloledb" in the connection string?
If you specified sqloledb you should have removed "driver=" part.

code information for unused indexes in sql server

Hi,
I want to know about the unused indexes. Is sql server provide any direct
api or services to give information about the unused indexes.
My problem is to find indexes which are not used in certain period of time
and if yes then delete that indexes.
Please help me by providing some suggestion or solution.Perhaps you need run ITW to get some info about unused indexes
"Nikhil Kumar Jain" <Nikhil Kumar Jain@.discussions.microsoft.com> wrote in
message news:D4D1D792-5015-4D3C-B5D3-7C7474CFD95B@.microsoft.com...
> Hi,
> I want to know about the unused indexes. Is sql server provide any direct
> api or services to give information about the unused indexes.
> My problem is to find indexes which are not used in certain period of time
> and if yes then delete that indexes.
> Please help me by providing some suggestion or solution.
>|||In profiler you can get indexid for your queries for the scans event.
Run profiler for enough period of times and also run all queries which
you use for number of times.
Now from all indexid , object id in profiler and sysindex table you
can find which indexes are being used.
Remanining indexes you can decide what to do with it.
Regards
Amish|||Thanks for your reply.
Is there no other way to do the same.
Because i want to do all this things programitically. No manual intervention
is required.
I have to write a code which will search for unused indexes and delete it if
it were not used in some periods of times.
Thats why i want some alternate approach for this.
Regards,
"amish" wrote:

> In profiler you can get indexid for your queries for the scans event.
> Run profiler for enough period of times and also run all queries which
> you use for number of times.
> Now from all indexid , object id in profiler and sysindex table you
> can find which indexes are being used.
> Remanining indexes you can decide what to do with it.
> Regards
> Amish
>|||Of course, there is the Index Tuning Wizzard.
http://search.microsoft.com/search/...r />
ng+Wizard
However, it sounds like you want to do this programmatically. I don't think
that the system tables, at least in SQL Server 2000, store meta data about
the frequency of when indexes are utilized.
http://search.microsoft.com/search/...indexes

Therefore, monitoring usage of indexes would involve examining the execution
plan when a query is executed. Read up in Books Online about SET STATISTICS
PROFILE ON.
http://search.microsoft.com/search/...TICS+PROFILE+ON
When ON, a query will return it's result set followed by another result set
that contains data about the execution plan (joins, index scans, sorts,
etc.). You can write an application that parses this data (perhaps the
StmtText column) for index usage information for specific queries.
"Nikhil Kumar Jain" <NikhilKumarJain@.discussions.microsoft.com> wrote in
message news:5D59AF07-62A0-4195-951E-DC29E1A2A82A@.microsoft.com...
> Thanks for your reply.
> Is there no other way to do the same.
> Because i want to do all this things programitically. No manual
> intervention
> is required.
> I have to write a code which will search for unused indexes and delete it
> if
> it were not used in some periods of times.
> Thats why i want some alternate approach for this.
> Regards,
>
> "amish" wrote:
>

Clustered SQL Server 2000 is not seen by fn_virtualservernodes()

Hello,
Firstly I will provide some background. Few weeks before I started
work as database administrator but without cluster experience, and few
days ago I got a project to add sql serve pasive node to Windows 2003
cluster, I looked the internet but no serious sollutions wasn't find.
Nobody in company has idea how SQL Server was installed on this
cluster. Cluster consist of two machines with cluster services and
storage resurces installed, one has sql insatlled second, added later,
is waiting for my instalation. Cluster resuorces directing on active
SQL instance.
Before adding node quest I did some checks and found out that existing
sql node isn't recognize by fn_virtualservernodes() function. When I
wanted to add node according to Microsoft
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
I got error "Another Virtual Server of the same name exists on the
network error".
And finally my questions do have idea how to add second SQL node to
this configuration?
Or do I have to uninstall active sql serevr and start standard sql
serevr instalation on cluster?
Any help appreciate.
Regards,
BartekBartolo wrote:
> Hello,
> Firstly I will provide some background. Few weeks before I started
> work as database administrator but without cluster experience, and few
> days ago I got a project to add sql serve pasive node to Windows 2003
> cluster, I looked the internet but no serious sollutions wasn't find.
> Nobody in company has idea how SQL Server was installed on this
> cluster. Cluster consist of two machines with cluster services and
> storage resurces installed, one has sql insatlled second, added later,
> is waiting for my instalation. Cluster resuorces directing on active
> SQL instance.
> Before adding node quest I did some checks and found out that existing
> sql node isn't recognize by fn_virtualservernodes() function. When I
> wanted to add node according to Microsoft
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
> I got error "Another Virtual Server of the same name exists on the
> network error".
> And finally my questions do have idea how to add second SQL node to
> this configuration?
> Or do I have to uninstall active sql serevr and start standard sql
> serevr instalation on cluster?
> Any help appreciate.
> Regards,
> Bartek
hi Bartek,
I have few years of experience with MSCS and MSSQL. I setup SQL on MSCS
several times but never tried to add node later. AFAIK you need to
uninstall sql server and then install it again. This is not hard to do.
good luck and hth|||Thanks a lot Zarko!
It seems I have to take into account uninstallation and installation
from the very begining.
Best regards,
Bartek
On 7 Lut, 11:39, Zarko Jovanovic <mind_lessIsHsAsTsEsSsPsA...@.inet.hr>
wrote:
> Bartolo wrote:
> > Hello,
> > Firstly I will provide some background. Few weeks before I started
> > work as database administrator but without cluster experience, and few
> > days ago I got a project to add sql serve pasive node to Windows 2003
> > cluster, I looked the internet but no serious sollutions wasn't find.
> > Nobody in company has idea how SQL Server was installed on this
> > cluster. Cluster consist of two machines with cluster services and
> > storage resurces installed, one has sql insatlled second, added later,
> > is waiting for my instalation. Cluster resuorces directing on active
> > SQL instance.
> > Before adding node quest I did some checks and found out that existing
> > sql node isn't recognize by fn_virtualservernodes() function. When I
> > wanted to add node according to Microsoft
> >http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failcl...
> > I got error "Another Virtual Server of the same name exists on the
> > network error".
> > And finally my questions do have idea how to add second SQL node to
> > this configuration?
> > Or do I have to uninstall active sql serevr and start standard sql
> > serevr instalation on cluster?
> > Any help appreciate.
> > Regards,
> > Bartek
> hi Bartek,
> I have few years of experience with MSCS and MSSQL. I setup SQL on MSCS
> several times but never tried to add node later. AFAIK you need to
> uninstall sql server and then install it again. This is not hard to do.
> good luck and hth

Clustered SQL Server 2000 is not seen by fn_virtualservernodes()

Hello,
Firstly I will provide some background. Few weeks before I started
work as database administrator but without cluster experience, and few
days ago I got a project to add sql serve pasive node to Windows 2003
cluster, I looked the internet but no serious sollutions wasn't find.
Nobody in company has idea how SQL Server was installed on this
cluster. Cluster consist of two machines with cluster services and
storage resurces installed, one has sql insatlled second, added later,
is waiting for my instalation. Cluster resuorces directing on active
SQL instance.
Before adding node quest I did some checks and found out that existing
sql node isn't recognize by fn_virtualservernodes() function. When I
wanted to add node according to Microsoft
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
I got error "Another Virtual Server of the same name exists on the
network error".
And finally my questions do have idea how to add second SQL node to
this configuration?
Or do I have to uninstall active sql serevr and start standard sql
serevr instalation on cluster?
Any help appreciate.
Regards,
Bartek
Thanks a lot Zarko!
It seems I have to take into account uninstallation and installation
from the very begining.
Best regards,
Bartek
On 7 Lut, 11:39, Zarko Jovanovic <mind_lessIsHsAsTsEsSsPsA...@.inet.hr>
wrote:
> Bartolo wrote:
>
>
> hi Bartek,
> I have few years of experience with MSCS and MSSQL. I setup SQL on MSCS
> several times but never tried to add node later. AFAIK you need to
> uninstall sql server and then install it again. This is not hard to do.
> good luck and hth