You got it... My predesecor did it!
He was pulling data from the MDB in SSIS on a schedule, then, in his stored
procedures that the reports were running he was calling the SQL Server
database tables with the same name as the linked server. Esentially I am now
unable to find that he is ever using the linked server! It drove me nuts
because all of his other stored procedures that access local databases in SQL
Server do NOT use .. between the database and the table; he always does
database.dbo.table so he changed his way of doing things and in the process
sent me down a wild goose chase. I always wondered why he had one job that
pulled things in SSIS from the MDB and stored procs that were pulling from
the linked server, it made no sense.
Anyway, I commented his code to say what is going on and moved on. I may
test something later with that linked server to see if anyone is using it.
Thanks everyone for your assistance! Both of you were quite helpful!
Keith
[quoted text, click to view] "Andrew J. Kelly" wrote:
> My guess would be there is a database named PCCHARGE on that server and you
> are looking at the table on that SQL Server instance in that db and not the
> Access db thru the linked server. What happens when you actually fill in the
> names instead of using dots?
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "greenmtnsun" <greenmtnsun@discussions.microsoft.com> wrote in message
> news:1A90E243-B0E5-43F4-AE44-E3291A5933FB@microsoft.com...
> >I have a very strange situation. A select statement I am running isn't
> > returning the right number of records from a linked server.
> >
> > The linked server is to an Access 2000 Database using the Microsoft Jet
> > 4.0
> > OLE DB Provider driver. (4.0.9505.0)
> >
> > Here is the problem:
> >
> > SELECT * FROM PCCHARGE..TRANS -- This does not pull back todays data
> > judged
> > by looking at a column named "date".
> >
> > SELECT * FROM PCCHARGE...TRANS -- This pulls back todays data, it pulls
> > back
> > all of the data.
> >
> > What's the difference? the ... verses the ..
> >
> > The .. was used by my predecessor in our SP's. They worked without any
> > known problems until today. Now all of the sudden, they don't do so well.
> > I
> > came to find out if you right click on the table in server objects and ask
> > SSMS to generate the select statement it gives ... periods.
> >
> > So... Any idea what the problem is? What is the philosophy of periods
> > with
> > linked servers? How could they stop working?
> >
> > Thanks,
> > Keith
>
