[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?

[SQL 2005] How do I prevent or hide this warning message?

"Warning: Null value is eliminated by an aggregate or other SET operation."
i keep having this message while having some joint statement.
is there a way to prevent this or even hide this message?Check ANSI_WARNINGS in BoL|||sorry, may I know what is BoL? thanks :)|||bol = books online

it's the documentation for sql server.

[newbie] SQL-query with SUM() doesnt work. :(

Hi NG,

I have two tables:

TABLE1 (id1 INTEGER NOT NULL, id2 INTEGER NOT NULL, value INTEGER, PRIMARY KEY (id1, id2))

TABLE2 (id1 INTEGER NOT NULL, text CHAR(4), PRIMARY KEY (id1))

And this is the query, that I'm trying to run on this tables:

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

Well - it doesn't work at all! :(
That is the reason?!

Appreciate everyone's help!!!

S.B.well when doing aggregates you must have everything in the select statement that is not being summed in the group by clause.

Example:

select t1.id1, t2.text, sum(t1.value)
from table1 t1, table2 t2
where t1.id1 = t2.id1
group by t1.id1, t2.text
order by t1.id1 asc|||Hello,

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

DROP the TABLE2.text field cause this is not a group field or
use

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1, TABLE2.text
order by TABLE1.id1 asc

Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com|||Thanks, mkkmg and alligatorsql.com !!! :)

"group by TABLE1.id1, TABLE2.text" works perfectly!

S.B.

Originally posted by alligatorsql.com
Hello,

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

DROP the TABLE2.text field cause this is not a group field or
use

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1, TABLE2.text
order by TABLE1.id1 asc

Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com

@NumericVal IS NOT NULL seems not to return a boolean.

I'm trying to ensure that only of the parameters is passed to my stored procedure.

BOL says that the IS [NOT] NULL operator (language construct?) will return a boolean. An IF statement takes an expression which results in a boolean so I was surprised to find that the below code doesn't parse.

CREATE PROC sp_OneParm(
@.NumericVal float = null,
@.StringVal nvarchar(200) = null,
@.DateVal datetime = null,
@.BitVal bit = null)
AS
DECLARE @.ValCount tinyint
SET @.ValCount = 0

-- Ensure we've only got one update value specified
IF @.NumericVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.StringVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.DateVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.BitVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.ValCount > 1 RAISERROR ('Only one @.*Val paramater may be specified when calling sp_OneParm()', 16, 1)

-- Other Stuff
GO

Am I missing something simple or do I need to restructure my code to achieve the logic I want?Sure :)

IF @.NumericVal IS NOT NULL SET @.ValCount = @.ValCount + 1|||Bugger. :o
Thank-you roac.

@@SERVERNAME returns NULL value... Can we adjust so that it returns the server name

We are running SQL 2000 in a non-clustered server. For some reason when we
select @.@.SERVERNAME we are getting a NULL value back. According to BOL ...
the information in returned by @.@.SERVERNAME may be different than the
SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
automatically reports changes in the network name of the computer. In
contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
changes made to the local server name using the sp_addserver or
sp_dropserver stored procedure
Assuming that my server name is XYZ - I would assume that by executing the
following command we would be OK:
use master
go
sp_addserver 'XYZ', 'local'
go
But when executing this - we get the following message:
Server already exists.
Can someone advise the correct way to do this?
Thanks,
Tom
Hi,
Try this
sp_dropserver 'XYZ'
go
sp_addserver 'XYZ', 'local'
go
After this stop and start the MS SQL Server service. Then login in Query
analyzer and execute SELECT @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"TJT" <TJT@.nospam.com> wrote in message
news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> We are running SQL 2000 in a non-clustered server. For some reason when
> we
> select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> ...
> the information in returned by @.@.SERVERNAME may be different than the
> SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> automatically reports changes in the network name of the computer. In
> contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> changes made to the local server name using the sp_addserver or
> sp_dropserver stored procedure
> Assuming that my server name is XYZ - I would assume that by executing the
> following command we would be OK:
> use master
> go
> sp_addserver 'XYZ', 'local'
> go
> But when executing this - we get the following message:
> Server already exists.
> Can someone advise the correct way to do this?
> Thanks,
> Tom
>
>
>

@@SERVERNAME returns NULL value... Can we adjust so that it returns the server name

We are running SQL 2000 in a non-clustered server. For some reason when we
select @.@.SERVERNAME we are getting a NULL value back. According to BOL ...
the information in returned by @.@.SERVERNAME may be different than the
SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
automatically reports changes in the network name of the computer. In
contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
changes made to the local server name using the sp_addserver or
sp_dropserver stored procedure
Assuming that my server name is XYZ - I would assume that by executing the
following command we would be OK:
use master
go
sp_addserver 'XYZ', 'local'
go
But when executing this - we get the following message:
Server already exists.
Can someone advise the correct way to do this?
Thanks,
TomHi,
Try this
sp_dropserver 'XYZ'
go
sp_addserver 'XYZ', 'local'
go
After this stop and start the MS SQL Server service. Then login in Query
analyzer and execute SELECT @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"TJT" <TJT@.nospam.com> wrote in message
news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> We are running SQL 2000 in a non-clustered server. For some reason when
> we
> select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> ...
> the information in returned by @.@.SERVERNAME may be different than the
> SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> automatically reports changes in the network name of the computer. In
> contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> changes made to the local server name using the sp_addserver or
> sp_dropserver stored procedure
> Assuming that my server name is XYZ - I would assume that by executing the
> following command we would be OK:
> use master
> go
> sp_addserver 'XYZ', 'local'
> go
> But when executing this - we get the following message:
> Server already exists.
> Can someone advise the correct way to do this?
> Thanks,
> Tom
>
>
>|||Hi Hari,
I have the same scenario as below, and I have even try to do two times
server restart also, but @.@.servername still returns Null to me, which is
different result from SERVERNAME property of SERVERPROPERTY function. Any
idea what else we can try? Thanks in advance!
Regards,
JC
"Hari Prasad" wrote:
> Hi,
> Try this
>
> sp_dropserver 'XYZ'
> go
> sp_addserver 'XYZ', 'local'
> go
> After this stop and start the MS SQL Server service. Then login in Query
> analyzer and execute SELECT @.@.SERVERNAME
> Thanks
> Hari
> SQL Server MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> > We are running SQL 2000 in a non-clustered server. For some reason when
> > we
> > select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> > ...
> > the information in returned by @.@.SERVERNAME may be different than the
> > SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> > automatically reports changes in the network name of the computer. In
> > contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> > changes made to the local server name using the sp_addserver or
> > sp_dropserver stored procedure
> >
> > Assuming that my server name is XYZ - I would assume that by executing the
> > following command we would be OK:
> >
> > use master
> > go
> >
> > sp_addserver 'XYZ', 'local'
> > go
> >
> > But when executing this - we get the following message:
> > Server already exists.
> >
> > Can someone advise the correct way to do this?
> >
> > Thanks,
> > Tom
> >
> >
> >
> >
> >
> >
>
>

@@SERVERNAME returns NULL value... Can we adjust so that it returns the server name

We are running SQL 2000 in a non-clustered server. For some reason when we
select @.@.SERVERNAME we are getting a NULL value back. According to BOL ...
the information in returned by @.@.SERVERNAME may be different than the
SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
automatically reports changes in the network name of the computer. In
contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
changes made to the local server name using the sp_addserver or
sp_dropserver stored procedure
Assuming that my server name is XYZ - I would assume that by executing the
following command we would be OK:
use master
go
sp_addserver 'XYZ', 'local'
go
But when executing this - we get the following message:
Server already exists.
Can someone advise the correct way to do this?
Thanks,
TomHi,
Try this
sp_dropserver 'XYZ'
go
sp_addserver 'XYZ', 'local'
go
After this stop and start the MS SQL Server service. Then login in Query
analyzer and execute SELECT @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"TJT" <TJT@.nospam.com> wrote in message
news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> We are running SQL 2000 in a non-clustered server. For some reason when
> we
> select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> ...
> the information in returned by @.@.SERVERNAME may be different than the
> SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> automatically reports changes in the network name of the computer. In
> contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> changes made to the local server name using the sp_addserver or
> sp_dropserver stored procedure
> Assuming that my server name is XYZ - I would assume that by executing the
> following command we would be OK:
> use master
> go
> sp_addserver 'XYZ', 'local'
> go
> But when executing this - we get the following message:
> Server already exists.
> Can someone advise the correct way to do this?
> Thanks,
> Tom
>
>
>

@@servername returns NULL value

I have a SQL 2005 clustered server which is returning a Null value for @.@.servername. I find the server entry in sysservers. I have replication configured on this so i am not able to do a Sp_dropserver & sp_addserver as this acts as a publisher. The configured merge repication stopped working because of this issue and I am not able to delete replication as the the delete option uses @.@.servername which returns a null value. So I am struck in a loop.

Any advice is appreciated.

thanks

For a machine to be considered local, srvid = 0 in sysservers. Can you check on this?

Can a simple reboot of the nodes fix the problem? Otherwise how did you get into this state, considering you have replication set up properly already?

|||

Hi,

Have you rename the virtual server name or the participating node name?

Peng

@@SERVERNAME returns NULL

When I do:
SELECT @.@.SERVERNAME
It returns NULL. How, and how can I fix.
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Darin,
use:
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

@@servername returns NULL

Hi,
sql server 2000 sp3a
select @.@.servername returns null but
select * from sysservers shows the correct servername. This causes problems
to set up replication.
Any idea how to resolve apart of reinstalling?
Thanks
YanLook up sp_dropserver and sp_addserver system procedures in Books Online.
Have you renamed the server recently? (either the machine or the SQL Server
instance)
ML
http://milambda.blogspot.com/|||Yan,
please try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thanks, I found the problem when I used sp_addserver I did not add the
second param @.local so the result is that master..sysservers has no row with
a srvrid 0 which should represent the local server.
Thanks.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:CB9D38F6-E2F0-43F3-84C2-CF5DEA8AF8C8@.microsoft.com...
> Look up sp_dropserver and sp_addserver system procedures in Books Online.
> Have you renamed the server recently? (either the machine or the SQL
> Server
> instance)
>
> ML
> --
> http://milambda.blogspot.com/|||Yeah, those darn extra parameters... :)
ML
http://milambda.blogspot.com/

@@servername returns NULL

I am trying to script out the creation of database scripts. I am trying
to use @.@.servername in the statement. I found out the select
@.@.servername returns NULL. I used sp_dropserver to drop any servernames
first, restarted SQL, ran sp_addserver 'servername' to add the
servername, restarted SQL. select @.@.servername still returns NULL...

Any ideas why this may be happening?

Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!tgru (tgru@.devdex.com) writes:
> I am trying to script out the creation of database scripts. I am trying
> to use @.@.servername in the statement. I found out the select
> @.@.servername returns NULL. I used sp_dropserver to drop any servernames
> first, restarted SQL, ran sp_addserver 'servername' to add the
> servername, restarted SQL. select @.@.servername still returns NULL...
> Any ideas why this may be happening?

Did you specify 'local' as the second parameter to sp_addserver?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland is the man!!

Thanks, I battle syntax on a daily basis...

TGru

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

@@ServerName returns NULL

Hi: We use @.@.Servername throughout all our jobs to indicate to the script
which server the script is running upon. However, for some unknown reason,
the statement "Select @.@.ServerName" is returning a null value rather than the
name of the server. This has broken ALL of our job scripts. There is a work
around, however, with over 100 jobs that run, we are not too keen about
modifying existing scripts. Any ideas how we can get this fixed?
SQL Server Man Not
On Wed, 6 Oct 2004 14:57:02 -0700, Richard wrote:

>Hi: We use @.@.Servername throughout all our jobs to indicate to the script
>which server the script is running upon. However, for some unknown reason,
>the statement "Select @.@.ServerName" is returning a null value rather than the
>name of the server. This has broken ALL of our job scripts. There is a work
>around, however, with over 100 jobs that run, we are not too keen about
>modifying existing scripts. Any ideas how we can get this fixed?
Hi Richard,
Quote from Books Online:
SQL Server Setup sets the server name to the computer name during
installation. Change @.@.SERVERNAME by using sp_addserver and then
restarting SQL Server. This method, however, is not usually required.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the imput Hugo, we have done just that and we still get a Null
result.
"Hugo Kornelis" wrote:

> On Wed, 6 Oct 2004 14:57:02 -0700, Richard wrote:
>
> Hi Richard,
> Quote from Books Online:
> SQL Server Setup sets the server name to the computer name during
> installation. Change @.@.SERVERNAME by using sp_addserver and then
> restarting SQL Server. This method, however, is not usually required.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Wed, 13 Oct 2004 09:51:09 -0700, Richard wrote:

>Thanks for the imput Hugo, we have done just that and we still get a Null
>result.
Hi Richard,
I'm sorry to hear that. Unfortunately, I'm at a loss about what might
cause this. However, you might want to check the following links to see if
they apply to your situation:
http://support.microsoft.com/default...b;en-us;302223
http://support.microsoft.com/default...b;en-us;303774
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||run sp_dropserver
and then run sp_addserver 'local' if the instance is a default
instance .
If the instance is a named instance then run sp_addserver
'machinename\instancename' .
The server name will be changed after SQL is recycled.
HTH.
Venu
Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<rh3rm0t9lje6103c41f7f3plke6hoie7nj@.4ax.com>. ..
> On Wed, 13 Oct 2004 09:51:09 -0700, Richard wrote:
>
> Hi Richard,
> I'm sorry to hear that. Unfortunately, I'm at a loss about what might
> cause this. However, you might want to check the following links to see if
> they apply to your situation:
> http://support.microsoft.com/default...b;en-us;302223
> http://support.microsoft.com/default...b;en-us;303774
> Best, Hugo

@@ServerName returns NULL

Hi: We use @.@.Servername throughout all our jobs to indicate to the script
which server the script is running upon. However, for some unknown reason,
the statement "Select @.@.ServerName" is returning a null value rather than the
name of the server. This has broken ALL of our job scripts. There is a work
around, however, with over 100 jobs that run, we are not too keen about
modifying existing scripts. Any ideas how we can get this fixed?
--
SQL Server Man NotOn Wed, 6 Oct 2004 14:57:02 -0700, Richard wrote:
>Hi: We use @.@.Servername throughout all our jobs to indicate to the script
>which server the script is running upon. However, for some unknown reason,
>the statement "Select @.@.ServerName" is returning a null value rather than the
>name of the server. This has broken ALL of our job scripts. There is a work
>around, however, with over 100 jobs that run, we are not too keen about
>modifying existing scripts. Any ideas how we can get this fixed?
Hi Richard,
Quote from Books Online:
SQL Server Setup sets the server name to the computer name during
installation. Change @.@.SERVERNAME by using sp_addserver and then
restarting SQL Server. This method, however, is not usually required.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the imput Hugo, we have done just that and we still get a Null
result.
"Hugo Kornelis" wrote:
> On Wed, 6 Oct 2004 14:57:02 -0700, Richard wrote:
> >Hi: We use @.@.Servername throughout all our jobs to indicate to the script
> >which server the script is running upon. However, for some unknown reason,
> >the statement "Select @.@.ServerName" is returning a null value rather than the
> >name of the server. This has broken ALL of our job scripts. There is a work
> >around, however, with over 100 jobs that run, we are not too keen about
> >modifying existing scripts. Any ideas how we can get this fixed?
> Hi Richard,
> Quote from Books Online:
> SQL Server Setup sets the server name to the computer name during
> installation. Change @.@.SERVERNAME by using sp_addserver and then
> restarting SQL Server. This method, however, is not usually required.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 13 Oct 2004 09:51:09 -0700, Richard wrote:
>Thanks for the imput Hugo, we have done just that and we still get a Null
>result.
Hi Richard,
I'm sorry to hear that. Unfortunately, I'm at a loss about what might
cause this. However, you might want to check the following links to see if
they apply to your situation:
http://support.microsoft.com/default.aspx?scid=kb;en-us;302223
http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||run sp_dropserver
and then run sp_addserver 'local' if the instance is a default
instance .
If the instance is a named instance then run sp_addserver
'machinename\instancename' .
The server name will be changed after SQL is recycled.
HTH.
Venu
Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<rh3rm0t9lje6103c41f7f3plke6hoie7nj@.4ax.com>...
> On Wed, 13 Oct 2004 09:51:09 -0700, Richard wrote:
> >Thanks for the imput Hugo, we have done just that and we still get a Null
> >result.
> Hi Richard,
> I'm sorry to hear that. Unfortunately, I'm at a loss about what might
> cause this. However, you might want to check the following links to see if
> they apply to your situation:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;302223
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
> Best, Hugo

@@servername returning NULL

I have a SQL 2005 clustered server which is returning a Null value for @.@.servername. I find the server entry in sysservers. I have replication configured on this so i am not able to do a Sp_dropserver & sp_addserver as this acts as a publisher. The configured merge repication stopped working because of this issue and I am not able to delete replication as the the delete option uses @.@.servername which returns a null value. So I am struck in a loop.

Any advice is appreciated.

thanks

@.@.Servername of null can happen if there is no entry in sys.servers for server_id 0. check sys.servers with this:

select * from sys.servers where server_id=0

If this returns 0 rows you can add the local server with

sp_addserver '<servername>', local

you will need to restart the server for this to take effect.

@@servername NULL

