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.
Thursday, March 29, 2012
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?
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?
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)
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)
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
>
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
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,
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
>.
>
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.
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?
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
>
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
>
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
>
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
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
>
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
--
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.
>
>
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.
>
>
'field name' is not a recognized OPTIMIZER LOCK HINTS option
I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
when running the following query:
select
(SELECT
listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
euDateMod, HRTimeStamp) AS ChangeDate
FROM tablea Emp INNER JOIN Tableb HR
ON Emp.col1= HR.col1)
The function DateList2 is defined as so:
create FUNCTION [dbo].[DateList]
(
@.date1 smalldatetime,
@.date2 smalldatetime,
@.date3 smalldatetime,
@.date4 smalldatetime,
@.date5 smalldatetime
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT @.date1 as ListDate UNION
SELECT @.date2 as ListDate UNION
SELECT @.date3 as ListDate UNION
SELECT @.date4 as ListDate UNION
SELECT @.date5 as ListDate
)
The intent is to grab the highest date form a set of 5 dates that come from
either tablea or tableb and pass it as ChangeDate.
Any assistance would be appreciated.
Thanks,
DaveHi Dave
My guess is that the parser thinks DateList2 is a table, not a function, so
the parens following the name are thought to be a hint.
User defined functions must be qualified with their owner name to help the
parser distinguish them from tables. For example, if the owner of the
function is dbo, you can do this:
select
( SELECT
listdate FROM dbo.DateList2(ejDateBeg, epDateBeg, eeDateBeg,
euDateMod, HRTimeStamp) AS ChangeDate
...
HTH
Kalen Delaney, SQL Server MVP
"Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
news:FE9D8229-391D-4823-8079-D7CDD8155A5C@.microsoft.com...
>I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
> when running the following query:
> select
> (SELECT
> listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
> euDateMod, HRTimeStamp) AS ChangeDate
> FROM tablea Emp INNER JOIN Tableb HR
> ON Emp.col1= HR.col1)
> The function DateList2 is defined as so:
> create FUNCTION [dbo].[DateList]
> (
> @.date1 smalldatetime,
> @.date2 smalldatetime,
> @.date3 smalldatetime,
> @.date4 smalldatetime,
> @.date5 smalldatetime
> )
> RETURNS TABLE
> AS
> RETURN
> (
> -- Add the SELECT statement with parameter references here
> SELECT @.date1 as ListDate UNION
> SELECT @.date2 as ListDate UNION
> SELECT @.date3 as ListDate UNION
> SELECT @.date4 as ListDate UNION
> SELECT @.date5 as ListDate
> )
> The intent is to grab the highest date form a set of 5 dates that come
> from
> either tablea or tableb and pass it as ChangeDate.
> Any assistance would be appreciated.
> Thanks,
> Dave|||karen,
Thanks for responding. I did put dbo. and it still didn't work. It is like
the compiler is getting lost.
Dave
"Kalen Delaney" wrote:
> Hi Dave
> My guess is that the parser thinks DateList2 is a table, not a function, s
o
> the parens following the name are thought to be a hint.
> User defined functions must be qualified with their owner name to help th
e
> parser distinguish them from tables. For example, if the owner of the
> function is dbo, you can do this:
> select
> ( SELECT
> listdate FROM dbo.DateList2(ejDateBeg, epDateBeg, eeDateBeg,
> euDateMod, HRTimeStamp) AS ChangeDate
> ....
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> news:FE9D8229-391D-4823-8079-D7CDD8155A5C@.microsoft.com...
>
>|||One more thing. I even hardcoded dates, '1/1/2006' or passed null and it wor
ks.
"Dave Sundell" wrote:
> karen,
> Thanks for responding. I did put dbo. and it still didn't work. It is like
> the compiler is getting lost.
> Dave
> "Kalen Delaney" wrote:
>|||Dave,
In SQL Server 2000, you cannot pass a column to a table-valued
function. SQL Server 2000 can't handle a correlated table source,
but SQL Server 2005 can, if you use the APPLY operator.
It doesn't look to me as though what you are doing will work
anyway, since dbo.DateList may contain as many as 5 rows, and
if it does contain more than one, the sub-SELECT will raise an
error.
Try something like this if you are using 2000.
select
thisCol, thatCol,
(
select top 1 listdate from (
(select ejDateBeg as listdate union all
select epDateBeg union all select eeDateBeg
union all select euDateMod union all select HRTimeStamp
) as T
order by listdate desc
) as ChangeDate
from tablea as Emp ...
If this doesn't work (and I vaguely recall some quirks in
SQL Server 2000 when things are nested this deeply), please
post the CREATE TABLE statements and sample data sufficient
to reproduce the scenario (http://www.aspfaq.com/etiquette.asp?id=5006)
Steve Kass
Drew University
Dave Sundell wrote:
>I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
>when running the following query:
>select
>(SELECT
> listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
>euDateMod, HRTimeStamp) AS ChangeDate
>FROM tablea Emp INNER JOIN Tableb HR
> ON Emp.col1= HR.col1)
>The function DateList2 is defined as so:
>create FUNCTION [dbo].[DateList]
>(
> @.date1 smalldatetime,
> @.date2 smalldatetime,
> @.date3 smalldatetime,
> @.date4 smalldatetime,
> @.date5 smalldatetime
> )
>RETURNS TABLE
>AS
>RETURN
>(
> -- Add the SELECT statement with parameter references here
> SELECT @.date1 as ListDate UNION
> SELECT @.date2 as ListDate UNION
> SELECT @.date3 as ListDate UNION
> SELECT @.date4 as ListDate UNION
> SELECT @.date5 as ListDate
> )
>The intent is to grab the highest date form a set of 5 dates that come from
>either tablea or tableb and pass it as ChangeDate.
>Any assistance would be appreciated.
>Thanks,
>Dave
>|||You cannot pass columns as params for a Table valued UDF.
For fixing it, since you need only one value from that function, you can use
a scalar valued function.
Your query (as you have provided) will however work is SQL Server 2005 as
long as the UDF returns only one row.
Maybe you would want to go through this discussion :)
http://groups.google.co.in/group/mi...4b44dbec88e80d6
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Thanks to all (Karen, Steve, OmniBuzz) that responded. You were all a big he
lp.
Dave
"Omnibuzz" wrote:
> You cannot pass columns as params for a Table valued UDF.
> For fixing it, since you need only one value from that function, you can u
se
> a scalar valued function.
> Your query (as you have provided) will however work is SQL Server 2005 as
> long as the UDF returns only one row.
> Maybe you would want to go through this discussion :)
> http://groups.google.co.in/group/mi...4b44dbec88e80d6
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
when running the following query:
select
(SELECT
listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
euDateMod, HRTimeStamp) AS ChangeDate
FROM tablea Emp INNER JOIN Tableb HR
ON Emp.col1= HR.col1)
The function DateList2 is defined as so:
create FUNCTION [dbo].[DateList]
(
@.date1 smalldatetime,
@.date2 smalldatetime,
@.date3 smalldatetime,
@.date4 smalldatetime,
@.date5 smalldatetime
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT @.date1 as ListDate UNION
SELECT @.date2 as ListDate UNION
SELECT @.date3 as ListDate UNION
SELECT @.date4 as ListDate UNION
SELECT @.date5 as ListDate
)
The intent is to grab the highest date form a set of 5 dates that come from
either tablea or tableb and pass it as ChangeDate.
Any assistance would be appreciated.
Thanks,
DaveHi Dave
My guess is that the parser thinks DateList2 is a table, not a function, so
the parens following the name are thought to be a hint.
User defined functions must be qualified with their owner name to help the
parser distinguish them from tables. For example, if the owner of the
function is dbo, you can do this:
select
( SELECT
listdate FROM dbo.DateList2(ejDateBeg, epDateBeg, eeDateBeg,
euDateMod, HRTimeStamp) AS ChangeDate
...
HTH
Kalen Delaney, SQL Server MVP
"Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
news:FE9D8229-391D-4823-8079-D7CDD8155A5C@.microsoft.com...
>I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
> when running the following query:
> select
> (SELECT
> listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
> euDateMod, HRTimeStamp) AS ChangeDate
> FROM tablea Emp INNER JOIN Tableb HR
> ON Emp.col1= HR.col1)
> The function DateList2 is defined as so:
> create FUNCTION [dbo].[DateList]
> (
> @.date1 smalldatetime,
> @.date2 smalldatetime,
> @.date3 smalldatetime,
> @.date4 smalldatetime,
> @.date5 smalldatetime
> )
> RETURNS TABLE
> AS
> RETURN
> (
> -- Add the SELECT statement with parameter references here
> SELECT @.date1 as ListDate UNION
> SELECT @.date2 as ListDate UNION
> SELECT @.date3 as ListDate UNION
> SELECT @.date4 as ListDate UNION
> SELECT @.date5 as ListDate
> )
> The intent is to grab the highest date form a set of 5 dates that come
> from
> either tablea or tableb and pass it as ChangeDate.
> Any assistance would be appreciated.
> Thanks,
> Dave|||karen,
Thanks for responding. I did put dbo. and it still didn't work. It is like
the compiler is getting lost.
Dave
"Kalen Delaney" wrote:
> Hi Dave
> My guess is that the parser thinks DateList2 is a table, not a function, s
o
> the parens following the name are thought to be a hint.
> User defined functions must be qualified with their owner name to help th
e
> parser distinguish them from tables. For example, if the owner of the
> function is dbo, you can do this:
> select
> ( SELECT
> listdate FROM dbo.DateList2(ejDateBeg, epDateBeg, eeDateBeg,
> euDateMod, HRTimeStamp) AS ChangeDate
> ....
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> news:FE9D8229-391D-4823-8079-D7CDD8155A5C@.microsoft.com...
>
>|||One more thing. I even hardcoded dates, '1/1/2006' or passed null and it wor
ks.
"Dave Sundell" wrote:
> karen,
> Thanks for responding. I did put dbo. and it still didn't work. It is like
> the compiler is getting lost.
> Dave
> "Kalen Delaney" wrote:
>|||Dave,
In SQL Server 2000, you cannot pass a column to a table-valued
function. SQL Server 2000 can't handle a correlated table source,
but SQL Server 2005 can, if you use the APPLY operator.
It doesn't look to me as though what you are doing will work
anyway, since dbo.DateList may contain as many as 5 rows, and
if it does contain more than one, the sub-SELECT will raise an
error.
Try something like this if you are using 2000.
select
thisCol, thatCol,
(
select top 1 listdate from (
(select ejDateBeg as listdate union all
select epDateBeg union all select eeDateBeg
union all select euDateMod union all select HRTimeStamp
) as T
order by listdate desc
) as ChangeDate
from tablea as Emp ...
If this doesn't work (and I vaguely recall some quirks in
SQL Server 2000 when things are nested this deeply), please
post the CREATE TABLE statements and sample data sufficient
to reproduce the scenario (http://www.aspfaq.com/etiquette.asp?id=5006)
Steve Kass
Drew University
Dave Sundell wrote:
>I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
>when running the following query:
>select
>(SELECT
> listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
>euDateMod, HRTimeStamp) AS ChangeDate
>FROM tablea Emp INNER JOIN Tableb HR
> ON Emp.col1= HR.col1)
>The function DateList2 is defined as so:
>create FUNCTION [dbo].[DateList]
>(
> @.date1 smalldatetime,
> @.date2 smalldatetime,
> @.date3 smalldatetime,
> @.date4 smalldatetime,
> @.date5 smalldatetime
> )
>RETURNS TABLE
>AS
>RETURN
>(
> -- Add the SELECT statement with parameter references here
> SELECT @.date1 as ListDate UNION
> SELECT @.date2 as ListDate UNION
> SELECT @.date3 as ListDate UNION
> SELECT @.date4 as ListDate UNION
> SELECT @.date5 as ListDate
> )
>The intent is to grab the highest date form a set of 5 dates that come from
>either tablea or tableb and pass it as ChangeDate.
>Any assistance would be appreciated.
>Thanks,
>Dave
>|||You cannot pass columns as params for a Table valued UDF.
For fixing it, since you need only one value from that function, you can use
a scalar valued function.
Your query (as you have provided) will however work is SQL Server 2005 as
long as the UDF returns only one row.
Maybe you would want to go through this discussion :)
http://groups.google.co.in/group/mi...4b44dbec88e80d6
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Thanks to all (Karen, Steve, OmniBuzz) that responded. You were all a big he
lp.
Dave
"Omnibuzz" wrote:
> You cannot pass columns as params for a Table valued UDF.
> For fixing it, since you need only one value from that function, you can u
se
> a scalar valued function.
> Your query (as you have provided) will however work is SQL Server 2005 as
> long as the UDF returns only one row.
> Maybe you would want to go through this discussion :)
> http://groups.google.co.in/group/mi...4b44dbec88e80d6
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
Field name in LIKE statement
Hi,
I have come across an SQL statement today that I actually thought was impossible. I always thought that a LIKE statement must use a literal value such as field1 like '%ABC'. But today I came across a subselect that used a like statement
select * from TABLE_A
where exists (select 'X' from TABLE_B where TABLE_B.f1 like '%'+TABLE_A.f2+'%')
As I said I didn't think you could use a field in a like statement. This works in SQL Server,,, could people tell me if this would function for other databases.
Thanks
DaveYes; no problem in Oracle.SQL> SELECT * FROM EMP e
2 WHERE EXISTS (SELECT NULL FROM DEPT d
3 WHERE d.deptno LIKE '%' || e.sal || '%');
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---- ---- --- ---- --- ---- ----
7839 KING PRESIDENT 17.11.81 50 10
SQL>|||also no problem in mysql -- where TABLE_B.f1 like concat('%', TABLE_A.f2, '%')|||In Informix it can be done the same way as previously described for Oracle... Looks like standard SQL to me.sql
I have come across an SQL statement today that I actually thought was impossible. I always thought that a LIKE statement must use a literal value such as field1 like '%ABC'. But today I came across a subselect that used a like statement
select * from TABLE_A
where exists (select 'X' from TABLE_B where TABLE_B.f1 like '%'+TABLE_A.f2+'%')
As I said I didn't think you could use a field in a like statement. This works in SQL Server,,, could people tell me if this would function for other databases.
Thanks
DaveYes; no problem in Oracle.SQL> SELECT * FROM EMP e
2 WHERE EXISTS (SELECT NULL FROM DEPT d
3 WHERE d.deptno LIKE '%' || e.sal || '%');
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---- ---- --- ---- --- ---- ----
7839 KING PRESIDENT 17.11.81 50 10
SQL>|||also no problem in mysql -- where TABLE_B.f1 like concat('%', TABLE_A.f2, '%')|||In Informix it can be done the same way as previously described for Oracle... Looks like standard SQL to me.sql
Field name as a parameter to a stored procedure?
Hi,
Is it possible to pass a field name as a parameter to a stored procedure?
The user wants to select either Dollar or Euro in the UI. The field I want
to be variable is a float & is used in a simple calculation...
(a.A_Tot_Rev_For / e.USDollar)
So I want e.USDollar to be able to change to e.Euro for example when my
report needs euro values.
thanks in advance,
jpi think a UDF is a better option
Is it possible to pass a field name as a parameter to a stored procedure?
The user wants to select either Dollar or Euro in the UI. The field I want
to be variable is a float & is used in a simple calculation...
(a.A_Tot_Rev_For / e.USDollar)
So I want e.USDollar to be able to change to e.Euro for example when my
report needs euro values.
thanks in advance,
jpi think a UDF is a better option
Field name as a Parameter
Hi,
I'm trying to figure out how to pass a field name into a procedure so the procedure can selectively find out the value of different fields. The DB table I'm interested in has multiple bool fields in it and the field name is the parameter I want to pass into the procedure. With the example code I list below, there is only one row that exists in the table so the end result should be dependent upon the value in the field. The calling procedure is "attempting" to pass in the name of the field, and the called procedure should use the paramter passed in as the field to return in the table. If the field returned in the called procedure is true, the called procedure returns "0" to the calling procedure, otherwise it returns 1. The syntax I have doesn't seem to work in that the called procedure always returns true from the field and the calling procedure always gets 0 back from the called procedure.
In the calling procedure, I'm doing a select just to find out if a 0 or 1 was returned, but this will not be the final version of the code, its just for test purposes.
Any help on what I'm doing wrong greatly appriciated.
- Bruce
ALTER PROCEDURE [dbo].[caller]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @.include int,
@.Committee bit
execute @.include = dbo.callee @.Committee
IF @.include = 0
BEGIN
select * from ProvCompensation
END
ELSE
BEGIN
select * from ProvCommittee
END
END
ALTER PROCEDURE [dbo].[callee]
(@.fName bit)
AS
BEGIN
DECLARE @.aBool bit
SET NOCOUNT ON;
select @.aBool = @.fName from ProvisionsPlanSum where PlanId = -99
IF @.aBool = 'true'
BEGIN
RETURN (0)
END
ELSE
BEGIN
RETURN (1)
END
END
Generally speaking, parameterizing a column (or table for that matter) is not considered the "right" way to build SQL Server objects. But, if you really need to, you can use dynamic sql, and just execute it like:
declare @.aBool bit
declare @.query nvarchar(max)
declare @.fName varchar(10)
set @.fName = 'someCol'
set @.query = 'select @.aBool = ' + @.fName + ' from ProvisionsPlanSum where PlanId = -99'
exec sp_executeSQL
@.query, N'@.aBool bit output', @.aBool= @.aBool output
select @.aBool
Not 100% sure if this is perfect. I do know that this will work, if you need an example:
declare @.objectId int
exec sp_executeSQL
N'select @.objectId = max(object_id) from sys.objects',
N'@.objectId int output', @.objectId=@.objectId output
select @.objectId
This will also work :)
declare @.aBool bit
declare @.query nvarchar(1000)
declare @.fName varchar(30)
set @.fName = '1; DROP TABLE Test12; --'
set @.query = 'select @.aBool = ' + @.fName + ' from ProvisionsPlanSum where PlanId = -99'
EXEC sp_executeSQL
@.query, N'@.aBool bit output', @.aBool= @.aBool OUTPUT
SELECT @.aBool
|||Hey guys,
Thanks for the help. I'll give it a go sometime today.
... worked like poop through a goose, thanks guys!
- Bruce
Field msrepl_tran_version added
Hello there
I've create transacional replication from two databases. At the publisher
new field added to all the tables i mark as replicated: msrepl_tran_version
It cause damage to my database. What i need to do to ged rid of them? and
what i need to do so they won't be created again?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
You have to run a script to drop them. These columns are using by immediate
updating or queued updating publications. Have you need of these
publications?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23EzlsbLAGHA.3296@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've create transacional replication from two databases. At the publisher
> new field added to all the tables i mark as replicated:
> msrepl_tran_version
> It cause damage to my database. What i need to do to ged rid of them? and
> what i need to do so they won't be created again?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary:
When i tried to delete the field by this code?
ALTER TABLE Client
DROP COLUMN msrepl_tran_version
I got an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__Client__msrepl_t__02FED618' is dependent on column
'msrepl_tran_version'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
objects access this column.
how can i delete these records?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
> You have to run a script to drop them. These columns are using by
> immediate updating or queued updating publications. Have you need of these
> publications?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%23EzlsbLAGHA.3296@.TK2MSFTNGP12.phx.gbl...
>
|||You will have to drop the constraints before dropping the column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23ePeTNMAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Whell Hilary:
> When i tried to delete the field by this code?
> ALTER TABLE Client
> DROP COLUMN msrepl_tran_version
> I got an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__Client__msrepl_t__02FED618' is dependent on column
> 'msrepl_tran_version'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
> objects access this column.
> how can i delete these records?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
>
|||I'm sorry with my language. it's one constraint per msrepl_tran_version
column, not constraints...
Sorry.
|||There are default contraints on that field. You'll have to drop those
constraints bfore dropping the column itself. you should do a select on
sysobjects to find the default constraints like '%df_%_msrepl%'. i would
assume, replication is not in place(immediate updating or queued updating).
Once you drop the constraint, it lets u drop the column.
HTH
Tejas
"Roy Goldhammer" wrote:
> Whell Hilary:
> When i tried to delete the field by this code?
> ALTER TABLE Client
> DROP COLUMN msrepl_tran_version
> I got an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__Client__msrepl_t__02FED618' is dependent on column
> 'msrepl_tran_version'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
> objects access this column.
> how can i delete these records?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
>
>
|||Whell Tejas:
It gives me new error: ALTER TABLE DROP COLUMN failed because
'msrepl_tran_version' is currently replicated.
Now what i need to get rid of that, or what i need to cause it not be
created again?
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:32CD928E-5D0B-45EF-8A07-85C65A30BA65@.microsoft.com...[vbcol=seagreen]
> There are default contraints on that field. You'll have to drop those
> constraints bfore dropping the column itself. you should do a select on
> sysobjects to find the default constraints like '%df_%_msrepl%'. i would
> assume, replication is not in place(immediate updating or queued
> updating).
> Once you drop the constraint, it lets u drop the column.
> HTH
> Tejas
> "Roy Goldhammer" wrote:
|||You will have to drop your subscription before trying to make these changes.
Is this on the publisher or subscriber?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:essSUXVAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Whell Tejas:
> It gives me new error: ALTER TABLE DROP COLUMN failed because
> 'msrepl_tran_version' is currently replicated.
> Now what i need to get rid of that, or what i need to cause it not be
> created again?
> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
> news:32CD928E-5D0B-45EF-8A07-85C65A30BA65@.microsoft.com...
>
|||Whell Hilary:
It is on the Publisher
And it also addes me new tables Conflict...
I've chosed to use Transactional replication, and it act like merge
replication.
This is realy bad.
What i need to do to use replication without changing the database
Publisher?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#K1WEuYAGHA.3140@.TK2MSFTNGP14.phx.gbl...
> You will have to drop your subscription before trying to make these
changes.[vbcol=seagreen]
> Is this on the publisher or subscriber?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:essSUXVAGHA.2036@.TK2MSFTNGP14.phx.gbl...
would[vbcol=seagreen]
them?
>
I've create transacional replication from two databases. At the publisher
new field added to all the tables i mark as replicated: msrepl_tran_version
It cause damage to my database. What i need to do to ged rid of them? and
what i need to do so they won't be created again?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
You have to run a script to drop them. These columns are using by immediate
updating or queued updating publications. Have you need of these
publications?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23EzlsbLAGHA.3296@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've create transacional replication from two databases. At the publisher
> new field added to all the tables i mark as replicated:
> msrepl_tran_version
> It cause damage to my database. What i need to do to ged rid of them? and
> what i need to do so they won't be created again?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary:
When i tried to delete the field by this code?
ALTER TABLE Client
DROP COLUMN msrepl_tran_version
I got an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__Client__msrepl_t__02FED618' is dependent on column
'msrepl_tran_version'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
objects access this column.
how can i delete these records?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
> You have to run a script to drop them. These columns are using by
> immediate updating or queued updating publications. Have you need of these
> publications?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%23EzlsbLAGHA.3296@.TK2MSFTNGP12.phx.gbl...
>
|||You will have to drop the constraints before dropping the column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23ePeTNMAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Whell Hilary:
> When i tried to delete the field by this code?
> ALTER TABLE Client
> DROP COLUMN msrepl_tran_version
> I got an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__Client__msrepl_t__02FED618' is dependent on column
> 'msrepl_tran_version'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
> objects access this column.
> how can i delete these records?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
>
|||I'm sorry with my language. it's one constraint per msrepl_tran_version
column, not constraints...
Sorry.
|||There are default contraints on that field. You'll have to drop those
constraints bfore dropping the column itself. you should do a select on
sysobjects to find the default constraints like '%df_%_msrepl%'. i would
assume, replication is not in place(immediate updating or queued updating).
Once you drop the constraint, it lets u drop the column.
HTH
Tejas
"Roy Goldhammer" wrote:
> Whell Hilary:
> When i tried to delete the field by this code?
> ALTER TABLE Client
> DROP COLUMN msrepl_tran_version
> I got an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__Client__msrepl_t__02FED618' is dependent on column
> 'msrepl_tran_version'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
> objects access this column.
> how can i delete these records?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
>
>
|||Whell Tejas:
It gives me new error: ALTER TABLE DROP COLUMN failed because
'msrepl_tran_version' is currently replicated.
Now what i need to get rid of that, or what i need to cause it not be
created again?
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:32CD928E-5D0B-45EF-8A07-85C65A30BA65@.microsoft.com...[vbcol=seagreen]
> There are default contraints on that field. You'll have to drop those
> constraints bfore dropping the column itself. you should do a select on
> sysobjects to find the default constraints like '%df_%_msrepl%'. i would
> assume, replication is not in place(immediate updating or queued
> updating).
> Once you drop the constraint, it lets u drop the column.
> HTH
> Tejas
> "Roy Goldhammer" wrote:
|||You will have to drop your subscription before trying to make these changes.
Is this on the publisher or subscriber?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:essSUXVAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Whell Tejas:
> It gives me new error: ALTER TABLE DROP COLUMN failed because
> 'msrepl_tran_version' is currently replicated.
> Now what i need to get rid of that, or what i need to cause it not be
> created again?
> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
> news:32CD928E-5D0B-45EF-8A07-85C65A30BA65@.microsoft.com...
>
|||Whell Hilary:
It is on the Publisher
And it also addes me new tables Conflict...
I've chosed to use Transactional replication, and it act like merge
replication.
This is realy bad.
What i need to do to use replication without changing the database
Publisher?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#K1WEuYAGHA.3140@.TK2MSFTNGP14.phx.gbl...
> You will have to drop your subscription before trying to make these
changes.[vbcol=seagreen]
> Is this on the publisher or subscriber?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:essSUXVAGHA.2036@.TK2MSFTNGP14.phx.gbl...
would[vbcol=seagreen]
them?
>
field missing in Crystal Enterprise
I've got a report that runs from Crystal 10 just fine and the field shows up, but the field is missing when the report is run from Crystal Enterprise. All other fields show up just fine.
Information that may be important or not: I'm retrieving data from a SQL Server 2000 database Previously, I was getting a "failed to create rowset" error and the report wouldn't run at all. I narrowed the problem to the "Solution" field. Once I changed the driver* in the database connection, the report ran but the Solution field didn't show up. It's the longest field in the report and the only field in the report over 255 characters, if that means anything.
*I think I changed it from OLE DB to ODBC, but it could have been the other way around or totally different. Someone who's been here more than 3 weeks talked me through it.
Edit: I've tried Cstr({Solution}) and even Left(Cstr({Solution}), 20)Strangely, removing the Select Distinct Records option fixed it. To solve the resulting duplicate records problem, suppressed every result where the "ticket number" equaled the previous number. I think the report takes longer to run now.
Information that may be important or not: I'm retrieving data from a SQL Server 2000 database Previously, I was getting a "failed to create rowset" error and the report wouldn't run at all. I narrowed the problem to the "Solution" field. Once I changed the driver* in the database connection, the report ran but the Solution field didn't show up. It's the longest field in the report and the only field in the report over 255 characters, if that means anything.
*I think I changed it from OLE DB to ODBC, but it could have been the other way around or totally different. Someone who's been here more than 3 weeks talked me through it.
Edit: I've tried Cstr({Solution}) and even Left(Cstr({Solution}), 20)Strangely, removing the Select Distinct Records option fixed it. To solve the resulting duplicate records problem, suppressed every result where the "ticket number" equaled the previous number. I think the report takes longer to run now.
field mapping
Hi all,
I have written many stored procedures in our database to import data from
another database. Now I need to create a report that lists the source table
and column names and their corresponding destination table and column names.
I think it will be difficult and time-consuming for me to open each
procedure and copy this information manually. Are there any procedures or
queries that can help me in generating this report.
Thanks in advance.Hi,
maybe this will help you ...
SELECT OBJECT_NAME(id) AS Source, OBJECT_NAME(depid) AS Uses
FROM dbo.sysDepends WHERE id = OBJECT_ID('proc_sys_InsertPropertyValue'
)
All dependencies of objects are stored in the relation dbo.sysdepends!
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htmsql
I have written many stored procedures in our database to import data from
another database. Now I need to create a report that lists the source table
and column names and their corresponding destination table and column names.
I think it will be difficult and time-consuming for me to open each
procedure and copy this information manually. Are there any procedures or
queries that can help me in generating this report.
Thanks in advance.Hi,
maybe this will help you ...
SELECT OBJECT_NAME(id) AS Source, OBJECT_NAME(depid) AS Uses
FROM dbo.sysDepends WHERE id = OBJECT_ID('proc_sys_InsertPropertyValue'
)
All dependencies of objects are stored in the relation dbo.sysdepends!
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htmsql
Field looping
Hi, is there a way to loop thru all field in a row without knowing the field name something like that
DECLARE
rec XYZ%rowtype;
BEGIN
for i in 1..10 loop
dbms_output.printline(rec(i));
end loop;
END;
? ThanksOriginally posted by nbernierpq
Hi, is there a way to loop thru all field in a row without knowing the field name something like that
DECLARE
rec XYZ%rowtype;
BEGIN
for i in 1..10 loop
dbms_output.printline(rec(i));
end loop;
END;
? Thanks
You can certainly loop through the rows without knowing the field names, but you can't output the values without using the names.
DECLARE
rec XYZ%rowtype;
BEGIN
for i in 1..10 loop
dbms_output.printline(rec(i));
end loop;
END;
? ThanksOriginally posted by nbernierpq
Hi, is there a way to loop thru all field in a row without knowing the field name something like that
DECLARE
rec XYZ%rowtype;
BEGIN
for i in 1..10 loop
dbms_output.printline(rec(i));
end loop;
END;
? Thanks
You can certainly loop through the rows without knowing the field names, but you can't output the values without using the names.
Field locked
Dear all,
We've got a very strange issue and we can't work out so any help will be
welcomed. Involved Access and Sql Server.
From Access db a specific field of a row is not be able be updated for the
developer (by hand) as well as by the end-user.
But the rest ones (other fields in the same row, below rows, and so on, no
problem at all)
That table is linked to Sql Server. Nothing amazing with that.
Message is the following: "Writing forbidden, that field has already locked
by another user". In a fact, nobody else is in that moment working with the
Access db or Sql Native info.
Looking for any clue with sp_lock, select object_id and analyzing the
problem from another workstation doesn't give us any solution.
Does anyone ever experienced this problem?
Thanks for any input and regards,Could it be that the column in question is referenced by a constraint in
SQL.
The following should tell you :- EXEC sp_helpconstraint tablename
HTH. Ryan
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:6B04EF91-6F68-44B1-BAFE-5009E483D932@.microsoft.com...
> Dear all,
> We've got a very strange issue and we can't work out so any help will be
> welcomed. Involved Access and Sql Server.
> From Access db a specific field of a row is not be able be updated for the
> developer (by hand) as well as by the end-user.
> But the rest ones (other fields in the same row, below rows, and so on, no
> problem at all)
> That table is linked to Sql Server. Nothing amazing with that.
> Message is the following: "Writing forbidden, that field has already
> locked
> by another user". In a fact, nobody else is in that moment working with
> the
> Access db or Sql Native info.
> Looking for any clue with sp_lock, select object_id and analyzing the
> problem from another workstation doesn't give us any solution.
> Does anyone ever experienced this problem?
> Thanks for any input and regards,|||Yes, it have constraints but no in the affected field.
Although I would remark that the datatype is 'text'
"Ryan" wrote:
> Could it be that the column in question is referenced by a constraint in
> SQL.
> The following should tell you :- EXEC sp_helpconstraint tablename
> --
> HTH. Ryan
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:6B04EF91-6F68-44B1-BAFE-5009E483D932@.microsoft.com...
>
>
We've got a very strange issue and we can't work out so any help will be
welcomed. Involved Access and Sql Server.
From Access db a specific field of a row is not be able be updated for the
developer (by hand) as well as by the end-user.
But the rest ones (other fields in the same row, below rows, and so on, no
problem at all)
That table is linked to Sql Server. Nothing amazing with that.
Message is the following: "Writing forbidden, that field has already locked
by another user". In a fact, nobody else is in that moment working with the
Access db or Sql Native info.
Looking for any clue with sp_lock, select object_id and analyzing the
problem from another workstation doesn't give us any solution.
Does anyone ever experienced this problem?
Thanks for any input and regards,Could it be that the column in question is referenced by a constraint in
SQL.
The following should tell you :- EXEC sp_helpconstraint tablename
HTH. Ryan
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:6B04EF91-6F68-44B1-BAFE-5009E483D932@.microsoft.com...
> Dear all,
> We've got a very strange issue and we can't work out so any help will be
> welcomed. Involved Access and Sql Server.
> From Access db a specific field of a row is not be able be updated for the
> developer (by hand) as well as by the end-user.
> But the rest ones (other fields in the same row, below rows, and so on, no
> problem at all)
> That table is linked to Sql Server. Nothing amazing with that.
> Message is the following: "Writing forbidden, that field has already
> locked
> by another user". In a fact, nobody else is in that moment working with
> the
> Access db or Sql Native info.
> Looking for any clue with sp_lock, select object_id and analyzing the
> problem from another workstation doesn't give us any solution.
> Does anyone ever experienced this problem?
> Thanks for any input and regards,|||Yes, it have constraints but no in the affected field.
Although I would remark that the datatype is 'text'
"Ryan" wrote:
> Could it be that the column in question is referenced by a constraint in
> SQL.
> The following should tell you :- EXEC sp_helpconstraint tablename
> --
> HTH. Ryan
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:6B04EF91-6F68-44B1-BAFE-5009E483D932@.microsoft.com...
>
>
Field list is empty when calling stored procedure
Hello!
I am having problems retrieving list of fields when using stored
procedure as Dataset Source. I noticed that Reporting Services can retrieve
field list if stored procedure is trivial (simple select etc.). In my case,
I am insrting into temporary table and after some manipulations return
resultset. SQL Trace show that RS tries to execute SET FMTONLY ON <sp_name>
SET FMTONLY OFF when reading list of the fiels. This returns empty result
for my stored procedure.
I can not move any further without field list being populated properly. I
can try to replace stored procedure with the view etc. but I would really
like to know if someone experienced similar problems.
Any help is greately appreciated,
IgorIgor,
I have experienced that same behavior. The stored procedure will run but
I will not get a field list. I received the error that told me to hit
REFRESH to get the fields list ... I returned to the data tab, ran the
procedure and hit the refresh button ... then when I returned to layout tab
... the fields list was there. Otherwise I have read in books that you have
to type in your own fields list which to me was not a very acceptable answer
as my fields list was quite extensive. Hope that helps you!
MJ
"imarchenko" wrote:
> Hello!
> I am having problems retrieving list of fields when using stored
> procedure as Dataset Source. I noticed that Reporting Services can retrieve
> field list if stored procedure is trivial (simple select etc.). In my case,
> I am insrting into temporary table and after some manipulations return
> resultset. SQL Trace show that RS tries to execute SET FMTONLY ON <sp_name>
> SET FMTONLY OFF when reading list of the fiels. This returns empty result
> for my stored procedure.
> I can not move any further without field list being populated properly. I
> can try to replace stored procedure with the view etc. but I would really
> like to know if someone experienced similar problems.
> Any help is greately appreciated,
> Igor
>
>
I am having problems retrieving list of fields when using stored
procedure as Dataset Source. I noticed that Reporting Services can retrieve
field list if stored procedure is trivial (simple select etc.). In my case,
I am insrting into temporary table and after some manipulations return
resultset. SQL Trace show that RS tries to execute SET FMTONLY ON <sp_name>
SET FMTONLY OFF when reading list of the fiels. This returns empty result
for my stored procedure.
I can not move any further without field list being populated properly. I
can try to replace stored procedure with the view etc. but I would really
like to know if someone experienced similar problems.
Any help is greately appreciated,
IgorIgor,
I have experienced that same behavior. The stored procedure will run but
I will not get a field list. I received the error that told me to hit
REFRESH to get the fields list ... I returned to the data tab, ran the
procedure and hit the refresh button ... then when I returned to layout tab
... the fields list was there. Otherwise I have read in books that you have
to type in your own fields list which to me was not a very acceptable answer
as my fields list was quite extensive. Hope that helps you!
MJ
"imarchenko" wrote:
> Hello!
> I am having problems retrieving list of fields when using stored
> procedure as Dataset Source. I noticed that Reporting Services can retrieve
> field list if stored procedure is trivial (simple select etc.). In my case,
> I am insrting into temporary table and after some manipulations return
> resultset. SQL Trace show that RS tries to execute SET FMTONLY ON <sp_name>
> SET FMTONLY OFF when reading list of the fiels. This returns empty result
> for my stored procedure.
> I can not move any further without field list being populated properly. I
> can try to replace stored procedure with the view etc. but I would really
> like to know if someone experienced similar problems.
> Any help is greately appreciated,
> Igor
>
>
Subscribe to:
Posts (Atom)