putting dbo explicitly in select staetement

I have witten a lot of stored procedures in my project where I did not put dbo before the user tables.My colleague told me that I have to put dbo for all statements other there could be a problem.

Any thought?,

Please assist.

Arkhan:

There are at least a couple of places in which the owner name prefix -- dbo -- is required including (1) naming of a scalar functions and (2) objects used with schemabinding. In addition, if your "shop standards" are to always designate object names with the owner name then you need to do so.

I can think of at least one situation in which I prefer that objects NOT be owned by dbo. This is at least somewhat controversial so take it with a grain of salt. In DTS "staging" tables I like to have a designated table owner so that that owner has the right to truncate the table without needing the database owner privilege. This does not figure to be relevant to your problem. It is good practice to include the owner name as part of your qualified name. One thing that worries about your question has to do with the practice of deployment of privileges.

I do not like it when I see scores of tables or other database objects that are owned by many different database users. When I see this type of stuff my knee-jerk reaction is that privileges are beging deployed to liberally. And I would guess that if you and your colleagues are seeing many problems from NOT including the owner name that you likely have this privilege problem.

Short answer: Include the dbo portion of the name.

Dave

|||

I've heard it said that not including the schema owner (usually DBO) on your object prefix can result in a "Compile lock" against your stored procedure while the client determines whether there is an object in existence for its own schema. In this situation, multiple users executing the same stored procedure would suffer from a queuing effect as each would place a compile lock on the procedure (or statement).

That being said, I've never been able to repro this is a testing environment and I have yet to see any white papers or KB articles that discuss this so I'd love it if somebody could chime in on this.

|||

Specifying the owner can help the system find the stored procedure faster. It also prevents issues if someone creates the same name procedure with a different owner (or schema in 2005) by mistake. At least if everyone uses two-part names, they will be less likely to make mistakes.

As for the compile locks, I have reproduced recompile locks in my stored procedures. I'm not 100% how I did it, but it seemed to be with temporary tables stored procedures. I added dbo in the front of each stored procedure and table name within the stored procedure and it elliminated most of the problems we had.

sql

Putting @Parameters in Stored Procedures

I am creating a stored Procedure and I am getting an error which relates toDATENAME.

SELECTCOUNT(*)AScalls,DATENAME(@.varDate, CALLSTARTTIME)AS 'Total Calls'

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS='1')AND(CALLSTARTTIME >= @.StartDate)AND(CALLENDTIME <=@.EndDate

sql doesn't like: DATENAME( @.varDate, CallStartTime)
sql works fine if I change @.varDate into 'yy', 'mm', 'dd', or 'wk'
Since I do not want to make 5 unique Stored Proc just because of @.varDate....

Is there any way to work around this problem?

Well, you could do something like this:

DECLARE @.varDate VARCHAR(10)
SELECT @.varDate = 'mm'
SELECT
OrderDate,
CASE @.varDate
WHEN 'mm' THEN DATENAME(mm,OrderDate)
WHEN 'yy' THEN DATENAME(yy,OrderDate)
WHEN 'dw' THEN DATENAME(dw,OrderDate)
END -- added this as an EDIT to my post
FROM Orders

|||I tried,

ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.VarDate as char,
@.StartDate as char,
@.EndDate as char
)
AS
SELECT
COUNT(*) AS 'Calls',
CASE @.VarDate
When 'yy' Then DATEPART(yy, CALLSTARTTIME) AS 'Year'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME IS NOT NULL) AND (CALLENDTIME IS NOT NULL)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)
SQL wouldn't allow me to save. What did I do wrong?
|||Somehow when I coped and pasted my test code I left out an END, sorry about that!
Try it like this:

SELECT
COUNT(*) AS 'Calls',
CASE @.VarDate
When 'yy' Then DATEPART(yy, CALLSTARTTIME)ENDAS 'Year'

FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME IS NOT NULL) AND (CALLENDTIME IS NOT NULL)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)

Also, in the future, you need to be more specific than "SQL wouldn'tallow me to save". Exact error messages go a long way to gettingexact help.
|||thanks for the advice.
got another error:
ADO error: Column 'Call_LOG_MASTER.CALLSTARTTIME' is invalid in theselect list because it is not contained in either an aggregate functionor the GROUP BY clause.

SELECT
CASE @.varDate
when 'yy' Then DATEPART(yy, CALLSTARTTIME) END as 'Year',
COUNT(DATEPART(yy, CALLSTARTTIME)) AS 'Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)|||

I think I found the problem,

ALTER PROCEDUREdbo.GET_TOTALCALLS

(

@.varDateas varchar(255),

@.StartDateas datetime,

@.EndDateas datetime

)

AS

SELECT

CASE@.varDate

When'Year'Then DATEPART(yy, CALLSTARTTIME)

When'Month'Then DATEPART(mm, CALLSTARTTIME)

When'Week'Then DATEPART(wk, CALLSTARTTIME)

When'Day'Then DATEPART(dd, CALLSTARTTIME)

when'Quarter'Then Datepart(qq, CALLSTARTTIME)

END,

Count(*)as'Total Calls'

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(CALLSTARTTIME >= @.StartDate)AND(CALLENDTIME <= @.EndDate)

GROUP BY

CASE@.varDate

When'Year'Then DATEPART(yy, CALLSTARTTIME)

When'Month'Then DATEPART(mm, CALLSTARTTIME)

When'Week'Then DATEPART(wk, CALLSTARTTIME)

When'Day'Then DATEPART(dd, CALLSTARTTIME)

when'Quarter'Then Datepart(qq, CALLSTARTTIME)

End

ORDER BY

CASE@.varDate

When'Year'Then DATEPART(yy, CALLSTARTTIME)

When'Month'Then DATEPART(mm, CALLSTARTTIME)

When'Week'Then DATEPART(wk, CALLSTARTTIME)

When'Day'Then DATEPART(dd, CALLSTARTTIME)

when'Quarter'Then Datepart(qq, CALLSTARTTIME)

End

RETURN

However, the column can't be named this way. My new question is how do I renamed the datepart column by case?
Namely,
when @.varDate = 'Year', the column name should be 'Year',
when @.varDate = 'Month', the column name should be as 'Month', etc..

|||still stuck on this problem, could use some help if possible =)|||If that is your requirement, then I would scrap what you have and just do something like this:
ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS
IF @.varDate = 'Year'
BEGIN
SELECT
DATEPART(yy, CALLSTARTTIME) AS Year,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY 1
END
IF @.varDate = 'Month'
BEGIN
SELECT
DATEPART(mm, CALLSTARTTIME) AS Month,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(mm, CALLSTARTTIME)
ORDER BY 1
END
-- etc
RETURN

|||yes, thank you. That will work fine

put results from sp to another sp

Hi,
Can anyone tell me how to use the result of a stored procedure to be used in
another stored procedure?
The first sp is a simple select statement with 2 columns, the second one
needs those two columns to insert in a temptable.
ThnxCreate a temporary table in the second sp and use INSERT ... EXEC
Example:
use northwind
go
create procedure proc1
@.d datetime
as
set nocount on
select orderid, orderdate
from dbo.orders
where orderdate >= convert(char(8), @.d, 112) and orderdate < dateadd(day, 1,
convert(char(8), @.d, 112))
return @.@.error
go
create procedure proc2
@.d datetime
as
set nocount on
create table #t (
orderid int,
orderdate datetime
)
insert into #t
exec proc1 @.d
select * from #t
drop table #t
go
exec proc2 '19980226'
go
drop procedure proc1, proc2
go
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
AMB
"Jason" wrote:

> Hi,
> Can anyone tell me how to use the result of a stored procedure to be used
in
> another stored procedure?
> The first sp is a simple select statement with 2 columns, the second one
> needs those two columns to insert in a temptable.
> Thnx
>
>|||OUTPUT parameters ?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:eoGJzrIMFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can anyone tell me how to use the result of a stored procedure to be used
> in
> another stored procedure?
> The first sp is a simple select statement with 2 columns, the second one
> needs those two columns to insert in a temptable.
> Thnx
>

Purging Data Cache

How do I go abouts purging the data cache for a particular database? Is there a stored procedure that can be executed??Try the :

DBCC DROPCLEANBUFFERS -- for clear out the buffer cache
DBCC FREEPROCCACHE -- to clear out the procedure cache.

Worse, just shutdown & restart the server - I have to post a waiver for this :-)

SVTsql

Pulling stored Procs from a remote db

Hi all -
I am trying to rebuild a website abandoned by the builder and an SQLServer
(I think 2k) db is used under it at the host. I can get at the db by using
Access/ODBC with a URL but the migration facility seems only to let me get
at the tables.
There are about half a dozen SPs referred to in the ASP code ( eg EXEC
sp_www) and I want to get hold of the code as I am not sure at all what they
are doing.
How do I get the code for each SP and store it in the local copy of the db?
Can I just replicate the db and move the replica to a local area?
Any advice on this gratefully (!) receive
Thanks
Jim McDonaldYou can get the source code of a stored procedure using sp_helptext.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jim McDonald" <jim@.missile.demon.co.uk> wrote in message
news:ct7q5g$n7j$1$8302bc10@.news.demon.co.uk...
> Hi all -
> I am trying to rebuild a website abandoned by the builder and an SQLServer
(I think 2k) db is used
> under it at the host. I can get at the db by using Access/ODBC with a URL
but the migration
> facility seems only to let me get at the tables.
> There are about half a dozen SPs referred to in the ASP code ( eg EXEC sp_
www) and I want to get
> hold of the code as I am not sure at all what they are doing.
> How do I get the code for each SP and store it in the local copy of the d
b? Can I just replicate
> the db and move the replica to a local area?
> Any advice on this gratefully (!) receive
> Thanks
> Jim McDonald
>

Pulling stored Procs from a remote db

Hi all -
I am trying to rebuild a website abandoned by the builder and an SQLServer
(I think 2k) db is used under it at the host. I can get at the db by using
Access/ODBC with a URL but the migration facility seems only to let me get
at the tables.
There are about half a dozen SPs referred to in the ASP code ( eg EXEC
sp_www) and I want to get hold of the code as I am not sure at all what they
are doing.
How do I get the code for each SP and store it in the local copy of the db?
Can I just replicate the db and move the replica to a local area?
Any advice on this gratefully (!) receive
Thanks
Jim McDonald
You can get the source code of a stored procedure using sp_helptext.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jim McDonald" <jim@.missile.demon.co.uk> wrote in message
news:ct7q5g$n7j$1$8302bc10@.news.demon.co.uk...
> Hi all -
> I am trying to rebuild a website abandoned by the builder and an SQLServer (I think 2k) db is used
> under it at the host. I can get at the db by using Access/ODBC with a URL but the migration
> facility seems only to let me get at the tables.
> There are about half a dozen SPs referred to in the ASP code ( eg EXEC sp_www) and I want to get
> hold of the code as I am not sure at all what they are doing.
> How do I get the code for each SP and store it in the local copy of the db? Can I just replicate
> the db and move the replica to a local area?
> Any advice on this gratefully (!) receive
> Thanks
> Jim McDonald
>

Pulling stored Procs from a remote db

Hi all -
I am trying to rebuild a website abandoned by the builder and an SQLServer
(I think 2k) db is used under it at the host. I can get at the db by using
Access/ODBC with a URL but the migration facility seems only to let me get
at the tables.
There are about half a dozen SPs referred to in the ASP code ( eg EXEC
sp_www) and I want to get hold of the code as I am not sure at all what they
are doing.
How do I get the code for each SP and store it in the local copy of the db?
Can I just replicate the db and move the replica to a local area?
Any advice on this gratefully (!) receive
Thanks
Jim McDonaldYou can get the source code of a stored procedure using sp_helptext.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jim McDonald" <jim@.missile.demon.co.uk> wrote in message
news:ct7q5g$n7j$1$8302bc10@.news.demon.co.uk...
> Hi all -
> I am trying to rebuild a website abandoned by the builder and an SQLServer
(I think 2k) db is used
> under it at the host. I can get at the db by using Access/ODBC with a URL
but the migration
> facility seems only to let me get at the tables.
> There are about half a dozen SPs referred to in the ASP code ( eg EXEC sp_
www) and I want to get
> hold of the code as I am not sure at all what they are doing.
> How do I get the code for each SP and store it in the local copy of the d
b? Can I just replicate
> the db and move the replica to a local area?
> Any advice on this gratefully (!) receive
> Thanks
> Jim McDonald
>

