query equivalent: statement

Showing posts with label statement. Show all posts

Monday, March 26, 2012

Looping through a list of values

Hi all
How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.
cheers
james :)In T-SQL? Even heart of a cursor?|||The only way that i could think of doing this is perhaps to use the 'IN' keyword within the 'WHERE' CLAUSE|||Originally posted by DoktorBlue
In T-SQL? Even heart of a cursor?

i'll have to look that up tonight. Never used cursors before.

thanx dok|||With a cursor, you can loop through a recordset, where the cursor references to one record at a time. The best way of understanding is to look for an example.

See DECLARE CURSOR (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp) for the syntax and soem examples in SQL Server 2000. Version 7 is similar.|||Originally posted by nano_electronix
Hi all

How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.

cheers
james :)

Yep.. the only way to do a while loop is by using cursor.. If there is another much better way, I'd like to know too..|||Just save result of query to temp table with IDENTITY column and make loop by this field...

Good luck|||give us more details, maybe there is no need to do the loop at all.|||Hi all

I did it... woohoo, thanx for all your help guys.
This is probably the longest TSQL i've ever written, 200+ lines and most importantly it works.

Special thanx to dok for pointing out CURSOR as a looping solution, it works magics.

Cheers
James

PS: Here is the single store procedure implementing the application logic i intended. (just to show off) hahahahah :D

CREATE PROCEDURE GenerateTimesheets
(
@.JobID decimal
)
AS
BEGIN
-- Constants
DECLARE @.AllocatedTime decimal

-- Used for looping through recordset with CURSORs
DECLARE @.AccumulatedTime decimal -- Accumulated time spent on a particular job
DECLARE @.SpentTime decimal -- Time spent on each subtask of a job
DECLARE @.TimesheetDate datetime
DECLARE @.JobUpdateID decimal
DECLARE @.TimesheetID decimal
DECLARE @.TimesheetType int

-- Initialize variables
SELECT @.AllocatedTime = allocatedTime
FROM Job WHERE jobID = @.JobID
SET @.AccumulatedTime = 0
SET @.TimesheetType = 1

-- Define CURSOR for each distinct day
-- Because timesheets are generated on a daily basis.
DECLARE date_cursor CURSOR FOR
SELECT DISTINCT CAST( CONVERT(varchar(10), dateSubmitted, 120) as datetime)
FROM jobUpdate WHERE jobUpdate.jobID = @.JobID

OPEN date_cursor
FETCH NEXT FROM date_cursor
INTO @.TimesheetDate
-- Create timesheets for each day
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Create a new timesheet for each day
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
-- Return the new timesheetID for reference
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType

-- Create new cursor to loop through jobupdates for a particular day
DECLARE jobupdate_cursor CURSOR FOR
SELECT spentTime, jobUpdateID
FROM jobUpdate
WHERE @.TimesheetDate = CAST(CONVERT(varchar(10), dateSubmitted, 120) as datetime)
AND jobID = @.JobID

OPEN jobupdate_cursor
FETCH NEXT FROM jobupdate_cursor
INTO @.SpentTime, @.JobUpdateID

-- Loop through and process all jobupdates for the day
-- creating new timesheets for CT and TA if necessary
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.AccumulatedTime = @.AccumulatedTime + @.SpentTime

------------
-- if currently in standard time phase
------------
if @.TimesheetType = 1
BEGIN
IF @.AccumulatedTime >= @.AllocatedTime + 2
BEGIN
-- Create new timesheet approval timesheet
SET @.TimesheetType = 3
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id

END
ELSE IF @.AccumulatedTime >= @.AllocatedTime
BEGIN
-- Create new completion time timesheet
SET @.TimesheetType = 2
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType

-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
ELSE
BEGIN
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
END

-------------
-- if currently in completion time phase
-------------
ELSE IF @.TimesheetType = 2
BEGIN
IF @.AccumulatedTime >= @.AllocatedTime + 2
BEGIN
-- Create new timesheet approval timesheet
SET @.TimesheetType = 3
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
ELSE
BEGIN
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
END

--------------
-- if currently in timesheet approval phase
--------------
ELSE IF @.TimesheetType = 3
BEGIN
-- Update the current job with the timesheet refernce id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END

FETCH NEXT FROM jobupdate_cursor
INTO @.SpentTime, @.JobUpdateID

END
CLOSE jobupdate_cursor
DEALLOCATE jobupdate_cursor

FETCH NEXT FROM date_cursor
INTO @.TimesheetDate

END
CLOSE date_cursor
DEALLOCATE date_cursor

END|||Hi James, very impressive! You are a fast learner.|||Originally posted by DoktorBlue
Hi James, very impressive! You are a fast learner.

Thanx dok !!!
I was wondering if you also know stuffs on Oracle as I will be working with oracle very soon, next monday actually. I guess I will asking questions in the Oracle forum, but it would be great if you are a Oracle expert too.

You have been very helpful, thanx again.

Cheers
James|||I think you have to be careful with cursors as they are not the most efficient methods available. In fact most problems can be solved without the need for cursors.

If you provide us with what you're trying to do, maybe someone will provide you with a set based solutions instead.|||I think James is quickly-learning poor programming habits. I can't believe this code does what he thinks it is doing, there are so many oportunities for errors. Why is TimeSheetType not reset to 1 when processing starts for a new day? I can't believe you need nested cursors to do whatever it is you are trying to do.

I can't figure out the logic from the code, and that at least is going to cause problems for whoever comes along and has to revise or debug it.

Add my vote to rdjabarov's and Crespo-n00b's that there ought to be a better solution.

blindman|||everyone learns from mistakes, won't take long ;-)|||Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.|||Taking a step into the cursor world is too often a step in the wrong direction. Cursors are too often a crutch used by procedural programmers. James needs to learn set-based processing, and their are a lot of individuals on this forum who would be happy to help him.

You may be impressed by volumes of code, but I'm impressed by short, elegant code that is simple, easily understood, well commented and readily debugged.

Inside every large program is a small program screaming to get out.

blindman|||Why don't you let it out, I'm curious to see whether you can add practise to you theory.|||Well, I've managed to shorten a lot of your code, and I and other members will help James if he asks.

I'll help you if you ask, too.

blindman|||You should go into politics. I challanged you to come with an elegant solution, but you didn't probably even understand James' problem. So, what's your point? Desperately referring to other threads? Hiding behind "other members"? The only one, who really helped James, was me.

About cursors: they are a usual mean to express functionality, and they are not bad at advance. It up to the user to use or abuse this functionality, like you can also easily write a "query from hell". The main point about cursors is, that the user takes responsibility of the execution plan from the Analyzer.

Blindman, if you want to respond, please do me a favour and focus on the issue of this thread, not other threads, not aiming at me, just show your short elegant solution, which would be a real contribution.|||Originally posted by DoktorBlue
Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.

What you say is true to a certain extent. There are view few cases where Cursors are needed and I believe that if people paid more attention to database concepts they would invariably find a set based solution to most of their SQL problems.

As the previous poster stated, it is hard to follow the logic of the cursor, and to make matters more complicated he has also used a nested cursor!

What we need is some CREATE TABLE statements and a few example records and I'm almost 100% sure that one of us here will come up with a much more efficient set based solution.

My 2 cents.|||I am also totally in agreement with blindman .
I would rather not use cursors ... let alone nested cursors , if there was an alternative way.

You should go into politics. I challanged you to come with an elegant solution, but you didn't probably even understand James' problem. So, what's your point? Desperately referring to other threads? Hiding behind "other members"? The only one, who really helped James, was me.

well , DoktorBlue , whats all the fighting for ... we are all here to share our knowledge .. dosent matter who is helping whom ... till the other guy gets the solution. Whats the problem in a little friendly discussion.|||I agree with DoktorBlue that you shouldn't continually jump onto threads and say that someone's solution is bad without offering a solution. The anti-cursor evangelists have done the same to me. I asked for a solution but they didn't provide one.

I'm actually a junior TSQL guy but even I can see that maybe the outer loop of this guy's code could be a WHILE loop looping through days (most timesheets don't skip days in the middle) but it would appear some folks are too scared to risk their lofty positions by actually suggesting some code. You'd think that this would be a simple enough of an example that they could put something out there....nope.

How do you get those stars under your name anyway...|||One more thing ...

If it is really a growing problem that cursors are often used inappropriately (because newbies are procedural rather than focused on how datasets work or for whatever reason), why not write a quick article and post it on the web (if it doesn't exist already). The article would show "practical" examples of common mistakes and how to avoid them.

That way, when you see samples of inappropriate cursor usage in these forums you can just tell the poster "You may benefit from this article" and give'em the URL. I could personally benefit from this article right now.

It's cool that folks exist out there like you guys who know the theories of what is good and bad but if nobody can explain in practical terms when they apply then...

Sorry for the rant.|||bill_dev,

I and other members have offered to provide more assistance with this problem, but we need more details about the requirements. We are not going to spend our time developing solutions (for free) without requirements. That is just chasing shadows.

Truth is, I have spent a lot of time looking over James' code, and as I stated quite a while back the first problem is that it is difficult to figure out what the code is supposed to be doing. Readability is important, especially if you have ever inherited a project, or even had to debug or modify something written six or twelve months before.

Cursors are crutches. They are slow, and awkward, and if you use them too much or too often they get painful. But like crutches, sometimes they are absolutely necessary and nothing else will do.

blindman|||I have to say I am in the anti-cursor faction. If you have a cursor on your system, try running it with task manager running, and watch the cpu graph. Now imagine that cursor being run by 5 or 6 other people at the same time. Truth is cursors are not scalable.
Another reason I am against cursors is that it promotes putting business logic directly on the database. If you know you are only going to have a very few records in these tables (under say 1000), then ok, the cursor can be used if there is no other way. But if you think, or even dream you will be going to hundreds of users, then the business logic has to come off the database. This is a job for application servers. Think about it this way. You can have many application servers, but you are stuck with one database.
To top it off, in many companies, you are not only stuck with one database server, but you have to share that server with all the other applications' databases. The performance on these servers is generally equal to the worst performing application.

As far as improvements in this particular procedure go, the only thing I have so far is to use select @.@.identity after the timesheet inserts, rather than select the new identity right off the table. If you have a million timesheets, you will see the performance degrade. If you have no index on the fields in the where clause, you guarantee that you have performance problems. Just things to watch for.

And, yes, I am still thinking about ways to eliminate the cursor entirely. So far, my best bet is to use a perl or VB script to do the actual logic.|||Originally posted by snail
Just save result of query to temp table with IDENTITY column and make loop by this field...

Good luck

snail's suggestion is good. Using a temp table is simpler than a cursor.

Here is a complete example:

-- Update area codes for people in the 415 area code
-- Set area code to 234
USE pubs
GO

-- Show values before update
SELECT au_id, phone
FROM authors
ORDER BY au_id

DECLARE @.error int
DECLARE @.authorCt int
DECLARE @.loopCt int

DECLARE @.authorsToUpdate TABLE (
EntryID integer IDENTITY(1,1),
au_id varchar(11),
phone char(12)
)

INSERT INTO @.authorsToUpdate (
au_id,
phone
)
SELECT
au_id,
REPLACE(phone, '415 ', '234 ')
FROM authors
WHERE phone LIKE '415 %'

SELECT
@.error = @.@.error,
@.authorCt = @.@.rowcount

-- Error check

IF @.authorCt > 0 BEGIN
SET @.loopCt = 0

BEGIN TRAN

WHILE @.loopCt < @.authorCt BEGIN
SET @.loopCt = @.loopCt + 1

UPDATE a
SET a.phone = u.phone
FROM authors AS a
JOIN @.authorsToUpdate AS u
ON a.au_id = u.au_id
WHERE u.EntryID = @.loopCt

-- Error check
END

-- Show values after update
SELECT au_id, phone
FROM authors
ORDER BY au_id

ROLLBACK TRAN
-- COMMIT TRAN
END
GO|||Cool. Very useful...Thanks|||Finally somebody with more than a story, but a proposal. Bravo!|||Thanx all for all your feedbacks.

I haven't looked this up for the last 2 days, so i missed all the discussions.

I think the suggestion of using temporary table is probably the way to get rid of cursor for certain cases. For the logic that i am trying to implement, using cursor is probably necessary and the most intuitive, but because the small amout of records that i have to process, there is no real performance issue for me.

The program logic that i tried to implement is to basically generate timesheets from jobupdate entries entered by developers. Basically how our company works is like follows.

1. Client submits a request
2. Project manager receives request and allocate Jobs to differnt developers. Each job has an allocated time.
3. Developers will update their job justifying what they did everytime they finish a task for that job, these jobupdates will include the time spent on this task

when ever a job is completed a number of timesheets would be generated from all the jobupdates submitted . for this particular company, they want to generate timesheets on a per job per day basis, what's more, all the jobupdates that exceeds the allocated time will be put into a differnt timesheet. so normally there would only be 1 timesheet per day when all the jobupdates submitted for a particular job are within allocated time. once allocated time is exceeded, there could be a maximum of 3 different timesheets generated for a particular day. Thus the reason that i have to process each entry individual and thus the need for cursor.

Because of this business logic, there is almost no way of avoiding cursor.

Cheers
James

PS: I love you guys, I have learnt a lot from dok and blindman and a number of other people since i joined. dbforums rocks because of you!! :)

Looping tables?

Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!You can use the undocumented sp_foreachtable:
sp_foreachtable ('select ''?'', count (*) from ?')
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!|||On Dec 27, 5:26=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via pu=re
> sql statement or SP) on a database and gives me the record count from each=
> one...
> Because I need to know the table wich has more records on it! Can you help=
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id =3D so.id
and so.type in ('U')
and si.status =3D 2066
order by so.name|||On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||Very very good man!
Thanks a lot !
"SB" <othellomy@.yahoo.com> escreveu na mensagem
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name|||On Dec 27, 5:00=A0pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=
> "Paulo" <prbs...@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via pu=re
> sql statement or SP) on a database and gives me the record count from each=
> one...
> Because I need to know the table wich has more records on it! Can you help=
> me ?
> Thanks!
That should be
sp_msforeachtable 'select ''?'', count (*) from ?'|||On Dec 27, 5:11=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Very very good man!
> Thanks a lot !
> "SB" <othell...@.yahoo.com> escreveu na mensagemnews:68856583-1664-4038-adf=b-ca87e89bbc02@.d4g2000prg.googlegroups.com...
> On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> > Hi, I would like to know if is possible to loop through all tables (via
> > pure
> > sql statement or SP) on a database and gives me the record count from ea=ch
> > one...
> > Because I need to know the table wich has more records on it! Can you he=lp
> > me ?
> > Thanks!
> This is a bit of a hack but works for me:
> select so.name,rowcnt
> from sysindexes si, sysobjects so
> where si.id =3D so.id
> and so.type in ('U')
> and si.status =3D 2066
> order by so.name
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx|||It works for 7.0, 2000 and 2005.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||Since I don't like to use undocumented procedures, since they can go away at
any time, here is the script I use for this
/* Start Script */
Create table #tmpRowCounts (
TblName varchar(128),
RowCt int
)
DECLARE crgetrows CURSOR
FOR select name from sysobjects where xtype = 'U' order by name
DECLARE @.tblname varchar(128)
OPEN crgetrows
FETCH NEXT FROM crgetrows INTO @.tblname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
Execute('
Declare @.rowcount int
select @.rowcount = count(*) from ' + @.tblname + '
Insert into #tmpRowCounts values(''' + @.tblname + ''',@.rowcount)
')
END
FETCH NEXT FROM crgetrows INTO @.tblname
END
CLOSE crgetrows
DEALLOCATE crgetrows
Select * from #tmpRowCounts
Drop table #tmpRowCounts
GO
/* End Script */
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure sql statement or SP) on a database and gives me the record count from
> each one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||> On SQL 2005?
Here's a SQL 2005-specific method:
SELECT
t.name,
SUM(rows) AS Rows
FROM sys.tables t
JOIN sys.partitions p ON
t.object_id = p.object_id
WHERE
p.index_id IN(0,1)
GROUP BY
t.name
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
> On SQL 2005?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
> news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
>> You can use the undocumented sp_foreachtable:
>> sp_foreachtable ('select ''?'', count (*) from ?')
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Paulo" <prbspfc@.uol.com.br> wrote in message
>> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> Hi, I would like to know if is possible to loop through all tables (via
>> pure
>> sql statement or SP) on a database and gives me the record count from
>> each
>> one...
>> Because I need to know the table wich has more records on it! Can you
>> help
>> me ?
>> Thanks!
>>
>|||Take your pick..........
select o.name tablename ,i.rows tblrowcount
from sysobjects o
inner join sysindexes i on (o.id = i.id)
where o.xtype = 'U' and o.name <> 'dtproperties'
and i.indid < 2 Order by tablename
"Paulo" wrote:
> Hi, I would like to know if is possible to loop through all tables (via pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>
>|||sysindexes is not guaranteed to be maintained to actual row count values.
See DBCC UPDATEUSAGE in BOL.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"SB" <othellomy@.yahoo.com> wrote in message
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name|||On Dec 27, 8:25=A0pm, DXC <D...@.discussions.microsoft.com> wrote:
> Take your pick..........
> select o.name tablename ,i.rows tblrowcount
> from sysobjects o
> =A0inner join sysindexes i on (o.id =3D i.id)
> where o.xtype =3D 'U' and o.name <> 'dtproperties'
> =A0and i.indid < 2 Order by tablename
>
> "Paulo" wrote:
> > Hi, I would like to know if is possible to loop through all tables (via =pure
> > sql statement or SP) on a database and gives me the record count from ea=ch
> > one...
> > Because I need to know the table wich has more records on it! Can you he=lp
> > me ?
> > Thanks!- Hide quoted text -
> - Show quoted text -
You need to refer this as well
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx

Looping tables?

Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!You can use the undocumented sp_foreachtable:
sp_foreachtable ('select ''?'', count (*) from ?')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
Hi, I would like to know if is possible to loop through all tables (via pure
sql statement or SP) on a database and gives me the record count from each
one...
Because I need to know the table wich has more records on it! Can you help
me ?
Thanks!|||On Dec 27, 5:26=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via pu=[/vbcol
]
re[vbcol=seagreen]
> sql statement or SP) on a database and gives me the record count from each=[/vbcol
]
[vbcol=seagreen]
> one...
> Because I need to know the table wich has more records on it! Can you help=[/vbcol
]
[vbcol=seagreen]
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id =3D so.id
and so.type in ('U')
and si.status =3D 2066
order by so.name|||On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||Very very good man!
Thanks a lot !
"SB" <othellomy@.yahoo.com> escreveu na mensagem
news:68856583-1664-4038-adfb-ca87e89bbc02@.d4g2000prg.googlegroups.com...
On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
This is a bit of a hack but works for me:
select so.name,rowcnt
from sysindexes si, sysobjects so
where si.id = so.id
and so.type in ('U')
and si.status = 2066
order by so.name|||On Dec 27, 5:00=A0pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=[/vbcol
]
[vbcol=seagreen]
> "Paulo" <prbs...@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via pu=[/vbcol
]
re[vbcol=seagreen]
> sql statement or SP) on a database and gives me the record count from each=[/vbcol
]
[vbcol=seagreen]
> one...
> Because I need to know the table wich has more records on it! Can you help=[/vbcol
]
[vbcol=seagreen]
> me ?
> Thanks!
That should be
sp_msforeachtable 'select ''?'', count (*) from ?'|||It works for 7.0, 2000 and 2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
On SQL 2005?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
> You can use the undocumented sp_foreachtable:
> sp_foreachtable ('select ''?'', count (*) from ?')
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure
> sql statement or SP) on a database and gives me the record count from each
> one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||On Dec 27, 5:11=A0pm, "Paulo" <prbs...@.uol.com.br> wrote:
> Very very good man!
> Thanks a lot !
> "SB" <othell...@.yahoo.com> escreveu na mensagemnews:68856583-1664-4038-adf=[/vbcol
]
b-ca87e89bbc02@.d4g2000prg.googlegroups.com...[vbcol=seagreen]
> On Dec 27, 5:26 pm, "Paulo" <prbs...@.uol.com.br> wrote:
>
ch[vbcol=seagreen]
>
lp[vbcol=seagreen]
>
> This is a bit of a hack but works for me:
> select so.name,rowcnt
> from sysindexes si, sysobjects so
> where si.id =3D so.id
> and so.type in ('U')
> and si.status =3D 2066
> order by so.name
Also refer
[url]http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=[/url
]
o-count-rows-from-a-table.aspx|||Since I don't like to use undocumented procedures, since they can go away at
any time, here is the script I use for this
/* Start Script */
Create table #tmpRowCounts (
TblName varchar(128),
RowCt int
)
DECLARE crgetrows CURSOR
FOR select name from sysobjects where xtype = 'U' order by name
DECLARE @.tblname varchar(128)
OPEN crgetrows
FETCH NEXT FROM crgetrows INTO @.tblname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
Execute('
Declare @.rowcount int
select @.rowcount = count(*) from ' + @.tblname + '
Insert into #tmpRowCounts values(''' + @.tblname + ''',@.rowcount)
')
END
FETCH NEXT FROM crgetrows INTO @.tblname
END
CLOSE crgetrows
DEALLOCATE crgetrows
Select * from #tmpRowCounts
Drop table #tmpRowCounts
GO
/* End Script */
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:eJlXstHSIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi, I would like to know if is possible to loop through all tables (via
> pure sql statement or SP) on a database and gives me the record count from
> each one...
> Because I need to know the table wich has more records on it! Can you help
> me ?
> Thanks!
>|||> On SQL 2005?
Here's a SQL 2005-specific method:
SELECT
t.name,
SUM(rows) AS Rows
FROM sys.tables t
JOIN sys.partitions p ON
t.object_id = p.object_id
WHERE
p.index_id IN(0,1)
GROUP BY
t.name
Hope this helps.
Dan Guzman
SQL Server MVP
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23RBSYEISIHA.1184@.TK2MSFTNGP04.phx.gbl...
> On SQL 2005?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> escreveu na mensagem
> news:e8MQlAISIHA.6036@.TK2MSFTNGP03.phx.gbl...
>

Looping Question!!..........

Hi all,
I have an initial parameter = 'TST0001'
I want to write an INSERT statement to automatically take the initial
parameter 'TST0001' and keep adding 1 to it until it get to 'TST9999'.
Now, my table should store data like these:
TST0001
TST0002
...
...
TST0010
TST0011
...
...
TST9999
Thanks,
Tom dYou can avoid looping by using a Numbers table for this sort of thing. See
the following articles:
http://www.bizdatasolutions.co_m/tsql/tblnumbers.asp
http://www.aspfaq.com/show.asp?id=2516
Here's an example:
CREATE TABLE foo (x VARCHAR(10) PRIMARY KEY)
INSERT INTO foo (x)
SELECT 'TST'+
RIGHT('0000'+CAST(N1.number*100+N2.number AS VARCHAR(4)),4)
FROM master.dbo.spt_values AS N1,
master.dbo.spt_values AS N2
WHERE N1.type = 'P'
AND N1.number BETWEEN 0 AND 99
AND N2.type = 'P'
AND N2.number BETWEEN 0 AND 99
I don't recommend you use this in any persistent code because spt_values
isn't documented. This is just to demonstrate what you can do with auxiliary
tables.
David Portas
SQL Server MVP
--

Friday, March 23, 2012

Loopback Server Policy

Hello all,

Recently, we ran into the issue that you can't do an insert into..exec statement on a loopback linked server that was previously commented on in:

http://www.dbnewsgroups.net/link.aspx?url=http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124137&SiteID=1

So, for example, if you have a linkedserver to a database that happens to be on the same server as the querying thread, it fails with the message 'context in use'.

The answer from the previous thread was, don't use linked servers when the database is on the same server.

However,

The enviornment in our production system is fairly dynamic -- operations can be expected to move databases around in response to load balancing issues. We were counting on linked servers to make certain (non-performance sensitive) queries without regard to where a given database was located. Accepting that we have to make an exception case where the database lives on the same server means we'll have to have two sets of queries for every case this happens.

Something like

(pseudocode)

If server of linkedserver <> @.@.server

Insert into table....

Exec linkedserver.database.dbo.sproc

Else

Insert Into Table

exec database.dbo.sproc

(end pseudocode)

This seems pretty kludgy to me -- any suggestions on how to better manage this situation?

Thanks in advance

use cluster services instead of linked server

Wednesday, March 21, 2012

Loop through all User Tables

Hi,
I would like to execute a sql statement on all user tables of my db. Do you
know how to script that this statement loops through all user tables?
Thanks in advance
Graham SmithMay this *undocumented* proc will help
EXEC sp_MSforeachtable 'SELECT TOP 1 * FROM ?'
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Graham Smith" <graham.smith@.bbank.com> wrote in message
news:uEp8hj33FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I would like to execute a sql statement on all user tables of my db. Do
> you know how to script that this statement loops through all user tables?
> Thanks in advance
> Graham Smith
>|||You can generate one this way:
SELECT 'SELECT TOP 1 * FROM ['
+ TABLE_SCHEMA + '].['
+ TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'
AND OBJECTPROPERTY(OBJECT_ID('['
+ TABLE_SCHEMA + '].['
+ TABLE_NAME + ']'), 'IsMsShipped') = 0
"Graham Smith" <graham.smith@.bbank.com> wrote in message
news:uEp8hj33FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I would like to execute a sql statement on all user tables of my db. Do
> you know how to script that this statement loops through all user tables?
> Thanks in advance
> Graham Smith
>

Loop a select statement?

Hi,
I have a select statement which brings back several fields and several
columns. Within one of these columns is an email address. I want to be
able to cycle through each record in the select statement and email the
details attached to their email address. My SP is below:-
SELECT dbo.tbl_surveillance.s_id as REG_NO,
dbo.tbl_surveillance_dates.sd_urn as URN,
dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
dbo.tbl_surveillance_dates.sd_renewal_date as
RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
FROM dbo.tbl_surveillance INNER JOIN
dbo.tbl_surveillance_dates ON
dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
JOIN
dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
dbo.tbl_email.e_id
WHERE getdate() > dateadd(day, -7, sd_renewal_date)
I know you can use the following to send emails:-
@.sbj varchar(200),
@.msg varchar(2000),
@.recipient varchar(50)
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
but I want to incorporate that xp_sendmail with the select statement.
If anyone has ever done this before and can give me some pointers it would
be greatly appreciated.
Thanks
DamonI think you will need to use a cursor.
Here's some help with your SELECT statement, by the way. It's still largely
unreadable because of the superfluous prefixes and mismatched column names,
but it should be a little easier to tackle. Note that I changed the WHERE
clause to apply transformation to the constant, instead ofto the column, and
kept with the tradition of object-operator-value instead of
value-operator-object. getdate()>column+x is very difficult to process, at
least for me. If the table is huge, you may find an advantage in declaring
a variable of smalldatetime up front and calculating GETDATE()+7 and storing
it in a constant. However, if there is no index on sd_renewal_date, it's
probably all moot.
SELECT
Reg_No = s.s_id,
URN = d.sd_urn,
OFFICER = d.sd_reviewing_officer,
RENEWAL_DATE = d.sd_renewal_date,
OFFICER_EMAIL = e.e_officer_email
FROM
dbo.tbl_surveillance s
INNER JOIN
dbo.tbl_surveillance_dates d
ON
s.s_id = d.sd_s_id
LEFT OUTER JOIN
dbo.tbl_email e
ON
d.sd_e_id = e.e_id
WHERE
d.sd_renewal_date < GETDATE()+7;
If the cursor's only purpose is to send e-mail, then you probably want an
inner join against tbl_email. What is the point of getting rows where there
isn't a valid recipient?
I'd write the cursor for you, but it is entirely unclear to me how you are
deriving @.sbj and @.msg based on Reg_No, URN, OFFICER, and RENEWAL_DATE.
Please see http://www.aspfaq.com/5006
"Damon" <nonsense@.nononsense.com> wrote in message
news:O6Xnf.19494$8v6.12132@.newsfe6-gui.ntli.net...
> Hi,
> I have a select statement which brings back several fields and several
> columns. Within one of these columns is an email address. I want to be
> able to cycle through each record in the select statement and email the
> details attached to their email address. My SP is below:-
> SELECT dbo.tbl_surveillance.s_id as REG_NO,
> dbo.tbl_surveillance_dates.sd_urn as URN,
> dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
> dbo.tbl_surveillance_dates.sd_renewal_date as
> RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
> FROM dbo.tbl_surveillance INNER JOIN
> dbo.tbl_surveillance_dates ON
> dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
> JOIN
> dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
> dbo.tbl_email.e_id
> WHERE getdate() > dateadd(day, -7, sd_renewal_date)
> I know you can use the following to send emails:-
> @.sbj varchar(200),
> @.msg varchar(2000),
> @.recipient varchar(50)
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> but I want to incorporate that xp_sendmail with the select statement.
> If anyone has ever done this before and can give me some pointers it would
> be greatly appreciated.
> Thanks
> Damon
>|||Thank you very much for your reply. I can see why you are a SQL Server MVP.
Unfortunately my works have not supplied me with SQL training so have had to
learn myself, thus the messy code.
The @.sbj would be the same everytime, something like "List of renewal
dates". @.msg would literally be a compilation of the other fields i.e.
Reg_no & ', ' & URN & ', ' & RENEWAL_DATE. This is just so the officer
being emailed can see the list of renewals that they have which are due up
within the next w

.
I really appreciate your help. Need to get on to my works to send me on a
SQL course.
Thanks again
Damon.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrdL3fMAGHA.532@.TK2MSFTNGP15.phx.gbl...
>I think you will need to use a cursor.
> Here's some help with your SELECT statement, by the way. It's still
> largely unreadable because of the superfluous prefixes and mismatched
> column names, but it should be a little easier to tackle. Note that I
> changed the WHERE clause to apply transformation to the constant, instead
> ofto the column, and kept with the tradition of object-operator-value
> instead of value-operator-object. getdate()>column+x is very difficult to
> process, at least for me. If the table is huge, you may find an advantage
> in declaring a variable of smalldatetime up front and calculating
> GETDATE()+7 and storing it in a constant. However, if there is no index
> on sd_renewal_date, it's probably all moot.
> SELECT
> Reg_No = s.s_id,
> URN = d.sd_urn,
> OFFICER = d.sd_reviewing_officer,
> RENEWAL_DATE = d.sd_renewal_date,
> OFFICER_EMAIL = e.e_officer_email
> FROM
> dbo.tbl_surveillance s
> INNER JOIN
> dbo.tbl_surveillance_dates d
> ON
> s.s_id = d.sd_s_id
> LEFT OUTER JOIN
> dbo.tbl_email e
> ON
> d.sd_e_id = e.e_id
> WHERE
> d.sd_renewal_date < GETDATE()+7;
> If the cursor's only purpose is to send e-mail, then you probably want an
> inner join against tbl_email. What is the point of getting rows where
> there isn't a valid recipient?
> I'd write the cursor for you, but it is entirely unclear to me how you are
> deriving @.sbj and @.msg based on Reg_No, URN, OFFICER, and RENEWAL_DATE.
> Please see http://www.aspfaq.com/5006
>
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:O6Xnf.19494$8v6.12132@.newsfe6-gui.ntli.net...
>|||I've taken an example cursor from Books Online and adjusted it somewhat to
fit your situation. However, it's just a rough draft and you will need to
complete it. Basically, the cursor allows you to iternate through the query
result one row at a time, giving you the ability to populate variables and
exec the xp_sendmail call. Every column that you plan to reference will need
to be assigned a variable. I've also added the FAST_FORWARD option so it
should use less resources.
DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
SELECT dbo.tbl_surveillance.s_id as REG_NO,
dbo.tbl_surveillance_dates.sd_urn as URN,
dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
dbo.tbl_surveillance_dates.sd_renewal_date as
RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
FROM dbo.tbl_surveillance INNER JOIN
dbo.tbl_surveillance_dates ON
dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
JOIN
dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
dbo.tbl_email.e_id
WHERE getdate() > dateadd(day, -7, sd_renewal_date)
OPEN surveillance_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables should be in the same order as the columns in the
SELECT statement.
FETCH NEXT FROM surveillance_cursor
INTO @.recipient, @.Reg_no, @.RENEWAL_DATE, etc.
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sbj = ?
select @.msg = @.Reg_no + ', ' + @.URN + ', ' + @.RENEWAL_DATE + etc.
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.recipient, @.Reg_no, @.RENEWAL_DATE
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
"Damon" <nonsense@.nononsense.com> wrote in message
news:cCXnf.28255$XZ6.26473@.newsfe1-gui.ntli.net...
> Thank you very much for your reply. I can see why you are a SQL Server
> MVP. Unfortunately my works have not supplied me with SQL training so have
> had to learn myself, thus the messy code.
> The @.sbj would be the same everytime, something like "List of renewal
> dates". @.msg would literally be a compilation of the other fields i.e.
> Reg_no & ', ' & URN & ', ' & RENEWAL_DATE. This is just so the officer
> being emailed can see the list of renewals that they have which are due up
> within the next w

.
> I really appreciate your help. Need to get on to my works to send me on a
> SQL course.
> Thanks again
> Damon.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OrdL3fMAGHA.532@.TK2MSFTNGP15.phx.gbl...
>|||>> I have a select statement which brings back several fields and several co
lumns. <<
Interesting, since SQL does not have fields and columns are a totally
different concept.
More interesting, since SQL does not have records and rows are a
totally different concept. Tables are sets and not files;
sets by definition have no ordering, so cycles make no sense
whatsoever.
You need to use a cursor (explicit or hidden in a called procedure) to
convert the result table into a sequential structure that can have
loops. While you are catching up on the foundations of RM, you might
also want to learn ISO-11179 so that you stop using that silly "tbl-"
in your code, Standard SQL keywords, etc. You are writing SQL like a
procedural or OO programmer because you have not got the right mindset
yet.|||CELKO,
As I mentioned in my previous message, I have not had any official training
in SQL as my employer has not yet put me on a course, I have had to try and
learn this by myself so you will have to forgive my wording and code. I am
a VB programmer so most of the stuff I do I do in VB as I do not know SQL
very well and I find it easier to do in VB.
Thanks for your reply.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134664262.456898.41440@.g14g2000cwa.googlegroups.com...
> Interesting, since SQL does not have fields and columns are a totally
> different concept.
>
> More interesting, since SQL does not have records and rows are a
> totally different concept. Tables are sets and not files;
> sets by definition have no ordering, so cycles make no sense
> whatsoever.
> You need to use a cursor (explicit or hidden in a called procedure) to
> convert the result table into a sequential structure that can have
> loops. While you are catching up on the foundations of RM, you might
> also want to learn ISO-11179 so that you stop using that silly "tbl-"
> in your code, Standard SQL keywords, etc. You are writing SQL like a
> procedural or OO programmer because you have not got the right mindset
> yet.
>|||JT,
Thank you very much for your reply. I really appreciate your time in
replying in such detail.
I will have a crack @. this today.
Thanks again.
"JT" <someone@.microsoft.com> wrote in message
news:eNhERNZAGHA.2560@.TK2MSFTNGP12.phx.gbl...
> I've taken an example cursor from Books Online and adjusted it somewhat to
> fit your situation. However, it's just a rough draft and you will need to
> complete it. Basically, the cursor allows you to iternate through the
> query result one row at a time, giving you the ability to populate
> variables and exec the xp_sendmail call. Every column that you plan to
> reference will need to be assigned a variable. I've also added the
> FAST_FORWARD option so it should use less resources.
> DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
> SELECT dbo.tbl_surveillance.s_id as REG_NO,
> dbo.tbl_surveillance_dates.sd_urn as URN,
> dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
> dbo.tbl_surveillance_dates.sd_renewal_date as
> RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
> FROM dbo.tbl_surveillance INNER JOIN
> dbo.tbl_surveillance_dates ON
> dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
> JOIN
> dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
> dbo.tbl_email.e_id
> WHERE getdate() > dateadd(day, -7, sd_renewal_date)
>
> OPEN surveillance_cursor
> -- Perform the first fetch and store the values in variables.
> -- Note: The variables should be in the same order as the columns in the
> SELECT statement.
> FETCH NEXT FROM surveillance_cursor
> INTO @.recipient, @.Reg_no, @.RENEWAL_DATE, etc.
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sbj = ?
> select @.msg = @.Reg_no + ', ' + @.URN + ', ' + @.RENEWAL_DATE + etc.
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.recipient, @.Reg_no, @.RENEWAL_DATE
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:cCXnf.28255$XZ6.26473@.newsfe1-gui.ntli.net...
>

Monday, March 19, 2012

Lookup with more possibilities?

How can I do a lookup which doens't directly link two columns but uses another statement?

I tried in advanced with:

Code Snippet

select * from
(select * from [dbo].[Employees]) as refTable
where [refTable].[EM_ID] = ? and [refTable].[EM_From] <= ? and
([refTable].[EM_Until] > ? or [refTable].[EM_Until] IS NULL)

and adding 2 parameters.

Error 1 Validation error. Fill Planning: Lookup [2376]: An input column with the lineage ID 1760, referenced in the ParameterMap custom property with the parameter on position number 1, cannot be found in the input columns collection. Package.dtsx 0 0

So I guess that's not the way to go. Any other way to tackle this?

Aren't you missing a ")" at the end? I have sucessfully tried using the advanced tab to input parameters into the lookup transform in the past....

Here is a decent article on this type of action: http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range

|||Nope no ) missing. I got 2 ( and 2 ) so that's all good. Strange, it should work, I'll play some more with it. Strange thing, it works when I don't do the advanced stuff so something must be wrong there.|||

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

|||

rept wrote:

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

Sure.|||

yes.

Notice that parameter 1 and 2 of the julian kuiters article are both "modifydate".

|||

Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?

|||

" Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?"

I have the same question. It looks like that SQL is more complex that it should be; and I know for sure that SSIS is not very good at finding the parameter in subqueries. Give it a try without using that in-line-view and see if that fix the problem.

|||

Thanks for all the replies!

I just extended what SSIS had by default (same as in Julian Kuiters article as well BTW). I replaced it now but no difference however.

|||

Finally figured it out.

You need to make sure that every parameter that you use in the query is also connected graphically in the columns tab! It doesn't matter if the relation you draw doesn't make sence, you need to for SSIS to be able to find the input column! Hope this will save someone a lot of time someday

Thanks for all who replied!

Lookup with more possibilities?

How can I do a lookup which doens't directly link two columns but uses another statement?

I tried in advanced with:

Code Snippet

select * from
(select * from [dbo].[Employees]) as refTable
where [refTable].[EM_ID] = ? and [refTable].[EM_From] <= ? and
([refTable].[EM_Until] > ? or [refTable].[EM_Until] IS NULL)

and adding 2 parameters.

So I guess that's not the way to go. Any other way to tackle this?

Aren't you missing a ")" at the end? I have sucessfully tried using the advanced tab to input parameters into the lookup transform in the past....

Here is a decent article on this type of action: http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

|||

rept wrote:

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

Sure.|||

yes.

Notice that parameter 1 and 2 of the julian kuiters article are both "modifydate".

|||

Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?

|||

" Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?"

|||

Thanks for all the replies!

I just extended what SSIS had by default (same as in Julian Kuiters article as well BTW). I replaced it now but no difference however.

|||

Finally figured it out.

Thanks for all who replied!

Lookup Transform with Variable Parameter

Is it possible to use a VARIABLE in the Lookup Transform? I am setting the cache mode to partial and have modified the caching SQL statement on the advanced tab to include the parameterized query, but the parameter button only allows me to select columns to map to the parameter. I need to use a variable instead. I see the ParameterMap property of the transform in the advanced editor, but don't see how I can use this to map to a variable.

Can this be done, or do I need to use a new source, sort and left join component to accomplish the same thing?

Thanks!

Brandon

Brandon I don't believe this can be done with the Lookup Transform, as I have ran into this limitation before.
Adrian
|||

The way I did it was to use a derived column transform before the lookup transform that "transform" my variable in a column. By doing this, I can now see the new column in the input column of the "set query parametsrs" parameter window.

Ccote

Wednesday, March 7, 2012

looking for something like SHOW CREATE TABLE

After I do a
SELECT * INTO #temp_table FROM x WHERE y
I would like to see a CREATE TABLE statement for #temp_table somehow.Hi
"metaperl" wrote:
> After I do a
> SELECT * INTO #temp_table FROM x WHERE y
> I would like to see a CREATE TABLE statement for #temp_table somehow.
>
You can view the table in tempdb, but it will be called something like
#temp_table_____________________________12345678. The object browser (F8)in
Query Analyser should show it but the scripting options will not work!
The table structure would be the same as table x, so you can script that and
alter the script to remove contraints and indexes.
John|||On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> The table structure would be the same as table x, so you can script that and
> alter the script to remove contraints and indexes.
table x was hypothetical. in fact, this final temp table is the result
of many joins of many temp tables... so it's much easier to see the
schema of this final output temp table than to trace back through all
the joins and try to figure out its schema that way
> John
Terrence!|||On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "metaperl" wrote:
> > I would like to see a CREATE TABLE statement for #temp_table somehow.
> You can view the table in tempdb, but it will be called something like
> #temp_table_____________________________12345678. The object browser (F8)in
> Query Analyser should show it but the scripting options will not work!
What is tempdb? I am on MS-SQL 2000 still. I did not see such a table
in the Catalog (I use Synametrics WinSQL, we dont have anything else
installed for analyzing tables).|||Hi
"metaperl" wrote:
> On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > "metaperl" wrote:
> >
> > > I would like to see a CREATE TABLE statement for #temp_table somehow.
> >
> > You can view the table in tempdb, but it will be called something like
> > #temp_table_____________________________12345678. The object browser (F8)in
> > Query Analyser should show it but the scripting options will not work!
> What is tempdb? I am on MS-SQL 2000 still. I did not see such a table
> in the Catalog (I use Synametrics WinSQL, we dont have anything else
> installed for analyzing tables).
>
Tempdb is one of the system databases used for things such as temporary
tables and is sometimes used for other system actions such as rebuilding
indexes.
You should download books online and read the administration and
architecture sections. SQL 2005 books online can be downloaded from
http://go.microsoft.com/fwlink/?linkid=50478 SQL 2000 can be downloaded from
http://technet.microsoft.com/en-us/sqlserver/bb331733.aspx
You probably have MSDE or SQLExpress in which case download Microsoft SQL
Server Management Studio Express
http://www.microsoft.com/downloads/details.aspx?FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en if you are using SQL Express or
if you have SQL 2000 the SQL Server Web Data Administrato
http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&DisplayLang=en
If you don't want to do this, then you could write some DMO/SMO code to get
the table definitions (see Books Online). If you changed the statement to not
use a temporary table then you could get the definition of the table that you
have created rather than using the source tables. The T-SQL command sp_help
may give you enough information to create your own CREATE TABLE statement use
EXEC sp_help MyTable
John

looking for something like SHOW CREATE TABLE

After I do a
SELECT * INTO #temp_table FROM x WHERE y
I would like to see a CREATE TABLE statement for #temp_table somehow.
Hi
"metaperl" wrote:

> After I do a
> SELECT * INTO #temp_table FROM x WHERE y
> I would like to see a CREATE TABLE statement for #temp_table somehow.
>
You can view the table in tempdb, but it will be called something like
#temp_table_____________________________12345678. The object browser (F8)in
Query Analyser should show it but the scripting options will not work!
The table structure would be the same as table x, so you can script that and
alter the script to remove contraints and indexes.
John
|||On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:

> The table structure would be the same as table x, so you can script that and
> alter the script to remove contraints and indexes.
table x was hypothetical. in fact, this final temp table is the result
of many joins of many temp tables... so it's much easier to see the
schema of this final output temp table than to trace back through all
the joins and try to figure out its schema that way

> John
Terrence!
|||On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "metaperl" wrote:

>
> You can view the table in tempdb, but it will be called something like
> #temp_table_____________________________12345678. The object browser (F8)in
> Query Analyser should show it but the scripting options will not work!
What is tempdb? I am on MS-SQL 2000 still. I did not see such a table
in the Catalog (I use Synametrics WinSQL, we dont have anything else
installed for analyzing tables).
|||Hi
"metaperl" wrote:

> On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
>
> What is tempdb? I am on MS-SQL 2000 still. I did not see such a table
> in the Catalog (I use Synametrics WinSQL, we dont have anything else
> installed for analyzing tables).
>
Tempdb is one of the system databases used for things such as temporary
tables and is sometimes used for other system actions such as rebuilding
indexes.
You should download books online and read the administration and
architecture sections. SQL 2005 books online can be downloaded from
http://go.microsoft.com/fwlink/?linkid=50478 SQL 2000 can be downloaded from
http://technet.microsoft.com/en-us/sqlserver/bb331733.aspx
You probably have MSDE or SQLExpress in which case download Microsoft SQL
Server Management Studio Express
http://www.microsoft.com/downloads/details.aspx?FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en if you are using SQL Express or
if you have SQL 2000 the SQL Server Web Data Administrator
http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&DisplayLang=en
If you don't want to do this, then you could write some DMO/SMO code to get
the table definitions (see Books Online). If you changed the statement to not
use a temporary table then you could get the definition of the table that you
have created rather than using the source tables. The T-SQL command sp_help
may give you enough information to create your own CREATE TABLE statement use
EXEC sp_help MyTable
John

looking for something like SHOW CREATE TABLE

After I do a
SELECT * INTO #temp_table FROM x WHERE y
I would like to see a CREATE TABLE statement for #temp_table somehow.Hi
"metaperl" wrote:

> After I do a
> SELECT * INTO #temp_table FROM x WHERE y
> I would like to see a CREATE TABLE statement for #temp_table somehow.
>
You can view the table in tempdb, but it will be called something like
#temp_table_____________________________
12345678. The object browser (F8)in
Query Analyser should show it but the scripting options will not work!
The table structure would be the same as table x, so you can script that and
alter the script to remove contraints and indexes.
John|||On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:

> The table structure would be the same as table x, so you can script that a
nd
> alter the script to remove contraints and indexes.
table x was hypothetical. in fact, this final temp table is the result
of many joins of many temp tables... so it's much easier to see the
schema of this final output temp table than to trace back through all
the joins and try to figure out its schema that way

> John
Terrence!|||On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "metaperl" wrote:

>
> You can view the table in tempdb, but it will be called something like
> #temp_table_____________________________
12345678. The object browser (F8)i
n
> Query Analyser should show it but the scripting options will not work!
What is tempdb? I am on MS-SQL 2000 still. I did not see such a table
in the Catalog (I use Synametrics WinSQL, we dont have anything else
installed for analyzing tables).|||Hi
"metaperl" wrote:

> On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
>
> What is tempdb? I am on MS-SQL 2000 still. I did not see such a table
> in the Catalog (I use Synametrics WinSQL, we dont have anything else
> installed for analyzing tables).
>
Tempdb is one of the system databases used for things such as temporary
tables and is sometimes used for other system actions such as rebuilding
indexes.
You should download books online and read the administration and
architecture sections. SQL 2005 books online can be downloaded from
http://go.microsoft.com/fwlink/?linkid=50478 SQL 2000 can be downloaded from
http://technet.microsoft.com/en-us/...r/bb331733.aspx
You probably have MSDE or SQLExpress in which case download Microsoft SQL
Server Management Studio Express
http://www.microsoft.com/downloads/...&displaylang=en if you are using SQL Express or
if you have SQL 2000 the SQL Server Web Data Administrator
http://www.microsoft.com/downloads/...&DisplayLang=en
If you don't want to do this, then you could write some DMO/SMO code to get
the table definitions (see Books Online). If you changed the statement to no
t
use a temporary table then you could get the definition of the table that yo
u
have created rather than using the source tables. The T-SQL command sp_help
may give you enough information to create your own CREATE TABLE statement us
e
EXEC sp_help MyTable
John

looking for some hints on SP performance

I'm trying to figure out why a SQL statement will run faster in a query
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JD
does it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon
|||I suggest you first read up on the difference between constants, parameters and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is created and estimates
selectivity. Plan is created based on that and re-used (even if not optimal for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...
> I'm trying to figure out why a SQL statement will run faster in a query window then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5 minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I check it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless indexes have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with this.
> Thanks,
> JD
>
|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =
DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARCHAR(8),@.ST ART_DATE),101),CONVERT(DATETIME,CONVERT(VARCHAR(8) ,@.END_DATE),101))
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1,CHARINDEX (',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1,LEN(@.USER S))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('\',Resou rces.LogonName)+1),LEN(Resources.LogonName)-CHARINDEX('\',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.Logon Name)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.Logon Name)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('\',U.LogonName)+ 1),LEN(U.LogonName)-CHARINDEX('\',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>
|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>

looking for some hints on SP performance

I'm trying to figure out why a SQL statement will run faster in a query
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JDdoes it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon|||I suggest you first read up on the difference between constants, parameters
and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is crea
ted and estimates
selectivity. Plan is created based on that and re-used (even if not optimal
for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average
of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equa
ls returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...reen">
> I'm trying to figure out why a SQL statement will run faster in a query wi
ndow then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5
minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting
it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I c
heck it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless index
es have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with thi
s.
> Thanks,
> JD
>|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =
DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARC
HAR(8),@.START_DATE),101),CONVERT(DAT
ETIME,CONVERT(VARCHAR(8),@.END_DATE),101)
)
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1
,CHARINDEX(',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1
,LEN(@.USERS))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('',Resources.LogonName)+1),LEN(Res
ources.LogonName)-CHARINDEX('',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('',SSIU.LogonName)+1),LEN(SSIU.LogonNam
e)-CHARINDEX('',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('',SSIU.LogonName)+1),LEN(SSIU.LogonNam
e)-CHARINDEX('',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('',U.LogonName)+1),LEN(U.LogonName)-CHARIN
DEX('',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>

Monday, March 26, 2012

Looping through a list of values

Looping tables?

Looping tables?

Looping tables?

Looping Question!!..........

Friday, March 23, 2012

Loopback Server Policy

Wednesday, March 21, 2012

Loop through all User Tables

Loop a select statement?

Monday, March 19, 2012

Lookup with more possibilities?

Lookup with more possibilities?

Lookup Transform with Variable Parameter

Wednesday, March 7, 2012

looking for something like SHOW CREATE TABLE

looking for something like SHOW CREATE TABLE

looking for something like SHOW CREATE TABLE

looking for some hints on SP performance

looking for some hints on SP performance

query equivalent

Blog Archive

About Me