Showing posts with label row. Show all posts
Showing posts with label row. Show all posts

Thursday, March 29, 2012

count query problem

I have a table with a list of clients, some tasks associated with each and the status of each task (open, closed).

I want to return a SINGLE row for each client, along with a COUNT of the number of "open" and "closed" tasks.

The typical count statement I am using is this:

select SiteName, Count(IssueStatus) as cnt, IssueStatus,
from IssueMaster
group by SiteName, IssueStatus

It works fine, however, it returns a row for each combination of client and status. I would like it to return "Client, Count of Open, Count of Closed".

Any ideas?It depends on which SQL dialect you are using, but I'd use Sum() combined with CASE instead of Count().

-PatP|||I'm using MSSQL, but can I SUM a non-numeric column? And do you have an example I could use as a model?

Thanks!|||Like this:

SUM (CASE WHEN status='OPEN' THEN 1 ELSE 0 END) AS open_count|||Or still using COUNT:

COUNT (CASE WHEN status='OPEN' THEN 1 END) AS open_count

count property on group?

Is there any way to show the count of a collapsed group on the row set to
toggle visibility. For example, if I have rows for Purchase Orders and
grouped under that, rows for line detail that are hidden with Purcharse
Orders number as the toggle textbox, is there some way to show the detail
line count on the Purchase Order row? That is, short of establishing a
dataset?
--
Thanks,
CGWjust a thought, create a parameter and set it equal to the the line detail
row count and display the parameter for every purchase order.
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
> Is there any way to show the count of a collapsed group on the row set to
> toggle visibility. For example, if I have rows for Purchase Orders and
> grouped under that, rows for line detail that are hidden with Purcharse
> Orders number as the toggle textbox, is there some way to show the detail
> line count on the Purchase Order row? That is, short of establishing a
> dataset?
> --
> Thanks,
> CGW|||I'm used to setting paramater values with either a constant or a query (as in
dataset). How would the expression look? And if the count is some property I
can have with a parameter, could I not also have it for the group and/or
detail row count? I'm sorry, but I'm still new to a bit of this. If you could
give me just a little more clarification, it would really help.
Thanks
"Vaibhav" wrote:
> just a thought, create a parameter and set it equal to the the line detail
> row count and display the parameter for every purchase order.
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
> > Is there any way to show the count of a collapsed group on the row set to
> > toggle visibility. For example, if I have rows for Purchase Orders and
> > grouped under that, rows for line detail that are hidden with Purcharse
> > Orders number as the toggle textbox, is there some way to show the detail
> > line count on the Purchase Order row? That is, short of establishing a
> > dataset?
> > --
> > Thanks,
> >
> > CGW
>
>|||I am assuming that to retrieve the line details you are passing purchase
order to the details dataset. In the retireved dataset along with the other
columns add one more column with row count and then set the parameter equal
to the column value for the dataset.and display paramter.value for every
PO(line details count).
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:88F8172F-58C3-46B9-B691-AAFD48AD9D9D@.microsoft.com...
> I'm used to setting paramater values with either a constant or a query (as
> in
> dataset). How would the expression look? And if the count is some property
> I
> can have with a parameter, could I not also have it for the group and/or
> detail row count? I'm sorry, but I'm still new to a bit of this. If you
> could
> give me just a little more clarification, it would really help.
> Thanks
> "Vaibhav" wrote:
>> just a thought, create a parameter and set it equal to the the line
>> detail
>> row count and display the parameter for every purchase order.
>> "CGW" <CGW@.discussions.microsoft.com> wrote in message
>> news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
>> > Is there any way to show the count of a collapsed group on the row set
>> > to
>> > toggle visibility. For example, if I have rows for Purchase Orders and
>> > grouped under that, rows for line detail that are hidden with Purcharse
>> > Orders number as the toggle textbox, is there some way to show the
>> > detail
>> > line count on the Purchase Order row? That is, short of establishing a
>> > dataset?
>> > --
>> > Thanks,
>> >
>> > CGW
>>|||The dataset query returns both Purchase Order and Detail information with
grouping done at the report level. To put a count for PO detail in is
possible, but would require additional querying. I suppose I was just hoping
that SQL Reporting might have a property on groups, or something like that,
which could be used. Thank you.
"Vaibhav" wrote:
> I am assuming that to retrieve the line details you are passing purchase
> order to the details dataset. In the retireved dataset along with the other
> columns add one more column with row count and then set the parameter equal
> to the column value for the dataset.and display paramter.value for every
> PO(line details count).
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:88F8172F-58C3-46B9-B691-AAFD48AD9D9D@.microsoft.com...
> > I'm used to setting paramater values with either a constant or a query (as
> > in
> > dataset). How would the expression look? And if the count is some property
> > I
> > can have with a parameter, could I not also have it for the group and/or
> > detail row count? I'm sorry, but I'm still new to a bit of this. If you
> > could
> > give me just a little more clarification, it would really help.
> >
> > Thanks
> >
> > "Vaibhav" wrote:
> >
> >> just a thought, create a parameter and set it equal to the the line
> >> detail
> >> row count and display the parameter for every purchase order.
> >>
> >> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> >> news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
> >> > Is there any way to show the count of a collapsed group on the row set
> >> > to
> >> > toggle visibility. For example, if I have rows for Purchase Orders and
> >> > grouped under that, rows for line detail that are hidden with Purcharse
> >> > Orders number as the toggle textbox, is there some way to show the
> >> > detail
> >> > line count on the Purchase Order row? That is, short of establishing a
> >> > dataset?
> >> > --
> >> > Thanks,
> >> >
> >> > CGW
> >>
> >>
> >>
>
>|||You should be able to to do this thru the Count aggregation. Try
"=Count(Fields.MyField.Value)". This does require that the Purchase Order is
an actual group within the table.
"CGW" wrote:
> The dataset query returns both Purchase Order and Detail information with
> grouping done at the report level. To put a count for PO detail in is
> possible, but would require additional querying. I suppose I was just hoping
> that SQL Reporting might have a property on groups, or something like that,
> which could be used. Thank you.
> "Vaibhav" wrote:
> > I am assuming that to retrieve the line details you are passing purchase
> > order to the details dataset. In the retireved dataset along with the other
> > columns add one more column with row count and then set the parameter equal
> > to the column value for the dataset.and display paramter.value for every
> > PO(line details count).
> >
> > "CGW" <CGW@.discussions.microsoft.com> wrote in message
> > news:88F8172F-58C3-46B9-B691-AAFD48AD9D9D@.microsoft.com...
> > > I'm used to setting paramater values with either a constant or a query (as
> > > in
> > > dataset). How would the expression look? And if the count is some property
> > > I
> > > can have with a parameter, could I not also have it for the group and/or
> > > detail row count? I'm sorry, but I'm still new to a bit of this. If you
> > > could
> > > give me just a little more clarification, it would really help.
> > >
> > > Thanks
> > >
> > > "Vaibhav" wrote:
> > >
> > >> just a thought, create a parameter and set it equal to the the line
> > >> detail
> > >> row count and display the parameter for every purchase order.
> > >>
> > >> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> > >> news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
> > >> > Is there any way to show the count of a collapsed group on the row set
> > >> > to
> > >> > toggle visibility. For example, if I have rows for Purchase Orders and
> > >> > grouped under that, rows for line detail that are hidden with Purcharse
> > >> > Orders number as the toggle textbox, is there some way to show the
> > >> > detail
> > >> > line count on the Purchase Order row? That is, short of establishing a
> > >> > dataset?
> > >> > --
> > >> > Thanks,
> > >> >
> > >> > CGW
> > >>
> > >>
> > >>
> >
> >
> >|||Funny, I thought I had tried that. It works great now!!! Thanks!
"John W" wrote:
> You should be able to to do this thru the Count aggregation. Try
> "=Count(Fields.MyField.Value)". This does require that the Purchase Order is
> an actual group within the table.
> "CGW" wrote:
> > The dataset query returns both Purchase Order and Detail information with
> > grouping done at the report level. To put a count for PO detail in is
> > possible, but would require additional querying. I suppose I was just hoping
> > that SQL Reporting might have a property on groups, or something like that,
> > which could be used. Thank you.
> >
> > "Vaibhav" wrote:
> >
> > > I am assuming that to retrieve the line details you are passing purchase
> > > order to the details dataset. In the retireved dataset along with the other
> > > columns add one more column with row count and then set the parameter equal
> > > to the column value for the dataset.and display paramter.value for every
> > > PO(line details count).
> > >
> > > "CGW" <CGW@.discussions.microsoft.com> wrote in message
> > > news:88F8172F-58C3-46B9-B691-AAFD48AD9D9D@.microsoft.com...
> > > > I'm used to setting paramater values with either a constant or a query (as
> > > > in
> > > > dataset). How would the expression look? And if the count is some property
> > > > I
> > > > can have with a parameter, could I not also have it for the group and/or
> > > > detail row count? I'm sorry, but I'm still new to a bit of this. If you
> > > > could
> > > > give me just a little more clarification, it would really help.
> > > >
> > > > Thanks
> > > >
> > > > "Vaibhav" wrote:
> > > >
> > > >> just a thought, create a parameter and set it equal to the the line
> > > >> detail
> > > >> row count and display the parameter for every purchase order.
> > > >>
> > > >> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> > > >> news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
> > > >> > Is there any way to show the count of a collapsed group on the row set
> > > >> > to
> > > >> > toggle visibility. For example, if I have rows for Purchase Orders and
> > > >> > grouped under that, rows for line detail that are hidden with Purcharse
> > > >> > Orders number as the toggle textbox, is there some way to show the
> > > >> > detail
> > > >> > line count on the Purchase Order row? That is, short of establishing a
> > > >> > dataset?
> > > >> > --
> > > >> > Thanks,
> > > >> >
> > > >> > CGW
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >

Count of no. of characters

Hi,
Could anyone please let me know how many characters can we display in a single row in Crystal Report XI?
Thanks.It depends on the size of the page. Can you give us more informations on what you are trying to do

Tuesday, March 27, 2012

Count of Columns <> 0

How would you count the number of columns with a value not equal to 0 for
each row in a table?

Thanks!

Joe"Joe User" <joe@.user.com> wrote in message
news:c4s5vp$6je$1@.tribune.mayo.edu...
> How would you count the number of columns with a value not equal to 0 for
> each row in a table?
> Thanks!
> Joe

Here's one way:

select PrimaryKeyColumn,
case when col1 = 0 then 0 else 1 end +
case when col2 = 0 then 0 else 1 end +
case when col3 = 0 then 0 else 1 end +
...
case when coln = 0 then 0 else 1 end as 'NonZeroColumns'
from
dbo.MyTable

Simon|||Excellent!

Thanks!

Next question....
How does someone relatively new to tsql learn this sort of thing?

TIA

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4071a1c0$1_3@.news.bluewin.ch...
> "Joe User" <joe@.user.com> wrote in message
> news:c4s5vp$6je$1@.tribune.mayo.edu...
> > How would you count the number of columns with a value not equal to 0
for
> > each row in a table?
> > Thanks!
> > Joe
> Here's one way:
> select PrimaryKeyColumn,
> case when col1 = 0 then 0 else 1 end +
> case when col2 = 0 then 0 else 1 end +
> case when col3 = 0 then 0 else 1 end +
> ...
> case when coln = 0 then 0 else 1 end as 'NonZeroColumns'
> from
> dbo.MyTable
>
> Simon|||"Joe User" <joe@.user.com> wrote in message
news:c4sa4g$c6p$1@.tribune.mayo.edu...
> Excellent!
> Thanks!
> Next question....
> How does someone relatively new to tsql learn this sort of thing?
> TIA

<snip
Get a good book or two - there are some suggestions here:

http://vyaskn.tripod.com/sqlbooks.htm

But don't forget Books Online itself - it's very helpful to read through the
TSQL reference part. I don't mean read every word (unless you have a lot of
time on your hands...), but it helps to have an idea of what's available in
the language. Even if you only vaguely remember what a keyword does, or if
you only remember the name, you can always look it up. The list of functions
is another useful page to review, for the same reason. The
SELECT/INSERT/UPDATE/DELETE entries are very important, as are the CREATE
XXXX entries - all of them are linked to lots of related information, so you
can go into as much detail as you want.

Simon|||>> How would you count the number of columns with a value not equal to
0 for each row in a table? <<

SELECT keycol,
ABS(SIGN(col1)) +
ABS(SIGN(col2)) +
ABS(SIGN(col3)) + .. AS non_zero_tally
FROM Foobar;

Count occurence of a word

How can I count the rows in wich a specific word occurs in a field in the
detail row of a report?
Thanks
HThere are at least two approaches that come to my mind.
1. Add a caculated field to your dataset that calls a custom VB function
(either embedded in the report or external) which counts the words per row.
Then use the Sum() function to get the overall total.
2. Add a class-level variable to the report embedded code that keeps a
running total. Call a VB function from the field that needs to be counted.
HTH
--
----
Teo Lachev, MVP, MCSD, MCT
Home page and blog: http://www.prologika.com/
----
"Hennie" <spam_me@.linux.com> wrote in message
news:3F608B1B-5F27-4249-912E-0360B60C93FD@.microsoft.com...
> How can I count the rows in wich a specific word occurs in a field in the
> detail row of a report?
> Thanks
> Hsql

Count Occurances in a column

Is there a way to count the number of times a character or string
appears in a column in a table row/column?
Example, tblTable has column StringData varchar(1000)
StringData contains the value "Mary Had A Little Lamb Lamb and Then It
Died"
I want to know how many times "Lamb" appears in this string.
Any help is appreciated.
ThanksOne popular way to do this is:
SELECT ( LEN( stringdata ) -
LEN( REPLACE( stringdata, 'Lamb', '' ) ) ) / LEN( 'Lamb' )
FROM tbl ;
Alternatively, you can use a table of sequentially incrementing numbers and
construct a generic logic using SUBSTRING functions too.
Anith|||laurenq uantrell wrote:
> Is there a way to count the number of times a character or string
> appears in a column in a table row/column?
> Example, tblTable has column StringData varchar(1000)
> StringData contains the value "Mary Had A Little Lamb Lamb and Then It
> Died"
> I want to know how many times "Lamb" appears in this string.
> Any help is appreciated.
> Thanks
DECLARE @.str VARCHAR(1000)
SET @.str= 'Lamb'
SELECT LEN(REPLACE(stringdata,@.str,@.str+'_'))-LEN(stringdata)
FROM tbltable;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||LEN has its problems, so here's one with DATALENGTH:
declare @.text nvarchar(4000)
declare @.string nvarchar(4000)
set @.text = N'Mary Had A Little Lamb Lamb and Then It Died'
set @.string = N'Lamb'
select (datalength(@.text) - datalength(replace(@.text, @.string, N''))) /
datalength(@.string)
ML
http://milambda.blogspot.com/|||Yup, that is right.
Anith|||DECLARE @.StringData varchar(1000)
DECLARE @.counter int
SET @.StringData = 'Mary Had A Little Lamb Lamb and Then It Died'
SET @.counter = 0
WHILE PATINDEX('%Lamb%',@.StringData) <> 0
BEGIN
SET @.counter = @.counter + 1
SET @.StringData = STUFF(@.StringData, PATINDEX('%Lamb%',@.StringData), 4, '')
END
PRINT @.counter
If you want to do this for all rows in a table, one way to accomplish this
is to put the above into a (gasp!) cursor:
DECLARE @.pkcol int --Change this to match that datatype of your PK column
DECLARE @.StringData varchar(1000)
DECLARE @.counter int
CREATE TABLE #results (pkcol int, StringCount int) --same here
DECLARE my_cursor CURSOR STATIC FORWARD_ONLY
FOR
SELECT pkcol, StringData FROM tblTable
FETCH NEXT FROM my_cursor INTO @.pkcol, @.StringData
WHILE (@.@.FETCH_STATUS = 0 )
BEGIN
SET @.counter = 0
WHILE PATINDEX('%Lamb%',@.StringData) <> 0
BEGIN
SET @.counter = @.counter + 1
SET @.StringData = STUFF(@.StringData, PATINDEX('%Lamb%',@.StringData), 4,
'')
END
INSERT INTO #results VALUES (@.pkcol, @.counter)
FETCH NEXT FROM my_cursor INTO @.pkcol, @.StringData
END
SELECT pkcol, StringCount FROM #results
"laurenq uantrell" wrote:

> Is there a way to count the number of times a character or string
> appears in a column in a table row/column?
> Example, tblTable has column StringData varchar(1000)
> StringData contains the value "Mary Had A Little Lamb Lamb and Then It
> Died"
> I want to know how many times "Lamb" appears in this string.
> Any help is appreciated.
> Thanks
>|||Mark,
A single SELECT with a table of numbers may do better than a cursor :
SELECT COUNT(*)
FROM Nbrs
WHERE SUBSTRING( @.stringdata, n, LEN( 'Lamb' ) ) = 'Lamb' ;
Anith|||There's only crap on TV, so...
http://milambda.blogspot.com/2006/0...th-strings.html
ML
http://milambda.blogspot.com/|||On 16 Feb 2006 14:11:22 -0800, laurenq uantrell wrote:

>Is there a way to count the number of times a character or string
>appears in a column in a table row/column?
>Example, tblTable has column StringData varchar(1000)
>StringData contains the value "Mary Had A Little Lamb Lamb and Then It
>Died"
>I want to know how many times "Lamb" appears in this string.
>Any help is appreciated.
>Thanks
Hi laurenq,
SELECT ( DATALENGTH(StringData)
- DATALENGTH(REPLACE(StringData, 'Lamb', '')) )
/ DATALENGTH('Lamb')
FROM tblTable
Hugo Kornelis, SQL Server MVP|||Thanks David and all other posters for these solutions.
lq

Sunday, March 25, 2012

Count Frequencies of row using sql query

I'm new to t-sql, but i would like to know how to generate a frequency of a row, let say i would like to have a query to select the number of times the result of 'Good' shows up, and number of times the result of 'Perfect' shows up in the data provided. i've got like thousands of rows with hundreds of different result groups, in the data sample below i just use 3(Good, Perfect, and Bad) of them. can anyone give me a suggestion on how to do this? thanks.

CREATETABLE #Count(Result VARCHAR(60))

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

SELECTCOUNT(Result)[ResultBad]FROM #Count where result ='Bad'

groupby result

DROPTABLE #Count

ResultBad

2

Check for "group by" clause in BOL.

select Result, Count(*) as cnt

from #Count

group by Result

order by Result

go

AMB

|||

Code Snippet

select ResultBad= sum(case Result when 'Bad' then 1 else 0 end) ,
ResultGood= sum(case Result when 'Good' then 1 else 0 end) ,
ResultPerfect= sum(case Result when 'Perfect' then 1 else 0 end)
from #count

You can also use PIVOT if you are using Sql Server 2005...

http://msdn2.microsoft.com/en-us/library/ms177410(SQL.90).aspx

|||

You have already discovered or learned how to find dupes.

Are you asking how to calculate a probability?

Such as:

Code Snippet

CREATETABLE #Count(Result VARCHAR(60))

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

DECLARE @.frequency asint

DECLARE @.Count asint

DECLARE @.Probability asint

SET @.Frequency =(SELECTCOUNT(Result)[ResultBad] FROM #Count where result ='Perfect'

groupby result)

SET @.Count =(SELECTCOUNT(*)FROM #Count)

SET @.Probability = @.Frequency * 100 / @.Count

SELECTCAST(@.Probability as varchar(5))+'% Chance'

DROPTABLE #Count

Adamus

|||Thanks y'all, i should have known that i only need to modify my select statement. Sorry for confusion, but thanks!!

Sunday, March 11, 2012

Could not get the data of the row from the OLE DB provider 'SQLOLE

We are running SQL2000 SP3 on two servers. One is Server 2003 the other is
Server 2000. Both servers are linked to one another. The 2003 server is
running DTC. We are running several lightweight jobs that are occasionally,
and more often than we like, having the following error:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp basically set variables with data from both the primary server A and
the linked server B using views. This part is successful. The next step is
updating information in server B via a view. This appears to be where the
job is creating the above error. The last part of the sp is another update
entirely on the local server A.
I appreciate your help...
Thanks...Hi
Error 7312 is "Could not set up parameter for remote server '%.*ls'."
I have sometimes seen this error when a remote server has a deadlock or when
a lock can not be acquired to satisfy the request. Have a look at what is
happening on the linked server when this occurs.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisD" <ChrisD@.discussions.microsoft.com> wrote in message
news:D508BC5E-178C-4518-BF00-D17BA13143DF@.microsoft.com...
> We are running SQL2000 SP3 on two servers. One is Server 2003 the other
is
> Server 2000. Both servers are linked to one another. The 2003 server is
> running DTC. We are running several lightweight jobs that are
occasionally,
> and more often than we like, having the following error:
> Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
> [SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE DB
error
> trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
> [SQLSTATE 01000] (Error 7300). The step failed.
> The sp basically set variables with data from both the primary server A
and
> the linked server B using views. This part is successful. The next step
is
> updating information in server B via a view. This appears to be where the
> job is creating the above error. The last part of the sp is another
update
> entirely on the local server A.
> I appreciate your help...
> Thanks...
>

Could not get the data of the row from the OLE DB provider 'SQLOLE

I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...
You might want to post your actual T-SQL statements and some configuration
values.
Sincerely,
Anthony Thomas

"ChrisD" <ChrisD@.discussions.microsoft.com> wrote in message
news:1E63096F-95DF-480E-9BFB-6B36BEE44901@.microsoft.com...
I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute
a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...

Could not get the data of the row from the OLE DB provider 'SQLOLE

I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute
a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE D
B error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...You might want to post your actual T-SQL statements and some configuration
values.
Sincerely,
Anthony Thomas
"ChrisD" <ChrisD@.discussions.microsoft.com> wrote in message
news:1E63096F-95DF-480E-9BFB-6B36BEE44901@.microsoft.com...
I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute
a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE D
B error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...

Could not get the data of the row from the OLE DB provider 'SQLOLE

I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...You might want to post your actual T-SQL statements and some configuration
values.
Sincerely,
Anthony Thomas
"ChrisD" <ChrisD@.discussions.microsoft.com> wrote in message
news:1E63096F-95DF-480E-9BFB-6B36BEE44901@.microsoft.com...
I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute
a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...

Thursday, March 8, 2012

Could not find stored procedure

I have this error (Merge Replication):

The row was inserted at 'DISTRIBUTION.db_main' but could not
be inserted at 'subscriber.db_test'. Could not find stored
procedure 'bp_ins_8284C429C5514F08046769C0F2D24607'.

How can I solve this problem?

Thanks.

the proc name doesn't look right, it should be 'sp_ins...', not 'bp_ins...'. Regardless, did you or someone else drop the proc along the way by accident? You may want to try reinitializing the subscription.

|||The first time you generate the publication the stored procedure name will begin with sp_ins; with subsequent reinitializations it will increment the letter, the next one will be ap_ins, then bp_ins.|||BTW-I opened a support incident on this problem in 2002, IIRC - the support engineer was Rand Boyd. Hope this helps you to track it.|||

How can i track it?

|||

I can't recall the srx number, nor the date I opened it. The work around was to deploy all the subscribers. That involved dropping all existing subscribers and resyncing.

I am not sure if the MS guy was able to repro the problem or solve the bug. You would have to call Microsoft and mention my name and have them search on it. Perhpas one of the MS guys who hang out here could seach the RAID database for it.

|||Do you know who is that writes in sysmergearticles?
|||I am not sure what you mean by this. Sysmergearticles is created when you do a sp_replicationdboption 'database','merge publish','true'

The merge agent will fill in this table with the values.|||Why does Merge agent create a new stored? In which situation?
|||Yes, the first time the merge agent runs to apply the snapshot it should fill in these values. It should not modify them at any other point in time.|||Should it modify them only if there is a reinizialization?

I think that it is so but...

For solve this problem I reinitialize from publisher four subscriber then I lunch themerge agent and I have this error: Line 1: Incorrect syntax near ','. 170

and

The process could not deliver the snapshot to the Subscriber. -2147201001.

Sad Sad Sad
|||I drop all subscriptions of this publication. I drop my publication that give me error.

I do a new publication. I do my subscriptions with no initializate schema and data.

Now all go well.

Could not find stored procedure

I have this error (Merge Replication):

The row was inserted at 'DISTRIBUTION.db_main' but could not
be inserted at 'subscriber.db_test'. Could not find stored
procedure 'bp_ins_8284C429C5514F08046769C0F2D24607'.

How can I solve this problem?

Thanks.

the proc name doesn't look right, it should be 'sp_ins...', not 'bp_ins...'. Regardless, did you or someone else drop the proc along the way by accident? You may want to try reinitializing the subscription.

|||The first time you generate the publication the stored procedure name will begin with sp_ins; with subsequent reinitializations it will increment the letter, the next one will be ap_ins, then bp_ins.|||BTW-I opened a support incident on this problem in 2002, IIRC - the support engineer was Rand Boyd. Hope this helps you to track it.|||

How can i track it?

|||

I can't recall the srx number, nor the date I opened it. The work around was to deploy all the subscribers. That involved dropping all existing subscribers and resyncing.

I am not sure if the MS guy was able to repro the problem or solve the bug. You would have to call Microsoft and mention my name and have them search on it. Perhpas one of the MS guys who hang out here could seach the RAID database for it.

|||Do you know who is that writes in sysmergearticles?
|||I am not sure what you mean by this. Sysmergearticles is created when you do a sp_replicationdboption 'database','merge publish','true'

The merge agent will fill in this table with the values.|||Why does Merge agent create a new stored? In which situation?
|||Yes, the first time the merge agent runs to apply the snapshot it should fill in these values. It should not modify them at any other point in time.|||Should it modify them only if there is a reinizialization?

I think that it is so but...

For solve this problem I reinitialize from publisher four subscriber then I lunch themerge agent and I have this error: Line 1: Incorrect syntax near ','. 170

and

The process could not deliver the snapshot to the Subscriber. -2147201001.

Sad Sad Sad
|||I drop all subscriptions of this publication. I drop my publication that give me error.

I do a new publication. I do my subscriptions with no initializate schema and data.

Now all go well.

Could not find stored procedure

I have this error (Merge Replication):

The row was inserted at 'DISTRIBUTION.db_main' but could not
be inserted at 'subscriber.db_test'. Could not find stored
procedure 'bp_ins_8284C429C5514F08046769C0F2D24607'.

How can I solve this problem?

Thanks.

the proc name doesn't look right, it should be 'sp_ins...', not 'bp_ins...'. Regardless, did you or someone else drop the proc along the way by accident? You may want to try reinitializing the subscription.

|||The first time you generate the publication the stored procedure name will begin with sp_ins; with subsequent reinitializations it will increment the letter, the next one will be ap_ins, then bp_ins.|||BTW-I opened a support incident on this problem in 2002, IIRC - the support engineer was Rand Boyd. Hope this helps you to track it.|||

How can i track it?

|||

I can't recall the srx number, nor the date I opened it. The work around was to deploy all the subscribers. That involved dropping all existing subscribers and resyncing.

I am not sure if the MS guy was able to repro the problem or solve the bug. You would have to call Microsoft and mention my name and have them search on it. Perhpas one of the MS guys who hang out here could seach the RAID database for it.

|||Do you know who is that writes in sysmergearticles?
|||I am not sure what you mean by this. Sysmergearticles is created when you do a sp_replicationdboption 'database','merge publish','true'

The merge agent will fill in this table with the values.|||Why does Merge agent create a new stored? In which situation?
|||Yes, the first time the merge agent runs to apply the snapshot it should fill in these values. It should not modify them at any other point in time.|||Should it modify them only if there is a reinizialization?

I think that it is so but...

For solve this problem I reinitialize from publisher four subscriber then I lunch themerge agent and I have this error: Line 1: Incorrect syntax near ','. 170

and

The process could not deliver the snapshot to the Subscriber. -2147201001.

Sad Sad Sad
|||I drop all subscriptions of this publication. I drop my publication that give me error.

I do a new publication. I do my subscriptions with no initializate schema and data.

Now all go well.

Could not find stored procedure

I have this error (Merge Replication):

The row was inserted at 'DISTRIBUTION.db_main' but could not
be inserted at 'subscriber.db_test'. Could not find stored
procedure 'bp_ins_8284C429C5514F08046769C0F2D24607'.

How can I solve this problem?

Thanks.

the proc name doesn't look right, it should be 'sp_ins...', not 'bp_ins...'. Regardless, did you or someone else drop the proc along the way by accident? You may want to try reinitializing the subscription.

|||The first time you generate the publication the stored procedure name will begin with sp_ins; with subsequent reinitializations it will increment the letter, the next one will be ap_ins, then bp_ins.|||BTW-I opened a support incident on this problem in 2002, IIRC - the support engineer was Rand Boyd. Hope this helps you to track it.|||

How can i track it?

|||

I can't recall the srx number, nor the date I opened it. The work around was to deploy all the subscribers. That involved dropping all existing subscribers and resyncing.

I am not sure if the MS guy was able to repro the problem or solve the bug. You would have to call Microsoft and mention my name and have them search on it. Perhpas one of the MS guys who hang out here could seach the RAID database for it.

|||Do you know who is that writes in sysmergearticles?
|||I am not sure what you mean by this. Sysmergearticles is created when you do a sp_replicationdboption 'database','merge publish','true'

The merge agent will fill in this table with the values.|||Why does Merge agent create a new stored? In which situation?
|||Yes, the first time the merge agent runs to apply the snapshot it should fill in these values. It should not modify them at any other point in time.|||Should it modify them only if there is a reinizialization?

I think that it is so but...

For solve this problem I reinitialize from publisher four subscriber then I lunch themerge agent and I have this error: Line 1: Incorrect syntax near ','. 170

and

The process could not deliver the snapshot to the Subscriber. -2147201001.

Sad Sad Sad
|||I drop all subscriptions of this publication. I drop my publication that give me error.

I do a new publication. I do my subscriptions with no initializate schema and data.

Now all go well.

Could not find stored procedure

I have this error (Merge Replication):

The row was inserted at 'DISTRIBUTION.db_main' but could not
be inserted at 'subscriber.db_test'. Could not find stored
procedure 'bp_ins_8284C429C5514F08046769C0F2D24607'.

How can I solve this problem?

Thanks.

the proc name doesn't look right, it should be 'sp_ins...', not 'bp_ins...'. Regardless, did you or someone else drop the proc along the way by accident? You may want to try reinitializing the subscription.

|||The first time you generate the publication the stored procedure name will begin with sp_ins; with subsequent reinitializations it will increment the letter, the next one will be ap_ins, then bp_ins.|||BTW-I opened a support incident on this problem in 2002, IIRC - the support engineer was Rand Boyd. Hope this helps you to track it.|||

How can i track it?

|||

I can't recall the srx number, nor the date I opened it. The work around was to deploy all the subscribers. That involved dropping all existing subscribers and resyncing.

I am not sure if the MS guy was able to repro the problem or solve the bug. You would have to call Microsoft and mention my name and have them search on it. Perhpas one of the MS guys who hang out here could seach the RAID database for it.

|||Do you know who is that writes in sysmergearticles?
|||I am not sure what you mean by this. Sysmergearticles is created when you do a sp_replicationdboption 'database','merge publish','true'

The merge agent will fill in this table with the values.|||Why does Merge agent create a new stored? In which situation?
|||Yes, the first time the merge agent runs to apply the snapshot it should fill in these values. It should not modify them at any other point in time.|||Should it modify them only if there is a reinizialization?

I think that it is so but...

For solve this problem I reinitialize from publisher four subscriber then I lunch themerge agent and I have this error: Line 1: Incorrect syntax near ','. 170

and

The process could not deliver the snapshot to the Subscriber. -2147201001.

Sad Sad Sad
|||I drop all subscriptions of this publication. I drop my publication that give me error.

I do a new publication. I do my subscriptions with no initializate schema and data.

Now all go well.

Wednesday, March 7, 2012

could not find row in sysindexes for database

I am trying to attach a database that was created on another server. I believe the database was created using SQL Server Management Studio Express 2005. Now, I have successfully attached the database in my development environment, which is using SQL Server Management Studio Express 2005.

However, when I tried to attach the database onto our production server, which is SQL Server 2000 I received the error:

Error 602:
Could not find row in sysindexes for database ID 18, object ID 1, index ID 1. Run DBCC CheckTable on sysindexes.

In my development environment, I ran DBCC CheckDB on the source database and no errors are returned.

I aslo checked the compatibility level under the database properties and it shows: Sql Server 2000 (80)

So, this should not be a version incompatibility issue. What is causing the attach to fail on SQL Server 2000?

Thanks for any help.

What I know is:

If you have an .mdf file in SQL Server 2000, then you attached it to SQL Server 2005 ... no way you can use it (the .mdf file) again with SQL Server 2000.

What I think about is SQL Serve 2006 .mdf files can not be used with SQL Server 2000!!

Try Backup/Restore option or Copy Database Wizard.

Good luck.

|||

Are you trying to attach a SQL 2005 database to a SQL 2000 server? If so, that's the issue. Obviously you can upgrade (2000 --> 2005), but you can't downgrade.

-Damien

|||

CS4Ever:

What I know is:

If you have an .mdf file in SQL Server 2000, then you attached it to SQL Server 2005 ... no way you can use it (the .mdf file) again with SQL Server 2000.

What I think about is SQL Serve 2006 .mdf files can not be used with SQL Server 2000!!

Try Backup/Restore option or Copy Database Wizard.

Good luck.

Here is a link that support what I wrote and it marked as answered in MSDN forum: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=165696&SiteID=17

This link might be helpful for you as well: http://sqlserver-qa.net/blogs/tools/archive/2007/08/14/error-could-not-find-row-in-sysindexes-for-database-8966-823-and-602.aspx

Good luck.

Could Not Find Row In Sysindexes

Folks,
I have an MDF and LDF file which I am attempting to attach as a SQL db. I
keep getting the error 602 that begins with "could not find row in sysindexes
for database ID 13, object id 1, index id 1".
I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I tried
to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
get this error?
Bob Sullentrup
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
Yes, that is the error you get when trying to attach a database that is from
a higher version of SQL Server.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup
|||Hi,
Looks like your database is corrupted. Do you a good copy of database in
your server; else you may need to restore
from a good backup available.
For SQL 2000; Please execute DBCC CHECKDB atleast weekly once to chek if
there is any corruptions.
Thanks
Hari
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup
|||And the answer is ...
I installed SQL Server 2005 and tried to load my mdf file. It worked
perfectly, so clearly I was trying to load into SQL Server 2000 something
that had the 2005 format.
Meanwhile, I scripted the objects and tweaked the script and loaded the
gemisch into SQL 2000. Then I exported the data from 2005 into 2000. Works
like a charm in SQL 2000 now.
Bob Sullentrup
"Gail Erickson [MS]" wrote:

> Yes, that is the error you get when trying to attach a database that is from
> a higher version of SQL Server.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
> news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
>
>

Could Not Find Row In Sysindexes

Folks,
I have an MDF and LDF file which I am attempting to attach as a SQL db. I
keep getting the error 602 that begins with "could not find row in sysindexe
s
for database ID 13, object id 1, index id 1".
I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I tried
to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
get this error?
--
Bob Sullentrup> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
Yes, that is the error you get when trying to attach a database that is from
a higher version of SQL Server.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup|||Hi,
Looks like your database is corrupted. Do you a good copy of database in
your server; else you may need to restore
from a good backup available.
For SQL 2000; Please execute DBCC CHECKDB atleast weekly once to chek if
there is any corruptions.
Thanks
Hari
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup|||And the answer is ...
I installed SQL Server 2005 and tried to load my mdf file. It worked
perfectly, so clearly I was trying to load into SQL Server 2000 something
that had the 2005 format.
Meanwhile, I scripted the objects and tweaked the script and loaded the
gemisch into SQL 2000. Then I exported the data from 2005 into 2000. Works
like a charm in SQL 2000 now.
Bob Sullentrup
"Gail Erickson [MS]" wrote:

> Yes, that is the error you get when trying to attach a database that is fr
om
> a higher version of SQL Server.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in messag
e
> news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
>
>

Could not fetch a row 'VFPOLEDB'

I am running an MSDE of SQL Server 2000 with the latest server pack.
I set up a linked server connecting to a FoxPro database using the vfpoledb
provider.
On my development machine I can get this linked server to work, but on my
test machine, without a full SQL version, I get the error "Could not fetch a
row...". Does anyone have any ideas on how to fix this? I have tried
reinstalling the vfpoledb.exe but that didn't help it.
--
Evan HennisHi
I am not a VFP expert, but you may want to check that the locations for the
VFP database are correct and that MDAC versions are up-to-date and
consistent, and VFP is patched to the same level.
You may also want to see if your permissions on these machines varies.
If this is working on one machine and not the other implies the mechanism is
correct, although if the data is different it could be a factor.
The are plenty of posts concerning this error and VFP
http://tinyurl.com/94xhx and replies suggest that you check primary keys,
read http://support.microsoft.com/default.aspx?scid=kb;en-us;207595 ...
John
"EvanHennis" wrote:
> I am running an MSDE of SQL Server 2000 with the latest server pack.
> I set up a linked server connecting to a FoxPro database using the vfpoledb
> provider.
> On my development machine I can get this linked server to work, but on my
> test machine, without a full SQL version, I get the error "Could not fetch a
> row...". Does anyone have any ideas on how to fix this? I have tried
> reinstalling the vfpoledb.exe but that didn't help it.
> --
> Evan Hennis|||The actual error is:
"Could not fetch a row from OLE DB provider 'VFPOLEDB'
I am trying to connect to a local FoxPro database. Is there some security on
the local computer I need to set?
--
Evan Hennis|||Hi
Being able to connecting to the database probably rules out permissions at a
OS level. It is not clear if this is a networked database or separate ones,
if the latter check for differences in the databases. If not it is most
likely environmental e.g. MDAC.
John
"EvanHennis" wrote:
> The actual error is:
> "Could not fetch a row from OLE DB provider 'VFPOLEDB'
> I am trying to connect to a local FoxPro database. Is there some security on
> the local computer I need to set?
> --
> Evan Hennis
>|||On the test machine I am running Win2000 and I installed the MDAC 2.8. On the
development maching I am in XP Pro. Which I beleive has MDAC 2.8 built in. Is
there a way that I can compare the permissions and such on the MDAC?
--
Evan Hennis
"John Bell" wrote:
> Hi
> Being able to connecting to the database probably rules out permissions at a
> OS level. It is not clear if this is a networked database or separate ones,
> if the latter check for differences in the databases. If not it is most
> likely environmental e.g. MDAC.
> John
>
> "EvanHennis" wrote:
> > The actual error is:
> > "Could not fetch a row from OLE DB provider 'VFPOLEDB'
> >
> > I am trying to connect to a local FoxPro database. Is there some security on
> > the local computer I need to set?
> > --
> > Evan Hennis
> >
> >|||Hi
Download the the MDAC component checker
http://msdn.microsoft.com/data/mdac/downloads/default.aspx.
You have not said if this is the same database!!!
John
"EvanHennis" wrote:
> On the test machine I am running Win2000 and I installed the MDAC 2.8. On the
> development maching I am in XP Pro. Which I beleive has MDAC 2.8 built in. Is
> there a way that I can compare the permissions and such on the MDAC?
> --
> Evan Hennis
>
> "John Bell" wrote:
> > Hi
> >
> > Being able to connecting to the database probably rules out permissions at a
> > OS level. It is not clear if this is a networked database or separate ones,
> > if the latter check for differences in the databases. If not it is most
> > likely environmental e.g. MDAC.
> >
> > John
> >
> >
> > "EvanHennis" wrote:
> >
> > > The actual error is:
> > > "Could not fetch a row from OLE DB provider 'VFPOLEDB'
> > >
> > > I am trying to connect to a local FoxPro database. Is there some security on
> > > the local computer I need to set?
> > > --
> > > Evan Hennis
> > >
> > >|||Same Database? Well, each computer has its own MSDE instance on it. They are
both connecting to a FoxPro database on the development machine. BUT, I have
tried connecting to a local FoxPro database on the test machine and it
doesn't work. But if I try to connect to a local database on the development
machine it works fine.
--
Evan Hennis
"John Bell" wrote:
> Hi
> Download the the MDAC component checker
> http://msdn.microsoft.com/data/mdac/downloads/default.aspx.
> You have not said if this is the same database!!!
> John
> "EvanHennis" wrote:
> > On the test machine I am running Win2000 and I installed the MDAC 2.8. On the
> > development maching I am in XP Pro. Which I beleive has MDAC 2.8 built in. Is
> > there a way that I can compare the permissions and such on the MDAC?
> > --
> > Evan Hennis
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Being able to connecting to the database probably rules out permissions at a
> > > OS level. It is not clear if this is a networked database or separate ones,
> > > if the latter check for differences in the databases. If not it is most
> > > likely environmental e.g. MDAC.
> > >
> > > John
> > >
> > >
> > > "EvanHennis" wrote:
> > >
> > > > The actual error is:
> > > > "Could not fetch a row from OLE DB provider 'VFPOLEDB'
> > > >
> > > > I am trying to connect to a local FoxPro database. Is there some security on
> > > > the local computer I need to set?
> > > > --
> > > > Evan Hennis
> > > >
> > > >|||Hi Evan,
It may not solve your problem, but be sure you've got the latest VFP OLE DB
provider (VFP9), downloadable from
http://msdn.microsoft.com/vfoxpro/downloads/updates . It is not included
with an MDAC installation.
Are you trying to read data or update data? Updating VFP data via a linked
server was not supported in earlier versions of the OLE DB data provider.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"EvanHennis" <EvanHennis@.discussions.microsoft.com> wrote in message
news:1628DF07-BF7C-4265-8B19-00733D8CA75F@.microsoft.com...
>I am running an MSDE of SQL Server 2000 with the latest server pack.
> I set up a linked server connecting to a FoxPro database using the
> vfpoledb
> provider.
> On my development machine I can get this linked server to work, but on my
> test machine, without a full SQL version, I get the error "Could not fetch
> a
> row...". Does anyone have any ideas on how to fix this? I have tried
> reinstalling the vfpoledb.exe but that didn't help it.
> --
> Evan Hennis|||Thanks for responding but I can't use the latest VFP OLE DB because we have a
pretty large application and I can't make a change like that. I am trying to
do a normal read, Updates are not supported.
I had this working at one point so I am stumped.
--
Evan Hennis
"Cindy Winegarden" wrote:
> Hi Evan,
> It may not solve your problem, but be sure you've got the latest VFP OLE DB
> provider (VFP9), downloadable from
> http://msdn.microsoft.com/vfoxpro/downloads/updates . It is not included
> with an MDAC installation.
> Are you trying to read data or update data? Updating VFP data via a linked
> server was not supported in earlier versions of the OLE DB data provider.
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy_winegarden@.msn.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
> "EvanHennis" <EvanHennis@.discussions.microsoft.com> wrote in message
> news:1628DF07-BF7C-4265-8B19-00733D8CA75F@.microsoft.com...
> >I am running an MSDE of SQL Server 2000 with the latest server pack.
> > I set up a linked server connecting to a FoxPro database using the
> > vfpoledb
> > provider.
> > On my development machine I can get this linked server to work, but on my
> > test machine, without a full SQL version, I get the error "Could not fetch
> > a
> > row...". Does anyone have any ideas on how to fix this? I have tried
> > reinstalling the vfpoledb.exe but that didn't help it.
> > --
> > Evan Hennis
>
>|||I downloaded the Component Checker and I don't see the VFPOLEDB.dll in the
list of files. Could this be the issue?
--
Evan Hennis|||Hi
I think Cindy has answered this! You can check the file version numbers by
looking at the file properties. If they are different you may have to either
change the way you are doing this or regression test the parts that use the
OLEDB provider.
John
"EvanHennis" wrote:
> I downloaded the Component Checker and I don't see the VFPOLEDB.dll in the
> list of files. Could this be the issue?
> --
> Evan Hennis
>|||I don't know what you mean by saying "Cindy has answered this"? I have
installed the VFP 8 Runtime and it doesn't seem to work. So I don't think it
has anything to do with my install. Is there some place that I can check to
see if I have the VFP OLE DB installed?
--
Evan Hennis
"John Bell" wrote:
> Hi
> I think Cindy has answered this! You can check the file version numbers by
> looking at the file properties. If they are different you may have to either
> change the way you are doing this or regression test the parts that use the
> OLEDB provider.
> John
> "EvanHennis" wrote:
> > I downloaded the Component Checker and I don't see the VFPOLEDB.dll in the
> > list of files. Could this be the issue?
> > --
> > Evan Hennis
> >|||I found the issue. For some reason my installer, which is an ENTIRE different
can of worms, wasn't installing the VFP stuff correctly. I reverted back to
an older version and that fixed my issue. I am still confused that when I
tried to install the run time from a downloaded exe that it didn't work.
Anyway, thanks for all the help.
--
Evan Hennis
"EvanHennis" wrote:
> I don't know what you mean by saying "Cindy has answered this"? I have
> installed the VFP 8 Runtime and it doesn't seem to work. So I don't think it
> has anything to do with my install. Is there some place that I can check to
> see if I have the VFP OLE DB installed?
> --
> Evan Hennis
>
> "John Bell" wrote:
> > Hi
> >
> > I think Cindy has answered this! You can check the file version numbers by
> > looking at the file properties. If they are different you may have to either
> > change the way you are doing this or regression test the parts that use the
> > OLEDB provider.
> >
> > John
> >
> > "EvanHennis" wrote:
> >
> > > I downloaded the Component Checker and I don't see the VFPOLEDB.dll in the
> > > list of files. Could this be the issue?
> > > --
> > > Evan Hennis
> > >|||Hi Evan,
The VFP runtime is different from the OLE DB data provider. The runtime
supports Visual FoxPro applications (EXEs and DLLs), and also access to Fox
data from within those applications. The OLE DB data provider supports
access to Fox data from "outside." I don't know how you installed the
runtime but it doesn't necessarily install the OLE DB data provider, unless
by the "runtime" you mean a full install of the Visual FoxPro IDE, which
will, of course, install the OLE DB data provider.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"EvanHennis" <EvanHennis@.discussions.microsoft.com> wrote in message
news:BFFC8772-7669-4429-8DA3-892BA5441C02@.microsoft.com...
>I found the issue. For some reason my installer, which is an ENTIRE
>different
> can of worms, wasn't installing the VFP stuff correctly. I reverted back
> to
> an older version and that fixed my issue. I am still confused that when I
> tried to install the run time from a downloaded exe that it didn't work.|||I am not sure why I was worried about the "runtime". It was a long worthless
day so who knows. Any way, the issue was with the VFP OLE DB provider not
getting installed. I redid my installer and it works now.
Thanks,
-- Evan Hennis