Is it possible to query a server's DBCC SHOWFILESTATS from one server to another?

I am trying to write a single query that will go out a collect all the
DBCC SHOWFILESTATS. The trick is that the query has to attach to the
remote server, then run the DBCC SHOWFILESTATS, and pull that
information back.
e.g. use servername2.master DBCC SHOWFILESTATS
This obviouly does not work even with the linked server option. Is
there another way to do this? I know you can use a select statement and
a linked server connection to query remote databases.you could create a procedure like this then call the proc on the server
create proc prShowFileStats
as
set nocount on
create table #SHOWFILESTATS (FileID int,
FileGroup int,
TotalExtents int,
UsedExtents int,
Name varchar(100),
FileName varchar(100))
insert into #SHOWFILESTATS
exec ('DBCC SHOWFILESTATS')
select * from #SHOWFILESTATS
set nocount off
http://sqlservercode.blogspot.com/

Is it possible to get all the column values in a single column?

Hi
Is it possible to get all the column values in a single column from a
table?
For e.g.
a1 a2 a3
== == ==
1 2 3
4 5 6
I need the output like
Allcolums
***********
123
456
I am not ready to mention the column names in the query. It should be
generic.
Thanks in advance.Hi
You would be better of leaving this for the front end otherwise it would
mean resorting to cursors and dynamic SQL.
John
"KssKumar2000" wrote:

> Hi
> Is it possible to get all the column values in a single column from a
> table?
> For e.g.
> a1 a2 a3
> == == ==
> 1 2 3
> 4 5 6
> I need the output like
> Allcolums
> ***********
> 123
> 456
>
> I am not ready to mention the column names in the query. It should be
> generic.
> Thanks in advance.
>|||will this work
assuming that each column is of string data type
select a1+a2+a3 from table
or this one if you have nulls
select isnull(a1,'')+isnull(a1,'')+isnull(a1,''
)
thanks,
Jose de Jesus Jr. Mcp,Mcdba
MCP #2324787
"KssKumar2000" wrote:

> Hi
> Is it possible to get all the column values in a single column from a
> table?
> For e.g.
> a1 a2 a3
> == == ==
> 1 2 3
> 4 5 6
> I need the output like
> Allcolums
> ***********
> 123
> 456
>
> I am not ready to mention the column names in the query. It should be
> generic.
> Thanks in advance.
>|||You might want to learn about First Normal Form (1NF) and what a tiered
architecture is before you try SQL coding. You have missed the
foundations and are asking for kludges in Newsgroups when you need to
be studying.
If you **really** want to be a bad programmer, look up cursors and
always comment your code with a note that it needs to be replaced by a
good SQL programmer.|||You might want to find out why the poster wants to do this before you slag
him or her off.
Just because with your limited real industrial experience in programming
you've not come across a need for this doesn't make the poster a bad
programmer.
You need a serious lesson in maners - go back to school and learn how to
interact with society, perhaps you should just shut up with the dissing
people on here and take lessons on how to politly communicate with people!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134740263.450919.20570@.g47g2000cwa.googlegroups.com...
> You might want to learn about First Normal Form (1NF) and what a tiered
> architecture is before you try SQL coding. You have missed the
> foundations and are asking for kludges in Newsgroups when you need to
> be studying.
> If you **really** want to be a bad programmer, look up cursors and
> always comment your code with a note that it needs to be replaced by a
> good SQL programmer.
>|||Yes BUT...the ng needs wits regardless of the adjective(s) or
quantity(s) that realistically apply :)
Happy Holidays to the full spectrum of wits...
www.rac4sql.net
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eLEMwGlAGHA.2040@.TK2MSFTNGP14.phx.gbl...
> You might want to find out why the poster wants to do this before you slag
> him or her off.
> Just because with your limited real industrial experience in programming
> you've not come across a need for this doesn't make the poster a bad
> programmer.
> You need a serious lesson in maners - go back to school and learn how to
> interact with society, perhaps you should just shut up with the dissing
> people on here and take lessons on how to politly communicate with people!
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1134740263.450919.20570@.g47g2000cwa.googlegroups.com...
>

Is it possible to create a DATASET through an Assembly?

Problem: Trying to combine two datasets onto a single table for x-referencing purposes. One dataset comes from a SQL Server and the other comes from a File System processing unit. The File System dataset makes it so that OpenRowSet can not be used.
Attempt: I tried to create a DataSet through an Assembly and have that assembly imbedded in the report. This way, I can just call code from the assembly (containing one dataset) and reference it to data from the dataset from the report.
Problem: I think it has something to do with permissions, since this method works only on 'preview' report but does not work when it is actually published.
All permissions for the assembly is set to full in both the reporting server and the report designer
<pre>
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="Database_Connector"
Description="Code group for connecting to databases">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\DatabaseConnector.dll"
/>
</CodeGroup>
</pre>I think you're right. I would post a question on
www.sqlreportingservices.net as Peter is the SME for CAS.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"John D." <John D.@.discussions.microsoft.com> wrote in message
news:F7939480-C490-410D-A9CE-6D8E1A865613@.microsoft.com...
> Problem: Trying to combine two datasets onto a single table for
x-referencing purposes. One dataset comes from a SQL Server and the other
comes from a File System processing unit. The File System dataset makes it
so that OpenRowSet can not be used.
> Attempt: I tried to create a DataSet through an Assembly and have that
assembly imbedded in the report. This way, I can just call code from the
assembly (containing one dataset) and reference it to data from the dataset
from the report.
> Problem: I think it has something to do with permissions, since this
method works only on 'preview' report but does not work when it is actually
published.
> All permissions for the assembly is set to full in both the reporting
server and the report designer
> <pre>
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="Database_Connector"
> Description="Code group for connecting to databases">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\DatabaseConnector.dll"
> />
> </CodeGroup>
> </pre>
>|||Sounds like your assembly has a reference to some other assembly that
also needs to be granted FullTrust.
"William \(Bill\) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message news:<uUIFq64bEHA.4092@.TK2MSFTNGP11.phx.gbl>...
> I think you're right. I would post a question on
> www.sqlreportingservices.net as Peter is the SME for CAS.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> "John D." <John D.@.discussions.microsoft.com> wrote in message
> news:F7939480-C490-410D-A9CE-6D8E1A865613@.microsoft.com...
> > Problem: Trying to combine two datasets onto a single table for
> x-referencing purposes. One dataset comes from a SQL Server and the other
> comes from a File System processing unit. The File System dataset makes it
> so that OpenRowSet can not be used.
> >
> > Attempt: I tried to create a DataSet through an Assembly and have that
> assembly imbedded in the report. This way, I can just call code from the
> assembly (containing one dataset) and reference it to data from the dataset
> from the report.
> >
> > Problem: I think it has something to do with permissions, since this
> method works only on 'preview' report but does not work when it is actually
> published.
> >
> > All permissions for the assembly is set to full in both the reporting
> server and the report designer
> >
> > <pre>
> > <CodeGroup
> > class="UnionCodeGroup"
> > version="1"
> > PermissionSetName="FullTrust"
> > Name="Database_Connector"
> > Description="Code group for connecting to databases">
> >
> > <IMembershipCondition class="UrlMembershipCondition"
> > version="1"
> > Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\bin\DatabaseConnector.dll"
> > />
> > </CodeGroup>
> > </pre>
> >|||Did you assert the necessary permissions in your code? What permission error
are you receiving? You should debug your code and find out what exact
permissions are required. Probably FileIOPermission and SqlPermission.
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"John D." <John D.@.discussions.microsoft.com> wrote in message
news:F7939480-C490-410D-A9CE-6D8E1A865613@.microsoft.com...
> Problem: Trying to combine two datasets onto a single table for
x-referencing purposes. One dataset comes from a SQL Server and the other
comes from a File System processing unit. The File System dataset makes it
so that OpenRowSet can not be used.
> Attempt: I tried to create a DataSet through an Assembly and have that
assembly imbedded in the report. This way, I can just call code from the
assembly (containing one dataset) and reference it to data from the dataset
from the report.
> Problem: I think it has something to do with permissions, since this
method works only on 'preview' report but does not work when it is actually
published.
> All permissions for the assembly is set to full in both the reporting
server and the report designer
> <pre>
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="Database_Connector"
> Description="Code group for connecting to databases">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\DatabaseConnector.dll"
> />
> </CodeGroup>
> </pre>
>

Is it possible to chart values contained within a single field?

Just picking up SRS for the first time, so please excuse me if my question turns out to be dumb. If I have a Field that contains a csv (comma seperated variable) list of integers, is there any way to chart those values within a report?

I.e:

SELECT id, value FROM DataTable WHERE id = @.ValueId

id value

-- --

1 1,2,3,4,5,6,7,8,9,10,11

(1 row(s) affected)

I can do something like =split(Fields!Value.Value, ",") to get the individual data points out, but I'm not sure how to create a data set out of them so that I can point the chart to it. Changing the DB schema to accomidate the report is kinda out of the question at this point.

Thanks!

You could rewrite your query to split "Value" into numbers and group it by "ID", something like this:

1, ‘1,2,3,4’

2, ‘1,1,2’

1, 1

1,2

1,3

1,4

2,1

2,1

2,2

You can use split() but I don't think you can use it in the chart. It won't acceppt string[] as a value.

Is it possible in single SQL statement

Does anyone know how should I write the sql for getting the following result?

Original Table like below.
----------
[WorkDay] [AgentCode]
06/12/01 3
06/12/02 2
06/12/02 3
06/12/03 2
06/12/03 3
----------

Curernt SQL:

When I put an "agentcode=2" in 'WHERE' clause, the result does not have '06/12/01' row.

Example,
SELECT DISTINCT WorkDay, AgentCode FROM MasterScheduleTransaction WHERE AgentCode=2
----------
[WorkDay] [AgentCode]
06/12/02 2
06/12/03 2
----------

I would like to know the agent is in the specified date.
The expected result like below.
----------
[WorkDay] [AgentCode]
06/12/01 NULL
06/12/02 2
06/12/03 2
----------

Please help its urgentwhy would the result have a row for 06/12/01 when you are telling it to get the rows for agentcode=2... it will discard all the rows having agentcode <> 2.
I think you need to use a self outer join to get the expected result.|||SELECT DISTINCT
Workday,
CASE WHEN AgentCode = 2 THEN
2
ELSE
NULL
END AS AgentCodeIfTwo
FROM OriginalTable|||or when your table is called justanotherday

SELECT DISTINCT
a.workday, b.agentcode
FROM
justanotherday a
LEFT JOIN
justanotherday b
ON
(a.workday = b.workday AND b.agentcode = 2)|||I suppose if you are trying to kill time and want to scan the table twice...|||:D

Well, that shouldn't be too hard... in five years are only 1825 date records... so...|||...assuming only 1 record per day, in which case why select DISTINCT? There is no way to tell how many records are in the table.|||no... i mean.. it's 'only' 1825 records against x records in 5 years... if x is not extremely large, no SQL interpreter should have problems with it in my opinion... or am i totally wrong here?|||The issue isn't that there would be a noticable difference in performance against small record sets. The issue is that the solution you proposed is sub-optimal, and might encourage someone to use the same method against larger databases. We try to propose (and frequently argue about) "best practices" on this forum. If you think that the algorithm you proposed is more efficient than Pootle's method, then take the opportunity to justify your opinion.|||ok... you are right about that... my solution is not really an optimal one :)

is it maintenance plan bug?

When I run Integrity check with option attempt to repair
minor problem seems like there is no integrity checking on
databases whic are not in single user mode.
If I disable that option seems that integrity checking is
running.
Also Inegrity Check jobs doesn't report any error but when
I run dbcc checkdb from analyzer I can see 2-3 2511 errors.
Is the Maintenance plan realy reliable or I should
schedule job to run dbcc checkdb. We are running SQL7 sp3
on W2000.
ThanksWe are running dbcc dbreindex on every table in the
database, after Integrity Checks, Is it possible that
index corruption is generated from reindexing command?
Thanks
>--Original Message--
>It is advisable not to have the attempt to repair minor
problems option set
>in the maintenance paln and this issue with it having to
be in single user
>mode is one of the main reasons. Generally it either
can't get it into
>single user mode and fails or leaves it in single user
mode after it
>finishes, neither of which is desirable. With regard to
your specific
>errors, have your tried dropping and creating the indexes
affected
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"milan" <mmirce01@.yahoo.ca> wrote in message
>news:034201c3507b$2a92a940$a101280a@.phx.gbl...
>When I run Integrity check with option attempt to repair
>minor problem seems like there is no integrity checking on
>databases whic are not in single user mode.
>If I disable that option seems that integrity checking is
>running.
>Also Inegrity Check jobs doesn't report any error but when
>I run dbcc checkdb from analyzer I can see 2-3 2511
errors.
>Is the Maintenance plan realy reliable or I should
>schedule job to run dbcc checkdb. We are running SQL7 sp3
>on W2000.
>Thanks
>
>.
>

is it a good idea to keep sql server with mail server on single T1

hello everyone!!!!!!!!
I just wanted to know is it a good idea to keep both mail and databse server
on same T1.if yes then plz tell me why.
Waiting for yr help.if you could have any documentation on this plz let me
know.
Thanks in advance for yr help.
Bharat.Hi Bharat
This depends on what you are doing with the mail, for instance if you are
using SQL Mail or SMTP.
Having the the mail server or a relay nearby will help reduce the time it
takes to send the mail, which would be very important if you are sending
synchronous emails for instance (for example if in some fit of madness you
decided to send emails from within triggers).
I have not seen any documentation on this but the advice gained by
experience has been posted in the past by others.
John
"bharat" <bharat@.discussions.microsoft.com> wrote in message
news:AECB5C4E-1EEA-479C-B247-291017A552DA@.microsoft.com...
> hello everyone!!!!!!!!
> I just wanted to know is it a good idea to keep both mail and databse
> server
> on same T1.if yes then plz tell me why.
> Waiting for yr help.if you could have any documentation on this plz let me
> know.
> Thanks in advance for yr help.
>
> Bharat.|||bharat wrote:
> hello everyone!!!!!!!!
> I just wanted to know is it a good idea to keep both mail and databse server
> on same T1.if yes then plz tell me why.
For most serious production systems it is a bad idea to have them on the
same machine there is just too much coupling / dependency - performance
wise as well as liveness wise.
> Waiting for yr help.if you could have any documentation on this plz let me
> know.
You don't really need documentation - just think a bit about it for a
moment.
Kind regards
robert|||No --I feel
why? It depends
.How busy you database server is? OLTP or DSS
.Number of User's(concurrent user's) expected on database
.Hardware consideration can be one aspect to look in to
If u have Multiple CPU machine/server with gig's og memory enough to
suffice SQL Server requirement.go ahead u can have Mail on the same machine
Thanks
"bharat" wrote:
> hello everyone!!!!!!!!
> I just wanted to know is it a good idea to keep both mail and databse server
> on same T1.if yes then plz tell me why.
> Waiting for yr help.if you could have any documentation on this plz let me
> know.
> Thanks in advance for yr help.
>
> Bharat.

is it a good idea to keep sql server with mail server on single T1

hello everyone!!!!!!!!
I just wanted to know is it a good idea to keep both mail and databse server
on same T1.if yes then plz tell me why.
Waiting for yr help.if you could have any documentation on this plz let me
know.
Thanks in advance for yr help.
Bharat.Hi Bharat
This depends on what you are doing with the mail, for instance if you are
using SQL Mail or SMTP.
Having the the mail server or a relay nearby will help reduce the time it
takes to send the mail, which would be very important if you are sending
synchronous emails for instance (for example if in some fit of madness you
decided to send emails from within triggers).
I have not seen any documentation on this but the advice gained by
experience has been posted in the past by others.
John
"bharat" <bharat@.discussions.microsoft.com> wrote in message
news:AECB5C4E-1EEA-479C-B247-291017A552DA@.microsoft.com...
> hello everyone!!!!!!!!
> I just wanted to know is it a good idea to keep both mail and databse
> server
> on same T1.if yes then plz tell me why.
> Waiting for yr help.if you could have any documentation on this plz let me
> know.
> Thanks in advance for yr help.
>
> Bharat.|||No --I feel
why? It depends
.How busy you database server is? OLTP or DSS
.Number of User's(concurrent user's) expected on database
.Hardware consideration can be one aspect to look in to
If u have Multiple CPU machine/server with gig's og memory enough to
suffice SQL Server requirement.go ahead u can have Mail on the same machine
Thanks
"bharat" wrote:

> hello everyone!!!!!!!!
> I just wanted to know is it a good idea to keep both mail and databse serv
er
> on same T1.if yes then plz tell me why.
> Waiting for yr help.if you could have any documentation on this plz let me
> know.
> Thanks in advance for yr help.
>
> Bharat.

Is Cursor Best Way To Go?

I need to get two values from a complex SQL statement which returns a single
record and use those two values to update a single record in a table. In
order to assign those two values to variables and then use those variables
in the UPDATE statement, I created a cursor and used Fetch Next... Into.
This way, I only have to call the complex SQL once instead of twice.
This seems like the best way to go. However, I've always used cursors for
scrolling through resultsets. In this case, though, there is just a single
record being returned, and the cursor doesn't scroll.
Is that the most efficient way to go, or is there a better way to be able to
use both values from the SQL statement without having to call it twice?
Thanks.Hi Neil
I'd need more details regarding the query/DDL to say anything too
meaningful, but certainly a set-based solution is always preferable to
an iterative/cursor-solution.|||here is a guess without seeing your code.
declare @.v1 int, @.v2 int
select @.v1=[col1], @.v2=[colx]
from (
-- your complex query
) as derived_table
-oj
"Neil" <nospam@.nospam.net> wrote in message
news:vhAne.4180$s64.2269@.newsread1.news.pas.earthlink.net...
>I need to get two values from a complex SQL statement which returns a
>single record and use those two values to update a single record in a
>table. In order to assign those two values to variables and then use those
>variables in the UPDATE statement, I created a cursor and used Fetch
>Next... Into. This way, I only have to call the complex SQL once instead
>of twice.
> This seems like the best way to go. However, I've always used cursors for
> scrolling through resultsets. In this case, though, there is just a single
> record being returned, and the cursor doesn't scroll.
> Is that the most efficient way to go, or is there a better way to be able
> to use both values from the SQL statement without having to call it twice?
> Thanks.
>|||SQL Server doesn't support the standard SQL syntax for this but it does
have a proprietary syntax to do the same job:
UPDATE T1
SET x = foo,
y = bar
FROM
(SELECT foo, bar /* your query here */
FROM ... ) AS T2
WHERE T2.key_col = T1.key_col
/* join condition should yield a single row from T2 for each row in
T1 */

> I've always used cursors for
> scrolling through resultsets
Really? For what purpose? Cursors should be the rare exception rather
than the rule. Usually there are better set-based solutions.
David Portas
SQL Server MVP
--|||> SQL Server doesn't support the standard SQL syntax for this but it does
> have a proprietary syntax to do the same job:
> UPDATE T1
> SET x = foo,
> y = bar
> FROM
> (SELECT foo, bar /* your query here */
> FROM ... ) AS T2
> WHERE T2.key_col = T1.key_col
> /* join condition should yield a single row from T2 for each row in
> T1 */
Yes, that was what I was looking for (though I needed to use UPDATE T1
SET... From T1, (Select foo...) As T2...)
Also, since I'm only updating a single row in T1, and since T2 only returns
a single row with values, I eliminated the WHERE T2.keycol=T1.keycol. My SQL
looks like:
UPDATE T1
SET X = T2.FOO, Y=T2.BAR
FROM T1, (SELECT FOO, BAR FROM MYQUERY WHERE ID=@.VALUE) AS T2
WHERE T1.ID=@.VALUE
Do you see any problem with that?

> Really? For what purpose? Cursors should be the rare exception rather
> than the rule. Usually there are better set-based solutions.
I guess one of the main areas where I've used them is in order-rearranging
functions -- such as where there are a set of items in a table, each with a
value in a field that specifies the order. The user clicks, say, an up arrow
in the interface, and the current item needs to move up one in order --
decrement it's field value by one, and increment the preceding item's by
one.
Another time I used a cursor was in a procedure in which the length of two
fields combined needed to be compared to a value and then, based on the
length of the combined fields, different values would be placed in a certain
field. I suppose that could have just been done with a set-based solution;
but the cursor seemed more straightforward. It was also only dealing with
one record at a time.
Thanks for your help!
Neil

> --
> David Portas
> SQL Server MVP
> --
>|||Re-arranging order based on a column (pos):
UPDATE foo
SET pos = CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos
Update different columns based on the length of a string value:
UPDATE YourTable
SET col1 =
CASE
WHEN LEN(x+y)<=10
THEN a ELSE b END,
col2 =
CASE
WHEN LEN(x+y)>10
THEN a ELSE b END
WHERE ...
David Portas
SQL Server MVP
--
UPDATE|||David Portas Jun 2, 7:07 am show options
Newsgroups: comp.databases.ms-sqlserver,
microsoft.public.sqlserver.programming
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.o=ADrg> - Find
messages by this author
Date: 2 Jun 2005 04:07:17 -0700
Local: Thurs,Jun 2 2005 7:07 am
Subject: Re: Is Cursor Best Way To Go?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
Re-arranging order based on a column (pos):
UPDATE foo
SET pos =3D CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos;
Very neat! I always did a monster CASE expression with extra WHEN
clauses based on (old_pos ' newpos).|||Hi, David.
Here's another one for you. I have an sp that takes various input parameters
for a customer, and processes the data using various case statements. I now
want to run this sp for all customers on a nightly basis. My immediate
reaction, as previously, would be to use a cursor to loop through all the
customers, get the input parameters for the sp from the Customer table, and
call the sp once for each customer. Is there a way to do this without a
cursor?
Thanks,
Neil
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1117710437.665131.255080@.g47g2000cwa.googlegroups.com...
> Re-arranging order based on a column (pos):
> UPDATE foo
> SET pos = CASE pos
> WHEN @.old_pos
> THEN @.new_pos
> ELSE pos + SIGN(@.old_pos - @.new_pos)
> END
> WHERE pos BETWEEN @.old_pos AND @.new_pos
> OR pos BETWEEN @.new_pos AND @.old_pos
> Update different columns based on the length of a string value:
> UPDATE YourTable
> SET col1 =
> CASE
> WHEN LEN(x+y)<=10
> THEN a ELSE b END,
> col2 =
> CASE
> WHEN LEN(x+y)>10
> THEN a ELSE b END
> WHERE ...
> --
> David Portas
> SQL Server MVP
> --
>
>
>
> UPDATE
>|||Neil (nospam@.nospam.net) writes:
> Here's another one for you. I have an sp that takes various input
> parameters for a customer, and processes the data using various case
> statements. I now want to run this sp for all customers on a nightly
> basis. My immediate reaction, as previously, would be to use a cursor to
> loop through all the customers, get the input parameters for the sp from
> the Customer table, and call the sp once for each customer. Is there a
> way to do this without a cursor?
Yes, but you will of course have to rewrite the procedure, so that it
works with many customers. To do this, you need to pass the input
parameters in a table rather than as parameter. This table can be a temp
table, or a permanent table which is keyed by @.@.spid or similar. I discuss
this on http://www.sommarskog.se/share_data.html#temptables.
Well, rather you would write a new procedure that works with many, and
then rewrite the old procedure to be a wrapper on the new procedure.
Now, whether you actually should go this route depends. Let's say that
it takes 10 minutes to run a cursor over all customers and call the
existing procedure, and that you have plenty of time to spare in the
night. In this case, it's not likely to be worth the development effort.
Also, if you opt to use a temp table to pass the input parameters, the
procedure will be recompiled each time. This will have the net effect
that calls for single customers will now be more expensive, and could
even be performance problems, if the procedure is huge.
We actually did this exercise with a core procedure in our system, and
in our case it was really necessary. But it was a major developement task.
Our estimate was 200 hours for development, but I think the true outcome
was more than 300 hours. But that was a long procedure, on 700-800 lines
and which called several sub-procedures. The final multi-version is a
3000-line monster with no less than 43 table variables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Everything Erland has said. This is where it pays to have a good design
pattern from kick-off. For an UPDATE/INSERT/DELETE proc servicing the
UI you may typically want to pass parameters for a single row. For
procs that implement other business logic however, you should generally
design with a set-based approach in mind. Unfortunately, programmers
used to other languages too often try to encapsulate all logic in procs
that act like scalar functions - a sure route to cursor hell!
David Portas
SQL Server MVP
--

Is Cursor Best Way To Go?

I need to get two values from a complex SQL statement which returns a single
record and use those two values to update a single record in a table. In
order to assign those two values to variables and then use those variables
in the UPDATE statement, I created a cursor and used Fetch Next... Into.
This way, I only have to call the complex SQL once instead of twice.

This seems like the best way to go. However, I've always used cursors for
scrolling through resultsets. In this case, though, there is just a single
record being returned, and the cursor doesn't scroll.

Is that the most efficient way to go, or is there a better way to be able to
use both values from the SQL statement without having to call it twice?

Thanks.Hi Neil

I'd need more details regarding the query/DDL to say anything too
meaningful, but certainly a set-based solution is always preferable to
an iterative/cursor-solution.|||here is a guess without seeing your code.

declare @.v1 int, @.v2 int
select @.v1=[col1], @.v2=[colx]
from (
-- your complex query
) as derived_table

--
-oj

"Neil" <nospam@.nospam.net> wrote in message
news:vhAne.4180$s64.2269@.newsread1.news.pas.earthl ink.net...
>I need to get two values from a complex SQL statement which returns a
>single record and use those two values to update a single record in a
>table. In order to assign those two values to variables and then use those
>variables in the UPDATE statement, I created a cursor and used Fetch
>Next... Into. This way, I only have to call the complex SQL once instead
>of twice.
> This seems like the best way to go. However, I've always used cursors for
> scrolling through resultsets. In this case, though, there is just a single
> record being returned, and the cursor doesn't scroll.
> Is that the most efficient way to go, or is there a better way to be able
> to use both values from the SQL statement without having to call it twice?
> Thanks.|||SQL Server doesn't support the standard SQL syntax for this but it does
have a proprietary syntax to do the same job:

UPDATE T1
SET x = foo,
y = bar
FROM
(SELECT foo, bar /* your query here */
FROM ... ) AS T2
WHERE T2.key_col = T1.key_col
/* join condition should yield a single row from T2 for each row in
T1 */

> I've always used cursors for
> scrolling through resultsets

Really? For what purpose? Cursors should be the rare exception rather
than the rule. Usually there are better set-based solutions.

--
David Portas
SQL Server MVP
--|||> SQL Server doesn't support the standard SQL syntax for this but it does
> have a proprietary syntax to do the same job:
> UPDATE T1
> SET x = foo,
> y = bar
> FROM
> (SELECT foo, bar /* your query here */
> FROM ... ) AS T2
> WHERE T2.key_col = T1.key_col
> /* join condition should yield a single row from T2 for each row in
> T1 */

Yes, that was what I was looking for (though I needed to use UPDATE T1
SET... From T1, (Select foo...) As T2...)

Also, since I'm only updating a single row in T1, and since T2 only returns
a single row with values, I eliminated the WHERE T2.keycol=T1.keycol. My SQL
looks like:

UPDATE T1
SET X = T2.FOO, Y=T2.BAR
FROM T1, (SELECT FOO, BAR FROM MYQUERY WHERE ID=@.VALUE) AS T2
WHERE T1.ID=@.VALUE

Do you see any problem with that?

>> I've always used cursors for
>> scrolling through resultsets
> Really? For what purpose? Cursors should be the rare exception rather
> than the rule. Usually there are better set-based solutions.

I guess one of the main areas where I've used them is in order-rearranging
functions -- such as where there are a set of items in a table, each with a
value in a field that specifies the order. The user clicks, say, an up arrow
in the interface, and the current item needs to move up one in order --
decrement it's field value by one, and increment the preceding item's by
one.

Another time I used a cursor was in a procedure in which the length of two
fields combined needed to be compared to a value and then, based on the
length of the combined fields, different values would be placed in a certain
field. I suppose that could have just been done with a set-based solution;
but the cursor seemed more straightforward. It was also only dealing with
one record at a time.

Thanks for your help!

Neil

> --
> David Portas
> SQL Server MVP
> --|||Re-arranging order based on a column (pos):

UPDATE foo
SET pos = CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos

Update different columns based on the length of a string value:

UPDATE YourTable
SET col1 =
CASE
WHEN LEN(x+y)<=10
THEN a ELSE b END,
col2 =
CASE
WHEN LEN(x+y)>10
THEN a ELSE b END
WHERE ...

--
David Portas
SQL Server MVP
--

UPDATE|||David Portas Jun 2, 7:07 am show options

Newsgroups: comp.databases.ms-sqlserver,
microsoft.public.sqlserver.programming
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.o*rg> - Find
messages by this author
Date: 2 Jun 2005 04:07:17 -0700
Local: Thurs,Jun 2 2005 7:07 am
Subject: Re: Is Cursor Best Way To Go?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Re-arranging order based on a column (pos):

UPDATE foo
SET pos = CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos;

Very neat! I always did a monster CASE expression with extra WHEN
clauses based on (old_pos ?? newpos).|||Hi, David.

Here's another one for you. I have an sp that takes various input parameters
for a customer, and processes the data using various case statements. I now
want to run this sp for all customers on a nightly basis. My immediate
reaction, as previously, would be to use a cursor to loop through all the
customers, get the input parameters for the sp from the Customer table, and
call the sp once for each customer. Is there a way to do this without a
cursor?

Thanks,

Neil

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1117710437.665131.255080@.g47g2000cwa.googlegr oups.com...
> Re-arranging order based on a column (pos):
> UPDATE foo
> SET pos = CASE pos
> WHEN @.old_pos
> THEN @.new_pos
> ELSE pos + SIGN(@.old_pos - @.new_pos)
> END
> WHERE pos BETWEEN @.old_pos AND @.new_pos
> OR pos BETWEEN @.new_pos AND @.old_pos
> Update different columns based on the length of a string value:
> UPDATE YourTable
> SET col1 =
> CASE
> WHEN LEN(x+y)<=10
> THEN a ELSE b END,
> col2 =
> CASE
> WHEN LEN(x+y)>10
> THEN a ELSE b END
> WHERE ...
> --
> David Portas
> SQL Server MVP
> --
>
>
>
> UPDATE|||Neil (nospam@.nospam.net) writes:
> Here's another one for you. I have an sp that takes various input
> parameters for a customer, and processes the data using various case
> statements. I now want to run this sp for all customers on a nightly
> basis. My immediate reaction, as previously, would be to use a cursor to
> loop through all the customers, get the input parameters for the sp from
> the Customer table, and call the sp once for each customer. Is there a
> way to do this without a cursor?

Yes, but you will of course have to rewrite the procedure, so that it
works with many customers. To do this, you need to pass the input
parameters in a table rather than as parameter. This table can be a temp
table, or a permanent table which is keyed by @.@.spid or similar. I discuss
this on http://www.sommarskog.se/share_data.html#temptables.

Well, rather you would write a new procedure that works with many, and
then rewrite the old procedure to be a wrapper on the new procedure.

Now, whether you actually should go this route depends. Let's say that
it takes 10 minutes to run a cursor over all customers and call the
existing procedure, and that you have plenty of time to spare in the
night. In this case, it's not likely to be worth the development effort.
Also, if you opt to use a temp table to pass the input parameters, the
procedure will be recompiled each time. This will have the net effect
that calls for single customers will now be more expensive, and could
even be performance problems, if the procedure is huge.

We actually did this exercise with a core procedure in our system, and
in our case it was really necessary. But it was a major developement task.
Our estimate was 200 hours for development, but I think the true outcome
was more than 300 hours. But that was a long procedure, on 700-800 lines
and which called several sub-procedures. The final multi-version is a
3000-line monster with no less than 43 table variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Everything Erland has said. This is where it pays to have a good design
pattern from kick-off. For an UPDATE/INSERT/DELETE proc servicing the
UI you may typically want to pass parameters for a single row. For
procs that implement other business logic however, you should generally
design with a set-based approach in mind. Unfortunately, programmers
used to other languages too often try to encapsulate all logic in procs
that act like scalar functions - a sure route to cursor hell!

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Everything Erland has said. This is where it pays to have a good design
> pattern from kick-off. For an UPDATE/INSERT/DELETE proc servicing the
> UI you may typically want to pass parameters for a single row. For
> procs that implement other business logic however, you should generally
> design with a set-based approach in mind. Unfortunately, programmers
> used to other languages too often try to encapsulate all logic in procs
> that act like scalar functions - a sure route to cursor hell!

Permit me to expand a bit on what I touched in my previous post.

In many cases it is reasonable to write a procedure that operates
on a scalar set of values. It cannot be denied that writing such a
procedure is simpler, and thus cuts development costs at that stage.

Passing data in tables is actually quite messy. Let's look at the options:
1) Use a temp table. The caller must create the temp table, and the callee
trust the caller. If the procedure is called from many places, many
callers must create the table. This can be address with an include-
file, if you have the luxury of a preprocessor. We have that, but it's
not a standard feature.
And if even you get by all this, the callee is recompiled for each
new instance of the caller. This can be expensive.
2) A permanent table, typically spid-keyed. We use this technique for the
really heavy-duty stuff. If you make this routine, you get lots of
these tables. Note also that the tables are typically stored disjunct
in the version-control system, which means that procedure and
"parameter list" are in two places.
3) Clients can't use any of 1 or 2, but they can pass comma-separated
lists or XML-documents. But if A_SP calls B_SP, it would be a bad
idea if A_SP built an XML document from its data, only to be able
to call B_SP. What you can to is to have a wrapper that accepts
the XML document, and unpacks that into the temp table or spid-
keyed table. If the client is mainly interested in single-row
operations, it probably needs a scalar wrapper as well. Else, it
will be a lot extra development overhead to build XML documents.

So, clearly, if you at point A in your devleopment cycle only have a need
for a procedure that operates on scalar parameters, you write a procedure
that works with scalar procedure only, because that is what you are paid
for.

If you later at point B need to do the same operation on many rows,
you have to make a judicious choice between:

1) Write a cursor loop.
2) Just forget about the old procedure, and write a new set-based.
3) Replace the old procedure.

If the logic of the procedure is trivial, like "IF NOT EXISTS INSERT ELSE
UPDATE" you should pick #2. But say that the logic is non-trivial, for
instance includes updates to dependent tables in some unnormalised
scenario, then at some point #2 becomes completely impermissible. At
this point #1 can very well be the best pick. Say that you know that
it will be rare that the cursor will comprise as much as 100 rows. If
the procedure takes 100 ms to run, it may be very difficult to motivate
to rewrite the old procedure, if this would take 100 hours.

There is also another issue here that is worth mentioning. Say that your
procedure performs some sort of INSERT operation (in a couple of tables),
and the data comes from some less trustable source, which thus may
supply non-conformant data. If you have a scalar procedure, error
handling is fairly simple. You can do explicit checks on anticipated
errors, but you can be fairly relaxed, because if some data violates a
constraint or trigger check, the operation will fail.

This because a lot more complex if you accept input data in a table.
Because if you apply the same strategy, 1000 rows could fail to insert
when there is an error in a single one. This could very likely be
entirely unacceptable. Thus in case, you will need to duplicate all
constraint and trigger checks in your code, so you can mark which rows
that are illegal.

So while it is easy to say "replace cursor loops with set-based
statments", one should realise that in complex cases, this is far from
trivial.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp