i recently installed sql server 2005 standard edition and create a new linked server with the name QODBC and tried to run the following report

select * from openquery(QODBC, 'sp_report profitandlossdetail show text, blank, txntype, date, refnumber, name, memo, clearedstatus, splitaccount, defit,credit, runningbalance prameters datemacro=''ThisYearToDate'', AccountFilterType = ''ExpenseAndOtherExpense''')

and got the following error message:

This worked fine in sql 2000, but not in sql 2005... plz help me where did i go wrong

Thanks

Keats

Looks like you added a typo between using the statement in MS SQL Server 2000 and 2005. defit should be debit.

select * from openquery(QODBC, 'sp_report profitandlossdetail show text, blank, txntype, date, refnumber, name, memo, clearedstatus, splitaccount, debit, credit, runningbalance prameters datemacro=''ThisYearToDate'', AccountFilterType = ''ExpenseAndOtherExpense''')

There is no column named "defit" as indicated by the error message.

Hi thanks for the quick response...

But this still doesnt work for me a new error message pops up:

OLE DB provider "MSDASQL" for linked server "QODBC" returned message "[QODBC] Error".

Same is the Case with few other reports which worked in sql 2000... is it due to more number of columns that it returns: here in my case the report will return around 1500 columns. is there any limitation on the row size that i fetch from QB through linked server.

Thanks

Keats

There's even more typos here!

select * from openquery(QODBC, 'sp_report profitandlossdetail show text, blank, txntype, date, refnumber, name, memo, clearedstatus, splitaccount, debit, credit, runningbalance parameters datemacro=''ThisYearToDate'', AccountFilterType = ''ExpenseAndOtherExpense''')

"prameters" should be parameters

No this time its not the typos... here are the list of sp_reports that returned the same message

select * from openquery(QODBC, 'sp_report profitandlossdetail show text, blank, txntype, date, refnumber, name, memo, clearedstatus, splitaccount, debit, credit, runningbalance parameters datemacro=''ThisYearToDate'', AccountFilterType = ''ExpenseAndOtherExpense''')

select * from OPENQUERY(QODBC,'sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount parameters datemacro=''ThisYearToDate'',  SummarizeColumnsBy = ''Class''')

other reports just worked normaly as they used to... i googled for this error and found similar problem at  the post  http://www.qdeveloper.com.au/forum.php?homeinclude=topicdetails&forum_id=1&category_id=17&post_id=1382

I tried all the things mentioned in the above post and even reconfigured the Ole Automation Procedures by following the steps :

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO 

And stil i get this error message below:

OLE DB provider "MSDASQL" for linked server "QODBC" returned message "[QODBC] Error".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "QODBC".

or sometimes end up with other error message:

Select * from OpenQuery(QODBC, 'sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount parameters DateMacro = ''ThisYearToDate'', SummarizeColumnsBy = ''Month''')

Select * from OpenQuery(QODBC,'sp_report BalanceSheetStandard show Amount_Title, Text, Label, Amount parameters DateMacro = ''ThisYearToDate'', SummarizeColumnsBy = ''TotalOnly''')

Even though above reports take some time to display the output they are working fine....

Interesting. How does this run?

select * from openquery(QODBC, 'sp_report profitandlossdetail show rownumber, text, blank, txntype, date, refnumber, name, memo, clearedstatus, splitaccount, debit, credit, runningbalance parameters datemacro=''ThisYearToDate'', AccountFilterType = ''ExpenseAndOtherExpense''')