Automating XML from Web to SQL Server

I have a web service, which returns XML to an http request. I need to automate the insert of this XML into SQL server.
I have already designed a procedure which used OPENXML to insert the XML packet, but I need to figure out a way to automate going to the page and retrieving the XML and calling the procedure.
I lack experience on the application development side of SQL Server, and would appriciate any quick & simple ways to perform this. The simpler the better...
Thanks in advance for your ideas.
Michael D.
"sorengi" <sorengi@.discussions.microsoft.com> wrote in message
news:5EEC9F63-7457-4343-A145-5FDCC3615AB3@.microsoft.com...
>I have a web service, which returns XML to an http request. I need to
>automate the insert of this XML into SQL server.
> I have already designed a procedure which used OPENXML to insert the XML
> packet, but I need to figure out a way to automate going to the page and
> retrieving the XML and calling the procedure.
The easiest way to do this would be to create a DTS package in SQL Server
that retrevies the XML and passes it into your procedure. You could then
schedule the package to execute at a specific interval.
See the SQL Server Books Online for information about DTS packages and
scheduling. A simple example of DTS can be found here:
http://sqlxml.org/faqs.aspx?faq=10
Bryant

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.

automaticly create a record's field

I used a field as the record's number,how can I get a automaticly created
number field (it can inrease automaticly) when I insert a record into a
table?Refer to the IDENTITY property in BOL
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"authorking" <authorking2002@.hotmail.com> wrote in message
news:uKU01w2CFHA.3732@.TK2MSFTNGP14.phx.gbl...
>I used a field as the record's number,how can I get a automaticly created
>number field (it can inrease automaticly) when I insert a record into a
>table?
>|||First, let's clarify some concepts. In SQL, rows are identified by a Key not
by a "record number". In fact the concept of a record number is quite alien
to the relational database model. The Key is part of your data - it is some
subset of the attributes that uniquely identify a row.
What you are asking for is called a *surrogate* or *artificial* key. SQL
Server provides the IDENTITY feature as a mechanism for an artifically
generated, surrogate key so take a look at IDENTITY in Books Online.
IDENTITY is not a substitute for the natural key of your table. It is just a
surrogate for that key and may be used in foreign key references. Many times
you won't need IDENTITY at all. If you aren't familiar with some of these
key concepts then look them up in a book on relational database
fundamentals.
Hope this helps.
David Portas
SQL Server MVP
--

automatically grow file

SQL Server 7.00, Service Pack 6
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
GeorgeThere is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George|||Seems you can't use a variable for the size, quite simply. Try using dynamic SQL to EXEC the
statement instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>sql

automatically grow file

SQL Server 7.00, Service Pack 6
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
George
There is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George
|||Seems you can't use a variable for the size, quite simply. Try using dynamic SQL to EXEC the
statement instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>

automatically grow file

SQL Server 7.00, Service Pack 6
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
GeorgeThere is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George|||Seems you can't use a variable for the size, quite simply. Try using dynamic
SQL to EXEC the
statement instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gb
l...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>

Automatically create rows

Is there a way to automatically insert a row into a table when a row is
created in another table?
For example, suppose a row is added to the "Current Data" table. I would
like another table, "Historical Data", to be automatically updated with data
from from the row added to "Current Data". Is this possible? If so how?
Thanks in advance for any help!Read-up on triggers in SQL Server Books Online. Triggers can be written to
respond to various DML statements and can do operations like inserting into
other tables etc.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:AF9C3D3C-518F-47DE-BF9B-9F4A0C544449@.microsoft.com...
> Is there a way to automatically insert a row into a table when a row is
> created in another table?
> For example, suppose a row is added to the "Current Data" table. I would
> like another table, "Historical Data", to be automatically updated with
> data
> from from the row added to "Current Data". Is this possible? If so how?
> Thanks in advance for any help!
>|||Matt
Lookup CREATE TRIGGER ... ON Table FOR INSERT,UPDATE in the BOL
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:AF9C3D3C-518F-47DE-BF9B-9F4A0C544449@.microsoft.com...
> Is there a way to automatically insert a row into a table when a row is
> created in another table?
> For example, suppose a row is added to the "Current Data" table. I would
> like another table, "Historical Data", to be automatically updated with
> data
> from from the row added to "Current Data". Is this possible? If so how?
> Thanks in advance for any help!
>sql

Automatic insert with a variable

Hi everyone, I'm very new at SQL so my knowledge is very limited. Therefore, all the details/examples I need to know to accomplish this would be greatly appreciated. Anyway, I was wondering if there was a way to create a job (that will run every week) that inserts a record into a database...which I've figured out how to do using the "Job" area in SQL EM, in fact, here's what I have -

INSERT INTO ProjMan (CustID, EntryDate, DueDate, DueTime, EmpEntered, Tech, ProjDesc, ProjType, Notes, IsCompleted) Values (1, '12/10/03', '1/2/04', '1/2/04 10:00:00 AM', 'Jamie', 'Jamie', 'Lito Report - auto insert','Job', 'no notes', 0)

