Model Builder Field Order and Inheritance

When I create a report model, it is easy to change the order in a given entity. Does anyone know how to order the fields in an entity when you are using inheritance or inlining? I want to have the fields in alphabetical order so that the end user doesn't have to hunt for the correct field.

The other item I need to find out is how to customize the prefixes that inlining uses. Any ideas there?

Ron

Specifying field order across entities is not supported in this release. One thing you can do to mitigate this is use field folders so the user has a "tree" to search instead of a long list that is not ordered very well. In the case of inlining, you can actually place the role in a folder by itself, and all its fields will show up there when the role is expanded.

By "prefixes" I assume you mean the contextual naming that is applied based on the Role.ContextualName and Attribute.ContextualName properties. These are the mechanism for controlling this behavior.

Hope this helps!

|||

That was right on the money with both of the questions. That folder trick is really helpful in cleaning up some seriously unmanageable lists of fields.

R

sql

Mod operation

Hi
I have a field that holds an integer value. I want to only show the value
of a remainder if there is one.
=Fields!IDCount.Value, Mod 2
What I am looking for is if the number is not even, then by having a
remainder when dividing by two will prove it is an odd number.
I keep getting the message "The value expression for the field
â'=(Fields!IDCount.Value, Mod 2)â' contains an error: [BC30198] ')' expected".
Any help would be greatI think you need something like the following as an expression for that
field;
=Iif (Fields!IDCount.Value Mod 2,
Code.RemainderHelp(Fields!IDCount.Value, 0),
Code.RemainderHelp(Fields!IDCount.Value,1))
So you would have a function in the custom code area called
RemainderHelp which formatted the output approprately depending on if
you passed in 0 or 1. I think something like that might work.
Make sense?

Mod function in SQL 2000

I need to use the mod function on a numeric field with 2digits decimal.
I was told that SQL server does not support mod on a decimal number.
Is it correct? If yes, is there any way around this?
Thanks in advanceWhat does MOD do?|||Modulus, right?|||Originally posted by Brett Kaiser
What does MOD do?

I meant MODULO. (Provides the remainder of one number divided by another.)

Thanks|||modulo isn't designed to work with decimals regardless if it's SQL or any other language.

You can must CAST or CONVERT the decimal to INT.

regards,

hmscott|||You can multiply both numbers by 100 and cast them as INT, like this:

select cast(4.32 * 100 as int) & cast(5.87 * 100 as int)|||Or, you can have this function:

if object_id('dbo.fn_Modulo42Decimals') is not null
drop function dbo.fn_Modulo42Decimals
go
create function dbo.fn_Modulo42Decimals (
@.First decimal(18, 2),
@.Second decimal(18, 2) ) returns int
as begin
return (
cast(@.First * 100 as int) & cast(@.Second * 100 as int)
)
end
go
select dbo.fn_Modulo42Decimals(5995.32, 154.67)

Not sure if it actually works right, always hated math :D|||How about

DECLARE @.x decimal(15,2), @.y decimal(15,2)
SELECT @.x = 12345.67, @.y = 2.15
SELECT FLOOR(@.x/@.y)-@.x/@.y|||But this is my favorite (courtesy of Kaiser's Bar & Grill - franchise inquiries are welcome):

DECLARE @.Weekend datetime
SELECT @.Weekend =
CONVERT(datetime,
CONVERT(varchar(10),GetDate(),120) + ' 17:00:00')
SELECT
DATEDIFF(mi, GetDate(), @.Weekend)/60.00 As Hours_till_Margarittaville|||and how about when I have a value of 48 digits long? I declared a float(50),
but when I perform a division it keeps displaying an error message about the maximum precision of a numeric being 38. How can I do this or work around?|||38 digits is the limit for numeric calculations in SQL Server. I don't know of any way around that limit within SQL.

You've piqued my curiouisity though... Why on earth would you care ?!?! What the heck would you store with fifty digits of precision ?

-PatP|||well, it's a barcode consisting of an employee's company number, department, dates, ... It's in total 50 digits long, the first 48 containing data and the last 2 are check digits (modulo 97). I need to check if the data is correctly recognized by our scanning software and if not, present that record to the operator that has to manually correct it.|||Hmmm... The only thing that comes to my mind would be to treat the barcode as two NUMERIC(30,0) columns.

-PatP|||And how would you do that? I don't think I can just cust the value in 2 parts and perform some calculations on it?|||Company number?
Departement number?

These are not really numbers, they are codes. If you don't add it, subtract it, or multiply it, then it is a string, not a number. Store your barcode as a 50 character string.|||they are already stored as varchar, but I have to perform a check on the recognized number, so: (first 48 characters) modulo 97 = (last 2 characters)|||Applause, please...

create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!

begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(8)
set @.WorkingString = @.BigNumString

set @.CalcString = left(@.WorkingString, 8)
while @.CalcString > @.Divisor
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 8)
end

return cast(@.CalcString as int)
end|||This may take some experimentation, but if I remember right

n * m mod p = ((n mod p) * (m mod p)) mod p

and

n + m mod p = ((n mod p) + (m mod p)) mod p

In that case, you can break your number into two pieces

(substing (number, 1, 25) * 10^25 + substring(number 26, 22)) mod 97. Break down the individual sections, and you have an ugly but workable solution.|||I'll applaud when I figure out how it was done, but it looks like it works. Don't you ever think inside the box?

Oh, and my 10^25 should probably be 10^22, or whatever exponent is correct for breaking the number into two halves.|||Found a bug. Have to handle the case where there are multiple consecutive zeros:

drop Function BigStringModulo
go
create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as

--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!

--TestVariables
-- declare @.BigNumString varchar(500)
-- declare @.Divisor int
-- set @.BigNumString = '97000000000001'
-- set @.Divisor = 2

begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(7)
set @.WorkingString = @.BigNumString

set @.CalcString = left(@.WorkingString, 7)
while (@.CalcString > @.Divisor) or len(@.WorkingString) > len(@.Divisor)
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 7)
end

--select cast(@.CalcString as int)
return cast(@.CalcString as int)
end|||Thanks for your replies. I will take a look at it see if it works tommorow, as I am far past my working hours, so I'll be off home now :D|||I had to change
"while @.CalcString >= @.divisor"

in case the divisor and working string were the same, but it looks like you found the same bug, and solved it a different way.

I think I understand what you have done, now. Your solution is similar to mine, but you are tagging on the upper string at the beginning of the lower string, and effectively getting the power of ten included that way. Nice job, Blindman.|||I liked the algorithm, but as you found out the code was not heavily tested. It is tough to verify the results when there are no other functions that will duplicate it! Please let me know if you see any more bugs in it.|||Well, it works ... Thanks blindman and MCrowley|||That was a fun challenge! Thanks for posting it.sql

MOD Equivalent in SQl Server?

I'm trying to build a query that I'd like only run on records with an odd
number in a specific field.
Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0"
Is there a mod function in SQL Server 2K? I can't find it... If not,
what's my alternative?
Thanks in advance...
gThe "%" character is the modulo function, so try
Field % 2 <> 0
"Greg Toronto" wrote:

> I'm trying to build a query that I'd like only run on records with an odd
> number in a specific field.
> Using the "MOD" function, I'd simply throw a criteria in that says where
> "Field Mod 2 <> 0"
> Is there a mod function in SQL Server 2K? I can't find it... If not,
> what's my alternative?
> Thanks in advance...
> g|||>> Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0" <<
They stole the infixed % from C; the ANSI/ISO syntax is MOD(). But be
careful and try your MOD to see how you expect negative numbers to
work. This was a big problem in Standardizing Pascal years ago.

MM/DD instead of MM/DD/YYYY?

Is it possible to display a date field as just MM/DD instead of the full MM/DD/YYYY?

Yes, just go to the format property of the textbox in which this date is displayed and enter MM/dd

|||That was embarrassingly easy. Thanks for the help.

mixed case

I know this is more of a front-end issue, but I need to know how to do it in
TSQL (sql 2k). I have a field that I need to convert to mixed case. The
field contains values such as "LAW OFFICES OF JOHN DOE", and I need it to
read "Law Offices Of John Doe". I'm sure with some work I could come up
with a string parser that will do this, but I'm wondering if anyone out
there has already come up with this code. If so, I'd appreciate knowing how
you accomplished this task.
Thanks in advance,
AndreI got lots of hits when I googled 'SQL Server proper case'. Here's one:
http://vyaskn.tripod.com/code/propercase.txt
Hope this helps.
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uaQ5mvAAGHA.4036@.TK2MSFTNGP10.phx.gbl...
>I know this is more of a front-end issue, but I need to know how to do it
>in TSQL (sql 2k). I have a field that I need to convert to mixed case.
>The field contains values such as "LAW OFFICES OF JOHN DOE", and I need it
>to read "Law Offices Of John Doe". I'm sure with some work I could come up
>with a string parser that will do this, but I'm wondering if anyone out
>there has already come up with this code. If so, I'd appreciate knowing
>how you accomplished this task.
> Thanks in advance,
> Andre
>|||using a numbers table, you can easily get this - here's one way
[note: this only considers spaces to be word separators]
create table numbers (number int primary key)
-- populate the numbers table with numbers from 1 to x, however you'd like
create function ProperCase(@.in varchar(8000)) returns varchar(8000)
as
begin
declare @.out varchar(8000)
select @.out=''
declare @.res table (ltr char(1), number int)
insert @.res
select substring(@.in, number, 1) as ltr, number
from numbers
where number<=len(@.in)
select @.out=@.out+
case when number=1
or number in (select number+1 from @.res where ltr ='') then
upper(ltr) else lower(ltr) end
from @.res order by number
return @.out
end
Andre wrote:
> I know this is more of a front-end issue, but I need to know how to do it
in
> TSQL (sql 2k). I have a field that I need to convert to mixed case. The
> field contains values such as "LAW OFFICES OF JOHN DOE", and I need it to
> read "Law Offices Of John Doe". I'm sure with some work I could come up
> with a string parser that will do this, but I'm wondering if anyone out
> there has already come up with this code. If so, I'd appreciate knowing h
ow
> you accomplished this task.
> Thanks in advance,
> Andre
>|||Hi
If you searched Google you would find multiple ways of doing this including
http://www.aspfaq.com/show.asp?id=2299
John
"Andre" wrote:

> I know this is more of a front-end issue, but I need to know how to do it
in
> TSQL (sql 2k). I have a field that I need to convert to mixed case. The
> field contains values such as "LAW OFFICES OF JOHN DOE", and I need it to
> read "Law Offices Of John Doe". I'm sure with some work I could come up
> with a string parser that will do this, but I'm wondering if anyone out
> there has already come up with this code. If so, I'd appreciate knowing h
ow
> you accomplished this task.
> Thanks in advance,
> Andre
>
>|||http://vyaskn.tripod.com/code.htm#propercase
"Andre" <no@.spam.com> wrote in message
news:uaQ5mvAAGHA.4036@.TK2MSFTNGP10.phx.gbl...
>I know this is more of a front-end issue, but I need to know how to do it
>in TSQL (sql 2k). I have a field that I need to convert to mixed case.
>The field contains values such as "LAW OFFICES OF JOHN DOE", and I need it
>to read "Law Offices Of John Doe". I'm sure with some work I could come up
>with a string parser that will do this, but I'm wondering if anyone out
>there has already come up with this code. If so, I'd appreciate knowing
>how you accomplished this task.
> Thanks in advance,
> Andre
>|||Cool, thanks. I did search Google but for mixed case, not proper case.
Thanks for all the tips/links.
Andre

Mistery with UPDATE statement

I have a table gVendor that has a field IsActive. It just shows if vendor is
active or not. When I update gVendor informatoin I leave IsActive out and
hanlde it in an other SP.
The problem is that eventhough I don't update that fields it resets to false
(0).
I can't figure out why.
Thank you.
Shimon.
Here is SP.
ALTER PROCEDURE [dbo].[g_pVendorUpdate]
(@.VendorId [int],
@.CompanyName [varchar](60),
@.VendorTypeId [tinyint],
@.AccountId [int],
@.TaxId [varchar](30),
@.ContactName [varchar](50),
@.Phone [varchar](25),
@.Fax [varchar](25),
@.Email [varchar](100),
@.Address [varchar](100),
@.City [varchar](50),
@.State [varchar](25),
@.Zip [varchar](10),
@.IsUtility [bit],
@.Note [varchar](500))
AS UPDATE [gVendor]
SET [CompanyName] = @.CompanyName,
[VendorTypeId] = @.VendorTypeId,
[AccountId] = @.AccountId,
[TaxId] = @.TaxId,
[ContactName] = @.ContactName,
[Phone] = @.Phone,
[Fax] = @.Fax,
[Email] = @.Email,
[Address] = @.Address,
[City] = @.City,
[State] = @.State,
[Zip] = @.Zip,
[IsUtility] = @.IsUtility,
[Note] = @.Note
WHERE
( [VendorId] = @.VendorId)
If I add IsActive=IsActive then it will work.
Here is the table
CREATE TABLE [dbo].[gVendor](
[VendorId] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](60) COLLATE Cyrillic_General_CI_AS NOT NULL,
[VendorTypeId] [tinyint] NOT NULL CONSTRAINT [DF_gVendor_VendorTypeId]
DEFAULT (0),
[IsActive] [bit] NOT NULL CONSTRAINT [DF_gVendor_IsActive] DEFAULT (1),
[AccountId] [int] NULL,
[TaxId] [varchar](30) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_TaxId] DEFAULT (''),
[ContactName] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL
CONSTRAINT [DF_gVendor_ContactName] DEFAULT (''),
[Phone] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Phone] DEFAULT (''),
[Fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Fax] DEFAULT (''),
[Email] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Email] DEFAULT (''),
[Address] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Address] DEFAULT (''),
[City] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_City] DEFAULT (''),
[State] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_State] DEFAULT (''),
[Zip] [char](10) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Zip] DEFAULT (''),
[Balance] [money] NOT NULL CONSTRAINT [DF_gVendor_Balance] DEFAULT (0),
[IsUtility] [bit] NOT NULL,
[Note] [varchar](500) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
[DF_gVendor_Memo] DEFAULT (''),
CONSTRAINT [PK_gVendor] PRIMARY KEY CLUSTERED
(
[VendorId] ASC
) ON [PRIMARY],
CONSTRAINT [unq_gVendorName] UNIQUE NONCLUSTERED
(
[CompanyName] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GODoes it do this when you are in QA or SSMS and make sure it happens there.
Then consider tracing your calls to see that something odd isn't taking
place. Nothing here seems to look fishy, but trace a set of calls
(including outputting values) and post it.

> If I add IsActive=IsActive then it will work.
Not sure that I understand this either. Is VendorId not the primary key?
It says it is in the script. The only thing this should to is eliminate
null rows. Since if IsActive is null then isActive=isActive is UNKNOWN,
which is not TRUE, and any NOT TRUE rows are eliminated.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Shimon Sim" <shimonsim048@.community.nospam> wrote in message
news:uVrhBMeJGHA.964@.tk2msftngp13.phx.gbl...
>I have a table gVendor that has a field IsActive. It just shows if vendor
>is active or not. When I update gVendor informatoin I leave IsActive out
>and hanlde it in an other SP.
> The problem is that eventhough I don't update that fields it resets to
> false (0).
> I can't figure out why.
> Thank you.
> Shimon.
> Here is SP.
> ALTER PROCEDURE [dbo].[g_pVendorUpdate]
> (@.VendorId [int],
> @.CompanyName [varchar](60),
> @.VendorTypeId [tinyint],
> @.AccountId [int],
> @.TaxId [varchar](30),
> @.ContactName [varchar](50),
> @.Phone [varchar](25),
> @.Fax [varchar](25),
> @.Email [varchar](100),
> @.Address [varchar](100),
> @.City [varchar](50),
> @.State [varchar](25),
> @.Zip [varchar](10),
> @.IsUtility [bit],
> @.Note [varchar](500))
> AS UPDATE [gVendor]
> SET [CompanyName] = @.CompanyName,
> [VendorTypeId] = @.VendorTypeId,
> [AccountId] = @.AccountId,
> [TaxId] = @.TaxId,
> [ContactName] = @.ContactName,
> [Phone] = @.Phone,
> [Fax] = @.Fax,
> [Email] = @.Email,
> [Address] = @.Address,
> [City] = @.City,
> [State] = @.State,
> [Zip] = @.Zip,
> [IsUtility] = @.IsUtility,
> [Note] = @.Note
> WHERE
> ( [VendorId] = @.VendorId)
> If I add IsActive=IsActive then it will work.
> Here is the table
> CREATE TABLE [dbo].[gVendor](
> [VendorId] [int] IDENTITY(1,1) NOT NULL,
> [CompanyName] [varchar](60) COLLATE Cyrillic_General_CI_AS NOT NULL,
> [VendorTypeId] [tinyint] NOT NULL CONSTRAINT [DF_gVendor_VendorTypeId]
> DEFAULT (0),
> [IsActive] [bit] NOT NULL CONSTRAINT [DF_gVendor_IsActive] DEFAULT (1),
> [AccountId] [int] NULL,
> [TaxId] [varchar](30) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_TaxId] DEFAULT (''),
> [ContactName] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL
> CONSTRAINT [DF_gVendor_ContactName] DEFAULT (''),
> [Phone] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_Phone] DEFAULT (''),
> [Fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> CONSTRAINT [DF_gVendor_Fax] DEFAULT (''),
> [Email] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_Email] DEFAULT (''),
> [Address] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL
> CONSTRAINT [DF_gVendor_Address] DEFAULT (''),
> [City] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_City] DEFAULT (''),
> [State] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_State] DEFAULT (''),
> [Zip] [char](10) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_Zip] DEFAULT (''),
> [Balance] [money] NOT NULL CONSTRAINT [DF_gVendor_Balance] DEFAULT (0),
> [IsUtility] [bit] NOT NULL,
> [Note] [varchar](500) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> [DF_gVendor_Memo] DEFAULT (''),
> CONSTRAINT [PK_gVendor] PRIMARY KEY CLUSTERED
> (
> [VendorId] ASC
> ) ON [PRIMARY],
> CONSTRAINT [unq_gVendorName] UNIQUE NONCLUSTERED
> (
> [CompanyName] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>|||This is the WORST code I have seen in ws!!
None of your data elements names comply with ISO-11179 standards. Can
you tell us why you violated them? You do known the baiscs of your
trade, don't you'
What is a G-vendor and why is he not like a regular vendor? Why did
you think that Dr. Codd is wrong and that IDENTITY can ever be a key'
Why do your vendors not have an industry standard code, like a DUNS
numbers? Did you know that the DEFAULT clause comes before the NOT
NULL constraint in Standard SQL? That money has screwed up math? Etc.
Why do you think that a data element name like "foobar_type_id" is
a meaningful name? How can a thing be both a type code and an
identifier? Why did you use BIT data types in an RDBMS -- were you an
assembly language programmer? Did you confuse 1950's files with the
relational model?
How did you get a phone number that is VARCHAR(25) when the
international standard is CHAR(15)? An address line of CHAR(100), when
the International Postal Union allows only CHAR(35)? The rest of your
DDL is un-researched and unusable.
I could not find a single column in your DDL that was right. Please
stop coding and get some help.|||> How did you get a phone number that is VARCHAR(25) when the
> international standard is CHAR(15)? An address line of CHAR(100), when
> the International Postal Union allows only CHAR(35)? The rest of your
> DDL is un-researched and unusable.
For our edification, where is this documented? I can not seem to find a
good source of such things.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138681735.224930.83760@.g43g2000cwa.googlegroups.com...
> This is the WORST code I have seen in ws!!
> None of your data elements names comply with ISO-11179 standards. Can
> you tell us why you violated them? You do known the baiscs of your
> trade, don't you'
> What is a G-vendor and why is he not like a regular vendor? Why did
> you think that Dr. Codd is wrong and that IDENTITY can ever be a key'
> Why do your vendors not have an industry standard code, like a DUNS
> numbers? Did you know that the DEFAULT clause comes before the NOT
> NULL constraint in Standard SQL? That money has screwed up math? Etc.
>
> Why do you think that a data element name like "foobar_type_id" is
> a meaningful name? How can a thing be both a type code and an
> identifier? Why did you use BIT data types in an RDBMS -- were you an
> assembly language programmer? Did you confuse 1950's files with the
> relational model?
> How did you get a phone number that is VARCHAR(25) when the
> international standard is CHAR(15)? An address line of CHAR(100), when
> the International Postal Union allows only CHAR(35)? The rest of your
> DDL is un-researched and unusable.
> I could not find a single column in your DDL that was right. Please
> stop coding and get some help.
>|||Hi Shimon,
Welcome to use MSDN Managed Newsgroup! This is Justin.
I checked the script of the table and SP but did not find anything abnormal.
If you run this SP in Query Analyzer, will it have the same problem? If so,
please check whether there is any update trigger defined on the table.
And please capture a Profiler trace when running the SP so that we could
know what is exactly going on behind the scene. Thus, we may able to find
the cause of this problem.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Shimon Sim" <shimonsim048@.community.nospam>
| Subject: Mistery with UPDATE statement
| Date: Mon, 30 Jan 2006 16:20:31 -0500
| Lines: 146
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| Message-ID: <uVrhBMeJGHA.964@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: ool-43530893.dyn.optonline.net 67.83.8.147
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.programming:577395
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| I have a table gVendor that has a field IsActive. It just shows if vendor
is
| active or not. When I update gVendor informatoin I leave IsActive out and
| hanlde it in an other SP.
| The problem is that eventhough I don't update that fields it resets to
false
| (0).
| I can't figure out why.
| Thank you.
| Shimon.
| Here is SP.
| ALTER PROCEDURE [dbo].[g_pVendorUpdate]
|
| (@.VendorId [int],
|
| @.CompanyName [varchar](60),
|
| @.VendorTypeId [tinyint],
|
| @.AccountId [int],
|
| @.TaxId [varchar](30),
|
| @.ContactName [varchar](50),
|
| @.Phone [varchar](25),
|
| @.Fax [varchar](25),
|
| @.Email [varchar](100),
|
| @.Address [varchar](100),
|
| @.City [varchar](50),
|
| @.State [varchar](25),
|
| @.Zip [varchar](10),
|
| @.IsUtility [bit],
|
| @.Note [varchar](500))
|
| AS UPDATE [gVendor]
|
| SET [CompanyName] = @.CompanyName,
|
| [VendorTypeId] = @.VendorTypeId,
|
| [AccountId] = @.AccountId,
|
| [TaxId] = @.TaxId,
|
| [ContactName] = @.ContactName,
|
| [Phone] = @.Phone,
|
| [Fax] = @.Fax,
|
| [Email] = @.Email,
|
| [Address] = @.Address,
|
| [City] = @.City,
|
| [State] = @.State,
|
| [Zip] = @.Zip,
|
| [IsUtility] = @.IsUtility,
|
| [Note] = @.Note
|
| WHERE
|
| ( [VendorId] = @.VendorId)
|
| If I add IsActive=IsActive then it will work.
|
| Here is the table
| CREATE TABLE [dbo].[gVendor](
|
| [VendorId] [int] IDENTITY(1,1) NOT NULL,
|
| [CompanyName] [varchar](60) COLLATE Cyrillic_General_CI_AS NOT NULL,
|
| [VendorTypeId] [tinyint] NOT NULL CONSTRAINT [DF_gVendor_VendorTypeId]
| DEFAULT (0),
|
| [IsActive] [bit] NOT NULL CONSTRAINT [DF_gVendor_IsActive] DEFAULT (1),
|
| [AccountId] [int] NULL,
|
| [TaxId] [varchar](30) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_TaxId] DEFAULT (''),
|
| [ContactName] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL
| CONSTRAINT [DF_gVendor_ContactName] DEFAULT (''),
|
| [Phone] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_Phone] DEFAULT (''),
|
| [Fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT
| [DF_gVendor_Fax] DEFAULT (''),
|
| [Email] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_Email] DEFAULT (''),
|
| [Address] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL
CONSTRAINT
| [DF_gVendor_Address] DEFAULT (''),
|
| [City] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_City] DEFAULT (''),
|
| [State] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_State] DEFAULT (''),
|
| [Zip] [char](10) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_Zip] DEFAULT (''),
|
| [Balance] [money] NOT NULL CONSTRAINT [DF_gVendor_Balance] DEFAULT (0),
|
| [IsUtility] [bit] NOT NULL,
|
| [Note] [varchar](500) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
| [DF_gVendor_Memo] DEFAULT (''),
|
| CONSTRAINT [PK_gVendor] PRIMARY KEY CLUSTERED
|
| (
|
| [VendorId] ASC
|
| ) ON [PRIMARY],
|
| CONSTRAINT [unq_gVendorName] UNIQUE NONCLUSTERED
|
| (
|
| [CompanyName] ASC
|
| ) ON [PRIMARY]
|
| ) ON [PRIMARY]
|
| GO
|
|
||||>> Why did
Oh dear, we still have not got it into your thick skull about surrogate KEYS
and the use of the IDENTITY property!
Stop holding people back, Codd's stuff is really great foundations but we
need to evolve as business and the industry evolves with different
challenges.
Stop using Doctorine! It might help the majority of your dated and
unworkable examples and bring your skill set up-to-date, we are no longer in
the 80's!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138681735.224930.83760@.g43g2000cwa.googlegroups.com...
> This is the WORST code I have seen in ws!!
> None of your data elements names comply with ISO-11179 standards. Can
> you tell us why you violated them? You do known the baiscs of your
> trade, don't you'
> What is a G-vendor and why is he not like a regular vendor? Why did
> you think that Dr. Codd is wrong and that IDENTITY can ever be a key'
> Why do your vendors not have an industry standard code, like a DUNS
> numbers? Did you know that the DEFAULT clause comes before the NOT
> NULL constraint in Standard SQL? That money has screwed up math? Etc.
>
> Why do you think that a data element name like "foobar_type_id" is
> a meaningful name? How can a thing be both a type code and an
> identifier? Why did you use BIT data types in an RDBMS -- were you an
> assembly language programmer? Did you confuse 1950's files with the
> relational model?
> How did you get a phone number that is VARCHAR(25) when the
> international standard is CHAR(15)? An address line of CHAR(100), when
> the International Postal Union allows only CHAR(35)? The rest of your
> DDL is un-researched and unusable.
> I could not find a single column in your DDL that was right. Please
> stop coding and get some help.
>|||Thanks for questions.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138681735.224930.83760@.g43g2000cwa.googlegroups.com...
> This is the WORST code I have seen in ws!!
> None of your data elements names comply with ISO-11179 standards. Can
> you tell us why you violated them? You do known the baiscs of your
> trade, don't you'
> What is a G-vendor and why is he not like a regular vendor?
The database that I was using was used for other application as well and in
order to avoid collision I used prefix. It is common this days you can check
any MS databases for application like ASPNET or BizTalkl Server(i think)
> Why did
> you think that Dr. Codd is wrong and that IDENTITY can ever be a key'
I never saw an article from Dr. Codd on this. The books that I used have
identity as a primary key.
> Why do your vendors not have an industry standard code, like a DUNS
> numbers?
Wasn't part of requirements.

> Did you know that the DEFAULT clause comes before the NOT
> NULL constraint in Standard SQL?
This is generated code from SSMS

> That money has screwed up math?
What'

> Etc.
>
> Why do you think that a data element name like "foobar_type_id" is
> a meaningful name?
again generated code.

> How can a thing be both a type code and an
> identifier? Why did you use BIT data types in an RDBMS -- were you an
> assembly language programmer?
BIT is a SQL Server data type.

> Did you confuse 1950's files with the
> relational model?
I wasn't born then.
> How did you get a phone number that is VARCHAR(25) when the
> international standard is CHAR(15)?
Client didn't care about standards. Needed to include extentions and extra
field I didn't want to manage. It was OK with them and me.
> An address line of CHAR(100), when
> the International Postal Union allows only CHAR(35)?
What company are you working for that you need to know all this stuff?
I didn't need. It doesn't affect size of database in any way. So what is a
problem?

> The rest of your
> DDL is un-researched and unusable.
That is good I need to answere less questions.
> I could not find a single column in your DDL that was right. Please
> stop coding and get some help.
>|||Thank you for your answer.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OC$t65hJGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Does it do this when you are in QA or SSMS and make sure it happens there.
> Then consider tracing your calls to see that something odd isn't taking
> place. Nothing here seems to look fishy, but trace a set of calls
> (including outputting values) and post it.
Yes. I ran the code on SSMS and got the problem again. I will try to post
results later.
But From all the posts it does seem that I am missing something simple.
I don't see any triggers execting in the trace in Profiler. Will they show
in there?

>
> Not sure that I understand this either. Is VendorId not the primary key?
> It says it is in the script. The only thing this should to is eliminate
> null rows. Since if IsActive is null then isActive=isActive is UNKNOWN,
> which is not TRUE, and any NOT TRUE rows are eliminated.
Yes the vendor is primary key.
I ment is it set part of code
UPDATE gVendor SET...
IsActive=IsActive
WHERE...
IsActive is never NULL it has default 1. But after update I get it 0.

> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "Shimon Sim" <shimonsim048@.community.nospam> wrote in message
> news:uVrhBMeJGHA.964@.tk2msftngp13.phx.gbl...
>|||"Justin Shen[MSFT]" <v-yishen@.online.microsoft.com> wrote in message
news:W0EwMTiJGHA.3944@.TK2MSFTNGXA02.phx.gbl...
> Hi Shimon,
> Welcome to use MSDN Managed Newsgroup! This is Justin.
> I checked the script of the table and SP but did not find anything
> abnormal.
> If you run this SP in Query Analyzer, will it have the same problem?
Yes.
>If so,
> please check whether there is any update trigger defined on the table.
I don't see any. But if there are any trigggers would the show in Profiler
trace?

> And please capture a Profiler trace when running the SP so that we could
> know what is exactly going on behind the scene. Thus, we may able to find
> the cause of this problem.
> If you have any question, please feel free to let me know.
> Thanks & Regards,
> Justin Shen
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a w to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others: https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Shimon Sim" <shimonsim048@.community.nospam>
> | Subject: Mistery with UPDATE statement
> | Date: Mon, 30 Jan 2006 16:20:31 -0500
> | Lines: 146
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <uVrhBMeJGHA.964@.tk2msftngp13.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.programming
> | NNTP-Posting-Host: ool-43530893.dyn.optonline.net 67.83.8.147
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.programming:577395
> | X-Tomcat-NG: microsoft.public.sqlserver.programming
> |
> | I have a table gVendor that has a field IsActive. It just shows if
> vendor
> is
> | active or not. When I update gVendor informatoin I leave IsActive out
> and
> | hanlde it in an other SP.
> | The problem is that eventhough I don't update that fields it resets to
> false
> | (0).
> | I can't figure out why.
> | Thank you.
> | Shimon.
> | Here is SP.
> | ALTER PROCEDURE [dbo].[g_pVendorUpdate]
> |
> | (@.VendorId [int],
> |
> | @.CompanyName [varchar](60),
> |
> | @.VendorTypeId [tinyint],
> |
> | @.AccountId [int],
> |
> | @.TaxId [varchar](30),
> |
> | @.ContactName [varchar](50),
> |
> | @.Phone [varchar](25),
> |
> | @.Fax [varchar](25),
> |
> | @.Email [varchar](100),
> |
> | @.Address [varchar](100),
> |
> | @.City [varchar](50),
> |
> | @.State [varchar](25),
> |
> | @.Zip [varchar](10),
> |
> | @.IsUtility [bit],
> |
> | @.Note [varchar](500))
> |
> | AS UPDATE [gVendor]
> |
> | SET [CompanyName] = @.CompanyName,
> |
> | [VendorTypeId] = @.VendorTypeId,
> |
> | [AccountId] = @.AccountId,
> |
> | [TaxId] = @.TaxId,
> |
> | [ContactName] = @.ContactName,
> |
> | [Phone] = @.Phone,
> |
> | [Fax] = @.Fax,
> |
> | [Email] = @.Email,
> |
> | [Address] = @.Address,
> |
> | [City] = @.City,
> |
> | [State] = @.State,
> |
> | [Zip] = @.Zip,
> |
> | [IsUtility] = @.IsUtility,
> |
> | [Note] = @.Note
> |
> | WHERE
> |
> | ( [VendorId] = @.VendorId)
> |
> | If I add IsActive=IsActive then it will work.
> |
> | Here is the table
> | CREATE TABLE [dbo].[gVendor](
> |
> | [VendorId] [int] IDENTITY(1,1) NOT NULL,
> |
> | [CompanyName] [varchar](60) COLLATE Cyrillic_General_CI_AS NOT NULL,
> |
> | [VendorTypeId] [tinyint] NOT NULL CONSTRAINT [DF_gVendor_VendorTypeId]
> | DEFAULT (0),
> |
> | [IsActive] [bit] NOT NULL CONSTRAINT [DF_gVendor_IsActive] DEFAULT (1),
> |
> | [AccountId] [int] NULL,
> |
> | [TaxId] [varchar](30) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_TaxId] DEFAULT (''),
> |
> | [ContactName] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL
> | CONSTRAINT [DF_gVendor_ContactName] DEFAULT (''),
> |
> | [Phone] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_Phone] DEFAULT (''),
> |
> | [Fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> CONSTRAINT
> | [DF_gVendor_Fax] DEFAULT (''),
> |
> | [Email] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL
> CONSTRAINT
> | [DF_gVendor_Email] DEFAULT (''),
> |
> | [Address] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL
> CONSTRAINT
> | [DF_gVendor_Address] DEFAULT (''),
> |
> | [City] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_City] DEFAULT (''),
> |
> | [State] [varchar](25) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_State] DEFAULT (''),
> |
> | [Zip] [char](10) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_Zip] DEFAULT (''),
> |
> | [Balance] [money] NOT NULL CONSTRAINT [DF_gVendor_Balance] DEFAULT (0),
> |
> | [IsUtility] [bit] NOT NULL,
> |
> | [Note] [varchar](500) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT
> | [DF_gVendor_Memo] DEFAULT (''),
> |
> | CONSTRAINT [PK_gVendor] PRIMARY KEY CLUSTERED
> |
> | (
> |
> | [VendorId] ASC
> |
> | ) ON [PRIMARY],
> |
> | CONSTRAINT [unq_gVendorName] UNIQUE NONCLUSTERED
> |
> | (
> |
> | [CompanyName] ASC
> |
> | ) ON [PRIMARY]
> |
> | ) ON [PRIMARY]
> |
> | GO
> |
> |
> |
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138681735.224930.83760@.g43g2000cwa.googlegroups.com...
> This is the WORST code I have seen in ws!!
>
You post this same insult twice a day.
Have you ever tried constructive criticism?
I'll refer you to Dale Carnegie, who, although often a bit touchy-feely for
my taste, is the undisputed authority on the topic.

Mission Impossible ? Pivot Table at front end Excel

I have a table with this field headers Product/Status/USD:-

A/Actual/100
A/Budget/90
A/Variance/10

I have created a Pivot Table in Excel with
Product as row field (A or B)
Status as Column field (Actual, Budget, Variance)
USD as Value

It looks fine like this but I need to create a column called Variance % which is (Variance/Budget x 100%).

Please urgently advise how to create this new % column within Pivot Table.

Thanks. Sorry it seems a bit more about Excel but can't get answers in Excel forum...

I am not using SQL Server.

help...

Sorry, but I think your chances better off with trying Excel forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Missing Values

Hi,
Can anyone help me with a query?
Let say I have a table called tblTest with one int field called NumOfCall.
NumOfCall hold numbers in sequential order but some are missing. for
example the table has 100 records 1 - 105 and 4, 19, 32, 46, 86 are missing.
I need a query that will tell me what values are missing.
Thanks
FredFrinton wrote:
> Hi,
> Can anyone help me with a query?
> Let say I have a table called tblTest with one int field called NumOfCall.
> NumOfCall hold numbers in sequential order but some are missing. for
> example the table has 100 records 1 - 105 and 4, 19, 32, 46, 86 are missing.
> I need a query that will tell me what values are missing.
> Thanks
> Fred
Use a table (Numbers) that contains all the potential numbers you want
to look for:
SELECT num
FROM Numbers AS N
WHERE NOT EXISTS
(SELECT *
FROM tblTest
WHERE numofcall = N.num)
AND N.num BETWEEN 1 AND 100 ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Another option is to use:
SELECT col + 1
FROM tbl
WHERE col < ( SELECT MAX( col ) FROM tbl )
AND NOT EXISTS( SELECT *
FROM tbl t1
WHERE t1.col = tbl.col + 1 ) ;
If you have series of missing numbers do:
SELECT start + 1, end - 1
FROM ( SELECT t1.col, MIN( t2.col )
FROM tbl t1
INNER JOIN tbl t2
WHERE t1.col < t2.col
GROUP BY t1.col ) D ( start, end )
WHERE start < end - 1 ;
--
Anith

Missing Values

Hi,
Can anyone help me with a query?
Let say I have a table called tblTest with one int field called NumOfCall.
NumOfCall hold numbers in sequential order but some are missing. for
example the table has 100 records 1 - 105 and 4, 19, 32, 46, 86 are missing.
I need a query that will tell me what values are missing.
Thanks
Fred
Frinton wrote:
> Hi,
> Can anyone help me with a query?
> Let say I have a table called tblTest with one int field called NumOfCall.
> NumOfCall hold numbers in sequential order but some are missing. for
> example the table has 100 records 1 - 105 and 4, 19, 32, 46, 86 are missing.
> I need a query that will tell me what values are missing.
> Thanks
> Fred
Use a table (Numbers) that contains all the potential numbers you want
to look for:
SELECT num
FROM Numbers AS N
WHERE NOT EXISTS
(SELECT *
FROM tblTest
WHERE numofcall = N.num)
AND N.num BETWEEN 1 AND 100 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Another option is to use:
SELECT col + 1
FROM tbl
WHERE col < ( SELECT MAX( col ) FROM tbl )
AND NOT EXISTS( SELECT *
FROM tbl t1
WHERE t1.col = tbl.col + 1 ) ;
If you have series of missing numbers do:
SELECT start + 1, end - 1
FROM ( SELECT t1.col, MIN( t2.col )
FROM tbl t1
INNER JOIN tbl t2
WHERE t1.col < t2.col
GROUP BY t1.col ) D ( start, end )
WHERE start < end - 1 ;
Anith

Missing Values

Hi,
Can anyone help me with a query?
Let say I have a table called tblTest with one int field called NumOfCall.
NumOfCall hold numbers in sequential order but some are missing. for
example the table has 100 records 1 - 105 and 4, 19, 32, 46, 86 are missing.
I need a query that will tell me what values are missing.
Thanks
FredFrinton wrote:
> Hi,
> Can anyone help me with a query?
> Let say I have a table called tblTest with one int field called NumOfCall.
> NumOfCall hold numbers in sequential order but some are missing. for
> example the table has 100 records 1 - 105 and 4, 19, 32, 46, 86 are missin
g.
> I need a query that will tell me what values are missing.
> Thanks
> Fred
Use a table (Numbers) that contains all the potential numbers you want
to look for:
SELECT num
FROM Numbers AS N
WHERE NOT EXISTS
(SELECT *
FROM tblTest
WHERE numofcall = N.num)
AND N.num BETWEEN 1 AND 100 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Another option is to use:
SELECT col + 1
FROM tbl
WHERE col < ( SELECT MAX( col ) FROM tbl )
AND NOT EXISTS( SELECT *
FROM tbl t1
WHERE t1.col = tbl.col + 1 ) ;
If you have series of missing numbers do:
SELECT start + 1, end - 1
FROM ( SELECT t1.col, MIN( t2.col )
FROM tbl t1
INNER JOIN tbl t2
WHERE t1.col < t2.col
GROUP BY t1.col ) D ( start, end )
WHERE start < end - 1 ;
Anith

Missing Fields

I am calling a stored procedure from reporting services. The problem I'm
having is that it's only returning the first field in the result set in the
field list.
When I query under the data tab, data and field names are there and all data
is returned successfully.
I have tried refreshing the fieds, refreshing data, creating a new
report...you name it.
I have used both the sql server and ole db providers and both produce the
same behavoir.
The stored procedure has 5 parameters. I have tried both temp tables and
table variables in the stored procedure to no avail.
When I manually add the fields I get errors as well.
Can anyone provide any help or insight? I'm stumped on this one.
Thanks!OK...I think I have this figured out, although I'm not really happy about the
behavoir. Perhaps MS can put this on their list of issues.
It appears that if you use a stored procedure within another stored
procedure (say the one your calling to populate your report) , it throws off
the fields that are returned.
In my case, I have a stored procedure internal to the one I was calling that
figures out the current date. The return value was the only field being
returned within the reporting services .net interface.
Perhaps it's the way I'm calling the SP and assigning the return value to an
internal variable.
Nonetheless...I hope this helps. Hopefully someone from MS will comment on
this behavoir for us.
"Scott M" wrote:
> I am calling a stored procedure from reporting services. The problem I'm
> having is that it's only returning the first field in the result set in the
> field list.
> When I query under the data tab, data and field names are there and all data
> is returned successfully.
> I have tried refreshing the fieds, refreshing data, creating a new
> report...you name it.
> I have used both the sql server and ole db providers and both produce the
> same behavoir.
>
> The stored procedure has 5 parameters. I have tried both temp tables and
> table variables in the stored procedure to no avail.
>
> When I manually add the fields I get errors as well.
> Can anyone provide any help or insight? I'm stumped on this one.
> Thanks!|||Can you duplicate this behavior with either adventureworks2000 or northwind.
I would like to investigate this and see if there is a workaround.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott M" <ScottM@.discussions.microsoft.com> wrote in message
news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> OK...I think I have this figured out, although I'm not really happy about
the
> behavoir. Perhaps MS can put this on their list of issues.
> It appears that if you use a stored procedure within another stored
> procedure (say the one your calling to populate your report) , it throws
off
> the fields that are returned.
> In my case, I have a stored procedure internal to the one I was calling
that
> figures out the current date. The return value was the only field being
> returned within the reporting services .net interface.
> Perhaps it's the way I'm calling the SP and assigning the return value to
an
> internal variable.
> Nonetheless...I hope this helps. Hopefully someone from MS will comment
on
> this behavoir for us.
> "Scott M" wrote:
> > I am calling a stored procedure from reporting services. The problem
I'm
> > having is that it's only returning the first field in the result set in
the
> > field list.
> >
> > When I query under the data tab, data and field names are there and all
data
> > is returned successfully.
> >
> > I have tried refreshing the fieds, refreshing data, creating a new
> > report...you name it.
> >
> > I have used both the sql server and ole db providers and both produce
the
> > same behavoir.
> >
> >
> > The stored procedure has 5 parameters. I have tried both temp tables
and
> > table variables in the stored procedure to no avail.
> >
> >
> > When I manually add the fields I get errors as well.
> >
> > Can anyone provide any help or insight? I'm stumped on this one.
> >
> > Thanks!|||Bruce,
I don't have those databases installed here at work...but I can give you the
essence of what happened.
There was a stored procedure called within a wrapper stored procedure. The
internal stored procedure was written to return the current period code
(200408, 200409 etc).
The wrapper stored procedure returned a recordset based on the period code
that was returned in the internal stored procedure.
When the report executed the wrapper stored procedure, it was only returning
the record from the internal stored procedure. So all I got was one field
with a value of the current period code.
I resolved the issue by creating a function to return the period code. That
seemed to fix it.
There might be a better way to call an internal stored procedure than the
way this was used. It was assigning an internally declared variable within
the wrapper stored procedure to the output parameter on the stored procedure
that returns the current period code.
Not sure if that helps. Seems like a bug to me, but perhaps it's by design
for some strange reason. Nonetheless, I'll have to re-write a lot of SP's in
order to utilize Reporting Services since the developer who originally
created a lot of the procedures in our environment didn't seem to utilize
functions.
Thanks for your response. Let me know if I can provide any further insight.
Scott
"Bruce L-C [MVP]" wrote:
> Can you duplicate this behavior with either adventureworks2000 or northwind.
> I would like to investigate this and see if there is a workaround.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott M" <ScottM@.discussions.microsoft.com> wrote in message
> news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> > OK...I think I have this figured out, although I'm not really happy about
> the
> > behavoir. Perhaps MS can put this on their list of issues.
> >
> > It appears that if you use a stored procedure within another stored
> > procedure (say the one your calling to populate your report) , it throws
> off
> > the fields that are returned.
> >
> > In my case, I have a stored procedure internal to the one I was calling
> that
> > figures out the current date. The return value was the only field being
> > returned within the reporting services .net interface.
> >
> > Perhaps it's the way I'm calling the SP and assigning the return value to
> an
> > internal variable.
> >
> > Nonetheless...I hope this helps. Hopefully someone from MS will comment
> on
> > this behavoir for us.
> >
> > "Scott M" wrote:
> >
> > > I am calling a stored procedure from reporting services. The problem
> I'm
> > > having is that it's only returning the first field in the result set in
> the
> > > field list.
> > >
> > > When I query under the data tab, data and field names are there and all
> data
> > > is returned successfully.
> > >
> > > I have tried refreshing the fieds, refreshing data, creating a new
> > > report...you name it.
> > >
> > > I have used both the sql server and ole db providers and both produce
> the
> > > same behavoir.
> > >
> > >
> > > The stored procedure has 5 parameters. I have tried both temp tables
> and
> > > table variables in the stored procedure to no avail.
> > >
> > >
> > > When I manually add the fields I get errors as well.
> > >
> > > Can anyone provide any help or insight? I'm stumped on this one.
> > >
> > > Thanks!
>
>|||I've encountered the same problem. I've created about 50 reports based on
stored procedures and this is the first time this has occured.
The proc in question has 11 parameters. It returns the result set in the
Data Preview section, but only returns a single field in the field list. The
field returned is not even in the actual result set.
The proc I'm calling does call other procs. It was created bby a third party
and is used for other functions as well as the report I'm writing, so I can't
modify it.
Any ideas?
"Bruce L-C [MVP]" wrote:
> Can you duplicate this behavior with either adventureworks2000 or northwind.
> I would like to investigate this and see if there is a workaround.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott M" <ScottM@.discussions.microsoft.com> wrote in message
> news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> > OK...I think I have this figured out, although I'm not really happy about
> the
> > behavoir. Perhaps MS can put this on their list of issues.
> >
> > It appears that if you use a stored procedure within another stored
> > procedure (say the one your calling to populate your report) , it throws
> off
> > the fields that are returned.
> >
> > In my case, I have a stored procedure internal to the one I was calling
> that
> > figures out the current date. The return value was the only field being
> > returned within the reporting services .net interface.
> >
> > Perhaps it's the way I'm calling the SP and assigning the return value to
> an
> > internal variable.
> >
> > Nonetheless...I hope this helps. Hopefully someone from MS will comment
> on
> > this behavoir for us.
> >
> > "Scott M" wrote:
> >
> > > I am calling a stored procedure from reporting services. The problem
> I'm
> > > having is that it's only returning the first field in the result set in
> the
> > > field list.
> > >
> > > When I query under the data tab, data and field names are there and all
> data
> > > is returned successfully.
> > >
> > > I have tried refreshing the fieds, refreshing data, creating a new
> > > report...you name it.
> > >
> > > I have used both the sql server and ole db providers and both produce
> the
> > > same behavoir.
> > >
> > >
> > > The stored procedure has 5 parameters. I have tried both temp tables
> and
> > > table variables in the stored procedure to no avail.
> > >
> > >
> > > When I manually add the fields I get errors as well.
> > >
> > > Can anyone provide any help or insight? I'm stumped on this one.
> > >
> > > Thanks!
>
>