I am running SQL 7 Standard on a W2K server, both at the most current
service packs. After applying the lastest security update to Windows
and rebooting the server, SQL has suddenly fogottern its name - SELECT
@.@.SERVERNAME returns NULL. In conjuction with this loss of identity,
various maintenance plan jobs seem to hang and never complete. This is
wreaking all sorts of havoc with my backups since none of them complete
successfully.
Will the sp_dropserver/sp_addserver combination fix the NULL problem?
Is there anything else I need to do?
Thanks,
M. E. HoustonYes...the dropserver, addserver should clean it up.
If you query the system table sysserver, you will probably
find that there isn't a server with the srvid = 0. The 0 is
used for the local server. If there is an entry for the
local server and srvid is not 0, you can remove it with
sp_dropserver. Uisng sp_addserver with 'local' adds the
server with the srvid = 0. If you don't specifiy 'local',
it's not added with the 0 for srvid.
-Sue
On Tue, 30 Sep 2003 11:19:09 -0500, meh
<margit.houston@.no_spam.gsiusa.com> wrote:
>I am running SQL 7 Standard on a W2K server, both at the most current
>service packs. After applying the lastest security update to Windows
>and rebooting the server, SQL has suddenly fogottern its name - SELECT
>@.@.SERVERNAME returns NULL. In conjuction with this loss of identity,
>various maintenance plan jobs seem to hang and never complete. This is
>wreaking all sorts of havoc with my backups since none of them complete
>successfully.
>Will the sp_dropserver/sp_addserver combination fix the NULL problem?
>Is there anything else I need to do?
>Thanks,
>M. E. Houston|||This solved my server name problem, but I'm still having
issues with jobs created by maintenance plans hanging. In
order to make them stop, I have to kill the process. When
I do that, the following message gets written to the log --
[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionRead (recv()).
[Microsoft][ODBC SQL Server Driver][Shared Memory]General
network error. Check your network documentation.
-- Anyone have any clue as to what this really means?
Thanks,
Margit
>--Original Message--
>Yes...the dropserver, addserver should clean it up.
>If you query the system table sysserver, you will probably
>find that there isn't a server with the srvid = 0. The 0
is
>used for the local server. If there is an entry for the
>local server and srvid is not 0, you can remove it with
>sp_dropserver. Uisng sp_addserver with 'local' adds the
>server with the srvid = 0. If you don't specifiy 'local',
>it's not added with the 0 for srvid.
>-Sue|||Just curious based on the first issue and not sure if it's
the issue or not but did you by any chance rename the server
after the jobs were created? Do you have any problems
changing, modifying jobs?
-Sue
On Thu, 2 Oct 2003 13:22:09 -0700, "M. E. Houston"
<margit.houston@.no_spam.gsiusa.com> wrote:
>This solved my server name problem, but I'm still having
>issues with jobs created by maintenance plans hanging. In
>order to make them stop, I have to kill the process. When
>I do that, the following message gets written to the log --
>[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0:
>[Microsoft][ODBC SQL Server Driver][Shared Memory]
>ConnectionRead (recv()).
>[Microsoft][ODBC SQL Server Driver][Shared Memory]General
>network error. Check your network documentation.
>-- Anyone have any clue as to what this really means?
>Thanks,
>Margit
>
>>--Original Message--
>>Yes...the dropserver, addserver should clean it up.
>>If you query the system table sysserver, you will probably
>>find that there isn't a server with the srvid = 0. The 0
>is
>>used for the local server. If there is an entry for the
>>local server and srvid is not 0, you can remove it with
>>sp_dropserver. Uisng sp_addserver with 'local' adds the
>>server with the srvid = 0. If you don't specifiy 'local',
>>it's not added with the 0 for srvid.
>>-Sue|||Sorry for the delay in reply.
I did not have any problems making changes to any of the
jobs/maintenance plans either before or after I renamed the server. I
thought the problem might have been a naming issue so I deleted all the
maintenance plans and all the jobs I had created and started over. The
new jobs under the correct server name still exhibit the same bad
behaviour. There doesn't seem to be any consistency as to where in the
job stream things fall apart, i.e., which database in the backup job it
hangs on. On some occassions, the jobs even complete as expected.
Thanks,
Margit
Sue Hoegemeier wrote:
> Just curious based on the first issue and not sure if it's
> the issue or not but did you by any chance rename the server
> after the jobs were created? Do you have any problems
> changing, modifying jobs?
> -Sue|||Sorry for the delay in reply.
I did not have any problems making changes to any of the
jobs/maintenance plans either before or after I renamed the server. I
thought the problem might have been a naming issue so I deleted all the
maintenance plans and all the jobs I had created and started over. The
new jobs under the correct server name still exhibit the same bad
behaviour. There doesn't seem to be any consistency as to where in the
job stream things fall apart, i.e., which database in the backup job it
hangs on. On some occassions, the jobs even complete as expected.
Thanks,
Margit
Sue Hoegemeier wrote:
> Just curious based on the first issue and not sure if it's
> the issue or not but did you by any chance rename the server
> after the jobs were created? Do you have any problems
> changing, modifying jobs?
> -Sue|||Can't tell you for sure why the jobs randomly hang - I've
seen jobs hang when they attempt to send mail and there are
problems somewhere in the mail configuration. You could run
profiler or a trace to try to track down what specifically
is going on when the jobs hang.
-Sue
On Wed, 08 Oct 2003 08:53:32 -0500, meh
<margit.houston@.no_spam.gsiusa.com> wrote:
>Sorry for the delay in reply.
>I did not have any problems making changes to any of the
>jobs/maintenance plans either before or after I renamed the server. I
>thought the problem might have been a naming issue so I deleted all the
>maintenance plans and all the jobs I had created and started over. The
>new jobs under the correct server name still exhibit the same bad
>behaviour. There doesn't seem to be any consistency as to where in the
>job stream things fall apart, i.e., which database in the backup job it
>hangs on. On some occassions, the jobs even complete as expected.
>Thanks,
>Margit
>Sue Hoegemeier wrote:
>> Just curious based on the first issue and not sure if it's
>> the issue or not but did you by any chance rename the server
>> after the jobs were created? Do you have any problems
>> changing, modifying jobs?
>> -Sue

@@servername

If anybody can explain why for one of my SQL Server2000 servers the statement select @.@.servername returns NULL instead of name?
ThanksThe BOL is incomplete about this subject. Try this microsoft article (http://support.microsoft.com/default.aspx?scid=kb;en-us;303774)

Good luck
Hope this helped|||I'd use:EXECUTE sp_addserver 'desiredServerName', 'LOCAL'-PatP

@@servername

Hello,
Can anybody tell me why when i "select @.@.servername" it=B4s=20
returned NULL value?
Should i do an INSERT in the master..sysservers? or can=20
you tell me if exists something that correct this=20
situation?
Best RegardsMake a backup of master first
do sp_dropserver 'servername'
go
sp_addserver 'servername', local
go
then stop and start your server.
Then select @.@.servername
Jeff
MCDBA, MCSE+I|||Thanks Jeff
>--Original Message--
>Make a backup of master first
>do sp_dropserver 'servername'
>go
>sp_addserver 'servername', local
>go
>then stop and start your server.
>Then select @.@.servername
>Jeff
>MCDBA, MCSE+I
>.
>

@@servername

Hello,
Can anybody tell me why when i "select @.@.servername" it=B4s=20
returned NULL value?
Should i do an INSERT in the master..sysservers? or can=20
you tell me if exists something that correct this=20
situation?
Best Regards
Make a backup of master first
do sp_dropserver 'servername'
go
sp_addserver 'servername', local
go
then stop and start your server.
Then select @.@.servername
Jeff
MCDBA, MCSE+I
|||Thanks Jeff
>--Original Message--
>Make a backup of master first
>do sp_dropserver 'servername'
>go
>sp_addserver 'servername', local
>go
>then stop and start your server.
>Then select @.@.servername
>Jeff
>MCDBA, MCSE+I
>.
>

@@servername

Hello,
Can anybody tell me why when i "select @.@.servername" it=B4s returned NULL value?
Should i do an INSERT in the master..sysservers? or can you tell me if exists something that correct this situation?
Best RegardsMake a backup of master firs
do sp_dropserver 'servername
g
sp_addserver 'servername', loca
g
then stop and start your server
Then select @.@.servernam
Jef
MCDBA, MCSE+|||Thanks Jeff
>--Original Message--
>Make a backup of master first
>do sp_dropserver 'servername'
>go
>sp_addserver 'servername', local
>go
>then stop and start your server.
>Then select @.@.servername
>Jeff
>MCDBA, MCSE+I
>.
>

@@server_name

Should it concern me that the code below returns Null? I simply was hoping
to get the name of the server as a string ...
select @.@.SERVERNAME
Thanks in advance.
Mark
Hi
Run sp_dropserver and then sp_addserver against the server.
Master DB does not have a valid value for the server. This can occur on
servers where Master DB was restored, or upgraded or the server name was
changed.
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/
"Mark" <Mark@.nowhere.com> wrote in message
news:eJZOBVWDFHA.3256@.tk2msftngp13.phx.gbl...
> Should it concern me that the code below returns Null? I simply was
hoping
> to get the name of the server as a string ...
> select @.@.SERVERNAME
> Thanks in advance.
> Mark
>