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.

Automatical table update within a database

Hello!

We are developping a project using MS-SQLServer 7 and we need a
process for the synchronization of 3 tables together.
Inserts and updates in the table A should immediately and
automatically occur on table C, and updates on table C should also
automatically occur on table B.
We think that the solution using triggers and stored procedures is the
right choice. Could someone with knowledge on stored procedures help
us?
We need the help soon, this is quite urgent, so we’d be happy to
get an answer!
If something is not clear just ask!

Thanks in advance!
E. KellerLook at CREATE TRIGGER topic in the BOL

"E.Keller" <emmanuel.keller@.net2000.ch> wrote in message
news:4eed4cad.0401070114.65961769@.posting.google.c om...
> Hello!
> We are developping a project using MS-SQLServer 7 and we need a
> process for the synchronization of 3 tables together.
> Inserts and updates in the table A should immediately and
> automatically occur on table C, and updates on table C should also
> automatically occur on table B.
> We think that the solution using triggers and stored procedures is the
> right choice. Could someone with knowledge on stored procedures help
> us?
> We need the help soon, this is quite urgent, so we’d be happy to
> get an answer!
> If something is not clear just ask!
> Thanks in advance!
> E. Keller|||Use triggers if you cannot guarantee that the process that modifies table A
will also do the other two. If you do it all with stored procedures (my
preferred method) you can write the modification code for all three tables
inside the procs. make sure you wrap the 3 statements in a transaction so
they all complete or all roll back. Triggers automatically do the
transaction for you, but this is not always a good thing.

"E.Keller" <emmanuel.keller@.net2000.ch> wrote in message
news:4eed4cad.0401070114.65961769@.posting.google.c om...
> Hello!
> We are developping a project using MS-SQLServer 7 and we need a
> process for the synchronization of 3 tables together.
> Inserts and updates in the table A should immediately and
> automatically occur on table C, and updates on table C should also
> automatically occur on table B.
> We think that the solution using triggers and stored procedures is the
> right choice. Could someone with knowledge on stored procedures help
> us?
> We need the help soon, this is quite urgent, so we’d be happy to
> get an answer!
> If something is not clear just ask!
> Thanks in advance!
> E. Keller

AutoGenerate

Hi
I have set a field "MessageId" as primary in a Messages table. What I want is that whenever user inserts a message through my site, the MsSql should automatically generate MessageId for the new message inserted, but this is not happening. Any suggestions, advice are highly appreciated. Thank YouIs the column set as an IDENTITY column? If not, that explains the problem. In Enterprise Manager, go into Design mode for the table, and make sure in the properties window, Identity is True (or Yes, do not recall which is used).|||You've got to create a table in SQL with something like the below. As long as there is input in the column named "Message" then the MessageID will automatically increase.

CREATE TABLE Message
(
MessageID int IDENTITY(1,1) PRIMARY KEY,
Messagevarchar (2000)NOT NULL
)

Good luck!
-Gabian-|||Thanks to both of you gentleman.

One more thing, what enum of SqlDbtype should i keep for my actual Message(thats being recorded by the user) : "text" or "varChar" ?|||What are your needs:

Varchar will allow a maximum of 8000 bytes
Text will allow very large values ~ 2gb

Varchar will give you much more flexibility for searching and manipulating data though and if it is sufficient would be my recomendation.|||Depends on the size...

I usually use varchar (^_^)

Auto time stamp in Sql. Express

Is there a property setting in SqlEX. That automatically inserts the date and time in to a field (timestamp) in the dB, when a record is created. If so can someone please show me how this is done.

Thanks in advance

You can set up the default value for the column as getdate() in the design view of the table.