Problem function year in sql mobile

Dear , all

I have a problem in fucntion year in sql mobile , Use select Date_in from Test Where Year(Date_in) = 8

Brg

Tingnong

The YEAR function does not exist in SQL Compact, you can use:

Code Snippet

DATEPART(yy, date)

like so:

Code Snippet

select [Order Date] from Orders Where DATEPART(yy, [Order date]) = 1991

(Using Northwind.sdf inculded with SQL Compact 3.1 SDK)

Problem dropping replication support

I am working on establishing a merge replication process between SQL Server 2005 and SQL Mobile 2005. I started with a new SQL Server 2005 instance and went through the Sample for SQL Mobile merge replication. So far so good.

Today, I tried to drop all support for replication on my current SQL 2005 test instance, so that I can start from a fresh instance and do another. I used Management Studio to drop all publisher and distribution settings and had several errors occur, where some roles were not allowed to be dropped because they had membership in them.

I dropped all the users that I added to the SQL logins and tried again.

Now I am trying to run the following script:

use AdventureWorks
exec sp_replicationdboption @.dbname = N'AdventureWorks', @.optname = N'merge publish', @.value = N'false', @.ignore_distributor = 'true'

-- Dropping the distribution publishers
exec sp_dropdistpublisher @.publisher = N'XP-MIKED-LAPTOP'
GO

-- Dropping the distribution databases
use master
exec sp_dropdistributiondb @.database = N'distribution'
GO

/****** Uninstalling the server XP-MIKED-LAPTOP as a Distributor. Script Date: 1/14/2006 2:16:29 PM ******/

use master
exec sp_dropdistributor @.no_checks = 1, @.ignore_distributor = 1
GO

The error I am getting from the first batch (sp_replicationdboption) is this:

Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.

To me it looks like all the publication objects have been already removed from AdventureWorks, but in sysdatabases, the category column still says 4 (merge publication). Since I can't just do this anymore:

UPDATE MASTER.DBO.SYSDATABASES
set category = 0
where dbid=8

I just don't know what I can do at this point. I can't even create a new publication in AdventureWorks because it thinks there is a sysmergepublications table in there and fails when there isn't.

try sp_removedbreplication.

|||

Greg Yvkoff wrote:

try sp_removedbreplication.

Yes, that was exactly it. I got to the end of the KB article 324401 and found that stored proc. It worked like a charm.

Problem creating SQL Server Mobile Subscription

Hi there

When I try and create a subscription in SQL Server Mobile to a published SQL Server 2005 database, I get the following problem error during the data synchronization process:

Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect.

HRESULT 0x80004005 (29061)

Any help would be gratefully appreciated.

Regards

JB

If you are using integrated authentication, make sure that the IIS user is able to connect to the publisher server.|||

Mahesh

Thanks for the reply. I have to confess I am not too clear on what you mean by this...still learning...

I used the computername/iuser_computername account to create the publisher. This user has access to the snapshot folder and the virtual directory for the web server. (by the way, all of this is running on the same machine - VS2005, SQL Server 2005, IIS 5.1, and SQL Server Mobile 2005)

As far as I can see, computername/iuser_computername has been used consistently during the creation of the publication (it appears on the Publication Access List for example). I use my own log in on the PC which has admin rights as the Snapshot Agent login for creation of the snapshots.

Where am I going wrong?

Thanks

|||Can you verify if the IUSR_xxx has access to the database by connecting to the server and using the database from an osql command?|||

I am getting the same error as this. When I go to the SQLCESA30.LOG file in the "C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\SqlMobileReplication" directory, the two entries that I get in the log are as follows:

2006/01/07 16:59:36 Hr=00000000 SQLCESA30.DLL loaded 0
2006/01/07 16:59:58 Hr=80004005 ERR:OpenDB failed getting pub version 28627

I'll start a new thread.

|||I've started a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193693&SiteID=1 for "Hr=80004005 ERR:OpenDB failed getting pub version 28627"|||

JB,

Chris, who started the other thread (with the note above) has had very similar issue. He troubleshooted the problem and has been kind enough to post the analysis and the resolution in his thread. Please go through that thread and see if it helps your problem. From what I see the error messages are very much related.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=194640&SiteID=1&mode=1

|||

Hi

I have been Getting the Same Error unable to connect to Sql server or IIS configured properly

Any one who any help me in this regard.

How we would make sure that --> If you are using integrated authentication, make sure that the IIS user is able to connect to the publisher server.

Rg Ad

|||I try use IIS 7, but its difficult than I though. When create publication under IIS 6 I just only add IUSR into login, then create publication and put IUSR into PAL, then subscription just work fine. But when use IIS 7 I follow the steps for IIS 6 then when try synchronize data it said that the IUSR is not valid SQL Server user, is the IIS 7 have another guest account so I can put it into login?

Thanks,

Problem creating SQL Server Mobile Subscription

Hi there

When I try and create a subscription in SQL Server Mobile to a published SQL Server 2005 database, I get the following problem error during the data synchronization process:

Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect.

HRESULT 0x80004005 (29061)

Any help would be gratefully appreciated.

Regards

JB

If you are using integrated authentication, make sure that the IIS user is able to connect to the publisher server.|||

Mahesh

Thanks for the reply. I have to confess I am not too clear on what you mean by this...still learning...

I used the computername/iuser_computername account to create the publisher. This user has access to the snapshot folder and the virtual directory for the web server. (by the way, all of this is running on the same machine - VS2005, SQL Server 2005, IIS 5.1, and SQL Server Mobile 2005)

As far as I can see, computername/iuser_computername has been used consistently during the creation of the publication (it appears on the Publication Access List for example). I use my own log in on the PC which has admin rights as the Snapshot Agent login for creation of the snapshots.

Where am I going wrong?

Thanks

|||Can you verify if the IUSR_xxx has access to the database by connecting to the server and using the database from an osql command?|||

I am getting the same error as this. When I go to the SQLCESA30.LOG file in the "C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\SqlMobileReplication" directory, the two entries that I get in the log are as follows:

2006/01/07 16:59:36 Hr=00000000 SQLCESA30.DLL loaded 0
2006/01/07 16:59:58 Hr=80004005 ERR:OpenDB failed getting pub version 28627

I'll start a new thread.

|||I've started a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193693&SiteID=1 for "Hr=80004005 ERR:OpenDB failed getting pub version 28627"|||

JB,

Chris, who started the other thread (with the note above) has had very similar issue. He troubleshooted the problem and has been kind enough to post the analysis and the resolution in his thread. Please go through that thread and see if it helps your problem. From what I see the error messages are very much related.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=194640&SiteID=1&mode=1

|||

Hi

I have been Getting the Same Error unable to connect to Sql server or IIS configured properly

Any one who any help me in this regard.

How we would make sure that --> If you are using integrated authentication, make sure that the IIS user is able to connect to the publisher server.

Rg Ad

|||I try use IIS 7, but its difficult than I though. When create publication under IIS 6 I just only add IUSR into login, then create publication and put IUSR into PAL, then subscription just work fine. But when use IIS 7 I follow the steps for IIS 6 then when try synchronize data it said that the IUSR is not valid SQL Server user, is the IIS 7 have another guest account so I can put it into login?

Thanks,

Problem creating SQL Server Mobile Subscription

Hi there

When I try and create a subscription in SQL Server Mobile to a published SQL Server 2005 database, I get the following problem error during the data synchronization process:

Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect.

HRESULT 0x80004005 (29061)

Any help would be gratefully appreciated.

Regards

JB

If you are using integrated authentication, make sure that the IIS user is able to connect to the publisher server.|||

Mahesh

Thanks for the reply. I have to confess I am not too clear on what you mean by this...still learning...

I used the computername/iuser_computername account to create the publisher. This user has access to the snapshot folder and the virtual directory for the web server. (by the way, all of this is running on the same machine - VS2005, SQL Server 2005, IIS 5.1, and SQL Server Mobile 2005)

As far as I can see, computername/iuser_computername has been used consistently during the creation of the publication (it appears on the Publication Access List for example). I use my own log in on the PC which has admin rights as the Snapshot Agent login for creation of the snapshots.

Where am I going wrong?

Thanks

|||Can you verify if the IUSR_xxx has access to the database by connecting to the server and using the database from an osql command?|||

I am getting the same error as this. When I go to the SQLCESA30.LOG file in the "C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\SqlMobileReplication" directory, the two entries that I get in the log are as follows:

2006/01/07 16:59:36 Hr=00000000 SQLCESA30.DLL loaded 0
2006/01/07 16:59:58 Hr=80004005 ERR:OpenDB failed getting pub version 28627

I'll start a new thread.

|||I've started a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193693&SiteID=1 for "Hr=80004005 ERR:OpenDB failed getting pub version 28627"|||

JB,

Chris, who started the other thread (with the note above) has had very similar issue. He troubleshooted the problem and has been kind enough to post the analysis and the resolution in his thread. Please go through that thread and see if it helps your problem. From what I see the error messages are very much related.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=194640&SiteID=1&mode=1

|||

Hi

I have been Getting the Same Error unable to connect to Sql server or IIS configured properly

Any one who any help me in this regard.

How we would make sure that --> If you are using integrated authentication, make sure that the IIS user is able to connect to the publisher server.

Rg Ad

|||I try use IIS 7, but its difficult than I though. When create publication under IIS 6 I just only add IUSR into login, then create publication and put IUSR into PAL, then subscription just work fine. But when use IIS 7 I follow the steps for IIS 6 then when try synchronize data it said that the IUSR is not valid SQL Server user, is the IIS 7 have another guest account so I can put it into login?

Thanks,

Problem creating SQL Server Mobile Subscription

Hi there

When I try and create a subscription in SQL Server Mobile to a published SQL Server 2005 database, I get the following problem error during the data synchronization process:

Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect.

HRESULT 0x80004005 (29061)

Any help would be gratefully appreciated.

Regards

JB

If you are using integrated authentication, make sure that the IIS user is able to connect to the publisher server.|||

Mahesh

Thanks for the reply. I have to confess I am not too clear on what you mean by this...still learning...

I used the computername/iuser_computername account to create the publisher. This user has access to the snapshot folder and the virtual directory for the web server. (by the way, all of this is running on the same machine - VS2005, SQL Server 2005, IIS 5.1, and SQL Server Mobile 2005)

As far as I can see, computername/iuser_computername has been used consistently during the creation of the publication (it appears on the Publication Access List for example). I use my own log in on the PC which has admin rights as the Snapshot Agent login for creation of the snapshots.

Where am I going wrong?

Thanks

|||Can you verify if the IUSR_xxx has access to the database by connecting to the server and using the database from an osql command?|||

I am getting the same error as this. When I go to the SQLCESA30.LOG file in the "C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\SqlMobileReplication" directory, the two entries that I get in the log are as follows:

2006/01/07 16:59:36 Hr=00000000 SQLCESA30.DLL loaded 0
2006/01/07 16:59:58 Hr=80004005 ERR:OpenDB failed getting pub version 28627

I'll start a new thread.

|||I've started a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193693&SiteID=1 for "Hr=80004005 ERR:OpenDB failed getting pub version 28627"|||

JB,

Chris, who started the other thread (with the note above) has had very similar issue. He troubleshooted the problem and has been kind enough to post the analysis and the resolution in his thread. Please go through that thread and see if it helps your problem. From what I see the error messages are very much related.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=194640&SiteID=1&mode=1

|||

Hi

I have been Getting the Same Error unable to connect to Sql server or IIS configured properly

Any one who any help me in this regard.

How we would make sure that --> If you are using integrated authentication, make sure that the IIS user is able to connect to the publisher server.

Rg Ad

|||I try use IIS 7, but its difficult than I though. When create publication under IIS 6 I just only add IUSR into login, then create publication and put IUSR into PAL, then subscription just work fine. But when use IIS 7 I follow the steps for IIS 6 then when try synchronize data it said that the IUSR is not valid SQL Server user, is the IIS 7 have another guest account so I can put it into login?

Thanks,

Problem Creating a subscription

I am trying to publish / subscribe a database onto SQL Server Mobile and when I run the following code that was, for the most part, generated by the SQL Server Management Studio Subscription Wizard,

repl.InternetUrl=@."file://chiefjoseph/walklisttransfersite/sqlcesa30.dll";

repl.InternetLogin = @."rr";

repl.InternetPassword = @."88";

repl.Publisher = @."CHIEFJOSEPH\SQLSERVER2005";

repl.PublisherDatabase = @."WalkList";

repl.PublisherSecurityMode = SecurityType.DBAuthentication;

repl.PublisherLogin = @."sx";

repl.PublisherPassword = @."xxx";

repl.Publication = @."WalkList";

repl.Subscriber = @."WalkListSubscription";

repl.SubscriberConnectionString = @."Data Source=""\My Documents\SQLServer\NewWalkList.sdf"";Password=""pwd"";Max Database Size=32;Default Lock Escalation =100;";

try

{

// repl.AddSubscription(AddOption.ExistingDatabase);

repl.AddSubscription(AddOption.ExistingDatabase);

repl.Synchronize();

}

catch (SqlCeException ex)

{

MessageBox.Show(ex.ToString());

}

I get the following error on the Pocket PC emulator

“A native exception has occurred in WalkLister.exe.Select Quit and then restart this program, or select Details for more information.”

When I try to use the subscription wizard to create the subscription, I get the following error:

Synchronizing Data (Error)

Messages

· Connection broken.
HRESULT 0x80004005 (0)

· The operation could not be completed.

Any insight to what the problem might be?

-Rob

I see a couple of odd things in your replication properties -

1. repl.InternetUrl=@.file://chiefjoseph/walklisttransfersite/sqlcesa30.dll

should be repl.InternetUrl = "http://chiefjoseph/walklisttransfersite/sqlcesa30.dll ";

2. the subscriber connection string should be

repl.SubscriberConnectionString = @."Data Source='\My Documents\SQLServer\NewWalkList.sdf';Password='pwd';Max Database Size=32;Default Lock Escalation =100";

I would recommend you start by using anonymous authentication (be sure to grant account IUSR_chiefjoseph permissions on your publication and the Walklist database if you do so) and then add SSL later. NT and DB authentication are a poor choice for merge replication because the userid and password are sent in the clear to the server.

-Darren