Field Selection

I'm pretty new to Crystal and I'm stumped at the following issue:

I need to select a sub-set of a dataset. In this case I need to select fields with a status of "Requested", from a list of fields that include "Accepted", "Requested", "Declined". This part is easy, but it gets complicated (for me):

Then I have to use those fields filtered by "Requested" to find, within the last 180 days, if any clients have "Accepted" and/or "Requested" and/or "Declined" (i.e. status = any).

This is a two staged selection process, and I'm fine with the code for the 180 days selection. It almost seems to be a contradiction in that I need to filter by "Requested" in the first intance, and in the second I need to then show returns (for all Status types), but the initial filter means I can not do this.

Another way of looking at it is:

1. I want to select X1 from X(1,2,3,4,5), then
2. I want to use X1 to determine if there have been cases of X(1,2,3,4,5) in the last 6 months.

Any help is muchly appreciated.

Cheers.

Mat.any ideas? I'm desperate.

Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are not
> directly dependent, just somewhat later the log begins growing, but no error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
> > - In the meanwhile it appears that DB log begins growing: the things are
> > not
> > directly dependent, just somewhat later the log begins growing, but no
> > error
> > is found in SQL errorlog.
> > - In which way may the two things (overflow and log grow) be related?
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
> > - What really happens on SQL server when data overflow occurs? How does
it
> > handle?
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?

Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.

> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set

> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30)
)
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30)
)
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:

> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are n
ot
> directly dependent, just somewhat later the log begins growing, but no err
or
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written t
o
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
>
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
it[vbcol=seagreen]
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?

Field Order

I am new to SQL so keep asking questions. My apologies if they are dumb.
Does it make any difference to the performance or otherwise of SQL if I
arrange the fields in one particular order in my table, and then input them
in another?
For example, my table is Field 1, Field 2, Field 3 etc., but I might want to
input Field 3 first, then Field 1, then Field 2.
Thanks
On Mon, 5 Apr 2004 11:55:48 +0100, "Keith" <@..> wrote:

>I am new to SQL so keep asking questions. My apologies if they are dumb.
There are no dumb questions. Not asking - that is dumb.

>Does it make any difference to the performance or otherwise of SQL if I
>arrange the fields in one particular order in my table, and then input them
>in another?
No, not at all.
(Sole exception - if you write an insert statement supplying data for
ALL columns in the same order as they are defined, you are allowed to
leave out the column list in the insert statement - but that is
definitely not recommended practice, since it will produce errors as
soon as someone or something changes the columns' order)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Field Order

I am new to SQL so keep asking questions. My apologies if they are dumb.
Does it make any difference to the performance or otherwise of SQL if I
arrange the fields in one particular order in my table, and then input them
in another?
For example, my table is Field 1, Field 2, Field 3 etc., but I might want to
input Field 3 first, then Field 1, then Field 2.
ThanksOn Mon, 5 Apr 2004 11:55:48 +0100, "Keith" <@..> wrote:

>I am new to SQL so keep asking questions. My apologies if they are dumb.
There are no dumb questions. Not asking - that is dumb.

>Does it make any difference to the performance or otherwise of SQL if I
>arrange the fields in one particular order in my table, and then input them
>in another?
No, not at all.
(Sole exception - if you write an insert statement supplying data for
ALL columns in the same order as they are defined, you are allowed to
leave out the column list in the insert statement - but that is
definitely not recommended practice, since it will produce errors as
soon as someone or something changes the columns' order)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:78g270t8umrbpp1in6r6ihk04ebichk8ng@.
4ax.com...
> On Mon, 5 Apr 2004 11:55:48 +0100, "Keith" <@..> wrote:
>
> There are no dumb questions. Not asking - that is dumb.
>
them
> No, not at all.
> (Sole exception - if you write an insert statement supplying data for
> ALL columns in the same order as they are defined, you are allowed to
> leave out the column list in the insert statement - but that is
> definitely not recommended practice, since it will produce errors as
> soon as someone or something changes the columns' order)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Field ntext in the table only stores 256 characters

declare @.mensagem varchar(8000)
CREATE TABLE #Mensagem (mensagem text)
set @.mensagem = 'Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa dsfsdfsdf sdfsdfsdf 111111111 00 '
insert into #Mensagem select @.mensagem
select * from #MensagemHi Frank.
I ran that under SQL2KEE & it produced a perfect result..
The Query Analyser truncates column output to 256 characters by default, so
try setting your "Maximum characters per column" to something higher (eg
8000) under Query Analyser's "Tools/Options" menu, "Results" tab.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:#ZTWHAgjDHA.2592@.TK2MSFTNGP10.phx.gbl...
>
>
> declare @.mensagem varchar(8000)
> CREATE TABLE #Mensagem (mensagem text)
> set @.mensagem =>
'Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
>
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa dsfsdfsdf sdfsdfsdf 111111111 00 '
> insert into #Mensagem select @.mensagem
> select * from #Mensagem
>

Field not sorting in ascending order

Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance

That's because your field is not numeric, if it were numeric, you would expect 61,100,200

but, with text, it looks at MP with a '1' and sees it first, then, MP with a '2' and sees it next - - naturally 6 is after 2 - - but that's the reason.

|||

It is possible to get text to behave a bit like numbers.

If you had stored this instead, it would sort correctly:

MP061, MP100, MP200

This example presumes that the numerical component will always be no more than 3 characters and all 3 postiions are identified for each record (even if they are zero). In general, it's better not to try to sort alpha-numeric data in a numerical sort order.

|||If all of your records have a 2-character prefix in that column, andthere are only numeric characters that follow, you can use thisapproach to solve your sorting problem:

SELECT
someColumns
FROM
someTable
ORDER BY
CAST(SUBSTRING(mpColumn,3,99) AS integer),
mpColumn

Field Not Recognized In Dataset

I have a dataset that I was using in a report that I determined I needed to
add a field to. After I changed the query to include this field, the field
is not displaying as part of the dataset when I look in the field browser or
try to use the field in the report. The field I have added is "Quota AS
SalesTarget". Quota is a field on the table, but for some reason in the
dataset it is not being recognized. If I run the query in Enterprise
Manager, it runs fine displaying the column. Any suggestions would be
appreciated. Here is the query:
="SELECT CorpEntityCode AS CFPBusinessUnitCode,
CorpEntity AS CFPBusinessUnitCodename,
9 AS SalesStageCode,
'Remaining Quota' AS SalesStageCodename,
Quota AS SalesTarget,
Quota - (SELECT sum(EstimatedValue) FROM Opportunity o WHERE ((StateCode = 1) AND (EstimatedValue >= 2000000.00) AND (EstimatedCloseDate >= DATEADD(yy,
DATEDIFF(yy, 0, GETDATE()), 0)) AND (DeletionStateCode = 0)" &
IIf(Parameters!BUSelect.Value = 0, "", " AND (CFPBusinessUnitCode = " &
Parameters!BUSelect.Value & ")") & ")) AS SalesStageValue
FROM CustomSalesQuota
WHERE (CorpEntityCode = " & Parameters!BUSelect.Value & ")
UNION
SELECT CFPBusinessUnitCode,
CASE CFPBusinessUnitCode WHEN 1 THEN 'Commercial Solutions' WHEN 2 THEN
'Europe' WHEN 3 THEN 'Government Services' WHEN 4 THEN 'Healthcare' WHEN 5
THEN 'Technology Solutions' END AS CFPBusinessUnitCodename, SalesStageCode,
CASE SalesStageCode WHEN 2 THEN 'Open' WHEN 3 THEN 'Develop' WHEN 4 THEN
'Scope/Propose' WHEN 5 THEN 'Negotiate/Close' WHEN 6 THEN 'Won' END AS
SalesStageCodename,
0.00 AS SalesTarget,
Sum(EstimatedValue) AS SalesStageValue
FROM dbo.Opportunity
WHERE ((StateCode = 0) AND (SalesStageCode IN (3, 4, 5)) AND (EstimatedValue
>= 2000000.00) AND (EstimatedCloseDate >= DATEADD(yy, DATEDIFF(yy, 0,
GETDATE()), 0)) AND (DeletionStateCode = 0) OR (StateCode = 1) AND
(EstimatedValue >= 2000000.00) AND (EstimatedCloseDate >= DATEADD(yy,
DATEDIFF(yy, 0, GETDATE()), 0)) AND (DeletionStateCode = 0))" &
IIf(Parameters!BUSelect.Value = 0, "", " AND (CFPBusinessUnitCode = " &
Parameters!BUSelect.Value & ")") & " GROUP BY CFPBusinessUnitCode,
SalesStageCode"
ThanksGo to the Data Tab, and click the refresh Fields button... or re-execute
the query from the data tab...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CaskeyRW" <CaskeyRW@.discussions.microsoft.com> wrote in message
news:5ABCD385-BBCB-4A55-823F-F33507B1EEBB@.microsoft.com...
>I have a dataset that I was using in a report that I determined I needed to
> add a field to. After I changed the query to include this field, the
> field
> is not displaying as part of the dataset when I look in the field browser
> or
> try to use the field in the report. The field I have added is "Quota AS
> SalesTarget". Quota is a field on the table, but for some reason in the
> dataset it is not being recognized. If I run the query in Enterprise
> Manager, it runs fine displaying the column. Any suggestions would be
> appreciated. Here is the query:
> ="SELECT CorpEntityCode AS CFPBusinessUnitCode,
> CorpEntity AS CFPBusinessUnitCodename,
> 9 AS SalesStageCode,
> 'Remaining Quota' AS SalesStageCodename,
> Quota AS SalesTarget,
> Quota - (SELECT sum(EstimatedValue) FROM Opportunity o WHERE ((StateCode => 1) AND (EstimatedValue >= 2000000.00) AND (EstimatedCloseDate >=> DATEADD(yy,
> DATEDIFF(yy, 0, GETDATE()), 0)) AND (DeletionStateCode = 0)" &
> IIf(Parameters!BUSelect.Value = 0, "", " AND (CFPBusinessUnitCode = " &
> Parameters!BUSelect.Value & ")") & ")) AS SalesStageValue
> FROM CustomSalesQuota
> WHERE (CorpEntityCode = " & Parameters!BUSelect.Value & ")
> UNION
> SELECT CFPBusinessUnitCode,
> CASE CFPBusinessUnitCode WHEN 1 THEN 'Commercial Solutions' WHEN 2 THEN
> 'Europe' WHEN 3 THEN 'Government Services' WHEN 4 THEN 'Healthcare' WHEN 5
> THEN 'Technology Solutions' END AS CFPBusinessUnitCodename,
> SalesStageCode,
> CASE SalesStageCode WHEN 2 THEN 'Open' WHEN 3 THEN 'Develop' WHEN 4 THEN
> 'Scope/Propose' WHEN 5 THEN 'Negotiate/Close' WHEN 6 THEN 'Won' END AS
> SalesStageCodename,
> 0.00 AS SalesTarget,
> Sum(EstimatedValue) AS SalesStageValue
> FROM dbo.Opportunity
> WHERE ((StateCode = 0) AND (SalesStageCode IN (3, 4, 5)) AND
> (EstimatedValue
>>= 2000000.00) AND (EstimatedCloseDate >= DATEADD(yy, DATEDIFF(yy, 0,
> GETDATE()), 0)) AND (DeletionStateCode = 0) OR (StateCode = 1) AND
> (EstimatedValue >= 2000000.00) AND (EstimatedCloseDate >= DATEADD(yy,
> DATEDIFF(yy, 0, GETDATE()), 0)) AND (DeletionStateCode = 0))" &
> IIf(Parameters!BUSelect.Value = 0, "", " AND (CFPBusinessUnitCode = " &
> Parameters!BUSelect.Value & ")") & " GROUP BY CFPBusinessUnitCode,
> SalesStageCode"
> Thankssql

Field not found: 'System.Collections.Generic.KeyValuePair`2.Value'

I get a Field not found: 'System.Collections.Generic.KeyValuePair`2.Value' when i try to open up a table in SQL Server 2005 Managment Studio. I have the June CTP of SQL 2005 and I have the July CTP of Visual Studio 2005 Professional. Can anyone help me cause i'm bout to lose my mind. I'm hoping to find the real problem because i'm thinking that this error message does not lead to exactly the problem. I would appreciate any help that you all can give me.
Thanks,

MatthewHello Mulktide!
I encountered the same error. Did you manage to solve the problem?

Thanks.
pax

field not found!

Hello
We have made a database installed it on MS SQL server version 8.0.0.760 (SP3) running on Win NT 4.0 (SP6a) danish. The database is accessed from a program made with delphi 6, and it uses BDE.
The problem is when we start the program, it says :
Field 'V=E6rdi' not found.
This field is accessed by a view called Xsystem:
which contain: select * from Xtable.
This problem is not found on another PC runnig Windows 2000 (sp3). On this PC it work fine.
Any Help pleaseHi,
TRY FOR TEST
CREATE VIEW TESTVIEW AS
SELECT "Værdi" AS Vaerdi from Xtable
>We have made a database installed it on MS SQL server
>version 8.0.0.760 (SP3) running on Win NT 4.0 (SP6a)
>danish. The database is accessed from a program made with
>delphi 6, and it uses BDE.
Check version of Middlewares
MDAC
BDE
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003|||Thank you Mr. SHINICHI YONEDA for your reply.
I will try this.
>--Original Message--
>Hi,
> TRY FOR TEST
>CREATE VIEW TESTVIEW AS
>SELECT "V=E6rdi" AS Vaerdi from Xtable
>>We have made a database installed it on MS SQL server
>>version 8.0.0.760 (SP3) running on Win NT 4.0 (SP6a)
>>danish. The database is accessed from a program made with
>>delphi 6, and it uses BDE.
> Check version of Middlewares
>MDAC
>BDE
>--
>SHINICHI YONEDA MXL04371@.nifty.ne.jp
>Microsoft Most Valuable Professional
>MVP for SQL Server 2002-2003
>.
>

Field not being updated within Stored Procedure

I have this stored procedure that loops through a table and updates a
couple of fields. For some reason one of the fields is not being
updated. If I run the same code from query analyzer, it works fine.
Let me know if anyone can figure out why @.lastscandate would ever be
NULL. If it is null it should be equal to @.maildate. The senerio that
seems to fail is when no records are returned from the select statement
to fill in @.lastscandate. This should then active the next if
statement and set the @.lastscandate equal to the @.maildate. MailDate
is always filled in in the database and LastScanDate will be NULL.

Thanks for your help.

DECLARE c1 CURSOR LOCAL FOR
SELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid
= 0) AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

OPEN c1
FETCH FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

WHILE @.@.fetch_status = 0
BEGIN

--Get MailDate from Manifest - if NULL then use GetDate
set @.maildate = NULL
SELECT @.maildate = MAX(whenmailed) FROM manifest WITH (nolock)
WHERE acctno = @.acctno AND ordid = @.ordid AND cycle = @.cycle
if @.maildate is NULL
set @.maildate = getdate()

--Get Last Scan Date from Transactions - if NULL then use MailDate
set @.lastscandate = NULL
select @.lastscandate=max(actiondate) from transactions where
acctno=@.acctno and ordid=@.ordid and cycle=@.cycle and actionid=303
if @.lastscandate is NULL
set @.lastscandate = @.maildate

BEGIN TRANSACTION
UPDATE master SET printstatus = 'ML', maildate = @.maildate,
lastscandate=@.lastscandate
WHERE id = @.mid

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @.acctno, @.ordid,
@.cycle, 'Update Mail Dates')
COMMIT TRANSACTION

FETCH NEXT FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

END

CLOSE c1[posted and mailed, please reply in news]

AS400 Guru (hazen@.candid.com) writes:
> I have this stored procedure that loops through a table and updates a
> couple of fields. For some reason one of the fields is not being
> updated. If I run the same code from query analyzer, it works fine.
> Let me know if anyone can figure out why @.lastscandate would ever be
> NULL. If it is null it should be equal to @.maildate. The senerio that
> seems to fail is when no records are returned from the select statement
> to fill in @.lastscandate. This should then active the next if
> statement and set the @.lastscandate equal to the @.maildate. MailDate
> is always filled in in the database and LastScanDate will be NULL.

I don't immediately see why, but I don't have the tables, so it's
difficult to debug. Since you insert into transactions and read from
it, in the same cursor, there could be some funny things.

However, I would suggest that you should rewrite as a one UPDATE
statment and one INSERT Statement. For simplicty I use a temp table
though:

INSERT #temp (...)
SELECT m.id, m.acctno, m.ordid, m.cycle
FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML')
AND (m.batchid IS NULL OR m.batchid >= 0)
AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno
AND m.ordid = p.ordid
AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

UPDATE master
SET printstatus = 'ML',
maildate = coalesce(mf.whenmailed, getdate(),
lastscandate = coalesce(tr.actiondate, mf.whenmailed, getdate())
FROM #temp t
JOIN master ma ON t.mid = ma.mid
JOIN (SELECT accno, ordid, cycle, whenmailed = MAX(whenmailed)
FROM manifest
GROUP BY accno, ordid, cycle) mf ON mf.ordid = t.ordid
AND mf.accntno = t.acctno
AND mf.cycle = t.cycle
JOIN (SELECT accno, ordid, cycle, actiondate = MAX(actiondate)
FROM transactions
WHERE actionid = 303
GROUP BY accno, ordid, cycle) tr ON tr.ordid = t.ordid
AND tr.accntno = t.acctno
AND tr.cycle = t.cycle

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program)
SELECT 'RLT', 55, 'Mars', acctno, ordid, cycle, 'Update Mail Dates'
FROM #temp

COMMIT TRANSACTION

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Field Names that contain the Pound (#) symbol

I'm trying to generate some reports from our AS400 mainframe using OLE
DB. The problem is some of the fields have a name like josel#. For
some reason, the Report builder in VS.net 2003 adds a space between
the # and the characters. I've tried adding [] around the field but
the as400 does not like that. Has anyone had any experience with this
problem?
BartIs this happening in generic query designer?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<Bart> wrote in message news:nahu915fhecll9qe7hvdpmm82uhsak6md8@.4ax.com...
> I'm trying to generate some reports from our AS400 mainframe using OLE
> DB. The problem is some of the fields have a name like josel#. For
> some reason, the Report builder in VS.net 2003 adds a space between
> the # and the characters. I've tried adding [] around the field but
> the as400 does not like that. Has anyone had any experience with this
> problem?
> Bart|||have you set the language for the report to the correct language?

Field names must be CLS-compliant identifiers.

While creating a report using 'CreateReport' API method for Report Services
2000, I am getting the following error.
//
A field in the data set â'MainDataSetâ' has the name â'Count Borrower Nameâ'.
Field names must be CLS-compliant identifiers.
--> A field in the data set â'MainDataSetâ' has the name â'Count Borrower Nameâ'.
Field names must be CLS-compliant identifiers.
//
The error occurs due to the space on the computed field name. If I
concatinate the words with '_' (for example) then I do not get an error.
I would like know how can I create report that has field with the name like
â'Count Borrower Nameâ', etc?
Thanks.
-SueThe CLS-compliance rule for field names comes from the fact that in vast
majority of scenarios fields in expressions are referenced using the
following syntax: =Fields!FieldName.Value
If a field name is not CLS-compliant (for example "Field Name") then the
syntax above would result in VB compilation error: =Fields!Field Name.Value
is not a valid VB snippet.
RDL does not support field names with spaces. Although if you data source
returns non-CLS-compliant name you designer will build
<Field name="non_CLS_Compliant_Name">
<DataField>non CLS Compliant Name</DataField>
</Field>
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E82BA30A-FD8B-48E9-BF24-28EA9408301D@.microsoft.com...
> While creating a report using 'CreateReport' API method for Report
Services
> 2000, I am getting the following error.
> //
> A field in the data set 'MainDataSet' has the name 'Count Borrower Name'.
> Field names must be CLS-compliant identifiers.
> --> A field in the data set 'MainDataSet' has the name 'Count Borrower
Name'.
> Field names must be CLS-compliant identifiers.
> //
>
> The error occurs due to the space on the computed field name. If I
> concatinate the words with '_' (for example) then I do not get an error.
> I would like know how can I create report that has field with the name
like
> 'Count Borrower Name', etc?
> Thanks.
> -Sue
>

Field Names from SQL Statement

Is there anyway to determine what the resulting Field Names are going to be from a SQL Statement?

For example:
SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3, TABLE2.FIELD1 AS ANOTHERNAME
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PK = TABLE2.FK

resulting field names:
FIELD1
FIELD2
FIELD3
ANOTHERNAME

Seems easy enough splitting all values before "FROM" by comma and doing some manipulation to remove table names and anything before the word "AS". However, it gets more difficult when you have complex CASE statements embedded in you query that may also contain commas.

Just a shot in the dark because I don't know if anyone has already done something like this before.

Thank you in advance,

JeffI'm not quite sure what you are after... but, assuming that all columns are aliased... it would be the 'word' preceding the commas and the FROM.

Where are you trying to consume this from? ADO.NET will expose these field names to you, or are you looking to somehow consume them in just SQL Server?

--Mike|||What I am trying to do is build a report editor whereby the user simply enters a SQL statement on one tab. The second tab would then contain a datagrid with the field names as one column and some other columns that are formatting the report such as column width, forecolor, etc. for each field. All of this information is then written to a database table to be used later to restore that report definitions which are used to build a dynamic datagrid to display the results of the query.

Unfortunately, not all fields are aliased so I cannot simply use those words that precede a comma. Additionally, there are also times when fields are concatenated and include commas such as TABLE1.LAST_NAME + ', ' + TABLE1.FIRST_NAME AS FULL_NAME.

Field names

Hi
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick
|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>
|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:

>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.

> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>

Field names

Hi
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:

>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.

> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>

Field Names

I want to build a report that from an OLAP data source that uses a named set
that is a rolling 12 month view. The problem is I don't like the field names
that are displayed. For Example,
'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
All I want is August 2003. I know I can edit the field name, but what
happens when the fields change due to the nature of the rolling view? Any
ideas?
--
Michael Hardy
ETL Developer
Visit our web pages:
www.atlantis.com
www.oceanclub.com
www.oneandonlyresorts.com
www.kerzner.comOpuspocus wrote:
> I want to build a report that from an OLAP data source that uses a named set
> that is a rolling 12 month view. The problem is I don't like the field names
> that are displayed. For Example,
> 'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
> All I want is August 2003. I know I can edit the field name, but what
> happens when the fields change due to the nature of the rolling view? Any
> ideas?
We uses this technique, coudl be more out there...
WITH
Member [Measures].[TimeMemberUniqueName] as
'[Time].currentmember.UniqueName'
Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
SET [SelectedPeriod] AS '[Time].[All Time].[2003].[Q4] : [Time].[All
Time].[2003].[Q4].Lag(3)'
SELECT
{ [Measures].[TimeDisplayName], [Measures].[DaMeasure] } ON AXIS(0)
{ [SelectedPeriod]} on 1
FROM XX_YY
In the dynamic version we get the "lag" and actual date from parameters
and code functions. We then get a static field_name
"Measures_TimeDisplayName" to be used in the report.
Regards
// Jonas Montonen|||I'm trying to work out the syntax. I get a 'Query failed: Syntax error in
axis definition' for the following MDX. Can't figure out why.
WITH
Member [Measures].[TimeMemberUniqueName] as
'[ArrivalDate].currentmember.UniqueName',
Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
SET [SelectedPeriod] AS '[ArrivalDate].[All ArrivalDate].[2003].[3] :
[ArrivalDate].[All ArrivalDate].[2003].[3].Lag(3)'
SELECT
{ [Measures].[TimeDisplayName], [Measures].[RsrvtnCount] } ON Axis(0)
{ [SelectedPeriod]} on ROWS
FROM [RsrvtnSummary]
"Jonas Montonen" wrote:
> Opuspocus wrote:
> > I want to build a report that from an OLAP data source that uses a named set
> > that is a rolling 12 month view. The problem is I don't like the field names
> > that are displayed. For Example,
> >
> > 'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
> > All I want is August 2003. I know I can edit the field name, but what
> > happens when the fields change due to the nature of the rolling view? Any
> > ideas?
> We uses this technique, coudl be more out there...
> WITH
> Member [Measures].[TimeMemberUniqueName] as
> '[Time].currentmember.UniqueName'
> Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
> SET [SelectedPeriod] AS '[Time].[All Time].[2003].[Q4] : [Time].[All
> Time].[2003].[Q4].Lag(3)'
> SELECT
> { [Measures].[TimeDisplayName], [Measures].[DaMeasure] } ON AXIS(0)
> { [SelectedPeriod]} on 1
> FROM XX_YY
> In the dynamic version we get the "lag" and actual date from parameters
> and code functions. We then get a static field_name
> "Measures_TimeDisplayName" to be used in the report.
> Regards
> // Jonas Montonen
>sql

Field names

Hi
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> Is there a preference in sql server 2005 as to how fields/objects are
>> named such as Client_Address, Client Address, Client-Address or
>> ClientAddress?
>> Also what are the reserved words that should be avoided in field names?
>> Thanks
>> Regards
>>
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
>> OK, well that was just an example. What about answers to my questions?
>> Regards
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:
>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.
> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>

Field Name variable

I'm trying to use sField as a variable for a field in the [Order Details]
table in Northwind, but having trouble with syntax. Can anyone shed light on
a solution?
CODE:
declare @.orderID int
declare @.sField Char(40)
set @.orderID = '10248'
set @.sField='UnitPrice'
"SELECT SUM(" + @.sField + ") FROM [Order Details] WHERE orderID=" +
@.orderIDScott,
You will need to use dynamic sql in order to do this. Before going forward,
take a minute and read this article to learn the pros and cons od dynamic sq
l.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
declare @.sql nvarchar(4000)
declare @.orderID int
declare @.sField sysname
declare @.result int
set @.orderID = 10248
set @.sField = N'UnitPrice'
set @.sql = N'SELECT @.result = SUM([' + @.sField + N']) FROM [Order Details]
WHERE orderID = @.orderID'
exec sp_executesql @.sql, N'@.orderID int, @.result int output', @.orderID,
@.result output
print @.result
go
AMB
"Scott" wrote:

> I'm trying to use sField as a variable for a field in the [Order Details]
> table in Northwind, but having trouble with syntax. Can anyone shed light
on
> a solution?
> CODE:
> declare @.orderID int
> declare @.sField Char(40)
> set @.orderID = '10248'
> set @.sField='UnitPrice'
> "SELECT SUM(" + @.sField + ") FROM [Order Details] WHERE orderID=" +
> @.orderID
>
>|||Can you point me to a link explaining the N' syntax? Or maybe offer an
explanation? I see it sometimes and not familiar with it.
thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:51D77971-D702-4851-9E9E-54FFACB1736F@.microsoft.com...
> Scott,
> You will need to use dynamic sql in order to do this. Before going
> forward,
> take a minute and read this article to learn the pros and cons od dynamic
> sql.
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
> declare @.sql nvarchar(4000)
> declare @.orderID int
> declare @.sField sysname
> declare @.result int
> set @.orderID = 10248
> set @.sField = N'UnitPrice'
> set @.sql = N'SELECT @.result = SUM([' + @.sField + N']) FROM [Order Details]
> WHERE orderID = @.orderID'
> exec sp_executesql @.sql, N'@.orderID int, @.result int output', @.orderID,
> @.result output
> print @.result
> go
>
> AMB
> "Scott" wrote:
>|||N' just denotes a unicode literal string rather than a ANSI string.
In your case it seems unnecessary and wasteful to use dynamic SQL
DECLARE @.orderid INTEGER
DECLARE @.sfield CHAR(40)
SET @.orderid = '10248'
SET @.sfield = 'unitprice'
SELECT
CASE @.sfield
WHEN 'unitprice' THEN SUM(unitprice)
WHEN 'quantity' THEN SUM(quantity)
END
FROM [order details]
WHERE orderid = @.orderid ;
David Portas
SQL Server MVP
--|||i have a problem with your syntax. Can you fix my code to work. The reason I
need it written this way is I will be using about 3 WHERE variables and
couldn't figure out how to pass them with your previous syntax.
CODE:
declare @.orderID int
declare @.sField sysname
declare @.result int
set @.orderID = 10248
set @.sField = 'UnitPrice'
set @.result = 'SELECT @.result = SUM([' + @.sField + ']) FROM [Order Details]
WHERE orderID = @.orderID'
print @.result
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1131728882.552388.46330@.f14g2000cwb.googlegroups.com...
> N' just denotes a unicode literal string rather than a ANSI string.
> In your case it seems unnecessary and wasteful to use dynamic SQL
> DECLARE @.orderid INTEGER
> DECLARE @.sfield CHAR(40)
> SET @.orderid = '10248'
> SET @.sfield = 'unitprice'
> SELECT
> CASE @.sfield
> WHEN 'unitprice' THEN SUM(unitprice)
> WHEN 'quantity' THEN SUM(quantity)
> END
> FROM [order details]
> WHERE orderid = @.orderid ;
> --
> David Portas
> SQL Server MVP
> --
>|||Scott wrote:
> i have a problem with your syntax. Can you fix my code to work. The reason
I
> need it written this way is I will be using about 3 WHERE variables and
> couldn't figure out how to pass them with your previous syntax.
>
I'm no longer clear about what you are trying to achive. WHERE can be
parametemerized without using dynamic SQL. You also don't need dynamic
code to return a value to a variable - in fact it's easier to do
without.
DECLARE @.orderid INTEGER
DECLARE @.sfield CHAR(40)
DECLARE @.result INTEGER
SET @.orderid = '10248'
SET @.sfield = 'unitprice'
SET @.result =
(SELECT
CASE @.sfield
WHEN 'unitprice' THEN SUM(unitprice)
WHEN 'quantity' THEN SUM(quantity)
END
FROM [order details]
WHERE orderid = @.orderid) ;
PRINT @.result
David Portas
SQL Server MVP
--

Field name question

Why does SQL Server change the name of a field named Level to [Level]? Level
is not a reserved word as far as I can tell, and this is creating a problem
when trying to hotsync PDAs to a SQL Server database.Ghost,
Level is an ODBC reserved keyword as well as a potential future SQL Server
keyword. See Reserved Keywords in the SQL Bol.
HTH
Jerry
"Ghost Dog" <caspar@.friendly.com> wrote in message
news:%23X6FJDiuFHA.908@.tk2msftngp13.phx.gbl...
> Why does SQL Server change the name of a field named Level to [Level]?
> Level
> is not a reserved word as far as I can tell, and this is creating a
> problem
> when trying to hotsync PDAs to a SQL Server database.
>|||The name has not changed - []'s are quotes for a quoted identifier.
(useful for using keywords if necessary, or multple words in a name).
The connection (oledb, odbc) might be defined to not use quoted
identifiers. If it's off, try turning it on and see if that solves the
problem.
Ghost Dog wrote:

>Why does SQL Server change the name of a field named Level to [Level]? Level
>is not a reserved word as far as I can tell, and this is creating a problem
>when trying to hotsync PDAs to a SQL Server database.
>
>