OpenQuery function 1

[AccessSQLUser]

Can any select statement be used with the OpenQuery funtion? I need to do a select statement on a table in my linked server called DataLineDBFs. This is what I did:

select * from openquery(DatalineDBFs, 'SELECT AID, Max(Convert(SmallDateTime,[DOS])) AS LastWorked
FROM Aidhrs GROUP BY AID')

I get the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] Undefined function 'Convert' in expression.]

 

[tlbroadbent]

My understanding is that OpenQuery executes a pass-through query on the linked server. Therefore, the query syntax must be acceptable on DBase. Being unfamiliar with DBase, I can only guess that convert is not a recognized DBase function. Perhaps there is an equivalent DBase function.

If you are unable to find a function that works on DBase try modifying the query as follows.

Select
AID,
Cast(LastWorked As SmallDateTime) As LastWorked
From OpenQuery(DatalineDBFs,
'SELECT AID, Max([DOS]) AS LastWorked
FROM Aidhrs GROUP BY AID') Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[AccessSQLUser]

I can't seem to get it to work.
Well, let me tell you my whole situation here.
I have 2 SQL servers that have the same linked servers (which are basically DBF files and I created a DSN for them through ODBC). I need to run the same stored procedure on both SQL servers. This stored procedure creates a table called [Last Worked] which is base on one of the DBF tables. This is the code that I had until now on one of my servers:

Delete [Last Worked]
INSERT INTO [Last Worked] (AID, LastWorked)
SELECT AID, Max(Convert(SmallDateTime,[DOS])) AS LastWorked
FROM DatalineDBFs...Aidhrs

The stored procedure usually worked but there were times when I would get this error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]

and it took a few times until it worked ok.

Now I'm trying to run the same stored procedure on a different SQL Server and when I do I get the error:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
So that's why I decided to try using the OpenQuery function but now I have the problem with the Convert. I tried to make this as clear as possible. If there is something that's unclear, please ask me. Thank you.

 

[tlbroadbent]

If the query works on one SQL server and not on the other I would verify that the SQL servers are setup the same way, versions are the same, the link is the same, the drivers are the same, the database compatibility level is the same, login permissions are the same... You get the idea.

What happened when you tried my recommended change? "I can't seem to get it to work" is not descriptive enough. Did you get syntax errors, ODBC errors, or what?

Thanks, Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[AccessSQLUser]

Ok. Here goes. We realized why one server was acting a little strange. The DSN for that server was referring to files on a different server and now that we moved the files to the Server that SQL Server is on and changed the DSN to look at those files, it works great. And the stored procedure that contains the following code works just fine.

Delete [Last Worked]
INSERT INTO [Last Worked] (AID, LastWorked)
SELECT AID, Max(Convert(SmallDateTime,[DOS])) AS LastWorked
FROM DatalineDBFs...Aidhrs

But when I want to run it on the other SQL Server it gives the invalid catalog or schema error.
Everything seems to be the same on both servers.

So now I want to use the OpenQuery function and when I tried your recommened change, I get the following error:

Server: Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type text to smalldatetime is not allowed.

 

[tlbroadbent]

What is the format of the column [DOS]? I assumed it was a character field with a date format. We still might be able to fix it if the format is workable. Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[AccessSQLUser]

It's a String datatype but has the format of a date. Ex. '12/31/01'

 

[AccessSQLUser]

I don't think that the problem is with the format because the following works on the other server:
SELECT AID, Max(cast([DOS] as SmallDateTime)) AS LastWorked
FROM DatalineDBFs...Aidhrs
group by aid

 

[tlbroadbent]

Try this query.

Select
AID,
max(Cast(Cast(DOS As varchar) As SmallDateTime)) As LastWorked

From OpenQuery(DatalineDBFs,
'SELECT AID, DOS FROM Aidhrs')

Group By AID

Cast(DOS As varchar) is required because the value is being returned as text data type which cannot be converted to datetime. However, it can be converted to varchar which can be converted to datetime.

The aggregation (Group By and Max) must be done after the conversion to smalldatetime data type. Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[AccessSQLUser]

Guess what? Your suggestion using the Cast function also worked on the other server but nothing seems to be working on the second server!?

 

[AccessSQLUser]

Great it worked!! Thanks tons!

 

[AccessSQLUser]

I have one more question for you that I was wondering about. Before I had mentioned that the stored procedure would sometimes work on one of our servers and sometimes not. And then we realized that the DBF files were on a different server and when we moved them to the SQL server and created a DSN based on those, it worked great. We assume that the reason why it didn't work the other way was because it was timing out trying to connect to the other server. Is there any way to keep the files on the other server and not have this problem?

 

[Guest_imported]

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]File 'ingreso1.dbf' does not exist.]

 

[tlbroadbent]

amellado,

Please create a new thread to post your question and provide more details, such as SQL Version, what you attempted to do when you got the error, etc. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin