Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts

Tuesday, March 20, 2012

[varchar] (100) and empty space

Hello

I am creating the following table

CREATE TABLE [dbo].[myT] (
[name] [varchar] (100) NULL
)

if I do

INSERT INTO myT (name) VALUES ('A')

i get in the table a column with

A ...

the 100 char place is full even if there is a data with only 1 char

how is it possible to avoid it ?
I want 100 char maximum but not full with nothing

thank you for helpingWhat do you get when you run this?:

select len([Name]), '[' + [Name] + ']' from myT

Name is a reserved word, and so it is not a good label for a column, but I don't think this would cause the problem you are seeing.|||the 100 char place is full even if there is a data with only 1 charHow do you know?

Could this be a display issue of whatever client program you use to display the data?
Only CHAR columns are padded to the full length not VARCHAR columns|||I know it when I fill a formula with datas I am getting 99 empty spaces

blindman it is an exemple i have no column named [name]

if i run select datalength(name) from myT
i am getting 200 2 times (100)|||Strange I just ran all the scripts above & everything looks good to me

I get 1 from select datalength(name) from myT

anselme

take a deep breath reboot and start running these scripts again

If this does'nt work

Reply here and

stop using the word Char as in the 100 char place is full even if there is a data with only 1 char

explain what query editor you are using

explain what database you are using

run the script exactly as blindman suggests select len([Name]), '[' + [Name] + ']' from myT and tell us exactly the output

GW|||blindman it is an exemple i have no column named [name]You have some sort of typo, and if you expect any more help on this you need to post the actual code so we don't waste more of our time.|||I know it when I fill a formula with datas I am getting 99 empty spacesSQL Server does not have "formulas" to be "filled" (whatever that should mean).
What exactly are you doing?|||i agree with Gwilliy everything looks cool for me too....
varchar will only occupy the required number of space...
however if still problems persist you can always use LTRIM and RTRIM to get rid of the remaining whitespaces

so your query will be something like
select LTRIM(RTRIM([name])) from myT

thats the most we can get you....

However the fact that the 100 varchar place is full still mystifies me|||However the fact that the 100 varchar place is full still mystifies me
I suspect this is a front end issue.
What is this "formula" thing he is filling in?

Friday, February 24, 2012

[help] ALTER TABLE: error message 4929

Hi,

I've the following error message:

-----
ALTER TABLE [dbo].[AnagraficoAutomezzi]
ALTER COLUMN [CodiceXTrascodifica] varchar(10)
Go

Server: messaggio 4929, livello 16, stato 1, riga 1
Cannot alter table 'AnagraficoAutomezzi' because it is being published for
replication.
-----

This is only part of a script with much more alter table…

How can I lunch the script without this problem?

Thanks for your help!
Giacomo

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/Well, you cant alter schema involved in replication all that easily. For
this, you can use sp_addmergecolumn (not certain if the name is correct but
its close). If you post other code samples we can try to see what can be
done

MC

"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgst4zpwt6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

>
Hi,
>
I've the following error message:
>
-----
ALTER TABLE [dbo].[AnagraficoAutomezzi]
ALTER COLUMN [CodiceXTrascodifica] varchar(10)
Go
>
Server: messaggio 4929, livello 16, stato 1, riga 1
Cannot alter table 'AnagraficoAutomezzi' because it is being published for
replication.
-----
>
This is only part of a script with much more alter table.
>
How can I lunch the script without this problem?
>
Thanks for your help!
Giacomo
>
>
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

|||I've no more code sample to post, it's just a script with some ALTER TABLE
to change columns name or type.
We use this script to update client's DB. But one of them use replication
and we are always in trouble to update this DB.|||One of the downsides to replication is that is complicates subsequent schema
changes. As ML mentioned, you need to run the appropriate sp_repl* procs
instead of ALTER TABLE. The details vary depending on the type of
replication and the changes you are making.

If your client established replication outside the scope of your normal
support agreement, I suggest you have then remove replication so that your
normal script can be run and then reestablish replication afterwards. It's
unreasonable to expect your upgrade script to handle a replicated schema
unless you have detailed knowledge of the replication topology.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER TABLE
to change columns name or type.
We use this script to update client's DB. But one of them use replication
and we are always in trouble to update this DB.

|||Just to add something to my previous response. You could try using EM to
implement some of the changes, however I really wouldnt recommend this.
Changing schema for database involved in replication isnt something you
should do lightly.
Thats why I asked if you could post additional code that doesnt work for you
so we could suggest something.

MC

PS. Dan, its MC (Marko Culo) ;).

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:i4sUg.1248$NE6.754@.newssvr11.news.prodigy.com ...

Quote:

Originally Posted by

One of the downsides to replication is that is complicates subsequent


schema

Quote:

Originally Posted by

changes. As ML mentioned, you need to run the appropriate sp_repl* procs
instead of ALTER TABLE. The details vary depending on the type of
replication and the changes you are making.
>
If your client established replication outside the scope of your normal
support agreement, I suggest you have then remove replication so that your
normal script can be run and then reestablish replication afterwards.


It's

Quote:

Originally Posted by

unreasonable to expect your upgrade script to handle a replicated schema
unless you have detailed knowledge of the replication topology.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER


TABLE

Quote:

Originally Posted by

Quote:

Originally Posted by

to change columns name or type.
We use this script to update client's DB. But one of them use


replication

Quote:

Originally Posted by

Quote:

Originally Posted by

and we are always in trouble to update this DB.


>
>

|||PS. Dan, its MC (Marko Culo) ;).

Oops. Sorry, Marko.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"MC" <marko_culoNOSPAM@.yahoo.comwrote in message
news:eg0r1t$bqq$1@.ss408.t-com.hr...

Quote:

Originally Posted by

Just to add something to my previous response. You could try using EM to
implement some of the changes, however I really wouldnt recommend this.
Changing schema for database involved in replication isnt something you
should do lightly.
Thats why I asked if you could post additional code that doesnt work for
you
so we could suggest something.
>
>
MC
>
PS. Dan, its MC (Marko Culo) ;).
>
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:i4sUg.1248$NE6.754@.newssvr11.news.prodigy.com ...

Quote:

Originally Posted by

>One of the downsides to replication is that is complicates subsequent


schema

Quote:

Originally Posted by

>changes. As ML mentioned, you need to run the appropriate sp_repl* procs
>instead of ALTER TABLE. The details vary depending on the type of
>replication and the changes you are making.
>>
>If your client established replication outside the scope of your normal
>support agreement, I suggest you have then remove replication so that
>your
>normal script can be run and then reestablish replication afterwards.


It's

Quote:

Originally Posted by

>unreasonable to expect your upgrade script to handle a replicated schema
>unless you have detailed knowledge of the replication topology.
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"Giacomo" <no_spam@.grazie.itwrote in message
>news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER


TABLE

Quote:

Originally Posted by

Quote:

Originally Posted by

to change columns name or type.
We use this script to update client's DB. But one of them use


replication

Quote:

Originally Posted by

Quote:

Originally Posted by

and we are always in trouble to update this DB.


>>
>>


>
>

Thursday, February 16, 2012

[ask] trigger in sqlserver not working, HELPP!

i have this trigger in my database :

ALTER TRIGGER dbo.AddVoucher
ON dbo.User_AddVoucher
AFTER INSERT
AS
SET NOCOUNT ON;

DECLARE @.UserId int,
@.Add_id int,
@.voucher_id char,
@.Kredit money,
@.date smalldatetime,
@.last_balance money,
@.voucher_status char

SELECT @.UserId = UserId,
@.voucher_id = Voucher_ID,
@.Add_id = Add_id,
@.date = Deposit_Date
FROM Inserted

SELECT @.Kredit= Voucher_Value,
@.voucher_status = Voucher_Status
FROM Voucher
WHERE Voucher_ID = @.voucher_id

INSERT INTO User_Balance(AddVoucher_ID, UserId, Update_Type, Update_Date)
VALUES (@.Add_id,@.UserId, 'Kredit',@.date)

select @.last_balance = Balance
from User_Balance
WHERE UserId = @.UserId and Balance = (select TOP 1 Balance User_Balance where UserId = @.UserId order by Update_Id DESC)