Pulling stored Procs from a remote db

Hi all -
I am trying to rebuild a website abandoned by the builder and an SQLServer
(I think 2k) db is used under it at the host. I can get at the db by using
Access/ODBC with a URL but the migration facility seems only to let me get
at the tables.
There are about half a dozen SPs referred to in the ASP code ( eg EXEC
sp_www) and I want to get hold of the code as I am not sure at all what they
are doing.
How do I get the code for each SP and store it in the local copy of the db?
Can I just replicate the db and move the replica to a local area?
Any advice on this gratefully (!) receive
Thanks
Jim McDonald
You can get the source code of a stored procedure using sp_helptext.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jim McDonald" <jim@.missile.demon.co.uk> wrote in message
news:ct7q5g$n7j$1$8302bc10@.news.demon.co.uk...
> Hi all -
> I am trying to rebuild a website abandoned by the builder and an SQLServer (I think 2k) db is used
> under it at the host. I can get at the db by using Access/ODBC with a URL but the migration
> facility seems only to let me get at the tables.
> There are about half a dozen SPs referred to in the ASP code ( eg EXEC sp_www) and I want to get
> hold of the code as I am not sure at all what they are doing.
> How do I get the code for each SP and store it in the local copy of the db? Can I just replicate
> the db and move the replica to a local area?
> Any advice on this gratefully (!) receive
> Thanks
> Jim McDonald
>

pulling my hair out over udf and dynamic SQL

