RE: Creating A Duplicate Database Without Enterprise Manager

Hi There,

I was wondering if there was a simple way of create a duplicate of an entire database.

I often need to create a second copy of a database for development purposes. I have lost the use of Enterprise Manager which I can no longer simply script the database and then use dts to move the data across.

Is there a way of doing this in T-SQL?

Thanks

SteveTake a full backup and restore as a different name ... that should do it .. i think ... rather than going about it the other way round.|||You lost the use of Enterprise Manager?

HUH?|||I think they're an amputee|||Perhaps it was destroyed by a Klingon Manager?|||Or a Romulan cloaking device has been attached, so it's really there, he just can't see it ;-)|||Could have been an Organian illusion all along...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55463

RDA Pull method not creating tables/inserting data

I can't see what is going on, this is the situation:

I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far:

I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner.

So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code:
---------
string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values)
string connectionString = "Data Source=\"\\Program Files\\client\\db\\MobileDB.sdf\"";

SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll",
connectionString);

IList _tableNames = new ArrayList();
IList _queries = new ArrayList();

############
Code that prepares tables and queries
############

for (int counter = 0; counter < _tableNames.Count; counter++)
{
rda.Pull(_tableNames[counter].ToString(), _queries[counter].ToString(), rdaOleDbConnectString, RdaTrackOption.TrackingOn, "MobileError");
}

the For loop runs with no problems but no data is ever put (or tables created) into the Mobile DB.

A few things here -

1. from pocket internet explorer on your device or emulator, do you get a correct diagnostic message when you use the url http://10.1.1.206/mobile/sqlcesa30.dll ? if not, then your first issue is you aren't successfully getting from device to the server.

2. your connection string is screwed up. try this instead: connectionString = @."Data Source = \Program Files\client\db\MobileDB.sdf"

3. you do realize that the database must exist already before the pull? and that none of those tables can exist when you call pull? (you need to drop all the tables you want to pull from the server before you call rda.Pull()

4. the values you are using for <user> and <password> must represent a valid SQL Server login as well as have permissions on the specific database you are pulling from <DB>

5. what are you using for primary keys on the tables you are pulling? IDENTITY columns are going to get you into trouble with multiple users pulling with tracking turned on. better to switch to uniqueidentifiers (GUIDs).

Try that much and let me know.

Darren

|||

To answer your questions:

1. I get "SQL Server Mobile Server Agent 3.0" when I try to browse to the URL

2. I will try it without the quotes.

3. Yes, I know the tables must not exists prior to PULL

4. I had made the SQL user a db_owner

5. GUIDS

|||

It did not work, I changed the connection string from:

"Data Source=\"\\Program Files\\client\\db\\MobileDB.sdf\"";

to

"Data Source=\\Program Files\\client\\db\\MobileDB.sdf";

and it still didn't work

|||

if I understand correctly, you are not getting an exception, you're just not getting the tables created in the SQL mobile database after the pull? Are you just using a SELECT * FROM statement to pull each table? Maybe try reducing this to pulling a single table in case there is some issue with your logic to iterate through a collection of tables pulling each one. Also - not knowing the schema of the server side db, are there any constraints on those tables?

-Darren

|||

I just remembered something that I bet is your issue - when you install SQL Server, you have to specify an authentication mode for the server. By default, it is Windows Authentication. In your RDA connection string, you are using SQL Server authentication. As a result, you need to make sure your instance of SQL Server is set to "Mixed Mode (Windows Authentication and SQL Server Authentication).

Hope that helps.

Darren

|||Thanx, I'll try that|||we decided to give merge replication a shot, we may comeback to trying RDA later.

Rate Table - Need Help To Display Data Horizontally

Hi everyone. I need help creating a query for a transportation company. I am trying to querying a table of rates that stores it's values vertically and want to display the information horizontally.

Below is an example of sample data from the table. One column in the table lists all the different cities that a group of breakpoints apply to; another lists the different weight breakpoints; and the third contains the corresponding rates for each weight break. See example below.

Weight Range
(not a field in the database
just added to describe meaning
of breakpoint column) City Breakpoint Rate
0 -100 A 100 $100
101 - 200 A 200 $200
201 - 300 A 300 $300
0 -100 B 100 $100
101 - 200 B 200 $200
201 - 300 B 300 $300

I want to display the information horizontally
City 0-100 101-200 201-300
A 100 200 300
B 100 200 300

The only other twist is that different companies have different weight breaks and they are all stored in the same table. For example Company ABC's rates have the following weight breaks.

Weight Range
(not actual field
in database) Breakpoint field Rate
0-100 --> 100 $100
101-200 --> 200 $200
201-300 --> 300 $300

Company XYZ can have breakpoints such as the following.
0-50 --> 100 $100
51-100 --> 200 $200
101-150 --> 300 $300

If creating one report to accomodate both companies is not possible then I could also create seperate reports for each company. Any help or suggestions would greatly be appreciated. Thanks in advance.i would suggest that you do this in the application layer|||I agree with r937. Don't make your SQL statement too complicated, because it will be a pain in the butt to fix or change things later. It would be way easier to loop through the results and place each value in a cell in a table. By doing this you can also do some error checking for bad data before you display.

Good luck
Hope it helps|||This is essentially a "table pivoting" problem.
It's not clear from your example but I'm assuming that the number of output columns can be larger than 4, depending on the input data.
In that case, only recursive SQL (using "WITH", i.e., CTEs) will help you. (Or maybe your SQL engine has a built-in PIVOT functionality ...)

See http://tinyurl.com/6wugk for a related problem (with solution).

H.t.h.