PAGE HEADER: store

Showing posts with label store. Show all posts

Friday, March 30, 2012

Problem with SqlCeRemoteDataAccess.Pull() in Pocket PC 2003 application

hi
I am trying to make a Pocket PC 2003 application which will pull data from a SQL Server 2005 database and store in Mobile database. and Push the table from mobile databse to SQL server 2005 database

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

when user access the database as anoymous they'll log in as my account
I am working on Windows Vista and have IIS and SQL Server 2005 running on the same machine same machine alongside Visual Studio SP1 for vista
I've also got Windows Mobile Device Centre installed with Virtual PC 2007 Installer which means when I cradle the emulator the Windows Mobile Device Centre detects and connects it and I can internet on that.
but I can't access http://IDVISTA/SQLCE/sqlcesa30.dll (HostURL in the code) in Pocket PC 2003 emulator's Internet explorer (not sure if it counts)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

public void Pull()
{

// Create a remote data access object

SqlCeRemoteDataAccess RDA = new SqlCeRemoteDataAccess(HostURL, "", "", LocalConString);

try
{
RDA.Pull("Appointments", "SELECT * FROM Appointments ", RemoteConString, RdaTrackOption.TrackingOff, "ErrorPullingAppointments");
}
catch (SqlCeException sqle)
{
for (int i = 0; i < sqle.Errors.Count; i++)
MessageBox.Show(sqle.Errors[i].NativeError.ToString() + "\n" +
sqle.Errors[i].Source);
}
finally
{
RDA.Dispose();

}

Any help will be much appreciated
Thanks
hi
I am trying to make a Pocket PC 2003 application which will pull data from a SQL Server 2005 database and store in Mobile database. and Push the table from mobile databse to SQL server 2005 database

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

Any help will be much appreciated
Thanks

|||

It is vital that you can access the SQL CE agent URL from IE on your device. Try using the IP address of your PC instead of host name. Also, change your connection string to:

"Data Source=\\Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

Try connecting with SQL security instead of integrated security, to see if this is the issue.

Also, there may be problems related to Vista and integrated security, see this:

http://blogs.msdn.com/stevelasker/archive/2007/06/16/using-rda-with-integrated-authentication.aspx

|||

Thanks for the quick reply

I have now managed to access SQL CE agent URL from emulator's IE. (it was my windows firewall blocking incoming requests)

but the problem is still the same I still get the same error even after changing the connection string as you suggested

OK i gotta admit that I am fairly new to this whole thing and dont really know how to connect with sql security (could you specify a bit for me please)

i m reading the blog now, see if there is anything I am missing still out

Cheers

|||

still stuck

tried the idea in the blog too

need more help|||

You must specify a provider in your Remote connection string like this;

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

see http://www.connectionstrings.com/?carrier=sqlserver for more samples.

|||tried that too
still no avail

when it executes the pull(), in the output it says

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

any clues?

Problem with SqlCeRemoteDataAccess.Pull() in Pocket PC 2003 application

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

Any help will be much appreciated
Thanks

|||

It is vital that you can access the SQL CE agent URL from IE on your device. Try using the IP address of your PC instead of host name. Also, change your connection string to:

"Data Source=\\Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

Try connecting with SQL security instead of integrated security, to see if this is the issue.

Also, there may be problems related to Vista and integrated security, see this:

http://blogs.msdn.com/stevelasker/archive/2007/06/16/using-rda-with-integrated-authentication.aspx

|||

Thanks for the quick reply

I have now managed to access SQL CE agent URL from emulator's IE. (it was my windows firewall blocking incoming requests)

but the problem is still the same I still get the same error even after changing the connection string as you suggested

OK i gotta admit that I am fairly new to this whole thing and dont really know how to connect with sql security (could you specify a bit for me please)

i m reading the blog now, see if there is anything I am missing still out

Cheers

|||

still stuck

tried the idea in the blog too

need more help|||

You must specify a provider in your Remote connection string like this;

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

see http://www.connectionstrings.com/?carrier=sqlserver for more samples.

|||tried that too
still no avail

when it executes the pull(), in the output it says

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

any clues?

Problem with SqlCeRemoteDataAccess Pull

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

Any help will be much appreciated
Thanks

|||

It is vital that you can access the SQL CE agent URL from IE on your device. Try using the IP address of your PC instead of host name. Also, change your connection string to:

"Data Source=\\Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

Try connecting with SQL security instead of integrated security, to see if this is the issue.

Also, there may be problems related to Vista and integrated security, see this:

http://blogs.msdn.com/stevelasker/archive/2007/06/16/using-rda-with-integrated-authentication.aspx

|||

Thanks for the quick reply

I have now managed to access SQL CE agent URL from emulator's IE. (it was my windows firewall blocking incoming requests)

but the problem is still the same I still get the same error even after changing the connection string as you suggested

OK i gotta admit that I am fairly new to this whole thing and dont really know how to connect with sql security (could you specify a bit for me please)

i m reading the blog now, see if there is anything I am missing still out

Cheers

|||

still stuck

tried the idea in the blog too

need more help|||

You must specify a provider in your Remote connection string like this;

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

see http://www.connectionstrings.com/?carrier=sqlserver for more samples.

|||tried that too
still no avail

when it executes the pull(), in the output it says

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

any clues?

Monday, March 12, 2012

Problem with Saving a Stored Procedure

I have had the same problem. In the past I had the sql commands in the code. Last project was with SQL2000 and I decided to add store procedures part as a learning tool and part to better control the code. With the current project I am using SQL2005 and have a ummm fun time with the learning curve.

Below is my stored procedure.

USE [AdventureWorks]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER Procedure [dbo].[WDR_CountryAdd]

(

@.CountryName nvarchar(50),

@.CountryID int OUTPUT

)

INSERT INTO Country

(

CountryName

)

VALUES

(

@.CountryName

)

SELECT

@.CountryID = @.@.Identity

When I hit the execute button, I get the following error.

Msg 208, Level 16, State 6, Procedure WDR_CountryAdd, Line 24

Invalid object name 'WDR_CountryAdd'.

I I know that it does not exist as that is what I am trying to do, create it. I have checked to make sure that I am on the currect database. So how do I associate this procedure with the data base? Do I need to write a SQL query to create the procedure? Maybe that is the answer and then just modify it.

If it doesn't exists, you need to use a CREATE PROCEDURE statement instead of an ALTER PROCEDURE statement.|||

Arnie

I appreciate the fast response.

I used a create and it ran successfully. However, it still did not show up under the stored procedures. So if it does not show up in the list, how do I access it from my web application? With SQL 2000 I just did a right click create new, did the code and it saved it to the list and then I accessed it. I was planning on a code along the lines of WDR_CountryAdd("USA") (of course in the proper codes)

Now here is a beginners question, When I first execute this procedure, do I need to supply the parameters for it to successfully attach to the database?

Jerry

|||

Never Mind. I ran the CREATE again and it worked very well. Must of had a brain dead moment there.

Thanks for the help. I have learned a lot from this forum.

Jerry

Problem with rollback statement

Hi,

I have written a store procedure which inserts data into two tables. What I want do is to rollback transaction if the second insert fails. Below is a code.

Does anyone see my error?

Thanks,

poc1010

Create proc AddProducts

@.dcint=null,
@.pcint=null,
@.imagepathvarchar(50)=null,
@.typevarchar(2)=null,
@.descriptionvarchar(1000)=null,
@.gendervarchar(8)=null,
@.productidint=null,
@.pccodevarchar(2)=null,
@.weightvarchar(80)=null,
@.pricemoney=null,
@.activevarchar(1)=null

declare @.errorsave int
set @.errorsave=0
declare @.dg int

Begin transaction

insert productdescription(
designercategory,
productcategory,
imagepath,
type,
[description],
gender)
values(@.dc,
@.pc,
@.imagepath,
@.type,
@.description,
@.gender)

if @.@.error <> 0
set @.errorsave=@.@.error

set @.dg = @.@.identity

begin
insert Products(
productid,
designergroup,
designercategory,
productcategory,
pccode,
weight,
price,
active)
values(@.productid,
@.dg,
@.dc,
@.pc,
@.pccode,
@.weight,
@.price,
@.active)

if @.@.error <> 0
set @.errorsave=@.@.error
end

if @.errorsave <> 0
begin
print 'Insert into Products tables failed'
rollback transaction
return -5--Insert into Products tables failed
end

commit transaction
print 'Success'
return 0 --SuccessYou have begins and ends in useless spots. Whats the acual error message?|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||None of you guys used ELSE. Your BEGIN/END's are a little whacked out. Honestly, I'm against returning in mid procedure if it's not necessary. You can easily follow through the entire procedure using an ELSE, then returning a specified value.|||Pierre,

Thanks for your example. I saw what I was doing wrong. Works great.

Thank you for your help.

poc1010|||That's just personal taste Lee. No real argument either way. Not in this case.|||You're right. That's why I said that I prefer the other way. Didn't say you were wrong, because it works fine.

Wednesday, March 7, 2012

problem with query where column name is the same as a keyword

hi

I am having trouble with the following query within my store procedure.
as you can see, i am making an union of 2 separate queries.

in the 2nd part of the union, i encounter a column in the database where the column name is the same as the keyword "desc"

is there a way which i can get around this, or is there any other way that i can sepecify the column? (excluding the possibility of using *)

CREATE PROCEDURE topcat.getTransHistory
(
@.contact_id numeric(9)
)
AS
BEGIN
DECLARE @.phone_no varchar(255)

set @.phone_no = (select top 1 phone_num from topcat.class_contact where _id = @.contact_id)

select cast(trans_new.trans_date as varchar(50)) date,
'' code,
cast(payment.date_paid as varchar(50)) datepaid,
'' "desc",
case payment.payment_type
when 'cheque' then trans_new.item_total
else ''
end pledged,
'' mail,
case payment.payment_type
when 'cheque' then ''
else trans_new.item_total
end received,
'' receipt
from topcat.class_transaction trans_new left outer join topcat.class_payment payment on trans_new._id = payment.transaction_id
where trans_new.contact_id = @.contact_id
union
select cast(trans_old.date as varchar(50)) "date",
trans_old.code,
cast(trans_old.datepaid as varchar(50)) "datepaid",
trans_old.desc,
cast(trans_old.pledged as varchar(128)),
trans_old.mail,
cast(trans_old.received as varchar(128)),
trans_old.receipt
from topcat.MMTRANS$ trans_old
where phone = @.phone_no

END
GO

Cheers
James :)wrap your column name that is a keyword in square brackets eg [desc]|||thank you
thank you
thank you

you are a life saver, i was goin to change the column name of the table if i couldn't find an alternative way of doing it.

:)|||no worries,... try to avoid keywords in the future would be my advise though... ;)|||i was on a contract once and there converted database had tables and columns all over the place named with keywords.

i will never forget they had a column called select in a table called order.
nightmare to say the least.

Friday, March 30, 2012

Problem with SqlCeRemoteDataAccess.Pull() in Pocket PC 2003 application

Problem with SqlCeRemoteDataAccess.Pull() in Pocket PC 2003 application

Problem with SqlCeRemoteDataAccess Pull

Monday, March 12, 2012

Problem with Saving a Stored Procedure

Problem with rollback statement

Wednesday, March 7, 2012

problem with query where column name is the same as a keyword

PAGE HEADER

Blog Archive

About Me