Creating a publication

hi
I was following the walkthrough "Creating a Mobile Application with SQL Server Mobile" and when I got to the point where you create a "local publication" I couldn't find the link "Local Publication" in my Object Explorer.

I read all the help in books online however id did not tell me how to bring that link there.

I did install the replication component using the CD installation. I have SQL Server 2005 Standard Edition and Visual Studio 2005

I also found the help "Using the Publication Wizard to Create a Publication" but did not know where to locate or start the wizard.

any help will be appreciated.

1. Run sqlwb.exe to launch SQL Management Studio.

2. Make a connection to the SQL server you have installed. So it is shown as the root node of the tree view in the Object Explorer.

3. Look for Replication\Local Publications alone the tree view hierarchy.

4. Right click on the "Local Publications" to show the context menu - you can select "New Publication ...".

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights.

|||hi
thanks for your reply.
the problem is there is no "Local Publication" folder. The only thing is "Local Subscription"

how can I add that folder?
|||

I suspect you can add that folder. In general SQL 2005 Standard SKU should be able to do publication. Please check one more time that you do have Standard SKU not SQL Express SKU. You can get this information as "select @.@.version".

Thanks.

Creating a package with data flow task programmatically

I have created a simple package with 1 data flow task:
Source: data reader (to sql mobile)
Destination: oledb (to sql server)
And the aim is to do a "Select * from table_name" from source and move everything to destination database.

I have followed the steps in documentation, but the package fails! An exception is thrown with message: "Exception from HRESULT: 0xC0202072"

Since its the basic functionality of SSIS that I am implementing, it should work! I have spent 2 whole days trying to make this work and now its high time to ask for help. Can you please have a look!

Thanks,
Pragya

P.S. Any pointers to samples implementing a data flow task programmatically will help too!

Code:

Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;

//Add a SQL Mobile connection manager that is used by the component to the package.
ConnectionManager cm = package.Connections.Add("SQLMOBILE");
cm.Name = "SQL Mobile ConnectionManager";
cm.ConnectionString = "Data Source =D:\\Program Files\\Microsoft Visual Studio 8\\SmartDevices\\SDK\\SQL Server\\Mobile\\v3.0\\Northwind.sdf";

//Add a SQL Server connection manager that will be used later.
ConnectionManager cm1 = package.Connections.Add("OLEDB");
cm1.Name = "SQL Server Connection Manager";
cm1.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=tempdb;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

//Adding source component for SQL Mobile.
IDTSComponentMetaData90 component = dataFlow.ComponentMetaDataCollection.New();
component.Name = "ADONETSource";
component.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=9.0.242.0, Culture=neutral, blicKeyToken=89845dcd8080cc91";
CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
instance.SetComponentProperty("SqlCommand", "Select * from Employees");

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Adding destination component for SQL Server
IDTSComponentMetaData90 component1 = dataFlow.ComponentMetaDataCollection.New();
component1.Name = "SQL Server Destination";
component1.ComponentClassID = "DTSAdapter.SqlServerDestination.1";
CManagedComponentWrapper instance1 = component1.Instantiate();
instance1.ProvideComponentProperties();
if (component1.RuntimeConnectionCollection.Count > 0)
{
component1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
}
instance1.SetComponentProperty("BulkInsertKeepIdentity", true);
instance1.SetComponentProperty("BulkInsertKeepNulls", true);

//set path between components
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], component1.InputCollection[0]); //Assuming this is correct

// WORKS FINE TILL HERE -

// Reinitialize the metadata.
instance1.AcquireConnections(null);
instance1.ReinitializeMetaData(); //Throws exception. Message: "Exception from HRESULT: 0xC0202072" . Even if I reinitialize metadata after iterating through inputs of the component, the same exception is thrown at this statement.
instance1.ReleaseConnections();

// Iterate through the inputs of the component.
foreach (IDTSInput90 input in component1.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}

Well, 2 things I notice just from a first look is:

1. You don't set the ConnectionManagerID just the ConnectionManager, but both need to be set.

2. You don't specify a table or sqlstatement for your destination so how does the destination know what table to reinitialize its metadata for?

HTH,
Matt|||1. ConnectionManager.Id is a read only property. How to set it?
2. How can I do that?

Thanks|||

Ok! I got through one step. I added the following line:

instance1.SetComponentProperty("BulkInsertTableName","[Employees]");

to solve this.

However I have related questions,

1. Now the metadata can be initialized, but no rows are being transferred. Something is still amiss...

2. I need to manually create the employees table currently. How can i code the 'create table/view' option so that the table can be generated via code? Do I need to first compile the create table statement with column type information and then execute it using sql client? Is there a better way?

Thanks!

|||

Any information on this would help.

Thanks

Creating a Mobile Application with SQL Server Mobile - FIX

This is a great tutorial and it's a shame one of the more important steps was missed.

In the “Create the snapshot user” section you you find the steps to create the snapshot_agent account. Then in the “Create the snapshot folder” section you find the share and folder permissions. However, at no point do the instructions advise you about adding the snapshot_agent to the SQL Server Logins. The result is that agent cannot perform the initial snapshot but you won't find this out until 50 steps later after Step 10 in the section “Create a new subscription".

To get back on track, openthe Object Explorer's Security section and add the snapshot_agent to your logins. Then using the "User Mappings", set an appropriate level for the SQLMobile database role. Once completed you then need to run the agent.

Right-click the SQLMobile publication you created and select "View Snapshot Agent status". From that dialog you can select "Start" to run the agent. When it completes, you can return to the tutorial section "Create a new subscription" and continue with the tutorial.

that's one way to do it and thanks for pointing out the omission. a more common approach is to make sure the account that the snapshot agent runs as is granted permissions on the publication, the database engine, and the database.

Darren

|||

Darren,

In order to grant permissions on the database engine you need to create the login. And just granting permission on the database engine, database and the publication doesn't seem do it. You still need to asign a "Server Role" to the login. My first guess was "processadmin"; however, I tried a number of different combinations without success. Only when I set the snaphot-agent to the role of "sysadmin" was I able to get the agent to complete the process of creating the initial snapshot.

|||

that's true if the account you chose to run SQL Agent as isn't already recognized in the sysadmin role as a SQL Server login. for the average developer trying to get merge repl working the first time, SQL Server and IIS are both running on the machine that the device is connected to via ActiveSync. what I was trying to say in my last post is, whatever account you log on to you machine with, as long as it is an Administrator account, this is a good account to run the SQL Agent under. That makes the permissions issues easier to configure on SQL Server as you only have to grant that login appropriate permissions on the pub and the db. Of course you also grant permissions to the IUSR_{your machine name} account if using anonymous auth.

in a production environment, some other account should be used and as you correctly noted, this account needs to either be sysadmin on SQL Server or be granted db_reader and db_writer on the pub and published database. typically, IIS and SQL Server are on separate boxes in this scenario and a domain account is used that both machines can recognize.

Darren

|||

It is less than practical to use the "sysadmin" role in this instance. Review of the documentation shows that the minimum permissions for a "pull subscription" require the login to be associated with a user in the distribution database. At minimum be a member of the db_owner fixed database role in the distribution database.

So I have now removed the sysadmin role from the computername\snapshot_agent and added it as a user in the distribution database with the db_owner role. These permissions allow the agent to run the replication snapshot job.

Now, I have no indication that others are able to run this tutorial without specifically setting the above permission. If they are, then perhaps some other settings related to the wizards or replication itself are necessary.

The real point here is that given a tutorial which identifies each step in setting up replication (including specific names and permisions) should work according to the names/permissions identified. So lets fix the omission and move on.

Note: specific infornation is available at http://msdn2.microsoft.com/en-us/library/ms151868(d=ide).aspx

Creating a Mobile Application with SQL Server Compact Edition

Hello!!

I completed that example that I pasted in the subject part and when I try to synchronize my mobile database, the data from the server appear in my pocket pc; but when i refresh the data on my pocket pc they do not show on the server.

Can anyone give me a hand?

thanks

This sample is a download only scenario. Are you still running this code in form_Load ?:

Code Snippet

private void Form1_Load(object sender, EventArgs e)
{
DeleteDB();
Sync();

// TODO: Delete this line of code.
this.flightDataTableAdapter.Fill(this.sqlmobileDataSet.FlightData);
// TODO: Delete this line of code.
this.membershipDataTableAdapter.Fill(this.sqlmobileDataSet.MembershipData);
}

This sample allows two way sync: http://technet.microsoft.com/en-us/library/ms346580.aspx

and so does this Hands On Lab: http://msdn2.microsoft.com/en-us/library/aa454892.aspx

|||

I will try to make those example.

Thank you very much.

Creating a Mobile Application with SQL Server Compact Edition

Hello!!

I completed that example that I pasted in the subject part and when I try to synchronize my mobile database, the data from the server appear in my pocket pc; but when i refresh the data on my pocket pc they do not show on the server.

Can anyone give me a hand?

thanks

This sample is a download only scenario. Are you still running this code in form_Load ?:

Code Snippet

private void Form1_Load(object sender, EventArgs e)
{
DeleteDB();
Sync();

// TODO: Delete this line of code.
this.flightDataTableAdapter.Fill(this.sqlmobileDataSet.FlightData);
// TODO: Delete this line of code.
this.membershipDataTableAdapter.Fill(this.sqlmobileDataSet.MembershipData);
}

This sample allows two way sync: http://technet.microsoft.com/en-us/library/ms346580.aspx

and so does this Hands On Lab: http://msdn2.microsoft.com/en-us/library/aa454892.aspx

|||

I will try to make those example.

Thank you very much.

Creating .sdf file in PDA

Hi,

We are using VS 2005 with SQL Server Mobile Edition. We created the .sdf file on the desktop and added this file to our VS project and set the Build Action to content. Next we deployed our app on Symbol PDA which has Windows CE 5.0 as the OS. We followed the following steps:

1. Deploy the app.

2. Install .NET CF 2.0

3. System_SR_ENU.CAB

4. sqlce30.wce5.armv4i.CAB

5. sqlce30.dev.ENU.wce5.armv4i.CAB

6. sqlce30.repl.wce5.armv4i.CAB

Now I want to create a new .sdf file in PDA. So I open QA3.0 and type the create database command. But I get error:

You need to have a database connection opened in order to run this operation.

What could be the issue?

Regards,

Vilas

You cannot execute "CREATE DATABASE" unless you have an open connection, as stated. Use the Database icon with the green arrow at the bottom toolbar of the QA 3.0, then you will get a dialog where you create a new database.