@@servername returning NULL

_{Thursday, March 9, 2006 7:33 PM | 1 vote}

 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

_{Friday, March 10, 2006 12:11 AM ✅Answered | 3 votes}

@@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.

_{Monday, November 12, 2007 1:59 AM}

hi guys.. same problem here.  mr. jerome, there is an entry of the local server but the assigned server_id=2 not 0.  can i disconnect / unregistered the local server so that i will clear all entries in my sysserver table? 

_{Monday, November 12, 2007 7:44 AM}

Hi **Boycarr,

First please remove the local entry using the folowing Code

**

sp_dropserver '<servername/Existing Name>', 'local';

then add a new server name to your local SQL BOX.

sp_addserver '<servername/new  Name>', 'local';


I hope, Machne name and SQL SErver name is same. If it is different than Please make necessary changes in your DNS Server.

Or make note in host file located in "C:\WINDOWS\system32\drivers\etc" 


Regards
Mahesh
mailMaheshGupta@yahoo.com

_{Wednesday, July 13, 2011 10:19 PM}

This is the solution for my issue.

I had changed the name of the server, but the server name was entered under id 1.  After I checked with the string above, I was able to drop the server listed under 0, then drop the server listed under 1, add the server, restart the sql server and the correct name then appeared under 0.

This was driving me crazy.

Thanks for the help.

Brison

_{Monday, July 25, 2011 10:33 AM}

Hi.

I am also having this same error.  After running the select query above I am given one row.

   server_id   name        product     provider    data_source location    provider_string catalog connect_timeout     query_timeout   is_linked   is_remote_login_enabled is_rpc_out_enabled  is_data_access_enabled  is_collation_compatible uses_remote_collation   collation_name  lazy_schema_validation  is_system   is_publisher    is_subscriber   is_distributor  is_nonsql_subscriber    is_remote_proc_transaction_promotion_enabled    modify_date
1   0       mgi-sql6    SQL Server  SQLNCLI mgi-sql6        NULL        NULL            NULL        0               0           0       1                   1               0                   0                   1                   NULL            0                   0                   0           0           0               0                                   2011-07-25 11:16:25.870

I am not very familiar with SQL.  Can anyone help me with why I am receving the error:

>SELECT  LOWER(@@servername)
[2011-07-25 09:20:36.3] Process:CrmAsyncService |Organization:00000000-0000-0000-0000-000000000000 |Thread:   18(MSCRM:-TimerProcessor.ExecuteTimers) |Category: Platform.Async |User: 00000000-0000-0000-0000-000000000000 |Level: Error | AsyncService.OnUnhandledException
>Exception while executing async service: MSCRMAsyncService - System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
   at Microsoft.Crm.Asynchronous.JobDataAccess.RetrieveSqlServerName(Guid orgId)
   at Microsoft.Crm.Asynchronous.JobDataAccess.SelectJob(DateTime startCycleTime)
   at Microsoft.Crm.Asynchronous.JobManager.OnOrgDatabaseMaintenanceTimerEvent(Object sender, ElapsedEventArgs e)
   at Microsoft.Crm.Asynchronous.Timer.InvokeElapsed()
   at Microsoft.Crm.Asynchronous.Timer.TimerProcessor.ExecuteTimers(Object data)
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart(Object obj)

thank you

_{Monday, July 25, 2011 10:41 AM}

To clarify - as I cant seem to post the table properly - the only NULL fields are:

'location'

'provider_string'

'catalog'

'collation_name'

_{Wednesday, July 27, 2011 3:51 PM}

I resolved this by restarting the SQL server.

_{Wednesday, November 2, 2011 6:17 PM | 2 votes}

Hi

Hi

This worked for me

declare @servername varchar(30)

SELECT

@SERVERNAME = CAST(ServerProperty('servername')AS VARCHAR(30))  

use this variable as @@servername  .

Hope this will help some one .

Thanks

Hemanshu

_{Wednesday, February 29, 2012 4:10 PM}

Thanks for sharing this Jerome. Iw as able to alter a few things on my end to make my scripts run correctly from this. Nice.

_{Monday, July 9, 2012 12:31 PM}

When trying to start MSCRMAsyncService, it kicks off many stored procedures, the last 5 T-SQL statements are: SELECT LOWER(@@servername).

If it returns NULL then MSCRMAsyncService won't start, your method allows servername to be returned instead of NULL, therefore MSCRMAsyncService starts again.

Thanks Jerome. 







_{Monday, August 6, 2012 8:05 PM}

Thank you, Jerome.  This solution worked for me.

Marco Alcala

www.alcalaconsulting.com

_{Wednesday, November 21, 2012 11:55 AM}

Hi Jerome

the info was very useful.

thanks