architecture question

I asked this in the C# forum and got some input from some developers, now
I'm looking for some input from DBA's.
thanks
I'm working on a project and the lead developer wants to put all of the
business logic on the database side. He wants the proc to do all of the
logic and create XML on the fly, parse out data, etc, and then return the
final result to the business layer. I pushed back and said that the business
layer should do all of the logic, just have SQL return the data needed and
then have the code do all of the XML creation, parsing of data, etc. I was
told I was wrong and thats not the best practice.
Now, this app can hit the db by a total of 100-500 users a day, and having
the db do all of the processing can have a performance hit. Then he
mentioned something about using temp table in the database side, do a
select, then insert into the temp table, then do a select on the temp table
and do the business logic. I pushed back on that as well..
Now, I'm not a DBA but a developer and I've seen DB's take performance hits
when doing logic such as this on the database side, so my question is, which
would be the best solution for this?Hi
"Jon" wrote:
> I asked this in the C# forum and got some input from some developers, now
> I'm looking for some input from DBA's.
> thanks
> I'm working on a project and the lead developer wants to put all of the
> business logic on the database side. He wants the proc to do all of the
> logic and create XML on the fly, parse out data, etc, and then return the
> final result to the business layer. I pushed back and said that the business
> layer should do all of the logic, just have SQL return the data needed and
> then have the code do all of the XML creation, parsing of data, etc. I was
> told I was wrong and thats not the best practice.
> Now, this app can hit the db by a total of 100-500 users a day, and having
> the db do all of the processing can have a performance hit. Then he
> mentioned something about using temp table in the database side, do a
> select, then insert into the temp table, then do a select on the temp table
> and do the business logic. I pushed back on that as well..
> Now, I'm not a DBA but a developer and I've seen DB's take performance hits
> when doing logic such as this on the database side, so my question is, which
> would be the best solution for this?
>
You may want to look at http://msdn2.microsoft.com/en-us/library/ms973279.aspx
With client-server applications you can put business logic into the
database, but implementing a n-tier architecture you are moving away from
this and the business logic resides in one of the tiers.
As far as XML is concerned SQL Server can create and store XML data, you
would need to determine if the interface would work be better as a recordset
or XML and how much load the different methods would create.
John

architecture question

I asked this in the C# forum and got some input from some developers, now
I'm looking for some input from DBA's.
thanks
I'm working on a project and the lead developer wants to put all of the
business logic on the database side. He wants the proc to do all of the
logic and create XML on the fly, parse out data, etc, and then return the
final result to the business layer. I pushed back and said that the business
layer should do all of the logic, just have SQL return the data needed and
then have the code do all of the XML creation, parsing of data, etc. I was
told I was wrong and thats not the best practice.
Now, this app can hit the db by a total of 100-500 users a day, and having
the db do all of the processing can have a performance hit. Then he
mentioned something about using temp table in the database side, do a
select, then insert into the temp table, then do a select on the temp table
and do the business logic. I pushed back on that as well..
Now, I'm not a DBA but a developer and I've seen DB's take performance hits
when doing logic such as this on the database side, so my question is, which
would be the best solution for this?
Hi
"Jon" wrote:

> I asked this in the C# forum and got some input from some developers, now
> I'm looking for some input from DBA's.
> thanks
> I'm working on a project and the lead developer wants to put all of the
> business logic on the database side. He wants the proc to do all of the
> logic and create XML on the fly, parse out data, etc, and then return the
> final result to the business layer. I pushed back and said that the business
> layer should do all of the logic, just have SQL return the data needed and
> then have the code do all of the XML creation, parsing of data, etc. I was
> told I was wrong and thats not the best practice.
> Now, this app can hit the db by a total of 100-500 users a day, and having
> the db do all of the processing can have a performance hit. Then he
> mentioned something about using temp table in the database side, do a
> select, then insert into the temp table, then do a select on the temp table
> and do the business logic. I pushed back on that as well..
> Now, I'm not a DBA but a developer and I've seen DB's take performance hits
> when doing logic such as this on the database side, so my question is, which
> would be the best solution for this?
>
You may want to look at http://msdn2.microsoft.com/en-us/library/ms973279.aspx
With client-server applications you can put business logic into the
database, but implementing a n-tier architecture you are moving away from
this and the business logic resides in one of the tiers.
As far as XML is concerned SQL Server can create and store XML data, you
would need to determine if the interface would work be better as a recordset
or XML and how much load the different methods would create.
John

