mirror access

Can you access a database mirror via sql while it is being mirrored to,
not after failover?Hi,
Create a Snapshot on the mirrored database and Query the snapshot.
Thanks
Hari
SQL Server MVP
"anightintheparishilton" <tj@.realmlist.com> wrote in message
news:1155944106.329642.209720@.i42g2000cwa.googlegroups.com...
> Can you access a database mirror via sql while it is being mirrored to,
> not after failover?
>sql

mirror access

Can you access a database mirror via sql while it is being mirrored to,
not after failover?Hi,
Create a Snapshot on the mirrored database and Query the snapshot.
Thanks
Hari
SQL Server MVP
"anightintheparishilton" <tj@.realmlist.com> wrote in message
news:1155944106.329642.209720@.i42g2000cwa.googlegroups.com...
> Can you access a database mirror via sql while it is being mirrored to,
> not after failover?
>

Minium Permissions necessary to create a login?

What are the minimum permissions necessary to use CREATE LOGIN?

I'm using db_accessAdmin and db_securityAdmin and db_owner. The user as access to the database.

It still says I can't create the login. What am I missing?

The answer is in Books Online:

http://msdn2.microsoft.com/en-us/library/ms189751.aspx

"Requires ALTER ANY LOGIN permission on the server. If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server."

Creating a login requires permissions at the server level, it cannot be accomplished with database level permissions.

Thanks
Laurentiu

sql

Minium Permissions necessary to create a login?

What are the minimum permissions necessary to use CREATE LOGIN?

I'm using db_accessAdmin and db_securityAdmin and db_owner. The user as access to the database.

It still says I can't create the login. What am I missing?

The answer is in Books Online:

http://msdn2.microsoft.com/en-us/library/ms189751.aspx

"Requires ALTER ANY LOGIN permission on the server. If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server."

Creating a login requires permissions at the server level, it cannot be accomplished with database level permissions.

Thanks
Laurentiu

Mining Access 2003

I created a AS 2005 data source to an Access 2003 database on my PC. The connection tests fine, I can browse the data from AS 2005. But when I try to process the mining model, I get a userid & password error. There is no password on the Access database. When I set up the data source, the wizard wanted a userid and password - the default was Admin - and I left the password blank. The connection tested ok. I tried changing the Impersonation settings, but that gives me a deployment error. Any ideas why the model process can't find a connection?

Is the AS Client and Server on the same machine? Since the access connection string contains a path to the file, the path has to be the same from the client tools as it is from the server.

|||I assumed that it was since I was able to get a connection and explore the data from AS. I have an entry under SQL Server Management Studio that's on my machine. Let me check a few things....|||Nope, still doesn't work. It still wants a userid and password for the Access table. It doesn't like the Impersonation type if I tell it to use the current user credentials. It doesn't like the Class if a pick Default or Service Account. I even tried putting a password on the Access database - and it still says it's invalid. What really confuses me is that I can right-click the table in the data source view and explore the data in the Access database with no problem.|||Is the server physically located on the same machine as the client?|||I think that may be the problem. I've been trying to find a machine that I know has the server on it to verify the solution. But our techs are upgrading the dev servers this week. At this point, I think I'll wait until things settle down and I can upgrade my PC to the new release - then I can make sure the server gets loaded as well.

Thanks!|||

If your server's not on the same system, the reason you are seeing this behavior is that the access connection string contains a path that is relative to the location from where the connection originates. For example "C:\MyDB.mdb" on your client machine is not the same as "C:\MyDB.mdb" on the server machine.

What you can do is put the Access file on a share that is accessible via the same path from the client and the server, e.g. "\\MyMachine\MyShare\MyDB.mdb"

Mining Access 2003

I created a AS 2005 data source to an Access 2003 database on my PC. The connection tests fine, I can browse the data from AS 2005. But when I try to process the mining model, I get a userid & password error. There is no password on the Access database. When I set up the data source, the wizard wanted a userid and password - the default was Admin - and I left the password blank. The connection tested ok. I tried changing the Impersonation settings, but that gives me a deployment error. Any ideas why the model process can't find a connection?

Is the AS Client and Server on the same machine? Since the access connection string contains a path to the file, the path has to be the same from the client tools as it is from the server.

|||I assumed that it was since I was able to get a connection and explore the data from AS. I have an entry under SQL Server Management Studio that's on my machine. Let me check a few things....|||Nope, still doesn't work. It still wants a userid and password for the Access table. It doesn't like the Impersonation type if I tell it to use the current user credentials. It doesn't like the Class if a pick Default or Service Account. I even tried putting a password on the Access database - and it still says it's invalid. What really confuses me is that I can right-click the table in the data source view and explore the data in the Access database with no problem.|||Is the server physically located on the same machine as the client?|||I think that may be the problem. I've been trying to find a machine that I know has the server on it to verify the solution. But our techs are upgrading the dev servers this week. At this point, I think I'll wait until things settle down and I can upgrade my PC to the new release - then I can make sure the server gets loaded as well.

Thanks!|||

If your server's not on the same system, the reason you are seeing this behavior is that the access connection string contains a path that is relative to the location from where the connection originates. For example "C:\MyDB.mdb" on your client machine is not the same as "C:\MyDB.mdb" on the server machine.

What you can do is put the Access file on a share that is accessible via the same path from the client and the server, e.g. "\\MyMachine\MyShare\MyDB.mdb"

Minimum SQL Server permissions needed

I have some Access .adp VBA code (below) that deletes a SQL Server 2005 tabl
e
and recreates it.
DoCmd.DeleteObject acTable, "dbo.tblTemp"
DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
I would like to create a role that has the minimum permissions necessary to
do this. Any advice?
If I don't SELECT control to the two tables, Access doesn't seem to see
them. If I don't grant CONTROL, Access doesn't seem to be able to drop the
tables. Yet, after the tblTemp is recreated in the CopyObject line of code,
Access doesn't see it again as the role doesn't have that SELECT or CONTROL
permissions granted to it anymore.
Another problem which seems like overkill was that I had to grant CREATE
TABLE to the role.
Also, If I don't grant control to the role dbo, Access squawks about not
having permissions to dbo.
Your help is appreciated.Not sure about your case - as you don't mention how you are trying to access
these tables after their creations but did you look into the possibility of
creating your tables in the tempdb database?
(I don't even know if this will work from DoCmd.*; however, using a SP or
the ADO connection or the command objects would probably be a much better
idea than using DoCmd.* even if the DoCmd.* are working).
For example:
Set NoCount ON
create table Tempdb..Members (IdMember int Identity (1,1) primary key,
firstname
varchar(50) collate database_default)
insert into Tempdb..Members (firstname) values ('deny')
insert into Tempdb..Members (firstname) values ('ben')
select M.* from Tempdb..Members as M
drop table Tempdb..Members
(the collate database_default statement is there in case the default
collation for the tempdb database would be different from the default
collation of the current database. If this your case, don't forget the
collate database_default statement and don't use a use Tempdb
statement; otherwise the collation used will be the one defined for the
tempdb database. Of course, if both default collations are the same then
you don't have to fiddle with this.)
In my opinion, granting Control and Create table permission on an account is
pretty much giving away all security; so if possible, it would be a much
better idea to use the tempdb database; as this database has been created
exactly for that purpose.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>I have some Access .adp VBA code (below) that deletes a SQL Server 2005
>table
> and recreates it.
> DoCmd.DeleteObject acTable, "dbo.tblTemp"
> DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
> I would like to create a role that has the minimum permissions necessary
> to
> do this. Any advice?
> If I don't SELECT control to the two tables, Access doesn't seem to see
> them. If I don't grant CONTROL, Access doesn't seem to be able to drop
> the
> tables. Yet, after the tblTemp is recreated in the CopyObject line of
> code,
> Access doesn't see it again as the role doesn't have that SELECT or
> CONTROL
> permissions granted to it anymore.
> Another problem which seems like overkill was that I had to grant CREATE
> TABLE to the role.
> Also, If I don't grant control to the role dbo, Access squawks about not
> having permissions to dbo.
> Your help is appreciated.|||For the connection object, the most simple is to use the one who is already
available:
CurrentProject.Connection.Execute ("Your sql statement here")
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>|||Sylvain,
This is an older app that I've inherited. It currently runs with sa rights
(not good), so I was trying to create a new user & role with a limited set o
f
rights that could still allow the Access app to do what it needs. I was
hoping to avoid overly changing the MS Access code, but that may not be
possible. Your suggestion of ditching the DoCmd for executing a stored proc
though is a good one. I'll pursue this.
Thank you
"Sylvain Lafontaine" wrote:
[vbcol=seagreen]
> Not sure about your case - as you don't mention how you are trying to acce
ss
> these tables after their creations but did you look into the possibility o
f
> creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the ? collate database_default ? statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the ?
> collate database_default ? statement and don't use a ? use Tempdb ?
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
is
> pretty much giving away all security; so if possible, it would be a much
> better idea to use the tempdb database; as this database has been created
> exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...|||Rob (Rob@.discussions.microsoft.com) writes:
> This is an older app that I've inherited. It currently runs with sa
> rights (not good), so I was trying to create a new user & role with a
> limited set of rights that could still allow the Access app to do what
> it needs. I was hoping to avoid overly changing the MS Access code, but
> that may not be possible. Your suggestion of ditching the DoCmd for
> executing a stored proc though is a good one. I'll pursue this.
Yes, putting this in a stored proceedure is the only way out. But Sylvain
did not tell the full story. For this to work you need to sign the procedure
with a certificate, and create a user fot the certificate and grant that
user the required rights.
I describe this in detail in this article on my web site:
http://www.sommarskog.se/grantperm.html
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|||"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> ha
scritto nel messaggio news:#aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>

minimum privileges

what are the minimum privileges required by the sql server id used by
reporting services to access it's data store (ReportingServices and
ReportingServicesTempDB)? The install gives the id specified in the install
dbo privilliges and our DBA is complaningThe minimum should be that the ID needs the right to login to report server
and it needs the RSExec role for the following:
ReportServer
ReportServerTempDB
MSDB
Master
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:7FE38CB6-0BD4-441A-8B1C-E06FCB89D103@.microsoft.com...
> what are the minimum privileges required by the sql server id used by
> reporting services to access it's data store (ReportingServices and
> ReportingServicesTempDB)? The install gives the id specified in the
> install
> dbo privilliges and our DBA is complaning

