Case statement error

I have an sp that I am trying to run, but it keeps failing. Something like t
his
if a.one='PA' then
begin
select * from a
else
select * from b
end
I keep getting errors. I have tried a CASE statement, but it does not seems
to workIF EXISTS (SELECT one FROM a WHERE one='PA')
SELECT <column_list> FROM a
ELSE
SELECT <column_list> FROM b
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:4A5DC7F7-C8FE-4CA6-9F0D-C5108E871D65@.microsoft.com...
>I have an sp that I am trying to run, but it keeps failing. Something like
>this
> if a.one='PA' then
> begin
> select * from a
> else
> select * from b
> end
> I keep getting errors. I have tried a CASE statement, but it does not
> seems
> to work

case sensitive server - problems running scripts

I have just been given a new SQL Server 2000 box to look after in production. I just tried to run a standard t-sql script I use for setting up backup jobs and so on. However, it failed with a long list of errors - quite a surprise at first since I have run the same script on many other servers wihtout a hitch. On close examination, the problem appears to be that the new serer is setup with a server default collation... Latin1_General_BIN (I think a binary based collation makes this a case sensitive server).

This is quite an urgenet one since I have to get this wrapped up today. I don't think I can change the server's default collation without a lot of red-tape. Is there a quick way to run my scripts in a 'case insenstive' context within Query Analyzer? If so, how?

Thanks in advance,

CliveWe have that problem all of the time with the PeopleSoft servers. They can only run with binary sort orders.

If you find a way to work around the case sensitivity, please let me know!

-PatP|||Ok, well thanks anyway. It seems a bit over the top for a server to be set to case senstive by default. Even the master database is case sensitive as a result. I can't see why the s/w vendor couldn't have left the default server collation alone and just been a bit more selective about what tables/columns actually needed a _BIN collation. Laziness I suppose.

Clive|||Binary sort order os a bit faster than case sensitive, as it bypasses all of the checks for 'A' = 'a'. That aside, you should probably go over the script and change the identifiers (table names, column names) to be the proper case (fortunately this is all lowercase for system tables), and change all of your variables to be the same case throughout. The variables can be doe with find/replace very easily. After that, you would have a new script that would work on Latin1_General_CI_AS, _BIN, and _CS_AS servers.

Since i have a couple of case sensitive servers around, I have had to write all of my own scripts to be able to handle case sensitivity. Even the ones that "should" only run on the case insensitive ones. It is a good habit to get into.|||All of our main production servers are Latin1_General_BIN_437 which is actually what yours probably is. This was recommended back in the old days when it was based on 6.5. Since they reworked the architecture, It doesn't really make any difference on speed. Since it's not the industry standard to use this, it's just a big pain. Anyway, I've learned to use all upper-case for my commands and lower-case for my objects. We have a program at work that will automagically convert all your code for you. I'll see if I can "share" it with you. Saves soooooooo much time.

CASE Query

Hi i'm trying to run a CASE, Database is TS_Positions Column is Position Type, we usually get data being -1 or 1, i would like use the Function CASE to change that in my query(easier to read) 1=being a BUY... -1=being a SELL.
For Some reason my query will NOT Work, Every other part works just not the CASE part.. Any ideas?? Query:

SELECT CASE PositionType
WHEN PositionType '1' THEN 'BUY'
WHEN PositionType '-1' THEN 'SELL' AS [BS}, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike, expiringdate, comment, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE]
FROM TS_Positions
WHERE (Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY ContractFor starters, there's no need to repeat PositionType in the WHEN lines. You've specified it in the CASE line. Secondly, if the 1 or -1 is a numeric value, they shouldn't be surrounded by quotes. Third, the "}" is wrong. Fourth, no END.|||Hi,

You can re-edit the CASE statement as
CASE PositionType
WHEN 1 THEN 'BUY'
WHEN -1 THEN 'SELL'
END AS [BS],

SELECT
CASE PositionType
WHEN 1 THEN 'BUY'
WHEN -1 THEN 'SELL'
END AS [BS],
CAST(TradePrice as float(20,8) ) AS [Price],
Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike,
expiringdate,
comment,
SUBSTRING(contract+CONVERT(varchar,expiringdate),1 ,20) AS [FEEDCODE]
FROM TS_Positions
WHERE
(Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY Contract

Eralper
http://www.kodyaz.com|||Thanks Eralper! That worked but i decided to do it this way.

SELECT case positiontype when '1' then 'buy' when '-1' then 'sell' else 'none' end AS [B/S]...

Is there anyway where i can put on there to NOT show the NONE for the else? So i would only show the B(1) and S(-1).

Also on my query i would like to add 2 new columns at the end for example:

select col1,col2, , ‘portfolio’ as Portfolio,‘markets’ as Markets

Soo i should put that at the end of my query so it will look like this correct?

SELECT case positiontype when '1' then 'buy' when '-1' then 'sell' else 'none' end AS [B/S], comment, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike, expiringdate, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE], col1,col2, , ‘portfolio’ as Portfolio,‘markets’ as Markets
FROM TS_Positions
WHERE (Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY PositionType

2 new columns for my query would be Portfolio and Markets at the end..
Right now the columns without the config has:
B/S comment Price Volume Kind Strike Expiringdate Feedcode

New query would include 2 columns
B/S comment Price Volume Kind Strike Expiringdate Feedcode Portfolio Markets

Cascading query with parameters

I need to get at some information and this is the only way I know how to do it but I need to run these queries in order and using the previous query output. I don't know how to set up the output parameters so they can be used in the second query and the second query results to be used in the third query.

In a nutshell: The first query gets a number from a user as a parameter and pulls one record containing multiple fields. Some of those fields will be needed as parameters in the second query to pull mutltiple records. I need to put some where statements in to narrow down the result set. Then this part I haven't figured out either, but then the third query takes one record from the second result set and pulls mutliple records to put in a report.

I hope at least a good part of this makes sense. Please HELP ME!

ALTER PROCEDURE sp_getOfficerList

@.compositeNumber int

as

SELECT tblCampus.fldCampusCode, tblGroup.fldGroupCode, tblContract.fldGraduationMonthCode, tblComposite.fldCompositeNumber,

tblContract.fldContractCode, tblGroup.fldGroupName, tblCampus.fldCampusName

FROM tblComposite INNER JOIN

tblContract ON tblComposite.fldContractID = tblContract.fldContractID INNER JOIN

tblOrganization ON tblContract.fldOrganizationID = tblOrganization.fldOrganizationID INNER JOIN

tblGroup ON tblOrganization.fldGroupID = tblGroup.fldGroupID INNER JOIN

tblCampus ON tblOrganization.fldCampusID =tblCampus.fldCampusID

WHERE (tblComposite.fldCompositeNumber = @.compositeNumber)

declare

@.campusCode varchar(10),

@.groupCode varchar(5),

@.graduationMonthCode varchar(2)

SELECT tblComposite.fldCompositeNumber

FROM tblContract INNER JOIN

tblComposite ON tblContract.fldContractID = tblComposite.fldContractID INNER JOIN

tblOrganization ON tblContract.fldOrganizationID = tblOrganization.fldOrganizationID INNER JOIN

tblCampus ON tblOrganization.fldCampusID = tblCampus.fldCampusID INNER JOIN

tblGroup ON tblOrganization.fldGroupID = tblGroup.fldGroupID

WHERE (tblCampus.fldCampusCode = @.campusCode) AND (tblGroup.fldGroupCode = @.groupCode) AND

(tblContract.fldGraduationMonthCode = @.graduationMonthCode)

declare

@.lastCompositeNumber int

SELECT DISTINCT tblCameraCard.fldTitle

FROM tblCameraCard INNER JOIN

tblComposite_CameraCard_Link ON tblCameraCard.fldCameraCardID = tblComposite_CameraCard_Link.fldCameraCardID INNER JOIN

tblComposite ON tblComposite_CameraCard_Link.fldCompositeID = tblComposite.fldCompositeID

WHERE (tblComposite.fldCompositeNumber = @.lastCompositeNumber) AND (NOT (tblCameraCard.fldTitle IS NULL)) OR

(tblCameraCard.fldTitle = '')

Ok, let me rephrase my question. I want to take the result from query A and make it be the parameter for query B and take the result from query B and make that the parameter for query C. The results from query C is what I want to put in my report.

I did some research and found I could set a query equal to something. So I did that but how do I get that returned value to the next query?

Cascade Delete enumeration?

Hello,
Does anyone know a query I can run that will display all cascade actions (on
delete or otherwise) for all tables/relationships in a given database?
Thanks in advance
-Kerry-Something like:
select object_name(fkeyid) 'References',
object_name(rkeyid) 'Referenced',
case when ObjectProperty(r.constid, 'CnstIsDeleteCascade') = 1 then 'Yes'
else 'No' end 'DeleteCascade',
case when ObjectProperty(r.constid, 'CnstIsUpdateCascade') = 1 then 'Yes'
else 'No' end 'UpdateCascade'
from sysreferences r
where ObjectProperty(r.constid, 'CnstIsDeleteCascade') = 1
or ObjectProperty(r.constid, 'CnstIsUpdateCascade') = 1
Tom
"Kerry" <Kerry@.discussions.microsoft.com> wrote in message
news:9EFFA9D6-4C8E-400E-8A9E-BD08D9A0248F@.microsoft.com...
> Hello,
> Does anyone know a query I can run that will display all cascade actions
> (on
> delete or otherwise) for all tables/relationships in a given database?
> Thanks in advance
> -Kerry-|||Thank you Tom, I'll give it a try in the morning.
"Tom Cooper" wrote:

> Something like:
> select object_name(fkeyid) 'References',
> object_name(rkeyid) 'Referenced',
> case when ObjectProperty(r.constid, 'CnstIsDeleteCascade') = 1 then 'Yes'
> else 'No' end 'DeleteCascade',
> case when ObjectProperty(r.constid, 'CnstIsUpdateCascade') = 1 then 'Yes'
> else 'No' end 'UpdateCascade'
> from sysreferences r
> where ObjectProperty(r.constid, 'CnstIsDeleteCascade') = 1
> or ObjectProperty(r.constid, 'CnstIsUpdateCascade') = 1
> Tom
> "Kerry" <Kerry@.discussions.microsoft.com> wrote in message
> news:9EFFA9D6-4C8E-400E-8A9E-BD08D9A0248F@.microsoft.com...
>
>|||It worked, thanks Tom.
-Kerry-
"Tom Cooper" wrote:

> Something like:
> select object_name(fkeyid) 'References',
> object_name(rkeyid) 'Referenced',
> case when ObjectProperty(r.constid, 'CnstIsDeleteCascade') = 1 then 'Yes'
> else 'No' end 'DeleteCascade',
> case when ObjectProperty(r.constid, 'CnstIsUpdateCascade') = 1 then 'Yes'
> else 'No' end 'UpdateCascade'
> from sysreferences r
> where ObjectProperty(r.constid, 'CnstIsDeleteCascade') = 1
> or ObjectProperty(r.constid, 'CnstIsUpdateCascade') = 1
> Tom
> "Kerry" <Kerry@.discussions.microsoft.com> wrote in message
> news:9EFFA9D6-4C8E-400E-8A9E-BD08D9A0248F@.microsoft.com...
>
>

Capturing rows inserted from bulk insert

Is there any way to capture the count of rows processed by a bulk insert task?
A property perhaps?
I can run queries post import but would prefer it if there was a way to capture that number directly. It was something we had in the old DTS as the package ran. Anything we can do to discover it in SSIS?

Paul PisarekOne option is to enable the out-of-box logging to sysdtslog90 table - the components displays number of rows processed. It's in the 'message' text colum though, so you need to add a little bit of parsing to get your specific data.
KDog|||That's right. We're working on a sample that parses the string and creates a report from it, hopefully should be available to you soon. But, yes, parsing the log is the right approach.

Capture Traces of all Transactions

How can I capture and trace transactions to a log file that will run all day?If this is a one time thing you could use Profiler and alter the default setting to log to a file.

capture showplan in profiler

I want to capture showplan for all the queries running on the server at a
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
ThanksHassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>

capture showplan in profiler

I want to capture showplan for all the queries running on the server at a
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
ThanksHassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>

capture showplan in profiler

I want to capture showplan for all the queries running on the server at a
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
Thanks
Hassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>

Capture CPU Utilization in TSQL

Happy New Year everyone!

I would like to capture CPU Utilization % using TSQL. I know this can
be done using PerfMon but I would like to run TSQL command (maybe once
every 5 minutes) and see what is the CPU Utilization at that instant so
that I can insert the value in a table and run reports based on the
data.

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'

Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

Any help would be appreciated.

ThanksSQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'
>
Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

Performance counters are in sysperfinfo on SQL 2000 and
sys.dm_os_performance_counters on SQL 2005, but I could find the item
you are looking for in these views.

But I saw in Books Online for SQL 2005 that these values are cumultative. To
get the present value, sample with some interval. I guess you could to
the same: query @.@.CPU_BUSY twice with a second or so in between.

--
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|||Thanks for your quick response Erland. I ran the following script but I
don't think this is the correct value. But I cannot find anything
meaningful?

DECLARE
@.CPUBusy1 bigint
, @.CPUBusy2 bigint
, @.TimeTicks1 bigint
, @.TimeTicks2 bigint

SELECT
@.CPUBusy1 = @.@.CPU_BUSY
, @.TimeTicks1 = @.@.TIMETICKS

WAITFOR DELAY '0:00:01'

SELECT
@.CPUBusy2 = @.@.CPU_BUSY
, @.TimeTicks2 = @.@.TIMETICKS

SELECT
@.CPUBusy1 AS CPUBusy1
, @.CPUBusy2 AS CPUBusy2
, @.CPUBusy2 - @.CPUBusy1 AS CPUDiff
, @.TimeTicks1 AS TimeTicks1
, @.TimeTicks2 AS TimeTicks2
, @.TimeTicks2 - @.TimeTicks1 AS TimeTicksDiff

Thanks for your time and help!

Vishal

Erland Sommarskog wrote:

Quote:

Originally Posted by

SQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'

Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

>
Performance counters are in sysperfinfo on SQL 2000 and
sys.dm_os_performance_counters on SQL 2005, but I could find the item
you are looking for in these views.
>
But I saw in Books Online for SQL 2005 that these values are cumultative. To
get the present value, sample with some interval. I guess you could to
the same: query @.@.CPU_BUSY twice with a second or so in between.
>
>
>
--
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

|||Thanks everyone for reading this and your responses. I was able to find
the correct solution in another Google post:

DECLARE
@.CPU_BUSY int
, @.IDLE int

SELECT
@.CPU_BUSY = @.@.CPU_BUSY
, @.IDLE = @.@.IDLE

WAITFOR DELAY '000:00:01'

SELECT
(@.@.CPU_BUSY - @.CPU_BUSY)/((@.@.IDLE - @.IDLE + @.@.CPU_BUSY - @.CPU_BUSY) *
1.00) *100 AS CPUBusyPct

This solution was posted by Gert-Jan Strik on Tues, Jan 14 2003 6:55
PM.
Here is the URL for the thread:
http://groups.google.com/group/comp...f67728586773e8b
Thanks again,

Vishal

SQLJunkie wrote:

Quote:

Originally Posted by

Thanks for your quick response Erland. I ran the following script but I
don't think this is the correct value. But I cannot find anything
meaningful?
>
DECLARE
@.CPUBusy1 bigint
, @.CPUBusy2 bigint
, @.TimeTicks1 bigint
, @.TimeTicks2 bigint
>
SELECT
@.CPUBusy1 = @.@.CPU_BUSY
, @.TimeTicks1 = @.@.TIMETICKS
>
WAITFOR DELAY '0:00:01'
>
SELECT
@.CPUBusy2 = @.@.CPU_BUSY
, @.TimeTicks2 = @.@.TIMETICKS
>
SELECT
@.CPUBusy1 AS CPUBusy1
, @.CPUBusy2 AS CPUBusy2
, @.CPUBusy2 - @.CPUBusy1 AS CPUDiff
, @.TimeTicks1 AS TimeTicks1
, @.TimeTicks2 AS TimeTicks2
, @.TimeTicks2 - @.TimeTicks1 AS TimeTicksDiff
>
Thanks for your time and help!
>
>
Vishal
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

SQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'
>
Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

Performance counters are in sysperfinfo on SQL 2000 and
sys.dm_os_performance_counters on SQL 2005, but I could find the item
you are looking for in these views.

But I saw in Books Online for SQL 2005 that these values are cumultative. To
get the present value, sample with some interval. I guess you could to
the same: query @.@.CPU_BUSY twice with a second or so in between.

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

capitalized queries shower?

Hi all,
One of my co-workers claims that a capitalized query like "SELECT foo FROM
bar" would run slower than "select foo from bar." This sounds absurd to me,
and since I can't find any reference that claims his statement is true, I've
decided to ask the community.
Your help is appreciated.
-Oleg.Can you give me the name of that co-worker? I have a bridge to sell ;-)
--
Jacco Schalkwijk
SQL Server MVP
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>|||> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
I agree 100%. I prefer queries where the keywords are capitalized; much
easier to parse and dissect visually.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||No, that's totally untrue.
But I highly recommend that you take advantage of the situation by making a
very large bet with your co-worker and force this person to prove it
unequivocally in order to win the bet.
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>|||> But I highly recommend that you take advantage of the situation by making
a
> very large bet with your co-worker and force this person to prove it
> unequivocally in order to win the bet.
And hopefully, you've already shut off his NNTP access so he doesn't stumble
upon the common sense brought up here. ;-)|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e3bbHeEYEHA.2816@.TK2MSFTNGP11.phx.gbl...
> I agree 100%. I prefer queries where the keywords are capitalized; much
> easier to parse and dissect visually.
I will concur on that one... All caps is easier for ME to read, but
certainly not a computer :)|||If you open SQL Profiler, from the tools in enterprise manager, do the 2
types of select in query analyzer, you will see that the duration for the
lowercase select will be lower than the uppercase select.
I would assume that the compiler has to convert the uppercase to lowercase
to compile the code first.
It is only a minimal speed difference, but there is a difference.
Jay Freeman
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>|||"Jay Freeman" <j.freeman@.halcyonsoftware.com> wrote in message
news:%23jEpJvEYEHA.1264@.TK2MSFTNGP11.phx.gbl...
> If you open SQL Profiler, from the tools in enterprise manager, do the 2
> types of select in query analyzer, you will see that the duration for the
> lowercase select will be lower than the uppercase select.
Looks the same to me, using this:
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics time on
SELECT * FROM PUBS..AUTHORS
go
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics time on
select * from pubs..authors
go
Parse and compile time was 0ms both times.

capitalized queries shower?

Hi all,
One of my co-workers claims that a capitalized query like "SELECT foo FROM
bar" would run slower than "select foo from bar." This sounds absurd to me,
and since I can't find any reference that claims his statement is true, I've
decided to ask the community.
Your help is appreciated.
-Oleg.
Can you give me the name of that co-worker? I have a bridge to sell ;-)
Jacco Schalkwijk
SQL Server MVP
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>
|||> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
I agree 100%. I prefer queries where the keywords are capitalized; much
easier to parse and dissect visually.
http://www.aspfaq.com/
(Reverse address to reply.)
|||No, that's totally untrue.
But I highly recommend that you take advantage of the situation by making a
very large bet with your co-worker and force this person to prove it
unequivocally in order to win the bet.
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>
|||> But I highly recommend that you take advantage of the situation by making
a
> very large bet with your co-worker and force this person to prove it
> unequivocally in order to win the bet.
And hopefully, you've already shut off his NNTP access so he doesn't stumble
upon the common sense brought up here. ;-)
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e3bbHeEYEHA.2816@.TK2MSFTNGP11.phx.gbl...
> I agree 100%. I prefer queries where the keywords are capitalized; much
> easier to parse and dissect visually.
I will concur on that one... All caps is easier for ME to read, but
certainly not a computer
|||If you open SQL Profiler, from the tools in enterprise manager, do the 2
types of select in query analyzer, you will see that the duration for the
lowercase select will be lower than the uppercase select.
I would assume that the compiler has to convert the uppercase to lowercase
to compile the code first.
It is only a minimal speed difference, but there is a difference.
Jay Freeman
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>
|||"Jay Freeman" <j.freeman@.halcyonsoftware.com> wrote in message
news:%23jEpJvEYEHA.1264@.TK2MSFTNGP11.phx.gbl...
> If you open SQL Profiler, from the tools in enterprise manager, do the 2
> types of select in query analyzer, you will see that the duration for the
> lowercase select will be lower than the uppercase select.
Looks the same to me, using this:
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics time on
SELECT * FROM PUBS..AUTHORS
go
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics time on
select * from pubs..authors
go
Parse and compile time was 0ms both times.

capacity was exceeded

I am getting the following error when I try to run a
remote query, it was working correctly for a while and
Suddenly I started getting this error below:
cannot create new transaction because capacity was exceeded
Why is this happening?
Thanks,
JohnThat doesn't sound like a SQL Server error to me. So, the error is probably
generated by your application/dev tool. You should check that documentation.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:040d01c3ad4a$c3d558b0$a101280a@.phx.gbl...
> I am getting the following error when I try to run a
> remote query, it was working correctly for a while and
> Suddenly I started getting this error below:
> cannot create new transaction because capacity was exceeded
> Why is this happening?
> Thanks,
> John|||John,
Is this the message? Are you getting it when calling ADO.Con.BeginTrans()?
Microsoft OLE DB Provider for SQL Server error '8004d01d'
Cannot create new transaction because capacity was exceeded
This appears to be an ADO / OLEDB problem. (One post (from a few years ago)
on Google http://tinyurl.com/ven4 refers to the problem being triggered by
calling a class module which had created an implicit transaction.)
Sorry that I am not able to help more, but perhaps this clue will help.
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:040d01c3ad4a$c3d558b0$a101280a@.phx.gbl...
> I am getting the following error when I try to run a
> remote query, it was working correctly for a while and
> Suddenly I started getting this error below:
> cannot create new transaction because capacity was exceeded
> Why is this happening?
> Thanks,
> John

Cant' run my SSIS Package from DTSRUN

Hi all.

I've working around this one. I have a package that receives a parameter in YYYYMMDD format. I've tried from dtsexecgui and pass parameter. Tried 20070101 and worked fine. Tried convert(varchar,getdate(),112) and the whole string is passed, not the result as I thought.

So, I google it, so I can do this convert. Find out that this can only be done by, for example, a stored procedure that runs a cmdshell (DTSRUN.exe).

I've deployed my package (LIXO) by the manifest (also tried in VS save copy as - think this is the same thing). And the package is under MSDB in SSIS.

But now, I trie to run the package with all I could, for example:

DTSRUN /S "." /N "LIXO" /E

DTSRUN /S "(localhost)" /N "LIXO" /E

Also tried with user and password. I think I tried everything. And the error is always the same.

Says

"Loading..."

but then it breaks telling that could not find the package...

I connect into SSIS, run the package and it works just fine.

Can't discover what is missing around here.

Thanks in advance.

Marco Francisco

DTSRUN works for DTS 2000 package, it can't do anything with SSIS 2005 packages.

Use DTEXEC.EXE

http://technet.microsoft.com/en-us/library/ms162810.aspx

|||Thank you very much Michael. That was it. Now it's all

working.

|||

Is there other way of executing dtexec? The production team will not enable xp_cmdshell, due to policies security. So I can't use this solution.

Other solution is to "force" variable to be getdate() in the parent package, but I'ld like to have other ideas/solutions. I want to make it throught a job sending YYYYMMDD parameter (based on GETDATE()) throught /SET statement.

Thanks.

|||

I can execute dtexec like this:

"C:\Progra~2\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /SQL "\PACKAGE_NAME" /SERVER "SERVER_NAME" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "VARIABLES"

my problem is, how can I use some SQL function in here. Function like CONVERT(varchar,getdate(),112). From what I've seen, this whole string (of convert function) is passed as the parameter and I want it's result in YYYYMMDD.

The purpose is to create a job (with SSIS step) that runs my SSIS package. But I want to edit command line manually so I can define the parameters as CONVERT(varchar,getdate(),112). Is this possible? Is this only possible with a Stored Procedure that builds this statement and execute it by xp_cmdshell?

|||Can't you edit the package so that instead of using that as a parameter, it just uses the expression as the default value for every execution?

If you're always passing in "convert(varchar,getdate(),112)" to the package, it doesn't make sense to continue passing that in when you can just apply that expression to a variable's expression property.

Cant' run my SSIS Package from DTSRUN

Hi all.

I've working around this one. I have a package that receives a parameter in YYYYMMDD format. I've tried from dtsexecgui and pass parameter. Tried 20070101 and worked fine. Tried convert(varchar,getdate(),112) and the whole string is passed, not the result as I thought.

So, I google it, so I can do this convert. Find out that this can only be done by, for example, a stored procedure that runs a cmdshell (DTSRUN.exe).

I've deployed my package (LIXO) by the manifest (also tried in VS save copy as - think this is the same thing). And the package is under MSDB in SSIS.

But now, I trie to run the package with all I could, for example:

DTSRUN /S "." /N "LIXO" /E

DTSRUN /S "(localhost)" /N "LIXO" /E

Also tried with user and password. I think I tried everything. And the error is always the same.

Says

"Loading..."

but then it breaks telling that could not find the package...

I connect into SSIS, run the package and it works just fine.

Can't discover what is missing around here.

Thanks in advance.

Marco Francisco

DTSRUN works for DTS 2000 package, it can't do anything with SSIS 2005 packages.

Use DTEXEC.EXE

http://technet.microsoft.com/en-us/library/ms162810.aspx

|||Thank you very much Michael. That was it. Now it's all

working.

|||

Is there other way of executing dtexec? The production team will not enable xp_cmdshell, due to policies security. So I can't use this solution.

Other solution is to "force" variable to be getdate() in the parent package, but I'ld like to have other ideas/solutions. I want to make it throught a job sending YYYYMMDD parameter (based on GETDATE()) throught /SET statement.

Thanks.

|||

I can execute dtexec like this:

"C:\Progra~2\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /SQL "\PACKAGE_NAME" /SERVER "SERVER_NAME" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "VARIABLES"

my problem is, how can I use some SQL function in here. Function like CONVERT(varchar,getdate(),112). From what I've seen, this whole string (of convert function) is passed as the parameter and I want it's result in YYYYMMDD.

The purpose is to create a job (with SSIS step) that runs my SSIS package. But I want to edit command line manually so I can define the parameters as CONVERT(varchar,getdate(),112). Is this possible? Is this only possible with a Stored Procedure that builds this statement and execute it by xp_cmdshell?

|||Can't you edit the package so that instead of using that as a parameter, it just uses the expression as the default value for every execution?

If you're always passing in "convert(varchar,getdate(),112)" to the package, it doesn't make sense to continue passing that in when you can just apply that expression to a variable's expression property.

Cant' run my SSIS Package from DTSRUN

Hi all.

I've working around this one. I have a package that receives a parameter in YYYYMMDD format. I've tried from dtsexecgui and pass parameter. Tried 20070101 and worked fine. Tried convert(varchar,getdate(),112) and the whole string is passed, not the result as I thought.

So, I google it, so I can do this convert. Find out that this can only be done by, for example, a stored procedure that runs a cmdshell (DTSRUN.exe).

I've deployed my package (LIXO) by the manifest (also tried in VS save copy as - think this is the same thing). And the package is under MSDB in SSIS.

But now, I trie to run the package with all I could, for example:

DTSRUN /S "." /N "LIXO" /E

DTSRUN /S "(localhost)" /N "LIXO" /E

Also tried with user and password. I think I tried everything. And the error is always the same.

Says

"Loading..."

but then it breaks telling that could not find the package...

I connect into SSIS, run the package and it works just fine.

Can't discover what is missing around here.

Thanks in advance.

Marco Francisco

DTSRUN works for DTS 2000 package, it can't do anything with SSIS 2005 packages.

Use DTEXEC.EXE

http://technet.microsoft.com/en-us/library/ms162810.aspx

|||Thank you very much Michael. That was it. Now it's all

working.

|||

Is there other way of executing dtexec? The production team will not enable xp_cmdshell, due to policies security. So I can't use this solution.

Other solution is to "force" variable to be getdate() in the parent package, but I'ld like to have other ideas/solutions. I want to make it throught a job sending YYYYMMDD parameter (based on GETDATE()) throught /SET statement.

Thanks.

|||

I can execute dtexec like this:

"C:\Progra~2\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /SQL "\PACKAGE_NAME" /SERVER "SERVER_NAME" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "VARIABLES"

my problem is, how can I use some SQL function in here. Function like CONVERT(varchar,getdate(),112). From what I've seen, this whole string (of convert function) is passed as the parameter and I want it's result in YYYYMMDD.

The purpose is to create a job (with SSIS step) that runs my SSIS package. But I want to edit command line manually so I can define the parameters as CONVERT(varchar,getdate(),112). Is this possible? Is this only possible with a Stored Procedure that builds this statement and execute it by xp_cmdshell?

|||Can't you edit the package so that instead of using that as a parameter, it just uses the expression as the default value for every execution?

If you're always passing in "convert(varchar,getdate(),112)" to the package, it doesn't make sense to continue passing that in when you can just apply that expression to a variable's expression property.

Cant' run my SSIS Package from DTSRUN

Hi all.

I've working around this one. I have a package that receives a parameter in YYYYMMDD format. I've tried from dtsexecgui and pass parameter. Tried 20070101 and worked fine. Tried convert(varchar,getdate(),112) and the whole string is passed, not the result as I thought.

So, I google it, so I can do this convert. Find out that this can only be done by, for example, a stored procedure that runs a cmdshell (DTSRUN.exe).

I've deployed my package (LIXO) by the manifest (also tried in VS save copy as - think this is the same thing). And the package is under MSDB in SSIS.

But now, I trie to run the package with all I could, for example:

DTSRUN /S "." /N "LIXO" /E

DTSRUN /S "(localhost)" /N "LIXO" /E

Also tried with user and password. I think I tried everything. And the error is always the same.

Says

"Loading..."

but then it breaks telling that could not find the package...

I connect into SSIS, run the package and it works just fine.

Can't discover what is missing around here.

Thanks in advance.

Marco Francisco

DTSRUN works for DTS 2000 package, it can't do anything with SSIS 2005 packages.

Use DTEXEC.EXE

http://technet.microsoft.com/en-us/library/ms162810.aspx

|||Thank you very much Michael. That was it. Now it's all

working.

|||

Is there other way of executing dtexec? The production team will not enable xp_cmdshell, due to policies security. So I can't use this solution.

Other solution is to "force" variable to be getdate() in the parent package, but I'ld like to have other ideas/solutions. I want to make it throught a job sending YYYYMMDD parameter (based on GETDATE()) throught /SET statement.

Thanks.

|||

I can execute dtexec like this:

"C:\Progra~2\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /SQL "\PACKAGE_NAME" /SERVER "SERVER_NAME" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "VARIABLES"

my problem is, how can I use some SQL function in here. Function like CONVERT(varchar,getdate(),112). From what I've seen, this whole string (of convert function) is passed as the parameter and I want it's result in YYYYMMDD.

The purpose is to create a job (with SSIS step) that runs my SSIS package. But I want to edit command line manually so I can define the parameters as CONVERT(varchar,getdate(),112). Is this possible? Is this only possible with a Stored Procedure that builds this statement and execute it by xp_cmdshell?

|||Can't you edit the package so that instead of using that as a parameter, it just uses the expression as the default value for every execution?

If you're always passing in "convert(varchar,getdate(),112)" to the package, it doesn't make sense to continue passing that in when you can just apply that expression to a variable's expression property.

Can't use TAB key in SQL Pane

Hello folks,
When working in Visual Studio 2005 Reporting Services, I have been
cursed with a small problem that I have run out of remedy ideas for.
On the data tab of a report I am no longer able to use the TAB key on
the keyboard to format my SQL. I say no longer because i was using
Visual Studio 2003 until recently and this problem did not exist. If
I hit TAB when writing code in the SQL pane the cursor just moves to
the next action object (button) as if on a form. The only band-aid
solution has been to write all my code in a SQL Management Studio
query window and than copy/paste it into the report SQL pane but this
is a nuisance, especially for short,easy report queries and when
returning to existing reports for upgrades or bug fixes. Another
workaround has been to copy a single TAB from Notepad or any other app
and paste the tab in the SQL pane when needed, but than i have to re-
copy it each time if i copy something else. Any ideas or
sympathizers, I would love to hear from you.
Thanks, and here is an example of what I mean.
/* this is what I am stuck with */
SELECT
foo.Column1,
foo.Column2
FROM
dbo.foo
/* this is what I want */
SELECT
foo.Column1,
foo.Column2
FROM
dbo.fooNo way t o use tab, you need to use spacebar with space, I understand if it
is a small query you can do it, but if it is a big query will be very
tedious.
what otherway you can do is, just click "generic query builder" and again
click what it does is, it indends automatically.
Amarnath
"Skilliam" wrote:
> Hello folks,
> When working in Visual Studio 2005 Reporting Services, I have been
> cursed with a small problem that I have run out of remedy ideas for.
> On the data tab of a report I am no longer able to use the TAB key on
> the keyboard to format my SQL. I say no longer because i was using
> Visual Studio 2003 until recently and this problem did not exist. If
> I hit TAB when writing code in the SQL pane the cursor just moves to
> the next action object (button) as if on a form. The only band-aid
> solution has been to write all my code in a SQL Management Studio
> query window and than copy/paste it into the report SQL pane but this
> is a nuisance, especially for short,easy report queries and when
> returning to existing reports for upgrades or bug fixes. Another
> workaround has been to copy a single TAB from Notepad or any other app
> and paste the tab in the SQL pane when needed, but than i have to re-
> copy it each time if i copy something else. Any ideas or
> sympathizers, I would love to hear from you.
> Thanks, and here is an example of what I mean.
> /* this is what I am stuck with */
> SELECT
> foo.Column1,
> foo.Column2
> FROM
> dbo.foo
> /* this is what I want */
> SELECT
> foo.Column1,
> foo.Column2
> FROM
> dbo.foo
>

can't update view

Hi all ,

I have a problem to insert new data to a view which created from 2 tables.

I am trying to so from run time

Any help will be appreciated

use an instead of INsert trigger for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Itamarqza, did the Instead Of resolve your issue? If not could you please provide more information.

Thanks,

Derek