updating text datatype: March 2012

Friday, March 30, 2012

Problem with clustered index

[tempdb].[dbo].[#IndexTest]),
SEEK

[tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORWAR
D)
(1 row(s) affected)
StmtText
---
SELECT * FROM #IndexTest WHERE Col3 = 'C'
(1 row(s) affected)
StmtText
----
--
|--Clustered Index Scan(OBJECT

[tempdb].[dbo].[#IndexTest]),
WHERE

[tempdb].[dbo].[#IndexTest].[Col3]='C'))
(1 row(s) affected)
StmtText
----
SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
(1 row(s) affected)
StmtText
----
----
---
|--Clustered Index Seek(OBJECT

[tempdb].[dbo].[#IndexTest]),
SEEK

[tempdb].[dbo].[#IndexTest].[Col1]=(1) AND
[tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
[tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
(1 row(s) affected)
Q.E.D.
Regards
robert|||Robert Klemme wrote:
> On 29.11.2006 14:24, Tracy McKibben wrote:
> You said:
>
> Then I wrote:
>
> The second SELECT does not use a set of /leading/ columns of the index!
> StmtText
> ---
> SELECT * FROM #IndexTest WHERE Col1 = 1
> (1 row(s) affected)
> StmtText
> ----
----
> |--Clustered Index Seek(OBJECT

[tempdb].[dbo].[#IndexTest])
,
> SEEK

[tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORW
ARD)
> (1 row(s) affected)
> StmtText
> ---
> SELECT * FROM #IndexTest WHERE Col3 = 'C'
> (1 row(s) affected)
> StmtText
> ----
---
> |--Clustered Index Scan(OBJECT

[tempdb].[dbo].[#IndexTest])
,
> WHERE

[tempdb].[dbo].[#IndexTest].[Col3]='C'))
> (1 row(s) affected)
> StmtText
> ----
> SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
> (1 row(s) affected)
> StmtText
> ----
----
---
> |--Clustered Index Seek(OBJECT

[tempdb].[dbo].[#IndexTest])
,
> SEEK

[tempdb].[dbo].[#IndexTest].[Col1]=(1) AND
> [tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
> [tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
> (1 row(s) affected)
> Q.E.D.
> Regards
> robert
?
I don't really know what you're debating here. I said that if IDNumber
(the leading column) wasn't used in the WHERE clause, an index seek
wouldn't happen. You disagreed with me, but then posted an example that
proves my point exactly. What am I missing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 29.11.2006 18:54, Tracy McKibben wrote:
> Robert Klemme wrote:
> ?
> I don't really know what you're debating here. I said that if IDNumber
> (the leading column) wasn't used in the WHERE clause, an index seek
> wouldn't happen. You disagreed with me, but then posted an example that
> proves my point exactly. What am I missing?
You said "If this is the only index available, then /only/ queries that
include IDNumber in the WHERE clause will seek against this index."
(accentuation by me). I objected that because /also/ queries that
contain /more leading columns/ from the index do a seek which is nicely
demonstrated by the plans I posted.
robert

Problem with clustered index

Hi!
I have a table with 5 primary keys. I have clustered index defined on
all 5 columns. However, I can see that this index is only using three
columns and not five. Why is that?
Also,
I for some reason when I run queries I get index scan as opposed to
index seek.
Thanks,
T.It sounds like the indexes you have are not working for you.
For us to assist you, please post the table DDL, along with a query or two
that you believe do not properly use indexing.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"tolcis" <a.liberchuk@.verizon.net> wrote in message
news:1164740732.714927.282500@.j44g2000cwa.googlegroups.com...
> Hi!
> I have a table with 5 primary keys. I have clustered index defined on
> all 5 columns. However, I can see that this index is only using three
> columns and not five. Why is that?
> Also,
> I for some reason when I run queries I get index scan as opposed to
> index seek.
> Thanks,
> T.
>|||> I have a table with 5 primary keys.
You can only have one PK per table. I assume you mean you have one PK based on 5 columns.
> I have clustered index defined on
> all 5 columns.
You mean that the index that goes with the PK is the clustered index.
> However, I can see that this index is only using three
> columns and not five.
Where do you see this?
> I for some reason when I run queries I get index scan as opposed to
> index seek.
You never get a table scan when a table has a clustered index, so I assume you mean "clustered index
scan". We need to know the table structure, indexes and queries to determine whether the index can
support your queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tolcis" <a.liberchuk@.verizon.net> wrote in message
news:1164740732.714927.282500@.j44g2000cwa.googlegroups.com...
> Hi!
> I have a table with 5 primary keys. I have clustered index defined on
> all 5 columns. However, I can see that this index is only using three
> columns and not five. Why is that?
> Also,
> I for some reason when I run queries I get index scan as opposed to
> index seek.
> Thanks,
> T.
>|||This is the table DDL:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[SERVICEW]') and OBJECTPROPERTY(id, N'IsUserTable') =1)
drop table [dbo].[SERVICEW]
GO
CREATE TABLE [dbo].[SERVICEW] (
[AppCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[IdNumber] [int] NOT NULL ,
[Family] [int] NOT NULL ,
[Sequence] [bigint] NOT NULL ,
[Source] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ApplicantType] [int] NULL ,
[Status] [int] NULL ,
[Type] [int] NULL ,
[UniqueExpansionId] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[UniqueIdNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Price] [real] NULL ,
[PriceOption] [int] NULL ,
[Quantity] [int] NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProductCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[exported] [bit] NULL ,
[UPTODATE] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SERVICEW] WITH NOCHECK ADD
CONSTRAINT [PK_SERVICEW] PRIMARY KEY CLUSTERED
(
[IdNumber],
[Family],
[AppCode],
[Sequence],
[Source]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SERVICEW] ADD
CONSTRAINT [DF__SERVICEW__Sequen__11FF8BD8] DEFAULT (0) FOR
[Sequence],
CONSTRAINT [DF__SERVICEW__Applic__12F3B011] DEFAULT (0) FOR
[ApplicantType],
CONSTRAINT [DF__SERVICEW__Status__13E7D44A] DEFAULT (0) FOR [Status],
CONSTRAINT [DF__SERVICEW__Type__14DBF883] DEFAULT (0) FOR [Type],
CONSTRAINT [DF__SERVICEW__Unique__15D01CBC] DEFAULT (' ') FOR
[UniqueExpansionId],
CONSTRAINT [DF__SERVICEW__Unique__16C440F5] DEFAULT (' ') FOR
[UniqueIdNumber],
CONSTRAINT [DF__SERVICEW__Price__7DE38492] DEFAULT (0.0) FOR [Price],
CONSTRAINT [DF__SERVICEW__PriceO__6F2B50E7] DEFAULT (0) FOR
[PriceOption],
CONSTRAINT [DF__SERVICEW__Quanti__396371BC] DEFAULT (0) FOR
[Quantity],
CONSTRAINT [DF__SERVICEW__Descri__03C67B1A] DEFAULT ('') FOR
[Description],
CONSTRAINT [DF__servicew__Produc__1452B3F5] DEFAULT ('') FOR
[ProductCode],
CONSTRAINT [DF__SERVICEW__UPTODA__08211BE3] DEFAULT (1) FOR [UPTODATE]
GO
My query is pretty large and it doesn't only use this table it use
multiple but in the execution plan I see it uses clustered index scan
on this table. From what I remember it should only be table seek and
not scan.
Thanks,
T.
Arnie Rowland wrote:
> It sounds like the indexes you have are not working for you.
> For us to assist you, please post the table DDL, along with a query or two
> that you believe do not properly use indexing.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "tolcis" <a.liberchuk@.verizon.net> wrote in message
> news:1164740732.714927.282500@.j44g2000cwa.googlegroups.com...
> > Hi!
> > I have a table with 5 primary keys. I have clustered index defined on
> > all 5 columns. However, I can see that this index is only using three
> > columns and not five. Why is that?
> > Also,
> > I for some reason when I run queries I get index scan as opposed to
> > index seek.
> >
> > Thanks,
> > T.
> >|||tolcis wrote:
> ALTER TABLE [dbo].[SERVICEW] WITH NOCHECK ADD
> CONSTRAINT [PK_SERVICEW] PRIMARY KEY CLUSTERED
> (
> [IdNumber],
> [Family],
> [AppCode],
> [Sequence],
> [Source]
> ) ON [PRIMARY]
> GO
>
If this is the only index available, then only queries that include
IDNumber in the WHERE clause will seek against this index. For example:
This will "seek":
SELECT * FROM ServiceW WHERE IDNumber = 10
This will "scan":
SELECT * FROM ServiceW WHERE AppCode = 'X'
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28.11.2006 22:05, Tracy McKibben wrote:
> tolcis wrote:
>> ALTER TABLE [dbo].[SERVICEW] WITH NOCHECK ADD
>> CONSTRAINT [PK_SERVICEW] PRIMARY KEY CLUSTERED
>> (
>> [IdNumber],
>> [Family],
>> [AppCode],
>> [Sequence],
>> [Source]
>> ) ON [PRIMARY]
>> GO
> If this is the only index available, then only queries that include
> IDNumber in the WHERE clause will seek against this index. For example:
> This will "seek":
> SELECT * FROM ServiceW WHERE IDNumber = 10
> This will "scan":
> SELECT * FROM ServiceW WHERE AppCode = 'X'
I beg to differ: /all/ queries containing filters on any set of
/leading/ columns of the index should be doing an index seek - unless
the optimizer decides that a full scan is more efficient (for example
because criteria will return 90% of the rows anyway).
Kind regards
robert|||Robert Klemme wrote:
> I beg to differ: /all/ queries containing filters on any set of
> /leading/ columns of the index should be doing an index seek - unless
> the optimizer decides that a full scan is more efficient (for example
> because criteria will return 90% of the rows anyway).
> Kind regards
> robert
Use my example below. Compare the execution plans of the two SELECT
statements. Illustrates exactly the point I was trying to make:
CREATE TABLE #IndexTest
(
Col1 INT,
Col2 INT,
Col3 CHAR(1),
Col4 CHAR(1),
Col5 DATETIME,
PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5)
)
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(1, 10, 'A', 'Z', DATEADD(dd, -1, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(2, 20, 'B', 'Y', DATEADD(dd, -2, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(3, 30, 'C', 'X', DATEADD(dd, -3, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(4, 40, 'D', 'W', DATEADD(dd, -4, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(5, 50, 'E', 'V', DATEADD(dd, -5, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(6, 60, 'F', 'U', DATEADD(dd, -6, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(7, 70, 'G', 'T', DATEADD(dd, -7, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(8, 80, 'H', 'S', DATEADD(dd, -8, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(9, 90, 'I', 'R', DATEADD(dd, -9, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(10, 100, 'J', 'Q', DATEADD(dd, -10, GETDATE()))
SELECT * FROM #IndexTest WHERE Col1 = 1
SELECT * FROM #IndexTest WHERE Col3 = 'C'
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 29.11.2006 14:24, Tracy McKibben wrote:
You said:
> If this is the only index available, then only queries that
> include IDNumber in the WHERE clause will seek against this index.
Then I wrote:
> Robert Klemme wrote:
>> I beg to differ: /all/ queries containing filters on any set of
>> /leading/ columns of the index should be doing an index seek - unless
>> the optimizer decides that a full scan is more efficient (for example
>> because criteria will return 90% of the rows anyway).
> Use my example below. Compare the execution plans of the two SELECT
> statements. Illustrates exactly the point I was trying to make:
> CREATE TABLE #IndexTest
> (
> Col1 INT,
> Col2 INT,
> Col3 CHAR(1),
> Col4 CHAR(1),
> Col5 DATETIME,
> PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5)
> )
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (1, 10, 'A', 'Z', DATEADD(dd, -1, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (2, 20, 'B', 'Y', DATEADD(dd, -2, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (3, 30, 'C', 'X', DATEADD(dd, -3, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (4, 40, 'D', 'W', DATEADD(dd, -4, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (5, 50, 'E', 'V', DATEADD(dd, -5, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (6, 60, 'F', 'U', DATEADD(dd, -6, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (7, 70, 'G', 'T', DATEADD(dd, -7, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (8, 80, 'H', 'S', DATEADD(dd, -8, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (9, 90, 'I', 'R', DATEADD(dd, -9, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (10, 100, 'J', 'Q', DATEADD(dd, -10, GETDATE()))
> SELECT * FROM #IndexTest WHERE Col1 = 1
> SELECT * FROM #IndexTest WHERE Col3 = 'C'
The second SELECT does not use a set of /leading/ columns of the index!
StmtText
---
SELECT * FROM #IndexTest WHERE Col1 = 1
(1 row(s) affected)
StmtText
------
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORWARD)
(1 row(s) affected)
StmtText
---
SELECT * FROM #IndexTest WHERE Col3 = 'C'
(1 row(s) affected)
StmtText
------
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#IndexTest]),
WHERE:([tempdb].[dbo].[#IndexTest].[Col3]='C'))
(1 row(s) affected)
StmtText
----
SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
(1 row(s) affected)
StmtText
--------
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1) AND
[tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
[tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
(1 row(s) affected)
Q.E.D.
Regards
robert|||Robert Klemme wrote:
> On 29.11.2006 14:24, Tracy McKibben wrote:
> You said:
>> If this is the only index available, then only queries that
> > include IDNumber in the WHERE clause will seek against this index.
> Then I wrote:
>> Robert Klemme wrote:
>> I beg to differ: /all/ queries containing filters on any set of
>> /leading/ columns of the index should be doing an index seek - unless
>> the optimizer decides that a full scan is more efficient (for example
>> because criteria will return 90% of the rows anyway).
>> Use my example below. Compare the execution plans of the two SELECT
>> statements. Illustrates exactly the point I was trying to make:
>> CREATE TABLE #IndexTest
>> (
>> Col1 INT,
>> Col2 INT,
>> Col3 CHAR(1),
>> Col4 CHAR(1),
>> Col5 DATETIME,
>> PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5)
>> )
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (1, 10, 'A', 'Z', DATEADD(dd, -1, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (2, 20, 'B', 'Y', DATEADD(dd, -2, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (3, 30, 'C', 'X', DATEADD(dd, -3, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (4, 40, 'D', 'W', DATEADD(dd, -4, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (5, 50, 'E', 'V', DATEADD(dd, -5, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (6, 60, 'F', 'U', DATEADD(dd, -6, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (7, 70, 'G', 'T', DATEADD(dd, -7, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (8, 80, 'H', 'S', DATEADD(dd, -8, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (9, 90, 'I', 'R', DATEADD(dd, -9, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (10, 100, 'J', 'Q', DATEADD(dd, -10, GETDATE()))
>> SELECT * FROM #IndexTest WHERE Col1 = 1
>> SELECT * FROM #IndexTest WHERE Col3 = 'C'
> The second SELECT does not use a set of /leading/ columns of the index!
> StmtText
> ---
> SELECT * FROM #IndexTest WHERE Col1 = 1
> (1 row(s) affected)
> StmtText
> ------
> |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
> SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORWARD)
> (1 row(s) affected)
> StmtText
> ---
> SELECT * FROM #IndexTest WHERE Col3 = 'C'
> (1 row(s) affected)
> StmtText
> ------
> |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#IndexTest]),
> WHERE:([tempdb].[dbo].[#IndexTest].[Col3]='C'))
> (1 row(s) affected)
> StmtText
> ----
> SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
> (1 row(s) affected)
> StmtText
> --------
> |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
> SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1) AND
> [tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
> [tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
> (1 row(s) affected)
> Q.E.D.
> Regards
> robert
?
I don't really know what you're debating here. I said that if IDNumber
(the leading column) wasn't used in the WHERE clause, an index seek
wouldn't happen. You disagreed with me, but then posted an example that
proves my point exactly. What am I missing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 29.11.2006 18:54, Tracy McKibben wrote:
> Robert Klemme wrote:
>> On 29.11.2006 14:24, Tracy McKibben wrote:
>> You said:
>> If this is the only index available, then only queries that
>> > include IDNumber in the WHERE clause will seek against this index.
>> Then I wrote:
>> Robert Klemme wrote:
>> I beg to differ: /all/ queries containing filters on any set of
>> /leading/ columns of the index should be doing an index seek -
>> unless the optimizer decides that a full scan is more efficient (for
>> example because criteria will return 90% of the rows anyway).
>> Use my example below. Compare the execution plans of the two SELECT
>> statements. Illustrates exactly the point I was trying to make:
>> CREATE TABLE #IndexTest
>> (
>> Col1 INT,
>> Col2 INT,
>> Col3 CHAR(1),
>> Col4 CHAR(1),
>> Col5 DATETIME,
>> PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5)
>> )
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (1, 10, 'A', 'Z', DATEADD(dd, -1, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (2, 20, 'B', 'Y', DATEADD(dd, -2, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (3, 30, 'C', 'X', DATEADD(dd, -3, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (4, 40, 'D', 'W', DATEADD(dd, -4, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (5, 50, 'E', 'V', DATEADD(dd, -5, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (6, 60, 'F', 'U', DATEADD(dd, -6, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (7, 70, 'G', 'T', DATEADD(dd, -7, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (8, 80, 'H', 'S', DATEADD(dd, -8, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (9, 90, 'I', 'R', DATEADD(dd, -9, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (10, 100, 'J', 'Q', DATEADD(dd, -10, GETDATE()))
>> SELECT * FROM #IndexTest WHERE Col1 = 1
>> SELECT * FROM #IndexTest WHERE Col3 = 'C'
>> The second SELECT does not use a set of /leading/ columns of the index!
>> StmtText
>> ---
>> SELECT * FROM #IndexTest WHERE Col1 = 1
>> (1 row(s) affected)
>> StmtText
>> ------
>> |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
>> SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORWARD)
>> (1 row(s) affected)
>> StmtText
>> ---
>> SELECT * FROM #IndexTest WHERE Col3 = 'C'
>> (1 row(s) affected)
>> StmtText
>> ------
>> |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#IndexTest]),
>> WHERE:([tempdb].[dbo].[#IndexTest].[Col3]='C'))
>> (1 row(s) affected)
>> StmtText
>> ----
>>
>> SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
>> (1 row(s) affected)
>> StmtText
>> --------
>> |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
>> SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1) AND
>> [tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
>> [tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
>> (1 row(s) affected)
>> Q.E.D.
>> Regards
>> robert
> ?
> I don't really know what you're debating here. I said that if IDNumber
> (the leading column) wasn't used in the WHERE clause, an index seek
> wouldn't happen. You disagreed with me, but then posted an example that
> proves my point exactly. What am I missing?
You said "If this is the only index available, then /only/ queries that
include IDNumber in the WHERE clause will seek against this index."
(accentuation by me). I objected that because /also/ queries that
contain /more leading columns/ from the index do a seek which is nicely
demonstrated by the plans I posted.
robert|||Why such a big primary key? Is it really necessary?
Try to cover your queries with indexes. If you're not lookin up the
records using the leading column in your primay key index, you should
plane an nonclustered index to cover your query.sql

Problem With Cluster !

Hello to everyone
I have two dell Poweredge 2650 units and a 6 Shared Hard drives with format
RAID -5 . The two units are clustered together via windows clustering
services. Software called PowerSun on each machines along with the cluster
owner tells which has ownership of the SAN.
What I need:
2 clustered machines, 1 shared storage. I will install my application on
both machines and have the database( or actual application, whichever works )
contained on the shared storeage. When one machines goes down, I want the
other machine to automatically start the application up. IS THIS
POSSIBLE?
This is very important to me, and would really appreciate any help that is
given.
Dimos-T
Hi
When you install SQL 2000 Enterprize Edition on a cluster, it sets up that
automatically for itself.
If you want your application be be started, make sure that you application
is "cluster aware" (look on MSDN and TechNet for information on that).
SQL BOL and Windows BOL have a lot of information on clustering.
Regards
Mike
"DImos GRIZANO-TRIKALWN" wrote:

> Hello to everyone
> I have two dell Poweredge 2650 units and a 6 Shared Hard drives with format
> RAID -5 . The two units are clustered together via windows clustering
> services. Software called PowerSun on each machines along with the cluster
> owner tells which has ownership of the SAN.
> What I need:
> 2 clustered machines, 1 shared storage. I will install my application on
> both machines and have the database( or actual application, whichever works )
> contained on the shared storeage. When one machines goes down, I want the
> other machine to automatically start the application up. IS THIS
> POSSIBLE?
> This is very important to me, and would really appreciate any help that is
> given.
> Dimos-T
>
|||Hi Dimos_m,
Please note it's recommended not to put your SQL data and log files on the
shared quorum disk so you should have a seperate shared disk as the SQL
server disk resource to located the data and log files (even better is to
have two to give data and log a seperate disk each).
Regards,
Jago
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> When you install SQL 2000 Enterprize Edition on a cluster, it sets up that
> automatically for itself.
> If you want your application be be started, make sure that you application
> is "cluster aware" (look on MSDN and TechNet for information on that).
> SQL BOL and Windows BOL have a lot of information on clustering.
> Regards
> Mike
> "DImos GRIZANO-TRIKALWN" wrote:
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F6EF3879-BF1D-4887-ABA7-B5E12492EEB2@.microsoft.com...
> Hi
> When you install SQL 2000 Enterprize Edition on a cluster, it sets up that
> automatically for itself.
> If you want your application be be started, make sure that you application
> is "cluster aware" (look on MSDN and TechNet for information on that).
>
It's not 100% necessary that your app be cluster aware, but it can aid some
things.
We have a clustered pair of servers, one of which runs an FTP server (not
IIS).
When the cluster failovers over, it cleanly starts up on the new machine.

Problem with Club Starter Kit

Hi All,

I have a problem with the Club Starter kit that I downloaded from the SQL Server Express Site. I installed the VSI into VS2005 but when I try to save the project, VS puts up a dialog with "Unable to complete Operation." The project runs OK within VS but any changes I make are lost when the app closes because it won't save.

I know this does not appear to be a SQL Server Express problem but I'm wondering if anyone else has used the starter kit and experienced the same problem, and more importantly, how to fix it.

Thanks a lot

This is starter kit have evolved more than others version 3.0 is in beta, the first link take you to version 2.0 and 3.0 beta 1 and the last two links are almost 60 pages tutorial about the kit. Print out both tutorials read it and if you are still having problems ask the experts at the project. If it is SQL Server database for the kit you can post again and I can answer it including T-SQL. Hope this helps.

http://www.codeplex.com/ClubStarterKit

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

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

Problem with CLR in SSAS 2005

Two things

1 When the CLR is integrated in SSAS 2005 and used in MDX, I get the error as "#Error Infinite recursion detected. The loop of dependencies is: r_variable -> _variable.". Please help me what can be the issue

2 How do I debug CLR methods from SSAS 2005, I searched the net/MS Article but could not find anything important

I really appreciate your help.

This error is likely have nothing to do with your use of CLR, but it is because of wrong MDX calculations. If you will provide the content of your MDX Script, it will help to find where the error is.

Problem with Client Side Printing

After the Cumulative Security Update for Internet Explorer (912812) the client side printing doesn't work any more. Almost each client get the error:

"Unable to load client print control."

Other clients get an error with code '0x8007F304'.

What can i do? Will SP1 solve the issue?

Client Print is an Active X control. The security update may have disabled the ability to run Active X controls. Check your IE properties to see if Active X controls are allowed to run. In the patch notes I read the following.

Compatibility Patch – To help enterprise customers who need more time to prepare for the ActiveX update changes discussed in Microsoft Knowledge Base Article 912945 and included in Microsoft Security Bulletin MS06-013, Microsoft is releasing a Compatibility Patch on April 11, 2006. As soon as it is deployed, the Compatibility Patch will temporarily return Internet Explorer to the previous functionality for handling ActiveX controls. This Compatibility Patch will function until an Internet Explorer update is released as part of the June update cycle, at which time the changes to the way Internet Explorer handles ActiveX controls will be permanent. This compatibility patch may require an additional restart for systems it is deployed on. For more information, see Microsoft Knowledge Base Article 917425.

|||

i checked my internet properties and nothing has changed.

other activex controls doesn't worked any more too. for example scriptX. meadriod published a new version of their activeX control.

so how can i get it work?

|||

Launch IE

Go to Tools\Internet Options\Security\Custom Level.

In the Active X controls and plugins, select to enable. If this is already done, you may need to then go to General\Settings\View Objects and remove and then reinstall the Active X controls.

Problem with checkpoints in SS2005 - Help is appreciated

Hello All,
I am having problems with the contention caused by checkpoints as I
migrated from SQL Server 2000 to SQL Server 2005. The same code, the
same database, tha same machine, the same load, but much worse
performance.
Do you know of any changes in the way checkpoints are performed in SQL
Server 2005?
In particular, any change in the locking behavior?
Your help is appreciated.
Kind regards
CD
CD
How do you know that is CHECKPOINT ? Have you run Profiler? Did you update
statistics after upgrading?
"CD" <crbd98@.yahoo.com> wrote in message
news:1179976388.257189.15410@.b40g2000prd.googlegro ups.com...
> Hello All,
> I am having problems with the contention caused by checkpoints as I
> migrated from SQL Server 2000 to SQL Server 2005. The same code, the
> same database, tha same machine, the same load, but much worse
> performance.
> Do you know of any changes in the way checkpoints are performed in SQL
> Server 2005?
> In particular, any change in the locking behavior?
> Your help is appreciated.
> Kind regards
> CD
>
|||Did you guys adjust the recovery interval?
Run a recompile on all stored procedures
But indeed monitor with profiler!
sql

Problem with checkpoints in SS2005 - Help is appreciated

Hello All,
I am having problems with the contention caused by checkpoints as I
migrated from SQL Server 2000 to SQL Server 2005. The same code, the
same database, tha same machine, the same load, but much worse
performance.
Do you know of any changes in the way checkpoints are performed in SQL
Server 2005?
In particular, any change in the locking behavior?
Your help is appreciated.
Kind regards
CDCD
How do you know that is CHECKPOINT ? Have you run Profiler? Did you update
statistics after upgrading?
"CD" <crbd98@.yahoo.com> wrote in message
news:1179976388.257189.15410@.b40g2000prd.googlegroups.com...
> Hello All,
> I am having problems with the contention caused by checkpoints as I
> migrated from SQL Server 2000 to SQL Server 2005. The same code, the
> same database, tha same machine, the same load, but much worse
> performance.
> Do you know of any changes in the way checkpoints are performed in SQL
> Server 2005?
> In particular, any change in the locking behavior?
> Your help is appreciated.
> Kind regards
> CD
>|||Did you guys adjust the recovery interval?
Run a recompile on all stored procedures
But indeed monitor with profiler!

Problem with checkpoints in SS2005 - Help is appreciated

Hello All,
I am having problems with the contention caused by checkpoints as I
migrated from SQL Server 2000 to SQL Server 2005. The same code, the
same database, tha same machine, the same load, but much worse
performance.
Do you know of any changes in the way checkpoints are performed in SQL
Server 2005?
In particular, any change in the locking behavior?
Your help is appreciated.
Kind regards
CDCD
How do you know that is CHECKPOINT ? Have you run Profiler? Did you update
statistics after upgrading?
"CD" <crbd98@.yahoo.com> wrote in message
news:1179976388.257189.15410@.b40g2000prd.googlegroups.com...
> Hello All,
> I am having problems with the contention caused by checkpoints as I
> migrated from SQL Server 2000 to SQL Server 2005. The same code, the
> same database, tha same machine, the same load, but much worse
> performance.
> Do you know of any changes in the way checkpoints are performed in SQL
> Server 2005?
> In particular, any change in the locking behavior?
> Your help is appreciated.
> Kind regards
> CD
>|||Did you guys adjust the recovery interval?
Run a recompile on all stored procedures
But indeed monitor with profiler!

Problem with Check Constraints

I am working with an evaluation copy of SQL Server 2000 for the first
time; my DB experience lies with MS Access.

I have a simple table in SQL Server (tblCompany) that has a field
called "Ticker." When new company stock tickers (i.e., MSFT for
Microsoft) are entered into the field, I'd like them in all
caps--whether the user types msft, Msft, MsFt, etc. In Access, this
was easy--simply set the Format to ">" in table design view.

In SQL Server Design Table view, I've clicked on "Manage Constraints"
and put the following code in that I found elsewhere:

([Ticker] = upper([Ticker]))

I then checked all three boxes below: "Check existing data on
creation," "Enforce constraint for replication," and "Enforce
constraint for INSERTs and UPDATEs." The first one, "Check existing
data..." is checked as I've already entered in some data in the field
in lowercase to see if the check constraint would go back and change
it to Upper Case--this because I'm wanting to ultimately migrate a
table from Access to SQL Server and ensure that all Tickers are in
Upper Case.

I'm able to do this and then save the table design with changes; but
every time, I then go and look at the table data to see if the check
constraint was applied, and each time it is not; then, I go back to
"Manage Constraints" and find that the "Check existing data..." box is
unchecked. I've gone through this SEVERAL times.

Hoping this is something simple. Apologize for my "newbieness." I've
got a "For Dummies" book in front of me as well as numerous Internet
windows open, trying to figure this out. Have checked books online on
the MSFT site as well to no avail.

Thanks in advance--

RADA constraint enforces data integrity rules but does not change existing or
newly inserted data. You need to cleanup your data and then add the
constraint to only permit uppercase values going forward.

If you want to automatically change values to upper case as they are entered
on the server side, you'll need to do this in a trigger. IMHO, this task is
better done in application code and let the database just enforce the data
integrity rule.

I don't know the details of the constraint you are adding but be aware that
case sensitivity is determined by collations in SQL 2000. The default
collation is case insensitive so you'll need to override the default
case-insensitive compare in your constraint. The example script below will
correct existing data and add a check constraint to ensure only upper case
values are allowed.

UPDATE Company
SET Ticker = UPPER('Ticker')
GO

ALTER TABLE Company
ADD CONSTRAINT CK_Ticker
CHECK (Ticker COLLATE SQL_Latin1_General_Cp1_CS_AS = UPPER(Ticker))
GO

On a side note, be aware that Hungarian notation (e.g. 'tbl' prefixes) are
frowned upon in client-server database design. The underlying database
implementation (table or view) should be transparent to database users.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"RAD" <rdavenport@.nyc.rr.com> wrote in message
news:d4119d8.0401251017.28f0b46f@.posting.google.co m...
> I am working with an evaluation copy of SQL Server 2000 for the first
> time; my DB experience lies with MS Access.
> I have a simple table in SQL Server (tblCompany) that has a field
> called "Ticker." When new company stock tickers (i.e., MSFT for
> Microsoft) are entered into the field, I'd like them in all
> caps--whether the user types msft, Msft, MsFt, etc. In Access, this
> was easy--simply set the Format to ">" in table design view.
> In SQL Server Design Table view, I've clicked on "Manage Constraints"
> and put the following code in that I found elsewhere:
> ([Ticker] = upper([Ticker]))
> I then checked all three boxes below: "Check existing data on
> creation," "Enforce constraint for replication," and "Enforce
> constraint for INSERTs and UPDATEs." The first one, "Check existing
> data..." is checked as I've already entered in some data in the field
> in lowercase to see if the check constraint would go back and change
> it to Upper Case--this because I'm wanting to ultimately migrate a
> table from Access to SQL Server and ensure that all Tickers are in
> Upper Case.
> I'm able to do this and then save the table design with changes; but
> every time, I then go and look at the table data to see if the check
> constraint was applied, and each time it is not; then, I go back to
> "Manage Constraints" and find that the "Check existing data..." box is
> unchecked. I've gone through this SEVERAL times.
> Hoping this is something simple. Apologize for my "newbieness." I've
> got a "For Dummies" book in front of me as well as numerous Internet
> windows open, trying to figure this out. Have checked books online on
> the MSFT site as well to no avail.
> Thanks in advance--
> RAD|||On 25 Jan 2004 10:17:18 -0800, rdavenport@.nyc.rr.com (RAD) wrote:

>I am working with an evaluation copy of SQL Server 2000 for the first
>time; my DB experience lies with MS Access.
>I have a simple table in SQL Server (tblCompany) that has a field
>called "Ticker." When new company stock tickers (i.e., MSFT for
>Microsoft) are entered into the field, I'd like them in all
>caps--whether the user types msft, Msft, MsFt, etc. In Access, this
>was easy--simply set the Format to ">" in table design view.
>In SQL Server Design Table view, I've clicked on "Manage Constraints"
>and put the following code in that I found elsewhere:
>([Ticker] = upper([Ticker]))
>I then checked all three boxes below: "Check existing data on
>creation," "Enforce constraint for replication," and "Enforce
>constraint for INSERTs and UPDATEs." The first one, "Check existing
>data..." is checked as I've already entered in some data in the field
>in lowercase to see if the check constraint would go back and change
>it to Upper Case--this because I'm wanting to ultimately migrate a
>table from Access to SQL Server and ensure that all Tickers are in
>Upper Case.
>I'm able to do this and then save the table design with changes; but
>every time, I then go and look at the table data to see if the check
>constraint was applied, and each time it is not; then, I go back to
>"Manage Constraints" and find that the "Check existing data..." box is
>unchecked. I've gone through this SEVERAL times.
>Hoping this is something simple. Apologize for my "newbieness." I've
>got a "For Dummies" book in front of me as well as numerous Internet
>windows open, trying to figure this out. Have checked books online on
>the MSFT site as well to no avail.
>Thanks in advance--
>RAD
That doesn't work, as can be shown with

select ticker from tblCompany where ticker = 'MSFT'

Your row will be returned regardless of the case of the data.

this may be something that can be set at the database level,
alternatively use a trigger to uppercase the data.

Something like;

create trigger instblCompany
on tblCompany
instead of insert
as
insert into tblCompany
select upper(ticker), all other columns
from inserted|||Thanks both Dan and Lyndon--I'll give it a whirl.

RAD

Problem with Check Constraint Validation in MS SQL Server Management Studio Express

Hello,

it seems like the check constraint validation of MS SQL Server Management Studio express is horribly, horribly broken. Either that or I'm using it wrong. I hope it's the latter.

I'm trying to add a check constraint to an empty table. The problem is that just about no form of expression syntax seems to get accepted by MSSQLSMS. On my a table "Projects" I've created a ntext column called "status". The following check constraint expressions have all yielded "Error validating constraint" from MSSQLSMS:
status = "Test"status in ( "One", "Two", "Three" )
I've tried converting the column to regular "text", but that didn't work.
I've tried disabling "check existing data", but that didn't work.
Just about the only thing that got accepted by MSSQLSMS was:
status like 'Test'
... but then MSSQLSMS failed to save the table. What am I doing wrong?sigh, i can't believe this is such an unsolvable problem.

problem with chart and grouping

hy,

i have a problem with placing Percentages on a stacked bar chart. It displays the mount of days worked per employee.i group by year for catagory group and on a productive field for series grouping (vacation is a ligit booking but not productive)

to calculate the percent i have to do:
value grouped by productive and year/grouped by year

when i change the productive group to also group on year it displayed the bar in productive and not productive but the productive part had color and a line across the bar to point it out.

so i tried making 2 series groups, with the year groups output property set to no,and got the same result.

any help would be great.
Jensif my question isn't clear please say sosql

Problem with Charindex function

Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
Bill
You will have to use TEXTPTR

>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Problem with Charindex function

Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Problem with Charindex function

Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR

>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Problem with CHARINDEX

Hi,
CHARINDEX does not seem to work on nvarchar datatype.
Code:
declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
select @.pCode = 'ABZC001'
select @.pInfo = 'ABZC0011,CBQA03,FG0023'
select @.index = charindex(@.pCode,@.pInfo)
if @.index = 0
begin
...
...
end
charindex returns 1, though a perfect match is not found!
Any suggestions, would sure be, of help.
Thanks.
kdkd
I'm not sure what are you trying to do?
What does 'perfect match' mean?
"kd" <kd@.discussions.microsoft.com> wrote in message
news:36373C11-8C5D-447D-9BC3-B02AA6943BD3@.microsoft.com...
> Hi,
> CHARINDEX does not seem to work on nvarchar datatype.
> Code:
> declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
> select @.pCode = 'ABZC001'
> select @.pInfo = 'ABZC0011,CBQA03,FG0023'
> select @.index = charindex(@.pCode,@.pInfo)
> if @.index = 0
> begin
> ....
> ....
> end
> charindex returns 1, though a perfect match is not found!
> Any suggestions, would sure be, of help.
> Thanks.
> kd|||Hi
There is a perfect match, but you may want add a comma to the end of @.pcode
to make sure that is a match for the whole string in the comma separated
list!!!
declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
select @.pCode = 'ABZC001,'
select @.pInfo = 'ABZC0011,CBQA03,FG0023'
select @.index = charindex(@.pCode,@.pInfo)
select @.INDEX
/*
0
(1 row(s) affected)
*/
John
"kd" <kd@.discussions.microsoft.com> wrote in message
news:36373C11-8C5D-447D-9BC3-B02AA6943BD3@.microsoft.com...
> Hi,
> CHARINDEX does not seem to work on nvarchar datatype.
> Code:
> declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
> select @.pCode = 'ABZC001'
> select @.pInfo = 'ABZC0011,CBQA03,FG0023'
> select @.index = charindex(@.pCode,@.pInfo)
> if @.index = 0
> begin
> ....
> ....
> end
> charindex returns 1, though a perfect match is not found!
> Any suggestions, would sure be, of help.
> Thanks.
> kd|||Hi John,
Adding a comma at the end of @.pCode seems to be a good idea. It would also
be required to add a comma at the end of the list @.pInfo, to make the last
string to be available for matching.
No comma at the end of the list would indicate the end of the list. If the
above solution is adopted, then, how to designate the end of the list?
kd.
"John Bell" wrote:

> Hi
> There is a perfect match, but you may want add a comma to the end of @.pcod
e
> to make sure that is a match for the whole string in the comma separated
> list!!!
> declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
> select @.pCode = 'ABZC001,'
> select @.pInfo = 'ABZC0011,CBQA03,FG0023'
> select @.index = charindex(@.pCode,@.pInfo)
> select @.INDEX
> /*
> --
> 0
> (1 row(s) affected)
> */
> John
>
> "kd" <kd@.discussions.microsoft.com> wrote in message
> news:36373C11-8C5D-447D-9BC3-B02AA6943BD3@.microsoft.com...
>
>|||Hi
You don't have to add a comma permanently!!
declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
select @.pCode = 'FG0023,'
select @.pInfo = 'ABZC0011,CBQA03,FG0023'
select @.index = charindex(@.pCode,@.pInfo+',')
select @.INDEX
You may also want to look at http://www.sommarskog.se/arrays-in-sql.html
John
"kd" <kd@.discussions.microsoft.com> wrote in message
news:E0354B88-6FA2-4504-A57F-F001686E28E0@.microsoft.com...
> Hi John,
> Adding a comma at the end of @.pCode seems to be a good idea. It would also
> be required to add a comma at the end of the list @.pInfo, to make the last
> string to be available for matching.
> No comma at the end of the list would indicate the end of the list. If the
> above solution is adopted, then, how to designate the end of the list?
> kd.
> "John Bell" wrote:
>|||I think you may be operating on a fundememental misconception about what
CharIndex () is for, and what it does...
It is simply to find the index, (or position) of one string, within another
SINGLE string. The function does not consider the target string as a set o
f
individual strings, but only as one single long string... To illustrate:
if you Look for' 11,,CBQ' within your string. it will return the value of 7
.
declare @.pCode as nvarchar(50), @.pInfo as nvarchar(50), @.index as integer
select @.pCode = '11,CBQ'
select @.pInfo = 'ABZC0011,CBQA03,FG0023'
select @.index = charindex(@.pCode,@.pInfo)
select @.INDEX
-- *************************
What you want I thing, is to use the SQL word "IN"
As:
declare @.pCode as nvarchar(50)
select @.pCode = 'ABZC0011'
If @.pCode IN ('ABZC0011', 'CBQA03', 'FG0023')
Print 'Found it'
Else
Print 'Not there'
"kd" wrote:
> Hi John,
> Adding a comma at the end of @.pCode seems to be a good idea. It would also
> be required to add a comma at the end of the list @.pInfo, to make the last
> string to be available for matching.
> No comma at the end of the list would indicate the end of the list. If the
> above solution is adopted, then, how to designate the end of the list?
> kd.
> "John Bell" wrote:
>|||What do you mean designate the end of the list? Wouldn't the lack of
follow-on data tend to indicate the end of the list :) As John also
replied, the comma doesn't have to be permanent.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"kd" <kd@.discussions.microsoft.com> wrote in message
news:E0354B88-6FA2-4504-A57F-F001686E28E0@.microsoft.com...
> Hi John,
> Adding a comma at the end of @.pCode seems to be a good idea. It would also
> be required to add a comma at the end of the list @.pInfo, to make the last
> string to be available for matching.
> No comma at the end of the list would indicate the end of the list. If the
> above solution is adopted, then, how to designate the end of the list?
> kd.
> "John Bell" wrote:
>

Problem with central subscriber model

Hi,

I am trying to acheive the central subscriber model for Sql server 2005 replication. I have two publishers and the centrol subscriber. Both publishers are Sql server 2005 Workgroup edition. The following table shows the Software I have used for the test.

SQL version

Service pack

HotFix

Publishers

SQL Server 2005 Workgroup edition

Subscriber

SQL Server 2005 Enterprise edition

I have encountered an obstacle in the replication process to achieve the Central subscriber model. I have configuration table, It exists on servers TB1 and TB2 and want to amalgamate the data to a central subscriber. There are two duplicate primary key and overlapping values existing across the 2 publisher tables. These records need to be updated during the process of replication.

TB1

Guid Name CreatDate EditDate

34a.. TEST02F 3/6/2007 3/6/2007

775.. EP109F 1/29/2007 1/29/2007

ffe.. EP109G 1/26/2007 1/26/2007

TB2

Guid Name CreatDate EditDate

d008.. MS060F 7/20/2005 11/21/2006

775.. EP109F 1/29/2007 1/29/2007

ffe.. EP109G 1/26/2007 1/26/2007

Normally, the default Article properties settings are used 'DROP existing table and re-create it', however here we don't want this setting, because the one table records will then be removed, leaving just the data from the other table. So, we modify the article properties on the snapshot tab to 'Keep the existing table unchanged'’. As result we got the PK violation error.

I have tried using -SkipError option in distribution job Replication Agent Profiles, that did not work.

Thank you for your help

Where are you getting this error? Are you getting it while the snapshot is being applied?

If so you need to create a consistent subscription which represents the data in all publishers with duplicates removed. Most people will have a location identifier in all publishers and then filter their publication on this filter (the delete all data which matches the row filter option in the name conflicts section of the snapshot tab).

If you are getting this error during the application of new commands you need to consider how unique your primary key is. GUIDs are pretty well guaranteed to be unique so I don't really understand how this could happen. You could modify your insert proc to do an existence check before it inserts the values.

|||The snapshot is going to take the contents of publisher1 and stick them into the tables on the subscriber. It will then take the contenst of publisher2 and stick that data into the same set of tables. There isn't any error with replication in this. If you can't take a table from publisher1 and a table from publisher2, smash them into a single table, and not get a primary key conflict, replication can not overcome this. A central subscriber is designed to aggregate data from 1 - N publishers into a single set of tables. That data still needs to obey the rules you set up on the tables such as PK. So, the only way this works is to ensure that you aren't trying to send anything in that causes a PK violation.sql

problem with CDO.Message

Hi
we using stroed precedure for sending mail . and we are
using CDOSYS.
It was working fine till yesterday. Suddenly from today its
excuting fine...but no mails has been received by anybody..we tried it
manually. The o/p is "mail send to rajukurian@.gmail.com"
But i dnt receive any mail..Till yrsterday the same procedure was
working fine..
what will be the reason..
Thanks!
RajHi
Have you checked/monitored the SMTP server?
John
"rajukurian@.gmail.com" wrote:
> Hi
> we using stroed precedure for sending mail . and we are
> using CDOSYS.
> It was working fine till yesterday. Suddenly from today its
> excuting fine...but no mails has been received by anybody..we tried it
> manually. The o/p is "mail send to rajukurian@.gmail.com"
> But i dnt receive any mail..Till yrsterday the same procedure was
> working fine..
> what will be the reason..
> Thanks!
> Raj
>|||rajukurian@.gmail.com wrote:
> Hi
> we using stroed precedure for sending mail . and we are
> using CDOSYS.
> It was working fine till yesterday. Suddenly from today its
> excuting fine...but no mails has been received by anybody..we tried it
> manually. The o/p is "mail send to rajukurian@.gmail.com"
> But i dnt receive any mail..Till yrsterday the same procedure was
> working fine..
> what will be the reason..
> Thanks!
> Raj
>
Most likely NOT a SQL Server problem, rather a problem somewhere in the
delivery stream. Check the SMTP server, check your recieving mail
server, check for aggressive spam filtering.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

problem with CDO.Message

Hi
we using stroed precedure for sending mail . and we are
using CDOSYS.
It was working fine till yesterday. Suddenly from today its
excuting fine...but no mails has been received by anybody..we tried it
manually. The o/p is "mail send to rajukurian@.gmail.com"
But i dnt receive any mail..Till yrsterday the same procedure was
working fine..
what will be the reason..
Thanks!
RajHi
Have you checked/monitored the SMTP server?
John
"rajukurian@.gmail.com" wrote:

> Hi
> we using stroed precedure for sending mail . and we are
> using CDOSYS.
> It was working fine till yesterday. Suddenly from today its
> excuting fine...but no mails has been received by anybody..we tried it
> manually. The o/p is "mail send to rajukurian@.gmail.com"
> But i dnt receive any mail..Till yrsterday the same procedure was
> working fine..
> what will be the reason..
> Thanks!
> Raj
>|||rajukurian@.gmail.com wrote:
> Hi
> we using stroed precedure for sending mail . and we are
> using CDOSYS.
> It was working fine till yesterday. Suddenly from today its
> excuting fine...but no mails has been received by anybody..we tried it
> manually. The o/p is "mail send to rajukurian@.gmail.com"
> But i dnt receive any mail..Till yrsterday the same procedure was
> working fine..
> what will be the reason..
> Thanks!
> Raj
>
Most likely NOT a SQL Server problem, rather a problem somewhere in the
delivery stream. Check the SMTP server, check your recieving mail
server, check for aggressive spam filtering.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problem with CDATA section in XQuery

Hi,
i am trying to insert a CDATA section using XQuery in T-SQL. I am following
the example from books online
http://msdn2.microsoft.com/en-us/library/ms175466.aspx and i keep getting th
e
error 'XQuery [modify()]: Syntax error near '<!''
The code is:
DECLARE @.myDoc xml
SET @.myDoc =
'<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features> </Features>
</ProductDescription>
</Root>'
SELECT @.myDoc
SET @.myDoc.modify('
insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>
into (/Root/ProductDescription/Features)[1] ')
SELECT @.myDoc ;
Does anyone have a solution for this?
Thanks,
Stefan
I think you can achieve the same effect by doing this
SET @.myDoc.modify('
insert text {"<notxml> as text </notxml> or cdata"}
into (/Root/ProductDescription/Features)[1]')|||Hello markc600@.hotmail.com,
Not really. If he really does want to use the CDATA tag here, he can't --
the content doesn't mater. I suspect, but XMLRW isn't parsing it correctly.
This smells like a bug to me.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||In this case I believe you should be able to use the text node constructor,
like
this
SET @.myDoc.modify('insert text{"<notxml> as text </notxml> or cdata"} into
(/Root/ProductDescription/Features)[1] ')
Denis Ruckebusch
SQL Server XML datatype test team
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mindflower" <mindflower@.discussions.microsoft.com> wrote in message
news:9DBECB6E-006B-4537-A039-CEEA784B9FD8@.microsoft.com...
> Hi,
> i am trying to insert a CDATA section using XQuery in T-SQL. I am followin
g
> the example from books online
> http://msdn2.microsoft.com/en-us/library/ms175466.aspx and i keep getting
the
> error 'XQuery [modify()]: Syntax error near '<!''
> The code is:
> DECLARE @.myDoc xml
> SET @.myDoc =
> '<Root>
> <ProductDescription ProductID="1" ProductName="Road Bike">
> <Features> </Features>
> </ProductDescription>
> </Root>'
> SELECT @.myDoc
> SET @.myDoc.modify('
> insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>
> into (/Root/ProductDescription/Features)[1] ')
> SELECT @.myDoc ;
> Does anyone have a solution for this?
> Thanks,
> Stefan

Problem with CDATA section in FOR XML EXPLICIT

SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT

in above query misc1 column may contain some non printable ascii
characters.

But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"

DECLARE @.XMLMessage XML

SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT)

I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.

Let me know if something dose'nt make sense

any help is appreciated

Thanks

You should ask the question in the SQL Server XML Forums.

Can you base64 encode your varbinary data instead of using CDATA section?

SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML AUTO, BINARY BASE64)

Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT
1AS tag
NULLAS parent,
template_idAS [Emailqueue!1!user_id],
misc1AS [Emailqueue!1!!cdata]
FROMEmailqueue WITH (NOLOCK)
WHEREqueue_id = -2147483169
FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii
characters.
But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"
DECLARE @.XMLMessage XML
SET @.XMLMessage = (SELECT
1AS tag
NULLAS parent,
template_idAS [Emailqueue!1!user_id],
misc1AS [Emailqueue!1!!cdata]
FROMEmailqueue WITH (NOLOCK)
WHEREqueue_id = -2147483169
FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.
Let me know if something dose'nt make sense
any help is appreciated
Thanks
prashant.k.jain@.gmail.com wrote:
> Hi,
> I am trying to create a XML out of sql 2005 database using FOR XML. I
> need to create XML for tables which may contain data having
> non-printable ascii characters (1-32 ascii character).
[...]
> But i need to store this XML data in some sql XML variable as i need to
> pass it to store procedure which expects an xml input. While doing
> following i gets an error saying "illegal xml character"
Using CDATA sections only protects the content against being parsed for
markup. It still has to conform to the XML rules on characters, so
control characters are still illegal: you'll have to filter them out or
encode them in some way.
///Peter
XML FAQ: http://xml.silmaril.ie/
|||Peter Flynn wrote:
> prashant.k.jain@.gmail.com wrote:
> [...]
> Using CDATA sections only protects the content against being parsed for
> markup. It still has to conform to the XML rules on characters, so
> control characters are still illegal: you'll have to filter them out or
> encode them in some way.
> ///Peter
> --
> XML FAQ: http://xml.silmaril.ie/
Just want to make sure is their no way other then filtering out or
encoding in some other manner. I thought their musst be some way within
SQL server to handel this.
Thanks,
Prashant
|||If you need to transport these unprintable characters in XML, you need to
cast the column to varbinary(max) and transport it as base64 encoded binary
data.
Best regards
Michael
<prashant.k.jain@.gmail.com> wrote in message
news:1162602860.975903.50180@.m73g2000cwd.googlegro ups.com...
> Peter Flynn wrote:
> Just want to make sure is their no way other then filtering out or
> encoding in some other manner. I thought their musst be some way within
> SQL server to handel this.
> Thanks,
> Prashant
>
sql

Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii
characters.
But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"
DECLARE @.XMLMessage XML
SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.
Let me know if something dose'nt make sense
any help is appreciated
Thanksprashant.k.jain@.gmail.com wrote:
> Hi,
> I am trying to create a XML out of sql 2005 database using FOR XML. I
> need to create XML for tables which may contain data having
> non-printable ascii characters (1-32 ascii character).
[...]
> But i need to store this XML data in some sql XML variable as i need to
> pass it to store procedure which expects an xml input. While doing
> following i gets an error saying "illegal xml character"
Using CDATA sections only protects the content against being parsed for
markup. It still has to conform to the XML rules on characters, so
control characters are still illegal: you'll have to filter them out or
encode them in some way.
///Peter
--
XML FAQ: http://xml.silmaril.ie/|||Peter Flynn wrote:
> prashant.k.jain@.gmail.com wrote:
> [...]
> Using CDATA sections only protects the content against being parsed for
> markup. It still has to conform to the XML rules on characters, so
> control characters are still illegal: you'll have to filter them out or
> encode them in some way.
> ///Peter
> --
> XML FAQ: http://xml.silmaril.ie/
Just want to make sure is their no way other then filtering out or
encoding in some other manner. I thought their musst be some way within
SQL server to handel this.
Thanks,
Prashant

problem with CAST and CONVERT in SQL Server2000 converting decimal places from 4 to 2

All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted.

For example:

Database Price SELECT statement Price

100.56 100.5600

I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56. Has anyone had a similar problem?

Thanks

Paradise [ip]

Look at the STR() function in Books Online.

STR(FloatExpression,LengthOverall,PlacesToRightOfDecimal)

|||Usually this type of formatting is done on the client side. You can use any of the string.format functions.|||You application is having a precision problem, it happens in SQL Server, there are two solutions a third party driver that on the TDS(Tabular Data Stream) level correct it or do it the cheap and free way use Decimal or Numeric instead of money and your problems will go away. Numeric is bigger than money but you will not get that problem. Hope this helps.|||I have tried to use the decimal datatype convert but it is rounding up or down and I am trying to show an amount, i.e. price. I just need something that will allow me to show the value as it exists in the database. When I view the data in the table, I only see the two decimal places.|||

Try the format in these links. Hope this helps

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingnumericdataforspecificculture.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

Problem with Case statement

not sure what's missing here but it's not liking my select with parameter check. The syntax is wrong here but not sure what.

CASE @.BKYChapter

WHEN 7 THEN

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, 'BK07', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'RecoveryCode' GROUP BY fc.R_FieldCode

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, '992', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'StatusCode' GROUP BY fc.R_FieldCode

WHEN 13 THEN

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, 'BK13', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'RecoveryCode' GROUP BY fc.R_FieldCode

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, '993', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'StatusCode' GROUP BY fc.R_FieldCode

[

ELSE SET @.Error = 'Chapter not valid'

]

END

CASE is not a control of flow statement. It is an expression. You have to use if...else to perform control of flow.|||thanks much!

Friday, March 30, 2012

Problem with clustered index

Problem with clustered index

Problem With Cluster !

Problem with Club Starter Kit

Problem with CLR in SSAS 2005

Problem with Client Side Printing

Problem with checkpoints in SS2005 - Help is appreciated

Problem with checkpoints in SS2005 - Help is appreciated

Problem with checkpoints in SS2005 - Help is appreciated

Problem with Check Constraints

Problem with Check Constraint Validation in MS SQL Server Management Studio Express

problem with chart and grouping

Problem with Charindex function

Problem with Charindex function

Problem with Charindex function

Problem with CHARINDEX

Problem with central subscriber model

problem with CDO.Message

problem with CDO.Message

Problem with CDATA section in XQuery

Problem with CDATA section in FOR XML EXPLICIT

Problem with CDATA section in FOR XML EXPLICIT

Problem with CDATA section in FOR XML EXPLICIT

problem with CAST and CONVERT in SQL Server2000 converting decimal places from 4 to 2

Problem with Case statement

updating text datatype

Blog Archive

About Me