Minimum level of rights for a SQL Server DBA.

Previously, DBAs in our company used to have local machine administrator
access on the SQL Server boxes. As part of tightening server security, those
rights have been taken away from DBAs (in some cases they have been added to
'Power Users' group).
In order to install SQL Server, the account need to be a local machine
administrator (that's given otherwise installation gives error).
But, what type of minimal rights should be given to DBAs on the server
resources to let that person function properly?
When I am talking about resources, I mean rights to write to specific
directories like
* SQL Server programs/tools
* Common DLLs (in C:\Program files\common files...)
* Local backup directories
* Directory for snapshot/transactional replication transfer data.
And right to execute programs/utilities on the server like
* Perfmon (for system tuning/performance monitoring)
* Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
dependencies).
Is there anything that is not needed here or, alternatively, is there
anything that I missed?
Another dimension of this issue is the OS login access that is needed to run
the SQL Server services (needed for remote backups and replication).
Any help will be greatly appreciated!
--
Regards,
MZeeshanAt my last company, I didnt have admin rights... just SA. 90% of the time
it was fine. The other 10% it sucked. Common things I needed someone to hold
my hand on were:
1. Service restarts.
2. Hotfix/ service packs.
3. Set up stuff like Log Shipping where directory access is needed.
4. Wanting to just see how much disk space I had left on my backup drive.
5. Replication Snapshot.
I know you already mentioned alot of these. The problem is that when the DBA
needs these things, alot of time he needs them NOW. Not once he can have
someone come to his desk and log in as Admin. But like I said, 90% of the
time it was fine and I actually would prefer it. SA is usually good enough.
Any less than SA and a DBA cant get his work done.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
> Previously, DBAs in our company used to have local machine administrator
> access on the SQL Server boxes. As part of tightening server security,
> those
> rights have been taken away from DBAs (in some cases they have been added
> to
> 'Power Users' group).
> In order to install SQL Server, the account need to be a local machine
> administrator (that's given otherwise installation gives error).
> But, what type of minimal rights should be given to DBAs on the server
> resources to let that person function properly?
> When I am talking about resources, I mean rights to write to specific
> directories like
> * SQL Server programs/tools
> * Common DLLs (in C:\Program files\common files...)
> * Local backup directories
> * Directory for snapshot/transactional replication transfer data.
> And right to execute programs/utilities on the server like
> * Perfmon (for system tuning/performance monitoring)
> * Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
> dependencies).
> Is there anything that is not needed here or, alternatively, is there
> anything that I missed?
> Another dimension of this issue is the OS login access that is needed to
> run
> the SQL Server services (needed for remote backups and replication).
> Any help will be greatly appreciated!
> --
> Regards,
> MZeeshan
>|||Thanks!
Anyone? any other ideas?
--
Regards,
MZeeshan
"ChrisR" wrote:
> At my last company, I didnt have admin rights... just SA. 90% of the time
> it was fine. The other 10% it sucked. Common things I needed someone to hold
> my hand on were:
> 1. Service restarts.
> 2. Hotfix/ service packs.
> 3. Set up stuff like Log Shipping where directory access is needed.
> 4. Wanting to just see how much disk space I had left on my backup drive.
> 5. Replication Snapshot.
> I know you already mentioned alot of these. The problem is that when the DBA
> needs these things, alot of time he needs them NOW. Not once he can have
> someone come to his desk and log in as Admin. But like I said, 90% of the
> time it was fine and I actually would prefer it. SA is usually good enough.
> Any less than SA and a DBA cant get his work done.
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
> > Previously, DBAs in our company used to have local machine administrator
> > access on the SQL Server boxes. As part of tightening server security,
> > those
> > rights have been taken away from DBAs (in some cases they have been added
> > to
> > 'Power Users' group).
> >
> > In order to install SQL Server, the account need to be a local machine
> > administrator (that's given otherwise installation gives error).
> >
> > But, what type of minimal rights should be given to DBAs on the server
> > resources to let that person function properly?
> >
> > When I am talking about resources, I mean rights to write to specific
> > directories like
> > * SQL Server programs/tools
> > * Common DLLs (in C:\Program files\common files...)
> > * Local backup directories
> > * Directory for snapshot/transactional replication transfer data.
> >
> > And right to execute programs/utilities on the server like
> >
> > * Perfmon (for system tuning/performance monitoring)
> > * Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
> > dependencies).
> >
> > Is there anything that is not needed here or, alternatively, is there
> > anything that I missed?
> >
> > Another dimension of this issue is the OS login access that is needed to
> > run
> > the SQL Server services (needed for remote backups and replication).
> >
> > Any help will be greatly appreciated!
> >
> > --
> > Regards,
> > MZeeshan
> >
>
>|||Hi MZeeshan,
If you need OS login access that is needed to run the SQL Server services
(needed for remote backups and replication). I think give DBA local
administrator privilege is necessary.
BTW, you are recommanded using the tools below to ensure the security of
your product server.
Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0
http://www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B
352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Since this is a consultation type issue, you can contact Advisory Services
(AS) . Microsoft Advisory Services provides short-term advice and guidance
for problems not covered by Problem Resolution Service as well as requests
for consultative assistance for design, development and deployment issues.
You may call this number to get Advisory Services: (800) 936-5200.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Minimizing Locking Question

