array with sql server 2000

hello
I have created one store procedures that return a table variable

'CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
DECLARE @.tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @.colA nvarchar(50)
DECLARE @.MyCursor CURSOR
/*declare @.mycursor1 cursor*/

SET @.MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@.immobile_id
order by posizione
OPEN @.MyCursor
FETCH NEXT FROM @.MyCursor
INTO @.ColA
WHILE @.@.FETCH_STATUS = 0
BEGIN

Insert @.tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @.ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@.immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @.MyCursor
INTO @.ColA
END

CLOSE @.MyCursor
DEALLOCATE @.MyCursor

select * from @.tbl

/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
Dim rs as new adodb.recordset
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you

Could you please provide more details?

Thanks

|||

CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
SET NOCOUNT ON

-- Original text followed

|||I guess using SET NOCOUNT ON resolved the problem. Several TSQL statements can produce results or messages. So to suppress some of the unwanted messages and read just the SELECT statement output for example you need to SET NOCOUNT ON in the SP. This will eliminate the DONE messages send to the client after the SELECT @.d = ... statement for example. See Books Online for more details on SET NOCOUNT ON effect on returning resultsets.

array with sql server 2000

hello
I have created one store procedures that return a table variable

'CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
DECLARE @.tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @.colA nvarchar(50)
DECLARE @.MyCursor CURSOR
/*declare @.mycursor1 cursor*/

SET @.MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@.immobile_id
order by posizione
OPEN @.MyCursor
FETCH NEXT FROM @.MyCursor
INTO @.ColA
WHILE @.@.FETCH_STATUS = 0
BEGIN

Insert @.tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @.ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@.immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @.MyCursor
INTO @.ColA
END

CLOSE @.MyCursor
DEALLOCATE @.MyCursor

select * from @.tbl

/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
Dim rs as new adodb.recordset
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you

Could you please provide more details?

Thanks

|||

CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
SET NOCOUNT ON

-- Original text followed

|||I guess using SET NOCOUNT ON resolved the problem. Several TSQL statements can produce results or messages. So to suppress some of the unwanted messages and read just the SELECT statement output for example you need to SET NOCOUNT ON in the SP. This will eliminate the DONE messages send to the client after the SELECT @.d = ... statement for example. See Books Online for more details on SET NOCOUNT ON effect on returning resultsets.

array

How do you define an array of varchar in a table and how do you manupiluate
it? Thanks.00KobeBrian wrote:
> How do you define an array of varchar in a table and how do you manupiluat
e
> it? Thanks.
There is no array type in SQL Server. It makes sense to eliminate any
arrays from your logical model, i.e. use a table for a set of varchar
values instead of an array.
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/ms130214(en-US,SQL.90).aspx
--

array

How do you define an array of varchar in a table and how do you manupiluate
it? Thanks.00KobeBrian wrote:
> How do you define an array of varchar in a table and how do you manupiluate
> it? Thanks.
There is no array type in SQL Server. It makes sense to eliminate any
arrays from your logical model, i.e. use a table for a set of varchar
values instead of an array.
--
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/ms130214(en-US,SQL.90).aspx
--

Aritmectic Operator in Formula?

Hi all,

this formula doesn't work as calculated column but WHY?

Column [MAIN]: VARCHAR(150)

VALUE : 'blabla - zobzob'

SUBSTRING([MAIN],1,CHARINDEX('-',[MAIN])) WORKS and return 'blabla -'

BUT

SUBSTRING([MAIN],1,CHARINDEX('-',[MAIN])-2) DOESN'T WORK in a Formula BUT well in a select clause...

Any Idea?

Thanks

In what way does it 'not work'...?

It seems to work ok... (SQL Server 2000)

create table #x (main varchar(20) not null)

insert #x select 'blabla - zobzob'

alter table #x add b as SUBSTRING(MAIN,1,CHARINDEX('-',MAIN)-2)

select * from #x

drop table #x

main b
-- --
blabla - zobzob blabla

/Kenneth

|||

Yes indeed, it works with a sql script but NOT manually in SQL Server 2000... Very Strange...

Tks a lot

|||

Hmm.. what do you mean by 'manually'..?

In my mind T-SQL code in QA is as 'manual' as you can get..?

/Kenneth

|||With the editor provided in SQL Server 2000...|||

You mean Enterprise Manager?

I'd suggest as a solution that you don't use it for other purposes than viewing.
For one thing it doesn't allow you any transactional control when you perform write operations, and some things it does in an awkward way, which adds up to that it takes unnecessary long time and/or large amount of resources to complete.

Make the wrong choice in EM and chances increase that you're looking for the latest good backup, instead of just typing ROLLBACK in QA, when something unexpected happened with the latest change. This ability is due to the fact that you can start your scripts with BEGIN TRAN (don't forget it) before actually performing the change. This is something that EM doesn't give you. =:o/

EM is imo not a fitting tool for database change management. (perhaps with the exception of user and roles management)

Do all your (data/schema) changes in Query Analyzer by controlled scripts instead.

/Kenneth

Are text pages deleted when a column is converted to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?

If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?
..morriszone@.hotmail.com (Steve Morris) wrote in message news:<757b6d8f.0404080820.7956cf62@.posting.google.com>...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?
> .

just try:

select * into your_table_bak from your_table

and then try your alter table statment with no risk of dataloss!
alter table *your_table_bak* :-)

if you dont like to read 8000 charakter to find out wether some data
is loss or not try the function
"getsize"

hth