Automating copy between 3 servers

I'm developing a small app (Access), based on a SQL Server 2000 DB.
Development occurs on 3 DBs:
- Office DB - (MS SQL server 2000)
- Customer DB - (MS SQL server 2000)
- Portable DB - (MSDE 2000)
Since moving continuosly, as a daily routine I have to copy the latest DB,
and currently I use Enterprise manager to do so (backup/restore); boring,
'cause db paths are different on the 3 machines hosting the db servers.
Is there an easy way to automate the exchange between the 3 servers?

if all of your data is moving only in one direction and all of your tables have PK's on them, think about transactional replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
sql

Automating Bkp & restore from command line

Currently developing a small app (Access) I'm using the same app on 3 SQL
Databases:
1 - Production: MS SQL 2000 server
2 - Test: MS SQL 2000 server
3 - Test: MSDE
As I'm modifyng extensively db structure I have to manually move modified
dbs between the 3 servers daily. multiple times, and got bored to manually
select each time paths and options (overwrite, force restore, devices etc)
So I wrote a small script in QA and thought if possible to runnit from
commandline (OSQL would do the job?). But I still miss a statement to
specifiy to wich DB the script must connect before doing th rest:
Here's my code and I've got a trutsed connection, so y would run:
osql -q "Restore.sql" -E:
The script contains:
USE MASTER
GO
RESTORE DATABASE Test
FROM DISK = 'C:\test.BAK'
WITH REPLACE,
MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.LDF',
MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
What am I missing to connect to the prper server?
Thanks
You first say:

> But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
And then:

> What am I missing to connect to the proper server?
Which one is it? Database or server? To specify a server, you use the -S switch for OSQL. The db is a bit more
problematic as it is included in the RESTORE command, but my guess is that it is server you are after.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atlas" <atlaspeak@.my-deja.com> wrote in message news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
|||osql -S [servername] ?
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server
2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
|||Hi,
OSQL -S Server_name -E -i c:\Restore.sql -oc:\restore.log
Note: -q option is to give the query in the OSQL itself. Use iption "i" to
input a file.
Thanks
Hari
MCDBA
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server
2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>

Automating Access to SQL Server

We're trying to put a view of data maintained in desktop Access databases online and into SQL Server.
The desktop Access system uses separate databases instead of tables within one database, It's a strange design, but it can't be changed.
We have been importing all of the separate databases into a single, new Access database, then upsizing the new databse to SQL Server, then uploading it.
This is not going to work long term, because we are stuck with a 250 mB Access database to upsize and upload, when we never need to update more than 2 or 3 of the tables and upload more than 2 mB.
We'd like to be able to upload only the tables -- preferably the Access *.mdb's -- that have changed, and then replace the SQL Server tables with the new information. And we'd like to automate it as much as possible, without upsizing Wizardy.
I don't know where to even begin looking for information about how this might be done.
Any suggestions would be deeply appreciated.
- Tinker
This sounds possibly like a job for DTS. You could set up a DTSpackage on the SQL Server that will loop through the Access databasesand perform the updates as needed.
This site is a good resources for DTS work:http://www.sqldts.com.
|||Hi Terri, and thank you.
When I first ran across information about DTS, I skipped right past it. I'm curently out in the MS Tech Net learning more and it appears DTS may be much closer to what we want to do than I first thought.
Neither the FAQs athttp://www.sqldts.com/ nor the information I have trolled-through so far on the Tech Net discuss automating DTS; is that possible?
While we don't want to ride herd on these data updates any more than is absolutely necessary, it would be super great if I don't have to try to program my way through deleting all the records in an SQL table, then refilling the table with the new records...
Appreciate your suggestion,
- Tinker|||Sure, it's possible to automate DTS. You can use SQL Agent toschedule a DTS package to run on a schedule of your choosing.|||Thank you. I finally found where I get to do all this.
Sometimes I feel dumber than usual... 8-)
- Tinker

Automatically Sort Table when Table is Opened in Project

Hi all,

We recently upsized two Microsoft Access Databases to SQL. We're
using an ADP (2002) as the front end.

All the conversion issues have been resolved, except for one:
Whenever we insert a record into a table, the table isn't sorted by
primary key like I would expect. Instead, the record can be found at
the end of the table. This makes finding a particular record
(especially as time goes on) very difficult.

I've tried eliminating all indexes except for the primary key, and
also writing AFTER INSERT triggers, but the table still does not sort
correctly.

Any suggestions would be greatly appreciated!

MattThis is a client-side problem since a table in SQL has no inherent logical
order. I don't think Access will resort the table unless you requery it. To
get around this you will probably need to create your own form for data
entry and display - a good idea anyway if you are doing a lot of data entry.

--
David Portas
SQL Server MVP
--|||Yes adding to David's post, you can define the sort order for the adp form
(you can make the form look virtually identical to a datasheet/table if
you'd like). It will be a string like an Order by clause. I believe you
need the sort string and you need to set the OrderByOn property to true. You
can add code to react to a new record or whatever. Good luck.
~ck

"Matt" <mhen001@.yahoo.com> wrote in message
news:ea450280.0406211322.7b5e60e2@.posting.google.c om...
> Hi all,
> We recently upsized two Microsoft Access Databases to SQL. We're
> using an ADP (2002) as the front end.
> All the conversion issues have been resolved, except for one:
> Whenever we insert a record into a table, the table isn't sorted by
> primary key like I would expect. Instead, the record can be found at
> the end of the table. This makes finding a particular record
> (especially as time goes on) very difficult.
> I've tried eliminating all indexes except for the primary key, and
> also writing AFTER INSERT triggers, but the table still does not sort
> correctly.
> Any suggestions would be greatly appreciated!
> Matt|||oops in a project you use these properties.
ServerFilter Property

You can use the ServerFilter property to specify a subset of records to be
displayed when a server filter is applied to a form or report within a
Microsoft Access project (.adp) or a data access page in a Microsoft Access
project (.adp) or database (.mdb).

Setting

The ServerFilter property is a string expression consisting of a WHERE
clause without the WHERE keyword. For example, the following Visual Basic
code defines and applies a filter to show only customers from the USA:

Me.ServerFilter = "Country = 'USA'"
Me.ServerFilterByForm = TrueThe easiest way to set this property is by using
a form or report's property sheet. You can also set this property on a form
or report by using Visual Basic.

CK

"Matt" <mhen001@.yahoo.com> wrote in message
news:ea450280.0406211322.7b5e60e2@.posting.google.c om...
> Hi all,
> We recently upsized two Microsoft Access Databases to SQL. We're
> using an ADP (2002) as the front end.
> All the conversion issues have been resolved, except for one:
> Whenever we insert a record into a table, the table isn't sorted by
> primary key like I would expect. Instead, the record can be found at
> the end of the table. This makes finding a particular record
> (especially as time goes on) very difficult.
> I've tried eliminating all indexes except for the primary key, and
> also writing AFTER INSERT triggers, but the table still does not sort
> correctly.
> Any suggestions would be greatly appreciated!
> Matt|||Thanks for the replies. It looks like I'll have to go the form route.

The strange thing is: if you open up the properties of the table and
click the "Data" tab, you can specify the "ORDER BY" property. Help
defines this property as:

"Shows client-side sorting criteria sorting criteria applied after
the result set is returned from the database. This property is a
string expression that is the name of the field or fields on which you
want to sort records. When you use more than one field name, separate
the names with a comma (,). If you want to sort records in descending
order, type DESC at the end of the string expression."

Why would Microsoft create this property yet in practice not allow you
to sort tables on the client-side?

Matt

"ck" <c_kettenbach@.hotmail.com> wrote in message news:<5CMBc.3267$6F6.1885@.newssvr25.news.prodigy.com>...
> oops in a project you use these properties.
> ServerFilter Property
>
> You can use the ServerFilter property to specify a subset of records to be
> displayed when a server filter is applied to a form or report within a
> Microsoft Access project (.adp) or a data access page in a Microsoft Access
> project (.adp) or database (.mdb).
> Setting
> The ServerFilter property is a string expression consisting of a WHERE
> clause without the WHERE keyword. For example, the following Visual Basic
> code defines and applies a filter to show only customers from the USA:
> Me.ServerFilter = "Country = 'USA'"
> Me.ServerFilterByForm = TrueThe easiest way to set this property is by using
> a form or report's property sheet. You can also set this property on a form
> or report by using Visual Basic.
> CK
> "Matt" <mhen001@.yahoo.com> wrote in message
> news:ea450280.0406211322.7b5e60e2@.posting.google.c om...
> > Hi all,
> > We recently upsized two Microsoft Access Databases to SQL. We're
> > using an ADP (2002) as the front end.
> > All the conversion issues have been resolved, except for one:
> > Whenever we insert a record into a table, the table isn't sorted by
> > primary key like I would expect. Instead, the record can be found at
> > the end of the table. This makes finding a particular record
> > (especially as time goes on) very difficult.
> > I've tried eliminating all indexes except for the primary key, and
> > also writing AFTER INSERT triggers, but the table still does not sort
> > correctly.
> > Any suggestions would be greatly appreciated!
> > Matt

Automatic Update From Access?

Hey all,
This is one of my first forays into SQL server, so I don't know how dumb this question is. I've got a SQL server db that's going to be the underlying source for an ASP app. Two of the tables will need to be updated somewhat regularly, perhaps 5 or 6 times
a month. We actually get the info in the form of an Access database.
All I need is to take My_Table and My_Other_Table from the Access DB and use them to replace My_Table and My_Other_Table in the SQL Server DB - I'll keep the same table names, column names, and I don't need to play around w/ data types.
What's the best way to do this? BCP, DTS, etc? I'd appreciate some guidance. Thanks.
The simplest way (IMO) is to use a transform data task in DTS.
First you could create the tables on sql server with the datatypes you
require - before creating the package.
Next you create a package with a transform data task for each table's
import. Before the transform data task runs, you have an Execute SQL task
which truncates the tables.
After that you can schedule the task. BTW, I prefer to use DTSRUNUI to
generate the readable command-line string and use that, just so I can see
which version and package is really running in a job.
HTH,
Paul Ibison

Automatic sync between SQLce and Access

Hi,

I am converting an old eVB application to CF.NET 2. It's a very simple setup.
1) An Access database sits on the host and has occasional edits applied to it.
2) An application on the PDA with a mirror copy of the Access database (filtered data) and also has occasional edits applied
3) When the user puts the PDA in the cradle the databases get automatically synchronized with each other.

Probably couldn't get much simpler than this. This used to be very easy to do with PocketAccess and ActiveSync. Unfortunately this is no longer supported. I decided to use the new SQLce database instead when porting to CF.NET. It's working like a charm except that I haven't found an easy way to synchronize with the desktop Access application.

Merge replication seems to be exactly what I need but it doesn't work with Access or SQL Server Express. I don't need (or want) a full version of SQL Server. The dataset is very small.

ADS seems to point in the right direction except it doesn't support automatic synchronization. It also doesn't support Vista in combination with PPC2003SE.

Sync Services seems to have the same problems as ADS.

I have seen some third party tools around but they all require the user to manually kick off synchronization which I'm desperatly trying to avoid.

I've been searching for days now and I'm not getting any closer to a solution. Any help would be greatly appreciated!

Cheers,
Thomas

Hi Thomas,

You can kick of a process on the PC (from the desktop PC) (your own program), whenever ActiveSync is connected, and then use RAPI to launch a process on the device. You can even add command line parameters to the PDA process.

The activesync registry key is:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows CE Services\AutoStartOnConnect]

and opennetcf.org has a .NET RAPI wrapper: http://www.opennetcf.com/library/communication/OpenNETCF.Desktop.Communication.RAPI.CreateProcess.html

|||

Thanks for the reply. This is indeed a step in the right direction.

I guess the idea then would be to use ADS on top of it. So cradling the PDA would create 2 processes, one to start the ADS service and one to start some custom synchronization application on the PDA which in turn would connect to the ADS desktop service using RDA.

Sounds very clunky to me, but it could work. I guess I was hoping there would be an easy solution. I mean, i can't imagine a much simpler scenario and it's not supported out of the box (anymore). Why does Microsoft not support this?

|||

ADS is the replacement technology for CEDB/ADOCE - MS Access sync - as described here: http://blogs.msdn.com/sqlservercompact/archive/2007/01/13/sync-with-access.aspx

It looks like ADS does do automatic sync on desktop connection:

Synchronization happens when there is an active ActiveSync connection between the device and the desktop.

(from http://blogs.msdn.com/sqlservercompact/archive/2007/02/19/microsoft-sql-server-2005-compact-edition-access-database-synchronizer-ads-rtw.aspx)

|||

That's what I thought too. Unfortunately I haven't found any evidence that it actually does so. ADS documentation is skimpy at the moment, but from what I gather you have to create a PDA application that uses RDA to sync yourself. And the only thing it does automatic is start the desktop "listen" service. There is no "client agent". This is not "simple and easy" to do since you have to push and pull data manually (using RDA) and provide your own conflict handling! ADS as it is now is definitely NOT a replacement.

|||

*bump*

Don't tell me that's it?

|||

This MS blog post addresses your question:
http://blogs.msdn.com/sqlservercompact/archive/2007/02/22/ads-automatically-starting-the-synchronization-between-device-and-desktop.aspx

sql

automatic number problem

Hi, i have imported an access database into sqlserver 2000. In access,
i have some table fields which were using automatic number as
datatype. But i see that there is not a datatype in sqlserver like
automatic no. I have to enter id numbers to my tables for each records
automatically. Would anybody help me about solving this problem? Any
idea? Thanksfatih kayaalp (kaya_alp@.hotmail.com) writes:
> Hi, i have imported an access database into sqlserver 2000. In access,
> i have some table fields which were using automatic number as
> datatype. But i see that there is not a datatype in sqlserver like
> automatic no. I have to enter id numbers to my tables for each records
> automatically. Would anybody help me about solving this problem? Any
> idea? Thanks

You can assign a column the IDENTITY property:

CREATE TABLE a (a int IDENTITY(1, 1) NOT NULL,

To get idenity value the most recently inserted row, use scope_identity().

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I have some table fields [sic] which were using automatic number as
datatype [sic]. <<

You do not understand SQL or data modeling. Fields are not columns;
tables are not file; rows are not records. The entire concept of a
physical numbering of rows is not relational. Your original design
was wrong and you want to copy it into SQL. Why do you want to do
that again??

Take a course and take the time to learn to do it right.|||Joe, go back in the hole you came from, take an attitude course and then
come back. Not earlier.

"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0401132113.38222a3a@.posting.google.c om...
> >> I have some table fields [sic] which were using automatic number as
> datatype [sic]. <<
> You do not understand SQL or data modeling. Fields are not columns;
> tables are not file; rows are not records. The entire concept of a
> physical numbering of rows is not relational. Your original design
> was wrong and you want to copy it into SQL. Why do you want to do
> that again??
> Take a course and take the time to learn to do it right.|||Martin Feuersteiner (theintrepidfox@.hotmail.com) writes:
> Joe, go back in the hole you came from, take an attitude course and then
> come back. Not earlier.

Joe on an attitude course? What a waste of time and money! That man is a
hopeless case!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> Joe on an attitude course? What a waste of time and money! That man
is a hopeless case! <<

I was going to take a Dale Carnegie course, but the restraining order is
still in effect.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Automatic Login?

Here's a "new" problem. I have this database set up so that it has an access front-end and an SQL backend. When I open the database on a computer with the SQL Server client installed it runs fine. When I open the database (access front-end) on a computer without the SQL client installed it prompts me to enter some login information to connect to the server. Is there any way to modify this front-end/backend so that the login information is automatically entered and it does not prompt the user to enter it? Just to let you know to make the front-end I used the link tables wizard to link the tables to the SQL Server.When you setup the "Link" did you click remember password? Did you setup the connection via ODBC using SQL Server Driver and if so do the non-"SQL Client" PCs have that driver installed? When you copy the Access front-end to a non-"SQL Client" PC is the DSN you defined for the lniked tables defined on those PCs?|||I did not click Remember Password. I got it fixed.
Thanks,
Aaron

Automatic Identity Range Handling warning message.

Ive implemented merge replication with SQL Server and an Access Database.
SQL Server is the publisher and Access database is the subscriber.
I have ONE table in the schema that can be updated in both the subscriber
side as well as the publisher side. I have an IDENTITY column in this table
and I have set the property "Automatic Identity Range Handling" ON for this
table.
Ive also created a script to create this publisher. Now, heres my problem.
When I create the publisher, I get a warning message thats stated below:
"Warning: only Subscribers running SQL Server 2000 can synchronize with
publication '<database>' because automatic identity ranges are being used."
OK, Ive done some resonable testing on this statement and it SEEMS that
synchronization with Automatic Identity Range Handing enabled DOES work for
Access databases as well even though the warning states otherwise! Im using
JET to connect to access and not the desktop engine. Once the threshold is
reached, and after the merge agent kicks in, the indentity seed is reset to
the appropriate values on both the access and the sql server side.
Appropriate check constraints are also placed.
I just want to make sure if this is correct - and this warning is just
bogus.
Thanks,
Girish
Hi Girish,
From your descriptions, I understood that you would like to know whether it
matters when warning is shown for "automatic identity ranges are being
used". Have I understood you? If there is anything I misunderstood, please
feel free to let me know.
Based on my scope, there are some known issues for us by using automatic
identity ranges. You could have a view on them
BUG: Unable to Change Identity Range of Publisher If You Use Auto Identity
Range
http://support.microsoft.com/default...b;en-us;310540
BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs
Continuously
http://support.microsoft.com/default...b;en-us;304706
Additionaly, there is a by design issue for automatic identity range based
on the descriptions in the following documents.
PRB: Automatic Identity Range Handling Is Not Correct If the Merge Agent
Runs an Insert Trigger
http://support.microsoft.com/default...b;en-us;324361
I am afraid the above all are why this warning is shown up.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

automate copying access table to sql server 2005 db

I have a table in MS Access that I would like to copy/import into SQL Server 2005. I would like to automate this process so that it copies the table over on a daily basis. Can someone tell me how this can be done?
Thank you in advance.Take a look at DTS packages which you can schedule as a job - nice and easy ;)|||Take a look at DTS packages which you can schedule as a job - nice and easy ;)
I'm sure you meant SSIS :)|||SSIS? I've not come across that acronym before...
I figured for someone new at SQL Server, a scheduled DTS would be the easiest solution...|||DTS is with 2000,
SSIS is with 2005|||In 2005 (Server Management Studio)
Select database
> Management
> Legact
> Data Transformation Services
*confused*|||key word is "Legacy" here. the new ETL component in 2005 is SSIS, it's been completely re-architected.|||How do I use SSIS exactly to accomplish my task (see orig question posted).

Thanks.|||How do I use SSIS exactly to accomplish my task (see orig question posted).

Thanks.

Use SSMS. Right-click the database to which you want the data imported. Select Tasks | Import Data. Follow through the wizard and be sure to save the SSIS package to your DB server.

Then you will need to create a scheduled job to run the SSIS package on whatever schedule you want.

Notes:
If the Access db is located on a server other than the DB server, you'll need to use UNC paths (and probably an ODBC link, but it's been a while since I've had to use Access)

SSIS is a separate component of the SQL 2005 installation. If you have not installed it, it won't be available to you.

Regards,

hmscott|||That was the answer I was looking for. I created a SSIS Package and created a job for it. Seems to work nicely. Thanks again!

automate Access project connection to MSDE database

I'm using Visual C++ to insert records into my MSDE database. I'd like the
user to be able to view the data via Access projects. In my application, is
it possible to configure the connection settings for an Access project to
connect to the MSDE database? For example, if I were to click the View button
in my application, can I have Access project connect to the database behind
the scenes and then display the contents of the database?
Thanks!
You can use automation to launch Access and display forms and reports.
I'm not a C++ programmer, so I couldn't tell you *how* to do that.
However, I'm not sure I really understand what you are trying to do.
The phrase, "display the contents of the database" covers a lot of
ground. Perhaps you can be more specific.
--Mary
On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
<luv2travel@.discussions.microsoft.com> wrote:

>I'm using Visual C++ to insert records into my MSDE database. I'd like the
>user to be able to view the data via Access projects. In my application, is
>it possible to configure the connection settings for an Access project to
>connect to the MSDE database? For example, if I were to click the View button
>in my application, can I have Access project connect to the database behind
>the scenes and then display the contents of the database?
>Thanks!
|||When you create a new access project that connects to an existing database
there are certain settings that the wizard asks of you before connection to
the database can be made. How do I pass those parameters from my application
to access project? Once the project has been created after the connections
settings have been configured, the user will have full access to the tables
within the database. The user can then create queries and reports as needed.
Does this explain things a little better?
"Mary Chipman [MSFT]" wrote:

> You can use automation to launch Access and display forms and reports.
> I'm not a C++ programmer, so I couldn't tell you *how* to do that.
> However, I'm not sure I really understand what you are trying to do.
> The phrase, "display the contents of the database" covers a lot of
> ground. Perhaps you can be more specific.
> --Mary
> On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
> <luv2travel@.discussions.microsoft.com> wrote:
>
>
|||When you create a new access project, there are connection settings that need
to be configured before a connection can be made. How do I pass those
parameters to the connection wizard via my application? Once the connection
is made to the MSDE database, Access will then display all the tables in the
database. The user can then generate queries or reports as needed. Forms are
not necessary because if I were to create custom forms I would have created
it within my application instead of connecting to an Access database. The
reason I chose to use Access project is because I'd like the users to be able
to create queries and reports of their choosing. My job is just to dump the
data into the table and have them decide on the type of queries and reports
to generate. Is this more clear?
"Mary Chipman [MSFT]" wrote:

> You can use automation to launch Access and display forms and reports.
> I'm not a C++ programmer, so I couldn't tell you *how* to do that.
> However, I'm not sure I really understand what you are trying to do.
> The phrase, "display the contents of the database" covers a lot of
> ground. Perhaps you can be more specific.
> --Mary
> On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
> <luv2travel@.discussions.microsoft.com> wrote:
>
>
|||I understand now. However, your choice to use an Access project is a
bad one. Users will not be able to create and save local queries
(although they can save local reports). You would need to grant them
permissions to create views or stored procedures on the server, and
this you probably don't want to do for two reasons: (1) security and
(2) clogging the server with a lot of user objects. Unless a user is
connected as a sysadmin, they will own all their own objects and will
need to grant others permissions to use them, which you don't want to
get into. In your situation, I'd use an .mdb. You can programmatically
link tables at runtime using DAO, and if users create their own
queries, they are saved locally, not on SQL Server. HTH,
Mary
On Sun, 6 Feb 2005 23:03:08 -0800, "luv2travel"
<luv2travel@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>When you create a new access project, there are connection settings that need
>to be configured before a connection can be made. How do I pass those
>parameters to the connection wizard via my application? Once the connection
>is made to the MSDE database, Access will then display all the tables in the
>database. The user can then generate queries or reports as needed. Forms are
>not necessary because if I were to create custom forms I would have created
>it within my application instead of connecting to an Access database. The
>reason I chose to use Access project is because I'd like the users to be able
>to create queries and reports of their choosing. My job is just to dump the
>data into the table and have them decide on the type of queries and reports
>to generate. Is this more clear?
>"Mary Chipman [MSFT]" wrote:

Autoincrement record position in a view

Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?

For example let's say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?

recno persons
1person1
2person2
3person3

Please help me

I will be very grateful if you also reply your answers also and to my email

Email: stavrinc@.hotmail.com

Thank you

Christos StavrinouWith FoxPro you can use the RECNO() function:

SELECT RECNO() AS recno, persons FROM myTable

This is not possible with SQL Server, and I am relatively sure it is not possible with Access.

Terri|||I should have said that this is possible with SQL Server only by inserting your result set into a #TEMP table which has an Identity column.view post 368541

Terri

Auto-Increment Primary key Sqlce Problem

Hello,

I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.

I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.

The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).

Example:

Table "Colmos" on First State after the copy using RDA:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1

Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)

When i want to do another insert i get this error:

A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]

The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.

The code that i am using is:

Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"

Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)

connection.Open()

c.ExecuteNonQuery()
connection.Close()

If anyone could help me..

Thanks!

Hello,

I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.

I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.

The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).

Example:

Table "Colmos" on First State after the copy using RDA:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1

Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)

When i want to do another insert i get this error:

A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]

The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.

The code that i am using is:

Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"

Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)

connection.Open()

c.ExecuteNonQuery()
connection.Close()

If anyone could help me..

Thanks!

|||

Hello,

I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.

I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.

The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).

Example:

Table "Colmos" on First State after the copy using RDA:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1

Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)

When i want to do another insert i get this error:

A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]

The problem is that the next id that the sqlce want to insert is number 2, and that id already exists than i got that error.

The code that i am using is:

Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"

Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)

connection.Open()

c.ExecuteNonQuery()
connection.Close()

If anyone could help me..

Thanks!

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

Auto-increment fields when moving a base from Access to SQL server

Hello all,

I'm a total newbie with SQL Server 2000 and I have a little problem when
moving a database form Access 2000 to SQL Server 2000.
In the Access database, each table has an auto-increment field.
After importing the tables in SQL Server, all the auto-increment fields
are turned into "int" type fields.
Does anybody have an explanation for that mystery?

Thanks in advance,
YanYou might want to change the field type from int to identity. The identity
field type in SQL Server is analogous to the Autonumber field type in
Access.

good luck

CJ

"Yan Roosens" <yan.roosens@.skynet.be> wrote in message
news:3F7C4258.6C6B428E@.skynet.be...
> Hello all,
> I'm a total newbie with SQL Server 2000 and I have a little problem when
> moving a database form Access 2000 to SQL Server 2000.
> In the Access database, each table has an auto-increment field.
> After importing the tables in SQL Server, all the auto-increment fields
> are turned into "int" type fields.
> Does anybody have an explanation for that mystery?
> Thanks in advance,
> Yan|||Hi CJ,

> You might want to change the field type from int to identity. The identity
> field type in SQL Server is analogous to the Autonumber field type in
> Access.

Thank you, the error message related to that problem is no longer displayed, I
will now care about the next one :-(

Yan|||On Thu, 02 Oct 2003 17:20:57 +0200, Yan Roosens
<yan.roosens@.skynet.be> wrote:

>Hello all,
>I'm a total newbie with SQL Server 2000 and I have a little problem when
>moving a database form Access 2000 to SQL Server 2000.
>In the Access database, each table has an auto-increment field.
>After importing the tables in SQL Server, all the auto-increment fields
>are turned into "int" type fields.
>Does anybody have an explanation for that mystery?
>Thanks in advance,
>Yan
Because that isn't a data type. Look up identity for an explanation of
how sql server can provide that sort of functionality.

AutoIncrement Fields

I am trying to upsize a Microsoft Access database.
I have used Autoincrement/random to generate some key fields
Is there a way to duplicate this fuctionality in MSDE? ( I can find the
ability to increment starting with a seed)
thanks
Ed Warren.
Hi,
See IDENTITY property in SQL Server books online. Usage is
CREATE TABLE TESTTABLE(i int IDENTITY(1,1), Name Varchar(10))
So the value for i start with 1 and increment by 1
Thanks
Hari
SQL Server MVP
"Ed Warren" <eowarren@.fakeaddress.zzz> wrote in message
news:exV1d5rNFHA.1500@.TK2MSFTNGP09.phx.gbl...
>I am trying to upsize a Microsoft Access database.
> I have used Autoincrement/random to generate some key fields
> Is there a way to duplicate this fuctionality in MSDE? ( I can find the
> ability to increment starting with a seed)
> thanks
> Ed Warren.
>
|||Ed,
You can use a trigger to give you a random auto increment - the upsizing
wizard can do this for you or you can add your own similar to
CREATE TRIGGER [StudentsTrig] ON dbo.Students
FOR INSERT
AS
SET NOCOUNT ON
Declare @.randc int, @.newc int
SET @.randc=0
WHILE @.randc=0
BEGIN
SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
END
SELECT @.newc = (SELECT [Stud ID] FROM inserted)
UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
You can modify the select statement if you want to allow negative numbers
(like Access).
Russ Stevens
|||That's what I'm looking for, thanks a lot
Ed Warren
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:e3FZJAtNFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Ed,
> You can use a trigger to give you a random auto increment - the upsizing
> wizard can do this for you or you can add your own similar to
> CREATE TRIGGER [StudentsTrig] ON dbo.Students
> FOR INSERT
> AS
> SET NOCOUNT ON
> Declare @.randc int, @.newc int
> SET @.randc=0
> WHILE @.randc=0
> BEGIN
> SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
> END
> SELECT @.newc = (SELECT [Stud ID] FROM inserted)
> UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
> You can modify the select statement if you want to allow negative numbers
> (like Access).
> Russ Stevens
>

AutoFetch Option with Fast Forward Cursors with SQLOLEDB

Hi,

I am using the AutoFetch Option with Fast Forward Cursors with SQLOLEDB to access SQL Server 2005. This really works out but for the first I execute the query only.

But as I use parameterized statements, when I re-use the cursor, just re-binding new variables and re-executing it again, the AutoFetch does not work any more.

I noticed that thru the SQL Server Profiler. I see a sp_cursorfetch been called at the second time the cursor is re-executed.

Does anyone know how to work it out?

Thanks in advance.

Marcelo.

Hi Marcelo,

Are you using OLEDB interfaces directly or are you using SQLOLEDB through ADO? Also, can try to see if not calling Prepare on the command makes a difference in the behavior?

Thanks

Waseem

|||

Hi Waseem,

I am using OLEDB interfaces directly.

I prepare the parameterized statement only once and then re-execute it multiple times, binding different variables for each new execution. I noticed that the auto-fetch happens when I execute the statement the first time, but after that in the next execution I see with SQL Server Profiler a fetching call been done.

Regards,

Marcelo.

Auto update to sql server tables

I'm changing data storage for an asp.net project from MS Access to Sql Server. I've got the web site working, but I need to update the sql server tables with data from our Oracle db daily. What is the "best" way to do that?

I've read about DTS, but have never done anything like that. Would it be worth the time and effort to study? (So far I've created a package, with the import wizard, that doesn't work & I don't have the authority to delete :-)

I know I could create a dataset with my Oracle data and use that to update sql server. But is there a way to schedule an aspx to run authomatically? Would this affect performance? The sql server db isn't very big (30-40,000 records), but the Oracle db is & I need to do quite a bit of manipulation to the data.

This is new to me & I'm don't know what I should be searching for to find help. And if there is a more appropriate place to post this question, please let me know.

Thanks.Yes. DTS is one good way to go and it is very easy to do.

Do you need to purge the data in your SQL before each load? Then the account has to have the right.

You can schedule the DTS to run at whatever time you want.

Auto numbering field similar to

I've been using MS Access 2000 for a while and have recently switched to MS
SQL 2000. When creating a primary key I am used to MS Access ability to auto
matically enter a number in the ID field when I enter data into my tables.
Does MS SQL have a feature similar to this? I checked all the data types and
the only thing that I see that is close to autonumber is uniqueidentifier. Is
that the same thing?
Walker_Michael wrote:
> I've been using MS Access 2000 for a while and have recently switched
> to MS SQL 2000. When creating a primary key I am used to MS Access
> ability to auto matically enter a number in the ID field when I enter
> data into my tables. Does MS SQL have a feature similar to this? I
> checked all the data types and the only thing that I see that is
> close to autonumber is uniqueidentifier. Is that the same thing?
No, not really. What you want is an IDENTITY column (attached to a
numeric data type) as in:
Create Table Customers (
CustID INT IDENTITY NOT NULL )
Unique identifiers can be used as well, but you need to generate the
number manually using the newid() function. They consists of a 16-byte
hexadecimal number (GUID). Some SQL Server users use them as keys. They
are used frequently in replication. The INT IDENTITY can accommodate
more than 2 Billion values and is only 4-bytes as opposed to 16.
The return the last identity value inserted, you should use
scope_identity(). From a stored procedure, you could use:
Create Proc dbo.UpdateCustomer
@.CustID INT OUTPUT,
@.CustName VARCHAR(50
as
Begin
If @.CustID IS NOT NULL
Update dbo.Customers
Set CustName = @.CustName
Where CustID = @.CustID
Else
Begin
Insert dbo.Customers (
CustName)
Values (
@.CustName )
Set @.CustID = SCOPE_IDENTITY()
End
End
To call this procedure:
Declare @.CustID INT
Exec dbo.UpdateCustomer @.CustID OUTPUT, 'David Gugick'
Select @.CustID
David Gugick
Imceda Software
www.imceda.com

Auto Number or Identity Seed on Oracle Database

Need help on the Auto Number or Identity Seed on the Oracle Database

I got an Access database that need to be converted to Oracle 9i.
Somehow the Trigger we created to simulate the "AUTO NUMBER" on Access
could not create the sequence number as soon as the value has been
inserted. The sequence number can only be created after we go to the
second line. Please see the trigger below.

Is there anyway we could create a trigger that could create the
sequence number as soon as we enter a value? It should be very
similar to the "Auto Number" on Access, or "Identity Seed" on SQL
Server.

------------------
1. sequence SNP.SECTION_ID_SQ:

CREATE SEQUENCE SNP.SECTION_ID_SQ
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER
/
GRANT SELECT ON SNP.SECTION_ID_SQ TO "PUBLIC"
/

2. Trigger SNP.SNP001_T_I_GET_NEXT_SECTION_ID:

CREATE OR REPLACE TRIGGER SNP.SNP001_T_I_GET_NEXT_SECTION_ID
BEFORE INSERT
ON SNP.SNP001_SECTION
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (new.section_id IS NULL)
BEGIN
SELECT section_id_sq.nextval
INTO :new.section_id
FROM dual;
END;Ken wrote:
[snip]

Already answered in c.d.m.a and a shedload of oracle groups, why are you
asking an Access/Oracle question in a SQL Server group? You're going to
annoy Celko in a minute <g
--
This sig left intentionally blank