NoRows Property: need to print table header.

Is there way to print the Header column of a table along with the
NoRows message ?
Seems like it just prints the NoRows message and doesnt render any part
of the table.
Thanks
RaviYEah thats right, "no ros" indicate not to render the actual element only a
notice that there is no data, you could go for a workaround to toggle
visibilty for this control and put another control under the actual control
which would be displayed with a dummy row to just display the column
headers. I know that quite the double to maintain, but it´s actually a
possibility.
HTH, Jens SUessmeyer.
--
http://www.sqlserver2005.de
--
"Ravi R" <bofobofo@.yahoo.com> schrieb im Newsbeitrag
news:1114794174.442413.16990@.f14g2000cwb.googlegroups.com...
> Is there way to print the Header column of a table along with the
> NoRows message ?
> Seems like it just prints the NoRows message and doesnt render any part
> of the table.
>
> Thanks
> Ravi
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid =
(
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__Stephen
I got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid =
> (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid =
(
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__StephenI got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid =
> (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid = (
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__StephenI got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid => (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

Normalization Question Regarding Column Combinations

We need to store land title information about properties in various
Australian states, but each state maintains it's own land title
registry and use different columns (well actually different
combinations of the same columns). For example:

Victoria store:

TorrensUnit
TorrensVolume
TorrensFolio

Queensland store:

TorrensCounty
TorrensLot
TorrensPlan
TorrensParish
TorrensUnit
TorrensVolume
TorrensTitleRef

There are 11 different columns and they are used in 8 different
combinations depending on the state.

Since we need to store information about land in different states I see
two possible solutions:

1. A sparse table containing the 11 columns with a CHECK constraint to
enforce the valid combinations.

2. A table for each state containing only the columns relevant to the
state with a foreign key relationship to the table containing the
common columns.

I'm not sure if the data type and length is consistent between states
yet (waiting to find this out) but assuming that it is which of these
approaches is going to be the most rigorous? I'm leaning towards (2)
but I don't like the feel of a table per state.>From a design standpoint I try to stay away from your first choice if
possible, seems like it'd be difficult to maintain. I like your second
point, but if there is a common set of data that is shared, then
"normalize" that information.

CREATE TABLE Store(
StoreID int IDENTITY(1,1) NOT NULL,
TorrensUnit varchar(50) NOT NULL,
TorrensVolume varchar(50) NOT NULL
)

CREATE TABLE StoreVictoria(
StoreVictoriaID int IDENTITY(1,1) NOT NULL,
StoreID INT NOT NULL, -- FK
--Specific columns
)

CREATE TABLE StoreQueensLand
(
StoreQueensLandID int IDENTITY(1,1) NOT NULL,
StoreID INT NOT NULL, -- FK
TorrensParish varchar(50),
TorrensTitleRef varchar(50)
--Specific columns
)|||On one hand, property does not move from state to state so one table
per state would work and make sense.

But do you ever view the set of all parcels of land in the country as
your unit of work? I woudl go with one table per state and VIEW that
has the global summary information.|||90% of the time we will be dealing with individual properties, there
will be time where we need to show a list of properties that are in
different states so a VIEW would make sense here. Thanks Celko, when's
your SQL Coding Standards book coming out? Looking forward to it.|||>> Thanks Celko, when's your SQL Coding Standards book coming out? Looking forward to it. <<

It has been out for a several weeks now.

http://www.amazon.com/exec/obidos/t...=glance&s=bookssql

NOP in sysprocesses (cmd column)

Can somebody please tell me what 'NOP' is in the cmd column of sysprocesses
(SQL2K) noticed this when a stored procedure was running . .. I could not
find anything about this cmd (relating to SQL) on the internet . . .however
there were some references to NOP in c# and .NET programming
Thanks for your help
Olu Adedeji
Baysignia Systems
In other areas NOP means "No Operation", but I do not know if that is true
here..
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
"Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> Can somebody please tell me what 'NOP' is in the cmd column of
sysprocesses
> (SQL2K) noticed this when a stored procedure was running . .. I could not
> find anything about this cmd (relating to SQL) on the internet . .
..however
> there were some references to NOP in c# and .NET programming
> Thanks for your help
>
> Olu Adedeji
> Baysignia Systems
|||Thanks Wayne
"Wayne Snyder" wrote:

> In other areas NOP means "No Operation", but I do not know if that is true
> here..
> --
> 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
> "Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
> news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> sysprocesses
> ..however
>
>

NOP in sysprocesses (cmd column)

Can somebody please tell me what 'NOP' is in the cmd column of sysprocesses
(SQL2K) noticed this when a stored procedure was running . .. I could not
find anything about this cmd (relating to SQL) on the internet . . .however
there were some references to NOP in c# and .NET programming
Thanks for your help
Olu Adedeji
Baysignia SystemsIn other areas NOP means "No Operation", but I do not know if that is true
here..
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
"Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> Can somebody please tell me what 'NOP' is in the cmd column of
sysprocesses
> (SQL2K) noticed this when a stored procedure was running . .. I could not
> find anything about this cmd (relating to SQL) on the internet . .
.however
> there were some references to NOP in c# and .NET programming
> Thanks for your help
>
> Olu Adedeji
> Baysignia Systems|||Thanks Wayne
"Wayne Snyder" wrote:

> In other areas NOP means "No Operation", but I do not know if that is true
> here..
> --
> 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
> "Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
> news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> sysprocesses
> ..however
>
>

NOP in sysprocesses (cmd column)

Can somebody please tell me what 'NOP' is in the cmd column of sysprocesses
(SQL2K) noticed this when a stored procedure was running . .. I could not
find anything about this cmd (relating to SQL) on the internet . . .however
there were some references to NOP in c# and .NET programming
Thanks for your help
Olu Adedeji
Baysignia SystemsIn other areas NOP means "No Operation", but I do not know if that is true
here..
--
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
"Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> Can somebody please tell me what 'NOP' is in the cmd column of
sysprocesses
> (SQL2K) noticed this when a stored procedure was running . .. I could not
> find anything about this cmd (relating to SQL) on the internet . .
.however
> there were some references to NOP in c# and .NET programming
> Thanks for your help
>
> Olu Adedeji
> Baysignia Systems|||Thanks Wayne
"Wayne Snyder" wrote:
> In other areas NOP means "No Operation", but I do not know if that is true
> here..
> --
> 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
> "Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
> news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> > Can somebody please tell me what 'NOP' is in the cmd column of
> sysprocesses
> > (SQL2K) noticed this when a stored procedure was running . .. I could not
> > find anything about this cmd (relating to SQL) on the internet . .
> ..however
> > there were some references to NOP in c# and .NET programming
> >
> > Thanks for your help
> >
> >
> > Olu Adedeji
> > Baysignia Systems
>
>sql

noob alert! What i thought was a real simple query...

Hi

I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.

By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.

SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber

Thanks for any suggestions you might have.
Jthis perhaps?
SELECT itemNumberSet.itemNumber
FROM itemNumberSet
LEFT OUTER
JOIN itemUPCtable
ON SUBSTRING(itemUPCtable.UPCcode,9,3) = itemNumberSet.itemNumber
WHERE SUBSTRING(itemUPCtable.UPCcode,9,3) IS NULL
ORDER
BY itemNumberSet.itemNumber|||I really need to practice playing with joins, cause as it stands im a hack. I should really be taking a course or something. I see you are in Toronto. I'm in Mississauga. Any tips as to where one might go to be properly educated in SQL?

Thanks again
J|||you can go a long way with online free sql tutorials

however, you need to make sure you are on an actual tutorial site, as in here's-some-good-information-because-i-love-sql type of tutorial site, rather than here's-some-sql-stuff-which-i-lifted-from-somewhere-so-i-can-run-a-lot-of-ads-and-make-money type of tutorial site

there are a couple of good sites listed here: http://r937.com/links.cfm?links=sqlsql

Non-queried parameter giving 'Invalid Column Name' error

I have created a non-queried boolean parameter called HideOptionalColumns.
This field doesn't exist in my query result set. I use this value in the
Hidden attribute of the Visiblity property for the column to hide or display
certain columns based on the user's runtime selection. It works fine when I
View Report in VS - the columns are hiddden/displayed appropriately. But
when I run the report, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'myDset'.
Invalid column name 'HideOptionalColumns'.
This seems to imply that it does need that parameter in the result set
itself. But that configuration doesn't work since the rows are then filtered
out based on that parameter. Is there another way to accomplish this hiding
of columns?
Thank you!On Apr 30, 4:04 pm, marian <mar...@.discussions.microsoft.com> wrote:
> I have created a non-queried boolean parameter called HideOptionalColumns.
Instead of using a parameter, you could place another field/textbox
right in your table or matrix. For each column you want to hide, set
the Hide option to trigger from the field or text box you add. I use
this a lot and it works without any problems.
> This field doesn't exist in my query result set. I use this value in the
> Hidden attribute of the Visiblity property for the column to hide or display
> certain columns based on the user's runtime selection. It works fine when I
> View Report in VS - the columns are hiddden/displayed appropriately. But
> when I run the report, I get the following error:
> An error has occurred during report processing.
> Query execution failed for data set 'myDset'.
> Invalid column name 'HideOptionalColumns'.
> This seems to imply that it does need that parameter in the result set
> itself. But that configuration doesn't work since the rows are then filtered
> out based on that parameter. Is there another way to accomplish this hiding
> of columns?
> Thank you!sql

Nonprintable / encrypted characters is not found

Hi All

I have a table with one column :

CREATE TABLE test2
( a char(15)
primary KEY CLUSTERED )

The column a is filled with encrypted data
(contains control and extended characters).
On my test, the select statement for one row
in table test2 does not always work sucessfully.

Below is my unsucessful statement for the second
row followed by 9 rows data. Each row is displayed
in 2 versions, text and VB ascii code.

select * from test2
where a = '\0<[\
|^[\]}{;\'

----
Row-1
%;>.[
,)]/\-,=/
37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
Row-2
\0<[\
|^[\]}{;\
92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
Row-3
\0<[\ ,)? {=\?
92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
Row-4
\0<[\ \%:_`- ]_
92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
Row-5
\0<[\ \^:& }{;\
92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
Row-6
\0[*_1>\_\ }{@.+
92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
Row-7
].>^/. ]-=}<)^&
93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
Row-8
]0_^{
}^~_|~{^_
93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
Row-9
{
}{31{2|02~||
123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
----

I have 3 questions for all of you :
- Why SQL2000 can not find the second row (certain row)?
- Can SQL2000 handle character 0 to 255 ?
- Does my encrypted method produce bad data for SQL2000?

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Anita" <anonymous@.devdex.com> wrote in message
news:4029728d$0$196$75868355@.news.frii.net...
> Hi All
> I have a table with one column :
> CREATE TABLE test2
> ( a char(15)
> primary KEY CLUSTERED )
> The column a is filled with encrypted data
> (contains control and extended characters).
> On my test, the select statement for one row
> in table test2 does not always work sucessfully.
> Below is my unsucessful statement for the second
> row followed by 9 rows data. Each row is displayed
> in 2 versions, text and VB ascii code.
> select * from test2
> where a = '\0<[\
> |^[\]}{;\'
> ----
> Row-1
> %;>.[
> ,)]/\-,=/
> 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> Row-2
> \0<[\
> |^[\]}{;\
> 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> Row-3
> \0<[\ ,)? {=\?
> 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> Row-4
> \0<[\ \%:_`- ]_
> 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> Row-5
> \0<[\ \^:& }{;\
> 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> Row-6
> \0[*_1>\_\ }{@.+
> 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> Row-7
> ].>^/. ]-=}<)^&
> 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> Row-8
> ]0_^{
> }^~_|~{^_
> 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> Row-9
> {
> }{31{2|02~||
> 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> ----
> I have 3 questions for all of you :
> - Why SQL2000 can not find the second row (certain row)?
> - Can SQL2000 handle character 0 to 255 ?
> - Does my encrypted method produce bad data for SQL2000?
> Thanks in advance
> Anita Hery
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

1. I don't know, but the most obvious reason is that your search string
doesn't exactly match the encrypted string. Did you enter the SELECT
statement exactly as above? If so, then the issue could be that SQL ignores
whitespace, so you would need something like this to handle the newline:

select * from test2
where a = '\0<[\' + char(10) + '|^[\]}{;\'

2. Yes

3. As long as the encrypted string is in a character set supported by the
server (and you could use Unicode if necessary), then there shouldn't be any
specific issues - it's up to your application to do the
encrpytion/decryption.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:402a7e11$1_3@.news.bluewin.ch...
> "Anita" <anonymous@.devdex.com> wrote in message
> news:4029728d$0$196$75868355@.news.frii.net...
> > Hi All
> > I have a table with one column :
> > CREATE TABLE test2
> > ( a char(15)
> > primary KEY CLUSTERED )
> > The column a is filled with encrypted data
> > (contains control and extended characters).
> > On my test, the select statement for one row
> > in table test2 does not always work sucessfully.
> > Below is my unsucessful statement for the second
> > row followed by 9 rows data. Each row is displayed
> > in 2 versions, text and VB ascii code.
> > select * from test2
> > where a = '\0<[\
> > |^[\]}{;\'
> > ----
> > Row-1
> > %;>.[
> > ,)]/\-,=/
> > 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> > Row-2
> > \0<[\
> > |^[\]}{;\
> > 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> > Row-3
> > \0<[\ ,)? {=\?
> > 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> > Row-4
> > \0<[\ \%:_`- ]_
> > 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> > Row-5
> > \0<[\ \^:& }{;\
> > 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> > Row-6
> > \0[*_1>\_\ }{@.+
> > 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> > Row-7
> > ].>^/. ]-=}<)^&
> > 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> > Row-8
> > ]0_^{
> > }^~_|~{^_
> > 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> > Row-9
> > {
> > }{31{2|02~||
> > 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> > ----
> > I have 3 questions for all of you :
> > - Why SQL2000 can not find the second row (certain row)?
> > - Can SQL2000 handle character 0 to 255 ?
> > - Does my encrypted method produce bad data for SQL2000?
> > Thanks in advance
> > Anita Hery
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> 1. I don't know, but the most obvious reason is that your search string
> doesn't exactly match the encrypted string. Did you enter the SELECT
> statement exactly as above? If so, then the issue could be that SQL
ignores
> whitespace, so you would need something like this to handle the newline:
> select * from test2
> where a = '\0<[\' + char(10) + '|^[\]}{;\'
> 2. Yes
> 3. As long as the encrypted string is in a character set supported by the
> server (and you could use Unicode if necessary), then there shouldn't be
any
> specific issues - it's up to your application to do the
> encrpytion/decryption.
> Simon

Oops - it's not at all correct to say that SQL ignores whitespace; I was
thinking of trailing spaces there for some reason. What may have happened is
that Query Analyzer interpreted your newline as ASCII 13 (carriage return):

select ascii('
')

This gives 13, but you need 10, according to what you posted, so the select
query I suggested above should hopefully be what you need.

Simon|||Simon,

Thanks for your reply.

I show the exact data in ascii code. I am sure the source
of my problem is not about mistyping. I first found the
problem in my VB application. The following is a part of it.

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.OpenRecordset(s, dbOpenDynaset)
For i = 1 To rs.RecordCount
Debug.Print rs!a
X = ""
For j = 1 To Len(rs!a)
X = X & Asc(Mid(rs!a, j, 1)) & ";"
Next
Debug.Print X

s = "select * from test2 where a = '" & rs!a & "'"
Set rs1 = cn.OpenRecordset(s, dbOpenDynaset)
If rs1.RecordCount = 0 Then
Debug.Print "==not found=="
End If
rs.movenext
Next
End Sub

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita,

I ran the following SQL Script and VBScript and it returned both rows. Does
this work in your environment? In any case, you might consider storing the
encrypted value as binary rather than character data.

CREATE TABLE test2
( a char(15)
primary KEY CLUSTERED )

DECLARE @.Value1 char(15)
SET @.Value1 =
CAST(0x253B3E2E5B0A2C295D2F5C2D2C3D2F AS char(15))
DECLARE @.Value2 char(15)
SET @.Value2 =
CAST(0x5C303C5B5C0A7C5E5B5C5D7D7B3B5C AS char(15))

INSERT INTO test2 VALUES(@.Value1)
INSERT INTO test2 VALUES(@.Value2)

SELECT
CAST(a AS binary(15)),
a FROM test2
WHERE a IN(@.Value1, @.Value2)

' test VBScript
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB.1;" _ &
"Data Source=MyServer;" _ &
"Initial Catalog=MyDatabase;" _ &
"Integrated Security=SSPI"

dotest()
WScript.Echo "Done"

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.Execute(s)
Do While rs.EOF = false
WScript.Echo rs.Fields("a")
X = ""
For j = 1 To Len(rs.Fields("a"))
X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
Next
WScript.Echo X

s = "select * from test2 where a = '" & rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
If rs1.RecordCount = 0 Then
WScript.Echo "==not found=="
End If
rs.movenext
Loop
End Sub

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:402aaa48$0$199$75868355@.news.frii.net...
> Simon,
> Thanks for your reply.
> I show the exact data in ascii code. I am sure the source
> of my problem is not about mistyping. I first found the
> problem in my VB application. The following is a part of it.
> Sub dotest()
> Dim i, X, j, s
> s = "select * from test2"
> Set rs = cn.OpenRecordset(s, dbOpenDynaset)
> For i = 1 To rs.RecordCount
> Debug.Print rs!a
> X = ""
> For j = 1 To Len(rs!a)
> X = X & Asc(Mid(rs!a, j, 1)) & ";"
> Next
> Debug.Print X
> s = "select * from test2 where a = '" & rs!a & "'"
> Set rs1 = cn.OpenRecordset(s, dbOpenDynaset)
> If rs1.RecordCount = 0 Then
> Debug.Print "==not found=="
> End If
> rs.movenext
> Next
> End Sub
> Anita Hery
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dan Guzman,

Thanks for your reply.

Currently I am working with DAO library / ODBC.
I am not familiar with VB script. So, I tried
using ADO library 2.7 and made a small modification
like this:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim qd As New ADODB.Command
Dim s As String

Private Sub Form_Load()
cn.ConnectionString = "PROVIDER=SQLOLEDB;
SERVER=srv2003;UID=sqlreg;PWD=cas;DATABASE=cas1"
cn.Open
dotest
End Sub

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.Execute(s)
i = 0
Do While rs.EOF = False
i = i + 1
Debug.Print "Row " & i & " ----"
Debug.Print rs.Fields("a")
X = ""
For j = 1 To Len(rs.Fields("a"))
X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
Next
Debug.Print X

s = "select * from test2 where a = '" &
rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
If rs1.EOF Then ' .RecordCount = 0 Then
Debug.Print "==not found=="
End If
rs.MoveNext
Loop
End Sub

If I use my original data, the above code will
produce the same problem on the same row (row 2).
Below is the output list:

====================
Row 1 ----
%;>.[
,)]/\-,=/
37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
Row 2 ----
\0<[\
|^[\]}{;\
92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
==not found==
Row 3 ----
\0<[\ ,)? {=\?
92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
Row 4 ----
\0<[\ \%:_`- ]_
92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
Row 5 ----
\0<[\ \^:& }{;\
92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
Row 6 ----
\0[*_1>\_\ }{@.+
92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
Row 7 ----
].>^/. ]-=}<)^&
93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
Row 8 ----
]0_^{
}^~_|~{^_
93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
Row 9 ----
{
}{31{2|02~||
123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
=======================

The row 2 can be accessed by the query like this:

select * from test2
where a =
char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
char(123)+char(59)+char(92)

But, I will not use this way.

Anita

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi, Anita.

I was able to recreate your problem and narrowed it down to the
backslash/newline sequence in the character string. As a workaround, I used
a parameter rather than an embedded literal value. This technique also
eliminates the need to escape other special characters like single quotes.
Example below.

Dim cmd As New ADODB.Command
Dim parm1 As ADODB.parameter
cmd.CommandText = "SELECT * FROM test2 WHERE a = ?"
cmd.ActiveConnection = cn
Set parm1 = cmd.CreateParameter("@.Parm1", adChar, 1, 15, s)
cmd.Parameters.Append parm1
cmd.Execute

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:402b81e0$0$202$75868355@.news.frii.net...
> Dan Guzman,
> Thanks for your reply.
> Currently I am working with DAO library / ODBC.
> I am not familiar with VB script. So, I tried
> using ADO library 2.7 and made a small modification
> like this:
> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim qd As New ADODB.Command
> Dim s As String
> Private Sub Form_Load()
> cn.ConnectionString = "PROVIDER=SQLOLEDB;
> SERVER=srv2003;UID=sqlreg;PWD=cas;DATABASE=cas1"
> cn.Open
> dotest
> End Sub
> Sub dotest()
> Dim i, X, j, s
> s = "select * from test2"
> Set rs = cn.Execute(s)
> i = 0
> Do While rs.EOF = False
> i = i + 1
> Debug.Print "Row " & i & " ----"
> Debug.Print rs.Fields("a")
> X = ""
> For j = 1 To Len(rs.Fields("a"))
> X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
> Next
> Debug.Print X
> s = "select * from test2 where a = '" &
> rs.Fields("a") & "'"
> Set rs1 = cn.Execute(s)
> If rs1.EOF Then ' .RecordCount = 0 Then
> Debug.Print "==not found=="
> End If
> rs.MoveNext
> Loop
> End Sub
> If I use my original data, the above code will
> produce the same problem on the same row (row 2).
> Below is the output list:
> ====================
> Row 1 ----
> %;>.[
> ,)]/\-,=/
> 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> Row 2 ----
> \0<[\
> |^[\]}{;\
> 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> ==not found==
> Row 3 ----
> \0<[\ ,)? {=\?
> 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> Row 4 ----
> \0<[\ \%:_`- ]_
> 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> Row 5 ----
> \0<[\ \^:& }{;\
> 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> Row 6 ----
> \0[*_1>\_\ }{@.+
> 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> Row 7 ----
> ].>^/. ]-=}<)^&
> 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> Row 8 ----
> ]0_^{
> }^~_|~{^_
> 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> Row 9 ----
> {
> }{31{2|02~||
> 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> =======================
> The row 2 can be accessed by the query like this:
> select * from test2
> where a =
> char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
> char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
> char(123)+char(59)+char(92)
> But, I will not use this way.
> Anita
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dan,

You are absolutely right.

I think my problem is about query translation
on client side (VB). Perhaps, it is a bug
from Microsoft.

If the query is sent using direct method, like :
s = "select * from test2 where a = '" & _
rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
then it is not guaranteed to work.

I made QA trial to prove that my problem was not
caused by SQL2000 :

CREATE TABLE test3
( a char(15)
primary KEY CLUSTERED,
b char(1) )

INSERT INTO test3
SELECT a, '2' as b FROM test2

--set b='1' on the second row
UPDATE test3 SET b = '1'
WHERE a =
char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
char(123)+char(59)+char(92)

DECLARE @.Value1 char(15)

SELECT @.value1 = a
FROM test3 where b = '1'

--successful second row access
SELECT * FROM test3 where a = @.Value1

Thanks for your reply

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||SQL Server MVP Steve Kass pointed out to me that this behavior is described
in MSKB 164291
<http://support.microsoft.com/default.aspx?scid=kb;en-us;164291>.
Basically, SQL Server interprets a backslash followed a newline as a literal
continuation escape sequence so these characters are ignored in the literal
string. You can repro this in Query Analyzer with the following script:

SELECT 'Continu\
ed string'
GO

Although this behavior is also described in the Books Online in the Embedded
SQL for C section <esqlforc.chm::/ec_6_epr_02_101f.htm>, it is not mentioned
elsewhere as it probably should. A doc bug has been filed so the
documentation issue should be addressed in the future.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:403007ad$0$195$75868355@.news.frii.net...
> Dan,
> You are absolutely right.
> I think my problem is about query translation
> on client side (VB). Perhaps, it is a bug
> from Microsoft.
> If the query is sent using direct method, like :
> s = "select * from test2 where a = '" & _
> rs.Fields("a") & "'"
> Set rs1 = cn.Execute(s)
> then it is not guaranteed to work.
> I made QA trial to prove that my problem was not
> caused by SQL2000 :
> CREATE TABLE test3
> ( a char(15)
> primary KEY CLUSTERED,
> b char(1) )
> INSERT INTO test3
> SELECT a, '2' as b FROM test2
> --set b='1' on the second row
> UPDATE test3 SET b = '1'
> WHERE a =
> char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
> char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
> char(123)+char(59)+char(92)
> DECLARE @.Value1 char(15)
> SELECT @.value1 = a
> FROM test3 where b = '1'
> --successful second row access
> SELECT * FROM test3 where a = @.Value1
> Thanks for your reply
> Anita Hery
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

nonindexed query & mem usage

1) for a given table that has around 26869141 rows, a query against a
nonindexed column such as << select * from table where somenonindexedcol =
25 >> seems to take a lot of memory as it runs. it must have to do a table
scan and store the results in memory, correct?
2) sql server never releases that memory to the os i think (even with a
max memory limit set)?
3) plus the query has the ability to go past that max limit as well?
4) the solution is to create a nonclustered index on that col, correct?
5) is it ok if i add more than one column to that nonclustered index?
im a developer, so your patience is appreciated.
thx
On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
<usenet.20.jimbo-black@.antichef.net> wrote:
>1) for a given table that has around 26869141 rows, a query against a
>nonindexed column such as << select * from table where somenonindexedcol =
>25 >> seems to take a lot of memory as it runs. it must have to do a table
>scan and store the results in memory, correct?
Well, if it's *around* 26m, then yes.
It doesn't store the results in memory, but as long as it has to read
them into memory to look at momentarily, it uses available memory to
cache them, just in case you ask for them again soon.

>2) sql server never releases that memory to the os i think (even with a
>max memory limit set)?
It will release it depending on competing demands.

>3) plus the query has the ability to go past that max limit as well?
If you mean, can your table be larger than your RAM, certainly yes!

>4) the solution is to create a nonclustered index on that col, correct?
Maybe.
It will help if the statistics on the distribution of values is such
that SQLServer thinks it's going to be cheaper to use the index. This
is generally the case if you're going to return just a few rows. But
if your query is going to return more than about 20% of the table, an
index won't help, scans are generally much faster. If the values
being selected are all contiguous, a clustered index may still be
faster than a complete scan. As always, "it depends" is applicable.

>5) is it ok if i add more than one column to that nonclustered index?
OK by me.
SQLServer only remembers the statistics for the first column, and an
index on two columns is less efficient at fetching values selected by
only one column, but if your query is by two fields, putting an index
on both at once is called a "covering index" and becomes an excellent
idea!

>im a developer, so your patience is appreciated.
I hear that.
J.
|||jxstern <jxstern@.nowhere.xyz> wrote in
news:t1o6q19libh9i8cucou27cgce5254e52p3@.4ax.com:

> On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
> <usenet.20.jimbo-black@.antichef.net> wrote:
thx just to confirm

> Well, if it's *around* 26m, then yes.
> It doesn't store the results in memory, but as long as it has to read
> them into memory to look at momentarily, it uses available memory to
> cache them, just in case you ask for them again soon.
yeah i understand. that explains why i saw the memory usage go way up
after that query ran.

> It will release it depending on competing demands.
well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
went down to the pre-query state around 650M. so maybe i'm
misunderstanding something here. i would think that it would go back down
to around 65om after a bit.

> Maybe.
> It will help if the statistics on the distribution of values is such
> that SQLServer thinks it's going to be cheaper to use the index. This
> is generally the case if you're going to return just a few rows. But
> if your query is going to return more than about 20% of the table, an
> index won't help, scans are generally much faster. If the values
> being selected are all contiguous, a clustered index may still be
> faster than a complete scan. As always, "it depends" is applicable.
it will return probably around 3 to 90 rows at the max. so the index will
probably help me out most in this situation.

> OK by me.
> SQLServer only remembers the statistics for the first column, and an
> index on two columns is less efficient at fetching values selected by
> only one column, but if your query is by two fields, putting an index
> on both at once is called a "covering index" and becomes an excellent
> idea!
so if i have 2 queries that use 2 diff cols, then its better to create 2
seperate nonclustered indexes?
thx much
|||Some comments inline:
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns972EE3850DCF4usenetjb@.207.115.17.102...
> jxstern <jxstern@.nowhere.xyz> wrote in
> news:t1o6q19libh9i8cucou27cgce5254e52p3@.4ax.com:
>
> thx just to confirm
>
> yeah i understand. that explains why i saw the memory usage go way up
> after that query ran.
>
> well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
> went down to the pre-query state around 650M. so maybe i'm
> misunderstanding something here. i would think that it would go back down
> to around 65om after a bit.
Why? How would SQL Server know that you won't refer to those particular pages soon again? The
purpose of caching data is for the data to be in cache next tome someone refers to the page. SQL
Server will not release pages *unless the machine is memory constrained*. See:
http://support.microsoft.com/default...;en-us;q321363
http://www.mssqlserver.com/faq/troub...memoryleak.asp

>
> it will return probably around 3 to 90 rows at the max. so the index will
> probably help me out most in this situation.
>
> so if i have 2 queries that use 2 diff cols, then its better to create 2
> seperate nonclustered indexes?
Possibly. You create indexes to support your queries. In order to comment what is best for you we
would know what the queries you want to support look like.

> thx much

nonindexed query & mem usage

1) for a given table that has around 26869141 rows, a query against a
nonindexed column such as << select * from table where somenonindexedcol =
25 >> seems to take a lot of memory as it runs. it must have to do a table
scan and store the results in memory, correct?
2) sql server never releases that memory to the os i think (even with a
max memory limit set)?
3) plus the query has the ability to go past that max limit as well?
4) the solution is to create a nonclustered index on that col, correct?
5) is it ok if i add more than one column to that nonclustered index?
im a developer, so your patience is appreciated.
thxOn Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
<usenet.20.jimbo-black@.antichef.net> wrote:
>1) for a given table that has around 26869141 rows, a query against a
>nonindexed column such as << select * from table where somenonindexedcol =
>25 >> seems to take a lot of memory as it runs. it must have to do a table
>scan and store the results in memory, correct?
Well, if it's *around* 26m, then yes.
It doesn't store the results in memory, but as long as it has to read
them into memory to look at momentarily, it uses available memory to
cache them, just in case you ask for them again soon.

>2) sql server never releases that memory to the os i think (even with a
>max memory limit set)?
It will release it depending on competing demands.

>3) plus the query has the ability to go past that max limit as well?
If you mean, can your table be larger than your RAM, certainly yes!

>4) the solution is to create a nonclustered index on that col, correct?
Maybe.
It will help if the statistics on the distribution of values is such
that SQLServer thinks it's going to be cheaper to use the index. This
is generally the case if you're going to return just a few rows. But
if your query is going to return more than about 20% of the table, an
index won't help, scans are generally much faster. If the values
being selected are all contiguous, a clustered index may still be
faster than a complete scan. As always, "it depends" is applicable.

>5) is it ok if i add more than one column to that nonclustered index?
OK by me.
SQLServer only remembers the statistics for the first column, and an
index on two columns is less efficient at fetching values selected by
only one column, but if your query is by two fields, putting an index
on both at once is called a "covering index" and becomes an excellent
idea!

>im a developer, so your patience is appreciated.
I hear that.
J.|||jxstern <jxstern@.nowhere.xyz> wrote in
news:t1o6q19libh9i8cucou27cgce5254e52p3@.
4ax.com:

> On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
> <usenet.20.jimbo-black@.antichef.net> wrote:
thx just to confirm

> Well, if it's *around* 26m, then yes.
> It doesn't store the results in memory, but as long as it has to read
> them into memory to look at momentarily, it uses available memory to
> cache them, just in case you ask for them again soon.
yeah i understand. that explains why i saw the memory usage go way up
after that query ran.

> It will release it depending on competing demands.
well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
went down to the pre-query state around 650M. so maybe i'm
misunderstanding something here. i would think that it would go back down
to around 65om after a bit.

> Maybe.
> It will help if the statistics on the distribution of values is such
> that SQLServer thinks it's going to be cheaper to use the index. This
> is generally the case if you're going to return just a few rows. But
> if your query is going to return more than about 20% of the table, an
> index won't help, scans are generally much faster. If the values
> being selected are all contiguous, a clustered index may still be
> faster than a complete scan. As always, "it depends" is applicable.
it will return probably around 3 to 90 rows at the max. so the index will
probably help me out most in this situation.

> OK by me.
> SQLServer only remembers the statistics for the first column, and an
> index on two columns is less efficient at fetching values selected by
> only one column, but if your query is by two fields, putting an index
> on both at once is called a "covering index" and becomes an excellent
> idea!
so if i have 2 queries that use 2 diff cols, then its better to create 2
seperate nonclustered indexes?
thx much|||Some comments inline:
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns972EE3850DCF4usenetjb@.207.115.17.102...
> jxstern <jxstern@.nowhere.xyz> wrote in
> news:t1o6q19libh9i8cucou27cgce5254e52p3@.
4ax.com:
>
> thx just to confirm
>
> yeah i understand. that explains why i saw the memory usage go way up
> after that query ran.
>
> well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
> went down to the pre-query state around 650M. so maybe i'm
> misunderstanding something here. i would think that it would go back down
> to around 65om after a bit.
Why? How would SQL Server know that you won't refer to those particular page
s soon again? The
purpose of caching data is for the data to be in cache next tome someone ref
ers to the page. SQL
Server will not release pages *unless the machine is memory constrained*. Se
e:
http://support.microsoft.com/defaul...b;en-us;q321363
http://www.mssqlserver.com/faq/trou...-memoryleak.asp

>
> it will return probably around 3 to 90 rows at the max. so the index will
> probably help me out most in this situation.
>
> so if i have 2 queries that use 2 diff cols, then its better to create 2
> seperate nonclustered indexes?
Possibly. You create indexes to support your queries. In order to comment wh
at is best for you we
would know what the queries you want to support look like.

> thx muchsql

nonindexed query & mem usage

1) for a given table that has around 26869141 rows, a query against a
nonindexed column such as << select * from table where somenonindexedcol = 25 >> seems to take a lot of memory as it runs. it must have to do a table
scan and store the results in memory, correct?
2) sql server never releases that memory to the os i think (even with a
max memory limit set)?
3) plus the query has the ability to go past that max limit as well?
4) the solution is to create a nonclustered index on that col, correct?
5) is it ok if i add more than one column to that nonclustered index?
im a developer, so your patience is appreciated.
thxOn Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
<usenet.20.jimbo-black@.antichef.net> wrote:
>1) for a given table that has around 26869141 rows, a query against a
>nonindexed column such as << select * from table where somenonindexedcol =>25 >> seems to take a lot of memory as it runs. it must have to do a table
>scan and store the results in memory, correct?
Well, if it's *around* 26m, then yes. :)
It doesn't store the results in memory, but as long as it has to read
them into memory to look at momentarily, it uses available memory to
cache them, just in case you ask for them again soon.
>2) sql server never releases that memory to the os i think (even with a
>max memory limit set)?
It will release it depending on competing demands.
>3) plus the query has the ability to go past that max limit as well?
If you mean, can your table be larger than your RAM, certainly yes!
>4) the solution is to create a nonclustered index on that col, correct?
Maybe.
It will help if the statistics on the distribution of values is such
that SQLServer thinks it's going to be cheaper to use the index. This
is generally the case if you're going to return just a few rows. But
if your query is going to return more than about 20% of the table, an
index won't help, scans are generally much faster. If the values
being selected are all contiguous, a clustered index may still be
faster than a complete scan. As always, "it depends" is applicable.
>5) is it ok if i add more than one column to that nonclustered index?
OK by me.
SQLServer only remembers the statistics for the first column, and an
index on two columns is less efficient at fetching values selected by
only one column, but if your query is by two fields, putting an index
on both at once is called a "covering index" and becomes an excellent
idea!
>im a developer, so your patience is appreciated.
I hear that.
J.|||jxstern <jxstern@.nowhere.xyz> wrote in
news:t1o6q19libh9i8cucou27cgce5254e52p3@.4ax.com:
> On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
> <usenet.20.jimbo-black@.antichef.net> wrote:
thx just to confirm
>>1) for a given table that has around 26869141 rows, a query against a
>>nonindexed column such as << select * from table where
>>somenonindexedcol = 25 >> seems to take a lot of memory as it runs.
>>it must have to do a table scan and store the results in memory,
>>correct?
> Well, if it's *around* 26m, then yes. :)
> It doesn't store the results in memory, but as long as it has to read
> them into memory to look at momentarily, it uses available memory to
> cache them, just in case you ask for them again soon.
yeah i understand. that explains why i saw the memory usage go way up
after that query ran.
>>2) sql server never releases that memory to the os i think (even with
>>a max memory limit set)?
> It will release it depending on competing demands.
well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
went down to the pre-query state around 650M. so maybe i'm
misunderstanding something here. i would think that it would go back down
to around 65om after a bit.
>>4) the solution is to create a nonclustered index on that col,
>>correct?
> Maybe.
> It will help if the statistics on the distribution of values is such
> that SQLServer thinks it's going to be cheaper to use the index. This
> is generally the case if you're going to return just a few rows. But
> if your query is going to return more than about 20% of the table, an
> index won't help, scans are generally much faster. If the values
> being selected are all contiguous, a clustered index may still be
> faster than a complete scan. As always, "it depends" is applicable.
it will return probably around 3 to 90 rows at the max. so the index will
probably help me out most in this situation.
>>5) is it ok if i add more than one column to that nonclustered index?
> OK by me.
> SQLServer only remembers the statistics for the first column, and an
> index on two columns is less efficient at fetching values selected by
> only one column, but if your query is by two fields, putting an index
> on both at once is called a "covering index" and becomes an excellent
> idea!
so if i have 2 queries that use 2 diff cols, then its better to create 2
seperate nonclustered indexes?
thx much|||Some comments inline:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns972EE3850DCF4usenetjb@.207.115.17.102...
> jxstern <jxstern@.nowhere.xyz> wrote in
> news:t1o6q19libh9i8cucou27cgce5254e52p3@.4ax.com:
>> On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
>> <usenet.20.jimbo-black@.antichef.net> wrote:
> thx just to confirm
>>1) for a given table that has around 26869141 rows, a query against a
>>nonindexed column such as << select * from table where
>>somenonindexedcol = 25 >> seems to take a lot of memory as it runs.
>>it must have to do a table scan and store the results in memory,
>>correct?
>> Well, if it's *around* 26m, then yes. :)
>> It doesn't store the results in memory, but as long as it has to read
>> them into memory to look at momentarily, it uses available memory to
>> cache them, just in case you ask for them again soon.
> yeah i understand. that explains why i saw the memory usage go way up
> after that query ran.
>
>>2) sql server never releases that memory to the os i think (even with
>>a max memory limit set)?
>> It will release it depending on competing demands.
> well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
> went down to the pre-query state around 650M. so maybe i'm
> misunderstanding something here. i would think that it would go back down
> to around 65om after a bit.
Why? How would SQL Server know that you won't refer to those particular pages soon again? The
purpose of caching data is for the data to be in cache next tome someone refers to the page. SQL
Server will not release pages *unless the machine is memory constrained*. See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
>>4) the solution is to create a nonclustered index on that col,
>>correct?
>> Maybe.
>> It will help if the statistics on the distribution of values is such
>> that SQLServer thinks it's going to be cheaper to use the index. This
>> is generally the case if you're going to return just a few rows. But
>> if your query is going to return more than about 20% of the table, an
>> index won't help, scans are generally much faster. If the values
>> being selected are all contiguous, a clustered index may still be
>> faster than a complete scan. As always, "it depends" is applicable.
> it will return probably around 3 to 90 rows at the max. so the index will
> probably help me out most in this situation.
>
>>5) is it ok if i add more than one column to that nonclustered index?
>> OK by me.
>> SQLServer only remembers the statistics for the first column, and an
>> index on two columns is less efficient at fetching values selected by
>> only one column, but if your query is by two fields, putting an index
>> on both at once is called a "covering index" and becomes an excellent
>> idea!
> so if i have 2 queries that use 2 diff cols, then its better to create 2
> seperate nonclustered indexes?
Possibly. You create indexes to support your queries. In order to comment what is best for you we
would know what the queries you want to support look like.
> thx much

NON-DETERMINISTIC?

I am trying to create a unique constraint on a computed column. I've tried
unique index, too, but they both fail w/this error:
Server: Msg 1933, Level 16, State 1, Line 2
Cannot create index because the key column 'msgID' is non-deterministic or
imprecise.
ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had to
use REPLACE in order to get rid of the date characters like MM/DD/YY. the
formula for MsgID is: (rtrim([endpoint]) +
replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
BOL says 1) all functions referenced by the expression are deterministic and
precise. 2) all columns referenced in the expression come from the table
containing the computed column and 3) no column reference pulls data from
multiple rows.
All of which I believe I'm good on. Each of these are SET ON:
ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
and NUMERIC_ROUNDABORT is SET OFF.
can somebody help me find what I'm missing?
-- LynnCan you give your table structure and some sample data?
http://www.aspfaq.com/5006
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Also, if you create a primary key or unique constraint on the three base
columns, do you really need the computed column to be explicitly unique
(since it should be unique by definition anyway)? I am often amazed at this
desire to store computed values when you don't have to; views and queries
could easily construct this value on select instead of storing redundant
data...
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Lynn,
The dateformat "1" uses a cutoff year which makes it imprecise.
The following example shows the problem and the solution.
create table #t(mydate datetime not null
,displaydate as convert(varchar(8),mydate,1)
)
create unique index someindex on #t(displaydate)
create table #t2(mydate datetime not null
,displaydate as
substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(10)
,mydate,101),9,2)
)
create unique index someindex2 on #t2(displaydate)
drop table #t
drop table #t2
Gert-Jan
Lynn wrote:
> I am trying to create a unique constraint on a computed column. I've trie
d
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had t
o
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic a
nd
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Gert-Jan, doing that, my value becomes this: 08/26/05
I need this MMDDYY, w/out the forward slashes in there.
is there no way to do that w/out becoming imprecise?
-- Lynn
"Gert-Jan Strik" wrote:

> Lynn,
> The dateformat "1" uses a cutoff year which makes it imprecise.
> The following example shows the problem and the solution.
> create table #t(mydate datetime not null
> ,displaydate as convert(varchar(8),mydate,1)
> )
> create unique index someindex on #t(displaydate)
> create table #t2(mydate datetime not null
> ,displaydate as
> substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(1
0),mydate,101),9,2)
> )
> create unique index someindex2 on #t2(displaydate)
> drop table #t
> drop table #t2
>
> Gert-Jan
>
> Lynn wrote:
>|||Yes, Aaron, unfortunately I do need it, as the composite PK of the three
columns invites duplicates. Meaning, it's endpoint+YYYY-MM-DD
HH:MM:MS:000+orderno. As weird as it may sound, the presence of the time
along w/the date is not desirable because like i said, it invites dupes.
Yes, I know the date w/out the time would seem as though it would do the sam
e
-- but it's something a little native to us, I suppose. So anyway, the
composite pk/constraint won't do, unless there's some way that I am unaware
of that will allow me to strip the time from the datestamp in the
constraint/pk.
can I do that? create a uniqe constraint and/or pk and/or unique index
(ideally, the constraint) on the three columns, but strip the time from the
exectime column?
exectime+endpoint+orderno
-- Lynn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also, if you create a primary key or unique constraint on the three base
> columns, do you really need the computed column to be explicitly unique
> (since it should be unique by definition anyway)? I am often amazed at th
is
> desire to store computed values when you don't have to; views and queries
> could easily construct this value on select instead of storing redundant
> data...
>
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>
>|||What do you hope to gain from this particular format that cannot also be
accomplished using one of the standard ones. For constraint purposes, the
format of the date is not important. Does the computed column need to be in
this format for visual purposes? If so, why not create a 2nd column for
constraint purposes only.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
> Gert-Jan, doing that, my value becomes this: 08/26/05
> I need this MMDDYY, w/out the forward slashes in there.
> is there no way to do that w/out becoming imprecise?
>
> -- Lynn
>
> "Gert-Jan Strik" wrote:
>|||This particular format is our uniqueID. W/the time, however, it is invalid
for business/application reasons. I started down this path hoping to do a
PK, but learned I could not do a PK on a computed column. Hence, I am tryin
g
both the unique constraint or the unique index, both of which fail w/the
non-deterministic problem. So, possibly for constraint purposes the format
of the date is not important. But it is for our purposes. I am hopeful
that I am in error or possibly missing something quite obvious, but I need
the time stripped from the datetime stamp in the value, whether constraint,
computed or otherwise.
-- Lynn
"Scott Morris" wrote:

> What do you hope to gain from this particular format that cannot also be
> accomplished using one of the standard ones. For constraint purposes, the
> format of the date is not important. Does the computed column need to be
in
> this format for visual purposes? If so, why not create a 2nd column for
> constraint purposes only.
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
>
>|||On Fri, 26 Aug 2005 12:49:04 -0400, "Scott Morris" <bogus@.bogus.com>
wrote:
>What do you hope to gain from this particular format that cannot also be
>accomplished using one of the standard ones. For constraint purposes, the
>format of the date is not important. Does the computed column need to be i
n
>this format for visual purposes? If so, why not create a 2nd column for
>constraint purposes only.
What he said.
J.

Non Usesfull error Message

ERROR MSG:

Msg 515, Level 16, State 2, Server SQL\CIS, Procedure ssp_gas_CustomerCancellation_A, Line 161

Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

The statement has been terminated.

Why didn't it actually tell me the Column and table name? i've never seen this before?

Anyone can help

Thanks

Marios

Oh I know this isn't the right forum but i couldn't find the right one to post in.

How is the procedure executed?

Not sure about why the error message is like that. Are there many fields in the table that do not allow nulls, I'd start checking those.

|||

It is called from another Stored Procedure the Stored Procedure that it rasises the error in is about 600lines of code and there are serval places where it Inserts int Tables and Temp Tables. I've looked at the data passed and which fields they are passed in to but i don't see where this error happends. It has only happend in our Production Database i can not so far reproduce it in our development environment. I don't understand why it doesn't show me the column and table

Thanks

Marios

|||

Do you explicity create your temporary tables or create/populate them through select into statments? Maybe that is why it doesn't show the table/column name (the fact that that it is a temporary table).

Nevermind, just tested that and it doesn't seem to be the case.

|||

no i explicitly defne the tables for temp tables before using them and but they are declare

CREATE #TableName

(

)

|||The only other thing I can think of, and I realize it may be impractical due to the large amount of code within the stored procedure is to use try...catch statements.|||i can't because it's a SQL 2000 server

Non significant '0' removed from alphanumeric code

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

Double check all of your metadata links and double check on the advanced editor screens that you don't inadvertently have that column cast as a numeric data type.|||Already done twice! ;-)|||

Sbastien Nunes wrote:

Already done twice! ;-)

Then I'm missing something. A DT_WSTR will not drop leading zeros.|||Are you using SQL in your lookup transformation?|||Yes I am.|||

Sebastion,

Did you get this resolved?

Did you try using data viewers across the Data flow to see at what point the leading zero gets removed...what type of destination are you using...may the probelm be there?

|||

Hi,

No it's not resolved. Yes I tried using Data Viewers, the column where the 0 is removed is an output column from a Lookup Transformation. The Lookup is based on a SQL query.

I have checked the MetaData and everything seems allright.

Thanks for your help.

|||

That is weird...

What type of OLE DB source you are using in the lookup transform?

Did you get same results when running the same query in a native query tool (e.g. SSMS if SQL Server)?

What happens if you try to explicitly cast that column to string type in the lookup query?

|||

Thanks for your answer Rafael, I didn't had the time to test that today, I'll try on monday and I'll let you know what happens.

Non significant '0' removed from alphanumeric code

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

Double check all of your metadata links and double check on the advanced editor screens that you don't inadvertently have that column cast as a numeric data type.|||Already done twice! ;-)|||

Sbastien Nunes wrote:

Already done twice! ;-)

Then I'm missing something. A DT_WSTR will not drop leading zeros.|||Are you using SQL in your lookup transformation?|||Yes I am.|||

Sebastion,

Did you get this resolved?

Did you try using data viewers across the Data flow to see at what point the leading zero gets removed...what type of destination are you using...may the probelm be there?

|||

Hi,

No it's not resolved. Yes I tried using Data Viewers, the column where the 0 is removed is an output column from a Lookup Transformation. The Lookup is based on a SQL query.

I have checked the MetaData and everything seems allright.

Thanks for your help.

|||

That is weird...

What type of OLE DB source you are using in the lookup transform?

Did you get same results when running the same query in a native query tool (e.g. SSMS if SQL Server)?

What happens if you try to explicitly cast that column to string type in the lookup query?

|||

Thanks for your answer Rafael, I didn't had the time to test that today, I'll try on monday and I'll let you know what happens.

Non deterministic function?

Gentlemen What is "Non_Deterministic" about the function below?

I pass DATETIME Column and a DECIMAL column ti the function. It keeps yelling at me saying it is a non-deterministic function.

I am using this function to PERSIST a Computed Column.

I have tried converting all NVARCHARs to VARCHARs.

Tredi returning a VARCHAR instead of a DATETIME, but still did not succeed.

Am I doing something wrong, I must be.....

CREATE FUNCTION [dbo].[udf_GetDateTime](@.Date DATETIME, @.TimeDecimal DECIMAL)

RETURNS DATETIME

AS

BEGIN

DECLARE @.DateStr NVARCHAR(23)

DECLARE @.TimeStr NVARCHAR(12)

DECLARE @.DateTimeResult DATETIME

SET @.TimeStr = RIGHT('000000' + CONVERT(NVARCHAR(6), @.TimeDecimal), 6)

SET @.DateStr = CONVERT(NVARCHAR(10), @.Date, 120) + ' ' +

SUBSTRING(@.TimeStr, 1, 2) + ':' +

SUBSTRING(@.TimeStr, 3, 2) + ':' +

SUBSTRING(@.TimeStr, 5, 2)

RETURN CONVERT(DATETIME, @.DateStr, 120)

END

I've tried on my sql servers, 2000 and 2005, and worked correctly on both of them.
The code I used to test your udf (with Management studio) is the following:

declare @.d datetime
set @.d = getdate()
print dbo.[udf_GetDateTime] (@.d, 1)|||

Could you please explain Carlop -- what you are trying to convey.

So you think the above function is Deterministic Or Non-Deterministic.

I know it works, but when I use the function to create a Computed Column (persisted), it yells saying I cannot do that -- since the function is non-deterministic.

|||

It is not deterministic because the UDF is not created with the SCHEMABINDING option. Without this option, the database engine has no way of knowing if the definition of the module has changed after it was created and so on. You can check deterministic property of modules by using OBJECTPROPERTY(OBJECT_ID('<your_module>'), 'IsDeterministic'). So create the function with SCHEMABINDING to make it deterministic.

Also, it appears that the use of scalar UDF in this case is unnecessary. You can simply use an expression to get the result you need in a computed column. Note that using scalar UDFs for simple operations has lot of overhead and hurts performance. So you will be better off using expression in computed column directly.

|||If you have to wrap the function up you are best to use a SQLCLR function. These perform better that TSQL UDFs, however as has been stated, bot perform worse than using an expression directly.|||Sorry but I didn't noticed that you used it to persist a column, so I've misunderstood the usage...

Non Clustered Index

Hi ,
When I creare a non clustered index , I get error as show below ,
Server: Msg 1904, Level 16, State 1, Line 1
Cannot specify more than 16 column names for statistics or index key list.
21 specified.
SQL Server only support up to 16 key values ?
Travis Tan
First off, this question should be posted in
Microsoft.public.sqlserver.programming. Clustering is a technology which
allows individual computers to share the same data and back each other up to
prevent system failures. Clients connect to a virtual server, whose
resources float between nodes which form the cluster.
Clustered indexes are when the data is clustered or grouped in a predefined
format or order for rapid retrieval of ranges of data.
16 columns makes your index very large and I suspect inefficient. You may be
able to use an indexed view to group your data in different orders for your
particular usage. And yes, clustered indexes only support a maximum of 16
columns or keys in SQL 200x.
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
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan
|||Yes. Why are you trying to create an index with 21 columns in it? That is
quite a bit beyond overkill.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan
|||Hi
Wrong newsgroup, crossposted to microsoft.public.sqlserver.programming
Why would you want to create a compound index of 16 or more columns? Your
query has to be very specific to be able to use it and the overhead
maintaining it will be high too.
If you have a table, with columns A-Z and you build and index on A, B, C, D
(in that column sequence), a where clause on A, B, C could use the index, a
query on column B can't, neither can a query on C, D. Column A always has to
be involved as it is the 1st sort sequence for the column.
Maybe the DB design is not optimal if you need to go to such extremes.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan