No dependencies when inserting data into a #temp table

Hi
I have a problem. I have a database with way to many stored procedures. In
order delete some i made a script that finds stored procedures and their
dependencies. Those without any dependencies should be deleted because they
typically have been emptied for logic.
But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
uses a normal table or a table variable there is a dependency.
My example below turns out to have no dependecies as all even though the
table exists up front and the procedure has been executed succesfully.
Example: --
CREATE PROCEDURE Test3 AS
Create Table #no (
i int
)
Insert into #no (i) Select top 10 ID from table_1
Select * from #no
Drop table #no
--
This is a problem for me because a lot of my reports collect data from
several tables and insert them into temp tables. But effectively they will
show up with no dependencies and therefor be subject for deletion.
Remodelling all stored procedures to use table variables is no option.
This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error in
the product or whether there is a good reason for doing as it does.> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
One of the reasons dependency information may be missing is due to deferred
name resolution. Since the temp table does not exist when the proc is
created, no dependency info is recorded for any objects referenced by the
INSERT statement.
> Remodelling all stored procedures to use table variables is no option.
If you create the temp table before creating the proc, you should get the
dependency info.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Although one could make the argument that this is a bug, it's unclear to me
what the correct behavior should be when a statement contains a mix of
existing and non-existing objects. You might consider submitting product
enhancement feedback via Connect (http://connect.microsoft.com/SQLServer) to
suggest that known dependency information be recorded when a statement
contains a mix of existing and non-existing objects. Perhaps this will make
it into the next SQL Server version or future service pack.
Hope this helps.
Dan Guzman
SQL Server MVP
"Karsten Feddersen, Denmark" <Karsten Feddersen,
Denmark@.discussions.microsoft.com> wrote in message
news:48D61C0B-5635-4137-A3E8-C7A7CC16796F@.microsoft.com...
> Hi
> I have a problem. I have a database with way to many stored procedures. In
> order delete some i made a script that finds stored procedures and their
> dependencies. Those without any dependencies should be deleted because
> they
> typically have been emptied for logic.
> But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
> FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
> uses a normal table or a table variable there is a dependency.
> My example below turns out to have no dependecies as all even though the
> table exists up front and the procedure has been executed succesfully.
> Example: --
> CREATE PROCEDURE Test3 AS
> Create Table #no (
> i int
> )
> Insert into #no (i) Select top 10 ID from table_1
> Select * from #no
> Drop table #no
> --
> This is a problem for me because a lot of my reports collect data from
> several tables and insert them into temp tables. But effectively they will
> show up with no dependencies and therefor be subject for deletion.
> Remodelling all stored procedures to use table variables is no option.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.

Next Friday

I have a table called Order, within which there is a field called orderdate,
I can I program so that I can always retrieve order with an order date as
next Friday when I am running this script anytime this w.
Thanks,
QjleeHi
You can work this out using a calendar table see
http://www.aspfaq.com/show.asp?id=2519 or use the dataadd and datepart
functions (see books online).
John
"qjlee" wrote:

> I have a table called Order, within which there is a field called orderdat
e,
> I can I program so that I can always retrieve order with an order date as
> next Friday when I am running this script anytime this w.
> Thanks,
> Qjlee|||You can solve it by using the DATEPART() function like this:
declare @.today smalldatetime
set @.today = '20051102'
create table #tmp
(
ID int identity(1,1) primary key clustered,
OrderDate smalldatetime null
)
set nocount on
insert into #tmp (OrderDate) values ('20051101')
insert into #tmp (OrderDate) values ('20051101')
insert into #tmp (OrderDate) values ('20051102')
insert into #tmp (OrderDate) values ('20051103')
insert into #tmp (OrderDate) values ('20051103')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051107')
insert into #tmp (OrderDate) values ('20051108')
insert into #tmp (OrderDate) values ('20051108')
insert into #tmp (OrderDate) values ('20051109')
insert into #tmp (OrderDate) values ('20051110')
insert into #tmp (OrderDate) values ('20051110')
insert into #tmp (OrderDate) values ('20051111')
insert into #tmp (OrderDate) values ('20051111')
insert into #tmp (OrderDate) values ('20051111')
set nocount off
select * from #tmp
where OrderDate =
(
select min(OrderDate) from #tmp -- Earliest...
where datepart(dw,OrderDate) = 6 -- Friday...
and OrderDate >= @.today -- on or after "today"
)
drop table #tmp
The DATEPART() function, when used with the 'dw' parameter, returns the
day of the w and is dependant on how you have the SET DATEFIRST
setting configured. For me, Friday = 6. Also, if your OrderDate column
contains time info as well then you'll need to do a little range
checking to make sure the OrderDate is somewhere on that day (and not
just at midnight) - there are heaps of references on how to do that (I'm
sure I've seen an example on http://aspfaq.com).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
qjlee wrote:

>I have a table called Order, within which there is a field called orderdate
,
>I can I program so that I can always retrieve order with an order date as
>next Friday when I am running this script anytime this w.
>Thanks,
>Qjlee
>|||Try this:
SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,'2005-11-02'))
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--|||Qjlee,
This should always give you the Friday in the w following
the current one, assuming your w begins on Monday.
dateadd(d,datediff(d,'19000101',getdate(
))/7*7+11,'19000101')
Steve Kass
Drew University
qjlee wrote:

>I have a table called Order, within which there is a field called orderdate
,
>I can I program so that I can always retrieve order with an order date as
>next Friday when I am running this script anytime this w.
>Thanks,
>Qjlee
>|||Try this instead:
SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,CURRENT_TIMESTAMP))
Thanks,
M. E. Houston
"SQLChallenge" <sqlchallenge@.saikoconsulting.com> wrote in message
news:1131062936.928610.144390@.g47g2000cwa.googlegroups.com...
> Try this:
> SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,'2005-11-02'))
> --
> Mark Graveline
> Take The Challenge
> http://www.sqlchallenge.com
> --
>

NEWBIE: TOP PREDICATE HELP

Hello NG
I am trying to use a declared variable of datatype INT within a SELECT
TOP n * FROM table WHERE clause ORDER BY column Statement. I keep getting an
incorrect syntax near my variable. What am I doing wrong?
IF @.varPRFI > @.varMinimumLabels
SELECT TOP @.varMinimumLabels * FROM [RestockLabels] WHERE
[PrintedCheck] = 0 AND UPPER([PRFI])='PULL FIRST'
ELSE
<do different Things>
TIAFAH
RandyTOP does not accept a variable in SQL Server 2000.
Try using SET ROWCOUNT, e.g.
SET ROWCOUNT @.varMinimumLabels
SELECT * FROM ... ORDER BY
You should also avoid SELECT * in production code.
A
"Randy" <randywfritz@.s@.nm@.r.com> wrote in message
news:eIWWAGY2FHA.2604@.TK2MSFTNGP12.phx.gbl...
> Hello NG
> I am trying to use a declared variable of datatype INT within a SELECT
> TOP n * FROM table WHERE clause ORDER BY column Statement. I keep getting
> an
> incorrect syntax near my variable. What am I doing wrong?
> IF @.varPRFI > @.varMinimumLabels
> SELECT TOP @.varMinimumLabels * FROM [RestockLabels] WHERE
> [PrintedCheck] = 0 AND UPPER([PRFI])='PULL FIRST'
> ELSE
> <do different Things>
> TIAFAH
> Randy
>|||OK TY
Now that brings up another dilemma. Let me expand - I need to get 15
Records I am looking for Priority records first if my priority records
exceed 15 then I get only 15 if they are less then 15 I need to get enough
records to make 15 - I had intended to use a UNION Query to get my 15 by the
following
IF @.varPRFI > @.varMinimumLabels
SELECT TOP @.varMinimumLabels * FROM [RestockLabels] WHERE
[PrintedCheck] = 0 AND UPPER([PRFI])='PULL FIRST'
ELSE
SET @.varMinimumLabels = @.varMinimumLabels-@.varPRFI
SELECT * FROM [RestockLabels] WHERE [PrintedCheck] = 0 AND
UPPER([PRFI])='PULL FIRST' UNION SELECT TOP @.varMinimumLabels * FROM (SELECT
* FROM [RestockLabels] WHERE [PrintedCheck] = 0 AND UPPER([PRFI])<>'PULL
FIRST')
Will SET ROWCOUNT Still accomplish this task when I remove the top predicate
form my Union SELECT Statement and will I also be able to insure that I get
my priorities - I do not have an order by clause on the union select - I
haven't gone that far yet but when I do will I lose priorities to non
priorities because my nons will be above in the ordered by result and then
getting the rowcount of the union query. I hope I made this clear enough.
Again TIAFAH
Randy
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OTh1XHY2FHA.4004@.TK2MSFTNGP09.phx.gbl...
> TOP does not accept a variable in SQL Server 2000.
> Try using SET ROWCOUNT, e.g.
> SET ROWCOUNT @.varMinimumLabels
> SELECT * FROM ... ORDER BY
> You should also avoid SELECT * in production code.
> A
>
> "Randy" <randywfritz@.s@.nm@.r.com> wrote in message
> news:eIWWAGY2FHA.2604@.TK2MSFTNGP12.phx.gbl...
SELECT
getting
>

Newbie: printing question

Please (do not) confirm:
- To have a "print" button on report in order to print a report and not the
html page do I need to wait for sp2?
When could the sp2 be available?Yes, print functionality will be available in SP2 in the form of a
downloadable control. SP 2 is currently undergoing beta testing and it
should be out before the end of the year based on what I know. So, make a
wish to Santa if you've been a good boy :-)
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Marko Linke" <mlinke@.inet.hr> wrote in message
news:ufPVShU0EHA.4004@.tk2msftngp13.phx.gbl...
> Please (do not) confirm:
> - To have a "print" button on report in order to print a report and not
the
> html page do I need to wait for sp2?
> When could the sp2 be available?
>|||Sure it helps, thank you very much! So, then, first I'll start by helping
people cross the street and polishing my shoes for Santa's name day 6th
December in hope SP2 will fit my chimney later on. I'll also tip my boss to
do the same :-)
Best regards,
Marko
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:%23rZG7cZ0EHA.3156@.TK2MSFTNGP10.phx.gbl...
> Yes, print functionality will be available in SP2 in the form of a
> downloadable control. SP 2 is currently undergoing beta testing and it
> should be out before the end of the year based on what I know. So, make a
> wish to Santa if you've been a good boy :-)
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Marko Linke" <mlinke@.inet.hr> wrote in message
> news:ufPVShU0EHA.4004@.tk2msftngp13.phx.gbl...
>> Please (do not) confirm:
>> - To have a "print" button on report in order to print a report and not
> the
>> html page do I need to wait for sp2?
>> When could the sp2 be available?
>>
>|||You never know what works :-)
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Marko Linke" <mlinke@.inet.hr> wrote in message
news:uhiv34e0EHA.3588@.TK2MSFTNGP14.phx.gbl...
> Sure it helps, thank you very much! So, then, first I'll start by helping
> people cross the street and polishing my shoes for Santa's name day 6th
> December in hope SP2 will fit my chimney later on. I'll also tip my boss
to
> do the same :-)
> Best regards,
> Marko
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:%23rZG7cZ0EHA.3156@.TK2MSFTNGP10.phx.gbl...
> > Yes, print functionality will be available in SP2 in the form of a
> > downloadable control. SP 2 is currently undergoing beta testing and it
> > should be out before the end of the year based on what I know. So, make
a
> > wish to Santa if you've been a good boy :-)
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Marko Linke" <mlinke@.inet.hr> wrote in message
> > news:ufPVShU0EHA.4004@.tk2msftngp13.phx.gbl...
> >> Please (do not) confirm:
> >>
> >> - To have a "print" button on report in order to print a report and not
> > the
> >> html page do I need to wait for sp2?
> >>
> >> When could the sp2 be available?
> >>
> >>
> >
> >
>