Link Server error: Msg 7399

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.]
What to do ? What to do ??Well , this Error belongs to Distributed Queries Error Messages collection.

- Remote OLE DB Data Source : ODBC Data Source
- OLE DB Provider : Microsoft OLE DB Provider for ODBC
- Provider Name : MSDASQL

The problem you are getting with only receiving one row on a select statement can be fixed by going into the configuration of the Client Access DSN and go to the other tab and check always scrollable, this will get all the rows.

As far as the other issue getting the error
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.]

You get this when journaling is not turned on the table on the AS400. Have one of your AS400 people turn journaling on for all the tables you need to access.

Since you noted that ..." [IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.] ".... I just could compare this error with one more familiar to Microsoft platform, and what are the reasons for getting this Error when trying to connect to MS Access DB.

1. MS Access DB is not secured Db , and there is no login : Admin with No Password .

2. The Access database is secured and :HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Sys temDB registry key is not pointing to the correct Access workgroup file.

-If you are trying to Change Data , try without : BEGIN >> COMMIT block, if possible.

-also try this link :

http://www.iseries.ibm.com/developer/db2/documents/mts/mts5.html

Hope this is something to start solving your problem.

rgds.
srdjan|||I linked two MSSQL 2000 servers by using enterprise manager. I succeeded in connecting between server 1(local) and server2 (remote). I have another remote one--server3. I used the same method to try connecting either local and server3, or server2 and server3.

I can do on local and server2 to see data from server3--

select * from server3.pubs.dbo.authors

however if I try to do this on server3

select * from server2.pubs.dbo.authors

I got following error messages:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.

So what is the problem here?

I will appreciate greatly for your help.

Cathy|||1. Check list of Linked Servers in SYSSERVERS system table ( you should have both (Server2 and Server3 ) servers listed .
2. Verify that there is a login mapping for the current user on Server3.
3. Check the rights of user you are trying to connect from your Server to Server3 . ( at least you must SELECT rights on table you are querying) ....

--Returns the list of linked servers defined in the local server
exec sp_linkedservers

--Creates a linked server
exec sp_addlinkedserver

hope this help

rgds.
srdjan|||Thanks for reply.

I checked all . I also used

CREATE Database NorthwindnDistributed
Go

Use NorthwindnDistributed
CREATE TABLE dbo.Customers
(CustomerID char(5),
CompanyName nvarchar(40))
EXEC sp_addlinkedserver @.server = 'remoteserver',
@.srvproduct ='SQLServer OLEDB Provider',
@.provider ='SQLOLEDB',
@.datasrc = 'remote server IPaddress'

IF not Exists (select 'True' From master.dbo.syslogins where Name = 'remoteUser')
EXEC sp_addlogin 'remoteUser','remotePassword'

Use NorthwindnDistributed
exec sp_adduser 'remoteUser'
exec sp_addrolemember 'db_owner', 'remoteUser'
exec sp_addlinkedsrvlogin @.rmtsrvname = GM,
@.useself = FALSE,
@.rmtuser = 'remoteUser',
@.rmtpassword ='remotePassword'

exec sp_serveroption @.server = GM,
@.OPTNAME ='lazy schema validation',
@.OPTVALUE = 'TRUE'

to create new linked servers from both of local and remote sides. It still give me the error message like

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.

I can do "select ..." from one side of server. But when I changed to other servers, I don't have any problems to connect them and work on them. If there is anything wrong when this server is set up? Why can I not get any error messages when i do the same operation on other servers but only fail on one particular server?:confused:

Thanks for any reply.

HELP!

limiting the number of record sets returned by a stored procedure

Hi,
I know that a stored procedure will return as many results sets as
SELECT statements are in. I want to actually mark which selects should be
returned as results sets and which are just internal. Is there a way of
doing this?
Thanks,
George.George,
There is noway to do this in t-sql. Why are you doing exactly?
if you are doing this kind of operation:
select col1, col2, ..., coln
from table1
where col1 like 'microsoft%'
if @.@.rowcount > 0
...
you can use:
if exists(select * from table1 where col1 like 'microsoft%')
...
AMB
"George Tihenea" wrote:

> Hi,
> I know that a stored procedure will return as many results sets as
> SELECT statements are in. I want to actually mark which selects should be
> returned as results sets and which are just internal. Is there a way of
> doing this?
> Thanks,
> George.
>
>|||Thanks,
Here are some more details. I have a stored procedure like this:
// start of stored proc, then
....
SELECT c1, c2, c3 from ...
WHERE (condition here)
if ( @.returned_rows > 0 ) return 0; /* all ok return*/
/* let the flow continue */
select c1, c2, c3 from...
where (a different condition here)
if ( @.returned_rows > 0 ) return 0; /* all ok return*/
/* let the flow continue */
......................
return 0 /* did not find anything*/
/// end of stored procedure
All this worked ok and I can get the result set from my OLEDB middle tier
using multiple results sets. Of cause I will always get ONLY ONE result set
but OLEDB needs to use the template with multiple results sets to work...
then I had to modify the stored precedure to do an INSERT before finishing.
Here is a scheleton code:
// start of stored proc, then
....
SELECT c1, c2, c3 from ...
WHERE (condition here)
if ( @.returned_rows > 0 ) go to FINISH /* all ok return*/
/* let the flow continue */
select c1, c2, c3 from...
where (a different condition here)
if ( @.returned_rows > 0 ) go to FINISH; /* all ok return*/
/* let the flow continue */
......................
FINISH:
INSERT INTO ....
VALUES (...)
return 0
/// end of stored procedure
The problem is this INSERT. For some reasons I cannot understand, my OLEDB
consumer templates will think that there are 3 result sets instead of 2! And
when I try to read the last one it will just crash while binding the
columns, which is normal because this last result set is bogus!!?
Thanks,
George.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F7B5BE46-FCA1-485B-902C-9F8D37629C78@.microsoft.com...
> George,
> There is noway to do this in t-sql. Why are you doing exactly?
> if you are doing this kind of operation:
> select col1, col2, ..., coln
> from table1
> where col1 like 'microsoft%'
> if @.@.rowcount > 0
> ...
> you can use:
> if exists(select * from table1 where col1 like 'microsoft%')
> ...
>
> AMB
>
> "George Tihenea" wrote:
>|||If you need these "other" select statements for debugging what I do is
add a debug variable to each stored procedure:
declare @.Debug int
and then in the TSQL code I test it:
if @.Debug = 1
Select * from Scheduler
Of course you can only run these from Query Analyzer interactively but
typically that is where you draw the line for debugging SPs.
On Thu, 17 Mar 2005 08:53:01 -0500, "George Tihenea"
<tihenea@.comcast.net> wrote:

> Hi,
> I know that a stored procedure will return as many results sets as
>SELECT statements are in. I want to actually mark which selects should be
>returned as results sets and which are just internal. Is there a way of
>doing this?
> Thanks,
> George.
>

Limiting Records Returned within Groups

Sorry to be a noob here, but I've scoured the rest of the threads, and can't find the answer to my dilemma.

I'm trying to write a report that returns the top 10 depositors (based on current balance) for each of our branches. I've broken out each branch into it's own group.

When I apply the 'Top N' function, it limits the first group to 10 records, but pulls nothing for the remaining groups.

I've seen other posts that say I need to write a formula that counts the records in each group, and then in the detail section, suppress records greater than 10, but I have no idea how to create the 'count records' formula.

Again, sorry to be the fresh fish here, but I'm at my wit's end.

Thanksfirstly create a formula

formula 1:

//it puts in section 'Group header'
shared numbervar mycount:=0;


formula 2:

//now, inside section 'details'
shared numbervar mycount;
mycount:=mycount+1;


now, click right on section 'detalis' it shows popup menu
you do click on Format Section ......
click on button Suppress and there you have to write

shared numbervar mycount;
if mycount>10 then
true


I don't tested this, but I think it may help you|||Hensa22, you are my savior. Thank you so much.

Limiting Records Returned

I'm building an Access front end (A2K2 ADP project) to view records on a SQL 2000 Server, and want to ensure the fastest performance for my users. I expect the database to grow to around 25,000 records (after that, they'll be archived), and would like opi
nions/comments on the best way to access the data. I would guess that a recordset would be fastest, but how do you set up a form to be based on a recordset instead of just hooking directly to the underlying table?
TIA,
Terry Roberts
Check the ADO and ADP references in the Microsoft Office
2000/Visual Basic Programmer's Guide:
http://msdn.microsoft.com/library/de...radobasics.asp
You can also find a list of Access resources here:
http://msdn.microsoft.com/library/en...acacclinks.asp
And...you really should considering getting the book: Access
Developer's Guide to SQL Server by
Mary Chipman and Andy Baron.
-Sue
On Mon, 10 May 2004 09:11:08 -0700, "Terry Roberts"
<anonymous@.discussions.microsoft.com> wrote:

>I'm building an Access front end (A2K2 ADP project) to view records on a SQL 2000 Server, and want to ensure the fastest performance for my users. I expect the database to grow to around 25,000 records (after that, they'll be archived), and would like op
inions/comments on the best way to access the data. I would guess that a recordset would be fastest, but how do you set up a form to be based on a recordset instead of just hooking directly to the underlying table?
>TIA,
>Terry Roberts

Limiting Records Returned

I'm building an Access front end (A2K2 ADP project) to view records on a SQL
2000 Server, and want to ensure the fastest performance for my users. I exp
ect the database to grow to around 25,000 records (after that, they'll be ar
chived), and would like opi
nions/comments on the best way to access the data. I would guess that a reco
rdset would be fastest, but how do you set up a form to be based on a record
set instead of just hooking directly to the underlying table?
TIA,
Terry RobertsCheck the ADO and ADP references in the Microsoft Office
2000/Visual Basic Programmer's Guide:
adobasics.asp" target="_blank">http://msdn.microsoft.com/library/d...
adobasics.asp
You can also find a list of Access resources here:
http://msdn.microsoft.com/library/e.../acacclinks.asp
And...you really should considering getting the book: Access
Developer's Guide to SQL Server by
Mary Chipman and Andy Baron.
-Sue
On Mon, 10 May 2004 09:11:08 -0700, "Terry Roberts"
<anonymous@.discussions.microsoft.com> wrote:

>I'm building an Access front end (A2K2 ADP project) to view records on a SQL 2000 S
erver, and want to ensure the fastest performance for my users. I expect the databas
e to grow to around 25,000 records (after that, they'll be archived), and would like
op
inions/comments on the best way to access the data. I would guess that a recordset would be
fastest, but how do you set up a form to be based on a recordset instead of just hooking dir
ectly to the underlying table?
>TIA,
>Terry Roberts

Limiting Records Returned

I'm building an Access front end (A2K2 ADP project) to view records on a SQL 2000 Server, and want to ensure the fastest performance for my users. I expect the database to grow to around 25,000 records (after that, they'll be archived), and would like opinions/comments on the best way to access the data. I would guess that a recordset would be fastest, but how do you set up a form to be based on a recordset instead of just hooking directly to the underlying table
TIA
Terry RobertsCheck the ADO and ADP references in the Microsoft Office
2000/Visual Basic Programmer's Guide:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovradobasics.asp
You can also find a list of Access resources here:
http://msdn.microsoft.com/library/en-us/dnacc2k/html/acacclinks.asp
And...you really should considering getting the book: Access
Developer's Guide to SQL Server by
Mary Chipman and Andy Baron.
-Sue
On Mon, 10 May 2004 09:11:08 -0700, "Terry Roberts"
<anonymous@.discussions.microsoft.com> wrote:
>I'm building an Access front end (A2K2 ADP project) to view records on a SQL 2000 Server, and want to ensure the fastest performance for my users. I expect the database to grow to around 25,000 records (after that, they'll be archived), and would like opinions/comments on the best way to access the data. I would guess that a recordset would be fastest, but how do you set up a form to be based on a recordset instead of just hooking directly to the underlying table?
>TIA,
>Terry Roberts

Limit total # of records used in a report

For performance purposes, is it possible to limit the # of records
returned for use in a report? I know you can do SELECT TOP 1000, but in
instances where the report contains several subselects, you have to
perform that function with every SELECT statement. This also has the
potential of making maintenance hectic.
Is there a way to globally limit the rendered rows?
Thanks!
MikeHi Mike,
There is no direct properties of records per page.FOr controlling the
records per page you need to specify the height and accordingly number of
recrds are set to the report.
Thanks
Jasvinder
"Bassist695" wrote:
> For performance purposes, is it possible to limit the # of records
> returned for use in a report? I know you can do SELECT TOP 1000, but in
> instances where the report contains several subselects, you have to
> perform that function with every SELECT statement. This also has the
> potential of making maintenance hectic.
> Is there a way to globally limit the rendered rows?
> Thanks!
> Mike
>

Limit the number of records returned in Stored procedure.

In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.

Query

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

Select the top 100 records:

SELECT TOP 100 *

Select the top 10% of the total number of records:

SELECT TOP 10 percent *

|||

Or if you want to retrieve a limited number of records, but not necessarily the top X records, you could use the row_number() function. For example, to retrieve records 250-299 you could try

 
1SELECT*
2FROM3(
4SELECT *, row = row_number()OVER (ORDER BY id)
6FROM yourtable
7) a
8WHERE rowBETWEEN 250AND 299
 

Limit string lengh

There is a way to limit the length in characters returned by but I cant find what it is.

As an example, returning only the first 20 characters of a field.

Any help would be greatly appreciated.

You can either SUBSTRING or LEFT string functions in your case to get what you want. For example,

SELECTLEFT(yourcolumn, 20)AS col20_1,SUBSTRING(yourcolumn,1,20)as co20_2 FROM yourtable

You can find more string function from this link.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7oqb.asp

Limno

|||Thank you!