if (@.voucher_status = 'active')

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for trigger here
BEGIN
update User_Balance
set Balance = @.last_balance + @.Kredit
where AddVoucher_ID = @.Add_id

update Voucher
set Sold_Date = @.date
where Voucher_ID = @.voucher_id

END

ELSE
BEGIN
raiserror ('Voucher is not valid',0,1)
rollback transaction
END
go

the problem is the update function is not working and the if statement always put to 'FALSE'

do you think anything wrong with the code

Thats perhaps because of the way you have declared the @.voucher_status parameter. char by default is same as char(1). So your @.voucher_status will only have an "a" if the status was "active". So change your datatype to @.varchar(10).

|||

oh my god...i didn't notice it...just a little mistake there, thanks bro for your help....

it runs smoothly now...

Saturday, February 11, 2012

@Variables

Just trying to confirm what I think I am seeing...
If I run a query which does something like this...
Select @.MyField = UserName
from dbo.signon
running this SQL statement inside a procedure does not produce anything in
terms of output row set? Just trying to confirm, that the client
application which is calling this procedure would not get any rows returned
(in this portion of the code) and that it is just setting some @. variables
for my later use within the Proc. Is that correct?
It is apparently "illegal" for me to have a statement like the following:
Select @.MyField = UserName, password
from dbo.signon
This is not a "real" situation, I am just trying to understand what I can
do and what I can not do. When I try this, I get the following error "A
SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.", which brings me to the conclusion that it
is illegal.
So in a situation where I needed to populate a "@.MyField" variable and also
retrieve the password (for some reason), this would be a two step process
(assuming no cursor logic)?
Thanks in advance for your assistance!!!!!!This is a multi-part message in MIME format.
--=_NextPart_000_00AC_01C3E587.E1CFBF20
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
No rows will be returned to the client when you use SELECT to populate
variables. You can populate multiple variables concurrently:
Select @.MyField = UserName
, @.MyFiled2 = password
from dbo.signon
Note: If your select would retrieve multiple rows, then the results are
unpredictable.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns947E5CB70CF08JimHeaveyhotmailcom@.207.46.248.16...
Just trying to confirm what I think I am seeing...
If I run a query which does something like this...
Select @.MyField = UserName
from dbo.signon
running this SQL statement inside a procedure does not produce anything in
terms of output row set? Just trying to confirm, that the client
application which is calling this procedure would not get any rows returned
(in this portion of the code) and that it is just setting some @. variables
for my later use within the Proc. Is that correct?
It is apparently "illegal" for me to have a statement like the following:
Select @.MyField = UserName, password
from dbo.signon
This is not a "real" situation, I am just trying to understand what I can
do and what I can not do. When I try this, I get the following error "A
SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.", which brings me to the conclusion that it
is illegal.
So in a situation where I needed to populate a "@.MyField" variable and also
retrieve the password (for some reason), this would be a two step process
(assuming no cursor logic)?
Thanks in advance for your assistance!!!!!!
--=_NextPart_000_00AC_01C3E587.E1CFBF20
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

No rows will be returned to the client =when you use SELECT to populate variables. You can populate multiple =variables concurrently:
Select @.MyField =3D UserName
, @.MyFiled2 =3D = password from dbo.signon
Note: If your select would retrieve multiple rows, then the results =are unpredictable.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jim Heavey" wrote =in message news:Xns9=47E5CB70CF08JimHeaveyhotmailcom@.207.46.248.16...Just trying to confirm what I think I am seeing...If I run a query =which does something like this... Select @.MyField ==3D UserName from dbo.signonrunning this SQL =statement inside a procedure does not produce anything in terms of output row set? Just trying to confirm, that the client application which =is calling this procedure would not get any rows returned (in this =portion of the code) and that it is just setting some @. variables for my later =use within the Proc. Is that correct?It is apparently ="illegal" for me to have a statement like the following: Select =@.MyField =3D UserName, password from =dbo.signonThis is not a "real" situation, I am just trying to understand what I can =do and what I can not do. When I try this, I get the following error "A SELECT statement that assigns a value to a variable must not be =combined with data-retrieval operations.", which brings me to the conclusion =that it is illegal.So in a situation where I needed to populate a ="@.MyField" variable and also retrieve the password (for some reason), this =would be a two step process (assuming no cursor logic)?Thanks in =advance for your assistance!!!!!!

--=_NextPart_000_00AC_01C3E587.E1CFBF20--

@Variables

Just trying to confirm what I think I am seeing...
If I run a query which does something like this...
Select @.MyField = UserName
from dbo.signon
running this SQL statement inside a procedure does not produce anything in
terms of output row set? Just trying to confirm, that the client
application which is calling this procedure would not get any rows returned
(in this portion of the code) and that it is just setting some @. variables
for my later use within the Proc. Is that correct?
It is apparently "illegal" for me to have a statement like the following:
Select @.MyField = UserName, password
from dbo.signon
This is not a "real" situation, I am just trying to understand what I can
do and what I can not do. When I try this, I get the following error "A
SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.", which brings me to the conclusion that it
is illegal.
So in a situation where I needed to populate a "@.MyField" variable and also
retrieve the password (for some reason), this would be a two step process
(assuming no cursor logic)?
Thanks in advance for your assistance!!!!!!No rows will be returned to the client when you use SELECT to populate
variables. You can populate multiple variables concurrently:
Select @.MyField = UserName
, @.MyFiled2 = password
from dbo.signon
Note: If your select would retrieve multiple rows, then the results are
unpredictable.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns947E5CB70CF08JimHeaveyhotmailcom
@.207.46.248.16...
Just trying to confirm what I think I am seeing...
If I run a query which does something like this...
Select @.MyField = UserName
from dbo.signon
running this SQL statement inside a procedure does not produce anything in
terms of output row set? Just trying to confirm, that the client
application which is calling this procedure would not get any rows returned
(in this portion of the code) and that it is just setting some @. variables
for my later use within the Proc. Is that correct?
It is apparently "illegal" for me to have a statement like the following:
Select @.MyField = UserName, password
from dbo.signon
This is not a "real" situation, I am just trying to understand what I can
do and what I can not do. When I try this, I get the following error "A
SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.", which brings me to the conclusion that it
is illegal.
So in a situation where I needed to populate a "@.MyField" variable and also
retrieve the password (for some reason), this would be a two step process
(assuming no cursor logic)?
Thanks in advance for your assistance!!!!!!

Thursday, February 9, 2012

@@rowcount

I want to do something like this.
SELECT *
FROM dbo.Contact [C]
-- If any rows are returned then return to front end.
IF @.@.rowcount > 0
Begin
GOTO FinishSuccessModule
End
-- But i had doubt here
We will never be sure that @.@.RowCount is giving the row count of the query
mentioned above since in a heavy concurrent scenario (as we have in our db
and its growing since our user base is also growing) before your query and
the checking of @.@.RowCount there might be another select fired by another
process or transaction which updates @.@.Rowcount which in turn give you
incorrect values.
how can we implement the same functionality using some other way, i dont
want If exists as there are around 6-7 conditions which are totally
select..if any one of them return record then come out else continue with
other sql st.
does @.@.rowcount create issues as mentioned above and second can we avoid
that using some other functionality.
Sanjay
Sanjay
No, rowcount is safe.
Bojidar Alexandrov
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:7276DE0A-F23B-403B-BEE5-C83E51FA71FC@.microsoft.com...
> I want to do something like this.
> SELECT *
> FROM dbo.Contact [C]
> -- If any rows are returned then return to front end.
> IF @.@.rowcount > 0
> Begin
> GOTO FinishSuccessModule
> End
> -- But i had doubt here
> We will never be sure that @.@.RowCount is giving the row count of the
query
> mentioned above since in a heavy concurrent scenario (as we have in our db
> and its growing since our user base is also growing) before your query and
> the checking of @.@.RowCount there might be another select fired by another
> process or transaction which updates @.@.Rowcount which in turn give you
> incorrect values.
> how can we implement the same functionality using some other way, i dont
> want If exists as there are around 6-7 conditions which are totally
> select..if any one of them return record then come out else continue with
> other sql st.
> does @.@.rowcount create issues as mentioned above and second can we avoid
> that using some other functionality.
> --
> Sanjay
> --
> Sanjay
|||If all you need to know is are "any" rows returned and don't care how many,
I would change the initial query to SELECT TOP 1 * FROM dbo.Contact (C). If
your contact table gets thousands of rows, why go thru and read them all
when you just need to know "do I get back at least 1 row"? And to be more
specific, you don't need a SELECT *. Pick one narrow column for your
select. Something like select top 1 ContactID from dbo.contact (c). This
is a lot more efficient.
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:eWegGSspEHA.3244@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> No, rowcount is safe.
> Bojidar Alexandrov
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:7276DE0A-F23B-403B-BEE5-C83E51FA71FC@.microsoft.com...
> query
db[vbcol=seagreen]
and[vbcol=seagreen]
another[vbcol=seagreen]
with
>

@@rowcount

I want to do something like this.
SELECT *
FROM dbo.Contact [C]
-- If any rows are returned then return to front end.
IF @.@.rowcount > 0
Begin
GOTO FinishSuccessModule
End
-- But i had doubt here
We will never be sure that @.@.RowCount is giving the row count of the query
mentioned above since in a heavy concurrent scenario (as we have in our db
and its growing since our user base is also growing) before your query and
the checking of @.@.RowCount there might be another select fired by another
process or transaction which updates @.@.Rowcount which in turn give you
incorrect values.
how can we implement the same functionality using some other way, i dont
want If exists as there are around 6-7 conditions which are totally
select..if any one of them return record then come out else continue with
other sql st.
does @.@.rowcount create issues as mentioned above and second can we avoid
that using some other functionality.
--
Sanjay
--
SanjayNo, rowcount is safe.
Bojidar Alexandrov
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:7276DE0A-F23B-403B-BEE5-C83E51FA71FC@.microsoft.com...
> I want to do something like this.
> SELECT *
> FROM dbo.Contact [C]
> -- If any rows are returned then return to front end.
> IF @.@.rowcount > 0
> Begin
> GOTO FinishSuccessModule
> End
> -- But i had doubt here
> We will never be sure that @.@.RowCount is giving the row count of the
query
> mentioned above since in a heavy concurrent scenario (as we have in our db
> and its growing since our user base is also growing) before your query and
> the checking of @.@.RowCount there might be another select fired by another
> process or transaction which updates @.@.Rowcount which in turn give you
> incorrect values.
> how can we implement the same functionality using some other way, i dont
> want If exists as there are around 6-7 conditions which are totally
> select..if any one of them return record then come out else continue with
> other sql st.
> does @.@.rowcount create issues as mentioned above and second can we avoid
> that using some other functionality.
> --
> Sanjay
> --
> Sanjay|||If all you need to know is are "any" rows returned and don't care how many,
I would change the initial query to SELECT TOP 1 * FROM dbo.Contact (C). If
your contact table gets thousands of rows, why go thru and read them all
when you just need to know "do I get back at least 1 row"? And to be more
specific, you don't need a SELECT *. Pick one narrow column for your
select. Something like select top 1 ContactID from dbo.contact (c). This
is a lot more efficient.
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:eWegGSspEHA.3244@.tk2msftngp13.phx.gbl...
> No, rowcount is safe.
> Bojidar Alexandrov
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:7276DE0A-F23B-403B-BEE5-C83E51FA71FC@.microsoft.com...
> > I want to do something like this.
> > SELECT *
> > FROM dbo.Contact [C]
> > -- If any rows are returned then return to front end.
> > IF @.@.rowcount > 0
> > Begin
> > GOTO FinishSuccessModule
> > End
> > -- But i had doubt here
> > We will never be sure that @.@.RowCount is giving the row count of the
> query
> > mentioned above since in a heavy concurrent scenario (as we have in our
db
> > and its growing since our user base is also growing) before your query
and
> > the checking of @.@.RowCount there might be another select fired by
another
> > process or transaction which updates @.@.Rowcount which in turn give you
> > incorrect values.
> >
> > how can we implement the same functionality using some other way, i dont
> > want If exists as there are around 6-7 conditions which are totally
> > select..if any one of them return record then come out else continue
with
> > other sql st.
> >
> > does @.@.rowcount create issues as mentioned above and second can we avoid
> > that using some other functionality.
> > --
> > Sanjay
> > --
> > Sanjay
>