I've read afew articles about .Net and they mentioned not using Access when developing with VS.Net, but the articles didn't go into why. Between the two, is one better/easier to use than the other? Is there a cost difference? Or does it depend on what you are doing?
Any input would be appreciated. Thanks.
Hi I am using Visual Studio.Net 2003 and SQL 2000 Web applications run using the ASPNET user account.
I have to set up this account and grant it permissions before my Web application will have access to a SQL database. How do I grant permissions ?.
what is the proper method of taking a database made in MS Access and converting it so that it runs in SQL Server 7?
View 1 Replies View RelatedIs it possible to have an ID that can access 2 databases? If it's possible, I have to create the same ID and password under each database?
View 6 Replies View RelatedHi everybody,there are several SQL-Server 2000 databases within a company locatedon different servers in different domains. On every database you canfind the same table X.I want to merge these tables X (UNION query) and print the result witha Crystal Report.Unfortunately I only have little knowledge on security, domains,distributed applications.Thanks for help.
View 2 Replies View RelatedThe project I work with consists of about a dozen databases. One of the .mdf files was deleted by one of the team members. Since we are still exploring with the data structures to some degree, we didn't have a backup for this file, but we do have the .sql file that created the database. Now, however, we cannot access any of the databases because the OS reports that it can't find the one file. The log shows that it connects to the others fine, but gives us a login failure because of one missing file [Error: 18456, Severity: 14, State: 11]. Losing the file is of no importance to the team, but it sure is causing a problem for SQL Server 2005. How do we get the rest of the databases to connect and just drop the damaged one to recreate it later?
View 8 Replies View RelatedI have a restaurant POS application that is written in VB 6 and the data is being written to a access database. I need to access this database over the internet. I need to get certain tables (current sales) in this database to automatically send the updated data to a Web Server. My questions are: What is the best way to do this? Do I need to have sql express loaded on site and let it do the interactions with the Access server and then connect to SQL express over the internet? I am new to this and not sure of the best way to design it. Do I use Report services or analysis services to design?
Thanks
I try to fill a recordset with a query in an asp page to an SQL Server through ODBC connection :
The ODBC connection (eg : DB1Cnnection) is done to one Database (eg : DB1).
and I need to link to another Database (eg : DB2)
it `s like :
set rs=DB1Connection.Execute("Select DB1.xxx FROM TABLE1, TABLE2 WHERE ...")
Please, How do I `tell` to ODBC that TABLE2 is in DB2 ???
HELP !
Scenario:
1. Access to database objects is based on database roles.
2. Application administrators handle the assignment of users to database roles. Application administrators are first line managers, typically; they are not DBA's and not Help Desk staff.
Question:
What products are available to simplify the management of user access to databases in the aforementioned scenario?
TIA to all respondents.
--
Peter
I know the SQL syntax to join two tables in different databases on the ame server, but does anyone know if a SQL statement will allow you to join tables from different databases on different servers?
View 8 Replies View RelatedI have SQL Server running on our web server located at a managed hosting site at Rackspace. I've got some developers who are going to be editing various sites and will need access to specific databases. Is there something built in to MSSQL, or a third party app, which will allow for this kind of remote access? They will be doing everything from restructuring these DB's to just inputting data.
Thanks!
Chris Sanders
Hi,
How can I provide a user read only access to all the databases on the server. I have 15 databases on the server. I know I can give db_datareader access in each database individually but that is time consuming and I have 10 servers for this to be done.
thanks in advance.
Any way to list all databases that a user has access to?
View 6 Replies View RelatedI’ve been asked to migrate many Access databases to SQL Server. There are no reports, queries or forms in these databases. There are two different database structures. Each has many copies with different data. For example, 80 people each have a copy of abc.mdb. Each copy has the same tables, but different data. The databases cannot be merged without modifying the structure of the databases and the programs that access them. There are over 100 databases ranging in size from 2 MB to 60 MB.
What’s the best approach to do this? I don’t want to manually run each through the upsize wizard.
Should I use a local SQL Server Express or a central SQL Server?
Will there be any benefits to doing this? Will the performance improve?
Personally I’d rather redesign the databases, merge the data and modify the applications that use the data. But it’s not my decision.
Hi there,I have a situation where an application needs to import data fromnumber of access mdb files on a daily bases. The file names changeevery day. The data import is very straight forward:insert into sql_table select * from acess_tableThere are up to 8 tables in each access file and some access files willhave less. So the process needs to figure out which tables exist inAccess mdb file and import them whole into sql staging tables.Any recommendations are appreciated.Thanks
View 2 Replies View RelatedHowdy Folks,
I have an ETL project that I want to ask a question about. I want to loop through a folder containing several Access databases, and extract and load each one into a single SQL Server 2005 database. The table layout is the same for all the Access dbs, as well as the SQL db. The number of Access dbs in the folder, their records, and their names will change. Is there a way i can use the SSIS Foreach tool to move through the folder and set the connection string inside the loop?
Thanks for your reply,
Chris
I have upgraded 2000 databases to sql server 2005.If the users use just query analyzer & enterprise manager in 2000 to access 2005 databases.
will there be any issues???
let me know
Hi, my first question, tried to use search first but couldnät find what I need.
Hopefully someone has asuggestion.
I work for a ISV producing HR applications.
When user starts the applications the program shows all databases that the user have access to. This is done by select all names from master..sysdatabases and then trying to "USE dbname" to see if user have access,. Users doesn't have sa rights.
This procedure takes approximately 90 secs for a server with 500 databases and that has become a problem.
Does anyone know off a faster method, any suggestions?
/Regards Anders
I have a test server where I was experiencing security/rights anomalies. I tried disabling the BUILTINAdministrators account, closed SEM, and when I reopened it, I could not access my Server (all databases,etc.are on local machine). I thought I could just use the SQL accounts (sa, etc.) to connect, but they all fail?? I tried creating a new registration, and that didn't work either. Since I deleted the old registration, and I cannot reconnect, what are my options(short of restoring image)? If I re-install SQL will all my databases still be there? Is there another way to get access back?? Any help would be appreciated.
rob
Hello,
Does anyone know how to prevent a certain machine from accessing SQL server? We have users who use a generic username and password (i.e. one login for 15 users) and I need to prevent a certain machine (in a high risk location) from hitting my databases. Any ideas
Thanks for your time and help
Gurmi
I have around 600 databases in my server, a user need select access of all the databases. will i have to go one by one in all the dbs and create that user and give datareader role to him. or is thr any shorter way to do so????
View 8 Replies View RelatedHi,We have several Access database that we would over time plan to convertto SQL. I am looking for a tool that works better than the upsizewizard in helping with the transition - I am looking for somethingreliable yet reasonably priced.Thanks in advance for all your helpKR
View 2 Replies View Related
Dear Readers,
Is it possible to create a query that access multiple sql express databases? Or is it possible to link a table in one database from another?
Dear all,
I wrote the below script to add a SQL server login account that is the db_datareader, db_datawriter, and granted permission on all stored procs, functions, and views on all databases within a server.
Code Snippet
USE master
GO
SET NOCOUNT ON
DECLARE @database_name sysname
DECLARE @object_name sysname
DECLARE @object_type char(2)
CREATE TABLE #databases (DATABASE_NAME sysname, DATABASE_SIZE int, REMARKS varchar(254))
INSERT #databases EXEC sp_databases
-- ++++++++++++++++++ add SQL Server Login ++++++++++++++++++
IF EXISTS (
SELECT 1 FROM master.dbo.syslogins
WHERE [name] = 'WEB_USER2'
) BEGIN
DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT DATABASE_NAME FROM #databases
OPEN db_cur
WHILE 1 = 1
BEGIN
FETCH db_cur INTO @database_name
IF (@@FETCH_STATUS <> 0) BREAK
EXEC ('USE ' + @database_name +';
IF EXISTS (
SELECT 1 FROM sysusers
WHERE [name] = ''WEB_USER2''
) BEGIN
EXEC sp_revokedbaccess ''WEB_USER2''
END
')
END
CLOSE db_cur
DEALLOCATE db_cur
EXEC sp_droplogin 'WEB_USER2'
END
EXEC sp_addlogin
@loginame = 'WEB_USER2',
@passwd = 'password'
-- ++++++++++++++++++ loop thro' all User-Databases ++++++++++++++++++
DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT DATABASE_NAME FROM #databases
WHERE DATABASE_NAME NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution', 'ASPState')
OPEN db_cur
WHILE 1 = 1
BEGIN
FETCH db_cur INTO @database_name
IF (@@FETCH_STATUS <> 0) BREAK
PRINT ''
PRINT 'Current database=' + @database_name
-- add user to databases
EXEC ('USE ' + @database_name +';
IF EXISTS (
SELECT 1 FROM sysusers
WHERE [name] = ''WEB_USER2''
) BEGIN
EXEC sp_revokedbaccess ''WEB_USER2''
END
')
EXEC ('USE ' + @database_name +'; EXEC sp_grantdbaccess ''WEB_USER2''; ')
-- add user to db_datareader
EXEC ('USE ' + @database_name +'; EXEC sp_addrolemember ''db_datareader'', ''WEB_USER2''; ')
-- add user to db_datawriter
EXEC ('USE ' + @database_name +'; EXEC sp_addrolemember ''db_datawriter'', ''WEB_USER2''; ')
-- grant permission on Stored proc, Scalar function, Inlined table-function, Table function, View
-- !! coz EXEC is a self-contained batch, so must use GLOBAL
EXEC('USE ' + @database_name +';
DECLARE obj_cur CURSOR GLOBAL FAST_FORWARD FOR
SELECT [name], [type] FROM sysobjects
WHERE [type] IN (''P'', ''FN'', ''IF'', ''TF'', ''V'')
')
OPEN obj_cur
WHILE 1 = 1
BEGIN
FETCH obj_cur INTO @object_name, @object_type
IF (@@FETCH_STATUS <> 0) BREAK
-- PRINT 'object=' + @object_name + '; type=' + @object_type
IF LTRIM(RTRIM(@object_type)) = 'P' OR @object_type = 'FN'
BEGIN
-- EXEC on Stored proc, Scalar function
EXEC('USE ' + @database_name +'; GRANT EXEC ON dbo.' + @object_name + ' TO WEB_USER2 ')
END
ELSE
BEGIN
-- SELECT ON table function, View
EXEC('USE ' + @database_name +'; GRANT SELECT ON dbo.' + @object_name + ' TO WEB_USER2 ')
END
END
CLOSE obj_cur
DEALLOCATE obj_cur
END
CLOSE db_cur
DEALLOCATE db_cur
DROP TABLE #databases
plz revise it if you have better idea! Thx!
I have a small client installable web application which used to work great on 32 bit machines. Well now clients are starting to use x64 Windows 2003 servers and things have begun to unravel. W2K3 x64 does not have any of the JET database drivers needed to communicate to the Access databases and it doesn't look like they're coming anytime soon.
Is SQL Server Express the new replacement for Access databases on x64 machines. Does Microsoft intend on moving some of the JET drivers over from 32 bit servers. I know that you can run IIS in 32 bit mode and everything is fine, but I can't ask everyone to do that.
Is SQL Server Express something that I can expect people to have installed, it sure doesn't sounds like it. After reading some posts, it sounds like it's a bit of a hastle to install.
Thanks in advance for your time.
-Mark
I need help in creating a Data Source within SQL Server Reporting Services 2005 to connect to a Microsoft Access database. I'm sure the problem is an authentication issue, because the report works fine when previewed through Visual Studio 2005. But, through the report server browser front end I get the following error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'AssignedEmployees'. (rsErrorExecutingCommand)
The Microsoft Jet database engine cannot open the file '...'. It is already opened exclusively by another user, or you need permission to view its data.
The Access database happens to reside on the same server as the SQL Server instance. The query that it is invoking uses tables linked to other Access databases including one that is on the same server and another that is on a completely different server. The error listed above references the database that is linked from the main Access database and that is on the same server.
I tried various things to get it to work, but what it comes down to is that I do not know the proper way to create the Data Source to an Access database from SRS (especially where the Access database links to other Access databases). Can anyone provide the steps to do this properly?
Hi,
Im trying to access data from a database on another server in a SQL 2005 query.
use Bury2k29.ServiceDeskForms
select .......
but I get the message
could not locate entry in sysdatabases for database 'Burky2k29'. No entry found with that name. Make sure that the name is entered correctly.
Bury2k29 is the name of the server, and ServiceDeskForms is the database I want to access.
When I open a blank query and enter only the code to access that database it runs fine.
Any ideas?
I am looking for something that can detect unauthorized access to our databases. Something that can be used in a 10+ sql server environment.
Hi everyone,
Hope somebody can help me on this.
I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:
"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"
There are NO users using the application. These are DBs for the Portfolio Server 2007 application.
After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.
Can they be deleted? if so, would there be any serious consequences?
Thank you for your help on this.
Oscar E.
I'm using SQL Server Management Studio eExpress and I'm tryng to link to a database created in Visual Web Developer Express. My problem is that I can't see large parts of my file system when I try to attach a database in SQL Server Express. Web Developer Express likes to put its databases in C:Documents and Settings<User Name>My DocumentsVisual Studio 2005WebSites<Site Name>App_Data. However, when I try to attach a database in that location to SQL Server Express (Right Click Databases node, select Attach from the shortcut menu then Add from the Attach Databases dialog) I get a folder tree that won’t let me go any further than C:Documents and Settings<User Name>. There seem to be large parts of my folder structure that I cannot access with this dialog.
Just in case this is a permissions issue, I am using Windows XP Pro on a stand alone machine. I log on as Administrator, and the <User Name> in the above paths is Administrator. The Administrator is a member of the Administrators group. I have never logged on as anything else and never made any changes to the way permissions are set. When I start SQL Server express I select Windows Authentication.
TIA
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
I have a client who would like to access his database via SQL Server Management Studio. I created a login for him. This login has access only to his database on the server (I set this up using the user mapping), the Securables has Connect to SQL checked (otherwise he won't see his database). When I test this login, he is able to see all the other databases on the server, though when he clicks on the ones he doesn't have access to, it will tell him that he can't access that database. He can access his database alright. My question is, can I hide all the other databases from him, and only display that only database he has access to? I have tried many different ways, I can't seem to be able to do so.
View 6 Replies View RelatedHi,
How would I set permission for SQL Server 2005 "User A" to prevent access to System and other user databases, also How to hide the databases that "User A" has no rights to. I mean, when User A logs in, All other user databases are not visible to him/her.
Thanks,