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

Conditional Container

May be it's too late, but I think this requests could be scheduled at least
for a SP1 if it's not possible for the RTM.

1) Execute Task without debugger: it would be very nice to be able to execute
a single task without going in debugging mode. Just as you would ask "Start
Without Debugging CTRL+F5" but for a single task

2) Customize default properties for task and component: when you drag a task
on the package you get a default value for the properties that you could want
to change; often I need to change the same property in the same way each time
(for example I'd like to set the Batch Size for a OLE DB destination to 1000
instead than 0)

3) If you open a package and connections to data source are not available,
propose to "work offline" at the first failed connection.

IMHO, these features would be very important for developer productivity.

Marco Russo
http://sqljunkies.com/weblog/sqlbi

Hi Marco,

All great suggestions. Can you open them in BetaPlace? Unfortunately they're too late for SQL Server 2005 but we'd love to revisit them for the future.

For #2, Copy/Paste might be a short term solution.

regards,
ash|||I cannot find the thread where someone from Microsoft solicited suggestions for changes; I thought it was in a thread by Jamie Thompson, but somehow I am now overlooking it (or misremembering).
In any case, in the hopes that someone relevant sees this, I have three more.
* In any editor for any component, have a visible indicator on all properties which are supplanted at run-time by expressions. For example, have the values in red. This is to indicate that what you are seeing is not what will be used.
* Mark all the boxes which have event handlers attached. As above, this is to inform the human that there is more here than is apparent, and that the human should go track down the "more" (in this case, event handlers), to really find out what is happening.
* Have a list, or tree view, of all the event handlers. I've not figured out anyway to find, say 20 event handlers scattered across 500 boxes in many packages, except by the slowly going through and double-clicking on every box looking for event handlers. This seems to me a terrible way to find event handlers; I don't know if I'm overlooking something obvious (I hope), but in case not, and perhaps in any case?, this request for enhancement.

(I cannot log in to betaplace; I spent some time trying to do so, and waving my mouse around clicking on invisible buttons, and I never got past a page saying that my account would be activated someday, I think, and I cannot even remember the sequence of steps to get there again now.)|||Great ideas Perry, I second all of them. The one about indicating in the control-flow which tasks/containers have eventhandlers on them is inspired.

Your idea about a visual representation of which properties have expressions on them has already been raised. Hopefully we'll see it in the next version!

-Jamie|||Yes, I third them! In addition, it would be nice to see the ability to copy/paste/modify multiple variables. Managing variables and managing parent variables in package configurations is not easily done incurrent state, especially when you are dealing with 100+ packages all sharing same/similar variables.|||How about something that shows underlying execution plan (akin to query plan) for the entire package with cost weightage?

regards,
Nitesh|||If you've been using Integration Services and have some feedback for how to make it better, we'd love to hear more.

Please add to this thread what you'd like to see added, fixed, changed, tweeked, or removed from Integration Services.

Your feedback is valuable.
We can't promise we'll be able to make it all happen, but certainly the guidance you give here will influence planning for the next version of Integration services.

Thanks,
|||The biggest pains for me so far in designing our ETL for our warehouse have been:

- Reusing data flows, I am doing a hack that lets the data flows run over a set of tables, performing work on the common columns. What would be useful is if you can define a "table set" within SSIS and then bind a data flow to the table set (where the table set is limited to the columns/types common across all tables.) I don't know if this would have to fit into the foreach stuff, or if it would be all within the data flow itself.
- Working with tables with LOTs of columns. I have a table with about 200 columns or so that I need to do a slowly changing dimension transform on. I also need to write script components that output 200 columns for inserting into the table. The script task input/output dialog makes it painful to enter the variables one by one, and the SCD wizard makes it too painful to do it by hand, so I actually went into the XML itself and changed the stuff (carefully :)) Not sure how to address this, but another major thing that's probably more of an issue to fix is that the SCD component goes insanely slow when you double click on it if you have a whole lot of columns like me. (Takes a good 3-5 minutes to come up.)
- I posted a thread earlier, but to re-iterate -- since we can't reuse data flows most of the time nor script tasks, cut and pasting should be cleaned up a bit so the formatting doesn't get completely destroyed when you paste in a huge block of data flow/control flow tasks.
- Undo! :)
- Another small feature suggestion would be a more complex lookup task that had inherently a built in behavior for when the lookup fails. I have an "Unknown" member for each dimension, and if my lookup fails for a certain member of a fact table I need to link it to the Unknown member. What this translates to are a conditional split for if the key being looked up is NULL (or 0) and then setting it to zero if it was NULL or actually doing the lookup, and then doing a union of the rows again. I realize I could just rely upon the error output of the Lookup, but that seems broken to me since "Unknown" is an expected behavior. The ideal situation is for the Lookup Task to have an optional default value to use if the lookup fails and/or if the column being looked up is NULL.|||Great suggestions!
Keep them coming!
K|||On the note of the Lookup Task, I think it's probably an extremely common use case where you have to translate a set of fact table business keys to surrogate dimension keys. (Project REAL, for example, seems to have a huge data flow to do this, and so do I.) With this in mind, it might be useful to have one single lookup task to translate all the keys (my current package has like 15 lookup tasks and a whole lot of conditionals for the aforementioned "Unknown" behavior checking.) Having one task that has a series of "table, join key, lookup value, lookup column, default value if null or not found" would consolidate my 40-50 tasks into a single one (which probably could internally do the lookups in parallel, increasing performance.)
|||

Ok here's my wish list,

1. Advanced Editor support for >1 input. (This should enable the script component with > 1 input)

2. Read only access to the whole package from componentmetadata, not just that related to the component.

3. Parallel For each loops. Performance.

4. Option on Raw file to create once per package. This allows the same raw destination to be used in a loop

5. Debug support for script component (not just the task)

6. Parallel multicast. Says it all really performance (I know the memory issue but it should be an option. Allows for the creation of a new execution tree. It would be great if the compiler (process that produces execution tree) could figure this out. This would probably need to now the distribution of data being processed.

7. Suggest Types for flat files to provide the option of reading a whole file. This is to avoid encountering bugs during run time, which is very time consuming.

8. Suggest types for flat files to all for data to be just strings, rather than convert data to proper types. This is for performance

9. IIS Log file connection both source and destination would be good. But would settle for source.

10. Multiple data readers out of package. This would enable a package to produce multiple summaries and have them consumed by a report or other application.

11. Be able to drag a connection from one component to another. Its a real pain to delete one connection to be recreate it to the other component. This looses any data viewers

That'll do for now.

|||Thanks Simon. Excellent input. Thanks!
Anyone else?
K|||

I would like to see 3 big key improvements within SSIS. I have raised this before, Kirk asked me to send him a mail, which I never got around to do it. Sorry Kirk.

1. Data Profiler. This is quite crucial when you analyse the data to determine how bad the data is etc. Yes I know, the feature is sort of there but it is not good enough. It need to be improved considerably. We should be able to put any type of files and profile it before we start the work. Saves lot of time. It should be quick and simple to do, in the meantime it should be powerful.

2. Meta Data Management Tool. This can be web based tool / something along those line, which can be given to the business users to indetify for example, how we derive Net Sales column in the fact table. From my own experience, spent hours / days explaining how we derive each column. In a huge data warehousing environment it is very time consuming. This is not fun, i rather be writing SSIS package instead .

3. Dependancy Analysis. I would like to see a tool that would do the dependancy analysis on the fly, if I specify, that I am going to drop column A, it should run some kind of routine and tells me if you drop this column from your SSIS package, it will affect this table, cube and package etc. Run the check against the metadata only, therefore it should be quick. Save lots of time and avoid mistakes happening.

These are my requests. I know they are big requests, but I think we do need them in Microsoft environment as other competitors got similar products.

What everyone else think about these features.

Thanks
Sutha

|||I've already fed alot of stuff back to Kirk offline but just for the edification of everyone else, here are some ideas:
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/16/1419.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/09/07/2130.aspx

-Jamie|||Sometimes .dtsx files get corrupted. Don't know why...don't know how!

It would be useful to have a tool to analyse a corrupt .dtsx file to tell you exactly what's wrong with it, how to fix it, possibly even fix it for you etc.... The error messages you get when trying to load it aren't really useful.

-Jamie

concurrency down to the column level - sql

Using VS05 pro SP1...

(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)

This is for a telephony application using Last In Wins concurrency...

Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.

We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...

- Fill table A and table B identically

- change column1 value in table A

- Update table A

- change column2 value in table B

- Update table B - this will generate a concurrency error.

Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.

So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.

I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.

Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.

Thanks!

Bob

hi Bob,

BobInIndy wrote:

(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)

This is for a telephony application using Last In Wins concurrency...

Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.

We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...

- Fill table A and table B identically

- change column1 value in table A

- Update table A

- change column2 value in table B

- Update table B - this will generate a concurrency error.

Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.

So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.

I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.

Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.

Thanks!

Bob

from a SQL Server point of view, you have to "build" a DDL script (aka stored procedure) for each single (and combination of) attribute of your entity, so that you can "execute" UPDATE obj SET singleCol = singleVal WHERE pk = xx, but this obviously will lead to a very high proliferation of stored procedures for each single base table.. another solution would be dynamic SQL, where the desired UPDATE statement can be "constructed" to reflect only the modified attributes, but I do personally not like accepting dynamic SQL against "my" tables...

or, depending on your NULL behaviour, you even could create a single proc where the NULL does not qualify for updates for the single attribute, that's to say something like

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, Data0 varchar(10) NOT NULL, Data1 int NOT NULL DEFAULT 10, Data2 datetime NOT NULL DEFAULT '20070101', rv timestamp NOT NULL ); INSERT INTO [dbo].[TestTB] ([Id], [Data0], [Data1], [Data2]) VALUES ( 1, 'Test', DEFAULT, DEFAULT ); GO CREATE PROCEDURE dbo.usp_Test ( @.Id int, @.Data0 varchar(10) = NULL, @.Data1 int = NULL, @.Data2 datetime = NULL, @.rv timestamp OUTPUT ) AS BEGIN DECLARE @.r int; BEGIN TRY UPDATE dbo.TestTB SET [Data0] = ISNULL(@.Data0, [Data0]), [Data1] = ISNULL(@.Data1, [Data1]), [Data2] = ISNULL(@.Data2, [Data2]) WHERE [Id] = @.Id AND [rv] = @.rv; SET @.r = @.@.ROWCOUNT; IF @.r = 0 BEGIN SELECT @.rv = [rv] FROM dbo.TestTB WHERE [Id] = @.Id; RAISERROR ('Concurrency violation', 16, 10); END; RETURN 0; END TRY BEGIN CATCH DECLARE @.ErrorMSG varchar(2000); SET @.ErrorMSG = ERROR_MESSAGE(); RAISERROR (@.ErrorMSG, 16, 1); RETURN -100; END CATCH; END; GO DECLARE @.Id int, @.Data0 varchar(10), @.Data1 int, @.Data2 datetime, @.rv timestamp; SELECT @.Id = 1, @.Data0 = '1st Mod', @.Data1 = NULL, @.Data2 = NULL, @.rv = [rv] FROM dbo.TestTB WHERE [Id] = 1; PRINT 'parameters to use'; SELECT @.Id, @.Data0, @.Data1, @.Data2, @.rv; EXEC dbo.usp_Test @.Id = 1, @.Data0 = @.Data0, @.Data1 = @.Data1, @.Data2 = @.Data2, @.rv = @.rv OUTPUT; PRINT 'Modified data'; SELECT * FROM dbo.TestTB; PRINT 'key parameters'; SELECT @.Id, @.rv; PRINT '--'; PRINT 'now let''s try a concurrency exception;'; PRINT 'manual modification to simulate a conflict'; UPDATE dbo.TestTB SET [Data1] = 1000 WHERE [Id] = 1; PRINT 'which results in a timestamp change'; SELECT * FROM dbo.TestTB; SELECT @.Data0 = '2st Mod', @.Data1 = NULL, @.Data2 = NULL; EXEC dbo.usp_Test @.Id = 1, @.Data0 = @.Data0, @.Data1 = @.Data1, @.Data2 = @.Data2, @.rv = @.rv OUTPUT; SELECT * FROM dbo.TestTB; PRINT 'fails, as Data1 now is 1000'; PRINT 'but you have the new timestamp value, so just resubmitit'; EXEC dbo.usp_Test @.Id = 1, @.Data0 = @.Data0, @.Data1 = @.Data1, @.Data2 = @.Data2, @.rv = @.rv OUTPUT; PRINT 'only your modified attribute is persisted'; SELECT * FROM dbo.TestTB; GO DROP PROCEDURE dbo.usp_Test; DROP TABLE dbo.TestTB; --<- parameters to use -- - -- -- 1 1st Mod NULL NULL 0x00000000000007EF Modified data Id Data0 Data1 Data2 rv -- - -- -- 1 1st Mod 10 2007-01-01 00:00:00.000 0x00000000000007F0 key parameters -- 1 0x00000000000007EF -- now let's try a concurrency exception; manual modification to simulate a conflict which results in a timestamp change Id Data0 Data1 Data2 rv -- - -- -- 1 1st Mod 1000 2007-01-01 00:00:00.000 0x00000000000007F1 Msg 50000, Level 16, State 1, Procedure usp_Test, Line 35 Concurrency violation Id Data0 Data1 Data2 rv -- - -- -- 1 1st Mod 1000 2007-01-01 00:00:00.000 0x00000000000007F1 fails, as Data1 now is 1000 but you have the new timestamp value, so just resubmitit only your modified attribute is persisted Id Data0 Data1 Data2 rv -- - -- -- 1 2st Mod 1000 2007-01-01 00:00:00.000 0x00000000000007F2

the logic is: if NULL is passed, the column will persists it's original value, else the new provided value is stored..

in case of concurrency violation (a trivial check is here performed), an exception is risen, and the new timestamp value is returned as output parameter.. you can this way do something (if you like) as informing you will overwrite someone's work (only if your modified column will collide with his/her column) and, having the current timestamp, you can re-execute the UDPATE statement..

this way a single procedure can accomplish the desired behaviour of modifying the "changed" columns, as in case of NULL the underlying attribute's value is conserved, but this obviously require your "logic" to use a NULL as a "logical" treshhold for "unchanged values"...

you should be able to access your original and current values in ado.net to populate the command parameters as "required"...

regards