Configure Distribution Wizard - unable to connect to server

I am been trying to setup replication on my local Developer edition copy of SQL Server 2005 SP1, but I keep running to into this unable to connect to server error within MS SQL Server Management Studio environment. I tried various connection settings under the configuration manager and things like adding entries to my host file with no luck. Understand that this requires the server name but failing at providing this for the connection to start the wizard. I am missing something simple here?

Darryl

what is the full error? Is this a remote distributor, and is this a machine in the same domain? If so, make sure to enable remote connections in the SAC.|||

Sorry that I didn't post the full error details on the first post. This is now posted below.

The SQL Server is running locally on the same PC in which I am using MS SQL Server Management Studio. The PC is part of a domain and I am logged using a domain account of which I am the admin on PC. I am able to connect/ open query windows using my domain windows account or SQL Server sa account. This the error below is when to 'right click' on Replication folder and select Configure Distribution. The wizard screen does not appear, just the error. No replication is setup on this server, but the Compact connections tools (IIS setup) were run on this same PC.

Darryl

===================================

SQL Server is unable to connect to server 'DUTTONDARRYL1'. (Configure Distribution Wizard)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.ConfigureWizardErrorSR&EvtID=CantConnect&LinkId=20476

===================================

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'INITIALSETUP1'. (Replication.Utilities)

Program Location:

at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn)
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.Open()
at Microsoft.SqlServer.Management.UI.ConfigureDistributionWizard.PrepareToShow()

|||

Did you rename your sql server? In the beginning of the error message it says it cannot connect to DUTTONDARRYL1, but in the end it says INITIALSETUP1. I would remove all the hostfile, aliases, etc. you modified/added, and make sure the sql server has the same name as your machine.

|||

Where does this INITIALSETUP1 name come from or where is it stored? It almost seems like something left over from a partial install. There is no INITIALSETUP1 in the host file and there are no aliases.

Darryl

|||I don't know, you tell me. Do select * from sysservers to see if the entries are correct.|||

Thanks for the select stament. Here is the result with the first 3 column

srvid srvsstatus srvname

0 1089 INITIALSETUP1

1 1184 BAMPrimaryImportSrv

2 1089 DUTTONDARRYL1

3 1089 MSSQLSERVER

The last 2 entries I added today through a store procedure (sp_addserver ?) base on reading other threads on this topic. Ok, I now see were this INITIALSETUP1 is getting stored, can I just write an update statement here against the first record or is there a better way to change the name?

Darryl

PS: Thanks for the quick followup

|||

What are the reasons for calling sp_addserver? You shouldn't be doing this unless you want to use linked servers. See topic "sp_addserver" in Books Online. If you do not need INITIALSETUP1 then you should remove it. Same with MSSQLSERVER entry. You should remove them with sp_dropserver.

|||

Ok I found the solution....

I did need to use sp_addserver, but I was using it incorrectly. As per the on-line books on sp_addserver , this is used for defining a remote server or the name of the local instance of SQL Server. The local instance is what is important to bring up the configuration distribution wizard without the error. Here are my steps:

1) Remove all the servers listed from sp_helpserver or 'select * from sysservers' (as per Greg sugguestion) that are not required or needed for linking. In my case, I removed all server by using the sp_ dropserver

2) Add in the local server using sp_addserver with the local parameter (i.e. sp_addserver @.server'DUTTONDARRYL1', @.local='LOCAL')

3) Re-start SQL Server

You can check if the local instance come up properly if the 'select @.@.servername' displays the server name.

Darryl

PS: Thanks Greg Y for helping me work through this problem

Configuaration of RS

I am unable to successfully configure RS. It fails at initialization and I get an error while switching panels, most likely caused by retrieving WMI properties.
Details:
Verison of the report server database is either in a format that is not valid or cannot be read. The found version in 'Unknown'. The expected version is 'C.0.8.40'. To continue update the version of the report server database and verify access rights.
I am thus unable to deploy a report - Error says that Report Designer is unable to connect to Report Server.................

Thanks for any helpCan you use the config tool to create a new RS database or are you seeing this error when using the config tool? If you are seeing the error in the config tool and unable to create a new DB you should try doing a repair on the Report Server via Add/Remove programs.|||I am seeing the error during configuration. I tried to repair, then reinstalled. As I tried to reconfigure again - I was unable to specify a report directory as well.|||

I am seeing the error during configuration. I tried to repair, then reinstalled. As I tried to reconfigure again - I was unable to specify a report directory as well.

|||I don't understand where you can not set a report directory. Do you mean a virtual directory? What error are you seeing? Have you checked in ReportServerService<timestamp>.log file to see if there are any errors?|||I was able to successfully configure the report server after deleting the old directory in IIS that conflicted with it. Now when I try to access the report server via http:/localhost/reportserver my browser states that it is unable to access the IIS Metabase. I have checked to make sure that I have read permissions for that site.|||

Hi All,

I am getting the following error in reporting services configuration. Could someone please help out.

1) The status of report server is running but initilized found to be No.

2) The report server virtual directory and report manager virtual directory is in Green.

There was an error while switching panels. The most likely cause is an error retrieving WMI properties. The Exception details are : An internal error occurred on the report server.See the error log for more details. ( rsInternalError) "

Could someone please give pointers where in the problem is occuring asap.

Thanks so much,

Kushal

Configuaration of RS

I am unable to successfully configure RS. It fails at initialization and I get an error while switching panels, most likely caused by retrieving WMI properties.
Details:
Verison of the report server database is either in a format that is not valid or cannot be read. The found version in 'Unknown'. The expected version is 'C.0.8.40'. To continue update the version of the report server database and verify access rights.
I am thus unable to deploy a report - Error says that Report Designer is unable to connect to Report Server.................

Thanks for any helpCan you use the config tool to create a new RS database or are you seeing this error when using the config tool? If you are seeing the error in the config tool and unable to create a new DB you should try doing a repair on the Report Server via Add/Remove programs.|||I am seeing the error during configuration. I tried to repair, then reinstalled. As I tried to reconfigure again - I was unable to specify a report directory as well.|||

I am seeing the error during configuration. I tried to repair, then reinstalled. As I tried to reconfigure again - I was unable to specify a report directory as well.

|||I don't understand where you can not set a report directory. Do you mean a virtual directory? What error are you seeing? Have you checked in ReportServerService<timestamp>.log file to see if there are any errors?|||I was able to successfully configure the report server after deleting the old directory in IIS that conflicted with it. Now when I try to access the report server via http:/localhost/reportserver my browser states that it is unable to access the IIS Metabase. I have checked to make sure that I have read permissions for that site.|||

Hi All,

I am getting the following error in reporting services configuration. Could someone please help out.

1) The status of report server is running but initilized found to be No.

2) The report server virtual directory and report manager virtual directory is in Green.

There was an error while switching panels. The most likely cause is an error retrieving WMI properties. The Exception details are : An internal error occurred on the report server.See the error log for more details. ( rsInternalError) "

Could someone please give pointers where in the problem is occuring asap.

Thanks so much,

Kushal

sqlsql

conditional formatting in reporting services (2005)

I am new to reporting services and am unable to get cell colouring conditional formatting to work in of a report. Basically I need to have the following:

>-5% = "Red"

Between -5% and -1% = "Green"

0% = "White"

Between 1% and 5% = "Green"

>5% = "Red"

Here is what I have tried to use:

=iif((Fields!TrueUpPercQ1.Value >0 and

Fields!TrueUpPercQ1.Value <=0.05) or (Fields!TrueUpPercQ1.Value < 0 and

Fields!TrueUpPercQ1.Value >=-0.05), "Green",

iif(Fields!TrueUpPercQ1.Value =0, "White","Red"))

Any ideas?

Thanks

Try this....

=iif((Fields!TrueUpPercQ1.Value > 0, "Red", IIF(Fields!TrueUpPercQ1.Value <=0.05, "Red", IIF(Fields!TrueUpPercQ1.Value < 0, "Blue,

IIF(Fields!TrueUpPercQ1.Value >=-0.05, "Green", iif(Fields!TrueUpPercQ1.Value =0, "White","Red")))))

You may want to tweak this to suit your need. Also make sure the number of closing brackets are correct. This is the way the IIF is used. Hope it helps. Let me know.

|||

Iif would work, but I think switch would be better

Try this...you have to have even number of arguments for it to work correctly. The first is your statement to evaluate to true then the second argument is executed, if false it proceeds to the next (3rd) argument until something evaluates as true.

=SWITCH(Fields!TrueUpPercQ1.Value <=0.05, "Red", Fields!TrueUpPercQ1.Value < 0, "Green", Fields!TrueUpPercQ1.Value =0, "White")

1 2 3 4 5 6

|||

Iif would work, but I think switch would be better

Try this...you have to have even number of arguments for it to work correctly. The first is your statement to evaluate to true then the second argument is executed, if false it proceeds to the next (3rd) argument until something evaluates as true.

=SWITCH(Fields!TrueUpPercQ1.Value <=0.05, "Red", Fields!TrueUpPercQ1.Value < 0, "Green", Fields!TrueUpPercQ1.Value =0, "White")

concatenation with space

Hi,

I am unable to concat 2 fields with a space between them in sql query.
I want to write my query in following fashion only as there are many conditions which I concat. Thus I am using variable @.sql_st and not the direct sql statement.

Following query works perfect
DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

but when modified to get space between first & last name of employee I get an error
DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + ' ' + EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

Pls reply ASAP.

Thanks
ShubhangiHi,

USE the below...

DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + ' + ' ' + 'EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

Cheers,
Sharmila|||Hi,

Space doesn't appears between first & last name using the below query.
please reply.

Quote:

Originally Posted by Senthil

Hi,

USE the below...

DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + ' + ' ' + 'EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

Cheers,
Sharmila

|||Hi,

Sorry Please use like this..

DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + '' '' + ' + ' ' + 'EM.LAST_NAME) as emp_name from employee_master em'
print @.SQL_ST

Regards,
Sharmila

concatenation

Hi,

I have previously posted but the reply given didn't solve my purpose.
Please check & revert.

I am unable to concat 2 fields with a space between them in sql query.
I want to write my query in following fashion only as there are many conditions which I concat. Thus I am using variable @.sql_st and not the direct sql statement.

Following query works perfect
DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

but when modified to get space between first & last name of employee I get an error
DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + ' ' + EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

Pls reply ASAP.

Thanks
Shubhangi/*Solo:*/
select (rtrim(EM.FIRST_NAME) + ' ' + rtrim(EM.LAST_NAME)) as pendejo from employee em

--Utilizando una variable:
declare @.x varchar(200)
set @.x = 'select (rtrim(casefunctionality) + '' '' + rtrim(casename)) as mames from employee em'
exec (@.x)

El problema es que le debes de poner doble comilla simple.

El RTrim es slo para quitarle los espacios.|||Hi,
The following code will solve your problem
instead of two single quotes use four single quotes to concatnate two fields
-------------------
declare @.str1 varchar(8000)
set @.str1=('select EM.EMPLOYEE_ID,EM.FIRST_NAME+'' ''+EM.LAST_NAME as EMPLOYEENAME from EMPLOYEE_MASTER EM')
print @.str1
exec (@.str1)