exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
ThanksHassan
http://sqlblog.com/blogs/linchi_she...erver-2000.aspx
http://sqlblog.com/blogs/linchi_she...lification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_she...erver-2000.aspx
> http://sqlblog.com/blogs/linchi_she...lification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>

exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
ThanksHassan
http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>>I know it was best practise to call a stored procedure as
>> "exec dbo.sproc" instead of "exec sproc"
>> I guess it was in reference to compilations.
>> But when i monitored compilations/sec and recompilations/sec, I did not
>> see it happening.
>> Is there another reason for why I need to use the username.sproc name ?
>> Thanks
>|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>> Hassan
>> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
>> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>>I know it was best practise to call a stored procedure as
>> "exec dbo.sproc" instead of "exec sproc"
>> I guess it was in reference to compilations.
>> But when i monitored compilations/sec and recompilations/sec, I did not
>> see it happening.
>> Is there another reason for why I need to use the username.sproc name ?
>> Thanks
>>
>

exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
Thanks
Hassan
http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>
|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>
|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>

EXEC stored procedure for every line of SELECT result table - how?

Hello,

Is it possible to EXEC stored procedure from a query?
I want to execute stored procedure for every line of SELECT result
table.

I guess it's possible with cursors, but maybe it's possible to make it
easier.

Give an example, please.

Thank you in advance.

Hubert<hubert.trzewik@.gmail.com> wrote in message
news:1110986308.176019.292290@.l41g2000cwc.googlegr oups.com...
> Hello,
> Is it possible to EXEC stored procedure from a query?
> I want to execute stored procedure for every line of SELECT result
> table.
> I guess it's possible with cursors, but maybe it's possible to make it
> easier.
> Give an example, please.
> Thank you in advance.
> Hubert

A cursor is probably the best solution, unless you can rewrite your stored
proc to operate on a set of data. First, you need to get the results from
your stored proc into a table (or convert the proc to a function) - this is
described here:

http://www.sommarskog.se/share_data.html

After that, you can do something like this:

declare @.c1 int, @.c2 int, ...
declare cur cursor fast_forward
for select col1, col2, ...
from dbo.TableWithProcResults -- or from dbo.MyTableFunction()

open cur
fetch next from cur into @.c1, @.c2...
while @.@.fetch_status = 0
begin
exec dbo.MyProc @.c1, @.c2, ...
fetch next from cur into @.c1, @.c2...
end

close cur
deallocate cur

Simon|||Thanks. It was quite fast and easy to create such procedure.

Excluding weekends in queries

Hi
How can I exclude wenddays from a query?
I have found "SQLDMOW_WEnds" which seems to be made for that purpose?
Otherwise I guess the only way is to use something like
where day(datefield) <> 6 and day(datefield) <> 7
Any other suggestions?
regards
HenryBuild a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year SMALLINT NOT NULL,
fiscal_month SMALLINT NOT NULL,
w_in_year SMALLINT NOT NULL, -- SQL server is not ISO standard
holiday SMALLINT NOT NULL
CHECK(holiday IN (0,1)),
day_in_year SMALLINT NOT NULL,
..);
A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day wends:
http://www.smart.net/~mmontes/ushols.html|||hi henry
you can do it as
SELECT datepart(w,datefield) NOT IN (1,7)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Henry" wrote:

> Hi
> How can I exclude wenddays from a query?
> I have found "SQLDMOW_WEnds" which seems to be made for that purpose
?
> Otherwise I guess the only way is to use something like
> where day(datefield) <> 6 and day(datefield) <> 7
> Any other suggestions?
>
>
> regards
> Henry
>
>|||That should be "dw" or "wday" in the DATEPART function; also look up
@.@.DATEFIRST and SET DATEFIRST before hard-coding the IN(1,7) condition.
"Chandra" wrote:
> hi henry
> you can do it as
> SELECT datepart(w,datefield) NOT IN (1,7)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Henry" wrote:
>sql