Easy way to update multiple databases

SQL 2K, Win 2K
My company is in the process of developing a new app that will have a lot of
client specific databases (30 to 50) with identical schemas and stored
procedures. My job as the DBA will be to make the schema and SP changes to
all of these when we have updates.
I have a similar app now that I have a script with several Use Database
commands and I just paste the changes in between the Use Database commands,
but I only have to do this for a few databases.
Does anybody know of a way to automate this with scripts or a tool to apply
the schema and SO change to all user databases on a server.
Thanks.
MikePerhaps DB Ghost would fill your needs.
www.dbghost.com
or
http://www.innovartis.co.uk/Home.aspx
-- Keith
"Mike" <Mike@.Comcast.net> wrote in message =news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> > My company is in the process of developing a new app that will have a =lot of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP =changes to
> all of these when we have updates.
> > I have a similar app now that I have a script with several Use =Database
> commands and I just paste the changes in between the Use Database =commands,
> but I only have to do this for a few databases.
> > Does anybody know of a way to automate this with scripts or a tool to =apply
> the schema and SO change to all user databases on a server.
> > Thanks.
> > Mike
> >|||Hi
If you are using a version control system to maintain your database code.
The most obvious way to upgrade would be use scripts called from osql/isql
or possibly DMO.
John
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||Check out if www.red-gate.com or www.dbghost.com does what you want.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||I wrote a little vb app that gets a list of db's to apply
changes to. you just paste the SQL syntax (ie "Alter
Table x add constraint y...") and click go, and it runs
the command on all dbs. I populate a simple table w/ the
names of db's to run the command against.
Also, I put together an app that uses a template database
(with the tables, sp's, triggers, that you want), and
pushes the schema out to a list of databases (in my case,
that list is in a table).
If you want the VB code for either, just let me know.
>--Original Message--
>SQL 2K, Win 2K
>My company is in the process of developing a new app that
will have a lot of
>client specific databases (30 to 50) with identical
schemas and stored
>procedures. My job as the DBA will be to make the schema
and SP changes to
>all of these when we have updates.
> I have a similar app now that I have a script with
several Use Database
>commands and I just paste the changes in between the Use
Database commands,
>but I only have to do this for a few databases.
>Does anybody know of a way to automate this with scripts
or a tool to apply
>the schema and SO change to all user databases on a
server.
>Thanks.
>Mike
>
>.
>|||I would appreciate it if you could share the app with me. It doesn't look
like DBGhost or SQL Compare will do it.
Thanks.
Mike
"Gene Daigle" <anonymous@.discussions.microsoft.com> wrote in message
news:024101c3bb73$7b7e3e10$a401280a@.phx.gbl...
> I wrote a little vb app that gets a list of db's to apply
> changes to. you just paste the SQL syntax (ie "Alter
> Table x add constraint y...") and click go, and it runs
> the command on all dbs. I populate a simple table w/ the
> names of db's to run the command against.
> Also, I put together an app that uses a template database
> (with the tables, sp's, triggers, that you want), and
> pushes the schema out to a list of databases (in my case,
> that list is in a table).
> If you want the VB code for either, just let me know.
>
> >--Original Message--
> >SQL 2K, Win 2K
> >
> >My company is in the process of developing a new app that
> will have a lot of
> >client specific databases (30 to 50) with identical
> schemas and stored
> >procedures. My job as the DBA will be to make the schema
> and SP changes to
> >all of these when we have updates.
> >
> > I have a similar app now that I have a script with
> several Use Database
> >commands and I just paste the changes in between the Use
> Database commands,
> >but I only have to do this for a few databases.
> >
> >Does anybody know of a way to automate this with scripts
> or a tool to apply
> >the schema and SO change to all user databases on a
> server.
> >
> >Thanks.
> >
> >Mike
> >
> >
> >.
> >

Dynamicallly Importing csv file into MS SQL

I have an app written in delphi v5 with a MS SQL Database and I'm struggling with an import feature i need

The user needs a function to import Contact data from a txt/csv file into the Contact Table

Details of feature:
the user enters the different parameters into the Delphi app e.g.
File Location
Files Includes Column Headings or not
Whether the file Comma or Tab Seperated
Mapping the fields

Then i need to use those parameters and run some sort of import routine putting the data into the specific table.

The tables consists of 3 fields - First Name, Surname, Mob Number - but these fields can be in any order in the file. for example Col 1 (in file) needs to go into Field 3 in DB. this is sorted in the Mapping Fields above

How do I do this using MS SQL??

I've been looking at using the BULK INSERT command e.g
BULK INSERT Contact
FROM 'C:\Import_data.csv'
WITH
{
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
}
but at the minute i just get error -
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

Is this the correct command to use??

Do you know any websites that can point me in the right direction??

ThanksIt's syntax error.
Use () instead of {} as below:

BULK INSERT Contact
FROM 'C:\Import_data.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)|||if you still hit error, try to see if you have grant the right permission in Windows for your csv files.

Dynamic SQL reading statements from table

Hi,

I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)

Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
sp_executesql @.sql

I get: Incorrect syntax near 'sp_executesql'.

If I run

sp_executesql 'SELECT foo FROM foostable'

I get: Procedure expects parameter '@.statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run

sp_executesql N'SELECT foo FROM foostable'

it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @.sql? Every variation of CAST
and CONVERT I use has no effect.

This is SQL Server 2005 SP2. Thanks in advance.On May 1, 5:04 pm, downwitch <downwi...@.gmail.comwrote:

Quote:

Originally Posted by

Hi,
>
I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)
>
Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like
>
DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
sp_executesql @.sql
>
I get: Incorrect syntax near 'sp_executesql'.
>
If I run
>
sp_executesql 'SELECT foo FROM foostable'
>
I get: Procedure expects parameter '@.statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run
>
sp_executesql N'SELECT foo FROM foostable'
>
it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @.sql? Every variation of CAST
and CONVERT I use has no effect.
>
This is SQL Server 2005 SP2. Thanks in advance.

Try printing your @.sql parameter and then firing it mannually, you
might find that the string is not what you expect. Anyway, that is my
standard way of debugging dynamic sql.|||On May 1, 5:08 pm, manstein <jkelly.ad...@.gmail.comwrote:

Quote:

Originally Posted by

On May 1, 5:04 pm, downwitch <downwi...@.gmail.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)

>

Quote:

Originally Posted by

Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like

>

Quote:

Originally Posted by

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
sp_executesql @.sql

>

Quote:

Originally Posted by

I get: Incorrect syntax near 'sp_executesql'.

>

Quote:

Originally Posted by

If I run

>

Quote:

Originally Posted by

sp_executesql 'SELECT foo FROM foostable'

>

Quote:

Originally Posted by

I get: Procedure expects parameter '@.statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run

>

Quote:

Originally Posted by

sp_executesql N'SELECT foo FROM foostable'

>

Quote:

Originally Posted by

it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @.sql? Every variation of CAST
and CONVERT I use has no effect.

>

Quote:

Originally Posted by

This is SQL Server 2005 SP2. Thanks in advance.

>
Try printing your @.sql parameter and then firing it mannually, you
might find that the string is not what you expect. Anyway, that is my
standard way of debugging dynamic sql.- Hide quoted text -
>
- Show quoted text -

as an addendum, what is your (MAX) size? If its too small to hold all
the characters in your string, your statement will be truncated and
raise an error.|||No, I have printed it, it's fine. No truncation. Like my example
above, I can't even get a simple short statement to work (my real
example is 42 characters), and I don't see the error.

On May 1, 5:13 pm, manstein <jkelly.ad...@.gmail.comwrote:

Quote:

Originally Posted by

On May 1, 5:08 pm, manstein <jkelly.ad...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

On May 1, 5:04 pm, downwitch <downwi...@.gmail.comwrote:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like

>

Quote:

Originally Posted by

Quote:

Originally Posted by

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
sp_executesql @.sql

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I get: Incorrect syntax near 'sp_executesql'.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

If I run

>

Quote:

Originally Posted by

Quote:

Originally Posted by

sp_executesql 'SELECT foo FROM foostable'

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I get: Procedure expects parameter '@.statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run

>

Quote:

Originally Posted by

Quote:

Originally Posted by

sp_executesql N'SELECT foo FROM foostable'

>

Quote:

Originally Posted by

Quote:

Originally Posted by

it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @.sql? Every variation of CAST
and CONVERT I use has no effect.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

This is SQL Server 2005 SP2. Thanks in advance.

>

Quote:

Originally Posted by

Try printing your @.sql parameter and then firing it mannually, you
might find that the string is not what you expect. Anyway, that is my
standard way of debugging dynamic sql.- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -

>
as an addendum, what is your (MAX) size? If its too small to hold all
the characters in your string, your statement will be truncated and
raise an error.

|||You are missing EXEC... It is optional only when you execute stored
procedures that are the first statement in the batch.

Just try:

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
EXEC sp_executesql @.sql

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||OK, yes, that does solve the first problem, thank you. Now for
another, related. I'm using a variation on Erland's proc here
http://www.sommarskog.se/dynamic_sql.html#quotestring
to handle nested quotes. There are none, of course, in the simple
statement, but running it through the proc causes it to fail
nonetheless.

Here's my version of the function:
----
CREATE FUNCTION uQuoteString(@.str nvarchar(MAX)) RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @.ret nvarchar(MAX),
@.sq nvarchar(4)
SELECT @.sq = ''''
SELECT @.ret = replace(@.str, @.sq, @.sq + @.sq)
RETURN(@.sq + @.ret + @.sq)
END
----

So running

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
SET @.sql = dbo.uQuoteString(@.sql)
EXEC sp_executesql @.sql

I now get: Incorrect syntax near 'SELECT foo FROM foostable'

Note that the error has changed, no longer referencing the stored proc
but instead the @.sql argument.

On May 1, 5:28 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

You are missing EXEC... It is optional only when you execute stored
procedures that are the first statement in the batch.
>
Just try:
>
DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
EXEC sp_executesql @.sql
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

|||downwitch (downwitch@.gmail.com) writes:

Quote:

Originally Posted by

Here's my version of the function:
----
CREATE FUNCTION uQuoteString(@.str nvarchar(MAX)) RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @.ret nvarchar(MAX),
@.sq nvarchar(4)
SELECT @.sq = ''''
SELECT @.ret = replace(@.str, @.sq, @.sq + @.sq)
RETURN(@.sq + @.ret + @.sq)
END
----
>
So running
>
DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
SET @.sql = dbo.uQuoteString(@.sql)
EXEC sp_executesql @.sql
>
I now get: Incorrect syntax near 'SELECT foo FROM foostable'
>
Note that the error has changed, no longer referencing the stored proc
but instead the @.sql argument.

I added a PRINT @.sql to your SQL batch, and this is what I saw:

'SELECT foo FROM foostable'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT foo FROM foostable'.

A string on its own is not legal T-SQL.

I don't really know what you want to achieve with your quotestring
function, but you put the entire SQL statement in quotes, which
certainly is not the right thing. You said you were reading statements
from a table. I don't really see why you would double any quotes in
these statements either.

Another issue is that the operation is certainly unsafe if anyone can
put statements intos this table, and you run your process with
heavy privs.

--
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|||manstein (jkelly.admin@.gmail.com) writes:

Quote:

Originally Posted by

as an addendum, what is your (MAX) size?

MAX implies in SQL 2005 a size of two gigabytes.

--
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|||Read that section in Erland's article again. The use of this function is
wrap an input parameter in quotes. I do not see any parameters in your SQL
statement, so no need to use the function.

Plamen Ratchev
http://www.SQLStudio.com|||On May 1, 6:14 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

manstein(jkelly.ad...@.gmail.com) writes:

Quote:

Originally Posted by

as an addendum, what is your (MAX) size?

>
MAX implies in SQL 2005 a size of two gigabytes.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

cool thanks. BTW what other declarations allow the use of MAX for
size? I tried char and that did not work. That being the case, isnt
this inconsistent implemetation? MS at its best.|||Perhaps I wasn't as clear as I should have been. Regardless, the
problem is solved--turns out it wasn't just "related" to the first
problem, it *was* the first problem. That's why I'd introduced the
quotestring function, actually, because when I switched from variable
to SQL (on a more complicated query, obviously, than the example I
provided, including multiple parameter values), the string failed
without doubling its parameter quotes. And then I was seeing the
quotes around the SQL string as an output result, not a part of the
string...

In short, duh on me.

RE the security risk, I'm fully aware of it. But as is often the case
with a very sensitive db, if anyone even gains access to it in the
first place there are much bigger potential headaches than whether or
not they want to drop a nasty dynamic SQL statement on it.

On May 1, 6:13 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

downwitch (downwi...@.gmail.com) writes:

Quote:

Originally Posted by

Here's my version of the function:
----
CREATE FUNCTION uQuoteString(@.str nvarchar(MAX)) RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @.ret nvarchar(MAX),
@.sq nvarchar(4)
SELECT @.sq = ''''
SELECT @.ret = replace(@.str, @.sq, @.sq + @.sq)
RETURN(@.sq + @.ret + @.sq)
END
----

>

Quote:

Originally Posted by

So running

>

Quote:

Originally Posted by

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
SET @.sql = dbo.uQuoteString(@.sql)
EXEC sp_executesql @.sql

>

Quote:

Originally Posted by

I now get: Incorrect syntax near 'SELECT foo FROM foostable'

>

Quote:

Originally Posted by

Note that the error has changed, no longer referencing the stored proc
but instead the @.sql argument.

>
I added a PRINT @.sql to your SQL batch, and this is what I saw:
>
'SELECT foo FROM foostable'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT foo FROM foostable'.
>
A string on its own is not legal T-SQL.
>
I don't really know what you want to achieve with your quotestring
function, but you put the entire SQL statement in quotes, which
certainly is not the right thing. You said you were reading statements
from a table. I don't really see why you would double any quotes in
these statements either.
>
Another issue is that the operation is certainly unsafe if anyone can
put statements intos this table, and you run your process with
heavy privs.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||manstein (jkelly.admin@.gmail.com) writes:

Quote:

Originally Posted by

cool thanks. BTW what other declarations allow the use of MAX for
size? I tried char and that did not work. That being the case, isnt
this inconsistent implemetation? MS at its best.

Since char is fixed length, char(MAX) would imply a data type which is
always 2GB in size. I suspect that such a type would do more harm than
good.

In SQL 2005 you can use varchar(MAX), nvarchar(MAX) and varbinary (MAX).
These are the successors to text, ntext and image, which now are
deprecated. The MAX types works very much like the regular
(n)varchar/binary. In difference to the old types that had lots of
limitations.

--
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

Dynamic SQL or CASE statement?

hi all,
we are having an asp app that is fetching a big resultset to display in a
list screen. The screen has 4 or 5 columns which have the click sort option
(ie., on clicking the column hdr, the list needs to be sorted out).
Currently, we are using a procedure which would dynamically construct the
sort order and use exec(@.stmt) to send back the resultset. Coz of perf issue
s
of late, we are thinking of removing dyn sql in the proc and recreating it
using CASE/IF statements. Which option would be better in general? Currently
,
the dyn sql has high counts of CPU.
would appreciate any help in this regards
thx
paraaWhy sort it at all? Can't ASP handle that? It would be more scalable than
making SQL Server do all the sorting.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
hi all,
we are having an asp app that is fetching a big resultset to display in a
list screen. The screen has 4 or 5 columns which have the click sort option
(ie., on clicking the column hdr, the list needs to be sorted out).
Currently, we are using a procedure which would dynamically construct the
sort order and use exec(@.stmt) to send back the resultset. Coz of perf
issues
of late, we are thinking of removing dyn sql in the proc and recreating it
using CASE/IF statements. Which option would be better in general?
Currently,
the dyn sql has high counts of CPU.
would appreciate any help in this regards
thx
paraa|||tom,
we have lean clients and so after lot of brainstorming have put the sorting
on the server side..sorting on client is not an option over here..as per the
scalability factor - may not be that much of an issue..thatz why we are
thinking abt using CASE on the proc side...wanted to know if that would buy
us something vis a vis dyn sql
thx
"Tom Moreau" wrote:

> Why sort it at all? Can't ASP handle that? It would be more scalable tha
n
> making SQL Server do all the sorting.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort optio
n
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa
>|||How "lean" are we talking? I find it hard to believe that an IIS server
can't handle this.
That said, you can do dynamic sorting without resorting to dynamic SQL:
declare @.sort varchar (100)
set @.sort = 'Country'
select
*
from
dbo.Customers
order by
case when @.sort = 'Country' then Country end
, case when @.sort = 'CustomerID' then CustomerID end
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:C3B84B96-3839-4A66-8FAE-0F737FBE21C5@.microsoft.com...
tom,
we have lean clients and so after lot of brainstorming have put the sorting
on the server side..sorting on client is not an option over here..as per the
scalability factor - may not be that much of an issue..thatz why we are
thinking abt using CASE on the proc side...wanted to know if that would buy
us something vis a vis dyn sql
thx
"Tom Moreau" wrote:

> Why sort it at all? Can't ASP handle that? It would be more scalable
> than
> making SQL Server do all the sorting.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort
> option
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa
>|||If you simply need to sort by a single column, then the ASP.NET DataGrid
could handle this. However, if your sort requirements are more complex
involving multiple columns or complex conditions, then it would probably
need to be done within the stored procedure. A conditional [order by] clause
using a case expression would be much preferable than dynamic SQL. Just as a
side note, you can also include case expressions in the [where] clause as
well for conditional row filtering, or even in the [group by] clause for
conditional grouping.
In the example below, there are 3 different sort options enabled using a
parameter called @.sort :
. . .
order by
case @.sort
when 0 then NULL
when 1 then company
when 2 then region
end,
case @.sort
when 0 then accountno
when 1 then lastname
when 2 then lastname
end
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort
> option
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa|||thx a bunch JT and tom..thatz what i was arriving at when i mentioned CASE
stmt v/s dyn sql...just wanted to make sure with the xperts
"JT" wrote:

> If you simply need to sort by a single column, then the ASP.NET DataGrid
> could handle this. However, if your sort requirements are more complex
> involving multiple columns or complex conditions, then it would probably
> need to be done within the stored procedure. A conditional [order by] clau
se
> using a case expression would be much preferable than dynamic SQL. Just as
a
> side note, you can also include case expressions in the [where] clause as
> well for conditional row filtering, or even in the [group by] clause for
> conditional grouping.
> In the example below, there are 3 different sort options enabled using a
> parameter called @.sort :
> .. . .
> order by
> case @.sort
> when 0 then NULL
> when 1 then company
> when 2 then region
> end,
> case @.sort
> when 0 then accountno
> when 1 then lastname
> when 2 then lastname
> end
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
>
>

Dynamic SQL in procedure compiles but does not run

The reason why is because we offer several "lines of coverage" One company may use our app for Workers' Comp and has it's own set of tables. They may use it for Liability that too has it's own set of tables and they may have both. And by using only one usually means that is th eonly set they have.

We have standard report for each line. Since the reports call a stored proceudure I've written it with dynamic SQL in an attept to only have one stored procedure. The idea is that I will pass the procedure the line of coverage and the corrects set of statements will run. I made them dynamic SQL because I figured it would puke when trying to compile and hoped it would not check if a table exist until runtime due to the iff statement.

Well, it is parsing all statements regardless of the instructions from the parameters. Anyone know of any tricks to get around this?I'm goint to try removing the IF statements for the lines of coverage and just do something like:

case @.lob when 'WC' then 'WC' else 'GL' end + 'CCLM1'

I suspect this will work just fine.|||I found the cause. I had hard coded the wrong line of coverage in an area of the procedure so that explains why it was looking for the opposite tables! I'm still going to do the case statement above. Since I've got it in one proc I should cut down what was four queries to two!|||...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"|||[QUOTE=DBA-ONE]We have standard report for each line. Since the reports call a stored proceudure I've written it with dynamic SQL in an attept to only have one stored procedure. The idea is that I will pass the procedure the line of coverage and the corrects set of statements will run. I made them dynamic SQL because I figured it would puke when trying to compile and hoped it would not check if a table exist until runtime due to the iff statement.
QUOTE]

But you ignore the fact that the optimizer will recompile your Dynamic SQL
** every **
time it executes, so you are trading writing four efficient procs running well for one extremely inefficient proc that runs poorly.:(|||That is actually fine. Because these reports are not run every second of the day so the overhead on the server is much more favorable then having to change several procedures and statements. I'd much rather update one procedure, one file and have to put that one file in a single location. See, we support Oracle and SQL Server so for each procedure I need one for comp, one liability, and one each of those for SQL and Oracle. Now do you see why I'm willing to give up a little on the server end to make the maintinence easier? When we roll something out it can be an enourmous pain having to put all these files in different places.|||By the way, I test the old version to the new. While the optimizer may have more work to do there was no difference to speak of in the time to execute. Not bad for my sloppy procedure huh?|||Recompilation expense is minimal and only becomes an issue when a statement might be executed hundreds of times each minute.|||Clients may execute these once a day. Usually at month end.|||...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"

At least it doesn't happen too often!