ADOMD.Connection error

We are getting this error: 'ADODB.Connection: Provider is not specified
and there is no designated default provider' on a 64 bit machine with
both Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript
that backs up our Olap databases. The script (below) works fine on a 32
bit server with Sql 2K installed.
Function QueryDatabaseList()
Dim oConnectionServer
Dim oRS
Dim arrDatabase
Set oConnectionServer =CreateObject("ADODB.Connection")
oConnectionServer.ConnectionString = "Data Source=" & ServerName &
"; Provider=MSOLAP.2"
oConnectionServer.Open
Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
arrDatabase = oRs.GetRows
oConnectionServer.Close
Set oConnectionServer = Nothing
QueryDatabaseList = arrDatabase
End Function
I am pretty sure the problem has to do with the fact that the right
driver can not be found. But which one?
Thanks in advance,
Koni.
MSOLAP.2 is for AS2000
so try MSOLAP.3 for SSAS2005
"Koni" <kkogan@.haiint.com> wrote in message
news:uAgqmPoPHHA.1152@.TK2MSFTNGP03.phx.gbl...
> We are getting this error: 'ADODB.Connection: Provider is not specified
> and there is no designated default provider' on a 64 bit machine with both
> Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript that
> backs up our Olap databases. The script (below) works fine on a 32 bit
> server with Sql 2K installed.
> Function QueryDatabaseList()
> Dim oConnectionServer
> Dim oRS
> Dim arrDatabase
>
> Set oConnectionServer =CreateObject("ADODB.Connection")
> oConnectionServer.ConnectionString = "Data Source=" & ServerName & ";
> Provider=MSOLAP.2"
> oConnectionServer.Open
> Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
> arrDatabase = oRs.GetRows
> oConnectionServer.Close
> Set oConnectionServer = Nothing
> QueryDatabaseList = arrDatabase
> End Function
> I am pretty sure the problem has to do with the fact that the right driver
> can not be found. But which one?
>
> Thanks in advance,
> Koni.

ADOMD.Connection error

We are getting this error: 'ADODB.Connection: Provider is not specified
and there is no designated default provider' on a 64 bit machine with
both Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript
that backs up our Olap databases. The script (below) works fine on a 32
bit server with Sql 2K installed.
Function QueryDatabaseList()
Dim oConnectionServer
Dim oRS
Dim arrDatabase
Set oConnectionServer =CreateObject("ADODB.Connection")
oConnectionServer.ConnectionString = "Data Source=" & ServerName &
"; Provider=MSOLAP.2"
oConnectionServer.Open
Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
arrDatabase = oRs.GetRows
oConnectionServer.Close
Set oConnectionServer = Nothing
QueryDatabaseList = arrDatabase
End Function
I am pretty sure the problem has to do with the fact that the right
driver can not be found. But which one?
Thanks in advance,
Koni.MSOLAP.2 is for AS2000
so try MSOLAP.3 for SSAS2005
"Koni" <kkogan@.haiint.com> wrote in message
news:uAgqmPoPHHA.1152@.TK2MSFTNGP03.phx.gbl...
> We are getting this error: 'ADODB.Connection: Provider is not specified
> and there is no designated default provider' on a 64 bit machine with both
> Sql 2K 32bit and Sql 2K5 64bit installed. It happens from Vbscript that
> backs up our Olap databases. The script (below) works fine on a 32 bit
> server with Sql 2K installed.
> Function QueryDatabaseList()
> Dim oConnectionServer
> Dim oRS
> Dim arrDatabase
>
> Set oConnectionServer =CreateObject("ADODB.Connection")
> oConnectionServer.ConnectionString = "Data Source=" & ServerName & ";
> Provider=MSOLAP.2"
> oConnectionServer.Open
> Set oRS = oConnectionServer.OpenSchema(adSchemaCatalogs)
> arrDatabase = oRs.GetRows
> oConnectionServer.Close
> Set oConnectionServer = Nothing
> QueryDatabaseList = arrDatabase
> End Function
> I am pretty sure the problem has to do with the fact that the right driver
> can not be found. But which one?
>
> Thanks in advance,
> Koni.

Adodb.field error 80020009 in my code

Hi everybody,

I really don't find the bug in my code. It returns Adodb.field error '80020009'. I tried various things but it don't works. Can U look at my code and find the bug....

-------------------
My connection object is 'dbconn'
My object query is 'rssqlselect_clients'
---------------------

<form name="monform">
<select name="listeA" onchange=changeliste()>
<option value=0>Choisit une liste</option>


<% rssqlselect_clients.moveFirst
while not rssqlselect_clients.eof %>

<% while not t > c %>

<option value=<%= t %>>Liste <%


response.write rssqlselect_clients("nomclient")
%> </option>

<%

t = t + 1

wend %>



<%rssqlselect_clients.moveNext
wend
rssqlselect_clients.close
Dbconn.close%>

------------

Thank U very much.

BaRRonWhat line of script is generating your error?

ADODB.Connection error 800a0e7a

I am getting the following error, when I try to run my ASP Web pages on an NT4 server has anyone got any Ideas ?

ADODB.Connection error '800a0e7a'

Provider cannot be found. It may not be properly installed.

I have installed every service pack and rebooted even reregistering .dlls

Anyone got any Ideas ?If your problem is like mine, you are getting this error because your MDAC got hosed.
I was un-installing something seemingly harmless and before I knew it I saw a flurry of mdac dlls being deleted.
After that, SQLServer7's Enterprise Manager and other VB6 database apps started failing with this error.
I re-installed the MDAC (26) and it (thankfully) fixed the problem.
Hope this helps.|||I agree - reinstall your mdac. You can try to reregister the individual dll that is causing the problem - but that may only be a partial fix.

ADODB.Command Question

How to using Adodb.command to get Access file data?
i using this script:

dim connection as new adodb.connection
dim command as new adodb.command
dim recordset as new adodb.recordset

connection.open("connectionstring")
command.activeconnection=connection
command.commandtext="querystring"
recordset=command.execute

but,the Recordset is empty.how to using to get data on Microsoft Access database?

thank!!!!

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Testdb.mdb;"
cmd.ActiveConnection = cn
cmd.CommandText = "select * from sampletbl"
Set rs = cmd.Execute

Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
MsgBox rs.Fields(i).Value
Next
rs.MoveNext
Loop

ADODB.Command Question

How to using Adodb.command to get Access file data?
i using this script:

dim connection as new adodb.connection
dim command as new adodb.command
dim recordset as new adodb.recordset

connection.open("connectionstring")
command.activeconnection=connection
command.commandtext="querystring"
recordset=command.execute

but,the Recordset is empty.how to using to get data on Microsoft Access database?

thank!!!!

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Testdb.mdb;"
cmd.ActiveConnection = cn
cmd.CommandText = "select * from sampletbl"
Set rs = cmd.Execute

Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
MsgBox rs.Fields(i).Value
Next
rs.MoveNext
Loop

ADODB.Command error 800a0cb3

I have posted various questions on the microsoft ng trying to identify the cause of this error.

ADODB.Command error '800a0cb3'
Object or provider is not capable of performing requested operation.

I have MDAC 2.8 installed locally on my machine.

Via IIS I created a virtual directory via IIS that points to my ASP files on c:

Via the SQL Server IIS for XML configuration utility I created a virtual directory with a different names that points to the same directory as that created via IIS.

The SQL database is on a different machine and I connect via the OLEDB DSNless connection string.

I used a ADODB.Stream to transform the XML against the XSL but I couldnt get it to work. To simplify things and work towards a solution I inserted the code into my ASP from the MS KB article Q272266 (see below). I amended the ms code to change the connection code and call a stored procedure that exists on the database. The ms code gives the same error as my original code.

I tried changing the CursorLocation to server and client but the results were the same.

I put a SQL trace on the DB to determine if the stored procedure gets ran, it does not.

If I run the following in the URL it works:

If I run http://localhost/p2/?sql=SELECT+*+FROM+tblStatus+FOR+XML+AUTO&root=root&xsl=tblStatusDesc.xsl it works.
If I run the xml template it works: http://localhost/p2/xml/test.xml

The two lines above run. My IIS server uses a virtual directory called dev, so when I run the ASP I type http://localhost/DEV/secure/aframes.asp the IIS virtual directory creted by sql server is called p2 but has the same source code directory.

Here is the MS code amended as described above that does not work.

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>Select StatusDesc from tblStatus for XML Auto</sql:query></ROOT>"
'*************************************************

Dim txtResults ' String for results
dim CmdStream ' as ADODB.Stream


sConn = "Provider=SQLOLEDB;Data Source=[name of sql server];UId=sa; Pwd=xxxxx; Initial Catalog=[DB Name]"

Set adoConn = CreateObject("ADODB.Connection")
Set adoStreamQuery = CreateObject("ADODB.Stream")

adoConn.ConnectionString = sConn
adoConn.Open

Set adoCmd = CreateObject("ADODB.Command")
set adoCmd.ActiveConnection = adoConn

adoConn.CursorLocation = adUseClient

Set adoCmd.ActiveConnection = adoConn

adoStreamQuery.Open ' Open the command stream so it may be written to
adoStreamQuery.WriteText sQuery, adWriteChar ' Set the input command stream's text with the query string
adoStreamQuery.Position = 0 ' Reset the position in the stream, otherwise it will be at EOS

Set adoCmd.CommandStream = adoStreamQuery ' Set the command object's command to the input stream set above
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" ' Set the dialect for the command stream to be a SQL query.
Set outStrm = CreateObject("ADODB.Stream") ' Create the output stream
outStrm.Open
adoCmd.Properties("Output Stream") = response ' Set command's output stream to the output stream just opened
adoCmd.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.

Response.Endtry this type of connection string

driver={SQL Server};server=ServerName;uid=sa;pwd=xxxx;Database =DBName|||Thanks for your response.

I think this is progress but it seems to have broken the command object and it doesn't do the execute, it fails on the line:

adoCmd.Properties("Output Stream") = response

and gives an error of:

Item cannot be found in the collection corresponding to the requested name or ordinal.|||the maybe try this provider woth your first connectionstring

Provider=SQLOLEDB.1;|||No, sorry, this gives the error;

Object or provider is not capable of performing requested operation.

On the line with the execute statement.

What I am thinking of doing is to install sql server locally and have the db on my c: and try again, perhaps it is something to do with win 2k permissions and accessing the sql server across the network. Or perhaps its cause we use host headers on the web server?

Code now:

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>Select StatusDesc from tblcStatusDesc for XML Auto</sql:query></ROOT>"

'sql = "<dataroot xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
'sql = sql & "<sql:header><sql:param name='number'>" & memberNo & "</sql:param></sql:header>" & sqlQuery & "</dataroot>"

'dim sQuery
'sQuery = sql
dim sXSLfile
sXSLfile = strXSL

'sConn = "Provider=SQLOLEDB;Data Source=LON09WEB;UId=sa; Pwd=develop; Initial Catalog=NMRA_INETSOURCE"

Set adoConn = CreateObject("ADODB.Connection")
Set adoStreamQuery = CreateObject("ADODB.Stream")
set objError = CreateObject("ADODB.Error")
Set adoCmd = CreateObject("ADODB.Command")

sConn = "Provider=SQLOLEDB.1;Data Source=LON09WEB;UId=sa; Pwd=develop; Initial Catalog=NMRA_INETSOURCE"
'sConn="Provider=SQLOLEDB.1;driver={SQL Server};server=LON09WEB;uid=sa;pwd=develop;Databas e=NMRA_INETSOURCE"