Hi, I hope someone can help me.
I have a stored procedure that creates some dynamic SQL which is used
to populate a payroll system with timesheets information. As part of
the SQL that is created a udf is called to calculate the employees base
pay for the month.
the issue is that if I execute the query string using the exec command
the udf does not fire, however if I copy the querystring into QA and
run it, the results are fine, any pointers here would be great.
Thanks
SimonADO? If so, try SET NOCOUNT ON. If not, can you repro the "not firing" scena
rio in QA?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<simon.wilkinson@.cohenschemist.co.uk> wrote in message
news:1127406711.829918.196100@.g49g2000cwa.googlegroups.com...
> Hi, I hope someone can help me.
> I have a stored procedure that creates some dynamic SQL which is used
> to populate a payroll system with timesheets information. As part of
> the SQL that is created a udf is called to calculate the employees base
> pay for the month.
> the issue is that if I execute the query string using the exec command
> the udf does not fire, however if I copy the querystring into QA and
> run it, the results are fine, any pointers here would be great.
> Thanks
> Simon
>|||post your SQL statements
<simon.wilkinson@.cohenschemist.co.uk> wrote in message
news:1127406711.829918.196100@.g49g2000cwa.googlegroups.com...
> Hi, I hope someone can help me.
> I have a stored procedure that creates some dynamic SQL which is used
> to populate a payroll system with timesheets information. As part of
> the SQL that is created a udf is called to calculate the employees base
> pay for the month.
> the issue is that if I execute the query string using the exec command
> the udf does not fire, however if I copy the querystring into QA and
> run it, the results are fine, any pointers here would be great.
> Thanks
> Simon
>|||Can you post a script to reproduce the problem? Please include DDL.
If you find yourself needing dynamic SQL for basic data manipulation tasks
then that's often an indicator of a poor underlying design. Have you tried
to solve the problem without the dynamic code?
David Portas
SQL Server MVP
--|||Why are you using dynamic SQL? Are the rules for the payroll
constantly changing from month to month, moment to moment? Why do
users know more at run time about how to do a payroll than you know at
design time?
This kind of programming is usually a sign of a lack of a proper data
model and the use of a UDF is usually a sign of proceudral coding in
SQL instead of relational code.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Hi all,
I managed to sort it, Looks like the udf was not needed after all in
this instance, but as to why that particular one would not fire when
called through a generated sql string is a mystery to me, especially
when others worked fine.
Tibor - thanks for the tip but I was still testing within QA, but will
bare the NO COUNT in mind should I come across the problem when using
ADO.
David - Thanks for the offer however I did not fancy posting a
storedprocedure containing over 500 lines of code that is not fully
commented as I am still devleloping it. This is not really what I
would term basic data manipulation, due to the rules of our pay
periods, and other factors. I did try to solve the issue without using
generated/dynamic SQL but kept hitting a brick wall, thanks again for
the offer.
Celko - I am using dynamic/generated SQL as trying to build a flat out
view for this task was proving a real headache. Our payroll rules do
not change from month to month, moment to moment, however working
things out like how our pay periods are structured does change, whether
this be a 5 w pay month or a four w pay month, plus the fact that
the actual period dates differ, and we are not simply paid from the
first to the 31st of each month that would be too easy. The users do
not know more at run time than I know as design time, all they know is
the last day of the pay month. From this the rules are applied and the
script generated to produce the output.
Correct me if I am wrong but aren't udfs ideal for things like business
rules and such like, for example, I am using udf's to manage, our
sickness rules, holiday rules and payroll rules. I would not really
fancy having to re-write views and stored procedures because our
sickness rules have changed slightly due to us buying another company
and honouring their sickness rules, I would be much happier just
changing a udf.
I see this a lot on these groups, saying udf's are bad and
dynamic/generated SQL is a sign of defeat, something I do not agree
with.
Anyway thats my rant over with, thanks for the offer of assistance, but
all I was really looking for was, maybe someone who had come across
this strange issue before.|||Nothing in what you've said indicates to me that dynamic SQL is an
obvious choice. The calendar, payment dates and stuff can go in a
table, where it can be easily modified without code changes.
I wouldn't say dynamic code is universally bad but it isn't best
practice for a production application. All too often, dynamic SQL is
just used as a kludge around poor data design. That may or may not be
the case here but I've worked with a few payroll systems and I haven't
yet seen one that couldn't represent all the configuration information
as data elements rather than dynamic code. In fact AFAIK that's pretty
standard practice with packaged payroll systems (tax and regulatory
stuff is usually implemented as separate modules that can be patched
for localization support), which in principle have to cope with a much
more complex and varied set of circumstances across a variety of
different business.
David Portas
SQL Server MVP
--|||David,
Fair comments I feel,
For some reason I had not actually thought of putting the pay dates and
periods into a table, not sure why as this is now obvious, maybe I
should get my school bag and go home! Actually doing that will get rid
of most of the dynamic/generated stuff I have in this instance as most
of it is working that out, lucky I am not actually writing a payroll
system but creating an add on.
Thanks.
Simon|||>> Our payroll rules do not change from month to month, moment to moment, ho
wever working things out like how our pay periods are structured does change
, whether this be a 5 w pay month or a four w pay month, plus the fact
that the actual period dat
es differ, and we are not simply paid from the first to the 31st of each mon
th that would be too easy. <<
You are still thinking with procedures and not relations. Look up the
use of an auxiliary Calendar table in one of my books. Do not try to
use dynamic SQL to construct this data every time you do a payroll.|||David and Celko,
Thanks for the tips, the using a table for dates/Auxiliary table worked
a treat.
Thanks
Simon.

Pulling information from an oracle database into my SQL Server database

I have been tasked with pulling information from a clients Oracle database in a stored procedure to pass out information. This needs to be a simple stored procedure but i am not sure as to how to point it to pull from a different server and use the ODBC to connect and pull this information. Any suggestions??:confused:Have you looked at using sp_addlinkedserver|||I took a look at this but was told by my supervisor we are not creating a linked server he just wants to use the ODBC to go from SQL to Oracle, and get the info need. So i am basically looking for a script on how to access the ODBC to perform this.|||Then look at OPENROWSET. Since you are saying that you want to access Oracle from MS-SQL, I'm assuming that this means T-SQL and not a program (VB,C++,Perl).|||I am actually wanting to query the Oracle database directly from a stored procedure. Yes it is T-Sql.|||If you don't (or can't) use Linked Servers then look at OPENROWSET. All you need is the ODBC driver for Oracle.

From BOL
Use OPENROWSET with an object and the OLE DB Provider for ODBC
This example uses the OLE DB Provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized with a provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog.schema.object syntax is used to define the row set returned.

USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
GO