.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

multiple access to sql server database, one table

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server

I have multiple apps running which access a sql server database. I had initially a table set aside for each app but now I was told to put everything into one table and have all apps access the database. I just want to make sure that just changing my connection string for all apps will suffice and that I do not have to make any other changes for all apps to write to one table. Will this be ok? The apps are writing to the database table which has a unique index on one column and a auto incremented primary id key. I changed the stored procedures I had for each app since the connection strings were the same as they access the same database. Just want to make sure there will be no conflict issues when each tries to write to the same table. None of the apps will be querying the table at any time except when I run manual queries myself.

View Complete Post

More Related Resource Links

Under the Table: How Data Access Code Affects Database Performance


In this article, the author delves into some commonly used ways of writing data access code and looks at the effect they can have on performance.

Bob Beauchemin

MSDN Magazine August 2009

Upsize Your Database: Convert Your Microsoft Access Application to Take Advantage of SQL Server 7.0


What if you need to convert an existing Microsoft Access 97 database application into a true client-server application that is based on a SQL Server back end? If you know a little about Visual Basic and SQL Server, it's easy to make your app take advantage of the power and scalability provided by SQL Server 7.0. Using some concrete code examples, this article takes you step by step through converting the native Jet queries in your Access application into stored procedures and pass-through queries that SQL Server can use. You'll also learn how to pass on parameters when your client-server app calls these SQL Server stored procedures and queries.

Michael McManus

MSDN Magazine June 2000

How to get access to content database after server hardware failure


Sharepoint server 2007 and remote sql 2000 SP4 database, after ShraPoint server crash only content databases are available and intact. Trying to restore from backup fail, stsadm -o restore says that not valid backup is available on path. How to get access to available content database using a new installation with same server name, ip address and partitions configuration. How to recover site with content databases information.

Regards, thanks for help.

Victor Naranjo MCSE + Security MCSA + Security MCSE + Messaging MCSA + Messaging ITIL Certified Comptia Security+

How to create a view in my database from a table in another server/database

Is there a way to create a view in my database referencing a table in another server/database if I have the connection information.  I know how to do it in Oracle, simply create a database link, but have never done so in sqlserver.

how to access to a remote SQL server database?

hi everybody, I'm really a beginner with database and I don't know how can I access by example with SQL server management studio (express or not) to a remote sql database which is based on a remote server through internet. When I start SQL server management studio express I can put "name of the server" and authentication but I really don't know what to put here. Do I need to put a remote IP address? Does it work like that? Can i access to a remote database with SQL server management studio or I need something else? What I want to do is to begin, access to this database, and then, I would like to copy a table from this database to a local SQL server. Any help will be really appreciated! Thanks  

SSRS2005: Is it safe to manually remove instances from the Keys table in the report server database?

I have a problem with redundant reporting services instances still hanging around in the initialization pane of the RS configuration wizard and I can't get rid of them. The problem arose when we had new webfarms and before I had removed all references to the old ones from the initialization screen, the old webfarms had been switched off, removed from the network and dismantled. Therefore when I try to 'de-initialize' them, I get an error stating that reporting services cannot connect to them and when I run reports, a fair few are trying to run on the old webfarms which no longer exist and are disappearing into the void. There's a table in the report server database called Keys whch contains details of these intances and i'm wondering if its safe to remove these via SQL commands without breaking reporting services on other machines.   As a side note: All this stems from the fact that when you uninstall Reporting Services, not all of the files / references are removed. So as a general word of warning, if you do an uninstall of RS2005, you'll need to make sure everything's gone before re-installing, throwing away old machines etc. These include folders in IIS and these old instances as mentioned above.  

SQL Server database restore with multiple logicalfiles by using t-sql

Hi,   I have set of database backups created in the production sql server 2005. I need restore these backups into another sql server 2005 by using t-sql script(stored procedure). Each database will have different number of logical files in it. The physical location of the target server may be different for these logical files. I know that i should use WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf' clause. Now i want to know how would i put it in a t-script stored procedure such a way that it automatically identifies all the logical files and create a physical file on the target server. User will supply source of bakup file and target folder for mdf,ldf, etc files. Do i need to populate the sql query as string by looping through all the logical files? If so how would i execute the populated sql query as string ?  Mr Genius

How to submit data to a SQL Server database table from a custom page ?

Using SPD, I have created a custom page. It has a form with ASP.NET controls textbox, radio buttons, dropdown, submit button etc., Also, I created a Database connection (to the SQLServer database table) under Database Connections. But not sure how to connect this form to the database connection so that when form data is submitted, the data will be inserted into the table.

