Code In The Database Or Middle Tier (the CLR Controversy)
Jul 23, 2005
There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
ULcQb.466$KU5.37@nwrddc02.gnilink.net).
Elsewhere there's been discussion about Microsoft SQL Server 2005
adding the CLR to support stored procedures in languages such as C#. A
scan of the Web and discussion forums finds differing opinions about
this.
Two authors have written articles that fall on different sides of the
debate.
"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.HTM
Joe Celko wrote the first article, but his objections point to
Microsoft SQL Server 2005:
"I have an article at WWSUG.com on how much I hate the CLR stuff that
Microsoft is putting out."
http://blog.intelligententerprise.c...ves/002419.html
"The bad news is that SQL Server 2005 will you define your own
aggregate
functions in a CLR language."
Message id: Join Bytes!
IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is this
a non-issue or are all three companies misguided?
View 49 Replies
ADVERTISEMENT
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
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
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
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
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 4, 2006
How can
ConfigurationManager.ConnectionStrings.Item("ConnectionString").ConnectionString
be embedded in the declaration of sqldatasource?
Thanks.
View 1 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
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
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
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
Feb 14, 2008
Hi all,
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
........................
etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Please help and advise.
Thanks,
Scott Chang
View 6 Replies
View Related
Sep 21, 2007
Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?
View 9 Replies
View Related
Oct 25, 2007
I have a table Customer with 200000 records.
When I run query
DECLARE @posTable TABLE(idkey INT IDENTITY(1,1), value VARCHAR(128))
INSERT INTO @posTable (value) SELECT CustID FROM Customer ORDER BY CustID
At the first time it takes 1 minute 42 seconds, at the second time it only takes 3 seconds.
When i run the query
SELECT IDENTITY(INT, 1, 1) AS idkey, CustID INTO #posTable FROM Customer ORDER BY CustID
The order in column idkey is not exacts
Pls. tell me why? and How can i rewrite it.
View 8 Replies
View Related
Dec 7, 2006
I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine.
However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example:
"SELECT TOP 20 to 40 * FROM Northwind"
Hopefully this makes sense. Is there any way of doing this?
Thanks in advance,Russ
View 2 Replies
View Related
Dec 29, 2003
I am trying to concatenate three fields into one in a View. I have the following data:
Last Name
First Name
Middle Initial (can be null)
I need my resultant field data to look like the following:
"Doe, John P."
I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the following: "Doe, John".
I have tried the following CASE statement:
CASE WHEN middleInitial IS NOT NULL THEN ' ' + middleInitial + '.' ELSE '' END
However, I get an error indicating that the CASE statement is not supported in the Query Designer.
How can I resolve this problem in a View? Is there a function similar to ISNULL(middleInitial, '') that would allow for the "."?
View 4 Replies
View Related
Sep 9, 2005
Lets say I have a column of type varchar and need to extract an integer value from the middle of it. The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?
View 2 Replies
View Related
Dec 12, 2001
Is it possible to use a TRIM(){or similar} function when JOINing 2 tables via a NVARCHAR field in T-SQL?
I need to trim the field first because some of the data contains trailing spaces (GRRR) and so might future data...
View 2 Replies
View Related
May 3, 2006
So I have this query where i need to get the average date of about five different dates... Is there any way to do this or am I screwed. I looked at using the avg function but SQL server 2005 did not like that.
Thanks in advance for any help.
View 3 Replies
View Related
Apr 5, 2007
Im having a dumb moment... what is the easiest way to get the third and fourth character of a string???
Thanks :)
View 7 Replies
View Related
Nov 15, 2013
I need to display the middle initial from a name field that contains the last name, comma, and the middle name or initial.
Example data:
Jane,Smith Ron
John,Dow L
Mary Jane,Dow Welsh
The result I am looking for is to capture only the first letter of the middle name. In this data example, I would need to display the following on a separate column:
R
L
W
View 5 Replies
View Related
Jan 7, 2004
-- I have a first name field that sometimes contains only the first name,
-- sometimes contains the first name and the middle initial, and
-- sometimes contains the first name, a space, followed by NMI (for no middle initial)
-- how do I correctly grab the first letter of the middle initial in all cases?
-- I have been playing with patindex but its harder than I thought. guess I need a case
-- statement in addition to this. Any idea how I can do this?
-- thanks!
create table UHS_t1 (c1 varchar(20))
insert UHS_t1 select 'john a'
insert UHS_t1 select 'jeff b'
insert UHS_t1 select 'sue z'
insert UHS_t1 select 'joe nmi'
insert UHS_t1 select 'jamie'
select *, substring(c1, patindex('%[ ]%', c1)+1, 1) as middle_name
from UHS_t1
go
drop table UHS_t1
View 10 Replies
View Related
Aug 13, 2012
How do I stop the restore in the middle?
restore database blah
from disk='mybackups hismonthbackup1.BAK'
with replace, recovery, stats=5, maxtransfersize=1048576
View 3 Replies
View Related
Jun 20, 2006
In a table I have a coulmn of postal-codes where there is a space in the middle of the postcode. How do I do to trim them away?
View 6 Replies
View Related
Feb 26, 2007
khosara writes "I have one parameter @String with value "My name is Khosara".
How to get the value only "Is khos".
Could you please help me, witch method shold i use.
Thank in advance."
View 3 Replies
View Related
Nov 16, 2007
How do you get the following to work properly?
WHERE Location = 'John's House'
The apostrophe between n and s has me stumped. Thanks.
View 6 Replies
View Related
Apr 14, 2008
Hello The following command is for Name Split as First Name and Second Name. Please let me know based on the following only how to get Middle name. SELECT name,
CASE WHEN CHARINDEX(',',Name) > 0 THEN SUBSTRING(Name,CHARINDEX(',',Name)+1,LEN(Name)-CHARINDEX(',',Name)) ELSE '' END AS Fname,
CASE WHEN CHARINDEX(',',Name) > 0 THEN LEFT(Name,CHARINDEX(',',Name)-1) ELSE Name
END AS Lname
FROM YourTable
View 1 Replies
View Related
Oct 26, 2006
Some of the databases that I inherited contain search that are based on finding a string anywhere within a last name such as:
WHERE lastName like '%smith%'
It is desired that each of these names be returned:
Smith
Smithson
Nesmith
What is presently done is that updates to the last name fields trigger that substrings of the last name be sent off into a substring table wtih retention of no 2-char substrings. For these three last names the following would be kept:
(1) Smith, (2) mith, (3) ith and (4) th
(1) Smithson, (2) mithson, (3) ithson, ..., (n) on
(1) Nesmith, (2) esmith, (3) smith, ... (n) th
The where now becomes
WHERE lastNameSub like 'smith%'
This seems to make the search routine by last name faster. I would like to improve on this if I can. Suggestions?
Dave
View 1 Replies
View Related
