Stored Procedure calling Access db..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure calling Access db..

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2005-11-28 : 11:33:14

We have a SQL Database that has a Linked Server which is a Microsoft Access Database. Apparently a stored procedure in SQL server used to work, but we had a fire and moved all our data to other servers and now it is not working. I get the linked server set up and running, but when we try to run the stored procedure:

CREATE PROCEDURE [rptBidDates2] AS
--Provides Bid Date information from linked access db, Projects
SELECT P.[Project Name], P.[Const Budget],
(SELECT Company_Short from project...companiesfp WHERE Company_ID = F.Company_ID) as Consultant,
F.Avertise AS Advertise, F.PreBid, right(convert(varchar(20),F.Time),7) as Time, F.Bid_Date
FROM Project...Project P LEFT JOIN Project...FP_Consult F ON P.PD = F.PD
--All dates in table where date is from yesterday to 45 days from now
WHERE f.Bid_Date > DATEADD(day, -1, getdate())
and f.Bid_Date < DATEADD(day, 45, getdate()) and F.PreBid is not null
ORDER BY F.Bid_Date
GO

We get this error:

[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: Data type mismatch in criteria expression.]

Not really sure where to tackle this. Checked the data in the access db and it was all dates or null values.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-28 : 12:01:18

Drop and recreate the linked server
Run the query with select count(*)
if you still get the error then start removing entries from the where clause untill you find out what the problem is.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2005-11-28 : 12:08:11

If I remove these lines:

f.Bid_Date > DATEADD("d", -1, getdate())

and

f.Bid_Date < DATEADD("d", 45, getdate())

It then works but it pulls more data then they want. They only want
all dates in table where date is from yesterday to 45 days from now. So it must be a formatting issue, but I don't understand why it stopped working. Any ideas, or what the format issue is?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-28 : 12:15:12

Use the profiler to see what is being sent.
I suspect a date format problem - which will b due to profiles of the users and the default settings for the machines.

You are probably sending an invalid date to access or receiving an invalid one back.
Try selecting the dates from the access database then using convert(datetime, f.Bid_Date, 101) or convert(datetime, f.Bid_Date, 103) depending on what it looks like.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2005-11-28 : 12:43:56

We select the data from the access database through a sql server stored procdedure. How would I do the convert?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-28 : 12:54:08

In the sp.
where convet(datetime, f.Bid_Date, 103) < DATEADD(dd, 45, getdate())

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2005-11-28 : 15:05:14

Tried this to test:

SELECT P.[Project Name], P.[Const Budget],
(SELECT Company_Short
FROM project...companiesfp
WHERE Company_ID = F.Company_ID) AS Consultant, F.Avertise AS Advertise, F.PreBid, RIGHT(CONVERT(varchar(20), F.[Time]), 7) AS [Time],
F.Bid_Date
FROM PROJECT...Project P LEFT OUTER JOIN
PROJECT...FP_Consult F ON P.PD = F.PD
WHERE (F.PreBid IS NOT NULL) AND (CONVERT(datetime, F.Bid_Date, 101) < DATEADD(dd, 45, GETDATE()))
ORDER BY F.Bid_Date

didn't work, so switched the 101 to 103 and tried that as well. Both failed and got the same error message I was getting before.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-11-28 : 15:29:52

Never hurts to try a different approach, often the discipline of rewritting it shows the obvious.

OPENQUERY

Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Syntax
OPENQUERY ( linked_server , 'query' )

That said I had a mother of a lock on some Access linked servers, I found it when I got back from vacation.
I didn't know what had been touched when I was gone so cause and effect debug was tough.
Profiles, Temp tables, all the other Known KB articles, (been there done that), I came in real late and rebooted.
That hurt my pride but it fixed it. This is without any known server change. Crazy huh!

Anyways. Another thing to check is Domain correctness. I fought with an Access linked server once in another Domain once and NEVER got it working.

"it's definitely useless and maybe harmful".

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2005-11-28 : 16:06:09

Can I run a sp off another sp?

I can get the data over if I don't put the Where clause in the Stored Procedure. Is there way to create a new SP that uses the previous SP and manipulate that data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources