Iterate through list of parameters

Hello,
I want to concatenate a string like
=Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
+ Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
+ Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
+ ...
But this should be universal for all reports and universal for
single/multivalue parameters (where JOINs would be necessary).
So my question:
Is it possible to iterate through the list of parameters in custom code or
in an assembly to return such a string in a single, report-independant way?
Kind regards,
R."Ralph Watermann" wrote:
> Hello,
> I want to concatenate a string like
> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
> + ...
> But this should be universal for all reports and universal for
> single/multivalue parameters (where JOINs would be necessary).
> So my question:
> Is it possible to iterate through the list of parameters in custom code or
> in an assembly to return such a string in a single, report-independant way?
> Kind regards,
> R.
>
>
sure you can ... here is a function I used in a previous report :
function createparameter(ByVal param as object) as object
dim strNewParam() as string
dim countery as int32
dim x as object
for each x in param
countery = countery + 1
next
redim strNewparam(countery + 1)
dim counterx as int32
counterx = 0
for each x in param
strNewParam(counterx) = x
counterx = counterx + 1
next
strNewParam(counterx) = ""
return strNewParam
end function
enjoy
Peter|||Ralph,
I have used the Join function to concatenate the selected values of my
multivalue parameters like this:
=Join(Parameters!Para1.Label + ": " + Parameters!Para1.Value, ", ")
if you need to wrap anything in quotes you will need to modify, say add
quotes around the whole thing:
=Join("'" + Parameters!Para1.Label + ": " + Parameters!Para1.Value, "', '")
Hope this helps
Reeves
"Ralph Watermann" wrote:
> Hello,
> I want to concatenate a string like
> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
> + ...
> But this should be universal for all reports and universal for
> single/multivalue parameters (where JOINs would be necessary).
> So my question:
> Is it possible to iterate through the list of parameters in custom code or
> in an assembly to return such a string in a single, report-independant way?
> Kind regards,
> R.
>
>|||Hello Peter,
thank you very much for your help. Maybe I do not understand your code, but
I can't see the solution to my problem.
So I try to explain in more detail:
I wan't to print out the filters a user selected for displaying a report,
containing all filter descriptions and values. And so I think I need to
write a function like
function concatenateparameter(ByVal param as object) as string
but I do not know how to specify the object "param" which has to consist auf
all available parameters and their descriptions.
Kind regards,
Ralph
"Peter De Rop" <PeterDeRop@.discussions.microsoft.com> schrieb im Newsbeitrag
news:507E6A77-B3E6-4B26-8171-D5CB69764E68@.microsoft.com...
>
> "Ralph Watermann" wrote:
>> Hello,
>> I want to concatenate a string like
>> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
>> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
>> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
>> + ...
>> But this should be universal for all reports and universal for
>> single/multivalue parameters (where JOINs would be necessary).
>> So my question:
>> Is it possible to iterate through the list of parameters in custom code
>> or
>> in an assembly to return such a string in a single, report-independant
>> way?
>> Kind regards,
>> R.
>>
> sure you can ... here is a function I used in a previous report :
> function createparameter(ByVal param as object) as object
> dim strNewParam() as string
> dim countery as int32
> dim x as object
> for each x in param
> countery = countery + 1
> next
> redim strNewparam(countery + 1)
> dim counterx as int32
> counterx = 0
> for each x in param
> strNewParam(counterx) = x
> counterx = counterx + 1
> next
> strNewParam(counterx) = ""
> return strNewParam
> end function
> enjoy
> Peter

it maybe a wrong return value

There is a sql function like this below:
Create Function dbo.abc
(@.ParamOne decimal, @.ParamTwo decimal)
RETURNS Bit
AS
BEGIN
DECLARE @.Result bit
if abs(@.ParamOne-@.ParamTwo)<0.01
SET @.Result=1
else
SET @.Result=0
RETURN @.Result
END
if i execute this sentence: "select dbo.abc (2.12,2.11)" , why does the
function return 1 instead of 0?
Thanks.
Lorry
Please give the precision and scale while using decimal. See the below
example.
ALTER Function dbo.abc
(@.ParamOne numeric(10,2), @.ParamTwo numeric(10,2))
RETURNS Bit
AS
BEGIN
DECLARE @.Result bit
if abs(@.ParamOne-@.ParamTwo)<0.01
SET @.Result=1
else
SET @.Result=0
RETURN @.Result
END
Thanks
Hari
"Lorry Astra" <LorryAstra@.discussions.microsoft.com> wrote in message
news:A86A320C-192C-4321-A4D4-05B32ADC3BD1@.microsoft.com...
> There is a sql function like this below:
> Create Function dbo.abc
> (@.ParamOne decimal, @.ParamTwo decimal)
> RETURNS Bit
> AS
> BEGIN
> DECLARE @.Result bit
> if abs(@.ParamOne-@.ParamTwo)<0.01
> SET @.Result=1
> else
> SET @.Result=0
> RETURN @.Result
> END
> if i execute this sentence: "select dbo.abc (2.12,2.11)" , why does the
> function return 1 instead of 0?
> Thanks.
>
> Lorry

Issues upgrading MSDE to SP4

Hello I'm having some issues upgrading MSDE sp3 to MSDE sp4. I don't
see anything in the log file of any importance, no return value 3 just
an occasional 2 which is interesting because I'm not canceling. The
only error I can find is in the application log:
Event Type:Warning
Event Source:MsiInstaller
Event Category:None
Event ID:1015
Date:6/14/2005
Time:1:41:48 PM
User:domain\user
Computer:servername
Description:
Failed to connect to server. Error: 0x800401F0
I am part of the system administrators role so should be able to
contact the database. I did stop all of the services prior to
beginning the install so that I would not have to reboot in order for
the upgrade to go through. This is a production machine and I can't
afford downtime nor any reboots.
Thanks in advance for your help.
Also this is not a named instance and I have already attempted to use
the proper MSP file to accomodate the upgrade.
|||hi,
Kewlhand wrote:
> Hello I'm having some issues upgrading MSDE sp3 to MSDE sp4. I don't
> see anything in the log file of any importance, no return value 3 just
> an occasional 2 which is interesting because I'm not canceling. The
> only error I can find is in the application log:
> Event Type: Warning
> Event Source: MsiInstaller
> Event Category: None
> Event ID: 1015
> Date: 6/14/2005
> Time: 1:41:48 PM
> User: domain\user
> Computer: servername
> Description:
> Failed to connect to server. Error: 0x800401F0
> I am part of the system administrators role so should be able to
> contact the database. I did stop all of the services prior to
> beginning the install so that I would not have to reboot in order for
> the upgrade to go through. This is a production machine and I can't
> afford downtime nor any reboots.
> Thanks in advance for your help.
please have a look at http://tinyurl.com/bwrvv if helps
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||none of those keys exist with those parameters. Thanks for the info
though. If you've got anything else I'd love to hear it.
Thanks.
Kewlhand
|||I too am experiencing this issue. I've found that on two systems
running XP SP2 that I can connect to the SQL server using SQLDMO, but
not using Enterprise Manager over a remote TCP/IP connection.
I also am seeing this on both systems' Application Event Logs when MSDE
starts:
SuperSocket info: ConnectionListen(Shared-Memory (LPC)) : Error 5.
and
SuperSocket info: (SpnRegister) : Error 1355.
Both systems report the same MSDE version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
I'm pressed for time, so I'll be backing up data and reinstalling MSDE
then SP4 on the two machines, but any tips for the future would be
greatly appreciated.
Kind regards,
Jordan.
Kewlhand wrote:
> none of those keys exist with those parameters. Thanks for the info
> though. If you've got anything else I'd love to hear it.
> Thanks.
> Kewlhand

Issue with URL Encoding report's hyperlink value.

Hello,

I've created a hyperlink in a report. The hyperlink action is 'Jump to URL' The URL is http://ServerName/SomePage.aspx?AccountCode=####&InvoiceLevel=1

The hyperlink works just fine in the designer, it's value becomes http://ServerName/SomePage.aspx?AccountCode=%23%%23%%23%%23&InvoiceLevel=1, however when I run from the web the report's URL is not properly URL encoded. It's value is http://ServerName/SomePage.aspx?AccountCode=#%23%23%23&InvoiceLevel=1 which is incorrect since the browser now treats the # as a hash.
I've tried explicitly URL Encoding the #### in the function but the report returns an error. "http://ServerName/SomePage.aspx?AccountCode=" + System.Web.HttpUtility.UrlEncode(####) + "&InvoiceLevel=1"

I've tried it in RS2000 & RS2005, same results.

Help..
Robert.

Hi Robert,

It looks like the first pound sign (number sign, hash, tic-tac-toe, octothrope... call it what you will) is being interpreted as a URL anchor symbol.

Is the "access" param always literally "####"? If so it should work if you URLEncode yourself by typing "%23%23%23%23".

If the access param is dynamic and may or may not begin with a #, you could use the Replace function in an expression to encode any #'s.

-Chris|||

Thanks for your reply,

The AccountCode parameter value is dynamic and for the most part as meaningful data, however the data originates from the mainframe so there are values that start with #. I could encode it using the replace however the Accountcode could contain all possible characters. Any other suggestions.

Robert.

|||

Try using the UrlEncode function on the argument value, e.g.:

=Globals!ReportServerUrl+ "?" & Globals!ReportFolder & "/Report&Parameter=" &
System.Web.HttpUtility.UrlEncode(Fields!AccountID.Value)

See also MSDN:
http://msdn2.microsoft.com/en-us/library/system.web.httputility.urlencode.aspx

-- Robert

|||

I tried using System.Web.HttpUtility.UrlEncode. Look at my first post. It works in the designer but not in Report Manager. It's very easy to recreate. Here are the steps:

Add a new blank report to a project.

Drop on a text box and set the expression to System.Web.HttpUtility.UrlEncode("####")

click report properties, click the reference tag and add System.web.

Preview the report in the designer. It works just fine.

Deploy the report and then view it in Report Manager. It displays #errror. I suspected the Report Server is not able to find System.web.dll

Robert.

|||

Did you ever resolve this problem, as I've just hit it!

Issue with URL Encoding report's hyperlink value.

Hello,

I've created a hyperlink in a report. The hyperlink action is 'Jump to URL' The URL is http://ServerName/SomePage.aspx?AccountCode=####&InvoiceLevel=1

The hyperlink works just fine in the designer, it's value becomes http://ServerName/SomePage.aspx?AccountCode=%23%%23%%23%%23&InvoiceLevel=1, however when I run from the web the report's URL is not properly URL encoded. It's value is http://ServerName/SomePage.aspx?AccountCode=#%23%23%23&InvoiceLevel=1 which is incorrect since the browser now treats the # as a hash.
I've tried explicitly URL Encoding the #### in the function but the report returns an error. "http://ServerName/SomePage.aspx?AccountCode=" + System.Web.HttpUtility.UrlEncode(####) + "&InvoiceLevel=1"

I've tried it in RS2000 & RS2005, same results.

Help..
Robert.

Hi Robert,

It looks like the first pound sign (number sign, hash, tic-tac-toe, octothrope... call it what you will) is being interpreted as a URL anchor symbol.

Is the "access" param always literally "####"? If so it should work if you URLEncode yourself by typing "%23%23%23%23".

If the access param is dynamic and may or may not begin with a #, you could use the Replace function in an expression to encode any #'s.

-Chris|||

Thanks for your reply,

The AccountCode parameter value is dynamic and for the most part as meaningful data, however the data originates from the mainframe so there are values that start with #. I could encode it using the replace however the Accountcode could contain all possible characters. Any other suggestions.

Robert.

|||

Try using the UrlEncode function on the argument value, e.g.:

=Globals!ReportServerUrl+ "?" & Globals!ReportFolder & "/Report&Parameter=" &
System.Web.HttpUtility.UrlEncode(Fields!AccountID.Value)

See also MSDN:
http://msdn2.microsoft.com/en-us/library/system.web.httputility.urlencode.aspx

-- Robert

|||

I tried using System.Web.HttpUtility.UrlEncode. Look at my first post. It works in the designer but not in Report Manager. It's very easy to recreate. Here are the steps:

Add a new blank report to a project.

Drop on a text box and set the expression to System.Web.HttpUtility.UrlEncode("####")

click report properties, click the reference tag and add System.web.

Preview the report in the designer. It works just fine.

Deploy the report and then view it in Report Manager. It displays #errror. I suspected the Report Server is not able to find System.web.dll

Robert.

|||

Did you ever resolve this problem, as I've just hit it!

Issue with incorrect metadata ?

We're running SQL2k5 and I've got some stored procedures which all have the
last parameter as optional with a default value of zero i.e.
create procedure myproc
@.Parm1 int,
@.Parm2 int=0
when I query the system catalogs on this proc the rows returned do not
indicate the parameter as having a default value....I was planning to use
this information but cannot seem to figure out why this is wrong. The
sys.parameters column "has_default_value" is zero for every parameter in all
of our databases....in sys.syscolumns the cdefault is zero as well.
Is there somewhere else to find this data and be able to depend on it? I'm
really stuck here the whole team is waiting on me and I'm supposed to be
providing a home grown solution for automated building of .NET SqlCommand
objects based on this information.
select * from sys.parameters where object_id=2056602615
select * from sys.syscolumns where id=2056602615
> when I query the system catalogs on this proc the rows returned do not
> indicate the parameter as having a default value....I was planning to use
> this information but cannot seem to figure out why this is wrong. The
> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
This is true, the information is not stored there (nor in
sys.all_parameters).
I ran a profiler trace and monitored expanding the parameters node under a
stored procedure in Management Studio (which shows "default" / "no default"
but not the actual value). Ignoring names/ids that are specific to my
environment, I saw this (my most relevant observation highlighted on line
13):
SELECT 'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(db_name(),'''')
+ ']' + '/StoredProcedure[@.Name=' + quotename(sp.name,'''')
+ ' and @.Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''')
+ ']' + '/Param[@.Name=' + quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar')
AND param.max_length <> -1 THEN param.max_length/2 ELSE
param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
null AS [DefaultValue], -- *********** NOTICE THIS ************
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param
ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = param.system_type_id
and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'fakeProcedure'
and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC
Nothing more promising showed up in the trace when scripting the object as
create to new window, or using the modify context menu option. Both seem to
just grab the code from sys.sql_modules and, in the case of modify, change
CREATE to ALTER -- without even bothering with the parameter list at all.
I looked at sp_sproc_columns, which I have spotted in profiler from time to
time, coming from an application that uses ODBC to call stored procedures.
But this procedure does not yield any information about default values. It
gets column_def from spt_sproc_columns_odbc_view (which I can't figure out
how to query directly) but it looks to be always null. I also tried to find
the source for spt_sproc_columns_odbc_view but it seems this may be locked
away in mssqlsystemresource db. The following yielded nothing:
use master;
go
select * from sys.all_objects where name = 'spt_sproc_columns_odbc_view';
select object_definition(object_id('spt_sproc_columns_odb c_view'));
select * from sys.sql_modules where object_id =
object_id('spt_sproc_columns_odbc_view');
select * from sys.system_sql_modules where object_name(object_id) =
'spt_sproc_columns_odbc_view';
Frankly, I think that SQL Server only stores this value in the text in
syscomments / sys.sql_modules. And when the node I mentioned above expands
it must parse the stored procedure text to see whether the parameter
declarations have = signs next to them or not. I couldn't find any other
way to get this information, and I remember it coming up during the beta and
I'm pretty sure it was closed as "won't fix." So unfortunately I think you
are stuck in the same boat; parsing
object_definition(object_id('procedure_name')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-parameters-for-a-function-or-stored-procedure.html
And this BOL article for SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms190340.aspx
Which says:
"SQL Server only maintains default values for CLR objects in this catalog
view; therefore, this column has a value of 0 for Transact-SQL objects. To
view the default value of a parameter in a Transact-SQL object, query the
definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
I have submitted a request for more clarification, and will follow up if I
get any useful information.
Cheers,
Aaron
|||> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
I have submitted a suggestion to Microsoft regarding this issue through
"official" channels.
If you have a passport / Windows Live ID, you can see my feedback here, and
vote if you feel strongly enough about it:
http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
|||Thanks for that reference...gives me alot to go on...
I wasn't trying to get the default value for a parameter...just the
knowledge that a parameter has a default value and can be considered
optional for input....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Op7OOSY$GHA.1220@.TK2MSFTNGP04.phx.gbl...
> Books Online is pretty clear on this. Here's a quote from sys.parameters,
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm
> :
> "SQL Server only maintains default values for CLR objects in this catalog
> view; therefore, this column has a value of 0 for Transact-SQL objects. To
> view the default value of a parameter in a Transact-SQL object, query the
> definition column of the sys.sql_modules catalog view, or use the
> OBJECT_DEFINITION system function."
> It has always been the case that we cannot get the default values of
> parameters in SQL Server. Seems we now can get it for CLR procedures, but
> still not for TSQL objects. So same applies as for earlier versions: parse
> the source code. You might want to post an enhancement request at:
> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
>
|||Voted!!!
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23AyWj3X$GHA.2328@.TK2MSFTNGP02.phx.gbl...
> I have submitted a suggestion to Microsoft regarding this issue through
> "official" channels.
> If you have a passport / Windows Live ID, you can see my feedback here,
> and vote if you feel strongly enough about it:
> http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
>
|||Here is a workaround for the time being (also posting it to the issue on
Connect).
I am also working on a version that retrieves the explicit default value,
but that is proving more cumbersome if the default value is a string and
contains a comma (but I am close).
ALTER PROCEDURE dbo.sys_GetParameters
@.object_name NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.object_id INT,
@.paramID INT,
@.paramName SYSNAME,
@.definition NVARCHAR(MAX),
@.t NVARCHAR(MAX),
@.loc1 INT,
@.loc2 INT,
@.loc3 INT,
@.loc4 INT,
@.has_default_value BIT;
SET @.object_id = OBJECT_ID(@.object_name);
IF (@.object_id IS NOT NULL)
BEGIN
SELECT @.definition = OBJECT_DEFINITION(@.object_id);
CREATE TABLE #params
(
parameter_id INT PRIMARY KEY,
has_default_value BIT NOT NULL DEFAULT (0)
);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT
parameter_id,
[name]
FROM
sys.parameters
WHERE
[object_id] = @.object_id;
OPEN c;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT
@.t = SUBSTRING
(
@.definition,
CHARINDEX(@.paramName, @.definition),
4000
),
@.has_default_value = 0;
SET @.loc1 = COALESCE(NULLIF(CHARINDEX('''', @.t), 0), 4000);
SET @.loc2 = COALESCE(NULLIF(CHARINDEX(',', @.t), 0), 4000);
SET @.loc3 = NULLIF(CHARINDEX('OUTPUT', @.t), 0);
SET @.loc4 = NULLIF(CHARINDEX('AS', @.t), 0);
SET @.loc1 = CASE WHEN @.loc2 < @.loc1 THEN @.loc2 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc3 < @.loc1 THEN @.loc3 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc4 < @.loc1 THEN @.loc4 ELSE @.loc1 END;
IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@.t, 1, @.loc1)))) > 0
SET @.has_default_value = 1;
INSERT #params
(
parameter_id,
has_default_value
)
SELECT
@.paramID,
@.has_default_value;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
END
SELECT
sp.[object_id],
[object_name] = @.object_name,
param_name = sp.[name],
sp.parameter_id,
type_name = UPPER(st.[name]),
sp.max_length,
sp.[precision],
sp.scale,
sp.is_output,
p.has_default_value
FROM
sys.parameters sp
INNER JOIN
#params p
ON
sp.parameter_id = p.parameter_id
INNER JOIN
sys.types st
ON
sp.user_type_id = st.user_type_id
WHERE
sp.[object_id] = @.object_id;
CLOSE c;
DEALLOCATE c;
DROP TABLE #params;
END
END
GO

Issue with incorrect metadata ?

We're running SQL2k5 and I've got some stored procedures which all have the
last parameter as optional with a default value of zero i.e.
create procedure myproc
@.Parm1 int,
@.Parm2 int=0
when I query the system catalogs on this proc the rows returned do not
indicate the parameter as having a default value....I was planning to use
this information but cannot seem to figure out why this is wrong. The
sys.parameters column "has_default_value" is zero for every parameter in all
of our databases....in sys.syscolumns the cdefault is zero as well.
Is there somewhere else to find this data and be able to depend on it? I'm
really stuck here the whole team is waiting on me and I'm supposed to be
providing a home grown solution for automated building of .NET SqlCommand
objects based on this information.
select * from sys.parameters where object_id=2056602615
select * from sys.syscolumns where id=2056602615> when I query the system catalogs on this proc the rows returned do not
> indicate the parameter as having a default value....I was planning to use
> this information but cannot seem to figure out why this is wrong. The
> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
This is true, the information is not stored there (nor in
sys.all_parameters).
I ran a profiler trace and monitored expanding the parameters node under a
stored procedure in Management Studio (which shows "default" / "no default"
but not the actual value). Ignoring names/ids that are specific to my
environment, I saw this (my most relevant observation highlighted on line
13):
SELECT 'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(db_name(),'''')
+ ']' + '/StoredProcedure[@.Name=' + quotename(sp.name,'''')
+ ' and @.Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''')
+ ']' + '/Param[@.Name=' + quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar')
AND param.max_length <> -1 THEN param.max_length/2 ELSE
param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
null AS [DefaultValue], -- *********** NOTICE THIS ************
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param
ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = param.system_type_id
and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'fakeProcedure'
and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC
Nothing more promising showed up in the trace when scripting the object as
create to new window, or using the modify context menu option. Both seem to
just grab the code from sys.sql_modules and, in the case of modify, change
CREATE to ALTER -- without even bothering with the parameter list at all.
I looked at sp_sproc_columns, which I have spotted in profiler from time to
time, coming from an application that uses ODBC to call stored procedures.
But this procedure does not yield any information about default values. It
gets column_def from spt_sproc_columns_odbc_view (which I can't figure out
how to query directly) but it looks to be always null. I also tried to find
the source for spt_sproc_columns_odbc_view but it seems this may be locked
away in mssqlsystemresource db. The following yielded nothing:
use master;
go
select * from sys.all_objects where name = 'spt_sproc_columns_odbc_view';
select object_definition(object_id('spt_sproc_columns_odbc_view'));
select * from sys.sql_modules where object_id =object_id('spt_sproc_columns_odbc_view');
select * from sys.system_sql_modules where object_name(object_id) ='spt_sproc_columns_odbc_view';
Frankly, I think that SQL Server only stores this value in the text in
syscomments / sys.sql_modules. And when the node I mentioned above expands
it must parse the stored procedure text to see whether the parameter
declarations have = signs next to them or not. I couldn't find any other
way to get this information, and I remember it coming up during the beta and
I'm pretty sure it was closed as "won't fix." So unfortunately I think you
are stuck in the same boat; parsing
object_definition(object_id('procedure_name')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-parameters-for-a-function-or-stored-procedure.html
And this BOL article for SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms190340.aspx
Which says:
"SQL Server only maintains default values for CLR objects in this catalog
view; therefore, this column has a value of 0 for Transact-SQL objects. To
view the default value of a parameter in a Transact-SQL object, query the
definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
I have submitted a request for more clarification, and will follow up if I
get any useful information.
Cheers,
Aaron|||> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
I have submitted a suggestion to Microsoft regarding this issue through
"official" channels.
If you have a passport / Windows Live ID, you can see my feedback here, and
vote if you feel strongly enough about it:
http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143|||Books Online is pretty clear on this. Here's a quote from sys.parameters,
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm :
"SQL Server only maintains default values for CLR objects in this catalog view; therefore, this
column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a
Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
It has always been the case that we cannot get the default values of parameters in SQL Server. Seems
we now can get it for CLR procedures, but still not for TSQL objects. So same applies as for earlier
versions: parse the source code. You might want to post an enhancement request at:
http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We're running SQL2k5 and I've got some stored procedures which all have the last parameter as
> optional with a default value of zero i.e.
> create procedure myproc
> @.Parm1 int,
> @.Parm2 int=0
> when I query the system catalogs on this proc the rows returned do not indicate the parameter as
> having a default value....I was planning to use this information but cannot seem to figure out
> why this is wrong. The sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
> Is there somewhere else to find this data and be able to depend on it? I'm really stuck here the
> whole team is waiting on me and I'm supposed to be providing a home grown solution for automated
> building of .NET SqlCommand objects based on this information.
> select * from sys.parameters where object_id=2056602615
> select * from sys.syscolumns where id=2056602615
>|||Thanks for that reference...gives me alot to go on...
I wasn't trying to get the default value for a parameter...just the
knowledge that a parameter has a default value and can be considered
optional for input....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Op7OOSY$GHA.1220@.TK2MSFTNGP04.phx.gbl...
> Books Online is pretty clear on this. Here's a quote from sys.parameters,
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm
> :
> "SQL Server only maintains default values for CLR objects in this catalog
> view; therefore, this column has a value of 0 for Transact-SQL objects. To
> view the default value of a parameter in a Transact-SQL object, query the
> definition column of the sys.sql_modules catalog view, or use the
> OBJECT_DEFINITION system function."
> It has always been the case that we cannot get the default values of
> parameters in SQL Server. Seems we now can get it for CLR procedures, but
> still not for TSQL objects. So same applies as for earlier versions: parse
> the source code. You might want to post an enhancement request at:
> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
>> We're running SQL2k5 and I've got some stored procedures which all have
>> the last parameter as optional with a default value of zero i.e.
>> create procedure myproc
>> @.Parm1 int,
>> @.Parm2 int=0
>> when I query the system catalogs on this proc the rows returned do not
>> indicate the parameter as having a default value....I was planning to
>> use this information but cannot seem to figure out why this is wrong.
>> The sys.parameters column "has_default_value" is zero for every parameter
>> in all of our databases....in sys.syscolumns the cdefault is zero as
>> well.
>> Is there somewhere else to find this data and be able to depend on it?
>> I'm really stuck here the whole team is waiting on me and I'm supposed to
>> be providing a home grown solution for automated building of .NET
>> SqlCommand objects based on this information.
>> select * from sys.parameters where object_id=2056602615
>> select * from sys.syscolumns where id=2056602615
>>
>|||Voted!!!
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23AyWj3X$GHA.2328@.TK2MSFTNGP02.phx.gbl...
>> sys.parameters column "has_default_value" is zero for every parameter in
>> all of our databases....in sys.syscolumns the cdefault is zero as well.
> I have submitted a suggestion to Microsoft regarding this issue through
> "official" channels.
> If you have a passport / Windows Live ID, you can see my feedback here,
> and vote if you feel strongly enough about it:
> http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
>|||Here is a workaround for the time being (also posting it to the issue on
Connect).
I am also working on a version that retrieves the explicit default value,
but that is proving more cumbersome if the default value is a string and
contains a comma (but I am close).
ALTER PROCEDURE dbo.sys_GetParameters
@.object_name NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.object_id INT,
@.paramID INT,
@.paramName SYSNAME,
@.definition NVARCHAR(MAX),
@.t NVARCHAR(MAX),
@.loc1 INT,
@.loc2 INT,
@.loc3 INT,
@.loc4 INT,
@.has_default_value BIT;
SET @.object_id = OBJECT_ID(@.object_name);
IF (@.object_id IS NOT NULL)
BEGIN
SELECT @.definition = OBJECT_DEFINITION(@.object_id);
CREATE TABLE #params
(
parameter_id INT PRIMARY KEY,
has_default_value BIT NOT NULL DEFAULT (0)
);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT
parameter_id,
[name]
FROM
sys.parameters
WHERE
[object_id] = @.object_id;
OPEN c;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT
@.t = SUBSTRING
(
@.definition,
CHARINDEX(@.paramName, @.definition),
4000
),
@.has_default_value = 0;
SET @.loc1 = COALESCE(NULLIF(CHARINDEX('''', @.t), 0), 4000);
SET @.loc2 = COALESCE(NULLIF(CHARINDEX(',', @.t), 0), 4000);
SET @.loc3 = NULLIF(CHARINDEX('OUTPUT', @.t), 0);
SET @.loc4 = NULLIF(CHARINDEX('AS', @.t), 0);
SET @.loc1 = CASE WHEN @.loc2 < @.loc1 THEN @.loc2 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc3 < @.loc1 THEN @.loc3 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc4 < @.loc1 THEN @.loc4 ELSE @.loc1 END;
IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@.t, 1, @.loc1)))) > 0
SET @.has_default_value = 1;
INSERT #params
(
parameter_id,
has_default_value
)
SELECT
@.paramID,
@.has_default_value;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
END
SELECT
sp.[object_id],
[object_name] = @.object_name,
param_name = sp.[name],
sp.parameter_id,
type_name = UPPER(st.[name]),
sp.max_length,
sp.[precision],
sp.scale,
sp.is_output,
p.has_default_value
FROM
sys.parameters sp
INNER JOIN
#params p
ON
sp.parameter_id = p.parameter_id
INNER JOIN
sys.types st
ON
sp.user_type_id = st.user_type_id
WHERE
sp.[object_id] = @.object_id;
CLOSE c;
DEALLOCATE c;
DROP TABLE #params;
END
END
GO

Issue with incorrect metadata ?

We're running SQL2k5 and I've got some stored procedures which all have the
last parameter as optional with a default value of zero i.e.
create procedure myproc
@.Parm1 int,
@.Parm2 int=0
when I query the system catalogs on this proc the rows returned do not
indicate the parameter as having a default value....I was planning to use
this information but cannot seem to figure out why this is wrong. The
sys.parameters column "has_default_value" is zero for every parameter in all
of our databases....in sys.syscolumns the cdefault is zero as well.
Is there somewhere else to find this data and be able to depend on it? I'm
really stuck here the whole team is waiting on me and I'm supposed to be
providing a home grown solution for automated building of .NET SqlCommand
objects based on this information.
select * from sys.parameters where object_id=2056602615
select * from sys.syscolumns where id=2056602615> when I query the system catalogs on this proc the rows returned do not
> indicate the parameter as having a default value....I was planning to use
> this information but cannot seem to figure out why this is wrong. The
> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
This is true, the information is not stored there (nor in
sys.all_parameters).
I ran a profiler trace and monitored expanding the parameters node under a
stored procedure in Management Studio (which shows "default" / "no default"
but not the actual value). Ignoring names/ids that are specific to my
environment, I saw this (my most relevant observation highlighted on line
13):
SELECT 'Server[@.Name=' + quotename(CAST(serverproperty(N'Serverna
me')
AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(db_name(),'''')
+ ']' + '/StoredProcedure[@.Name=' + quotename(sp.name,'''')
+ ' and @.Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''')
+ ']' + '/Param[@.Name=' + quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar')
AND param.max_length <> -1 THEN param.max_length/2 ELSE
param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
null AS [DefaultValue], -- *********** NOTICE THIS ************
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param
ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = param.system_type_id
and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'fakeProcedure'
and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC
Nothing more promising showed up in the trace when scripting the object as
create to new window, or using the modify context menu option. Both seem to
just grab the code from sys.sql_modules and, in the case of modify, change
CREATE to ALTER -- without even bothering with the parameter list at all.
I looked at sp_sproc_columns, which I have spotted in profiler from time to
time, coming from an application that uses ODBC to call stored procedures.
But this procedure does not yield any information about default values. It
gets column_def from spt_sproc_columns_odbc_view (which I can't figure out
how to query directly) but it looks to be always null. I also tried to find
the source for spt_sproc_columns_odbc_view but it seems this may be locked
away in mssqlsystemresource db. The following yielded nothing:
use master;
go
select * from sys.all_objects where name = 'spt_sproc_columns_odbc_view';
select object_definition(object_id('spt_sproc_c
olumns_odbc_view'));
select * from sys.sql_modules where object_id =
object_id('spt_sproc_columns_odbc_view')
;
select * from sys.system_sql_modules where object_name(object_id) =
'spt_sproc_columns_odbc_view';
Frankly, I think that SQL Server only stores this value in the text in
syscomments / sys.sql_modules. And when the node I mentioned above expands
it must parse the stored procedure text to see whether the parameter
declarations have = signs next to them or not. I couldn't find any other
way to get this information, and I remember it coming up during the beta and
I'm pretty sure it was closed as "won't fix." So unfortunately I think you
are stuck in the same boat; parsing
object_definition(object_id('procedure_n
ame')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-...-procedure.html
And this BOL article for SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms190340.aspx
Which says:
"SQL Server only maintains default values for CLR objects in this catalog
view; therefore, this column has a value of 0 for Transact-SQL objects. To
view the default value of a parameter in a Transact-SQL object, query the
definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
I have submitted a request for more clarification, and will follow up if I
get any useful information.
Cheers,
Aaron|||> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
I have submitted a suggestion to Microsoft regarding this issue through
"official" channels.
If you have a passport / Windows Live ID, you can see my feedback here, and
vote if you feel strongly enough about it:
http://connect.microsoft.com/feedba...edbackID=234143|||Books Online is pretty clear on this. Here's a quote from sys.parameters,
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-
7407d8b8a92b.htm :
"SQL Server only maintains default values for CLR objects in this catalog vi
ew; therefore, this
column has a value of 0 for Transact-SQL objects. To view the default value
of a parameter in a
Transact-SQL object, query the definition column of the sys.sql_modules cata
log view, or use the
OBJECT_DEFINITION system function."
It has always been the case that we cannot get the default values of paramet
ers in SQL Server. Seems
we now can get it for CLR procedures, but still not for TSQL objects. So sam
e applies as for earlier
versions: parse the source code. You might want to post an enhancement reque
st at:
http://connect.microsoft.com/site/s...aspx?SiteID=68
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We're running SQL2k5 and I've got some stored procedures which all have th
e last parameter as
> optional with a default value of zero i.e.
> create procedure myproc
> @.Parm1 int,
> @.Parm2 int=0
> when I query the system catalogs on this proc the rows returned do not ind
icate the parameter as
> having a default value....I was planning to use this information but cann
ot seem to figure out
> why this is wrong. The sys.parameters column "has_default_value" is zero
for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
> Is there somewhere else to find this data and be able to depend on it? I'
m really stuck here the
> whole team is waiting on me and I'm supposed to be providing a home grown
solution for automated
> building of .NET SqlCommand objects based on this information.
> select * from sys.parameters where object_id=2056602615
> select * from sys.syscolumns where id=2056602615
>|||Thanks for that reference...gives me alot to go on...
I wasn't trying to get the default value for a parameter...just the
knowledge that a parameter has a default value and can be considered
optional for input....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Op7OOSY$GHA.1220@.TK2MSFTNGP04.phx.gbl...
> Books Online is pretty clear on this. Here's a quote from sys.parameters,
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a
4-7407d8b8a92b.htm
> :
> "SQL Server only maintains default values for CLR objects in this catalog
> view; therefore, this column has a value of 0 for Transact-SQL objects. To
> view the default value of a parameter in a Transact-SQL object, query the
> definition column of the sys.sql_modules catalog view, or use the
> OBJECT_DEFINITION system function."
> It has always been the case that we cannot get the default values of
> parameters in SQL Server. Seems we now can get it for CLR procedures, but
> still not for TSQL objects. So same applies as for earlier versions: parse
> the source code. You might want to post an enhancement request at:
> http://connect.microsoft.com/site/s...aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
>|||Voted!!!
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23AyWj3X$GHA.2328@.TK2MSFTNGP02.phx.gbl...
> I have submitted a suggestion to Microsoft regarding this issue through
> "official" channels.
> If you have a passport / Windows Live ID, you can see my feedback here,
> and vote if you feel strongly enough about it:
> http://connect.microsoft.com/feedba...edbackID=234143
>|||Here is a workaround for the time being (also posting it to the issue on
Connect).
I am also working on a version that retrieves the explicit default value,
but that is proving more cumbersome if the default value is a string and
contains a comma (but I am close).
ALTER PROCEDURE dbo.sys_GetParameters
@.object_name NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.object_id INT,
@.paramID INT,
@.paramName SYSNAME,
@.definition NVARCHAR(MAX),
@.t NVARCHAR(MAX),
@.loc1 INT,
@.loc2 INT,
@.loc3 INT,
@.loc4 INT,
@.has_default_value BIT;
SET @.object_id = OBJECT_ID(@.object_name);
IF (@.object_id IS NOT NULL)
BEGIN
SELECT @.definition = OBJECT_DEFINITION(@.object_id);
CREATE TABLE #params
(
parameter_id INT PRIMARY KEY,
has_default_value BIT NOT NULL DEFAULT (0)
);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT
parameter_id,
[name]
FROM
sys.parameters
WHERE
[object_id] = @.object_id;
OPEN c;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT
@.t = SUBSTRING
(
@.definition,
CHARINDEX(@.paramName, @.definition),
4000
),
@.has_default_value = 0;
SET @.loc1 = COALESCE(NULLIF(CHARINDEX('''', @.t), 0), 4000);
SET @.loc2 = COALESCE(NULLIF(CHARINDEX(',', @.t), 0), 4000);
SET @.loc3 = NULLIF(CHARINDEX('OUTPUT', @.t), 0);
SET @.loc4 = NULLIF(CHARINDEX('AS', @.t), 0);
SET @.loc1 = CASE WHEN @.loc2 < @.loc1 THEN @.loc2 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc3 < @.loc1 THEN @.loc3 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc4 < @.loc1 THEN @.loc4 ELSE @.loc1 END;
IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@.t, 1, @.loc1)))) > 0
SET @.has_default_value = 1;
INSERT #params
(
parameter_id,
has_default_value
)
SELECT
@.paramID,
@.has_default_value;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
END
SELECT
sp.[object_id],
[object_name] = @.object_name,
param_name = sp.[name],
sp.parameter_id,
type_name = UPPER(st.[name]),
sp.max_length,
sp.[precision],
sp.scale,
sp.is_output,
p.has_default_value
FROM
sys.parameters sp
INNER JOIN
#params p
ON
sp.parameter_id = p.parameter_id
INNER JOIN
sys.types st
ON
sp.user_type_id = st.user_type_id
WHERE
sp.[object_id] = @.object_id;
CLOSE c;
DEALLOCATE c;
DROP TABLE #params;
END
END
GO

Issue with FORMAT use in textboxes when null value

SQL 2005 Dev, VS 2005

I use the following in my VS-created reports:

FORMAT("ShortDate", Fields!someField.value)

However, when the field returns a null field what displays in the tex box instead is the words "ShortDate". Can this be corrected?

Thanks in advance

Please reverse the order of the arguments, and use DateTime format strings as shown in the MSDN documentation:

* Standard datetime formats:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandarddatetimeformatstrings.asp

* Custom datetime formats:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandarddatetimeformatstrings.asp

In your case, you should try e.g. =Format(Fields!someField.Value, "d")

Alternatively, you can set the textbox Value property to =Fields!someValue.Value and set the Format property of the textbox to the desired format code.

-- Robert

Issue with float data type

Hi,

I have a table with float data type. When we insert or update data with some math(1+1.01) it converts the decimal value to approximate value.

Example:

Create a table say "Table1" with field "Field1" of data type float

Now insert a record with value 1.01

Select * from Table1 would give me out put at 1.01

Update Table1 set Field1 = 1.01+1

Now Select * from Table1

Instead of returning me 2.01, it returns 2.0099999999999998. Same thing happens if I insert record with following statement

insert into Table1(Field1) values (1+1.01)

Any idea why its doing this way? I know float is approximate data type, but shouldn't it be giving me correct value for what I am doing? Is there any server setting that I can change to get proper result?

Thanks,

Hiten:

I think the answer to your question is that there is no exact conversion of the number 2.01 into a hexidecimal floating point representation; what I remember is that it is initially surprising what numbers do not convert exactly and this is probably one of them. I think the answer is you need to live with the limitations of floating point representation or switch to an exact representation with a pre-defined precision and scale. If you stick with float, use of the ROUND function will be helpful. I verified and I receive the same behavior and same response that you do. Somebody check me on this?

Dave

|||

Hiten:

I found this article:

http://docs.python.org/tut/node16.html

The "B.1" section discusses the problem of representing 1/10 in binary; this should give the idea.

Dave

|||

Float is an approximate data type so it stores only close approximation of a value. See below link for more details:

http://msdn2.microsoft.com/en-us/library/ms187912.aspx

You need to use decimal or numeric to get exact representation of the value.

|||

Hi all,

What are the drawbacks of using decimal insted of float data type?

Thanks.

Issue with float data type

Hi,

I have a table with float data type. When we insert or update data with some math(1+1.01) it converts the decimal value to approximate value.

Example:

Create a table say "Table1" with field "Field1" of data type float

Now insert a record with value 1.01

Select * from Table1 would give me out put at 1.01

Update Table1 set Field1 = 1.01+1

Now Select * from Table1

Instead of returning me 2.01, it returns 2.0099999999999998. Same thing happens if I insert record with following statement

insert into Table1(Field1) values (1+1.01)

Any idea why its doing this way? I know float is approximate data type, but shouldn't it be giving me correct value for what I am doing? Is there any server setting that I can change to get proper result?

Thanks,

Hiten:

I think the answer to your question is that there is no exact conversion of the number 2.01 into a hexidecimal floating point representation; what I remember is that it is initially surprising what numbers do not convert exactly and this is probably one of them. I think the answer is you need to live with the limitations of floating point representation or switch to an exact representation with a pre-defined precision and scale. If you stick with float, use of the ROUND function will be helpful. I verified and I receive the same behavior and same response that you do. Somebody check me on this?

Dave

|||

Hiten:

I found this article:

http://docs.python.org/tut/node16.html

The "B.1" section discusses the problem of representing 1/10 in binary; this should give the idea.

Dave

|||

Float is an approximate data type so it stores only close approximation of a value. See below link for more details:

http://msdn2.microsoft.com/en-us/library/ms187912.aspx

You need to use decimal or numeric to get exact representation of the value.

|||

Hi all,

What are the drawbacks of using decimal insted of float data type?

Thanks.

Issue with fields without value

Hi all of you,

I'm loading a Sql destination table via flat file. Everything is file but when one concrete field is empty in my file

it generates an error. Why?

The first 10000 lines have data from 75 position till 85 in my file. But the 10001 no but at the same time I want to load

the rest of the fields

In my Sql table, of course that fields keep nulls.

When I ran DTS this casuistry worked fine.

[Destino 193_TMP [28]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

Thanks in advance for your help and thoughts,

Hi ya,

It seems that when you're casting it, it doesn't appear to be of right value for the casting function. If you don't have a casting function do put in the casting function so that null value is ignored or recognised.

Hope that helps

Cheers

Rizwan

|||

I belive the problem is that you're trying to insert a value with the wrong type in your destination. That's what it is with me when i get this error.

Issue with CONTAINSTABLE statement

I am using the following query to search all columns in the 'dashboard'
table for the value 'Meets':

SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,
'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]

I have multiple records that contain the word 'Meets', but none are
showing up as a result of this query. Any ideas?

Also, anytime I use a space in my search condition (Meets Expectations
instead of Meets) I am getting an error:
Syntax error occurred near 'Expectations'. Expected '' in search
condition 'Meets Expectations'.

Any ideas?

Thanks in advance.GAH.. Had not run a start_full for the index. All is working now.
Thanks!

Nate wrote:

Quote:

Originally Posted by

I am using the following query to search all columns in the 'dashboard'
table for the value 'Meets':
>
SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,
'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]
>
I have multiple records that contain the word 'Meets', but none are
showing up as a result of this query. Any ideas?
>
Also, anytime I use a space in my search condition (Meets Expectations
instead of Meets) I am getting an error:
Syntax error occurred near 'Expectations'. Expected '' in search
condition 'Meets Expectations'.
>
Any ideas?
>
Thanks in advance.

Issue inserting null value into a formview/gridview control

Hi,

My formview or gridview control stops updating or deleting a record once the record has a null value.

I have table tblTest with the following

pkID int NOT NULL **IDENTITY COLUMN**
string1 varchar(30)
string2 varchar(30)

I then create a SqlDataSource with the statement:

Select * From [tblTest]

I have the insert, update and delete statements generated, and choose optimistic concurrency. I add a couple records of dummy data.

I then drag a Formview control onto the page, and bind it to the SqlDataSource I just created. I then fire it up in my browser, and I can then update, insert and delete records. However, as soon as I update a record with a null value, I can no longer update or delete that record.

So, if I had a record in my FormView like:

string1: foo
string2: bar

I can update and delete normally. And when I update to:

string1: foo
string2:

the database correctly inserts a null value into string2. However, once that null is in the record, I can't change anything about the record. If I try to delete the record, the FormView will then display the previous record, but I can still page to the record that should have been deleted, and it still exists in the db. If I try to update the record, the edits I make will not keep and the process will fail silently.

What am I doing wrong? Should i be binding to a different object?

Regards,

Chris

Do you set your DataKeyNames for the formview? ( I thought you did). Anyway, here is a working copy and you may find your isseue by yourself.

<asp:FormView ID="fv1" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="id"
AllowPaging="true" >

<EditItemTemplate>

col1:

<asp:TextBox ID="col1TextBox" runat="server" Text='<%# Bind("col1") %>'></asp:TextBox><br />
col2:

<asp:TextBox ID="col2TextBox" runat="server" Text='<%# Bind("col2") %>'></asp:TextBox><br />

<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"

Text="Update"
</asp:LinkButton
<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

Text="Cancel"
</asp:LinkButton
</EditItemTemplate>
<InsertItemTemplate
col1:

<asp:TextBox ID="col1TextBox" runat="server" Text='<%# Bind("col1") %>'></asp:TextBox><br />
col2:

<asp:TextBox ID="col2TextBox" runat="server" Text='<%# Bind("col2") %>'></asp:TextBox><br /

<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"

Text="Insert"
</asp:LinkButton
<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

Text="Cancel"
</asp:LinkButton
</InsertItemTemplate>
<ItemTemplate
id:

<asp:Label ID="idLabel" runat="server" Text='<%# Eval("id") %>'></asp:Label><br /
col1:

<asp:Label ID="col1_Label" runat="server" Text='<%# Bind("col1") %>'></asp:Label><br />
col2:
<asp:Label ID="col2_Label" runat="server" Text='<%# Bind("col2") %>'></asp:Label><br /
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Delete"

Text="Delete"
</asp:LinkButton
<asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"

Text="Edit"
</asp:LinkButton
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="New"

Text="New"
</asp:LinkButton
</ItemTemplate>

</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:conn1 %>"
DeleteCommand="DELETE FROM [test1] WHERE [id] = @.id"
InsertCommand="INSERT INTO [test1] ( [col1], [col2]) VALUES ( @.col1, @.col2)"
SelectCommand="SELECT [id], [col1], [col2] FROM [test1]"
UpdateCommand="UPDATE [radios] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.id">
<DeleteParameters>
<asp:Parameter Name="id" Type="Int32" />
</DeleteParameters>
<UpdateParameters>

<asp:Parameter Name="col1" Type="String" />
<asp:Parameter Name="col2" Type="String" />
<asp:Parameter Name="id" Type="Int32" />
</UpdateParameters>
<InsertParameters>

<asp:Parameter Name="col1" Type="String" />
<asp:Parameter Name="col2" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

|||

Ok, our SqlDataSources differ somewhat. Was your auto-generated, or did you create it manually? The following code was generated for me:

============================================================================

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" DeleteCommand="DELETE FROM [tblTest] WHERE [id] = @.original_id AND [col1] = @.original_col1 AND [col2] = @.original_col2" InsertCommand="INSERT INTO [tblTest] ([col1], [col2]) VALUES (@.col1, @.col2)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [tblTest]" UpdateCommand="UPDATE [tblTest] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.original_id AND [col1] = @.original_col1 AND [col2] = @.original_col2"> <DeleteParameters> <asp:Parameter Name="original_id" Type="Int32" /> <asp:Parameter Name="original_col1" Type="String" /> <asp:Parameter Name="original_col2" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="col1" Type="String" /> <asp:Parameter Name="col2" Type="String" /> <asp:Parameter Name="original_id" Type="Int32" /> <asp:Parameter Name="original_col1" Type="String" /> <asp:Parameter Name="original_col2" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="col1" Type="String" /> <asp:Parameter Name="col2" Type="String" /> </InsertParameters> </asp:SqlDataSource>

=============================================================================================

 Specifically, my update and delete statements have different, more complicated WHERE clauses. When I change the WHERE clause so that it only uses the ID, the formview control works as it should:

 DeleteCommand="DELETE FROM [tblTest] WHERE [id] = @.original_id"

UpdateCommand
="UPDATE [tblTest] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.original_id"

Am I generating my statements incorrectly? It's bizarre that the SqlDataSource auto-generated command statements choke once they start working with null fields...

Regards,

Chris

|||

Hello,

Both of the codes are generated by the IDE, but you checked the "Use Optimistic Concurrency" to get the more complicated where clause. You may need find more information to use this. If you don't check this one, you will have the same code as I have.

HTH.

|||

That did it, thanks so much!

Regards,

Chris

|||The wizard portion that generates SQL Strings for optimistic queries has that bug. You can still use the wizard to generate the basic query, but you'll need to go in and modify it if any of the fields can be null.

Issue in using round in Vbscript

I am using a Round to get the Value of a Float datatype column from a database
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)

> samay
>
Please don't ask the same question in more than one thread.
KritiVerma@.hotmail.com wrote:

> I am using a Round to get the Value of a Float datatype column from a database
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>
>
>

Issue in using round in Vbscript

I am using a Round to get the Value of a Float datatype column from a databa
se
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column f
or Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)

> samay
>Please don't ask the same question in more than one thread.
KritiVerma@.hotmail.com wrote:

> I am using a Round to get the Value of a Float datatype column from a data
base
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>
>
>

Issue in using Round

I am using a Round to get the Value of a Float datatype column from a database
and if the use the below code and if the value of the Column is say 0 it returns me -1. please advice why this can be
Temp = round(0* 0.95,2)
samay
I am using a Round to get the Value of a Float datatype column from a database
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)

> samay
>
|||Samay,
Can you try to explain your problem again, and cut and paste the exact
statements that are not working? I don't understand what you are asking
at all. Since 0*0.95 is zero, the value of round(0*0.95,2) will be zero.
Steve Kass
Drew University
KritiVerma@.hotmail.com wrote:
[vbcol=seagreen]
> I am using a Round to get the Value of a Float datatype column from a database
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>
|||To add to Steve's response, the result of your round expression will always
be zero. I suspect your problem is due to persisting the result of a
boolean expression instead of the intended arithmetic expression result. A
VbScript True boolean value will be stored as -1 in a numeric datatype.
Hope this helps.
Dan Guzman
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:DCE60959-1658-4B62-B74A-30B1414229AB@.microsoft.com...
> I am using a Round to get the Value of a Float datatype column from a
database
> and if I use the below code
> and if the value of the Column is say 0
> It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype[vbcol=seagreen]
> please advice why this can be
> Temp = round(0* 0.95,2)
>

Issue in using Round

I am using a Round to get the Value of a Float datatype column from a databa
se
and if the use the below code and if the value of the Column is say 0 it ret
urns me -1. please advice why this can be
Temp = round(0* 0.95,2)
samayI am using a Round to get the Value of a Float datatype column from a databa
se
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column f
or Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)

> samay
>|||Samay,
Can you try to explain your problem again, and cut and paste the exact
statements that are not working? I don't understand what you are asking
at all. Since 0*0.95 is zero, the value of round(0*0.95,2) will be zero.
Steve Kass
Drew University
KritiVerma@.hotmail.com wrote:
[vbcol=seagreen]
> I am using a Round to get the Value of a Float datatype column from a data
base
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>|||To add to Steve's response, the result of your round expression will always
be zero. I suspect your problem is due to persisting the result of a
boolean expression instead of the intended arithmetic expression result. A
VbScript True boolean value will be stored as -1 in a numeric datatype.
Hope this helps.
Dan Guzman
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:DCE60959-1658-4B62-B74A-30B1414229AB@.microsoft.com...
> I am using a Round to get the Value of a Float datatype column from a
database
> and if I use the below code
> and if the value of the Column is say 0
> It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype[vbcol=seagreen]
> please advice why this can be
> Temp = round(0* 0.95,2)
>

Issue in using Round

I am using a Round to get the Value of a Float datatype column from a database
and if the use the below code and if the value of the Column is say 0 it returns me -1. please advice why this can be
Temp = round(0* 0.95,2)
samayI am using a Round to get the Value of a Float datatype column from a database
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)
> samay
>|||Samay,
Can you try to explain your problem again, and cut and paste the exact
statements that are not working? I don't understand what you are asking
at all. Since 0*0.95 is zero, the value of round(0*0.95,2) will be zero.
Steve Kass
Drew University
KritiVerma@.hotmail.com wrote:
> I am using a Round to get the Value of a Float datatype column from a database
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>
>>samay
>>|||To add to Steve's response, the result of your round expression will always
be zero. I suspect your problem is due to persisting the result of a
boolean expression instead of the intended arithmetic expression result. A
VbScript True boolean value will be stored as -1 in a numeric datatype.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:DCE60959-1658-4B62-B74A-30B1414229AB@.microsoft.com...
> I am using a Round to get the Value of a Float datatype column from a
database
> and if I use the below code
> and if the value of the Column is say 0
> It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
> > samay
> >