Accessing Active Directory using SQL Server 2000

Hello All,

I have a linked ADSI Server to our company Active Directory and
everything is fine. I'm running queries and such using LDAP. BUT how
can I aquire a list of attributes for the classes and categories. Such
as if i wanted to see a user's telephone number and email address for
use in a corporate database? Is this possible?

ThanksHi

I have never tried this.. but...:

The example of ADSI in books online returns the SN and ST attributes of the
contact class. See Books Online:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_12_94fn.htm

You should then be able to use the same OPENQUERY as the destination of the
update statement. See Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_oa-oz_5xix.htm

John

"Brandon Arnold" <btugg@.yahoo.com> wrote in message
news:8508d156.0312301444.b04f4c8@.posting.google.co m...
> Hello All,
> I have a linked ADSI Server to our company Active Directory and
> everything is fine. I'm running queries and such using LDAP. BUT how
> can I aquire a list of attributes for the classes and categories. Such
> as if i wanted to see a user's telephone number and email address for
> use in a corporate database? Is this possible?
> Thanks|||Thanks John but I'm looking a little deeper than that.

While searching yesterday I ran across another attribute that is
usually shown
in sample LDAP queries and it was "sAMAccountNAme" which is the
account a user may log on with. I'm for a list of attributes for
Computers, People, etc in Active Directory. Is there such a list or
definition table?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<ibyIb.2549$le.23310381@.news-text.cableinet.net>...
> Hi
> I have never tried this.. but...:
> The example of ADSI in books online returns the SN and ST attributes of the
> contact class. See Books Online:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
> ata.chm::/ac_8_qd_12_94fn.htm
> You should then be able to use the same OPENQUERY as the destination of the
> update statement. See Books Online:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
> lref.chm::/ts_oa-oz_5xix.htm
> John
> "Brandon Arnold" <btugg@.yahoo.com> wrote in message
> news:8508d156.0312301444.b04f4c8@.posting.google.co m...
> > Hello All,
> > I have a linked ADSI Server to our company Active Directory and
> > everything is fine. I'm running queries and such using LDAP. BUT how
> > can I aquire a list of attributes for the classes and categories. Such
> > as if i wanted to see a user's telephone number and email address for
> > use in a corporate database? Is this possible?
> > Thanks|||As in ?:
http://msdn.microsoft.com/library/d...ace_mapping.asp

John

"Brandon Arnold" <btugg@.yahoo.com> wrote in message
news:8508d156.0312310700.647abcfb@.posting.google.c om...
> Thanks John but I'm looking a little deeper than that.
> While searching yesterday I ran across another attribute that is
> usually shown
> in sample LDAP queries and it was "sAMAccountNAme" which is the
> account a user may log on with. I'm for a list of attributes for
> Computers, People, etc in Active Directory. Is there such a list or
> definition table?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<ibyIb.2549$le.23310381@.news-text.cableinet.net>...
> > Hi
> > I have never tried this.. but...:
> > The example of ADSI in books online returns the SN and ST attributes of
the
> > contact class. See Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
> > ata.chm::/ac_8_qd_12_94fn.htm
> > You should then be able to use the same OPENQUERY as the destination of
the
> > update statement. See Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
> > lref.chm::/ts_oa-oz_5xix.htm
> > John
> > "Brandon Arnold" <btugg@.yahoo.com> wrote in message
> > news:8508d156.0312301444.b04f4c8@.posting.google.co m...
> > > Hello All,
> > > > I have a linked ADSI Server to our company Active Directory and
> > > everything is fine. I'm running queries and such using LDAP. BUT how
> > > can I aquire a list of attributes for the classes and categories. Such
> > > as if i wanted to see a user's telephone number and email address for
> > > use in a corporate database? Is this possible?
> > > > Thanks|||Yes! Thanks John!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<L_TIb.3630$5X.31045680@.news-text.cableinet.net>...
> As in ?:
> http://msdn.microsoft.com/library/d...ace_mapping.asp
> John
> "Brandon Arnold" <btugg@.yahoo.com> wrote in message
> news:8508d156.0312310700.647abcfb@.posting.google.c om...
> > Thanks John but I'm looking a little deeper than that.
> > While searching yesterday I ran across another attribute that is
> > usually shown
> > in sample LDAP queries and it was "sAMAccountNAme" which is the
> > account a user may log on with. I'm for a list of attributes for
> > Computers, People, etc in Active Directory. Is there such a list or
> > definition table?
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:<ibyIb.2549$le.23310381@.news-text.cableinet.net>...
> > > Hi
> > > > I have never tried this.. but...:
> > > > The example of ADSI in books online returns the SN and ST attributes of
> the
> > > contact class. See Books Online:
> > > mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
> > > ata.chm::/ac_8_qd_12_94fn.htm
> > > > You should then be able to use the same OPENQUERY as the destination of
> the
> > > update statement. See Books Online:
> > mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
> > > lref.chm::/ts_oa-oz_5xix.htm
> > > > John
> > > > "Brandon Arnold" <btugg@.yahoo.com> wrote in message
> > > news:8508d156.0312301444.b04f4c8@.posting.google.co m...
> > > > Hello All,
> > > > > > I have a linked ADSI Server to our company Active Directory and
> > > > everything is fine. I'm running queries and such using LDAP. BUT how
> > > > can I aquire a list of attributes for the classes and categories. Such
> > > > as if i wanted to see a user's telephone number and email address for
> > > > use in a corporate database? Is this possible?
> > > > > > Thanks

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 to SQL migration