Set adoConn = CreateObject("ADODB.Connection")
Set adoStreamQuery = CreateObject("ADODB.Stream")

adoConn.ConnectionString = sConn
adoConn.Open

Set adoCmd = CreateObject("ADODB.Command")
set adoCmd.ActiveConnection = adoConn

adoConn.CursorLocation = adUseClient

Set adoCmd.ActiveConnection = adoConn

adoStreamQuery.Open ' Open the command stream so it may be written to
adoStreamQuery.WriteText sQuery, adWriteChar ' Set the input command stream's text with the query string
adoStreamQuery.Position = 0 ' Reset the position in the stream, otherwise it will be at EOS

Set adoCmd.CommandStream = adoStreamQuery ' Set the command object's command to the input stream set above
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" ' Set the dialect for the command stream to be a SQL query.
Set outStrm = CreateObject("ADODB.Stream") ' Create the output stream
outStrm.Open
adoCmd.Properties("Output Stream") = response ' Set command's output stream to the output stream just opened
adoCmd.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.

Set adoConn = NOTHING
Set adoStreamQuery = NOTHING
set objError = NOTHING
Set outStrm = NOTHING|||Why are you using the Stream property ?|||I'm not really too sure. I've been copying code samples from different places and ended up with the stream property in the code. My aim is to transform the xml against xsl. I've managed to get the xml bit working all I need now is to add the xsl file name.

Dim oCmd, sSQL

Set adoConn = CreateObject("ADODB.Connection")

sConn = "Provider=[Server];Data Source=xxx;UId=xxx; Pwd=xxx; Initial Catalog=xxx"

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = sConn

oCmd.CommandText = sQuery
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("base") =
oCmd.Properties("xsl") = "tblcStatusDesc.xsl"
oCmd.Properties("Output Stream") = Response
oCmd.Execute , , 1024|||I could give you VB code to use your db

> open/close connections
> open/commit/rollback transactions
> select data
> execute command queries

I going home soon

I'll be back only on monday (loooong WE comming up !)
if this interestes you, mail me monday|||Thanks for your help, have a great weekend.

ADODB Vesion 7.0.3300.0 in vb.net

Hi,

I have developed an application in vb.net 2005 Standard Edition and is running fine in my local machine. The executed version of the same application i tried to run in other machine and getting an error as follows :-

" Unable to install application. The application requires the assembly ADODB Version 7.0.3300.0 be installed in the global assembly cache (GAC) first "

Can you somebody help me to solve this problem.

Thanks

Saju John

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=174626&SiteID=1

HTH

|||I was able to solve this without resorting to the bootstrap loader by adding a reference to the adodb resource in my Studio project, then selecting this reference, and changing it's 'Copy Local' property to true.

ADODB Vesion 7.0.3300.0 in vb.net

Hi,

I have developed an application in vb.net 2005 Standard Edition and is running fine in my local machine. The executed version of the same application i tried to run in other machine and getting an error as follows :-

" Unable to install application. The application requires the assembly ADODB Version 7.0.3300.0 be installed in the global assembly cache (GAC) first "

Can you somebody help me to solve this problem.

Thanks

Saju John

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=174626&SiteID=1

HTH

|||I was able to solve this without resorting to the bootstrap loader by adding a reference to the adodb resource in my Studio project, then selecting this reference, and changing it's 'Copy Local' property to true.

ADODB Vesion 7.0.3300.0 in vb.net

Hi,

I have developed an application in vb.net 2005 Standard Edition and is running fine in my local machine. The executed version of the same application i tried to run in other machine and getting an error as follows :-

" Unable to install application. The application requires the assembly ADODB Version 7.0.3300.0 be installed in the global assembly cache (GAC) first "

Can you somebody help me to solve this problem.

Thanks

Saju John

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=174626&SiteID=1

HTH

|||I was able to solve this without resorting to the bootstrap loader by adding a reference to the adodb resource in my Studio project, then selecting this reference, and changing it's 'Copy Local' property to true.

ADODB to textBox

First time poster, I am using MS Access and I have used the following code to get some data. It is as follows:

Code Snippet

Private Sub FillGUI()
On Error Resume Next

Dim myRS2 As New ADODB.Recordset

myRS2.ActiveConnection = CurrentProject.Connection
myRS2.CursorType = adOpenDynamic
myRS2.LockType = adLockOptimisticd
myRS2.Open "SELECT E.SSN, E.LNAME, SUM(W.HOURS) FROM EMPLOYEE E, WORKS_ON W WHERE (E.SSN = W.ESSN)GROUP BY E.SSN, E.LNAME HAVING(SUM(HOURS)) < 40"

MsgBox (myRS2.GetString)
myRS2.MoveFirst

End Sub

and I have the following output:

http://www.angelfire.com/oh5/ohiostate120/untitled1.JPG

This is what I want. However I need this to be in a text box so I have the following code:

Code Snippet

Private Sub FillGUI()
On Error Resume Next

Dim myRS2 As New ADODB.Recordset

myRS2.ActiveConnection = CurrentProject.Connection
myRS2.CursorType = adOpenDynamic
myRS2.LockType = adLockOptimisticd
myRS2.Open "SELECT E.SSN, E.LNAME, SUM(W.HOURS) FROM EMPLOYEE E, WORKS_ON W WHERE (E.SSN = W.ESSN)GROUP BY E.SSN, E.LNAME HAVING(SUM(HOURS)) < 40"

Me.txtEmployee.SetFocus
Me.txtEmployee.Text = myRS2.GetString
myRS2.MoveFirst

End Sub

And i get this:

http://www.angelfire.com/oh5/ohiostate120/untitled2.JPG

