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

BDNull error...not expected!

I have a connection (SqlConnection1) established through the GUI. Here's the
code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
'Create and initialise the command object
Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
'State that the type of this command is a stored procedure
cmd.CommandType = CommandType.StoredProcedure
'Create the input paramater
cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
cmd.Parameters("@.BizName").Value = TextBox1.Text
'Create the output paramater
cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
'Enusre the connection is open
If (cmd.Connection.State <> ConnectionState.Open) Then
cmd.Connection.Open()
End If
'Execute the command object
cmd.ExecuteNonQuery()
'Assign the returned value of the output paramater
TextBox2.Text = cmd.Parameters("@.BizAddress").Value
'Close the connection
cmd.Connection.Close()
End Sub
This code allows a textbox (textbox1) to fill an input paramater and
displays the contents of the returned value from the output paramater in a
textbox (textbox2). It works fine with s imple select statement. But with a
stored procedure i get this error:
Cast from type 'DBNull' to type 'String' is not valid.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information abou
t
the error and where it originated in the code.
Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
type 'String' is not valid.
Source Error:
Line 63:
Line 64: 'Assign the returned value of the output paramater
Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
The code for the stored procedure:
CREATE PROCEDURE GetAddress
@.BizName varchar,
@.BizAddress varchar output
AS
SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
GO
Where did i go wrong?
Thanks for any insights.
NabYour Stored Procedure is returning a NULL value in the @.BizAddress output
parameter. You need to assign the value to an Object and check it for
DBNull.value before converting to string:
Dim o As Object
o = cmd.Parameters("@.BizAddress").Value
If (o Is Nothing OrElse o Is DBNull.Value) Then
TextBox2.Text = ""
Else
TextBox2.Text = Convert.ToString(o)
EndIf
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>I have a connection (SqlConnection1) established through the GUI. Here's
>the
> code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
> 'Enusre the connection is open
> If (cmd.Connection.State <> ConnectionState.Open) Then
> cmd.Connection.Open()
> End If
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> 'Close the connection
> cmd.Connection.Close()
> End Sub
> This code allows a textbox (textbox1) to fill an input paramater and
> displays the contents of the returned value from the output paramater in a
> textbox (textbox2). It works fine with s imple select statement. But with
> a
> stored procedure i get this error:
> Cast from type 'DBNull' to type 'String' is not valid.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
> type 'String' is not valid.
> Source Error:
>
> Line 63:
> Line 64: 'Assign the returned value of the output paramater
> Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
> Where did i go wrong?
> Thanks for any insights.
> Nab
>
>|||A NULL @.BizAddress value will be returned when no data is found and this
cannot be converted to a .Net string data type. You can check for NULL
using DbNull.Value:
If cmd.Parameters("@.BizAddress").Value Is DBNull.Value Then
MessageBox.Show("BizName not found")
Else
TextBox2.Text = cmd.Parameters("@.BizAddress").Value
End If
Also, you need specify varchar(50) in your stored procedure parameter
declaration. The default length is 1.
CREATE PROCEDURE GetAddress
@.BizName varchar(50),
@.BizAddress varchar (50) output
AS
SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>I have a connection (SqlConnection1) established through the GUI. Here's
>the
> code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
> 'Enusre the connection is open
> If (cmd.Connection.State <> ConnectionState.Open) Then
> cmd.Connection.Open()
> End If
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> 'Close the connection
> cmd.Connection.Close()
> End Sub
> This code allows a textbox (textbox1) to fill an input paramater and
> displays the contents of the returned value from the output paramater in a
> textbox (textbox2). It works fine with s imple select statement. But with
> a
> stored procedure i get this error:
> Cast from type 'DBNull' to type 'String' is not valid.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
> type 'String' is not valid.
> Source Error:
>
> Line 63:
> Line 64: 'Assign the returned value of the output paramater
> Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
> Where did i go wrong?
> Thanks for any insights.
> Nab
>
>|||Nab wrote:
> I have a connection (SqlConnection1) established through the GUI.
> Here's the code:
>
You really should post these client-side questions to a more appropriate
newsgroup. Here are some suggestions:
microsoft.public.dotnet.languages.vb.data
microsoft.public.dotnet.framework.adonet
More below:

> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction =
> ParameterDirection.Output
>
<snip>
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
<snip>
> Exception Details: System.InvalidCastException: Cast from type
> 'DBNull' to type 'String' is not valid.
>
<snip>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
Always, always, ALWAYS set the length of your parameters:
@.BizName varchar(50),
@.BizAddress varchar(50) output
Do not depend on the default values,

> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
>
From online help:
If the ParameterDirection is output, and execution of the associated
SqlCommand does not return a value, the SqlParameter contains a null value.
So I would guess that the value of @.BizName is not getting set. Use SQL
Profiler to verify this.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks Dan. Stating the size in the stored procedure did the trick. Cheers.
Nab
"Dan Guzman" wrote:

> A NULL @.BizAddress value will be returned when no data is found and this
> cannot be converted to a .Net string data type. You can check for NULL
> using DbNull.Value:
> If cmd.Parameters("@.BizAddress").Value Is DBNull.Value Then
> MessageBox.Show("BizName not found")
> Else
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> End If
> Also, you need specify varchar(50) in your stored procedure parameter
> declaration. The default length is 1.
> CREATE PROCEDURE GetAddress
> @.BizName varchar(50),
> @.BizAddress varchar (50) output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>
>