Upsizing Database from Access 2007 to SQL Server Express 2008

I have recently downloaded and installed SQL Server Express 2008 on to my Windows XP SP3 machine, I have a large MS Access 2007 database (.accdb) I wish to export out to SQL Server. I am using the 'upsizing wizard' that is found on the 'Database Tools' tab of Access 2007. Clicking on the 'Database Tools' tab and then on the 'SQL Server' button starts the Upsizing wizard. On the first page of which I select the 'Create new database' option, the next page asks 'What SQL Server would you like to use for this database?' it already has '(local)' entered into the text box, so I leave that unchanged, I tick the trusted connection box and name the new SQL Server database and then click 'Next'. But I get the following error (it appears in a Windows information message box):

Title: Microsoft SQL Server Login
Message Text:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen(Connect()).
Connection failed:
SQL State: '08001'
SQL Server Error: 17
[Microsft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

PS. I have successfully used the upsizing wizard to export the same Access database into SQL Server Express 2005 on another machine.

What am I doing wrong this time?

Does a SQL Server Compact 4.0 database support simultaneous access from different computers?


For example, if an sdf file is on a network server, could two network clients running SQL Server Compact 4.0 access the file at the same time?

This was not possible using SQLCE 3.5. I'm wondering if it's been added to 4.0 (so that it brings back the one advantage of using an Access db.)

Can instance(s) access database(s) not on there instance in sql server


I've only seen instance(s) access databases on that instance. Can multiple instances access databases on other instances, in the old versions or in R2 of sql server?

how to get Notepad all Item in sql server database table field



I have one asp.net application using Sql server as back end.. in sql server database i have one table which consist two fields.. ItemName and Rate

I have one notepad file which consist around 700 ItemName with Rate.. 

So how to get this ItemName and Rate in my Table...

Linked Server to Password Protected Access Database


I want to create linked server in SQL 2005 with Ms Access 2003 Database. I have used following commands to create linked server.

EXEC sp_addlinkedserver
@server = N'TestLinkServer',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'Access',
@datasrc = N'D:\Test.mdb',
@provstr = Null

EXEC master.dbo.sp_addlinkedsrvlogin

With above command linked server successfully create and can run SQL query against linked server. But if i set Database password to MS Access 2003 Database, then it gives following error message.

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkServer" reported an error. Authentication failed.

Any ideas?


Linked Server Error 7303 for Access 2003 Database

I am having difficulty creating a linked server to and Access 2003 database on my SQL 2005 server.  Whenever I create a linked server through the either the “New Linked Server” tool in the SQL Management Studio or using the sp_addlinkedserver method, receive and error indicating “Cannot initialize the data source object of the OLE DB provider…(Microsoft SQL Server Error: 7303)”.  Although my ultimate goal is to link to a secure Access 2003 database file, the current MDB file I am trying to link to is “unsecured”.  I have already verified that the SQL Service account has read and write access to the folder containing the MDB file as well as the temp directories on the SQL server (suggestions from other forums).  Is there any SQL Server configuration that I need to modify in order to use the Jet 4.0 linked server provider (Microsoft.Jet.OLEDB.4.0)?

MS SQL Server Stored Procedure - Writing on a table in another another database


I am writing a stored procedure in a database for an app that will become part of a "shell" program that was written to accept an endless # of VB.Net apps.

The stored procedure will look at data in a table on that database, but the end result is that I want to update a table that exisits in A DIFFERENT database on the same server. I think I am referencing the table itself correctly, (databasename).dbo.(tablename). The problem is that the SQL is not performing as it does if I manually run the SQL in the database I'm trying to update.

Here is the SQL:





/****** Object: StoredProcedure [dbo].[usp_ARReport] Script Date: 10/25/2010 14:43:20 ******/



Suggestion about exporting data into Access database from sql server 2008 without Linked Server



    We have a website that was using Sql server 2005. There was a option in Website where when a user clicks a button, Some tables data will be exported to an access database. To achieve this, we have setup a linked server to Access database. We have moved from sql server 2005 to 2008 (64bit). We did not want to use that option of having a linked server now. I am looking for different ways to achieve the task. We also donot want to use OPENROWSET and OPENDATASOURCE .


Can someone  give some insight about this?




How to set Primary Key to imported Access Table into SQL Server?

Ive already imported database from Access to SqlServer.
Tried to set a primary key and save it but the thing is SqlServer states "Saving changes is not permitted".
How can I make it work?
Any ideas . Please help. Many thanks
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend