behavior of command in the 'SQL Query Analyzer'

Hello,
A command in 'SQL Query Analyzer does not return expected results.
This command:
sp_depends 'lkpRate'
Returns these results:
dbo.usp_Rate_delstored procedure
dbo.usp_Rate_insstored procedure
dbo.usp_Rate_updstored procedure
But fails to return:
dbo.usp_Rate_sel
This command:
sp_depends 'usp_rate_sel'
Returns this result:
Object does not reference any object, and no objects reference it.
Here is the table 'lkpRate':
\\CREATE TABLE [lkpRate] (
[pkRateId] [smallint] IDENTITY (1, 1) NOT NULL ,
[fkRateTypeId] [smallint] NOT NULL ,
[RateDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Switch1] [tinyint] NOT NULL CONSTRAINT [DF_lkpRate_switch1] DEFAULT
(0),
[Rate] [smallmoney] NOT NULL ,
[rOrd] [tinyint] NOT NULL CONSTRAINT [DF_tblStartupAsst_saOrd] DEFAULT
(0),
[rHide] [bit] NOT NULL CONSTRAINT [DF_tblStartupAsst_saHide] DEFAULT
(0),
CONSTRAINT [PK_tblStartupAsst] PRIMARY KEY CLUSTERED
(
[pkRateId]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_lkpRate_lkpRateType] FOREIGN KEY
(
[fkRateTypeId]
) REFERENCES [lkpRateType] (
[pkRateTypeId]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO
//
Here is the stored procedure:
\\
CREATE PROCEDURE dbo.usp_Rate_sel
AS
SET NOCOUNT ON;
SELECT
pkRateId,
fkRateTypeId,
RateDescription,
Switch1,
Rate,
rOrd,
rHide
FROM dbo.lkpRate
GO
//
What do you make of it that Query Analyzer doesn't see the stored
procedure as belonging to the table?
Thank you,
dbuchanan
Dependency information is maintained correctly only when objects are
(re)created in correct dependency order. If usp_rate_sel was created before
the table or if the table was later recreated, dependency info will be
incomplete. You can fix correct the dependency information by recreating
usp_rate_sel.
Hope this helps.
Dan Guzman
SQL Server MVP
"dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
news:1141047719.180447.206490@.i40g2000cwc.googlegr oups.com...
> Hello,
> A command in 'SQL Query Analyzer does not return expected results.
> This command:
> sp_depends 'lkpRate'
> Returns these results:
> dbo.usp_Rate_del stored procedure
> dbo.usp_Rate_ins stored procedure
> dbo.usp_Rate_upd stored procedure
> But fails to return:
> dbo.usp_Rate_sel
> This command:
> sp_depends 'usp_rate_sel'
> Returns this result:
> Object does not reference any object, and no objects reference it.
> Here is the table 'lkpRate':
> \\CREATE TABLE [lkpRate] (
> [pkRateId] [smallint] IDENTITY (1, 1) NOT NULL ,
> [fkRateTypeId] [smallint] NOT NULL ,
> [RateDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Switch1] [tinyint] NOT NULL CONSTRAINT [DF_lkpRate_switch1] DEFAULT
> (0),
> [Rate] [smallmoney] NOT NULL ,
> [rOrd] [tinyint] NOT NULL CONSTRAINT [DF_tblStartupAsst_saOrd] DEFAULT
> (0),
> [rHide] [bit] NOT NULL CONSTRAINT [DF_tblStartupAsst_saHide] DEFAULT
> (0),
> CONSTRAINT [PK_tblStartupAsst] PRIMARY KEY CLUSTERED
> (
> [pkRateId]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_lkpRate_lkpRateType] FOREIGN KEY
> (
> [fkRateTypeId]
> ) REFERENCES [lkpRateType] (
> [pkRateTypeId]
> ) NOT FOR REPLICATION
> ) ON [PRIMARY]
> GO
> //
> Here is the stored procedure:
> \\
> CREATE PROCEDURE dbo.usp_Rate_sel
> AS
> SET NOCOUNT ON;
> SELECT
> pkRateId,
> fkRateTypeId,
> RateDescription,
> Switch1,
> Rate,
> rOrd,
> rHide
> FROM dbo.lkpRate
> GO
> //
> What do you make of it that Query Analyzer doesn't see the stored
> procedure as belonging to the table?
> Thank you,
> dbuchanan
>
|||Dan
Thank you.
Is there any way, maybe some command that I can use, to identify those
objects that are not up to date?
'sp_depends' seems kind of worthless if information must be accounted
for 'manually' in order for the commands to work.
dbuchanan
|||dbuchanan (dbuchanan52@.hotmail.com) writes:
> Is there any way, maybe some command that I can use, to identify those
> objects that are not up to date?
Not really. You could run a SELECT on sysobjects to identify procedures
that have been created before tables, but that will probably give you
too much information.

> 'sp_depends' seems kind of worthless if information must be accounted
> for 'manually' in order for the commands to work.
Yes, it is a feature or limited use. I use it quite a bit myself though,
but what I do is that I build an empty database with our build tools, so
that I know that dependencies from tables to procedures are correct.
(Dependencies from procedures are not.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

behavior of command in the 'SQL Query Analyzer'

Hello,
A command in 'SQL Query Analyzer does not return expected results.
This command:
sp_depends 'lkpRate'
Returns these results:
dbo.usp_Rate_del stored procedure
dbo.usp_Rate_ins stored procedure
dbo.usp_Rate_upd stored procedure
But fails to return:
dbo.usp_Rate_sel
This command:
sp_depends 'usp_rate_sel'
Returns this result:
Object does not reference any object, and no objects reference it.
Here is the table 'lkpRate':
\\CREATE TABLE [lkpRate] (
[pkRateId] [smallint] IDENTITY (1, 1) NOT NULL ,
[fkRateTypeId] [smallint] NOT NULL ,
[RateDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Switch1] [tinyint] NOT NULL CONSTRAINT [DF_lkpRate_switch1] DEFAULT
(0),
[Rate] [smallmoney] NOT NULL ,
[rOrd] [tinyint] NOT NULL CONSTRAINT [DF_tblStartupAsst_saOrd] DEFAULT
(0),
[rHide] [bit] NOT NULL CONSTRAINT [DF_tblStartupAsst_saHide] DEFAULT
(0),
CONSTRAINT [PK_tblStartupAsst] PRIMARY KEY CLUSTERED
(
[pkRateId]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_lkpRate_lkpRateType] FOREIGN KEY
(
[fkRateTypeId]
) REFERENCES [lkpRateType] (
[pkRateTypeId]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO
//
Here is the stored procedure:
\\
CREATE PROCEDURE dbo.usp_Rate_sel
AS
SET NOCOUNT ON;
SELECT
pkRateId,
fkRateTypeId,
RateDescription,
Switch1,
Rate,
rOrd,
rHide
FROM dbo.lkpRate
GO
//
What do you make of it that Query Analyzer doesn't see the stored
procedure as belonging to the table?
Thank you,
dbuchananDependency information is maintained correctly only when objects are
(re)created in correct dependency order. If usp_rate_sel was created before
the table or if the table was later recreated, dependency info will be
incomplete. You can fix correct the dependency information by recreating
usp_rate_sel.
Hope this helps.
Dan Guzman
SQL Server MVP
"dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
news:1141047719.180447.206490@.i40g2000cwc.googlegroups.com...
> Hello,
> A command in 'SQL Query Analyzer does not return expected results.
> This command:
> sp_depends 'lkpRate'
> Returns these results:
> dbo.usp_Rate_del stored procedure
> dbo.usp_Rate_ins stored procedure
> dbo.usp_Rate_upd stored procedure
> But fails to return:
> dbo.usp_Rate_sel
> This command:
> sp_depends 'usp_rate_sel'
> Returns this result:
> Object does not reference any object, and no objects reference it.
> Here is the table 'lkpRate':
> \\CREATE TABLE [lkpRate] (
> [pkRateId] [smallint] IDENTITY (1, 1) NOT NULL ,
> [fkRateTypeId] [smallint] NOT NULL ,
> [RateDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Switch1] [tinyint] NOT NULL CONSTRAINT [DF_lkpRate_switch1] DEFAULT
> (0),
> [Rate] [smallmoney] NOT NULL ,
> [rOrd] [tinyint] NOT NULL CONSTRAINT [DF_tblStartupAsst_saOrd] DEFAULT
> (0),
> [rHide] [bit] NOT NULL CONSTRAINT [DF_tblStartupAsst_saHide] DEFAULT
> (0),
> CONSTRAINT [PK_tblStartupAsst] PRIMARY KEY CLUSTERED
> (
> [pkRateId]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_lkpRate_lkpRateType] FOREIGN KEY
> (
> [fkRateTypeId]
> ) REFERENCES [lkpRateType] (
> [pkRateTypeId]
> ) NOT FOR REPLICATION
> ) ON [PRIMARY]
> GO
> //
> Here is the stored procedure:
> \\
> CREATE PROCEDURE dbo.usp_Rate_sel
> AS
> SET NOCOUNT ON;
> SELECT
> pkRateId,
> fkRateTypeId,
> RateDescription,
> Switch1,
> Rate,
> rOrd,
> rHide
> FROM dbo.lkpRate
> GO
> //
> What do you make of it that Query Analyzer doesn't see the stored
> procedure as belonging to the table?
> Thank you,
> dbuchanan
>|||Dan
Thank you.
Is there any way, maybe some command that I can use, to identify those
objects that are not up to date?
'sp_depends' seems kind of worthless if information must be accounted
for 'manually' in order for the commands to work.
dbuchanan|||dbuchanan (dbuchanan52@.hotmail.com) writes:
> Is there any way, maybe some command that I can use, to identify those
> objects that are not up to date?
Not really. You could run a SELECT on sysobjects to identify procedures
that have been created before tables, but that will probably give you
too much information.

> 'sp_depends' seems kind of worthless if information must be accounted
> for 'manually' in order for the commands to work.
Yes, it is a feature or limited use. I use it quite a bit myself though,
but what I do is that I build an empty database with our build tools, so
that I know that dependencies from tables to procedures are correct.
(Dependencies from procedures are not.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Start by generating a script file in Enterprise Manager. Include all
stored procedures and views. Script as CREATE, but do NOT include the
DELETE!
Now edit the script, and change all the CREATE PROC and CREATE VIEW to
ALTER commands.
Then run the script. Run it a few times, if you want. I believe the
dependencies should be up to date.
(Personally I don't pay any attention to those dependencies, but that
is partly because I formed my habits before they introduced ALTER!)
Roy
On 27 Feb 2006 07:22:50 -0800, "dbuchanan" <dbuchanan52@.hotmail.com>
wrote:

>Dan
>Thank you.
>Is there any way, maybe some command that I can use, to identify those
>objects that are not up to date?
>'sp_depends' seems kind of worthless if information must be accounted
>for 'manually' in order for the commands to work.
>dbuchanan

Beginners SqlDataSource SelectCommand Question

Hi folks,

I'm having problems using the SqlDataSource to return certain values from a SQL database. I have two DropDownLists. When the first DDL's selected index is changed, I need to take the value (an int id) from the field, use the value to look up a string in a different colum in the table for that record id, which should then be used to automatically select a value from the second DDL. I think my problem is I'm not sure how to get the SelectCommand to actually return the value, let alone in string form!

Here is my code:

{
string selectedProject = ProjectDDL.SelectedValue;
SqlDataSource temp = new SqlDataSource();
temp.ConnectionString = rootWebConfig.ConnectionStrings.ConnectionStrings["DBConnectionString"].ToString();

temp.SelectParameters.Add("id", selectedProject);
temp.SelectCommand = "SELECT [username] FROM dbo.projects WHERE id = @.id";

//temp.Select(); ?

//AssigneeField.SelectedValue = string returned from Select Statement!
}

I'd appreciate it if somone could point me in the right direction?

Thanks,

Ally

Hi,

you can either use a command object with a dataadaptor or a datareader to retrieve data from your command
assuming you are only returning one String value, using a datareader would be better on your system:

String result = "";

SqlCommand command = New SqlCommand( _
"SELECT CategoryID, CategoryName FROM dbo.Categories;" & _
"SELECT EmployeeID, LastName FROM dbo.Employees", connection);
command.parameters.add(...);
connection.Open();

sqldatareader reader = command.executereader();
Do While reader.Read()
<<<<<Do your processing with the data here>>>>>
result = reader(<columnNum>);
Loop

reader.Close();
return result

i'm sorry about the syntax errors as im not c# trained...
Hope this helps...

|||

Excellent, exactly what I needed, thanks! I was having problems with SqlDataReader.ExecuteReader() constantly returning anException:

System.InvalidOperationException: Invalid attempt to read when no data is present."

I'd accidently omitted the inital SqlDataReader.Read() call, fought with it for ages until I realised the reader doesn't move on to the first record until SqlDataRead.Read() is called for the first time, makes sense I guess! All working now :)

Thanks again,

Ally

Beginner T-SQL Question

I'd like to query my Tasks and TaskActivity Tables to return the following results:

Case Id
Estimated Hours
Total Hours
Remaining Hours

Because I'm an SQL novice, I've had to resort to a hack of creating three different views to get the results I want: I'm sure there's a better way (i.e. a single, well-formed query) but when I try that my numbers are all wrong.

Here's my hack to get the correct results:

First I have a view to get my Estimated Hours and CaseId from my TASKS table:
SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId
FROM dbo.Tasks
GROUP BY CaseId

Then I have a view to get my Total Hours and CaseId from my TASKACTIVITES table:
SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId

Finally I have a third view (that I actually use in my program) to put it all together:
SELECT vTaskActual.CaseId, vTaskEstimates.EstHrs, vTaskActual.TotalHrs,
vTaskActual.TotalHrs - vTaskEstimates.EstHrs AS RemHrs
FROM vTaskActual INNER JOIN
vTaskEstimates ON vTaskActual.CaseId = vTaskEstimates.CaseId

Any help would be greatly appreciated, and, if this is not the correct forum for newbie questions like this, please let me know.

TIA,
Rob

You can turn a query containing a single select statement into a derived table by surround it in parethesises and adding an alias. eg

select managers.name, managers.SSN, dept.departmentCode, dept.description

from department as dept

join (select name, SSN, deptid from employee where salary > 50.000) as managers

on dept.deptid = managers.deptid

So with the two views above, turn both into derived tables, and join:

select TaskActual.CaseId, TaskEstimates.EstHrs, TaskActual.TotalHrs,
TaskActual.TotalHrs - TaskEstimates.EstHrs AS RemHrs

from

(SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId
) as TaskEstimates

INNER JOIN

(SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId) as TaskActual

ON TaskActual.CaseId = TaskEstimates.CaseId

That should do it. As both derived table queries use the tasks table, it is likely that you can do the query in one query without using derived tables, but without having a better understanding of your schema and data I don't want to attempt that!

HTH

For more SQl tips, check out my blog:

|||Please indicate if this answered your question or not.|||

Thanks for the reply it was very helpful.

I did have to make a few changes to your response (see below) because my results are gathered from two different tables: Tasks contains the estimate, and TaskActivity contains the records of actual time. The info on derived tables will really help clean-up my DB.

Thanks again!

P.S. I use "COALESCE" to assign 0.00 to any NULL values in EstHrs. It is working so I assume that is correct)

Here's the final query that's working:

select TaskActual.CaseId, TaskEstimates.EstHrs, TaskActual.TotalHrs,

TaskActual.TotalHrs - TaskEstimates.EstHrs AS RemHrs

from

(SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId

FROM dbo.Tasks

GROUP BY CaseId

) as TaskEstimates

INNER JOIN

(SELECT dbo.Tasks.CaseId, SUM(dbo.TaskActivity.Minutes / 60.00) AS TotalHrs

FROM dbo.TaskActivity RIGHT OUTER JOIN

dbo.Tasks ON dbo.TaskActivity.TaskId = dbo.Tasks.TaskId

GROUP BY dbo.Tasks.CaseId) as TaskActual

ON TaskActual.CaseId = TaskEstimates.CaseId

Beginner Question: How to return list of right-most entries in a cross tab tabl

I currently have a report that contains a crosstab. Each row is a different property and each column is a different time. Several of the entries in the table are blank i.e. not all properties are recorded at all the times.

I would like to be able to turn this report into a list of the most recent values for each property along with the time it was recorded. I would be very grateful if someone could provide an example of a formula that would approximate this. Please let me know if I have not provided enough information.

Many thanks

JonI think you must ckick the time field in crosstab expert then select ascending.

Beginner question Searching table

I have a stored procedure that takes 5 parameters. Based on what parameters are passed i want to return a result set to the calling code. Not all of the parameters are required so there will be some combinations on the entered parameters.

Do i need to dynamic build the query with the IF statements to check if the input parameter is NULL or not ?

Thanks

You can use the isnull function like so:

-- Using the northwind database in sql server 2k

declare @.categoryname varchar(100)
declare @.categoryID varchar(100)

--set @.categoryname = 'Produce'
set @.CategoryID = 4

select * from categories
where isnull(@.categoryname, categoryname) = categoryname
and isnull(@.categoryID, categoryID) = categoryID

|||For additional reading on this subject, check this article for a full description of was to do this: http://www.sommarskog.se/dyn-search.html|||

Hi,

Instead of running the below one

select * from categories
where isnull(@.categoryname, categoryname) = categoryname
and isnull(@.categoryID, categoryID) = categoryID

Running this one may be faster

select * from categories
where (@.categoryname is null or categoryname = @.categoryname)
and (@.categoryID is null or categoryID = @.categoryID)

Since during the execution plan creation the engine may decide a better way since it knows that "@.categoryname is null" is true for every record. But a similar approach is also valid for the first one too. Actually this is related with how wise the sql server engine is.

Eralper

http://www.kodyaz.com

|||

Thanks all for helping. It works great now and it runs fast for me. Thanks so much for you help.

Guess i need to start reading up on my T SQL.

|||

The query optimizer doesn't quite optimize the conditions using the multiple variables like you described. The problem is that the value of only @.categoryname or @.categoryid will be fixed at compile time depending on the first execution and the plan primed into the cache will not be that efficient for the other invocations. Erland's article that Louis posted goes through lot of techniques and their various pros/cons. The recompilation whitepaper below is also a good read on how plan caching works:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx