locked
@@servername returning NULL RRS feed

  • Question

  •  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

    Thursday, March 9, 2006 7:33 PM

Answers

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

    Friday, March 10, 2006 12:11 AM

All replies

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

    Friday, March 10, 2006 12:11 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 1:59 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
    Monday, November 12, 2007 7:44 AM
  • 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

    Wednesday, July 13, 2011 10:19 PM
  • 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:33 AM
  • To clarify - as I cant seem to post the table properly - the only NULL fields are:

    'location'

    'provider_string'

    'catalog'

    'collation_name'

    Monday, July 25, 2011 10:41 AM
  • I resolved this by restarting the SQL server.
    Wednesday, July 27, 2011 3:51 PM
  • 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, November 2, 2011 6:17 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.
    Wednesday, February 29, 2012 4:10 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, July 9, 2012 12:31 PM
  • Thank you, Jerome.  This solution worked for me.

    Marco Alcala

    www.alcalaconsulting.com

    Monday, August 6, 2012 8:05 PM
  • Hi Jerome

    the info was very useful.

    thanks

    Wednesday, November 21, 2012 11:55 AM