But as you can see, my dates (and time) is 'hard-coded' in there. So my question is this: is there a way to automatically grab the current day and use it? Better yet, I would like this job to run every Sunday and actually have the date be for the coming up Tuesday, so i would need to add 2 days to the variable also if possible. If this isn't possible, I can just have the job run early Tuesday morning and insert it as the current day.

Thanks in advance for the help.Check BOL for functions getdate(), dateadd(), datepart(), etc.|||Thanks alot. I'll check it out.

Automatic Insert of data into a related table

I have two tables. When my user completes an insert of data in table (1), I would like the second "related" table (2) to be automatically populated with defaults. Is this possible?

My logical approach to this is:

1. Build a handler for the OnInsert event of the first table

2. In the handler, call the Insert Command on the SQLDataSource for the second table with the defaults specified in the DataSource.

What I'm not sure how to do is Step 2 or whats the best way. How do I call the Insertcommand programmatically for a DataSource? Or, is there a better way such as some kind of traditional hardwired SQL insert statement like in classical ASP? Or is there a way to programmatically call a stored procedure and if so is the 3rd approach the best way?

How exactly would someone do this best? It seems this would be a rather common thing someone might need to do.

Sub SqlDataSource1_Inserted(sender as object, e as system.eventargs) handles sqldatasource1.Inserted

dim conn as new sqlconnection("{Your connect string or pull from web.config"})

dim cmd as new sqlcommand("INSERT INTO Table2(col1,col2,col3) VALUES (@.col1,@.col2,@.col3",conn)

cmd.parameters.add("@.col1",sqldbtype.varchar).value={something}

cmd.parameters.add("@.col2",sqldbtype.varchar).value={something else}

cmd.parameters.add("@.col3",sqldbtype.varchar).value={something 3}

conn.open

cmd.executenonquery

conn.close

end sub

The above is easy, but it's not wrapped in a transaction. So if the 2nd insert fails, you'll have an inconsistant data model (No coresponding record in table2).

You can also build a trigger on table1 to do the insert.

You can also build a stored procedure, and use it to do both inserts via the sqldatasource.

|||

Motley:

You can also build a stored procedure, and use it to do both inserts via the sqldatasource.

I actually already have the first table insert occurring with a stored procedure so that I can get the identity. I guess I could use that stored procedure to also enter the default data for the second table but then I would need to know the Identity from the first table to plug it into the second table. Not really that good with stored procedures. Would something like this work?

CREATE PROCEDURE [InsertTable]

@.StoreID Int,

@.Weight real,

@.Length real,

@.Name nvarchar(25),

@.myID int OUTPUT

AS

INSERT INTO [FirstTable] ([StoreID], [Weight], [Length]) VALUES (@.StoreID, @.Weight, @.Length )

SELECT @.myID = @.@.IDENTITY

INSERT INTO [SecondTable] ([Table1ID], [Name]) VALUES (@.myID, @.Name)

|||

Yes, that should work fine. As a minor change, don't use @.@.IDENTITY, use SCOPE_IDENTITY() instead.

SET @.myID=SCOPE_IDENTITY()

There is some minor differences between the two, and what you really want is SCOPE_IDENTITY().

|||

I tried this and got errors for the other values so I gave up and went with the other approach which seems to be working.

The stored procedure is more elegant but it was just complaining there was no value for the very first column, and I had the value specified/harcoded as '0'. I was very

Automatic file grown

Hello:
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regardsAuto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>|||In addition to Geoff's post, you find a couple of stored procedures at .com" target="_blank">www.dbmaint
.com (see the "free
utilities section" for jobs that can serve as inspiration...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gb
l...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wa
it
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times? Wher
e
> can I find more information about this?
> Thanks in advance, best regards
>

Automatic file grown

Hello:
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regards
Auto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>
|||In addition to Geoff's post, you find a couple of stored procedures at www.dbmaint.com (see the "free
utilities section" for jobs that can serve as inspiration...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the DB
> grown? Can I force file grown when I want, ata some fewer-work times? Where
> can I find more information about this?
> Thanks in advance, best regards
>

Automatic file grown

Hello:
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regardsAuto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>|||In addition to Geoff's post, you find a couple of stored procedures at www.dbmaint.com (see the "free
utilities section" for jobs that can serve as inspiration...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the DB
> grown? Can I force file grown when I want, ata some fewer-work times? Where
> can I find more information about this?
> Thanks in advance, best regards
>

Automatic Creation and Modification log

Hi there,

I need some help in order to reduce the amount of code needed to
implement the logging of INSERT and UPDATE in my application's
database.

In every table of my database I have four fields (CreatedBy,
CreatedTime, ModifiedBy, ModificatedTime), these fields allow me to
trace the users who create and modify the registers in my application.
I know that I can add in every stored procedure a piece of code who
uses the app user as a parameter, and insert these information in the
register. But I would like to go beyond these method. I imagine there
is a better way of doing it, and I don't like to use triggers. Is
there any method, within the SQLServer functionality, that allows me
to automate this process?

So, any help would be very appreciated!!
Thank you very much in advance!Phino wrote:
> I imagine there is a better way of doing it, and I don't like to use
> triggers. Is
> there any method, within the SQLServer functionality, that allows me
> to automate this process?

Yeah, triggers.

--
Steve Troxell

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:

Auto-incremented fields error

I am trying to insert into a SQL Server table from an Oracle database. This table has an auto-incremented field, and when I try to insert into this table I get the following error:

Code Snippet

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL
into column '<column_name>', table '<my_table>'; column does not allow nulls.
UPDATE fails.[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been terminated. (SQL State: 23000; SQL Code: 515)

When I turn off all the triggers in the database, I don't get this error. But that is just a test environment, the production environment will need to have those triggers activated.

Any ideas on what is going on here?

Thanks.

Have a look at whether the following KB article (PRB: Guarantee @.@.IDENTITY Value on a Per Table Basis) is relevant to your case.|||

The most common cause of this issue is partial insertion into a table with non-null columns. If you have a table with (not null) columns, you must specify a value for that column on insert or it will generate this error. Make sure that either your inserts always apply values to these columns or the columns are nullable. Autoincrementing columns and columns with default values should not cause this issue, so you can ignore those as well.

Hope that helps,

John

Auto-increment key id field help

I have a key field ID (int) for a table. How can I auto incremnt the key so that when I insert a new record I can get a new key. I should not have to know the next available number in my table. What settings do I use for the field.

Thanks in advance
MoonWa

Check out the IDENTITY property.

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***
Check out SCOPE_IDENTITY() in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***
|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.

> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.codecomments.com ***

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***Check out SCOPE_IDENTITY() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.

> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.codecomments.com ***

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.developersdex.com ***Check out SCOPE_IDENTITY() in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.developersdex.com ***|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.developersdex.com ***

autocontained selects

Dear gurus,
select 'insert into cargafrecuencias([id],tipo,horas) values(' +
convert(char(2),[id]) + ',
''' + rtrim(ltrim(tipo)) + ''',
' + convert(char(5), horas) + ')'
from cargafrecuencias
That query returns fine INSERTS and ready for be executed:
insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
168 )
insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
720 )
insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
24 )
insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando llegue
el fichero', 8640 )
It's easy and useful without a doubt. But can you imagine do the same with a
table with 80 columns?
I would like do something like that (it would be gorgeous):
select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
from table
On the other hand is very annonying.
Does anyone have any ideas or thoughts?
Thanks in advance and regards,CREATE PROCEDURE SPInserttest
(
@.Test varchar(50)
)
AS
BEGIN
PRINT 'Do something with the test varchar(50)'
Select 'Jens'
UNION
SELECT 'BOB'
END
CREATE TABLE #Tabname
(
Names varchar(50)
)
INSERT INTo #Tabname
EXEC('SPInserttest ''Test''')
Select * from #Tabname
DROP Procedure SPInserttest
DROP Table #Tabname
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> Dear gurus,
> select 'insert into cargafrecuencias([id],tipo,horas) values(' +
> convert(char(2),[id]) + ',
> ''' + rtrim(ltrim(tipo)) + ''',
> ' + convert(char(5), horas) + ')'
> from cargafrecuencias
>
> That query returns fine INSERTS and ready for be executed:
> insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
> 168 )
> insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
> 720 )
> insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
> 24 )
> insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando llegue
> el fichero', 8640 )
>
> It's easy and useful without a doubt. But can you imagine do the same with
a
> table with 80 columns?
> I would like do something like that (it would be gorgeous):
> select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
> from table
> On the other hand is very annonying.
> Does anyone have any ideas or thoughts?
> Thanks in advance and regards,
>|||Try this one.
http://vyaskn.tripod.com/code/generate_inserts.txt
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
> Dear gurus,
> select 'insert into cargafrecuencias([id],tipo,horas) values(' +
> convert(char(2),[id]) + ',
> ''' + rtrim(ltrim(tipo)) + ''',
> ' + convert(char(5), horas) + ')'
> from cargafrecuencias
>
> That query returns fine INSERTS and ready for be executed:
> insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
> 168 )
> insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
> 720 )
> insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
> 24 )
> insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando
> llegue
> el fichero', 8640 )
>
> It's easy and useful without a doubt. But can you imagine do the same with
> a
> table with 80 columns?
> I would like do something like that (it would be gorgeous):
> select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
> from table
> On the other hand is very annonying.
> Does anyone have any ideas or thoughts?
> Thanks in advance and regards,
>|||Great, thanks a lot, it works very well
"Roji. P. Thomas" wrote:

> Try this one.
> http://vyaskn.tripod.com/code/generate_inserts.txt
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
>
>|||XXXXing life. That doesn't works with my table, it is too large in terms of
columns.
"Roji. P. Thomas" wrote:

> Try this one.
> http://vyaskn.tripod.com/code/generate_inserts.txt
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
>
>