I am trying to migrate a set of MS Access queries to SQL that call Access VB
A
code. For example, this is typical:
SELECT Name, DOB, VeryComplexFunction([Value]) AS NumericResult
FROM Table
where,
VeryComplexFunction is a function that is in an Access VBA code
module
All the Access query/VBA works of course, but now I need to migrate to SQL.
I be very grateful for for any help or pointers that anyone can offer.
Thanks in advance."A Shasore" <AShasore@.discussions.microsoft.com> wrote in message
news:8B40B5D3-D11F-437D-B1BA-BEBA142D4C4F@.microsoft.com...
> I am trying to migrate a set of MS Access queries to SQL that call Access
VBA
> code. For example, this is typical:
> SELECT Name, DOB, VeryComplexFunction([Value]) AS NumericResult
> FROM Table
> where,
> VeryComplexFunction is a function that is in an Access VBA code
> module
> All the Access query/VBA works of course, but now I need to migrate to
SQL.
> I be very grateful for for any help or pointers that anyone can offer.
> Thanks in advance.
>
I had a similar problem when I was tasked to upgrade my companys system from
Access to MSSQLS.
What you're looking at is recreating the VBA function as an SQL Server User
Defined Function (UDF) - which is going to require a knowledge of T-SQL.
For upgrading in general I can heartily recomend "Microsoft Access
Developer's Guide to SQL Server" by Chipman & Baron (0-672-31944-6).
Sorry this is a bit vague but I hope it helps.
Thanks
Chris.|||An in-line expression and then a user-defined function should be the
first things to try. Other options are to use an Extended SP or do the
calc in your middle tier.
Don't assume that the way it is done in VB is the best way in SQL. For
example, calculations involving many inputs could possibly be
simplified with a more set-based approach using joins and aggregates.
David Portas
SQL Server MVP
--

Access SQL to Transact SQL - Translator?

Friends,
I am new to SQL Server, and I access it mainly through
Access (ODBC DSN).
I need my queries to run faster, so I want to switch from
regular Access queries against linked SQL tables to faster
pass-through queries written in Transact SQL.
QUESTION: Does anyone know of an easy way to translate
Access SQL into Transact SQL - in other words, a way to
copy the Access SQL from SQL View, and paste it into a
translator?
Thanks in advance ...
"bill morgan" <willmorgan@.lisco.com> wrote in message
news:3f3d01c4a5bc$4dbaf7f0$a301280a@.phx.gbl...
> I am new to SQL Server, and I access it mainly through
> Access (ODBC DSN).
> I need my queries to run faster, so I want to switch from
> regular Access queries against linked SQL tables to faster
> pass-through queries written in Transact SQL.
If you want performance you may want to consider using SQL Server VIEWS and
Stored Procedures.

> QUESTION: Does anyone know of an easy way to translate
> Access SQL into Transact SQL - in other words, a way to
> copy the Access SQL from SQL View, and paste it into a
> translator?
There is no "easy" way to do this since the SQL syntax is different between
these 2 products.
On suggestion though, you could try running SQL Profiler and capture the SQL
Server SQL generated by an Access query. This you might be able to translate
into an SQL Server View or Stored Procedure.
Steve
|||Steve,
Thanks for your response. Actually, I have been capturing
the Access SQL, and generally it is simple to translate it
into Transact SQL. It gets sticky, though, when trying to
translate computed fields. For example, SQL Server does
not accept IIF() functions, so I have to come up with
alternative syntax and structure.
Thanks again ...[vbcol=seagreen]
>--Original Message--
>"bill morgan" <willmorgan@.lisco.com> wrote in message
>news:3f3d01c4a5bc$4dbaf7f0$a301280a@.phx.gbl...
from[vbcol=seagreen]
faster
>If you want performance you may want to consider using
SQL Server VIEWS and
>Stored Procedures.
>
>There is no "easy" way to do this since the SQL syntax is
different between
>these 2 products.
>On suggestion though, you could try running SQL Profiler
and capture the SQL
>Server SQL generated by an Access query. This you might
be able to translate
>into an SQL Server View or Stored Procedure.
>Steve
>
>.
>

Access queries using up space on server's C: drive

I have users who are running large SELECT type queries in Access against
a SQL Server via ODBC. What happens is that the c:\documents and
settings\<username>\Local Settings\Temp on the SQL server itself gets filled
until the drive starts running low on space. Other than to free up space on
the server's C: drive, are there any good suggestions to redirecting where
Access stores the temp files?

*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************
yeah
use access data projects instead and keep all your temp tables on the
db server.. where they belong (and have a good io system on the server)
|||Who's profile "<username>" ? And \temp dir gets filled up with what?
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| I have users who are running large SELECT type queries in Access
against
| a SQL Server via ODBC. What happens is that the c:\documents and
| settings\<username>\Local Settings\Temp on the SQL server itself gets
filled
| until the drive starts running low on space. Other than to free up space
on
| the server's C: drive, are there any good suggestions to redirecting where
| Access stores the temp files?
|
|
| --
|
| *************************************************
| Andy S.
| andymcdba1@.noreply.yahoo.com
|
| Please remove "noreply" before replying.
|
| *************************************************
|
|
|||Unfortunately, the end users are creating the queries. I wish I could have
coded everything myself, but that isn't an option. I'm thinking of even
trying to redirect the ODBC log directory setting, but I was hoping there
might be other ideas.
I'd actually rather keep all the temp files locally on the clients where
there is plenty of room.
<dbahooker@.hotmail.com> wrote in message
news:1128027434.706417.58830@.g49g2000cwa.googlegro ups.com...
> yeah
> use access data projects instead and keep all your temp tables on the
> db server.. where they belong (and have a good io system on the server)
>
|||end users CAN create queries in Access Data Projects on the SQL Server
side.. just as easily as on the Access side almost
stored procs are a lot more fun that mdb
|||username would be the end user's profile. I'm not sure of the file
extension, but it is a single large temp file that can grow several hundred
MB in size. I think it is from the ODBC setting for saving long running
queries to a particular folder. I'm not sure why it wouldn't be on the
workstation vs. the server.
"Dave Patrick" <DSPatrick@.nOsPAM.gmail.com> wrote in message
news:ermq9mTxFHA.3740@.tk2msftngp13.phx.gbl...
> Who's profile "<username>" ? And \temp dir gets filled up with what?
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Andy S." wrote:
> | I have users who are running large SELECT type queries in Access
> against
> | a SQL Server via ODBC. What happens is that the c:\documents and
> | settings\<username>\Local Settings\Temp on the SQL server itself gets
> filled
> | until the drive starts running low on space. Other than to free up
> space
> on
> | the server's C: drive, are there any good suggestions to redirecting
> where
> | Access stores the temp files?
> |
> |
> | --
> |
> | *************************************************
> | Andy S.
> | andymcdba1@.noreply.yahoo.com
> |
> | Please remove "noreply" before replying.
> |
> | *************************************************
> |
> |
>
|||How did the user profile folders on the SQL server come about? Do they
terminal service into the box? Some details of the files and or file types
may help. I've been running this scenario for a long time and never seen
this before.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| username would be the end user's profile. I'm not sure of the file
| extension, but it is a single large temp file that can grow several
hundred
| MB in size. I think it is from the ODBC setting for saving long running
| queries to a particular folder. I'm not sure why it wouldn't be on the
| workstation vs. the server.
|||why would you ever make a temp file that is hundreds of mb in size?
use some views man

Access queries using up space on server's C: drive

I have users who are running large SELECT type queries in Access against
a SQL Server via ODBC. What happens is that the c:\documents and
settings\<username>\Local Settings\Temp on the SQL server itself gets filled
until the drive starts running low on space. Other than to free up space on
the server's C: drive, are there any good suggestions to redirecting where
Access stores the temp files?
****************************************
*********
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
****************************************
*********yeah
use access data projects instead and keep all your temp tables on the
db server.. where they belong (and have a good io system on the server)|||Who's profile "<username>" ? And \temp dir gets filled up with what?
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| I have users who are running large SELECT type queries in Access
against
| a SQL Server via ODBC. What happens is that the c:\documents and
| settings\<username>\Local Settings\Temp on the SQL server itself gets
filled
| until the drive starts running low on space. Other than to free up space
on
| the server's C: drive, are there any good suggestions to redirecting where
| Access stores the temp files?
|
|
| --
|
| ****************************************
*********
| Andy S.
| andymcdba1@.noreply.yahoo.com
|
| Please remove "noreply" before replying.
|
| ****************************************
*********
|
||||Unfortunately, the end users are creating the queries. I wish I could have
coded everything myself, but that isn't an option. I'm thinking of even
trying to redirect the ODBC log directory setting, but I was hoping there
might be other ideas.
I'd actually rather keep all the temp files locally on the clients where
there is plenty of room.
<dbahooker@.hotmail.com> wrote in message
news:1128027434.706417.58830@.g49g2000cwa.googlegroups.com...
> yeah
> use access data projects instead and keep all your temp tables on the
> db server.. where they belong (and have a good io system on the server)
>|||end users CAN create queries in Access Data Projects on the SQL Server
side.. just as easily as on the Access side almost
stored procs are a lot more fun that mdb|||username would be the end user's profile. I'm not sure of the file
extension, but it is a single large temp file that can grow several hundred
MB in size. I think it is from the ODBC setting for saving long running
queries to a particular folder. I'm not sure why it wouldn't be on the
workstation vs. the server.
"Dave Patrick" <DSPatrick@.nOsPAM.gmail.com> wrote in message
news:ermq9mTxFHA.3740@.tk2msftngp13.phx.gbl...
> Who's profile "<username>" ? And \temp dir gets filled up with what?
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Andy S." wrote:
> | I have users who are running large SELECT type queries in Access
> against
> | a SQL Server via ODBC. What happens is that the c:\documents and
> | settings\<username>\Local Settings\Temp on the SQL server itself gets
> filled
> | until the drive starts running low on space. Other than to free up
> space
> on
> | the server's C: drive, are there any good suggestions to redirecting
> where
> | Access stores the temp files?
> |
> |
> | --
> |
> | ****************************************
*********
> | Andy S.
> | andymcdba1@.noreply.yahoo.com
> |
> | Please remove "noreply" before replying.
> |
> | ****************************************
*********
> |
> |
>|||How did the user profile folders on the SQL server come about? Do they
terminal service into the box? Some details of the files and or file types
may help. I've been running this scenario for a long time and never seen
this before.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| username would be the end user's profile. I'm not sure of the file
| extension, but it is a single large temp file that can grow several
hundred
| MB in size. I think it is from the ODBC setting for saving long running
| queries to a particular folder. I'm not sure why it wouldn't be on the
| workstation vs. the server.|||why would you ever make a temp file that is hundreds of mb in size?
use some views man

Access queries using up space on server's C: drive

I have users who are running large SELECT type queries in Access against
a SQL Server via ODBC. What happens is that the c:\documents and
settings\<username>\Local Settings\Temp on the SQL server itself gets filled
until the drive starts running low on space. Other than to free up space on
the server's C: drive, are there any good suggestions to redirecting where
Access stores the temp files?

*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************
yeah
use access data projects instead and keep all your temp tables on the
db server.. where they belong (and have a good io system on the server)
|||Who's profile "<username>" ? And \temp dir gets filled up with what?
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| I have users who are running large SELECT type queries in Access
against
| a SQL Server via ODBC. What happens is that the c:\documents and
| settings\<username>\Local Settings\Temp on the SQL server itself gets
filled
| until the drive starts running low on space. Other than to free up space
on
| the server's C: drive, are there any good suggestions to redirecting where
| Access stores the temp files?
|
|
| --
|
| *************************************************
| Andy S.
| andymcdba1@.noreply.yahoo.com
|
| Please remove "noreply" before replying.
|
| *************************************************
|
|
|||Unfortunately, the end users are creating the queries. I wish I could have
coded everything myself, but that isn't an option. I'm thinking of even
trying to redirect the ODBC log directory setting, but I was hoping there
might be other ideas.
I'd actually rather keep all the temp files locally on the clients where
there is plenty of room.
<dbahooker@.hotmail.com> wrote in message
news:1128027434.706417.58830@.g49g2000cwa.googlegro ups.com...
> yeah
> use access data projects instead and keep all your temp tables on the
> db server.. where they belong (and have a good io system on the server)
>
|||end users CAN create queries in Access Data Projects on the SQL Server
side.. just as easily as on the Access side almost
stored procs are a lot more fun that mdb
|||username would be the end user's profile. I'm not sure of the file
extension, but it is a single large temp file that can grow several hundred
MB in size. I think it is from the ODBC setting for saving long running
queries to a particular folder. I'm not sure why it wouldn't be on the
workstation vs. the server.
"Dave Patrick" <DSPatrick@.nOsPAM.gmail.com> wrote in message
news:ermq9mTxFHA.3740@.tk2msftngp13.phx.gbl...
> Who's profile "<username>" ? And \temp dir gets filled up with what?
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Andy S." wrote:
> | I have users who are running large SELECT type queries in Access
> against
> | a SQL Server via ODBC. What happens is that the c:\documents and
> | settings\<username>\Local Settings\Temp on the SQL server itself gets
> filled
> | until the drive starts running low on space. Other than to free up
> space
> on
> | the server's C: drive, are there any good suggestions to redirecting
> where
> | Access stores the temp files?
> |
> |
> | --
> |
> | *************************************************
> | Andy S.
> | andymcdba1@.noreply.yahoo.com
> |
> | Please remove "noreply" before replying.
> |
> | *************************************************
> |
> |
>
|||How did the user profile folders on the SQL server come about? Do they
terminal service into the box? Some details of the files and or file types
may help. I've been running this scenario for a long time and never seen
this before.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| username would be the end user's profile. I'm not sure of the file
| extension, but it is a single large temp file that can grow several
hundred
| MB in size. I think it is from the ODBC setting for saving long running
| queries to a particular folder. I'm not sure why it wouldn't be on the
| workstation vs. the server.
|||why would you ever make a temp file that is hundreds of mb in size?
use some views man

Access queries using up space on server's C: drive

I have users who are running large SELECT type queries in Access against
a SQL Server via ODBC. What happens is that the c:\documents and
settings\<username>\Local Settings\Temp on the SQL server itself gets filled
until the drive starts running low on space. Other than to free up space on
the server's C: drive, are there any good suggestions to redirecting where
Access stores the temp files?
--
*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************yeah
use access data projects instead and keep all your temp tables on the
db server.. where they belong (and have a good io system on the server)|||Who's profile "<username>" ? And \temp dir gets filled up with what?
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| I have users who are running large SELECT type queries in Access
against
| a SQL Server via ODBC. What happens is that the c:\documents and
| settings\<username>\Local Settings\Temp on the SQL server itself gets
filled
| until the drive starts running low on space. Other than to free up space
on
| the server's C: drive, are there any good suggestions to redirecting where
| Access stores the temp files?
|
|
| --
|
| *************************************************
| Andy S.
| andymcdba1@.noreply.yahoo.com
|
| Please remove "noreply" before replying.
|
| *************************************************
|
||||Unfortunately, the end users are creating the queries. I wish I could have
coded everything myself, but that isn't an option. I'm thinking of even
trying to redirect the ODBC log directory setting, but I was hoping there
might be other ideas.
I'd actually rather keep all the temp files locally on the clients where
there is plenty of room.
<dbahooker@.hotmail.com> wrote in message
news:1128027434.706417.58830@.g49g2000cwa.googlegroups.com...
> yeah
> use access data projects instead and keep all your temp tables on the
> db server.. where they belong (and have a good io system on the server)
>|||end users CAN create queries in Access Data Projects on the SQL Server
side.. just as easily as on the Access side almost
stored procs are a lot more fun that mdb|||username would be the end user's profile. I'm not sure of the file
extension, but it is a single large temp file that can grow several hundred
MB in size. I think it is from the ODBC setting for saving long running
queries to a particular folder. I'm not sure why it wouldn't be on the
workstation vs. the server.
"Dave Patrick" <DSPatrick@.nOsPAM.gmail.com> wrote in message
news:ermq9mTxFHA.3740@.tk2msftngp13.phx.gbl...
> Who's profile "<username>" ? And \temp dir gets filled up with what?
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Andy S." wrote:
> | I have users who are running large SELECT type queries in Access
> against
> | a SQL Server via ODBC. What happens is that the c:\documents and
> | settings\<username>\Local Settings\Temp on the SQL server itself gets
> filled
> | until the drive starts running low on space. Other than to free up
> space
> on
> | the server's C: drive, are there any good suggestions to redirecting
> where
> | Access stores the temp files?
> |
> |
> | --
> |
> | *************************************************
> | Andy S.
> | andymcdba1@.noreply.yahoo.com
> |
> | Please remove "noreply" before replying.
> |
> | *************************************************
> |
> |
>|||How did the user profile folders on the SQL server come about? Do they
terminal service into the box? Some details of the files and or file types
may help. I've been running this scenario for a long time and never seen
this before.
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Andy S." wrote:
| username would be the end user's profile. I'm not sure of the file
| extension, but it is a single large temp file that can grow several
hundred
| MB in size. I think it is from the ODBC setting for saving long running
| queries to a particular folder. I'm not sure why it wouldn't be on the
| workstation vs. the server.|||why would you ever make a temp file that is hundreds of mb in size?
use some views man

access project front end and SPs

Hi,
I have recently upsized an Access database to SQLServer. This has required
me rewriting the majority of the queries.
My question is this: How do I reference a control on an Access form in an
SP. (I have a query whose WHERE statement requires a value input by the user
on a form).
Many Thanks,
PeteThe SP can NOT reference a control on a form... In the form you must call
the sp passing the control's value as a parameter to the sp
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4B22054F-3043-4F39-979E-C88A747A01A6@.microsoft.com...
> Hi,
> I have recently upsized an Access database to SQLServer. This has
> required
> me rewriting the majority of the queries.
> My question is this: How do I reference a control on an Access form in an
> SP. (I have a query whose WHERE statement requires a value input by the
> user
> on a form).
> Many Thanks,
> Pete|||You should be able to use a Pass-through Query from Access --> SQL - filling
the parameters on the FE and passing the query call to SQL. The sp itself
can't reference the FE controls.
"Italian Pete" wrote:

> Hi,
> I have recently upsized an Access database to SQLServer. This has requir
ed
> me rewriting the majority of the queries.
> My question is this: How do I reference a control on an Access form in an
> SP. (I have a query whose WHERE statement requires a value input by the us
er
> on a form).
> Many Thanks,
> Pete|||Wayne,
What would the syntax for passing the controls as parameters be? ( I can't
seem to find a reference to it in the Access help files).
"Wayne Snyder" wrote:

> The SP can NOT reference a control on a form... In the form you must call
> the sp passing the control's value as a parameter to the sp
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
> news:4B22054F-3043-4F39-979E-C88A747A01A6@.microsoft.com...
>
>|||On a forms properties list (for example) is an item called "Input Parameters
"
You could put the values there, it takes some fiddling to get the order
right if you were going to use the onOpen event of the form to read in the
values from controls
eg:
Me.InputParameters = "@.a_variable a_datatype(a_length) = 'a_value'"
Me.RecordSource = "a_sproc_name"
Me.OrderByOn = True
Me.OrderBy = "a_field, another_field"
Me.UniqueTable = "table_name_to_be_updated"
real:
Me.InputParameters = "@.TYPE varchar(25) = 'My_Text'"
Me.RecordSource = "sp_Do_Some"
Me.OrderByOn = true
Me.OrderBy = "Year"
Me.UniqueTable = "tbl_Main"
"Italian Pete" wrote:
> Wayne,
> What would the syntax for passing the controls as parameters be? ( I can't
> seem to find a reference to it in the Access help files).
> "Wayne Snyder" wrote:
>

Access front-end to SQL back-end

I'm new to SQL Server 2000. I decided to purchase this server because
my old company (UPS) used the server to help expedite queries and
manage on-line reporting. So, now it's up to me to utilize and
understand the new server. I have been using MS Access as my front-
end for several ad-hoc databases, so I'd like to keep utilizing this
method. I want to test my first database using the SQL Server.
I've got everything set up. The IT department has installed the
server software on my computer. Within my Access database, I link in
tables from a database called "Progress" using a Merant driver.
Queries, forms, macros and modules are all pulling from these "linked"
tables. Do I need to link these tables into the SQL Server? How do I
transistion this database over to SQL? I guess I'm confused about the
relationship of how all of this should work.
Right now it looks like this:
PROGRESS .... linked...... MS Access ...... reporting/results
Where does SQL fit in?Hi,
What you need to do is migrate your database from whatever RDBMS you are
using to SQL Server. That means transferring the structure of your
database as well as the data. I don't know what RDBMS you are using so
I can't give you detailed instruction on how to do it. Essentially you
need to put your existing RDBMS out of service in favor of SQL Server.
So you can't link the exsiting "tables into SQL Server". You have to
transfer them completely.
A number tools exist for transferring data to SQL Server including bcp,
and DTS/SSIS.
Jonathan
mssbass wrote:
> I'm new to SQL Server 2000. I decided to purchase this server because
> my old company (UPS) used the server to help expedite queries and
> manage on-line reporting. So, now it's up to me to utilize and
> understand the new server. I have been using MS Access as my front-
> end for several ad-hoc databases, so I'd like to keep utilizing this
> method. I want to test my first database using the SQL Server.
>
> I've got everything set up. The IT department has installed the
> server software on my computer. Within my Access database, I link in
> tables from a database called "Progress" using a Merant driver.
> Queries, forms, macros and modules are all pulling from these "linked"
> tables. Do I need to link these tables into the SQL Server? How do I
> transistion this database over to SQL? I guess I'm confused about the
> relationship of how all of this should work.
>
> Right now it looks like this:
>
> PROGRESS .... linked...... MS Access ...... reporting/results
>
> Where does SQL fit in?
>