Hello all,
I am working on an Access 2000 project that is used to generate reports on archived sales data. This app uses linked tables that are pointing to some SQL 2K tables. Some of the queries are large and end up creating a table lock when executed, blocking all other users from using the app until it has finished executing the query. I would like to have queries that read data ignore this lock on the table but do not know of a way to do this using linked tables in Access 2000. I would use a NOLOCk table hint but need something else for this issue.
Any ideas?
ThanksWhat else do you need other than NOLOCK with linked tables?

Migration Tool

What is the best tool for migrating the database from access to SQL Server
or vise versa.
Thanks
Hi
Look at http://support.microsoft.com/default...b;en-us;237980 for
information.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <naissani@.hotmail.com> wrote in message
news:eluyvpFEFHA.1396@.tk2msftngp13.phx.gbl...
> What is the best tool for migrating the database from access to SQL Server
> or vise versa.
> Thanks
>

Migration to sql server on network for access database

hi,

I migrated the Northwind database using SQL Migration Assistant for Access. it worked fine and I was able to link the access forms to Local machine SQL Server 2005 and it worked fine.

Now i want to link the same Northwind access database to Another Server on the network.

When i try to run the wizard again, it won't migrate the user objects like tables to server, it will just create a database and that is it.

it will give me this error too.

No User objects were selected.

any idea,

Thanks,

---

Fahim

Since you have already migrated to your local SQL Server, you could use the Import/Export Wizard to move the tables to a different SQL Server.

In the Object Explorer, right-click on the database, select [Task..], and then click on Export Data...

Migration from MS access TO MS SQL

We recently needed to switch from Access over to MS SQL because of the
limitations with access and its size maximums. We currently use Access as our reporting trigger for the entire companies reporting. I'm currently trying to discover functionality that I had in Access in MS SQL. I'm slowly but surely learning. Our database structure in Access was set up with Master Files and revenue files in seperate Access databases and then linked into functional reporting access databases. So we had many links from DB to DB. Additionally we had links directly into the AS400(JDE). My question is two fold:

How do you link from DB to DB in MS SQL, my assumption was using a VIEW.
Is this the best method or should you store all the tables in the same DB(probably not, I would say).

How do you link(create view) a file where the source is the as/400? And again is this a good method for getting at that data. I think my problem here is the PROVIDER_NAME

One more. Our Infrastructure group named the server Local is that going to inhibit anything in the future.

And also one more. I get an error creating views that look at other SQL DB's when I include criteria. When I don't they work..

Thanks
jmThat's a lot of questions. I can try to answer some of them:

FACT: You can reference tables and other objects in other databases by using the full three-part name syntax:

database.owner.object

You can use this syntax is views, stored procedures, etc...wherever you would noramally reference the table or view. If you omit the database and owner, SQL server assumes you are refering to an object in the current database owned by either the current login or the dbo. Fully referencing objects is not normally required, and that's probably why you are not familiar with it.

OPINION: Put all your tables in one database. Was there a reason you had them separated in Access? That probably affected system performance.

QUESTION: What error are you getting when you include criteria in your linked views? (There should be some rules for the forum forbidding people from mentioning they are receiving an error without stating what the error is! :rolleyes: )

I don't think naming your server Local will do any harm except make code extremely confusing to read, especially code on other servers that reference it! It does show a complete lack of imagination and creativity, and you can tell them I said so.

blindman|||As fare as I know, SQL Server does not support links to "alien" systems like AS/400. So, you have in my opinion two options:

* Maintain MS Access as integration platform, even if your data is moved to SQL Server
* Let your reporting tool connect to the different systems and join the results

For a multi-database application, and a Access-SQL Server converison tool look at my references beneath.|||It is possible basically you may need MS Host Integration Server so you can talk to AS/400 servers. Using SQL 2000 Link Server technology you use Ole DB/ODBC to talk to any other RDBMS as long as it supports Ole DB/ODBC (including AS/400). I have not tried this myself so I could be way of the mark, I have tried it with Sybase running on Unix.|||Yes, you should be able to connect to your Oracle database. Checks SQL Server Books Online for "Configuring Linked Servers".

Access makes a fine reporting tool and a flexible user-interface, but if your data is on SQL server you would get the best performance by pushing as much processing back onto the server as possible. Access is notoriously slow when processing queries on remote data, because it often must pull entire tables across a network in order to make its joins. It is sometimes able to formulate a plan that involves the server filtering the data first, but this cannot be relied upon. Your best bet is to let SQL server make all the links between tables and other servers and pass that data back to Access for display. Add to that the fact that MS Access' security schema is primitive and clunky compared to SQL server.

Check out the option of converting your Access database to an Access Data Project (.adp file extension). ADP applications use a more direct connection to SQL server, and all processing is performed on the server since the ADP file cannot store tables and views. An Access Data Project is my preference for rapid application development.

blindman|||Blindman is corected. Why need a "middleware" Access when your data is in SQL Server? not ony that SQL Server is more robust & secure than Access , the performance is much better when directly access data. thru SQL Server.

Migration from MS Access 2003

Hello,

I'm very new to MS SQL Server; I just downloaded Express 2005 yesterday. I have it up and running; it seems to be working fine.

Now onto business - I need to migrate data from existing MS Access 2003. How, exactly, do I do this? I've tried searching MSDN, TechNet, and here, and while I get results that seem to tell part of the story, I don't seem to get a true "How to" for this task.

Can someone please assist me with this?

Thank you.

Here you go:

http://support.microsoft.com/default.aspx/kb/237980

Buck Woody

Migration from MS Access 2003

Hello,

I'm very new to MS SQL Server; I just downloaded Express 2005 yesterday. I have it up and running; it seems to be working fine.

Now onto business - I need to migrate data from existing MS Access 2003. How, exactly, do I do this? I've tried searching MSDN, TechNet, and here, and while I get results that seem to tell part of the story, I don't seem to get a true "How to" for this task.

Can someone please assist me with this?

Thank you.

Here you go:

http://support.microsoft.com/default.aspx/kb/237980

Buck Woody

Migration from Access to Sql Server

Hi, I import my tables from Access to SQl Server 2000
actually MSDE with the SQL server tools.

Is there a way to have the same table name as in access
because in SQl server it seems I have to call my
tables that way : testdb.dbo.po instead of just po in VB access.
That means changing all my Querys :(

Also I installed sql server on 1 PC (WinXp Pro) on my network and install the client
tools on another PC (Win98) but how can I access SQL Server from that PC?
When I'm in VB and do a connection with the ADO object I don't get
any server listed in it??
My connection string use on my local PC with server on it is working fine.

StrSqlSrv = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=imdedi;" & _
"Data Source=" & servername

Also Is it possible to package (With VB package) a program using MSDE so that it will
install MSDE and create all tables on the client PC?

Can DAO connects to sql server?

Thank you. I know... alot of questions :)> That means changing all my Querys :(

no it doesn't. try it. you can use

databasename.username.tablename

or just

tablename|||Hi, this is my connections that work fine with ADO
The only thing I do is changing the ConnectionString to my Sql Server
To make it work I have to use the .dbo thing....
This works ->> testdb.dbo.company

company.ConnectionString = StrSqlSrv
company.RecordSource = "SELECT compno,compname,telno FROM company ORDER BY compno;"

company.Refresh

Here is the error I get
A message box display Invalif Object Name company
When I click OK then
a run-time error"
(80040e37) Method 'Refresh' of Object 'IAdodc' failed|||Hi, I was able to access my tables without the .dbo
I was missing a user...

Migration from Access : AutoNumber

I have proceed to an Access Migration to SQL Server.

The Access field type "autonumber" are now
of type "int" in SQL Server.
I do not see in SQL Server a way to tell him that it is
an auto counter.
Is this transparent ?
Will SQL Server manage it alone ?

Thanks, PierreI'm not sure of your exact question, but in SQL Server the equivalent of "Autonumber" is IDENTITY. If you want to count by one, then its IDENTITY (1,1).|||I do not see the field type "identity" in SQL Server ?

My question was, in Access we have the type of field
"automatic number" which is an auto incremental integer field.
(often ID numbers)

After migration, this one become a simple "int" field.

So in my sql statement, I never fullfill this field through
the "insert", because it is automatic in ACCESS.

Now, under SQL Server, it bugs.
So I need to tell to SQL Server that this is an atomatic incremental field.

I do not see how to do that.

Thanks,
Pierre.|||Pierre,

The IDENTITY property is not a data type, but an attribute that can be assigned to INT data types (and other data types as well). From EM, you can right-click on the table and go into Design. In the design, click on the field you wish to set to auto-increment. In the area beneath the listing of columns, you will see a list of attributes (Description, Default Value, Precision, Scale, Identity, Identity Seed, Indentity Increment, etc...).

Click on the Identity attribute and set it equal to "Yes".

Alternatively, you may use a script to create the table. Here is a generic script that you may use. Note, however, that you will have issues with trying to do it this way. Obviously, you cannot create a table with the same name over the existing table. Also, if you create a temporary table and push the data from your existing table into it, you will have to enable Identity Insert (see SQL BOL). However, I thought you should see the DDL for creating a table with an Identity Column so that you might better understand what SQL is doing.

CREATE TABLE [dbo].[tbl_MyTable] (
[int] IDENTITY (1, 1) NOT NULL ,
[MyColumn1] [varchar] (50) NOT NULL ,
[MyColumn2] [varchar] (3000) NULL ,
[MyColumn3] [varchar] (50) NOT NULL
) ON [PRIMARY]

Also not the following:
1. The syntax for IDENTITY is INDENTITY (Seed, Increment)
2. Seed is a starting value. You may not want to start at 1
3. Increment is a value by which the increment the identity. You may want to increment by a value other than 1.

HTH,

Hugh Scott

See SQL BOL for more information on IDENTITY

[i]Originally posted by Plarde
I do not see the field type "identity" in SQL Server ?

My question was, in Access we have the type of field
"automatic number" which is an auto incremental integer field.
(often ID numbers)

After migration, this one become a simple "int" field.

So in my sql statement, I never fullfill this field through
the "insert", because it is automatic in ACCESS.

Now, under SQL Server, it bugs.
So I need to tell to SQL Server that this is an atomatic incremental field.

I do not see how to do that.

Thanks,
Pierre.|||Thank you very much,
that was the solution.

It does work fine.

Pierre.

Migration form MS access reports to Sql server reports

Does any of the free dowloadable reporting packs come with Reporting Services wizard to migrate data and reports from MS-Access?

Does SQL Server reporting tools come integrated with any of the VS 2005 tools?

Yes, if you are dealing with the 2000 version than have your service packs up to date and you have to have Access 2003 for the option to show up in your designer. Once you have Access 2003 it will give you the option under "project" --> "import reports".

I believe in 2005 it is under the same location: "project" --> "import reports"

I hope this helps.

|||I need to convert from MS Access 97 to .NET 2005.
Is this possible with Reporting tool?|||It may be, but with what is built into Reporting Services you have to have Access 2003. This doesn't mean you couldn't open the Access 97 database with a 2003 version of Access.

Migration Assistant For Access Can''t Find SQL Database?

I've installed SQL 2005 Express Addition which created a default database using Windows Authentication -- MachineName/SQLEXPRES. I want to convert some access databases to sql server in the migration assistant, however, when I try to connect the migration assistant to the default database I can't find the database in the drop down box wizard? I'm perplexed? Please shed some light or point me to some articles that will help. Thanks.

It is not practical to use standard Express to migrate Access so see if you can use the one with Management Studio instead, this will give you control of all the steps. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/

|||

Yes, Management Studio is the one I used. I am able to connect via Management Studio, however, the Migration Assistant can't locate it? Looking for other suggestions? Thanks.

|||

Try the eval good for 6 months, you could buy the developer for under $40 online if you have the ram this can help you move Access if all you have is tables and queries. If you have VBA you may need professional help. I have seen Access migration taking six months or more because it lets users add stuff SQL Server will not accept and it uses limited data types.

http://www.microsoft.com/sql/downloads/trial-software.mspx

|||

I'm not sure I understand what you mean by "default database" in this context. SQL Server does not create any default databases when you install it.

In my use of SSMS I find that the drop down box that show the database name does not actually show the list of databases in the server because it has not actually connected to the server yet, so it can not list the databases. You can either specify a new database to be created or specify an existing database to open once you connect, but you need to type the name of the database, it won't populate the list for you.

Regards,

Mike

Migration ACCESS --> SQL Linked table

Hi again,
I have paradox table linked in an access database. I want to migrate to SQL.
Can I link these tables in SQL ?
ThanksAccess does a better job of linking to ISAMs than SQLS does (which
uses the Jet provider). Is there any way you can migrate ALL of the
data? If linking to other ISAMs is a big part of your app, you might
be better keeping that part of it in Access/Jet. You can always link
to SQL Server tables as well.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Thu, 6 Nov 2003 17:23:19 -0500, "Sylvain Provencher"
<sylvain.provencher@.nobelia.com> wrote:
>Hi again,
>I have paradox table linked in an access database. I want to migrate to SQL.
>Can I link these tables in SQL ?
>Thanks
>