MS SQL Server, Linked Server to Pervasive

Hello all,

I am new to the forums and I have a question regarding linked servers. I am searching for the method of adding a Linked Server from MS SQL Server to a Pervasive database (specifically, the DEMODATA database that one gets after installing the Pervasive trial).

Specifically, I am looking for

1. What (if any) special drivers do I need?

2. What DSNs (if any) are necessary, besides DEMODATA with the Pervasive ODBC Engine Interface driver?

3. What parameters need I provide to sp_addlinkedserver?

Any input would be very much appreciated. Thanks.

RobRefer to books online for linked server and heterogeneous data sources topics.

MS SQL Server 2000 JDBC Driver setDate() bug report

Hi,

Recently, I found a bug in Microsoft's official SQL Server2000 JDBC driver.
It seemed to lie in the Statement's setDate() method. Maybe they simply assumed
all date are represented in 12-hours format.
The test code is below:


public void testMisc() throws Exception {
Connection conn = TransactionManager.getConnection();
String sql = "select count(salescheck0_.ID) as x0_0_ " +
"from T_CHK_OUT_BILL_TEST salescheck0_ " +
"where (salescheck0_.EX_TIME>?)" +
"and(salescheck0_.EX_TIME<?)";
PreparedStatement stmt = conn.prepareCall(sql);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date timefrom = sdf.parse("2004-04-02 00:00:00.000");
Date timeto = sdf.parse("2004-04-02 23:00:00.000");

stmt.setDate(1, new java.sql.Date(timefrom.getTime()));
stmt.setDate(2, new java.sql.Date(timeto.getTime()));
// stmt.setString(1, "2004-04-02 00:00:00.000" );
// stmt.setString(2, "2004-04-02 23:59:59.000" );
ResultSet rs = stmt.executeQuery();
if(rs.next()) {
int count = rs.getInt(1);
System.out.println("count = " + count);
}
}

The test data is following:
ID, SO_ID,WH_ID,CLIENT_ID,CODE,OPERATOR,CHK_OUT_TIME, STATUS,CREATE_TIME,DELIVER_TYPE
86179,67521,1,661,SLCCK200404020016,xuwei,2004-4-2 12:15,20,2004-4-2 9:40,10
86229,67566,1,118,SLCCK200404020066,xuwei,2004-4-2 12:23,20,2004-4-2 9:41,10
86231,67568,1,130,SLCCK200404020068,xuwei,2004-4-2 12:23,20,2004-4-2 9:41,10
86232,67569,1,134,SLCCK200404020069,xuyili,2004-4-2 11:08,20,2004-4-2 9:41,10
86233,67571,1,151,SLCCK200404020070,xuwei,2004-4-2 12:23,20,2004-4-2 9:41,10
86234,67572,1,55,SLCCK200404020071,xuwei,2004-4-2 12:23,20,2004-4-2 9:41,10
86235,67573,1,71,SLCCK200404020072,xuwei,2004-4-2 12:23,20,2004-4-2 9:41,10
86252,67590,1,298,SLCCK200404020089,xuwei,2004-4-2 12:33,20,2004-4-2 9:41,10
86268,67576,1,25,SLCCK200404020105,xuwei,2004-4-2 12:23,20,2004-4-2 10:18,10
86269,67588,1,271,SLCCK200404020106,xuwei,2004-4-2 12:33,20,2004-4-2 10:20,10
86270,67506,1,42,SLCCK200404020107,xuwei,2004-4-2 12:15,20,2004-4-2 10:21,10
86271,67531,1,751,SLCCK200404020108,xuyili,2004-4-2 11:28,20,2004-4-2 10:22,10
86272,67570,1,149,SLCCK200404020109,xuwei,2004-4-2 12:23,20,2004-4-2 10:29,10
86273,67600,1,6,SLCCK200404020110,xuwei,2004-4-2 12:15,20,2004-4-2 10:40,10
86373,67665,3,686,SLCCK200404020210,root,2004-4-2 17:18,20,2004-4-2 16:32,10
86374,67657,3,883,SLCCK200404020211,root,2004-4-2 17:18,20,2004-4-2 16:32,10
86375,67666,3,686,SLCCK200404020212,root,2004-4-2 17:18,20,2004-4-2 16:32,10
86376,54799,1,395,SLCCK200404020213,zhouliyi,2004-4-2 19:52,20,2004-4-2 19:51,10
86377,55383,1,341,SLCCK200404020214,zhoulingyi,200 4-4-2 19:52,20,2004-4-2 19:51,10

Given the code and data, the expected output ought to be "count = 19"
But it's "count = 19" instead.
However, if you set date by using alternative method like the commented statements,
you can get the correct result.


Does anybody know if there exists any patch?
Could we expect Mircosoft to fix this bug? If so, where could I submit this bug report?

Regards,
JustinWhich version of the Microsoft JDBC Driver are you using?|||Originally posted by peterlemonjello
Which version of the Microsoft JDBC Driver are you using?

Don't know very clearly.
It may be rather old.
We have used it for about 2 years.

I notice there is a SP2 of MS SQL Server 2000 JDBC driver.
I'll try it immediately, and feedback ASAP.

Regards,
Justin|||Given the code and data, the expected output ought to be "count = 19"
But it's "count = 19" instead.
So what's wrong with that?sql

MS SQL + ADO + VB6, Which is the best method ?

First, sorry for my english

In vb.6 I use 2 DataCombo and 1 DataGrid in 1 Form, totally I need 3 recordset on 1 form.

All 3 recordset is from MS SQL Server in one database.

Which is the best method ?

1. To place 3 ADODC control on the form and for each

ADODC1.ConnectionString="......................"
ADODC1.RecordSource="SELECT * FROM Table1"

ADODC2.ConnectionString = the same as ADODC 1
ADODC2.RecordSource="SELECT * FROM Table2"

ADODC3.ConnectionString = the same as ADODC 1
ADODC3.RecordSource="SELECT * FROM Table3"

OR

2. I don't use ADODC control, I use a single connection and 3 Recordset

Set cn = New ADODB.Connection

With cn
.ConnectionString = ".........."
.Open
End With

Dim Rec1 As New ADODB.Recordset
With Rec1
.Open "SELECT * FROM Table1, cn, adOpenStatic, adLockReadOnly
End With

Dim Rec2 As New ADODB.Recordset
...........................
Dim Rec3 As New ADODB.Recordset
..............................The single connection object with multiple recordset objects is the better choice. Also, make sure you use the SQLOLEDBXX provider in your connection string.|||Another comment - declare your objects at the top of your code so

Dim cn as adodb.connection
dim rs1 as adodb.recordset
dim rs2 as adodb.recordset ...

Then instantiate the recordset object as late as possible in your code (stay away from dim as new) and destroy the object as soons as possible:

set rs1 = new adodb.recordset
'start recordset manipulation
'end recordset manipulation
set rs1 = nothing