No distinct in a select into stement ?

Dear MSSQL experts,

I use MSSQL 2000 and encountered a strange problem wqhile I tried to
use a select into statement .

If I perform the command command below I get only one dataset which has
the described properties.
If I use the same statement in a select into statement (see the second
select) I get several datasets with the described properties like I
didn't use distinct
Is there any posiibility to use destinct in a select into statement

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
from Variables where Title1 is not NULL or Title2 is not NULL or
Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or AggregationTitle4 is not NULL;

This is the same with select into :

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
into VarTitles from Variables where Title1 is not NULL or Title2 is
not NULL or Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or
AggregationTitle4 is not NULL;

Hope anyone can help.

Best regards,

Daniel Wetzler

IDaniel Wetzler wrote:
> Dear MSSQL experts,
> I use MSSQL 2000 and encountered a strange problem wqhile I tried to
> use a select into statement .
> If I perform the command command below I get only one dataset which has
> the described properties.
> If I use the same statement in a select into statement (see the second
> select) I get several datasets with the described properties like I
> didn't use distinct
> Is there any posiibility to use destinct in a select into statement
> select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
> Title2, Title3 as Title3,
> AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
> AggregationTitle2,
> AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
> AggregationTitle4
> from Variables where Title1 is not NULL or Title2 is not NULL or
> Title3 is not NULL or
> AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
> AggregationTitle3 is not NULL or AggregationTitle4 is not NULL;
>
> This is the same with select into :
> select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
> Title2, Title3 as Title3,
> AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
> AggregationTitle2,
> AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
> AggregationTitle4
> into VarTitles from Variables where Title1 is not NULL or Title2 is
> not NULL or Title3 is not NULL or
> AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
> AggregationTitle3 is not NULL or
> AggregationTitle4 is not NULL;
> Hope anyone can help.
> Best regards,
> Daniel Wetzler

The IDENTITY function makes each row unique so DISTINCT doesn't
eliminate the duplicates in this case. Interestingly, this behaviour
seems to have changed in SQL Server 2005. If I run your SELECT INTO on
2005 I get a different execution plan with the IDENTITY value computed
after DISTINCT.

For 2000 the workaround is easy. The following should insert just one
row into vartitles.

CREATE TABLE variables (title1 VARCHAR(10) NULL, title2 VARCHAR(10)
NULL, title3 VARCHAR(10) NULL, aggregationtitle1 VARCHAR(10) NULL,
aggregationtitle2 VARCHAR(10) NULL, aggregationtitle3 VARCHAR(10) NULL,
aggregationtitle4 VARCHAR(10) NULL);

INSERT INTO variables VALUES ('1','1','1','1','1','1','1');
INSERT INTO variables VALUES ('1','1','1','1','1','1','1');

SELECT IDENTITY (INT) AS id,
title1, title2, title3, aggregationtitle1, aggregationtitle2,
aggregationtitle3, aggregationtitle4
INTO VarTitles
FROM (
SELECT DISTINCT
title1, title2, title3, aggregationtitle1, aggregationtitle2,
aggregationtitle3, aggregationtitle4
FROM variables
WHERE title1 IS NOT NULL
OR title2 IS NOT NULL
OR title3 IS NOT NULL
OR aggregationtitle1 IS NOT NULL
OR aggregationtitle2 IS NOT NULL
OR aggregationtitle3 IS NOT NULL
OR aggregationtitle4 IS NOT NULL) AS V ;

SELECT * FROM vartitles ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Thank you very much.
This was a very helpful hint.

Best regards,

Daniel|||Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_title1, aggregation_title2,
aggregation_title3, aggregation_title4) IS NOT NULL

Unfortunately these columns look like repeated and a really bad 1NF
problem. I have the feeling that you might have wanted to use
COALESCE() in the SELECT list to get a non-null title and non-null
aggregation_title instead of this convoluted query.|||> Minor trick to make the code easier to read and maintain:
> WHERE COALESCE (title1, title2, title3) IS NOT NULL
> OR COALESCE (ggregation_title1, aggregation_title2,
> aggregation_title3, aggregation_title4) IS NOT NULL

and... bang goes performance too other than a probable clustered index scan
/ table scan.

Consider these two statements on my 800,000 row 834MByte message table for
the nntp forums...

There is a non-clustered index on nntp_author and there is non-clustered
index on author_id

-- Query 1
select count(*)
from mb_message_detail
where nntp_author is not null
or author_id is not null

-- Query 2
select count(*)
from mb_message_detail
where coalesce( nntp_author, author_id ) is not null

Query 1 will use the index author_id and give a half reasonable plan.
Query 2 will do a clustered index scan

Out of 100%, Query 1 is 12% and Query 2 is a whopping 88%

Seriously, go and get a junior job as a programmer and get some very needed
industrial / real world experience instead of bashing people down on here,
as far as 'newbie' goes - you have room to talk.....

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145969845.021110.74070@.t31g2000cwb.googlegro ups.com...
> Minor trick to make the code easier to read and maintain:
> WHERE COALESCE (title1, title2, title3) IS NOT NULL
> OR COALESCE (ggregation_title1, aggregation_title2,
> aggregation_title3, aggregation_title4) IS NOT NULL
> Unfortunately these columns look like repeated and a really bad 1NF
> problem. I have the feeling that you might have wanted to use
> COALESCE() in the SELECT list to get a non-null title and non-null
> aggregation_title instead of this convoluted query.|||-------
Seriously, go and get a junior job as a programmer and get some very
needed
industrial / real world experience instead of bashing people down on
here,
as far as 'newbie' goes - you have room to talk.....
-------

<BIG GRIN> from on who has been the receiving side of one of CELKO's
many put-downs.

No columns defined in rowset

Hello Experts,
I have a stored procedure. I used ADO to called this stored procedure. In
runtime, it says "No columns defined in rowset".
Here is my stored procedure:
========================================
========
CREATE PROCEDURE [sp_MigrateAccount]
@.i_vchSessionID VARCHAR(50),
@.o_intRetVal int OUTPUT
AS
-- Variables for error handling.
DECLARE @.intError int
DECLARE @.insAcct_error int
DECLARE @.insAcctSum_error int
DECLARE @.insAddr_error int
DECLARE @.drop_error int
DECLARE @.intRecordErrorCounter int
-- Temporary storage
DECLARE @.chSourceAccountID char(12)
DECLARE @.vchSourceAccount char(128)
DECLARE @.vchSourceMainPhone char(32)
DECLARE @.vchSourceFax char(32)
DECLARE @.chSourceUserfield5 char(80)
DECLARE @.m_cintMigrateSucceed int
DECLARE @.m_cintRecordDataError int
DECLARE @.m_cintInsertToRealTableError int
DECLARE @.m_cintDuplicateKeyError int
SET NOCOUNT ON
-- Assign value to constants
SET @.m_cintMigrateSucceed = 0
SET @.m_cintRecordDataError = 2
SET @.m_cintInsertToRealTableError = 3
SET @.m_cintDuplicateKeyError = 7
-- Perform data validation --
-- Check for duplicate ACCOUNTID
SET @.intRecordErrorCounter = 0
SELECT @.intRecordErrorCounter = COUNT (a1.ACCOUNTID)
FROM sysdba.ACCOUNT AS a1
WHERE a1.ACCOUNTID IN
(SELECT ACCOUNTID
FROM ACCOUNT_TEMP
WHERE SessionID = @.i_vchSessionID)
IF (@.intRecordErrorCounter > 0) -- There exists at least one validation
error.
BEGIN
-- Update ErrorColumn in the source table. --
-- 1 is an arbitrary value for the ErrorColumn.
-- If ErrorColumn is NULL or 0, this record has no error.
UPDATE sysdba.ACCOUNT_TEMP
SET ErrorColumn = @.m_cintDuplicateKeyError
WHERE (SessionID = @.i_vchSessionID)
AND ACCOUNTID IN
(SELECT ACCOUNTID
FROM ACCOUNT)
-- No need to update the destination table, thus we quit the SP
-- Note that the error number used by SQL Server is greater than 0.
SET @.intError = @.m_cintDuplicateKeyError
GOTO Quit_SP
END
-- Up to this point, the data validation is passed.
-- We can insert record into the destination table. now.
BEGIN TRAN dataMigration
-- Insert records to the ACCOUNT table
-- People from newsgroup said that you are required to
-- enter uppercase Account Name into ACCOUNT_UC field.
INSERT INTO ACCOUNT
(ACCOUNTID, ACCOUNT, MAINPHONE, FAX, [DESCRIPTION], SECCODEID,
ADDRESSID, SHIPPINGID, ACCOUNTMANAGERID, CREATEUSER, MODIFYUSER,
CREATEDATE,
MODIFYDATE, ACCOUNT_UC)
SELECT ACCOUNTID, ACCOUNT, MAINPHONE, FAX, [DESCRIPTION], SECCODEID,
ADDRESSID, SHIPPINGID, ACCOUNTMANAGERID, CREATEUSER, MODIFYUSER,
CREATEDATE,
MODIFYDATE, ACCOUNT_UC
FROM sysdba.ACCOUNT_TEMP
WHERE SessionID = @.i_vchSessionID
SET @.insAcct_error = @.@.ERROR
IF (0 = @.insAcct_error) BEGIN
COMMIT TRAN dataMigration
END
ELSE
BEGIN
IF (0 <> @.insAcct_error)
-- Const m_cintInsertToRealTableError = 3
SET @.intError = @.m_cintInsertToRealTableError
ROLLBACK TRAN dataMigration
END
Error_Handler:
Quit_SP:
IF (@.intError IS NULL)
-- No value assigned to @.intError in the
-- body of this stored procedure.
-- Thus, migrate data is successful.
-- Const m_cintMigrateSucceed = 0
SET @.o_intRetVal = @.m_cintMigrateSucceed
ELSE
SET @.o_intRetVal = @.intError -- Failure encountered.
RETURN
GO
========================================
=
Here is my VBScript that used ADO to call the store procedure:
========================================
===
' :
' ADO database connection....
' :
With ADOCmd
.CommandText = "sp_MigrateAccount"
.CommandType = adCmdStoredProc
strInputText = i_strSessionID
lngParamLen = Len(strInputText)
If lngParamLen <= 0 Then
lngParamLen = 1
End If
Set ADOParam = .CreateParameter("@.i_vchType", adVarChar,
adParamInput, lngParamLen, strInputText)
.Parameters.Append ADOParam
Set ADOParam = .CreateParameter("@.o_intRetVal", adInteger,
adParamOutput)
.Parameters.Append ADOParam
Set .ActiveConnection = objCon
.Execute
lngRetVal = .Parameters("@.o_intRetVal").Value
End With
' :
' :
' :
'=======================================
==
The ".Execute" statement caused the "No columns defined in rowset".
If I try the following statement:
.Execute , , (adCmdStoredProc Or adExecuteNoRecords)
Or,
.Execute , , adExecuteNoRecords
I cannot get the output value (@.o_intRetVal) from the stored procedure.
May I know how to solve this problem? Thanks a lot.
SammySa wrote:
> Hello Experts,
> I have a stored procedure. I used ADO to called this stored
> procedure. In runtime, it says "No columns defined in rowset".
That does not sound like an ADO error. It sounds more like a T-SQL error.
ADO errors usually refer to recordsets, fields and records. "rowset" makes
it sound
like a T-SQL error.
Have you verified that this procedure runs correctly in Query Analyzer? Like
this:
declare @.ret int
exec sp_MigrateAccount 'some session id',@.ret output
select @.ret [Output Value]

> Here is my stored procedure:
> ========================================
========
> CREATE PROCEDURE [sp_MigrateAccount]
Nothing to do with your error, but using "sp_" for a non-system stored
procedure can cause a slight performance dip, since the query engine treats
all procedures beginning with this prefix as system procedures, looking for
them first in Master, and only looking for them in the current database when
it fails to find them in Master. If you mistakenly name your procedure the
same as a real system procedure, you've got a knotty debugging problem ahead
of you ...

> @.i_vchSessionID VARCHAR(50),
> @.o_intRetVal int OUTPUT
> AS
> -- Variables for error handling.
> DECLARE @.intError int
<snip>
> SET NOCOUNT ON
I doubt this is the problem, but I generally make this the first statement
in my procedures - before any other statement, even DECLARE's.

>
<snip>
> ========================================
=
>
> Here is my VBScript that used ADO to call the store procedure:
> ========================================
===
> ' :
> ' ADO database connection....
> ' :
> With ADOCmd
> .CommandText = "sp_MigrateAccount"
> .CommandType = adCmdStoredProc
> strInputText = i_strSessionID
> lngParamLen = Len(strInputText)
> If lngParamLen <= 0 Then
> lngParamLen = 1
> End If
> Set ADOParam = .CreateParameter("@.i_vchType", adVarChar,
> adParamInput, lngParamLen, strInputText)
> .Parameters.Append ADOParam
> Set ADOParam = .CreateParameter("@.o_intRetVal",
> adInteger, adParamOutput)
> .Parameters.Append ADOParam
> Set .ActiveConnection = objCon
> .Execute
> lngRetVal = .Parameters("@.o_intRetVal").Value
> End With
> ' :
> ' :
> ' :
> '=======================================
==
> The ".Execute" statement caused the "No columns defined in rowset".
> If I try the following statement:
> .Execute , , (adCmdStoredProc Or adExecuteNoRecords)
Should be:
.Execute , , (adCmdStoredProc + adExecuteNoRecords)
HTH,
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.sql