Automating Daily Database Inserts.

Hi can anybody please provide me information on how i can automate a vb.net script to insert records in to a SQL Server database everyday. I know that the Scheduled Tasks tool in Control Panel must be used but i am not sure about the VB.net code. Opening up the DB and inserting records is no problem its just geting the script to run using the Scheduled Tasks tool. Any other type of script that opens up a DB or updates it even a vb.net script that executes using the Scheduled Tasks tool would be helpful.

Thanks in advance for the help.

ImranDoes it have to be run via DotNet? It would be simpler if you could have the Sql Server Agent run some SQL or execute a sproc. Alternatively you could have DTS do it and Sql Server Agent would trigger the DTS package.

If you need to create the scheudle from DotNet you would need to create a Service that manages when things run and then at the appropriate time trigger your code. If you need to go this route I can post some code to get you started but it will be in C# so you'll have to translate.|||The service is the best idea.

Simply, yet very bad and chessy, but effective, is to build an aspx page that performs the work, and place the following command in a batch file:

"C:\Program Files\Internet Explorer\IEXPLORE.EXE" http://MyScriptURL"

You could then use the built in Scheduler (AT.exe from a command prompt) within 2000 or NT4, or any other scheduler, to run the batch file nightly.

Of course, the great risk here is anyone can fire that URL at anytime and then it runs more than once. If it's a critical, must be bullet-proof task, go with the service. If you want something quick and dirty, that's temporary, give the above a shot.

Brian|||McMurdoStation

i was going to post a question in the forum about this and i saw this thread.
can you give some advice on how to get started...link to some tutorial, i am writing a vb.net app tht will call some SP's ( the Sp's will add transactions to customer transactions table). i need to create an html file (i can already do this). basically i have the whole prog working. i can run it manually. i just need to schedule it so it runs automatically every night ( at the specified time).

thanks.|||This article should get you started for creating a DotNet scheduler service.|||Hi McMurdoStation,
yes could you post the code please.

Thanks for the help.|||Hi Brian,
is the command that you specified above the only command that needs to go in to the batch file or do some other commands need to be put in there. In other words do i need to create a file called for example updatedb.bat and put the following bit of code in to it in the following way.

C:\Program Files\Internet Explorer\IEXPLORE.EXE http://localhost/metrics/updatedb.aspx

or do some other bits of code need to go in there aswell? Thanks for your help its much appreciated.|||All you should need is the above one line of code. You can test it by running it from a cmd prompt yourself. All it does is fire IE with the address that follows, and of course, the page renders.

If you can schedule that command, w/o having to use a batch file, then that will work as well. It's just nice to keep the comand in a batch file so it can be updated w/o having to touch a scheduler.

I, again, do recommend the service. My idea is just a temp or short-term workaround.

Brian|||ASPNester,

The link posted above describes how to create a scheduler service with VB.Net. It's probably easier to work from that rather than try to translate my C# code.|||thanks McMurdoStation...will spend some tiem trying to go through the article and understanding it.
thanks.|||Brian thanks, the code worked.

However as you mention its not really an ideal method. Do you know of any web sites that have tutorials for creating "Windows?" Service, or any books that have instructions on creating window services, the URL that McDurmock gave uses Visual Studio so its not really much use for me.

Thanks for all you r help anyway.|||Wrox has a good book called "Visual Basic .NET Windows Services Handbook". It's just under 200 pages, and meant to get you going fast. However, it assumes you have VS.NET. Of course, you don't need VS.NET. It shows most code, so you should be able to get by.

I've only written one service, but was up to speed in just a few days with the book

ISBN 1-86100-772-8, ~$30.

Brian|||ok mate thanks for all your help.

Automatic delete from conflict tables

Is sql-server automatically deleting records from conflict tables?
Some records in the conflict tables seem to disappear.
If so, what is the definition of the time to retain data and how to
change it?
ThanksNo, SQL Server does not automatically delete records
unless its programmed in.
Try running a trace and see what is deleting them.
Peter
"Happiness is nothing more than good health and a bad
memory."
Albert Schweitzer
>--Original Message--
>Is sql-server automatically deleting records from
conflict tables?
>Some records in the conflict tables seem to disappear.
>If so, what is the definition of the time to retain data
and how to
>change it?
>Thanks
>.
>

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:

autoincremental field

How can we give serial numbers to the records in a tableUse RowNumber() function. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3quq.asp?frame=true
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"vinesh" <vineshk@.visitomega.com> wrote in message
news:%23sgjfUJcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> How can we give serial numbers to the records in a table
>

Autoincrement

Hello
I've a table of Users with an identity key
Some records are inserted by a replication system which sends records with
key like 2-4-6-8 ...
and put them into the table with a INSERT sql
Other records are inserted via web
I need that the records inserted via web takes a key like 1-3-5-7 ...
I've set the identity seed to 1 and identity increment to 2
I've made a test
1. Inserted some record by replication system
2. If I try to insert a new record manually (by enterprise manager) the new
key is a par number instead of an odd
What's wrong?
Can you help me?Why don't you instead of doing that create another field called Origin
make it a bit when it's from the web give it a value of 1 otherwise 0
Your identity will be Old Key + 2 (that's your increment)
http://sqlservercode.blogspot.com/
"Denis" wrote:

> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>|||Denis,

> What's wrong?
Is the property "not for replication" set in this identity column?
When the values are inserted from the replication, sql server takes that
number as the last identity value inserted in the table, so if the las value
was 8 then when you insert from the web using "set identity_insert t1 off"
will increment that value with the identity increment 8+2 and this will be
the next value to be inserted.
Example:
create table t1(
c1 int not null identity(1, 2)
)
go
insert into t1 default values
insert into t1 default values
insert into t1 default values
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 on
go
insert into t1(c1) values(2)
insert into t1(c1) values(4)
insert into t1(c1) values(6)
insert into t1(c1) values(8)
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 off
go
insert into t1 default values
go
select * from t1 order by c1 asc
go
drop table t1
go
AMB
"Denis" wrote:

> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>

AUTO UPDATE DATABASE

hello sir,
Please tell me how to update records in a table automatically in SQL.
Actually i want that if i delete a record from a table which has Serial
No. as primary key, then all other records should update automatically
means there Serial No. should be updated in sequence(1,2,3,4,5).
thanks
*** Sent via Developersdex http://www.codecomments.com ***Hi
I assume SerialNo is not an identity ...
create table e(j int ,i int)
insert e select 1, 3 union select 2, 7 union select 3,10 union select 4,20
select * from e
go
--delete the row
delete from e where j=2
--run this immeditaly
declare @.k int
set @.k=0
update e
set @.k=j=@.k+1
select * from e
go
drop table e
"ramji gupta" <gupta.rnd@.gmail.com> wrote in message
news:%23x%23m%2349tHHA.4948@.TK2MSFTNGP06.phx.gbl...
> hello sir,
> Please tell me how to update records in a table automatically in SQL.
> Actually i want that if i delete a record from a table which has Serial
> No. as primary key, then all other records should update automatically
> means there Serial No. should be updated in sequence(1,2,3,4,5).
> thanks
> *** Sent via Developersdex http://www.codecomments.com ***

AUTO UPDATE DATABASE

hello sir,
Please tell me how to update records in a table automatically in SQL.
Actually i want that if i delete a record from a table which has Serial
No. as primary key, then all other records should update automatically
means there Serial No. should be updated in sequence(1,2,3,4,5).
thanks
*** Sent via Developersdex http://www.codecomments.com ***
Hi
I assume SerialNo is not an identity ...
create table e(j int ,i int)
insert e select 1, 3 union select 2, 7 union select 3,10 union select 4,20
select * from e
go
--delete the row
delete from e where j=2
--run this immeditaly
declare @.k int
set @.k=0
update e
set @.k=j=@.k+1
select * from e
go
drop table e
"ramji gupta" <gupta.rnd@.gmail.com> wrote in message
news:%23x%23m%2349tHHA.4948@.TK2MSFTNGP06.phx.gbl.. .
> hello sir,
> Please tell me how to update records in a table automatically in SQL.
> Actually i want that if i delete a record from a table which has Serial
> No. as primary key, then all other records should update automatically
> means there Serial No. should be updated in sequence(1,2,3,4,5).
> thanks
> *** Sent via Developersdex http://www.codecomments.com ***

AUTO UPDATE DATABASE

hello sir,
Please tell me how to update records in a table automatically in SQL.
Actually i want that if i delete a record from a table which has Serial
No. as primary key, then all other records should update automatically
means there Serial No. should be updated in sequence(1,2,3,4,5).
thanks
*** Sent via Developersdex http://www.developersdex.com ***Hi
I assume SerialNo is not an identity ...
create table e(j int ,i int)
insert e select 1, 3 union select 2, 7 union select 3,10 union select 4,20
select * from e
go
--delete the row
delete from e where j=2
--run this immeditaly
declare @.k int
set @.k=0
update e
set @.k=j=@.k+1
select * from e
go
drop table e
"ramji gupta" <gupta.rnd@.gmail.com> wrote in message
news:%23x%23m%2349tHHA.4948@.TK2MSFTNGP06.phx.gbl...
> hello sir,
> Please tell me how to update records in a table automatically in SQL.
> Actually i want that if i delete a record from a table which has Serial
> No. as primary key, then all other records should update automatically
> means there Serial No. should be updated in sequence(1,2,3,4,5).
> thanks
> *** Sent via Developersdex http://www.developersdex.com ***

auto return of primary key of row just entered

Hello vmrocha,
Our records indicate that you have never posted to our site before. We hope you find the help you need.
If you need to make a post, we're always happy to help.Hello vmrocha,

Our records indicate that you have never posted to our site before. We hope you find the help you need.

If you need to make a post, we're always happy to help.|||Hello vmrocha,

Our records indicate that you have never posted to our site before. We hope you find the help you need.

If you need to make a post, we're always happy to help.|||perhaps this the golden rule being acted out in a web forum|||actually, seems more like a bug. post says it has two pages, but it doesn't really...|||it's a b0rken thread, the forum database has integrity problems

http://www.dbforums.com/showthread.php?t=1613785

kind of ironic for a database forum, eh

:)

Auto Number Sequence

How can I create a number sequence starting at a certain number and continue on for the number of records I have.

For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).

In my pseudo SQL code Im guessing it would be something like...

select * from Incident

update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)

Whats the easiest way to do this?

--1.

Alter TABLE Incident

DROP COLUMN RecordId

--2.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 3000

--or 3.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 1

You can look up this infomation Alter Table and Alter Column from Books Online.

|||

hi,

you can use the identity function

make use use of its parameter seed=3000 to start from 3000

here's the syntaxt

identity(seed, increament)

here's your sample code

use northwind

select IDENTITY(int, 3000,1) AS ID_Num,
lastname,firstname into #temp from employees

select * from #temp

regards,

joey

|||A potentially more expressive function you could use in place of identity is the row_number.

select row_number over( partiton by ... order by ...) + startin_number,

One caveat of Row_Number implementation on SqlServer is that SS05 doesn't allow an empty order by, nor does it allow to sort on a constant.|||

hi if any body can tell me the solutions of my prob.

i have a table in Access in which prid is auto number , but the prob is that these prid is not in sequnce i.e, some number missing, like that after 8 its 12 , how will i arange it with delete data etc

|||If you have issues with this column only within this table, you can delete this column and recreate another Auto number field for your prid column in Access.|||

You can do this by combining a variable with an update statement.
Here's an example. If you run this whole set of code, the records in the table end up with values 1,2,3,4.....

-- create a simple test table
create table testcounter (thefld int)
go

-- add four rows, all with the same value
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
go

-- Declare and initialize an int variable
DECLARE @.thecount int
set @.thecount = 0

-- update the table using the variable.
update testcounter SET
@.thecount = @.thecount + 1,
thefld = @.thecount
from testcounter
go

-- list the results
select * from testcounter

Auto Number Sequence

How can I create a number sequence starting at a certain number and continue on for the number of records I have.

For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).

In my pseudo SQL code Im guessing it would be something like...

select * from Incident

update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)

Whats the easiest way to do this?

--1.

Alter TABLE Incident

DROP COLUMN RecordId

--2.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 3000

--or 3.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 1

You can look up this infomation Alter Table and Alter Column from Books Online.

|||

hi,

you can use the identity function

make use use of its parameter seed=3000 to start from 3000

here's the syntaxt

identity(seed, increament)

here's your sample code

use northwind

select IDENTITY(int, 3000,1) AS ID_Num,
lastname,firstname into #temp from employees

select * from #temp

regards,

joey

|||A potentially more expressive function you could use in place of identity is the row_number.

select row_number over( partiton by ... order by ...) + startin_number,

One caveat of Row_Number implementation on SqlServer is that SS05 doesn't allow an empty order by, nor does it allow to sort on a constant.|||

hi if any body can tell me the solutions of my prob.

i have a table in Access in which prid is auto number , but the prob is that these prid is not in sequnce i.e, some number missing, like that after 8 its 12 , how will i arange it with delete data etc

|||If you have issues with this column only within this table, you can delete this column and recreate another Auto number field for your prid column in Access.|||

You can do this by combining a variable with an update statement.
Here's an example. If you run this whole set of code, the records in the table end up with values 1,2,3,4.....

-- create a simple test table
create table testcounter (thefld int)
go

-- add four rows, all with the same value
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
go

-- Declare and initialize an int variable
DECLARE @.thecount int
set @.thecount = 0

-- update the table using the variable.
update testcounter SET
@.thecount = @.thecount + 1,
thefld = @.thecount
from testcounter
go

-- list the results
select * from testcounter

Auto insert a variable number of records?

I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?

Thanks in advance,

Sunny Guam

Can't you put all operations in a trigger on tblA? Put the SELECT command to tblC before INSERT to tblB so that you can dynamically build INSERT commands to tblB.|||

Thanks for the advice, but at the risk of sounding dumb; I'm not familiar with triggers and how to set them up within SQL 2005. Can you give me an example perhaps?

|||

Here is an example:http://forums.asp.net/thread/1281234.aspx

You can start from hereEnforcing Business Rules with Triggers

Auto Increment Starting 1 after deleting records

Hi,
How can I make the auto increment number start from 1 again after deleting
records in the table? I should have written it down somewhere when I knew
it last year.
YontaekYou can use DBCC CHECKIDENT or TRUNCATE TABLE. See Books Online for more
details.
"Asp Psa" <asppsa@.hotmail.com> wrote in message
news:uqVv5akjGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Hi,
> How can I make the auto increment number start from 1 again after deleting
> records in the table? I should have written it down somewhere when I knew
> it last year.
> Yontaek
>