MS SQL 2000 vs MS SQL 7

MS SQL 2000 vs MS SQL 7

Post by Lon » Sun, 13 Jan 2002 05:33:28



I moved my application to a new server.  The application was on
WindowsNT, IIS4, FrontPage Extensions, and MS SQL 7.  The new server
has Windows2000, IIS5, FrontPage Extensions 2002, MDAC 2.6 AND MS SQL
2000.

Everything works fine except when I open an ASP page that has a
Database Results region and try to update that area.  I get an ODBC
error relating to "Syntax error converting datetime from character
string" or "Invalid length parameter passed to the substring function.
".  I can run the procedure using the same test execution string
that FrontPage uses in Query Analyzer and WinSQL with no problem.  The
ASP pages work.  It is just that, if I try to edit the database
results region the error comes up.

I pointed the ODBC System DSN back to the old MS SQL 7 database and it
works fine?

I have installed the SP2 for Win2000, SP2 for SQL2000, FP 2002 Ext and
MDAC 2.6.

Is there a way to duplicate what FrontPage is doing when the Database
Results Wizard attempts to obtain the procedure schema?

I am considering developing the FrontPage reports on the old server
and then simply publish them to the new.  This will double my work,
but it should work.

I just can't imagine why the ASP page will work and the Database
Results Wizard in FrontPage will not.

I exported the SQL 2000 database back to the MS SQL 7 server using the DTS
Export Wizard and the FrontPage Database Results works fine.  I backed up
the MS SQL 7 test database and restored it to the SQL 2000 server and now I
get the Database Connection Error:

Server error: Unable to retrieve schema information from the query:

exec RED_Info.dbo.DailyPotOut '1'

against a database using the connection string

DSN=RED_Info;DRIVER={SQL Server};UID=sa;PWD=cawv;.

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter
passed to the substring function.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

I am thinking maybe it has to do with the different collation between SQL
2000 and SQL 7.

Thank you,
Lon

 
 
 

MS SQL 2000 vs MS SQL 7

Post by Lon » Sun, 13 Jan 2002 06:19:13


Is there an OLE DB driver update that I can apply?

Thanks,
Lon

 
 
 

MS SQL 2000 vs MS SQL 7

Post by Simon Liao[M » Wed, 16 Jan 2002 12:10:08


Hi Lon,

Did you check out this website: http://www.microsoft.com/data

--- Simon Liao

This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

MS SQL 2000 vs MS SQL 7

Post by Lo » Wed, 16 Jan 2002 23:37:01


Yes, I have installed the latest MDAC.

ODBCBCP.DLL, SQLSRV32.DLL, & SQLSRV32.RLL have ver 2000.81.7713.0 and
date of 7/13/2001.

The closest FIX: I have found is
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q280294

I narrowed the problem down to the moving of the database from MS SQL
7 to MS SQL 2000.  I went back to the old server and pointed the ODBC
DSN to the new server and the errors come up.  To work around the
problem, I can develop the FrontPage database result tables on the old
MS SQL 7 database and then point them to the MS SQL 2000 database when
I am through.  I would like to fix this if you have any ideas of what
might be the difference between MS SQL 2000 and MS SQL 7.

The FrontPage 2002 database results wizard has errors unless I use MS
SQL 7, but the resulting ASP page will work on MS SQL 2000?

Thank you very much for any ideas you may have.
Lon


> Hi Lon,

> Did you check out this website: http://www.microsoft.com/data

> --- Simon Liao

> This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

1. Connecting to Sql Server from asp after upgrade from ms sql 7 to ms sql 2000

I am using a distant webhoster to run a classic asp program (not .net) which
used to connect to an sql server database again which they hosted.

A dsnless connection string similair to the following worked fine:

strCon = "Provider=sqloledb;" & _ "Data Source=233.77.166.2;" & _ "Network
Library=dbnmpntw;" & _ "Initial Catalog=databaseexample;" & _ "User
ID=webmasterofsite;" & _ "Password=4444;"

The strings details have been changed for security purposes but are similair
in type to the real string.

Now on the webhoster upgrading to sql server 2000 this connection string
stopped working.

However I can still connect using the same ip address and login details
through sql 2000 enterprise manager and through the access adp front end.

The error message I now get through asp is:

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.

Here is the full code for the connection routine:

strCon = "Provider=sqloledb;"
& _ "Data Source=233.77.166.2;"
& _ "Network Library=dbnmpntw;"
& _ "Initial Catalog=databaseexample;"
& _ "User ID=webmasterofsite;"
& _ "Password=4444;"
set Con = server.CreateObject("adodb.Connection")
con.open strCon

 set rs = con.execute("update counter set HitCount = ((HitCount) + 1) where
ID = 1")

 con.close
 set rs = nothing
 set con = nothing
if Request.Cookies("publicregistered")="True" then
 Response.Redirect("startSql.asp")
end if

Thanks

Jonathan

2. why does post not always post?

3. first week of the year - MSSQL 6.5

4. MS SQL 2000 to MS SQL 6.5

5. VB/Oracle Developer/Systems Engineer II (Job #4962)

6. Documents about the changes from MS SQL 6.5 to MS SQL 2000

7. Sybase DBAs needed nationwide-U.S.-(Recruiter)

8. strange ODBC problem with MS SQL 2000 Enterprise Edition, MS SQL 7.0

9. HELP: Oracle 8i vs 9i vs MS SQL 2000

10. Sybase ASA vs MS Access vs MS SQL Server

11. replication between ms access 2000 and ms sql sever 2000

12. Sybase ASA vs MS Access vs MS SQL Server