Exporting Packages

Can I simply Export my packeges on my SQL Server 2005 and use them on another SQL 2005 Server ?

Ie.

Can I build them at home, burn to a CD then import in work ? without any issues at all ?

I have a completly different user name in work for instance.

If not, is it possible to do this at all ?

Thanks

Yes, use windows authentication and/or configurations|||I would rather choose the option encrypt sensitive with password.
Then you can deploy your package where you want.
When you execute the package don't forget to provide the password...

Fridtjof|||Perfect thanks :)|||

Is you are moving packages between locations, the chances are that the resources used will be different and therefore have different credentials, so saving passwords would not really solve the problem, in fact it would be a risk as you would may disclose local credentials to another site or organisation, when there is no need. I'd also suggest that do not save sensitive is safer, and when used with configurations it is very easy for both development and operationally.

Exporting data with SMO?

I have searched low and high for some information on how to export data with SMO. My old build scripts were using SQLDMO.BulkCopy which was using BCP and it worked fast on entire database.
I can't find a way to do the same with SMO. As alternative, I tried using bcp.exe on each table but it's awefully slow because initial process startup overhead.
Please don't recommend Transfer class as I'm not looking to transfer server-to-server but server-to-hdd. Backup isn't right as well.
Any suggestions?
Thank you.
Alex

I'm not certain but I think the functionality you're looking for is now in the SSIS (SQL Server Integration Services) toolkit. It's the next evolution of DTS (which the BCP util uses by generating an adhoc DTS package). Or you can use the DMO with 2005 (you have to install the backwards compatibility components when installing SQL 2005 or you can add it on later if you want).

Note that SSIS is not available for SQL Server 2005 Express so you have to use DMO to do what you want (or BCP) if you don't have Standard or above. Also, you will find that the tools for designing SSIS packages are much more friendly than hand coding DMO.

|||

With regard to copying data: there is a Transfer object that perform this in an 'online' type of fashion. It creates the target database, creates the schema, and copies the data. All steps are optional.

See this sample for a starting point: http://blogs.msdn.com/mwories/articles/smosample_transfer.aspx

BTW, we decided not to implement BulkCopy in SMO as we lacked the proper library support for it. It ended up being a shim around SQL-DMO if we would have gone along with it. That would have not been much of an improvement.

SSIS is indeed something to look at.

Another option is to use the old DMO BulkCopy ad interim, until System.Data has full BulkCopy support (see the current bulkcopy object; which does not perform all of the functionality that the old BulkCopy object did).

Exporting .sdf from pda into a text file on desktop

Dear All,

I have an application running on my mobile device and the data is stored in the mobile database. So now i am trying to build any application on my pc which upon click should download the data from my mobile database into a local text file. Is there any idea or reference where I can stary working on this ?

DataSet ds = new DataSet();

...

DataAdapter da = new DataAdapter(...);

da.Fill(ds, "table1");

...

da.Fill(ds, "tableN");

ds.WriteXml("LocalTextFile.xml");

If XML is not what you want then write data row by row in the format you want using System.IO namespace.

|||

Dear Ilya,

I would prefer it to be in text file format. The problem what project shall I develop I mean in the visual studio 2005. Should it be windows form or device application ? Because if it is device application it cant run in my desktop rite ? So it should be windows form rite ? Thanks.

|||

Dear Ilya,

I have now build a new console application. So then I add a reference to

System.Data.SqlServerCe; to enable me to connect to the mobile database found in the pda. So when I try to run I get an error "Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)". The problem is that on the same machine I can compile my device application which is also using the same reference'System.Data.SqlServerCe. Please correct me if I am doing something wrong here. Another thing is that how must I reference the path to my mobile database in the pda ? I am trying like this I dont know if this is right

Mobile Device/MiniSD Card/MiniSD Card/test1/db1.sdf. Thanks.

|||

Dear All,

I have a problem here. What i exactly want to do it that build a console application where when I connect my pda to my desktop and run the exe file it should be able to transfer the .sdf data into a text file of my local desktop ? Is there any code or reference where can guide me ?

|||Some of the components from www.primeworks-mobile.com should be able to help you.|||

Dear ErikEJ,

I would prefer to write on my own as I cant afford third party software. Hope you can show me some light into it. Thanks.

|||What you are going to want to probably do is copy the database from the device to your desktop, then use the sql ce for the desktop libs (remember you can use sql ce on devices and full blown windows) to read through the database and output . You can accomplish copying the database over through code using the Windows CE RAPI. The full framework as far as I know does not have built-in support for it. So what you would need to do is P/Invoke some functions in the rapi.dll. Luckily somebody has essentially done this, look for the opennetcf's (www.opennetcf.org) desktop communication library. So once you use this to get a copy of the database over to your desktop you can use the sql ce for windows (not wm or ce) libs to connect to your now local copy of the database. Then using a SqlCeDataReader you can just iterate through every record in the database, while reading a record in you can format it however you like then push that into a file using a StreamWriter object.
|||

Dear Steve,

Thank you very much for your kind information. So now I have downloaded the OpenNETCF.Desktop.Communication Library . Now how shall I start ? Shall I start by building a console application ? So must I first manually download the database or the system can do it for me ? I am really at lost can your pls guide me further on this. Thanks once again.

|||If you want to do it as a console application then start there. Then my next step would be to use the Desktop Communication library to programmatically get the database from your device to local computer. The communication library should have came with an example application that you can probably look at to find out how they copy files back and forth. You can look their and do the same thing.
|||

Dear Steve,

Ok I have followed and done accordingly and sucessfully download the sdf file into my desktop. My next problem is that i am not able to connect to downloaded .sdf in the desktop. I am using the same code as I build the windows device application. Can your please tell me wat can be my mistake ? For example I use this

SqlCeConnection conn1 = new SqlCeConnection("Data Source = c:/test1.sdf");

First I got an error "Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)". Then I manage to solve "Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)". So wat is the possible error on my side ?

So what can be possible error on my side ? Thanks.

|||

Dear Steve,

I am ok with Desktop Communication library. I have already managed to download the database from the device to my local computer. My next step is that how can I from my console application link to the database which I have already downloaded using the said library. Thanks once again.

|||Once you have the sdf file over to you desktop you can use the desktop version of the sqlce data object. I think you need to download the Microsoft SQL Server Mobile 2005 Mobile Edition SDK. Once you have that downloaded and installed it you should be able to add the proper references to the desktop x86 version of the libraries that you need. Then you should be able to connect to the database and use it just like you did on the handheld with the .NET CF. You should be able to just use a SqlCeDataReader object to query the database then read through the returned records and output them using a StreamWriter object to put them into a text file which you can then control the format of.
|||

Dear Steve,

I have already downloaded the microsoft sql server mobile 2005 mobile edition sdk. So I tried to use the SqlCeDataReader and below is the errors I got. Can you please guide me on this ?

SqlCeConnection conn1 = new SqlCeConnection("Data Source = c:/test1.sdf");

First I got an error "Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)". Then I manage to solve that problem but got another one "Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)". So wat is the possible error on my side ?Thanks.

|||F**K YOU AND YOUR STUPID QUESTIONS. GOOGLE THE ANSWER AND FIND SAMPLE CODE YOU CHEAP BASTARD.