How can I get the text box format to look like the msgbox format? Thanks.........

This is not an SSIS question.

You might try asking in the "Where is the forum for...?" forum to see if someone there can direct you to the correct place to ask your Access question.

Forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=881&SiteID=1

Thanks,
Phil Brammer|||Yeah I thought I put it in the wrong place. Thanks

ADODB Streams

Does anyone have an example of how to use an ADODB Stream object in a Stored
Procedure to retrieve an image column.What are you going to do with the stream inside the stored procedure?
Did you mean that you would call a stored procedure from ASP or VB and use
the ADODB.Stream object against an IMAGE column that is returned as part of
the resultset of the stored procedure? Could you provide more details?
"Brian" <bfordSPAMOFF@.markem.com> wrote in message
news:eweWRhemFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Does anyone have an example of how to use an ADODB Stream object in a
> Stored
> Procedure to retrieve an image column.
>|||We are trying to extract PDF data held in an image column inserted by a 3rd
party vendor. The vendor has advised that we "Have the stored procedure use
an ADODB Stream object to get the data."
Apparantly there are some extreaneous null characters in the data due to the
method they use to insert it and they advice that the ADODB Stream object
will remove them.
I have not tried this before and so wondering if it was even possible?
Brian
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#KEDYmemFHA.3916@.TK2MSFTNGP10.phx.gbl...
> What are you going to do with the stream inside the stored procedure?
> Did you mean that you would call a stored procedure from ASP or VB and use
> the ADODB.Stream object against an IMAGE column that is returned as part
of
> the resultset of the stored procedure? Could you provide more details?
>
>
> "Brian" <bfordSPAMOFF@.markem.com> wrote in message
> news:eweWRhemFHA.2580@.TK2MSFTNGP09.phx.gbl...
>|||Brian wrote:

> Does anyone have an example of how to use an ADODB Stream object in a
> Stored Procedure to retrieve an image column.
See:
How To Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/?id=258038
Ben|||In that case, you would be using ADO.NET to retreive the image from the
stored procedure.
"Brian" <bfordSPAMOFF@.markem.com> wrote in message
news:%23xuPNremFHA.3648@.TK2MSFTNGP10.phx.gbl...
> We are trying to extract PDF data held in an image column inserted by a
> 3rd
> party vendor. The vendor has advised that we "Have the stored procedure
> use
> an ADODB Stream object to get the data."
> Apparantly there are some extreaneous null characters in the data due to
> the
> method they use to insert it and they advice that the ADODB Stream object
> will remove them.
> I have not tried this before and so wondering if it was even possible?
> Brian
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message
> news:#KEDYmemFHA.3916@.TK2MSFTNGP10.phx.gbl...
> of
>|||thanks everyone.
"Ben Amada" <ben@.REpoMOweVErpick.com> wrote in message
news:uwHUO3emFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Brian wrote:
>
> See:
> How To Access and Modify SQL Server BLOB Data by Using the ADO Stream
> Object
> http://support.microsoft.com/?id=258038
> Ben
>

adodb recordset problem

I am trying to access a table that I know exists and has data. But, when I create a recordset and check for RecordCount, I get a result -1 (no records). When I access the same table (using the same program), it reports (and I can view in a dbgrid) 752580 records exist.

Here's some of the code:

The table is originally copied from another database; I use the following code to be sure the previous connection is closed before proceeding.

If Not adoRS Is Nothing Then
If adoRS.State = adStateOpen Then adoRS.Close
Set adoRS = Nothing
End If
If Not DbConn Is Nothing Then
If DbConn.State = adStateOpen Then DbConn.Close
Set DbConn = Nothing
End If

Then a new connection (it works) is opened to access the database with the copied table:

strDbConn = "Provider=SQLNCLI;Integrated Security=SSPI;" & _
"Persist Security Info=False;Database=" & strDbName & ";" & _
"AttachDBFileName=" & DbPath & ";Data Source=.\sqlexpress;" & _
"User Instance=True"

Next I tried to create the recordset:

Set adoNewRS = New ADODB.Recordset 'Set OHLC recordset
Set adoNewRS.ActiveConnection = DestDbConn
adoNewRS.Open TableName, DestDbConn, adOpenDynamic, adLockOptimistic

Next I try to get the RecordCount:

NumRecords = adoNewRS.RecordCount

At this point, NumRecords (and adoNewRS.RecordCount) = -1 (even tho I know there are 752580 records in the table).

In the adoNewRS.Open statement, I also tried using the following sql statement:

sSQL = "SELECT * FROM TableName ORDER BY [DateTime];"

It also returns a recordcount = -1.

Anybody have clue?

While debugging, I noticed that adoNewRS.Open was taking about as long as the SQL query in ssms. So, I added adoNewRS.MoveLast & msgbox adoNewRS!XHigh and it popped up with the correct value. Apparently, the recordset was created correctly but the RecordCount value was wrong (as noted in previous post).

Using this new info, I discovered a PRB:

http://support.microsoft.com/default.aspx?scid=kb;en-us;194973

Apparently, in some cases, CursorLocation = adUseServer returns -1 instead of the correct RecordCount. Changing the value to adUseClient resolved the issue.

ADODB fill recrodset with stored proc

I have a ADODB recordset problem. I want to use a stored proc to retrieve a
recordset. Code goes something like:
Set SQLCmd = New ADODB.Command
Set SQLCmd.ActiveConnection = SQLConn
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = SQLConn
rs.CursorType = adOpenForwardOnly
SQLCmd.CommandText = "usp_RPT_PForm_SetWhereTableBooks"
SQLCmd.CommandType = adCmdStoredProc
'*** Set up parameters
SQLCmd.Parameters.Refresh
SQLCmd.Parameters(1) = "aaa"
…
SQLCmd.Parameters(6) = 0
Set rs = SQLCmd.Execute
The proc usp_RPT_PForm_SetWhereTableBooks should returns a recordset
Pre tests:
I know the connection is active because when I call
SQLCmd.Parameters.Refresh the parameter list (of 6 items) are created.
I have copied the parameters into the sql query analyzer and records are
returned
The problem is that the recordset is not being filled
Can you tell me what I am missing please.
Thanks in advance,
Stewart RogersDo you have "Set NoCOunt On"
at the beginning of your Stored Proc '
If Not, add it and try again...
"Datasort" wrote:

> I have a ADODB recordset problem. I want to use a stored proc to retrieve
a
> recordset. Code goes something like:
>
> Set SQLCmd = New ADODB.Command
> Set SQLCmd.ActiveConnection = SQLConn
> Set rs = New ADODB.Recordset
> Set rs.ActiveConnection = SQLConn
> rs.CursorType = adOpenForwardOnly
>
> SQLCmd.CommandText = "usp_RPT_PForm_SetWhereTableBooks"
> SQLCmd.CommandType = adCmdStoredProc
> '*** Set up parameters
> SQLCmd.Parameters.Refresh
> SQLCmd.Parameters(1) = "aaa"
> …
> SQLCmd.Parameters(6) = 0
> Set rs = SQLCmd.Execute
> The proc usp_RPT_PForm_SetWhereTableBooks should returns a recordset
> Pre tests:
> I know the connection is active because when I call
> SQLCmd.Parameters.Refresh the parameter list (of 6 items) are created.
> I have copied the parameters into the sql query analyzer and records are
> returned
> The problem is that the recordset is not being filled
> Can you tell me what I am missing please.
> Thanks in advance,
> Stewart Rogers
>|||try this:
Dim rs As Adodb.Recordset
Set rs = new ADODB.Recordset
SQLConn.usp_Rpt_PFrom_SetWhereTableBooks "aaa", rs
I bet that works.
Greg Jackson
Portland, OR|||That did it ... Thanks!!! Can you tell me why it works with nocount on?
"CBretana" wrote:
> Do you have "Set NoCOunt On"
> at the beginning of your Stored Proc '
> If Not, add it and try again...
>
> "Datasort" wrote:
>|||Datasort...
Yes, what's going on is that SQL Server sends the Record count information
ahead of the actual recordset... and ADO is not smart enough to tell the
difference, so it's trying to "Read" the record count info as your recordset
,
and failing... It couldn' be coded to figure this out, because ADO 2.x added
functionality to handle multiple recordsets in one call to the server.
There's a method (on the RecordSet Object) in ADO called .NextRecordset, tha
t
moves to the next one in the sequence, until it retruns null when you call i
t
on the last one.
"Datasort" wrote:
> That did it ... Thanks!!! Can you tell me why it works with nocount on?
> "CBretana" wrote:
>|||And, obviously, adding Set NoCount On, removes this information from the TDS
(Tabular Data Stream), and then all that's sent backto ADO Is the actual
recordset...
"Datasort" wrote:
> That did it ... Thanks!!! Can you tell me why it works with nocount on?
> "CBretana" wrote:
>|||Odd, horribly odd!
That sounds like a bug since the record count is not returned (or was not
returned) as a recordset, it is returned as a message AFAIK. Further, the
record count is returned after the requested recordset, not before since it
doesn't know the count until it has returned the rerdorset.
So, I would prefer to see this regarded as a BIG BAD BUG!
Comments?
- Tim
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:C6A14044-735D-4DD7-802D-1BB719A4A40F@.microsoft.com...
> Datasort...
> Yes, what's going on is that SQL Server sends the Record count information
> ahead of the actual recordset... and ADO is not smart enough to tell the
> difference, so it's trying to "Read" the record count info as your
> recordset,
> and failing... It couldn' be coded to figure this out, because ADO 2.x
> added
> functionality to handle multiple recordsets in one call to the server.
> There's a method (on the RecordSet Object) in ADO called .NextRecordset,
> that
> moves to the next one in the sequence, until it retruns null when you call
> it
> on the last one.
> "Datasort" wrote:
>|||this is common and well documented behavior in ado
GAJ|||So its a "feature" then?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23nc7lqYKFHA.3420@.tk2msftngp13.phx.gbl...
> this is common and well documented behavior in ado
>
> GAJ
>

ADODB errors since SQL 2000 upgrade

I have a VB 6.0 project that uses ADODB. Friday, it worked fine; Monday it
started generating the following two errors, one right after the other in
the order in which I've listed them:
-2147217885: Cursor operation conflict
2147217887: Multiple-step OLE DB operation generated errors. Check each OLE
DB status value, if available. No work was done.
The only difference is that it is now running against SQL 2000 database in
SQL 7.0 compatibility mode (Friday it was running against a SQL 7.0
database)
Any hits?- what the MDAC level on the old config and on the new config ?
jobi
"Nathan W. Phelps" <NOSPAM.nphelps@.solarc.com> wrote in message
news:#9BRQR5hDHA.2516@.TK2MSFTNGP09.phx.gbl...
> I have a VB 6.0 project that uses ADODB. Friday, it worked fine; Monday
it
> started generating the following two errors, one right after the other in
> the order in which I've listed them:
> -2147217885: Cursor operation conflict
> 2147217887: Multiple-step OLE DB operation generated errors. Check each
OLE
> DB status value, if available. No work was done.
> The only difference is that it is now running against SQL 2000 database in
> SQL 7.0 compatibility mode (Friday it was running against a SQL 7.0
> database)
> Any hits?
>|||On the server, I'm assuming that installing SQL 2000 upgrades the MDAC
stack. However, the client which is experiencing this issue was not
touched.
"jobi" <jobi@.reply2.group> wrote in message
news:bldugt$njq$1@.reader08.wxs.nl...
> - what the MDAC level on the old config and on the new config ?
> jobi
> "Nathan W. Phelps" <NOSPAM.nphelps@.solarc.com> wrote in message
> news:#9BRQR5hDHA.2516@.TK2MSFTNGP09.phx.gbl...
> > I have a VB 6.0 project that uses ADODB. Friday, it worked fine; Monday
> it
> > started generating the following two errors, one right after the other
in
> > the order in which I've listed them:
> >
> > -2147217885: Cursor operation conflict
> >
> > 2147217887: Multiple-step OLE DB operation generated errors. Check each
> OLE
> > DB status value, if available. No work was done.
> >
> > The only difference is that it is now running against SQL 2000 database
in
> > SQL 7.0 compatibility mode (Friday it was running against a SQL 7.0
> > database)
> >
> > Any hits?
> >
> >
>

ADODB DATE RETRIEVE PROBLEM

HI , I HAVE A BIG PROBLEM,
I TRY TO EXPLAIN YOU.
I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
COMMAND CALLING A STORED PROCEDURE.
WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT IN
THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW WHY
OR WHAT CAN I DO.
CAN SOMEONE HELP ME?
TAHNK YOU
Gianni
Look at regional settings on your workstation.
Also , have you tried to use FORMAT function on client side to see the date
output in format that you want.
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT
IN
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW
WHY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>
|||Hi Uri,
the regional settings are ok, infact thi problem appeared when i restored
windows 2000 file system on my server, this application work well for 1 Year.
Regarding the format function, i can't format the value because the
retrivved date is very strange and the function doesn't work.
I have 3 Asp Apllication now that when retrieve date value have this problem.
"Uri Dimant" wrote:

> Gianni
> Look at regional settings on your workstation.
> Also , have you tried to use FORMAT function on client side to see the date
> output in format that you want.
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> IN
> WHY
>
>
|||Hi
It seems to me that you have a different/version file system applied to the
server. What else?
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> Hi Uri,
> the regional settings are ok, infact thi problem appeared when i restored
> windows 2000 file system on my server, this application work well for 1
Year.
> Regarding the format function, i can't format the value because the
> retrivved date is very strange and the function doesn't work.
> I have 3 Asp Apllication now that when retrieve date value have this
problem.[vbcol=seagreen]
> "Uri Dimant" wrote:
date[vbcol=seagreen]
ADODB[vbcol=seagreen]
INFACT[vbcol=seagreen]
KNOW[vbcol=seagreen]
|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem.
"GIANNI" wrote:

> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT IN
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW WHY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>
|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem.
"Uri Dimant" wrote:

> Hi
> It seems to me that you have a different/version file system applied to the
> server. What else?
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> Year.
> problem.
> date
> ADODB
> INFACT
> KNOW
>
>
|||Gianni,
it depends on what you are doing with the recordset/dataset, but you might
want to look at using the CONVERT function on this field/output paramenter
to convert to a varchar. The CONVERT function has an optional 3rd argument
which can be used to format the datetime. On the ASP page you can have a
label explaining the format as it will obviously be invariant on the locale
of the client machine. This way you have no locale issues on the SQL Server
box or the IIS server box.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
I know that I Have to use the convert function,
the problem for me is that until 2 weeks ago the problem didn't exist on my
Server,
now i have to change all my ASp Applications and apply the convert function.
I hoped to solve the problem server side, and not to change all my Stored
Procedure.
However Thanks
"Paul Ibison" wrote:

> Gianni,
> it depends on what you are doing with the recordset/dataset, but you might
> want to look at using the CONVERT function on this field/output paramenter
> to convert to a varchar. The CONVERT function has an optional 3rd argument
> which can be used to format the datetime. On the ASP page you can have a
> label explaining the format as it will obviously be invariant on the locale
> of the client machine. This way you have no locale issues on the SQL Server
> box or the IIS server box.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

ADODB DATE RETRIEVE PROBLEM

HI , I HAVE A BIG PROBLEM,
I TRY TO EXPLAIN YOU.
I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
COMMAND CALLING A STORED PROCEDURE.
WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT IN
THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW WHY
OR WHAT CAN I DO.
CAN SOMEONE HELP ME?
TAHNK YOUGianni
Look at regional settings on your workstation.
Also , have you tried to use FORMAT function on client side to see the date
output in format that you want.
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT
IN
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW
WHY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>|||Hi Uri,
the regional settings are ok, infact thi problem appeared when i restored
windows 2000 file system on my server, this application work well for 1 Year.
Regarding the format function, i can't format the value because the
retrivved date is very strange and the function doesn't work.
I have 3 Asp Apllication now that when retrieve date value have this problem.
"Uri Dimant" wrote:
> Gianni
> Look at regional settings on your workstation.
> Also , have you tried to use FORMAT function on client side to see the date
> output in format that you want.
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> > HI , I HAVE A BIG PROBLEM,
> > I TRY TO EXPLAIN YOU.
> > I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> > I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> > COMMAND CALLING A STORED PROCEDURE.
> > WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT
> IN
> > THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> > AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW
> WHY
> > OR WHAT CAN I DO.
> > CAN SOMEONE HELP ME?
> > TAHNK YOU
> >
>
>|||Hi
It seems to me that you have a different/version file system applied to the
server. What else?
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> Hi Uri,
> the regional settings are ok, infact thi problem appeared when i restored
> windows 2000 file system on my server, this application work well for 1
Year.
> Regarding the format function, i can't format the value because the
> retrivved date is very strange and the function doesn't work.
> I have 3 Asp Apllication now that when retrieve date value have this
problem.
> "Uri Dimant" wrote:
> > Gianni
> > Look at regional settings on your workstation.
> > Also , have you tried to use FORMAT function on client side to see the
date
> > output in format that you want.
> >
> >
> >
> > "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> > news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> > > HI , I HAVE A BIG PROBLEM,
> > > I TRY TO EXPLAIN YOU.
> > > I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> > > I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND
ADODB
> > > COMMAND CALLING A STORED PROCEDURE.
> > > WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE,
INFACT
> > IN
> > > THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> > > AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T
KNOW
> > WHY
> > > OR WHAT CAN I DO.
> > > CAN SOMEONE HELP ME?
> > > TAHNK YOU
> > >
> >
> >
> >|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem.
"GIANNI" wrote:
> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT IN
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW WHY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem.
"Uri Dimant" wrote:
> Hi
> It seems to me that you have a different/version file system applied to the
> server. What else?
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> > Hi Uri,
> > the regional settings are ok, infact thi problem appeared when i restored
> > windows 2000 file system on my server, this application work well for 1
> Year.
> > Regarding the format function, i can't format the value because the
> > retrivved date is very strange and the function doesn't work.
> > I have 3 Asp Apllication now that when retrieve date value have this
> problem.
> >
> > "Uri Dimant" wrote:
> >
> > > Gianni
> > > Look at regional settings on your workstation.
> > > Also , have you tried to use FORMAT function on client side to see the
> date
> > > output in format that you want.
> > >
> > >
> > >
> > > "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> > > news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> > > > HI , I HAVE A BIG PROBLEM,
> > > > I TRY TO EXPLAIN YOU.
> > > > I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> > > > I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND
> ADODB
> > > > COMMAND CALLING A STORED PROCEDURE.
> > > > WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE,
> INFACT
> > > IN
> > > > THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> > > > AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T
> KNOW
> > > WHY
> > > > OR WHAT CAN I DO.
> > > > CAN SOMEONE HELP ME?
> > > > TAHNK YOU
> > > >
> > >
> > >
> > >
>
>|||Gianni,
it depends on what you are doing with the recordset/dataset, but you might
want to look at using the CONVERT function on this field/output paramenter
to convert to a varchar. The CONVERT function has an optional 3rd argument
which can be used to format the datetime. On the ASP page you can have a
label explaining the format as it will obviously be invariant on the locale
of the client machine. This way you have no locale issues on the SQL Server
box or the IIS server box.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thanks Paul,
I know that I Have to use the convert function,
the problem for me is that until 2 weeks ago the problem didn't exist on my
Server,
now i have to change all my ASp Applications and apply the convert function.
I hoped to solve the problem server side, and not to change all my Stored
Procedure.
However Thanks
"Paul Ibison" wrote:
> Gianni,
> it depends on what you are doing with the recordset/dataset, but you might
> want to look at using the CONVERT function on this field/output paramenter
> to convert to a varchar. The CONVERT function has an optional 3rd argument
> which can be used to format the datetime. On the ASP page you can have a
> label explaining the format as it will obviously be invariant on the locale
> of the client machine. This way you have no locale issues on the SQL Server
> box or the IIS server box.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

ADODB DATE RETRIEVE PROBLEM

HI , I HAVE A BIG PROBLEM,
I TRY TO EXPLAIN YOU.
I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
COMMAND CALLING A STORED PROCEDURE.
WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT IN
THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW WHY
OR WHAT CAN I DO.
CAN SOMEONE HELP ME?
TAHNK YOUGianni
Look at regional settings on your workstation.
Also , have you tried to use FORMAT function on client side to see the date
output in format that you want.
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT
IN
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW
WHY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>|||Hi Uri,
the regional settings are ok, infact thi problem appeared when i restored
windows 2000 file system on my server, this application work well for 1 Year
.
Regarding the format function, i can't format the value because the
retrivved date is very strange and the function doesn't work.
I have 3 Asp Apllication now that when retrieve date value have this problem
.
"Uri Dimant" wrote:

> Gianni
> Look at regional settings on your workstation.
> Also , have you tried to use FORMAT function on client side to see the da
te
> output in format that you want.
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:2A7D3B28-5399-482A-9710-A6DFBF1A0E9B@.microsoft.com...
> IN
> WHY
>
>|||Hi
It seems to me that you have a different/version file system applied to the
server. What else?
"GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> Hi Uri,
> the regional settings are ok, infact thi problem appeared when i restored
> Windows 2000 file system on my server, this application work well for 1
Year.
> Regarding the format function, i can't format the value because the
> retrivved date is very strange and the function doesn't work.
> I have 3 Asp Apllication now that when retrieve date value have this
problem.[vbcol=seagreen]
> "Uri Dimant" wrote:
>
date[vbcol=seagreen]
ADODB[vbcol=seagreen]
INFACT[vbcol=seagreen]
KNOW[vbcol=seagreen]|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem
.
"GIANNI" wrote:

> HI , I HAVE A BIG PROBLEM,
> I TRY TO EXPLAIN YOU.
> I HAVE MANY TABLE WHOSE CONTAIN DATETIME FIELDS ,
> I DISPLAY THIS VALUE ON A ASP PAGE BY USING AN ADODB RECORDSET AND ADODB
> COMMAND CALLING A STORED PROCEDURE.
> WHEN THIS RECORDSET RETRIEVE DATA THE DATE VALUE IS VERY STRANGE, INFACT I
N
> THE TABLE BY USING "SQL QUERY ANALIZER I SEE 2004-10-10 14:34:45.613
> AND IT'S OK, BUT ADODB RECORDSET RETRIEVE /10/aa 14.34.45, I DON'T KNOW W
HY
> OR WHAT CAN I DO.
> CAN SOMEONE HELP ME?
> TAHNK YOU
>|||Only this,
but I can't hunderstand if something is happen to the Adodb component,
or now is changed something in IIS , infact if i use ADODB in Visual Basic
and connect it with the same table and fields i haven't this kind of problem
.
"Uri Dimant" wrote:

> Hi
> It seems to me that you have a different/version file system applied to th
e
> server. What else?
>
> "GIANNI" <GIANNI@.discussions.microsoft.com> wrote in message
> news:F6529C7F-4103-4969-B13A-52CA8763EB8D@.microsoft.com...
> Year.
> problem.
> date
> ADODB
> INFACT
> KNOW
>
>|||Gianni,
it depends on what you are doing with the recordset/dataset, but you might
want to look at using the CONVERT function on this field/output paramenter
to convert to a varchar. The CONVERT function has an optional 3rd argument
which can be used to format the datetime. On the ASP page you can have a
label explaining the format as it will obviously be invariant on the locale
of the client machine. This way you have no locale issues on the SQL Server
box or the IIS server box.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thanks Paul,
I know that I Have to use the convert function,
the problem for me is that until 2 weeks ago the problem didn't exist on my
Server,
now i have to change all my ASp Applications and apply the convert function.
I hoped to solve the problem server side, and not to change all my Stored
Procedure.
However Thanks
"Paul Ibison" wrote:

> Gianni,
> it depends on what you are doing with the recordset/dataset, but you might
> want to look at using the CONVERT function on this field/output paramenter
> to convert to a varchar. The CONVERT function has an optional 3rd argument
> which can be used to format the datetime. On the ASP page you can have a
> label explaining the format as it will obviously be invariant on the local
e
> of the client machine. This way you have no locale issues on the SQL Serve
r
> box or the IIS server box.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

adodb connections to a linked server

Hello,
I'd like to create an ado connection to my linked server called Prob_Schd on
a machine called pccn-nt.
When I use the code below I recieve an error of the type ' Invalid
connection string attribute'. Would someone show me how to create an ado
connection to a linked server?
Thanks,
Chieko
dim oConn, strConn
Set oConn = Server.CreateObject("ADODB.Connection")
strConn = "provider=SQLOLEDB; Datasource=Prob_Schd; Initial Catalog=null
trusted_Connection=yes "
oConn.Open strConn
set GetDataConnection = oConnADO doesn't know anything about SQL Server's linked servers. You could
connect locally, and then execute queries like adoConnObject.Execute("SELECT
* FROM Prob_Schd.pubs.dbo.authors") or, you could make an OLEDB connection
directly to the linked server.
The specific error you are getting is because the "Datasource" attribute
you've used should have a space, "Data Source"... but I'm pretty sure ADO
will still be unable to find that server unless it is in the hosts file on
the machine (in other words, it doesn't care if that's a linked server in
SQL Server).
"Chieko Kuroda" <ckuroda@.med.unc.edu> wrote in message
news:O2OVMHuzDHA.3520@.tk2msftngp13.phx.gbl...

quote:

---

> Hello,
> I'd like to create an ado connection to my linked server called Prob_Schd

---

on

quote:

---

> a machine called pccn-nt.
> When I use the code below I recieve an error of the type ' Invalid
> connection string attribute'. Would someone show me how to create an ado
> connection to a linked server?
> Thanks,
> Chieko
>
> dim oConn, strConn
> Set oConn = Server.CreateObject("ADODB.Connection")
> strConn = "provider=SQLOLEDB; Datasource=Prob_Schd; Initial

---

Catalog=null

quote:

---

> trusted_Connection=yes "
> oConn.Open strConn
> set GetDataConnection = oConn
>

---

ADODB connection with SQL2000SP3

my code is using ADODB connection to connect to SQL (Virtual) Server.

the way it being done is (c++):

ADODB::_ConnectionPtr m_dbConn;
ADODB::_RecordsetPtr m_dbRst;
m_dbConn.CreateInstance(__uuidof(ADODB::Connection ));
m_dbRst.CreateInstance( __uuidof( ADODB::Recordset ));
m_dbConn->ConnectionString=( L"DSN=mydsn" );
m_dbConn->Open("","sa","",-1);

lately after installing SP3 over SQL2000, it was impossible to connect
- the error message showed: login failed for user 'null)'. reason: not
associated with a trusted sql server connection.

so i changed the connection string to:
m_dbConn-> ConnectionString =(L"DSN=mydsn; UID=sa; PWD=;");
m_dbConn->Open("","","",-1);

and now it works !!

what is the reason for that ?
what in sql SP3 interrupt for this kind of connection ?
what is the difference ?liorh (liorhal@.gmail.com) writes:
> my code is using ADODB connection to connect to SQL (Virtual) Server.
> the way it being done is (c++):
> ADODB::_ConnectionPtr m_dbConn;
> ADODB::_RecordsetPtr m_dbRst;
> m_dbConn.CreateInstance(__uuidof(ADODB::Connection ));
> m_dbRst.CreateInstance( __uuidof( ADODB::Recordset ));
> m_dbConn->ConnectionString=( L"DSN=mydsn" );
> m_dbConn->Open("","sa","",-1);
> lately after installing SP3 over SQL2000, it was impossible to connect
> - the error message showed: login failed for user 'null)'. reason: not
> associated with a trusted sql server connection.
> so i changed the connection string to:
> m_dbConn-> ConnectionString =(L"DSN=mydsn; UID=sa; PWD=;");
> m_dbConn->Open("","","",-1);
> and now it works !!
> what is the reason for that ?
> what in sql SP3 interrupt for this kind of connection ?
> what is the difference ?

Since I don't know what is in that DSN, I would have to guess a bit.
Then again, I never liked DSNs. Anyway, my guess is that since SP3
was very focused on security, it may be that since you did not provide
a password, there is now a default for Windows authentication.

Anyway, running a system with a blank password for 'sa' is extremely
poor practice. So is it for that matter, to use sa to connect to SQL
Server from an application.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp