AccessDataSource and stored querydefs

- i have been connecting to an mdb stored in the App_Data folder with AccessDataSource controls on .aspx pages.

- i was using stored queries in the mdb as the data sources without a problem. all of the queries were stored as views and were easy to find and configure to the AccessDataSource.

- i recently opened the mdb on its own to update and rename some queries and also add some new queries (knowing i would have to redo the data source configs, gridviews and dropdown lists, etc. on the .aspx page(s))

- but now a lot of the mdb queries are stored as functions and i can't use them as a data source. or can I?

- why are so many of my mdb querydefs now stored as functions, and how can i turn them back into views so i can easily connect to them, or am i missing something?

I'm an utter n00b, but I stumbled into the same thing, queryviews turning into functions, when I set something to RUN inside of access 'maketable query' or something to that effect. It shifted the qryMyquery from a view to a stored procedure, and created a new table instead.

Don't know if that'll help or not.

AccessDataSource and stored querydefs

- i have been connecting to an mdb stored in the App_Data folder with AccessDataSource controls on .aspx pages.

- i was using stored queries in the mdb as the data sources without a problem. all of the queries were stored as views and were easy to find and configure to the AccessDataSource.

- i recently opened the mdb on its own to update and rename some queries and also add some new queries (knowing i would have to redo the data source configs, gridviews and dropdown lists, etc. on the .aspx page(s))

- but now a lot of the mdb queries are stored as functions and i can't use them as a data source. or can I?

- why are so many of my mdb querydefs now stored as functions, and how can i turn them back into views so i can easily connect to them, or am i missing something?

I'm an utter n00b, but I stumbled into the same thing, queryviews turning into functions, when I set something to RUN inside of access 'maketable query' or something to that effect. It shifted the qryMyquery from a view to a stored procedure, and created a new table instead.

Don't know if that'll help or not.

Access Upsizing Wizard Size problem

I am using the Upsizing Wizard to create a copy of an Access database as a SQL Server database. The original .mdb is 14 mb. The resulting MSSQL database is 72 MB. Why the huge increase in size?I would be willing to bet that the database you are working with has a lot of fields with unspecified length. The wizard probably over-compensated by making all of the character fields nvarchar(4000) or something silly like that. Peruse the datatypes in your tables, and think to yourself if you really need that much room in any of the fields.|||Good idea, but that was not it. The fields are all the size they should be.|||How are you measuring the size? Is this the size of the data portion, the log portion, or the sum of both?|||Right-clicking on the database in Enterprise Manager, and choosing Properties.

Access to SQL Server Query Translation

Hi,

I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
application with SQL Server as Backend.

I'm having trouble converting Access Query into SQL Query. The Query is
given below:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
SELECT DISTINCTROW Buildings.BuildingNumber,
First(Buildings.BuildingName) AS FirstOfBuildingName,
First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
First(CityCodes.CityName) AS FirstOfCityName,
First(CountyCodes.CountyName) AS FirstOfCountyName,
First(Buildings.Address) AS FirstOfAddress,
First(Buildings.YearConstructed) AS FirstOfYearConstructed,
First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
First(Buildings.NumberLevels) AS FirstOfNumberLevels,
First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
First(Buildings.SpecialArea) AS FirstOfSpecialArea,
First(Buildings.CoveredUnenclosedGrossArea) AS
FirstOfCoveredUnenclosedGrossArea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
Buildings.OwnershipCode) ON CountyCodes.CountyCode =
CityCodes.CountyCode
GROUP BY Buildings.BuildingNumber;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~

Please can any one tell me substitue for First Function in Acess to SQL
Function.

Any help is appreciated.
Thanks,
S(s_wadhwa@.berkeley.edu) writes:

Quote:

Originally Posted by

I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
application with SQL Server as Backend.
>
I'm having trouble converting Access Query into SQL Query. The Query is
given below:
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
SELECT DISTINCTROW Buildings.BuildingNumber,
First(Buildings.BuildingName) AS FirstOfBuildingName,
First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
First(CityCodes.CityName) AS FirstOfCityName,
First(CountyCodes.CountyName) AS FirstOfCountyName,
First(Buildings.Address) AS FirstOfAddress,
First(Buildings.YearConstructed) AS FirstOfYearConstructed,
First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
First(Buildings.NumberLevels) AS FirstOfNumberLevels,
First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
First(Buildings.SpecialArea) AS FirstOfSpecialArea,
First(Buildings.CoveredUnenclosedGrossArea) AS
FirstOfCoveredUnenclosedGrossArea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
Buildings.OwnershipCode) ON CountyCodes.CountyCode =
CityCodes.CountyCode
GROUP BY Buildings.BuildingNumber;
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>
Please can any one tell me substitue for First Function in Acess to SQL
Function.

I don't know Access, but if I have understood it correctcly, First
returns the value for the "first" row in the group. What I don't know
if you are guaranteed that all these "first" will return data from the
same row from Buildings, or if they could be from different rows.

You see, in a relational database "first" is a not meaningful operation.
A table is a set of unordered tuples, and there is no first or last.

It could help if you posted the CREATE TABLE statements for the table,
including definitions of primary keys and foreign keys. It's also a good
idea to add a short description of what the query is supposed to achieve.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To add on to Erland's response, FIRST is not relational so there is no
direct SQL Server equivalent. I have seen FIRST most often used in Access
queries to mask problems with data or query formulation rather than to
address a real requirement.

It looks to me like the purpose of this query is to calculate the total
assignable square feet by building and include additional information
related to the building. In that case, you might try something like the
example below, which assumes the primary key and foreign key relationships
are on the joined columns:

SELECT
Buildings.BuildingNumber,
Buildings.BuildingName,
First(OwnershipCodes.OwnershipCode,
OwnershipCodes.OwnershipDesc,
CityCodes.CityName,
CountyCodes.CountyName,
Buildings.Address,
Buildings.YearConstructed,
Buildings.DateOccupancy,
Buildings.NumberLevels,
Buildings.BasicGrossArea,
(SELECT SUM(Rooms.AssignableSquareFeet)
FROM Rooms
WHERE Buildings.BuildingNumber = Rooms.BuildingNumber
) AS SumOfAssignableSquareFeet,
Buildings.UnrelatedGrossArea,
Buildings.SpecialArea,
Buildings.CoveredUnenclosedGrossArea
FROM Buildings
INNER JOIN CountyCodes
ON CityCodes.CityCode = Buildings.CityCode
INNER JOIN OwnershipCodes
ON OwnershipCodes.OwnershipCode = Buildings.OwnershipCode
INNER JOIN ConditionCodes
ON ConditionCodes.ConditionCode = Buildings.ConditionCode
INNER JOIN CityCodes
ON CountyCodes.CountyCode = CityCodes.CountyCode;

--
Hope this helps.

Dan Guzman
SQL Server MVP

<s_wadhwa@.berkeley.eduwrote in message
news:1155837812.840480.6380@.m73g2000cwd.googlegrou ps.com...

Quote:

Originally Posted by

Hi,
>
I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
application with SQL Server as Backend.
>
I'm having trouble converting Access Query into SQL Query. The Query is
given below:
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
SELECT DISTINCTROW Buildings.BuildingNumber,
First(Buildings.BuildingName) AS FirstOfBuildingName,
First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
First(CityCodes.CityName) AS FirstOfCityName,
First(CountyCodes.CountyName) AS FirstOfCountyName,
First(Buildings.Address) AS FirstOfAddress,
First(Buildings.YearConstructed) AS FirstOfYearConstructed,
First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
First(Buildings.NumberLevels) AS FirstOfNumberLevels,
First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
First(Buildings.SpecialArea) AS FirstOfSpecialArea,
First(Buildings.CoveredUnenclosedGrossArea) AS
FirstOfCoveredUnenclosedGrossArea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
Buildings.OwnershipCode) ON CountyCodes.CountyCode =
CityCodes.CountyCode
GROUP BY Buildings.BuildingNumber;
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>
Please can any one tell me substitue for First Function in Acess to SQL
Function.
>
Any help is appreciated.
Thanks,
S
>

|||

Quote:

Originally Posted by

First(OwnershipCodes.OwnershipCode,

Oops, missed one. Should be:

OwnershipCodes.OwnershipCode,

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:pCZFg.1509$yO7.177@.newssvr14.news.prodigy.com ...

Quote:

Originally Posted by

To add on to Erland's response, FIRST is not relational so there is no
direct SQL Server equivalent. I have seen FIRST most often used in Access
queries to mask problems with data or query formulation rather than to
address a real requirement.
>
It looks to me like the purpose of this query is to calculate the total
assignable square feet by building and include additional information
related to the building. In that case, you might try something like the
example below, which assumes the primary key and foreign key relationships
are on the joined columns:
>
SELECT
Buildings.BuildingNumber,
Buildings.BuildingName,
First(OwnershipCodes.OwnershipCode,
OwnershipCodes.OwnershipDesc,
CityCodes.CityName,
CountyCodes.CountyName,
Buildings.Address,
Buildings.YearConstructed,
Buildings.DateOccupancy,
Buildings.NumberLevels,
Buildings.BasicGrossArea,
(SELECT SUM(Rooms.AssignableSquareFeet)
FROM Rooms
WHERE Buildings.BuildingNumber = Rooms.BuildingNumber
) AS SumOfAssignableSquareFeet,
Buildings.UnrelatedGrossArea,
Buildings.SpecialArea,
Buildings.CoveredUnenclosedGrossArea
FROM Buildings
INNER JOIN CountyCodes
ON CityCodes.CityCode = Buildings.CityCode
INNER JOIN OwnershipCodes
ON OwnershipCodes.OwnershipCode = Buildings.OwnershipCode
INNER JOIN ConditionCodes
ON ConditionCodes.ConditionCode = Buildings.ConditionCode
INNER JOIN CityCodes
ON CountyCodes.CountyCode = CityCodes.CountyCode;
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
<s_wadhwa@.berkeley.eduwrote in message
news:1155837812.840480.6380@.m73g2000cwd.googlegrou ps.com...

Quote:

Originally Posted by

>Hi,
>>
>I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
>application with SQL Server as Backend.
>>
>I'm having trouble converting Access Query into SQL Query. The Query is
>given below:
>>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>SELECT DISTINCTROW Buildings.BuildingNumber,
>First(Buildings.BuildingName) AS FirstOfBuildingName,
>First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
>First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
>First(CityCodes.CityName) AS FirstOfCityName,
>First(CountyCodes.CountyName) AS FirstOfCountyName,
>First(Buildings.Address) AS FirstOfAddress,
>First(Buildings.YearConstructed) AS FirstOfYearConstructed,
>First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
>First(Buildings.NumberLevels) AS FirstOfNumberLevels,
>First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
>Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
>First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
>First(Buildings.SpecialArea) AS FirstOfSpecialArea,
>First(Buildings.CoveredUnenclosedGrossArea) AS
>FirstOfCoveredUnenclosedGrossArea
>FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
>INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
>Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
>Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
>Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
>Buildings.OwnershipCode) ON CountyCodes.CountyCode =
>CityCodes.CountyCode
>GROUP BY Buildings.BuildingNumber;
>>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>>
>Please can any one tell me substitue for First Function in Acess to SQL
>Function.
>>
>Any help is appreciated.
>Thanks,
>S
>>

>
>

Access to Sql server : file Size

I have migrated an Access Database to SQL server.

The original Access MDB file was around 50Mo.

After migration, I get 2 files in SQL directories :
xxx_data.mdf of around 100 Mo
and xxx_log.ldf of around 130 Mo

So the needed space is more than 4 times more than used under Access.

Is this normal, or is there something Wrong somewhere ?

Thanks for any Infos,
Pierre.Firstly the "data" component for your database is typically only stored in the xxx_data.mdf file, so it looks like it is around twice the size of your access db. You should back up your database, truncate and shirnk your transaction log.. see BOL for more info on transaction logs. This will control the size of your xxx_log.ldf file.

Next you can check how much of the 100Mb your data is actually consuming by running a a command like sp_spaceused. I'm not sure if Access has the concept of "devices" but I don't believe that it does. Have a read of this in BOL and maybe that will give you a hand..

Hope this helps.

Originally posted by Plarde
I have migrated an Access Database to SQL server.

The original Access MDB file was around 50Mo.

After migration, I get 2 files in SQL directories :
xxx_data.mdf of around 100 Mo
and xxx_log.ldf of around 130 Mo

So the needed space is more than 4 times more than used under Access.

Is this normal, or is there something Wrong somewhere ?

Thanks for any Infos,
Pierre.|||As specified make sure you didn't hit any errors during this upsizing from Access. And also assess the size of the files and schedule correct intervals of Tlog and database backups to avoid any out of space errors.

Occassionally refer to SQL error log for any information.|||Thanks for your advise.
But I am completly new with SQL server, and I do not see any tools
in SQL Manager to shrink/compact the database.

Where are these tools ?

Even in the BOL, I do not see anything talking about reducing space.

Thanks for any help.|||What version of SQL Server are you running?

Originally posted by Plarde
Thanks for your advise.
But I am completly new with SQL server, and I do not see any tools
in SQL Manager to shrink/compact the database.

Where are these tools ?

Even in the BOL, I do not see anything talking about reducing space.

Thanks for any help.|||True, you didn't mentioned version of SQL server used and make sure your login has SYSADMIN privileges to carry on those tasks.

Refer to BOL for SP_ATTACH_DB & SP_DETACH_DB topics which involves attaching/detaching process.

And make sure you have client tools installed on your machine to talk to the SQL server which has been migrated.

Access to SQL

Does anyone know how to import an Access db to SQL.

I need to do this programmatically.

The user selects the path to the Access.mdb clicks a button and the Access data and schema are imported to a SQL db.

I can do SQL to SQL with DTS.TransferObjectsTask2 and it works perfectly but I need to be able to do Access to SQL.

Thanks

You can do it in DTS package or SSIS package just select Access database in dts and ODBC data source in SSIS package. You can also use .Net and connect to Access using ODBC set of database objects and next save results into SQL using SQL objects you can use Dataset to store your tables or just read from ODBC and save into SQL

Thanks

Access tables transferred to SQL Server are set to "read only." How do I correct/

I created an Access project and transferred some tables from my .mdb Access
database into an existing SQL Server database. Now, the tables are "read
only" meaning that I can't add/modify/delete data in the tables. But, when
I open the same SQL Server database, from the same desktop, using EM I can
add/modify/delete data in the SAME table.
Where should I look?
TIA,
Larry WoodsCreate a primary key/unique index on the tables in question. Access
refuses to update SQLS tables otherwise.
--Mary
On Wed, 28 Jul 2004 07:18:35 -0700, "Larry Woods"
<larry@.NOSPAMlwoods.com> wrote:
>I created an Access project and transferred some tables from my .mdb Access
>database into an existing SQL Server database. Now, the tables are "read
>only" meaning that I can't add/modify/delete data in the tables. But, when
>I open the same SQL Server database, from the same desktop, using EM I can
>add/modify/delete data in the SAME table.
>Where should I look?
>TIA,
>Larry Woods
>

Access tables transferred to SQL Server are set to "read only." How do I correc

I created an Access project and transferred some tables from my .mdb Access
database into an existing SQL Server database. Now, the tables are "read
only" meaning that I can't add/modify/delete data in the tables. But, when
I open the same SQL Server database, from the same desktop, using EM I can
add/modify/delete data in the SAME table.
Where should I look?
TIA,
Larry Woods
Create a primary key/unique index on the tables in question. Access
refuses to update SQLS tables otherwise.
--Mary
On Wed, 28 Jul 2004 07:18:35 -0700, "Larry Woods"
<larry@.NOSPAMlwoods.com> wrote:

>I created an Access project and transferred some tables from my .mdb Access
>database into an existing SQL Server database. Now, the tables are "read
>only" meaning that I can't add/modify/delete data in the tables. But, when
>I open the same SQL Server database, from the same desktop, using EM I can
>add/modify/delete data in the SAME table.
>Where should I look?
>TIA,
>Larry Woods
>

Access tables transferred to SQL Server are set to "read only." How do I co

I created an Access project and transferred some tables from my .mdb Access
database into an existing SQL Server database. Now, the tables are "read
only" meaning that I can't add/modify/delete data in the tables. But, when
I open the same SQL Server database, from the same desktop, using EM I can
add/modify/delete data in the SAME table.
Where should I look?
TIA,
Larry WoodsCreate a primary key/unique index on the tables in question. Access
refuses to update SQLS tables otherwise.
--Mary
On Wed, 28 Jul 2004 07:18:35 -0700, "Larry Woods"
<larry@.NOSPAMlwoods.com> wrote:

>I created an Access project and transferred some tables from my .mdb Access
>database into an existing SQL Server database. Now, the tables are "read
>only" meaning that I can't add/modify/delete data in the tables. But, when
>I open the same SQL Server database, from the same desktop, using EM I can
>add/modify/delete data in the SAME table.
>Where should I look?
>TIA,
>Larry Woods
>

Access mdb link tables

I need help to link my MSDE 2000 database with access 2000 *.mdb. When I try to do this and then I try to open a table, I can't see my data but just something like "#delete".
Thank you for helpYou may wish to include more information about your environment and the workstation on which you are seeing the issue. For example:

Were you previously able to link from the workstation without seeing the issue (if so, describe what has been done since then)? Are you able to do so from any other workstations using Access (if so, describe them) or is this a universal issue? What Access / MS Office, and MDAC service packs are currently installed?

{It may or may not be the case that applying the current MDAC and Office service packs, and subsequently relinking, (delete old links, recreate new ones) will address the issue on the workstation.}|||I just find the solution, just have to use int datatype against bigint, in SQL Server 2000. After this change everything is ok.

Thank you

Originally posted by DBA
You may wish to include more information about your environment and the workstation on which you are seeing the issue. For example:

Were you previously able to link from the workstation without seeing the issue (if so, describe what has been done since then)? Are you able to do so from any other workstations using Access (if so, describe them) or is this a universal issue? What Access / MS Office, and MDAC service packs are currently installed?

{It may or may not be the case that applying the current MDAC and Office service packs, and subsequently relinking, (delete old links, recreate new ones) will address the issue on the workstation.}|||You are welcome.

Another Sql Server data type mapping to be cautious with in some versions of client software is bit. If a bit column is null in some rows, some front end applications do not always handle the situation gracefully (depending on what type the Sql Server bit column is mapped to in the front end app).

Access Linked Server Breaks

Hi,
1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
couple of weeks. If I do a SELECT statement to the Linked server in Query
Analyzer,, even on a small table of 3 records, QA seems to be working, and
no error messages come up. However, the SELECT command never completes.
2. This problem can always be cured by a reboot of server, but then it
happens again a few days or a few weeks later.
3. This exact same setup worked on another server for 1 3/4 years, but the
problem is on a newly set up SQLServer box.
4. Has anyone experienced such symptoms and found a resolution for sporadic
Linked Server to mdb failures?
5. When a Linked Server connects to an Access mdb, is this being done via an
IP connnection?
I believe a couple of years ago I had viewed the linked server process in
motion, showing among other things some IP connections, but I don't remember
how I did that.
Thanks for any help.
AlanI can't put my finger on it, but this feels like a SQL Server "mem-to-leave"
issue (the portion of SQL Server process memory that is not the buffer
pool.). Linked server info is stored there. Don't know how to help other
than suggesting running DBCC FREEPROCCACHE instead of restarting sql server
(or do you have to reboot the whole box?)
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
> couple of weeks. If I do a SELECT statement to the Linked server in Query
> Analyzer,, even on a small table of 3 records, QA seems to be working, and
> no error messages come up. However, the SELECT command never completes.
> 2. This problem can always be cured by a reboot of server, but then it
> happens again a few days or a few weeks later.
> 3. This exact same setup worked on another server for 1 3/4 years, but the
> problem is on a newly set up SQLServer box.
> 4. Has anyone experienced such symptoms and found a resolution for
> sporadic Linked Server to mdb failures?
> 5. When a Linked Server connects to an Access mdb, is this being done via
> an IP connnection?
> I believe a couple of years ago I had viewed the linked server process in
> motion, showing among other things some IP connections, but I don't
> remember how I did that.
> Thanks for any help.
> Alan
>|||Kevin,
Thanks very much for your reply.
I will try that next time Linked server breaks.
Whole box has had to be rebooted to cure this.
Regards,
Alan
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>I can't put my finger on it, but this feels like a SQL Server
>"mem-to-leave" issue (the portion of SQL Server process memory that is not
>the buffer pool.). Linked server info is stored there. Don't know how to
>help other than suggesting running DBCC FREEPROCCACHE instead of restarting
>sql server (or do you have to reboot the whole box?)
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
>|||You may also want to test SP4...lots of memory leaks were fixed between 3
and 4
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:e1YnGx8PHHA.4924@.TK2MSFTNGP05.phx.gbl...
> Kevin,
> Thanks very much for your reply.
> I will try that next time Linked server breaks.
> Whole box has had to be rebooted to cure this.
> Regards,
> Alan
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>

Access Linked Server Breaks

Hi,
1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
couple of weeks. If I do a SELECT statement to the Linked server in Query
Analyzer,, even on a small table of 3 records, QA seems to be working, and
no error messages come up. However, the SELECT command never completes.
2. This problem can always be cured by a reboot of server, but then it
happens again a few days or a few weeks later.
3. This exact same setup worked on another server for 1 3/4 years, but the
problem is on a newly set up SQLServer box.
4. Has anyone experienced such symptoms and found a resolution for sporadic
Linked Server to mdb failures?
5. When a Linked Server connects to an Access mdb, is this being done via an
IP connnection?
I believe a couple of years ago I had viewed the linked server process in
motion, showing among other things some IP connections, but I don't remember
how I did that.
Thanks for any help.
Alan
I can't put my finger on it, but this feels like a SQL Server "mem-to-leave"
issue (the portion of SQL Server process memory that is not the buffer
pool.). Linked server info is stored there. Don't know how to help other
than suggesting running DBCC FREEPROCCACHE instead of restarting sql server
(or do you have to reboot the whole box?)
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
> couple of weeks. If I do a SELECT statement to the Linked server in Query
> Analyzer,, even on a small table of 3 records, QA seems to be working, and
> no error messages come up. However, the SELECT command never completes.
> 2. This problem can always be cured by a reboot of server, but then it
> happens again a few days or a few weeks later.
> 3. This exact same setup worked on another server for 1 3/4 years, but the
> problem is on a newly set up SQLServer box.
> 4. Has anyone experienced such symptoms and found a resolution for
> sporadic Linked Server to mdb failures?
> 5. When a Linked Server connects to an Access mdb, is this being done via
> an IP connnection?
> I believe a couple of years ago I had viewed the linked server process in
> motion, showing among other things some IP connections, but I don't
> remember how I did that.
> Thanks for any help.
> Alan
>
|||Kevin,
Thanks very much for your reply.
I will try that next time Linked server breaks.
Whole box has had to be rebooted to cure this.
Regards,
Alan
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>I can't put my finger on it, but this feels like a SQL Server
>"mem-to-leave" issue (the portion of SQL Server process memory that is not
>the buffer pool.). Linked server info is stored there. Don't know how to
>help other than suggesting running DBCC FREEPROCCACHE instead of restarting
>sql server (or do you have to reboot the whole box?)
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
>
|||You may also want to test SP4...lots of memory leaks were fixed between 3
and 4
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:e1YnGx8PHHA.4924@.TK2MSFTNGP05.phx.gbl...
> Kevin,
> Thanks very much for your reply.
> I will try that next time Linked server breaks.
> Whole box has had to be rebooted to cure this.
> Regards,
> Alan
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>