architecture question

I asked this in the C# forum and got some input from some developers, now
I'm looking for some input from DBA's.
thanks
I'm working on a project and the lead developer wants to put all of the
business logic on the database side. He wants the proc to do all of the
logic and create XML on the fly, parse out data, etc, and then return the
final result to the business layer. I pushed back and said that the business
layer should do all of the logic, just have SQL return the data needed and
then have the code do all of the XML creation, parsing of data, etc. I was
told I was wrong and thats not the best practice.
Now, this app can hit the db by a total of 100-500 users a day, and having
the db do all of the processing can have a performance hit. Then he
mentioned something about using temp table in the database side, do a
select, then insert into the temp table, then do a select on the temp table
and do the business logic. I pushed back on that as well..
Now, I'm not a DBA but a developer and I've seen DB's take performance hits
when doing logic such as this on the database side, so my question is, which
would be the best solution for this?Hi
"Jon" wrote:

> I asked this in the C# forum and got some input from some developers, now
> I'm looking for some input from DBA's.
> thanks
> I'm working on a project and the lead developer wants to put all of the
> business logic on the database side. He wants the proc to do all of the
> logic and create XML on the fly, parse out data, etc, and then return the
> final result to the business layer. I pushed back and said that the busine
ss
> layer should do all of the logic, just have SQL return the data needed and
> then have the code do all of the XML creation, parsing of data, etc. I was
> told I was wrong and thats not the best practice.
> Now, this app can hit the db by a total of 100-500 users a day, and having
> the db do all of the processing can have a performance hit. Then he
> mentioned something about using temp table in the database side, do a
> select, then insert into the temp table, then do a select on the temp tabl
e
> and do the business logic. I pushed back on that as well..
> Now, I'm not a DBA but a developer and I've seen DB's take performance hit
s
> when doing logic such as this on the database side, so my question is, whi
ch
> would be the best solution for this?
>
You may want to look at [url]http://msdn2.microsoft.com/en-us/library/ms973279.aspx[/ur
l]
With client-server applications you can put business logic into the
database, but implementing a n-tier architecture you are moving away from
this and the business logic resides in one of the tiers.
As far as XML is concerned SQL Server can create and store XML data, you
would need to determine if the interface would work be better as a recordset
or XML and how much load the different methods would create.
Johnsql

Approach Help to load Data from Flatfiles into relational table where Data is coming as spaces i

Hi ,

My Input is a flat file source and it has spaces in few columns in the data . These columns are linked to another table as a foreign key and when i try loading them in a relational structure Foreigh key violation is occuring , is there a standard method to replace these spaces .

what approach should i take so that data gets loaded in a relational structure.

for example

Name Age Salary Address

dsds 23 fghghgh

Salary description level

2345 nnncncn 4

here salary is used in this example , the datatype is char in real scenario

what approach should i take to load the data in with cleansing the spaces in ssis

Assumption: You don't have any control over the structure of the destination tables, but you can add records.

To maintain your foreign key constraint, you will need to create a "salary" record that you can use where salary is not defined. EG:

Salary Description Level

9999 Not Assigned 1

Then you can use a Derived Column expression to create a new column in your dataset that uses an If expression and then write that derived column to your destination table

Like this:

Code Snippet

[Salary]=="" ? 9999 : [Salary]

This is not an optimal solution (even though it is very practical.) Ideally you should restructure your tables so:

Assumption: You can change the structure of your destination tables

In this case, you need to take the Salary column out of your destination table, and put it into another table. The relationship is that employees can have zero or one salary code. So you can create a table relating employees to salary codes with two columns (EmployeeNumber, SalaryCode) and a primary key on EmployeeCode.

(This is a drastic simplification, of course. In a real-world scenario you would probably want to track historical information too.)

|||

Thanks for the Quick reply i tried doing as you said using derived transformation and using replace function but it doesnot seem to capture the space as value in the replace expression in ssis.

Replace(salary," ",9999) it does not work it says that there might be a possibility of divide by zero error. what expression should i use here to replace the values

|||

first of all, since this is a char value, you should put quotes around the 9999.

Seond, I didn't say to use the REPLACE function, though I guess that would work, but you run the risk of having a valid id messed up by replacing a leading or trailing space with a 9999.

Also, if your Salary code was " " (eight spaces) then the REPLACE function will put in:

"99999999999999999999999999999999" which migh be too big for your destination field.

So, try using TRIM first to get ris of spaces, then ?: to replace blank with 9999.

Put this into the Expression column of Derived Value step:

Code Snippet

TRIM([SALARY])=="" ? "9999" : TRIM([Salary])

sql

Apostrophes and Dynamic SQL

I am trying to Ad-hoc query from a user input(internal app), where the user
enters in a name just as "Al's Game Crazy". Each time I do this, the
Apostrophe gets in the way. I have tried using the "char(39)" setup for wit
h
the like statement. But I keep getting and error.
Error returned:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Code:
set nocount on
Declare @.SQL_Where varchar(1000)
Declare @.Brand varchar(50)
Declare @.pos int
Declare @.SQL varchar(2000)
--Set @.Brand = 'Al' + char(39) +'s Game Crazy'
Set @.Brand = 'Al''s Game Crazy'
SET @.SQL_Where = ''
-- Check Brand
if Len(rtrim(ltrim(@.Brand))) > 0
BEGIN
SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
if @.pos > 0
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
Substring(@.Brand, @.pos + 1, len(@.Brand))
SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand +
'%' + char(39) + ' and'
END
-- Remove the last part of the where clause filter " and"
SET @.SQL_Where = rtrim(@.SQL_Where)
SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
select @.Brand
select @.SQL_Where
SET @.SQL = 'select top 3000 * from view_ContactLocation where' + @.SQL_Where
select @.SQL
exec (@.SQL)
set nocount off
Any help or direction would be appreciated.
Thanks,
ScottTry replacing occurrances of the single apostrophe ' with double apostrophe
''
"Scott Heffron" <ScottHeffron@.discussions.microsoft.com> wrote in message
news:C79CAF92-5466-4B06-B136-7C7970B93BBA@.microsoft.com...
>I am trying to Ad-hoc query from a user input(internal app), where the user
> enters in a name just as "Al's Game Crazy". Each time I do this, the
> Apostrophe gets in the way. I have tried using the "char(39)" setup for
> with
> the like statement. But I keep getting and error.
> Error returned:
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ''.
> Code:
> set nocount on
> Declare @.SQL_Where varchar(1000)
> Declare @.Brand varchar(50)
> Declare @.pos int
> Declare @.SQL varchar(2000)
> --Set @.Brand = 'Al' + char(39) +'s Game Crazy'
> Set @.Brand = 'Al''s Game Crazy'
> SET @.SQL_Where = ''
> -- Check Brand
> if Len(rtrim(ltrim(@.Brand))) > 0
> BEGIN
> SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
> if @.pos > 0
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand
> +
> '%' + char(39) + ' and'
> END
> -- Remove the last part of the where clause filter " and"
> SET @.SQL_Where = rtrim(@.SQL_Where)
> SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
> select @.Brand
> select @.SQL_Where
> SET @.SQL = 'select top 3000 * from view_ContactLocation where' +
> @.SQL_Where
> select @.SQL
> exec (@.SQL)
> set nocount off
>
> Any help or direction would be appreciated.
> Thanks,
> Scott
>
>|||JT, I tried the modifiying the following:
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) + Substring(@.Brand,
@.pos + 1, len(@.Brand))
to
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + '''' + Substring(@.Brand, @.pos
+ 1, len(@.Brand))
There appears to be no difference. I get the same errors. I believe that
is where you were thinking about doing the double "'".
Thanks,
Scott|||Scott Heffron wrote:
> JT, I tried the modifiying the following:
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> to
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + '''' +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> There appears to be no difference. I get the same errors. I believe
> that is where you were thinking about doing the double "'".
> Thanks,
> Scott
No. He's talking about using the REPLACE function to replace the apostrophe
with two apostrophes:
SET @.Brand=REPLACE(@.Brand,'''',''')
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You need to double-up the apostrophe to pass it into the stored procedure.
So from the app, the call need to look like this:
EXEC dbo.MyProc @.Brand = 'Al''s Game Crazy'
This doubling-up allows the stored procedure to be called successfully, as
the double apostrophe acts as an escape mechanism instead of ending the
string early. Now when you get the string into the stored procedure, it's
back to a single apostrophe only. So, if you are trying to put this string
into a string inside the stored procedure, you need to double them up again.
Of course, inside of SQL, you need to escape all instances of a quote. So,
try:
SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' +
REPLACE(@.Brand, '''',''') +
'%' + char(39) + ' and' -- shudder... why add an AND unless you know there
is more WHERE?
Please see the following.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
I think with some work you could streamline this process quite a bit and
make it far less hairy-looking. I'm not going to address the @.sql and
@.sql_where, because I have no idea if and why end users are able to write
your sql statements for you. But for the wildcard search alone, you could
do this:
CREATE PROCEDURE dbo.SearchContacts
@.Brand VARCHAR(50) = ''
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 3000 <column list! don't use *>
FROM View_ContactLocation
WHERE Brand LIKE '%'+@.Brand+'%';
END
GO
"Scott Heffron" <ScottHeffron@.discussions.microsoft.com> wrote in message
news:C79CAF92-5466-4B06-B136-7C7970B93BBA@.microsoft.com...
>I am trying to Ad-hoc query from a user input(internal app), where the user
> enters in a name just as "Al's Game Crazy". Each time I do this, the
> Apostrophe gets in the way. I have tried using the "char(39)" setup for
> with
> the like statement. But I keep getting and error.
> Error returned:
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ''.
> Code:
> set nocount on
> Declare @.SQL_Where varchar(1000)
> Declare @.Brand varchar(50)
> Declare @.pos int
> Declare @.SQL varchar(2000)
> --Set @.Brand = 'Al' + char(39) +'s Game Crazy'
> Set @.Brand = 'Al''s Game Crazy'
> SET @.SQL_Where = ''
> -- Check Brand
> if Len(rtrim(ltrim(@.Brand))) > 0
> BEGIN
> SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
> if @.pos > 0
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand
> +
> '%' + char(39) + ' and'
> END
> -- Remove the last part of the where clause filter " and"
> SET @.SQL_Where = rtrim(@.SQL_Where)
> SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
> select @.Brand
> select @.SQL_Where
> SET @.SQL = 'select top 3000 * from view_ContactLocation where' +
> @.SQL_Where
> select @.SQL
> exec (@.SQL)
> set nocount off
>
> Any help or direction would be appreciated.
> Thanks,
> Scott
>
>|||Thanks, it worked.|||There are several columns that can be used in the where clause that is the
reason for the "and" at the end. Not all the columns are used. I did not
want to do a search on column1 = '%%' if nothing was in the @.column1
variable. I am expecting that is wasting query time and not needed. Sorry,
I used the "*" to save space. You are absolutely right on using the column
names.
What the variable will look like from the users application is "Al's Game
Crazy"
There are 9 different variables. Is it better to allow for a search on '%%'
or leave it out and create the where clause dynamically?
Thanks,
Scott|||> There are 9 different variables. Is it better to allow for a search on
> '%%'
> or leave it out and create the where clause dynamically?
Have you read the following article? I think it applies quite nicely to
your situation. It looks like a long read, but I think you'll be glad you
did it.
http://www.sommarskog.se/dyn-search.html|||Scott Heffron (ScottHeffron@.discussions.microsoft.com) writes:
> I am trying to Ad-hoc query from a user input(internal app), where the
> user enters in a name just as "Al's Game Crazy". Each time I do this,
> the Apostrophe gets in the way. I have tried using the "char(39)" setup
> for with the like statement. But I keep getting and error.
Rather than building the entire SQL string, use sp_executesql instead.
Look at http://www.sommarskog.se/dyn-search.html#sp_executesql for
an example. (Further below there is also an example that uses EXEC()
and deals with strings in a structured way.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973B3B229ACAYazorman@.127.0.0.1...

> Rather than building the entire SQL string, use sp_executesql instead.
> Look at http://www.sommarskog.se/dyn-search.html#sp_executesql for
> an example. (Further below there is also an example that uses EXEC()
> and deals with strings in a structured way.)
Other way is to use some tool that will do all job for you (provide your use
r
with friendly interface to create queries and generate SQL statement in resu
lt).
We prefer EasyQuery (http://devtools.korzh.com/eq/) but I think there are so
me
similar products even free ones.
With the best regards, Nik.