2-Tier Architecture - How To Manage Lots Of Users
Nov 6, 2007
We have a 2-tier architecture with thick client (.NET 2.0) applications directly accessing the SQL Server database. We are struggling to manage lots of users while maintaining security. Granting lots of users directly to the database seems to be tough to manage. In fact, we would like to let supervisors without DBA previlege to add and remove users of our applications. Using SQL Authentication (a single account to access the database) is the other alternative but it is not considered as a secure solution.
I would appreciate if anyone gives me suggestions on how to handle this, without moving to a 3-tier architecture (dedicated middle-tier DB access layer running a custom user account).
Thanks in advance.
View 4 Replies
ADVERTISEMENT
Sep 21, 2007
I have created an application that I intended to be 3-tier, but I am not sure if I did it properly. I constructed it like this: I created a DLL that contains methods that validate the passed parameters, checks the data against business rules, and issues ADO.NET methods to access the data. The ASP.NET presentation layer uses Object Data Sources to link to these methods. With this architecture I consider the ASP.NET pages to be the presentation layer, the DLL to be the business layer, and the database itself to be the data layer.
Now I am wondering if the standard practice is to have a further division? In this case, there would be a business layer DLL whose only purpose is to validate the parameters passed to it by the presentation layer, and to do business rules checking. There would also be a data layer DLL whose purpose is to accept parameters from the business layer and issue ADO.NET methods to access the database. In this scenario the database itself would be considered part of the data layer, or not considered to be a layer at all.
Either one will work, but I would like to implement the architecture that is most accepted, and allows the easiest maintenance. What is the best practice for designing a 3-tier architecture?
View 3 Replies
View Related
Nov 28, 2006
I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database. I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture. I have been following the principles of Scott Mitchell's tutorials. I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor. Thus where the first version had code: -
Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'" Dim message As String = "" Dim Found As Boolean = False Try Using connection As New SqlConnection(GDB_AppSettings.Connection) Dim command As New SqlCommand(queryString, connection) connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
If reader.Read() Then Found = True _USRid = reader("USRid") _USRPassword = reader("USRPassword") _USREmail = reader("USREmail") _USRTitle = reader("USRTitle") _USRName = reader("USRName") _USRRole = reader("USRRole") If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End If End If reader.Close() End Using Catch ex As Exception If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then Return "Cannot open database to logon" Else Return Err.Description End If End Try
the new version is much more elegant: -
Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter()
Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid) If tbUser.Count <> 1 Then ' Anything other than 0 or 1 should be impossible Return "User not found" End If
Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0) _USRid = userid _USRPassword = password _USREmail = trUser.USREmail _USRTitle = trUser.USRTitle _USRName = trUser.USRName _USRRole = trUser.USRRole _USRAgreedConditions = trUser.USRAgreedConditions
However, there is a problem. The database field USRAgreedConditions is a Datetime field that can be null. The new version works perfectly when it is NOT null, but throws an exception: -
System.Data.StrongTypingException was unhandled by user code Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull." Source="App_Code.wt2vzoc1" ....
There is no point in writing: - If Not IsDBNull(trUser.USRAgreedConditions) Then _USRAgreedConditions = trUser.USRAgreedConditions End Ifbecause the exception occurs within the automatically-created code in the data access layer. I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String. Of course USRAgreedConditions is a Datetime field, so I can't change the property.
It seems that my only options are: -1. To stick with the old query-building code. But this doesn't give me the advantages of a proper 3-tier architecture2. To change the generated code in wt2vzoc. This MUST be a bad idea - surely I should leave this code untouched. Besides, what if the table adaptor has to be regenerated when I change the table design?3. Code a Try block within the business layer: - Try _USRAgreedConditions = trUser.USRAgreedConditions Catch ex As Exception _USRAgreedConditions = Nothing End Try
This seems to work OK, but seems less elegant than the original code in the old version: - If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End IfIs there a better way?
View 4 Replies
View Related
Mar 25, 2008
Hi ,
We are trying to implement a 2 tier architecture for our inbuild SQL 2005 backed online application. We want the SQL Reporting Server Interface --- Reporting service website to sit on Server 'A' and the actual reporting server to sit on Server 'B'.
Can someone suggest or advise us how to proceed with this.
Server 'A' is our application server and has IIS & Application.
Server 'B' is our DB server and has IIS,SQL 2005, Reporting Server,Intergration Service.
All suggestions are welcome.
Thanks,
Namit Sethi
View 1 Replies
View Related
Oct 31, 2006
Hi Folks ...Question for everyone that I have not been able to figure out. I have an application that is spread across tiers:SQL Connection defined in Web.Config file that connects to SQLServer database.DAL layer created references SQL Connection in Web.Config file - have noticed this copies the connection string information to the local properties for each TableAdapter that is defined in the DAL layer.BLL Layer that references Table Adapters declared in the DAL layer.When the web site is called, the link will provide an encoded id.Sample call to website: http://www.mysamplesite.com/default.aspx?company=AE2837FG28F7B327Based on the encoded id that is passed to the site, I need to switch the connection string to use different databases on the backend.Sample connection string: Data Source=localhost;Initial Catalog=dbSystem_LiveCorp1;User ID=client;Password=live2006 I would need to change Initial Catablog to the following:Data Source=localhost;Initial Catalog=dbSystem_LiveCorp196;User ID=client;Password=live2006How do I do this and have the connection string reflected in all of the Table Adapters that I have created in the DAL layer - currently 100+ Table Adapters have been defined.As we gain new clients, I need to be able to have each clients information located in a different database within SQL Server. Mandated - I have no choice in this requirement. Being as I don't want to have to recreate the DAL for several dozen clients and maintain that whenever I make a change to the DAL to then replicate across multiple copies. There has to be a way to dynamically alter the SQLConnection and have it recognized across all DAL TableAdapters.I'm developing with MS-Visual Studio 2005 - VB. Any help would be greatly appreciated. Thanks ...David Any day above ground is a good day ...
View 1 Replies
View Related
May 23, 2006
I have a user that I have granted the server role "securityadministrator" and a database role "db_securityadmin". When logged inas this user I can create new logins but not run sp_adduser to add thenew login to as this says I don't have permission to do this. I canhowever run sp_revokedbaccess to get rid of a user from the database.
View 2 Replies
View Related
Jun 13, 2006
I coding an ASP.NET app. I'm getting some error messages about the ASPNET user can't access the DB. How can I manage a user (specifically add one) in SQL Server 2000? Thanks in advance for your help!
View 3 Replies
View Related
May 31, 2014
I have a db to manage the creation of invoice number designed for a web application.
My problem is how to manage the concurrency when the users need to create an invoice number.
View 9 Replies
View Related
May 22, 2008
Hi
I think the subject is pretty clear.
We are thuinking of installing Server 2008 in x64 mode on a new Application-Tier-Machine. But due to existing projects which use several databases the SQL-machine runs on a Server 2003 x32.
Would it be possible to install the Reporting Services x64 binaries on the app-tier but use a x32 SQL-Instance for the data?
Thanks in advance
View 1 Replies
View Related
Jul 20, 2005
Greetings,We are trying to set up a set of "Leading Practices" for ourdevelopers, as well as ourselves, and hope some gentle reader canrecommend some documentation in favor of what appears to be the rightposition to take.We do not allow third party applications to run on our SQL Servers. Wewant to include DTS Packages under the definition of third partyapplications, insisting instead that the developers save theirpackages as COM Formatted files into their source code control systemsand run them from their app servers. The devlopers would like to hearthis from someone besides ourselves.While strong recomendations to remove guest access to MSDB altogetherabound, I have been unable to find a straight forward discussion ofthe advantages of structured file storage and app server off load ofDTS packages.Can anyone suggest any articles, white papers, rants, etc attemptingto formulate a solution to the benefits of taking msdb away fromguest, with the advantages of running DTS from an App server orworkstation platform, with the packages protected in source codecontrol?Thank youJohn Pollinsjpollins @ eqt . com
View 2 Replies
View Related
Feb 27, 2007
Hello all,
Q1:In all asp.net books the authors would tell you to seperate your presentation , business, and data layer but why does the wizards for the sql data source combine the presentation layer with the data layer?
Q2:Is there a way to retreive a dataset after you execut the sqldatasource.insert() command? .......I have a store procedure(sp) that i am using for inserting records. The sp is where i 'm validating all my data (shop standard) for example, ssn length, last name <> 0 etc. If there are any errors i grab all the error code from another table and return it. I can't seem to figure out how to get the dataset....
View 3 Replies
View Related
Feb 10, 2005
Hey all.
I've just been doing some surfing on the pros and cons of multi-tiered environments. I see a lot of conflicting information out there.
I'd like to get everybody's opinion of the advantages and disadvantages of two-tiered vs three-tiered applications, specifically with a SQL Server back-end and all the functionality that comes with it (TSQL, triggers, user-defined functions, etc...).
Also, some definitions or principles regarding what tasks should be handled in which layers (especially business logic).
Brett? Pat? RJabarov? Rudi? Others?
View 14 Replies
View Related
Nov 30, 2006
using System;
namespace BaResearch.Data.msSql
{
interface ISqlDataObject
{
System.Data.SqlClient.SqlConnection Connection { get; }
string ConnectionString { get; set; }
}
}
___________________________________using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace BaResearch.Data.msSql
{
public class SqlDataObject : ISqlDataObject
{
private string _ConnectionString;
public SqlConnection Connection
{
get { return new SqlConnection(_ConnectionString); }
}
public string ConnectionString
{
get
{
return _ConnectionString;
}
set
{
_ConnectionString = value;
}
}
}
}
__________________________________using System;
namespace BaResearch.Data
{
interface IBid
{
string Client { get; set; }
string Contact { get; set; }
string Sponsor { get; set; }
string Priority { get; set; }
string BidStatus { get; set; }
}
}
_____________________________________________using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace BaResearch.Data.msSql
{
public class Bid : SqlDataObject, IBid
{
#region private member & variables
private int _BidID;
private string _DateCreated;
private string _CreatedBy;
private string _Client;
private string _Contact;
private string _Sponsor;
private string _Priority;
private string _BidStatus;
private SqlDataAdapter sqlda = new SqlDataAdapter();
private SqlCommand _command;
private SqlParameter[] _parameters =
{
new SqlParameter("@client",SqlDbType.NVarChar),
new SqlParameter("@contact",SqlDbType.NVarChar),
new SqlParameter("@sponsor",SqlDbType.NVarChar),
new SqlParameter("@priority",SqlDbType.NVarChar),
new SqlParameter("@bidstatus",SqlDbType.NVarChar),
};
#endregion
#region public member variables - stored procedures
//private string sp_Create = "bids_sp_insert";
//private string sp_Update_ByID = "bids_sp_updatebyid";
//private string sp_Delete_ByID = "bids_sp_deletebyid";
//private string sp_Select_All = "bids_sp_selectall";
//private string sp_Select_ByID = "bids_sp_selectbyid";
//private string sp_Select_ByValue = "bids_sp_selectbyvalue";
#endregion
#region constructors and destructors
public Bid()
{
if (this.ConnectionString == null)
{
this.ConnectionString = ConfigurationManager.ConnectionStrings["SQL.ConnectionString"].ConnectionString;
}
else
{
new Bid(this.ConnectionString);
}
}
public Bid(string ConnectionString)
{
this.ConnectionString = ConnectionString;
}
#endregion
#region methods & events
/// <summary>
/// Attach parameters to an SqlCommand
/// </summary>
/// <param name="sqlcmd">SqlCommand</param>
private void attachparameters(SqlCommand sqlcmd)
{
sqlcmd.Parameters.Clear();
foreach (SqlParameter param in _parameters)
{
sqlcmd.Parameters.Add(param);
}
}
/// <summary>
/// Assign value to the parameters
/// </summary>
/// <param name="sqlcmd">SqlCommand</param>
/// <param name="value">Client object</param>
private void assignparametervalues(SqlCommand sqlcmd, Bid value)
{
this.attachparameters(sqlcmd);
// todo: assign parameter values here
sqlcmd.Parameters[0].Value = value.Client;
sqlcmd.Parameters[1].Value = value.Contact;
sqlcmd.Parameters[2].Value = value.Sponsor;
sqlcmd.Parameters[3].Value = value.Priority;
sqlcmd.Parameters[4].Value = value.BidStatus;
}
public virtual void Create(Bid value)
{
_command = new SqlCommand();
_command.Connection = this.Connection;
_command.CommandText = @"INSERT INTO bids (client, contact, sponsor, priority, bidstatus, createdby)
VALUES (@client, @contact, @sponsor, @priority, @bidstatus, @createdby);";
_command.CommandType = CommandType.Text;
try
{
sqlda.InsertCommand = _command;
this.assignparametervalues(sqlda.InsertCommand, value);
sqlda.InsertCommand.Parameters.AddWithValue("@createdby", value.CreatedBy);
sqlda.InsertCommand.Parameters.Add("@bidid", SqlDbType.Int);
sqlda.InsertCommand.Parameters["@bidid"].Direction = ParameterDirection.ReturnValue;
sqlda.InsertCommand.Connection.Open();
sqlda.InsertCommand.ExecuteNonQuery();
sqlda.InsertCommand.Connection.Close();
sqlda.InsertCommand.Connection.Dispose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public virtual int Update(Bid value)
{
int result;
_command = new SqlCommand();
_command.Connection = this.Connection;
_command.CommandText = @"UPDATE bids SET client=@client, contact=@contact, sponsor=@sponsor,
priority=@priority, bidstatus=@bidstatus
WHERE bidid=@bidid";
_command.CommandType = CommandType.Text;
try
{
sqlda.UpdateCommand = _command;
using (SqlCommand _cmd = sqlda.UpdateCommand)
{
this.assignparametervalues(_cmd, value);
_cmd.Parameters.AddWithValue("@bidid", value.BidID);
_cmd.Connection.Open();
result = _cmd.ExecuteNonQuery();
_cmd.Connection.Close();
_cmd.Connection.Dispose();
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return result;
}
public virtual void Delete(Bid value)
{
_command = new SqlCommand();
_command.Connection = this.Connection;
_command.CommandText = @"DELETE bids WHERE bidid=@bidid;";
_command.CommandType = CommandType.Text;
try
{
sqlda.DeleteCommand = _command;
using (SqlCommand _cmd = sqlda.DeleteCommand)
{
_cmd.Parameters.AddWithValue("@bidid", value.BidID);
_cmd.Connection.Open();
_cmd.ExecuteNonQuery();
_cmd.Connection.Close();
_cmd.Connection.Dispose();
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public virtual DataSet List(string filter)
{
DataSet result = new DataSet();
_command = new SqlCommand();
_command.Connection = this.Connection;
_command.CommandText = @"SELECT * FROM bids_view_listall
WHERE (clientname like @value) or (contactname like @value)
or (sponsor like @value) ORDER BY datecreated DESC";
_command.CommandType = CommandType.Text;
try
{
if (filter != null)
{
filter = filter.Replace(" ", "%");
}
sqlda.SelectCommand = _command;
sqlda.SelectCommand.Parameters.AddWithValue("@value", "%" + filter + "%");
sqlda.Fill(result);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return (DataSet)result;
}
#endregion
#region IBid Members
public string Client
{
get
{
return _Client;
}
set
{
_Client = value;
}
}
public string Contact
{
get
{
return _Contact;
}
set
{
_Contact = value;
}
}
public string Sponsor
{
get
{
return _Sponsor;
}
set
{
_Sponsor = value;
}
}
public string Priority
{
get
{
return _Priority;
}
set
{
_Priority = value;
}
}
public string BidStatus
{
get
{
return _BidStatus;
}
set
{
_BidStatus = value;
}
}
#endregion
#region Properties
public int BidID
{
get
{ return _BidID; }
set
{ _BidID = value; }
}
public string DateCreated
{
get
{
return _DateCreated;
}
set
{
_DateCreated = value;
}
}
public string CreatedBy
{
get
{
return _CreatedBy;
}
set
{
_CreatedBy = value;
}
}
#endregion
}
}
_____________________________________________
Am I on a right path? I will greatly appreciate in any comments or suggestions for a design. I am creating an n-tier application and i don't know if my design is right. I dont have the proper schooling for creating this kind of applications and I am still on layer of data access and business logic.
View 5 Replies
View Related
Oct 27, 2006
I have a 3 tier merge replication architecture that I am having some problems making changes to. We have a 3 tier architecture with many laptop instances on the bottom tier. It is exceedingly hard to coordinate having all laptop instances online simultaneously during non business hours with users present for local administration. The requirement is to change the name of the top tier without effecting the replication from the middle to bottom tiers.
I have so far renamed the top tier's instance, reconfigured publication, created a new top tier publication, and a new middle tier subscription. The top tier article has been set to delete all data in the subscriber's table when applying the snapshot. Making data changes at the middle tier filters correctly to both the top and bottom tiers. However, any changes made at the top or bottom tiers filter to the middle tier but no further. At that point, there are no errors, but simply no data needing to be merged.
Does anyone know how dropping and recreating a top tier publication could cause a 3 tiered replication architecture to behave like this? Is there any solution short of having to recreate subscriptions on all the bottom tier laptops?
View 5 Replies
View Related
Mar 4, 2006
How can
ConfigurationManager.ConnectionStrings.Item("ConnectionString").ConnectionString
be embedded in the declaration of sqldatasource?
Thanks.
View 1 Replies
View Related
Jul 23, 2005
There doesn't seem to be consensus about when to put code in thedatabase or in the middle tier. There was a long discussion about thisin an Oracle newsgroup (message ID:ULcQb.466$KU5.37@nwrddc02.gnilink.net).Elsewhere there's been discussion about Microsoft SQL Server 2005adding the CLR to support stored procedures in languages such as C#. Ascan of the Web and discussion forums finds differing opinions aboutthis.Two authors have written articles that fall on different sides of thedebate."Keys to the Database"http://www.intelligententerprise.co...icleID=50500830"SOA, Multi-Tier Architectures and Logic in the Database"http://www.sqlsummit.com/Articles/L...TheDatabase.HTMJoe Celko wrote the first article, but his objections point toMicrosoft SQL Server 2005:"I have an article at WWSUG.com on how much I hate the CLR stuff thatMicrosoft is putting out."http://blog.intelligententerprise.c...ves/002419.html"The bad news is that SQL Server 2005 will you define your ownaggregatefunctions in a CLR language."Message id: Join Bytes!IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is thisa non-issue or are all three companies misguided?
View 49 Replies
View Related
Dec 26, 2007
Hi All,
Im trying to migrate a N tiered solution written in Java and Berkeley DB(in memory DB product) to MS stack. ie the in memory DB is being used as a middle tier caching solution.
Im considering using SQLCE where Berkely DB was being used, im guessing that SQLCE being an inproc DB may be faster than using SQLExpress etc.
Has any one tried to do this before? and if so can you share your experience?
Alternatively if you can provide any resources on the web that discuss SQLCE performance(ie not when run on WinCE etc)/ comparison of query performance between SQLCE and SQL Express/Standard that would be great.
Thanks in advance
-Melvin
View 1 Replies
View Related
Mar 7, 2007
Hello Everyone
I'm looking for outside opinion on how to implement the business layer of an application. The straight forward idea I know of would be to develop a component that encapsulates all the business rules under objects. Client applications would access this component, and the business component would talk directly to the database. This was great for me when I used Access databases.
Now I have been introduced to SQL server and the stored procedures. I think stored procedures are one of the best features because these procedures can enforce rules as sort of a "last line of defence". So this is where my question comes in. Where would the best place be to implement the business layer? Through a component, such as XML services that allow other clients to access the business rules OR stored procedures.
I do understand that components/dlls provide MUCH more flexibility then stored procedures.
But, if I do take the component route:
- Other then SQL queries that get called multiple times, is there any use for stored procedures?
- For some case scenarios such as web sites that access a Business Layer, should the business layer use ONE sql server login/role to access the data or create a sql login/role for each major segment/service? E.g. one login for the accounting side, one login for the
web site services, one side for HR and the business layer will have to decide which one to use depending on the service requested.
- Would it be in the best interest of security that I allow the business components to send SQL queuries such as Insert/Delete/Update? I loved the idea of stored procedures because if for some reason, someone steals the business layer userId/password, they are only exposed to the assigned stored procedures and cannot do "Delete From Table". Unfortunately, this practice has led to an abundance of stored procedures (the list is HUGE).
If I do take the Sql stored procedure route:
- If someone steals any user Id/password, they will only be exposed to the assigned stored procedures and THOSE stored procedures contain the business rules. Thus the damage can be minimized.
- If some developer makes a mistake down the road or forgets some sort of business aspect (and is not going through the business layer), again, stored procedures would offer a last "line of defense". Lets face it, there are some applications that do not necessary require a business layer
I'd like to get everyone's opinion on how they partition the business rules with SQL server? Do you use SQL server stored procedures to only enforce formating rules (such as upper case, lower, etc) while the business rules are stored in a component?
Do you use stored procedures to enforce all business rules? None?
Thank you for your time,
Adrian
View 7 Replies
View Related
Oct 27, 2015
I want to create one bacpac file but it does fails, Need I delete all the descriptions before to begin? How odd! I don't understand why can we choose some scripting options such as we did on 'Generate Scripts' task..I get errors such as: dbo.table.field.Ms_Description is not supported when used as part of a data package
View 2 Replies
View Related
May 6, 2007
In case some of you have read my previous posts, you may be aware that I'm writing a webboard application as a replacement for the old one.The old one currently have approximately 50000 topics in the database, each has on average 10 replies (I just check recently. I though it was only 7000 topics).I need to provide paging and sorting feature to the topic list. But I can't just SELECT all of them and let GridView do the paging/sorting, right?I have been using stored procedures to store my SQL statement for several projects now. I know how to deal with the paging feature (ROW_NUMBER), but the sorting requires me to change to change the "ORDER BY" clause.1. Can somebody tell me how to change the ORDER BY clause in the stored procedure(s) at runtime? Or does anyone have other approach?
Currently I'm thinking about moving back from store procedures to hard-code SQL statements, and then modify/generate the SQL statement for each paging / sorting. But I've learn that stored procedures give more performance and security.2. According to the situation I provided, is it worth moving from stored procedures to hard-code SQL?I'm also using 3-tier architecture approach + OOP. But I reach a conflict in my thoughts. You see, according to OOP, I'm supposed to create classes that reflect the actual objects in the real-world, right? In my case the classes are "Board, Topic, Reply, ...." According to this and 3-tier approach, I intend to use ObjectDataSource as a bridge between Presentation Logic and Business Logic. But I wonder what my datasource class should return3. Should my data source class return data objects like1st approach[DataObject(True)]pubic class TopicDataSource{ public static Topic[] GetTopicList() { }}or should it return DataSet / DataTable / DataReader like2nd approach [DataObject(True)]public class TopicDataSource{ public static DataTable GetTopicList() {}}Personally I think approach 1 is more OOP and allow for more extendability, but approach 2 might be faster.4. If I go with approach 1, how should I control which property of my data objects is read-only after it's has been inserted/created? Can I just set my data object's property to be readonly? Or do I have to set it at page level (i.e. GridView-> Columns -> BoundField -> ReadOnly=True)? Or do I set it and the page level and write a code to throw an exception in the rare case the application / user try to change it's value? Or else?Please help. These questions slow me down for days now.If there's any concepts that I misunderstood, please tell me. I'm aware that I don't know as much as some of you.I will be extremely grateful to anyone who answer any of my questions.Thanks a lot.PS. For those who think my questions are stupid, I'm very, very sorry that I bother you.
View 3 Replies
View Related
Sep 19, 2006
Hello :)
I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?
SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL
View 8 Replies
View Related
Jul 20, 2005
Hello all,I have a database in SQL Server that should save data from a CRM-likeapplication.The database consists of tables like products, services, customers,partners etc. Problem is that the users should be able to find theseitems on different properties and with or without substring finding(SQL: LIKE). Example: I want the users to be able to find a customer,providing a customerID, but also providing a customername, zipcode orjust a part of those strings.This will result in a lot of queries. I bet there are some nicesolutions to this, since I will not be the first with this situation.If anyone can help, please.Thank you in advance.Regards,Freek Versteijn
View 3 Replies
View Related
May 21, 2004
Apparently, deleting 7,000,000 records from a table of about 20,000,000 is not advisable. We were able to take orders at 8:00AM, but not at 7:59.
So, what's the best way of going about deleting a large number of records? Pretty basic lookup table, no relationships with other tables, 12 or so address-type fields, 4 or 5 simple indexes. I can take it down for a weekend or night, if needed.
DTS the ones to keep to another table, drop the old and rename the new table?
Bulk copy out, truncate and bring back in?
DTS to text, truncate and import back?
Other ways?
Never worked with such a large table and need a little experienced guidance.
Thanks for the help
View 1 Replies
View Related
May 25, 2004
I am planning an application where ~1000 companies will be accessing data. Should I use a key to identify the company and place all data in one table i.e (WHERE company =123) or should the application create company specific tables i.e should I have 1000 small tables with 100 records in each, or one table with 100,000 records?
View 2 Replies
View Related
May 7, 2008
Hi,
You know how there are lots of hosted applications out there, many of them provide you with your own database (not shared).
1. If a server has 1K databases on it, will this slow down the server just due to the # of databases? (each user has their own database, but they won't be accessing it that much really).
A seperate database is required for security purposes usually.
2. Can you still open up EM with 1K+ databases?
View 3 Replies
View Related
Jan 30, 2007
Hi all,
I have a strange situation. Performance monitor shows that SQLServer:Transactions Transactions value is 125, but SQL Server Profiler does not show any activity.
I ran sp_who2 and I have a bunch of processes with SUSPENDED status. Would those be counted in Performance monitor?
When does a transaction get suspended?
Thanks.
Alec
View 1 Replies
View Related
Sep 20, 2007
I used MS SQL Server Management Studio Express to back up my SQL Server 2005 database called "PMDB" on a server in my office to a jump drive. Then, I removed the jump drive from the server and plugged it into my laptop. I then tried using MS SQL Server Management Studio Express to restore "PMDB" to my laptop SQL Server 2005 Express Edition (instance "Primavera") to a database called "pmdb$primavera". I've had several issues:
1. pmdb$primavera is now "locked up" "in the middle of a restore". I can't seem to unlock it even by rebooting my laptop.
2. when I execute the restore (I've used several command sequences...) I get a message that there are additional "families" restore is waiting for. I don't understand that.
3. I tried restoring to a completely new database (in the same instance...), but got the "family" problem. The query just hangs forever. When I cancel it, it hangs the database (see #1).
4. I now have two databases in the "Primavera" instance on my laptop in the "restoring" state. I can't get to either one of them.
I'm desperate. I have a customer presentation on Thursday where I must have the database working. Help!
Here's one of the queries I executed.... though I tweaked items here and there for 3 and 4 above.
restore database pmdb
from disk='f:databasebackupPMDB-BAK.BAK'
with recovery,
move 'pmdb_Dat' to 'C:Program FilesMSSQLPrimaveraMSSQL.1MSSQLDATApmdb$primavera_DAT.MDF',
move 'pmdb_log' to 'C:Program FilesMSSQLPrimaveraMSSQL.1MSSQLDATApmdb$primavera_LOG.LDF'
go
The issue seems to be #2 above each time.
View 4 Replies
View Related
Nov 14, 2007
Every time a transaction log is dumped we see the following message in the log file:
BackupDiskFile:penMedia: Backup device '\s-sqlbkups-1g$myserverlogmy_databasemydatabase_backup_200711071430.trn' failed to open. Operating system error 2(The system cannot find the file specified.).
Source spid139
Message
Error: 18204, Severity: 16, State: 1.
And yet, the actual log dump appears fine and the file is found on the share. The dump is done with a maintenance plan.
Any ideas?
View 2 Replies
View Related
Jul 28, 2015
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
View 5 Replies
View Related
Mar 6, 2008
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies
View Related
Sep 8, 2006
I have a page that has about 8 dropdown boxes that need to be populated from sql tables. What is the best way to populate these boxes. Here is how I have it nowconn = New SqlConnection(ConfigurationManager.AppSettings("SQLString")) ''''''''''''' Fill in DropDownList Status '''''''''''''''''''strSelect = "SELECT * FROM Requests_Status"cmdSelect = New SqlCommand(strSelect, conn) conn.Open()dtrSearch = cmdSelect.ExecuteReader()ddlRequestStatus.DataSource = dtrSearchddlRequestStatus.DataTextField = "RequestStatusName"ddlRequestStatus.DataValueField = "RequestStatus"ddlRequestStatus.DataBind()ddlRequestStatus.Items.Insert(0, New ListItem("-- Select Below --", -1)) cmdSelect.Cancel()dtrSearch.Close()conn.Close()''''''''''''' Fill in DropDownList Container '''''''''''''''''''strSelect = "SELECT * FROM Containers"cmdSelect = New SqlCommand(strSelect, conn) conn.Open()dtrSearch = cmdSelect.ExecuteReader()ddlContainer.DataSource = dtrSearchddlContainer.DataTextField = "ContainerName"ddlContainer.DataValueField = "ContainerID"ddlContainer.DataBind()ddlContainer.Items.Insert(0, New ListItem("-- Select Below --", -1)) cmdSelect.Cancel()dtrSearch.Close()conn.Close()'''''''''''''''''''''''''''''I then repeat the same commands as above for the other 6 dropdowns. This seems like a bad way to have to do all this.ThanksCraig
View 3 Replies
View Related
Apr 26, 2007
I have a database where several thousand records have NULL in a binary field. I want to change all the NULLs to false. I have Visual Studio 5, and the database is a SQL Server 5 database on a remote server. What is the easiest way to do this? Is there a query I can run that will set all ReNew to false where ReNew is Null? This is a live database so I want to get it right. I can't afford to mess it up.Diane
View 2 Replies
View Related
Aug 18, 2006
have a dts package that does txt -> sql server.
i have 200 txt files with the same exact format.
just want to know if i can write a SP passing a parameter that loads this txt files. because i dont wanna create 200 packages or 200 sources to load 200 txt files.
say:
exec SP_loadTXT txt1
or should i use bulk insert?
any approaches are fine. any suggestions are fine too.
View 14 Replies
View Related
